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
252 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 ...
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
3answers
278 views

Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server

SELECT A.Field1 FROM Table1 A INNER JOIN Table1 B ON A.Field2 = B.Field2 AND A.Field3 = B.Field3 AND A.Field4 = B.Field4 AND B.Field1 = "string1" AND B.Field2 = "string2" AND A.Field5 = (SELECT ...
4
votes
2answers
504 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 ...
3
votes
2answers
193 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
5
votes
3answers
1k 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 ...
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 ...
1
vote
1answer
133 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
100 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
855 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 ...
2
votes
3answers
220 views

MySQL data view

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
0
votes
1answer
96 views

Logging of incorrect row in SQL Server Integration Services

I newbie to SQL Server Integration Services and I have a problem: Data loaded from file looks like this: 1 'a' 'b' 2 'a' 'b' 1 'c' 'd' And this rows would be loaded in table like this: CREATE ...
2
votes
1answer
381 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 ...
3
votes
1answer
114 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
85 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
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
1answer
539 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 ...
7
votes
1answer
564 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
2
votes
3answers
896 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 ...
1
vote
0answers
142 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 ...
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
2answers
115 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 ...
0
votes
1answer
274 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 ...
1
vote
2answers
212 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
139 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
378 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 ...
3
votes
2answers
765 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 ...
9
votes
1answer
2k views

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
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 ...
10
votes
5answers
8k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
2
votes
0answers
102 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 ...
1
vote
2answers
265 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 ...
0
votes
1answer
83 views

SQL Query for the following structure

Given the following type of data structure. ID NAME TREELEFT TREERIGHT PARENT 1 XYZ 1 2 0 2 ABC 3 6 0 3 DEF 4 5 ...
9
votes
3answers
449 views

Are these two queries logically equivalent?

Are these two queries logically equivalent? DECLARE @DateTime DATETIME = GETDATE() Query 1 SELECT * FROM MyTable WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7 Query 2 SELECT * FROM ...
5
votes
1answer
163 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
1answer
222 views

GROUP_CONCAT with ORDER BY , but results are not ordered

The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT ...
5
votes
1answer
353 views

Does SQL manage foreign key constraints in terms of Insertion?

Say I have a table tbl1 with data A1 and A2, where A1 is the primary key. Then I have another table tbl2 with data A3 and A1, both together being the primary key, referencing A1 as a foreign key from ...
1
vote
2answers
242 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.
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
93 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
71 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 ...
1
vote
1answer
40 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 ...
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 ...
0
votes
2answers
164 views

Print variable of same datatype

I am looking for to print two variables of same datatype I have something like this declare @one int ,@two int set @one=1 set @two=2 print @one+@two obviously it will give a result 3 but I need ...
1
vote
1answer
273 views

Index strategy for a Queue Table, getting rid of filesort

I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is ...
0
votes
2answers
93 views

Grouping data checking against top values

I've got data in this form: CallNum Value Accepted -------------------------------------- 971374 81.482204444473609 True 971374 83.783551111089764 False 971374 97.936875555547886 ...
0
votes
1answer
63 views

How to add the aliases to the group by clause

In the below query how to add the aliases assignedTOiti, assignedTOasstAdm, assignedTOhlp, closedDate to the group by clause. I am struggling for the past few hours. SELECT ieor.aper07_req_k ...
8
votes
3answers
3k views

SQL Server 2008 datetime index performance bug

We are using SQL Server 2008 R2, and have a very large (100M+ rows) table with a primary id index, and a datetime column with a nonclustered index. We are seeing some highly unusual client/server ...