An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

1
vote
1answer
85 views

Tables of a huge database

When I work on a huge database, and after following normalization rules, I found that I have many tables for each part of my huge database. That makes me worry about the final number of tables, it ...
1
vote
2answers
111 views

How can I improve this query

I have the following table: CREATE TABLE `twitter_relationships` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `source_twitter_id` bigint(20) NOT NULL, ...
1
vote
1answer
63 views

Sanitizing a form's database input while retaining basic HTML markup

Is there a way to sanitize database input (say, for a comment form), which also allows for the end user to use basic HTML styling markup? As in, if a user wraps their text in <i>/<em> or ...
4
votes
2answers
493 views

How should I best design the tables and relationships, given the following rules?

In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy ...
-1
votes
1answer
73 views

Can I make that connection?

If I have a column with data type varchar(max) in a table that contains a big piece of text, can I make a connection between a word or a sentence in that text with another word or a sentence in ...
2
votes
2answers
750 views

Why does my query run faster after I force full table scan?

I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this. SELECT ...
9
votes
2answers
8k views

Slow Performance Inserting Few Rows Into Huge Table

We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
-1
votes
3answers
3k views

Conversion failure varchar to int

When running this code: declare @SQL NVARCHAR(max); declare @intFlag INT; set @intFlag = 2; SET @SQL = ' SELECT * into [##tmp1] FROM servicesstats_0511_0412 s WHERE department=''ACC'' and ((SELECT ...
3
votes
1answer
164 views

SQL query returns different results when index is dropped

In SQL Server 2000 SP4, I have a query SELECT <fields no aggregates> FROM PS_VED_VCH_TAO5 A ,PS_BUS_UNIT_TBL_AP B ,PS_RT_DFLT_VW C WHERE A.PROCESS_INSTANCE = 594170 ...
1
vote
4answers
3k views

Select from linked server to local server causing waits on both sides

The queries in question are here: http://pastie.org/4833351 Run individually, each of these queries runs through (albeit slow). Run as a group, it just sits there, showing either OLEDB waits on the ...
2
votes
1answer
4k views

query to find closest lesser date

I have a table with rates. each rate has an effective starting date. (the ending date of any rate is implied by the existence of a record with a newer effective starting date.) I also have a table ...
1
vote
1answer
246 views

postgresql pgadmin3 queryconnect attribute does not work

I'm using Fedora 17 and pgadmin3 to auto-connect to a postgresql database with one button click. Every time I want to run an SQL statement, I have to do 4 steps, (open pgadmin3), (connect to the ...
0
votes
3answers
57 views

Query Optimizing Request

I am very sorry if this question is structured in not a very helpful manner or the question itself is not a very good one! I need to update a MSSQL table call component every 10 minutes based on ...
2
votes
3answers
214 views

MySQL data view

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
5
votes
3answers
904 views

Many-to-Too-Many Relationship

I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question): tblBook: ID, Title tblPerson: ID, Name Note that the tblPerson table contains ...
1
vote
1answer
51 views

Query from database

I have a table named mip. The table has primary key id, a foreign key product_id and a column usage_type. product_id is a primary key id to a table named product. It is one to many relationship from ...
1
vote
1answer
129 views

Stored procedures and network congestion

When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it ...
1
vote
2answers
99 views

Easy SQL, embedded select

I have a table of zip codes with latitudes / longitudes. Unfortunately it doesn't seem to have a few zip codes. I would like to try to approximate the latitude / longitude based on other zip codes ...
2
votes
1answer
3k views

Oracle error “too many values”, must be solved with nested SELECTs

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs. A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
4
votes
1answer
809 views

Does UPDATE INDEXES work for local indexes?

I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed): CREATE TABLE table1 (col1 INT, col2 INT) PARTITION BY LIST(col1) ( PARTITION p1 VALUES(1), PARTITION ...
3
votes
1answer
112 views

query discrepancies on very large postgres table

I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem. these two queries although similar are taking massively ...
1
vote
1answer
399 views

How do I query based on percentages of a subset of values?

I have a table with a column named Type that can have some set of values. For instance, A, B, C, D, etc. Behind the scenes this is mapped to an Enum. I need to query this table to get a percentage ...
2
votes
1answer
365 views

Recovering from a stuck query

I have a big DELETE query (deleting 20M rows from a 600M row table with condition according to indexed column) that is stuck - running already for 17hrs. Because it's an InnoDB table, killing it will ...
1
vote
1answer
146 views

Does MySQL compress the stored data?

When I export a database in MySQL, it gives me a .sql file. Let's say this .sql file is 80 MB. Are they compressed in order to reduce disk space usage? Is that 80MB real physical-storage size?
1
vote
1answer
82 views

sqlite database: advanced SQL or redesign

I've been programming for several years, but I'm pretty new to SQL(ite). I'm designing a database for a sort of a bookkeeping program. What I've got so far: orders (o INTEGER PRIMARY KEY, ...
0
votes
1answer
270 views

how to pass url value to index [closed]

How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next ...
0
votes
1answer
1k views

Access query to select multiple records with matching field

I have a table with a list of people ([FirstName], [Surname], [Age]). Some of these people are in the same family. I'd like to return a list of people who have another family member in the table (same ...
5
votes
2answers
2k views

Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

E.g. consider the SQL query: SELECT A.[Name], ROW_NUMBER() OVER(ORDER BY A.[Name] ASC) FROM [FooTable] AS A Here I observe the results being returned sorted by A.[Name]. If I change the ...
2
votes
3answers
849 views

Is there a way to tell SQLCMD not to output rows to text or a file?

I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows ...
2
votes
1answer
507 views

Populating an empty table using loops, conditionals, and accessing multiple tables

This question pertains to a MySQL database, although, in the end, this may be database neutral. I am trying to populate an empty table (let's call it NewTable) that I created. The data population is ...
4
votes
5answers
277 views

How much does an index need to narrow the results of a search in order to be useful?

How much does an index need to narrow the results of a search in order to be useful in speeding up queries? Some examples all across the spectrum: A column for storing true/false values obviously ...
1
vote
0answers
136 views

Pass in dynamic value to Oracle job scheduler

How does one pass a dynamic value into the oracle job scheduler when the underlying command is a stored procedure? example procedure: create procedure foo (bar varchar2) ... Job creation in ...
1
vote
2answers
113 views

How can I append a column to an inner SQL statement?

I have two tables that have the same structure except one has an additional column that should be auto-incremented. Sequence and trigger are defined. I want to copy all data from one table to ...
3
votes
2answers
690 views

Blocking drop database command

I created a MySQL database and created a new user dedicated to that database with all permissions. The new user then proceeded of dropping the database in addition to all tables. I need to block that ...
1
vote
2answers
209 views

SQL query based on foreign key relationship

I have two tables, a Project and an Action: Project has the following fields: project_name Action has the following fields: action_name project status There is a one-to-many relationship ...
1
vote
0answers
134 views

SQL relationships, one-to-one, many-to-many etc [closed]

I'm trying to understand all relationship types that can be created in SQL. So far I only get the "belongs-to" one - Example: a table with "Blog post" records, which contains an author_id column that ...
2
votes
0answers
365 views

MySQL database structure: more columns or more rows [closed]

I collect how people tag topics with categories in table like: ID | topic_id | votes_Category_1 | votes_Category_2 |.......... | votes_Category_12 This table holds 1 row per topic and dynamically ...
2
votes
1answer
46 views

In real terms, how much effect does a bad fillfactor have on performance?

There's a storm brewing, or maybe I should say firestorm. A customer has recently implemented SQL Server 2012 Always On but that's not the most important bit. A list of fires to be put out has been ...
5
votes
4answers
3k views

SQL Cluster without a shared disk. Possible?

Is it possible to create a two node cluster using two basic network servers that only have local disks? One will act as an idle standby server. Without knowing too much about clustering I'm not ...
1
vote
2answers
259 views

correct security and user setup for SQL Server database

I am currently on setting a sql server database on a database server. A front end web application running off a web server communicates to the database. What is the best approach for setting up users ...
1
vote
3answers
2k views

Putting SQL Server data files in a different partition

I have a SQL Server and I put the data files (the .mdf) on a different partition. Windows runs from C: and the data files and logs are on D: Is it advantageous or disadvantageous to separate the ...
2
votes
0answers
100 views

QBE Query By Example. How does it handle OR conditions?

I've been looking at implementing QBE into an application but need more information on how more complex queries work. How does QBE handle "OR" conditions? For example when searching a telephone book ...
4
votes
1answer
142 views

GROUP BY on a dependent field

Let's say I have a SELECT statement as follows: SELECT ..., field1 FROM ... GROUP BY field1 Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent ...
1
vote
2answers
238 views

SQL Server Monitoring Tools [closed]

Can anyone recommend a good SQL server Monitoring Tool? One which will provide info on, server space usage, CPU usage , expensive queries run etc.
4
votes
1answer
219 views

DB Master Key Encrypted By *Correct* Service Master Key?

My client has frequently run process where we move databases from a prod environment to lower environments (refresh QA with prod database, etc). These DBs have database master keys on them. We have ...
1
vote
0answers
169 views

Multi-Table Join to Single Destination

I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL. To ...
1
vote
1answer
92 views

Getting the page name from a web URL in Oracle

I have a list of page URL's in my database table under the column heading URL. There are many different prefixes on each page name for example: Test1/Admin/Page7.asp Test1/One/Page3.asp ...
1
vote
1answer
69 views

System test Database user management

We are in development stage and to be able to give the developers an integration test environment, we need to give them access to the database, they can share and test their code in the system ...
0
votes
3answers
1k views

ORA-01562: failed to extend rollback segment number 11

I´m trying to execute this query into a SP DELETE FROM table SC WHERE SC.evaluationMonth= 'Some month'; COMMIT; But for this month there are something like 10 million records. So I get the ...
1
vote
1answer
39 views

Order questions depending on answer votes

I'm really curious about how to create an SQL query to order questions depending the votes_up and votes_down. (The votes can be the sum of both, votes_up + votes_down.) Question has_many answers ...