Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead. Answers to questions tagged with SQL should use ISO/IEC standard SQL.
671,302
questions
535
votes
8
answers
446k
views
When to use "ON UPDATE CASCADE"
I use ON DELETE CASCADE regularly but I never use ON UPDATE CASCADE as I am not so sure in what situation it will be useful.
For the sake of discussion let see some code.
CREATE TABLE parent (
id ...
- 25.5k
534
votes
14
answers
552k
views
Restrict results to top N rows per group
The following query:
SELECT
year, id, rate
FROM h
WHERE year BETWEEN 2000 AND 2009
ORDER BY id, rate DESC
yields:
year | id | rate
2006 | p01 | 8.0
2003 | p01 | 7.4
2008 | p01 | 6.8
2001 | ...
- 10.6k
533
votes
35
answers
574k
views
How to print a query string with parameter values when using Hibernate
Is it possible in Hibernate to print generated SQL queries with real values instead of question marks?
How would you suggest to print queries with real values if it is not possible with Hibernate API?...
- 15.4k
533
votes
46
answers
954k
views
How do I split a delimited string so I can access individual items?
Using SQL Server, how do I split a string so I can access item x?
Take a string "Hello John Smith". How can I split the string by space and access the item at index 1 which should return "John"?
- 75k
532
votes
12
answers
476k
views
Postgres DB Size Command
What is the command to find the size of all the databases?
I am able to find the size of a specific database by using following command:
select pg_database_size('databaseName');
- 6,186
532
votes
33
answers
1.3m
views
Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF
I have the below error when I execute the following script. What is the error about, and how it can be resolved?
Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)
...
Jaison
529
votes
13
answers
648k
views
What are DDL and DML?
I have heard the terms DDL and DML in reference to databases, but I don't understand what they are.
What are they and how do they relate to SQL?
- 6,739
527
votes
24
answers
872k
views
SQL MAX of multiple columns?
How do you return 1 value per row of the max of several columns:
TableName
[Number, Date1, Date2, Date3, Cost]
I need to return something like this:
[Number, Most_Recent_Date, Cost]
Query?
- 10.5k
524
votes
18
answers
1.4m
views
How to create id with AUTO_INCREMENT on Oracle?
It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.
How can I create a column that behaves like auto increment in Oracle 11g?
- 7,417
522
votes
13
answers
767k
views
516
votes
9
answers
610k
views
how can I Update top 100 records in sql server
I want to update the top 100 records in SQL Server. I have a table T1 with fields F1 and F2. T1 has 200 records. I want to update the F1 field in the top 100 records. How can I update based on TOP ...
- 6,419
515
votes
3
answers
1.0m
views
Nested select statement in SQL Server
Why doesn't the following work?
SELECT name FROM (SELECT name FROM agentinformation)
I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as
...
- 11k
515
votes
17
answers
778k
views
How to convert java.util.Date to java.sql.Date?
I am trying to use a java.util.Date as input and then creating a query with it - so I need a java.sql.Date.
I was surprised to find that it couldn't do the conversion implicitly or explicitly - but ...
- 12.7k
513
votes
20
answers
606k
views
MySQL error: key specification without a key length
I have a table with a primary key that is a varchar(255). Some cases have arisen where 255 characters isn't enough. I tried changing the field to a text, but I get the following error:
BLOB/TEXT ...
- 41.9k
509
votes
18
answers
736k
views
Error related to only_full_group_by when executing a query in MySql
I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySQL it works ...
- 5,365
508
votes
22
answers
653k
views
Difference between EXISTS and IN in SQL?
What is the difference between the EXISTS and IN clause in SQL?
When should we use EXISTS, and when should we use IN?
- 6,193
507
votes
4
answers
456k
views
What is the meaning of the prefix N in T-SQL statements and when should I use it?
I have seen prefix N in some insert T-SQL queries. Many people have used N before inserting the value in a table.
I searched, but I was not able to understand what is the purpose of including the N ...
- 9,413
505
votes
8
answers
896k
views
How Stuff and 'For Xml Path' work in SQL Server?
Table is:
Id
Name
1
aaa
1
bbb
1
ccc
1
ddd
1
eee
Required output:
Id
abc
1
aaa,bbb,ccc,ddd,eee
Query:
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH (...
- 5,899
503
votes
8
answers
2.0m
views
How do I query for all dates greater than a certain date in SQL Server?
I'm trying:
SELECT *
FROM dbo.March2010 A
WHERE A.Date >= 2010-04-01;
A.Date looks like: 2010-03-04 00:00:00.000
However, this is not working.
Can anyone provide a reference for why?
- 23.7k
500
votes
11
answers
525k
views
Exporting data In SQL Server as INSERT INTO
I am using SQL Server 2008 Management Studio and have a table I want to migrate to a different db server.
Is there any option to export the data as an insert into SQL script??
- 24.9k
500
votes
13
answers
706k
views
Altering a column to be nullable
I want to alter a table column to be nullable. I have used:
ALTER TABLE Merchant_Pending_Functions Modify NumberOfLocations NULL
This gives an error at Modify. What is the correct syntax?
- 7,446
499
votes
13
answers
387k
views
Best way to select random rows PostgreSQL
I want a random selection of rows in PostgreSQL, I tried this:
select * from table where random() < 0.01;
But some other recommend this:
select * from table order by random() limit 1000;
I have a ...
- 8,132
496
votes
5
answers
1.4m
views
Efficiently convert rows to columns in sql server
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records.
This is my example:
Id
Value
ColumnName
1
John
...
- 5,003
495
votes
12
answers
231k
views
Explicit vs implicit SQL joins
Is there any efficiency difference in an explicit vs implicit inner join?
For example:
SELECT * FROM
table a INNER JOIN table b
ON a.id = b.id;
vs.
SELECT a.*, b.*
FROM table a, table b
WHERE a.id =...
- 51.9k
494
votes
23
answers
869k
views
How to use GROUP BY to concatenate strings in SQL Server?
How do I get:
id Name Value
1 A 4
1 B 8
2 C 9
to
id Column
1 A:4, B:8
2 C:9
- 15.6k
494
votes
34
answers
1.1m
views
How to select the nth row in a SQL database table?
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...
- 20.5k
492
votes
14
answers
620k
views
How to log PostgreSQL queries?
How to enable logging of all SQL executed by PostgreSQL 8.3?
Edited (more info)
I changed these lines :
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
...
- 12.6k
489
votes
19
answers
1.1m
views
MySQL: Select DISTINCT / UNIQUE, but return all columns?
SELECT DISTINCT field1, field2, field3, ......
FROM table;
I am trying to accomplish the following SQL statement, but I want it to return all columns.
Is this possible?
Something like this:
SELECT ...
- 77.1k
488
votes
13
answers
1.2m
views
How to insert a value that contains an apostrophe (single quote)?
What is the correct SQL syntax to insert a value with an apostrophe in it?
Insert into Person
(First, Last)
Values
'Joe',
'O'Brien'
I keep getting an error as I think the apostrophe after the ...
- 193k
488
votes
9
answers
219k
views
Postgres and Indexes on Foreign Keys and Primary Keys
Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table?
- 14k
486
votes
18
answers
690k
views
Rename a column in MySQL
I am trying to rename a column in MySQL community server 5.5.27 using this SQL expression:
ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;
I also tried
ALTER TABLE table_name ...
- 5,068
485
votes
11
answers
691k
views
How to select rows with no matching entry in another table?
I'm doing some maintenance work on a database application and I've discovered that, joy of joys, even though values from one table are being used in the style of foreign keys, there's no foreign key ...
- 8,195
484
votes
12
answers
634k
views
How can I get multiple counts with one SQL query?
I am wondering how to write this query.
I know this actual syntax is bogus, but it will help you understand what I want.
I need it in this format, because it is part of a much bigger query.
SELECT ...
- 4,961
483
votes
10
answers
605k
views
How to delete a record in Django models?
I want to delete a particular record like:
delete from table_name where id = 1;
How can I do this in a django model?
- 11.4k
482
votes
18
answers
688k
views
What is the syntax for an inner join in LINQ to SQL?
I'm writing a LINQ to SQL statement, and I'm after the standard syntax for a normal inner join with an ON clause in C#.
How do you represent the following in LINQ to SQL:
select DealerContact.*
from ...
- 33.9k
481
votes
14
answers
430k
views
How to concatenate strings of a string field in a PostgreSQL 'group by' query?
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:
ID
COMPANY_ID
EMPLOYEE
1
1
Anna
2
1
Bill
3
2
Carol
4
2
Dave
and I wanted to ...
- 7,080
478
votes
17
answers
797k
views
Declare a variable in a PostgreSQL query
How do I declare a variable for use in a PostgreSQL 8.3 query?
In MS SQL Server I can do this:
DECLARE @myvar INT;
SET @myvar = 5/
SELECT * FROM somewhere WHERE something = @myvar;
How do I do ...
- 60.6k
478
votes
13
answers
410k
views
SQL join: selecting the last records in a one-to-many relationship
Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What ...
- 7,817
477
votes
2
answers
539k
views
Delete all Duplicate Rows except for One in MySQL? [duplicate]
How would I delete all duplicate data from a MySQL Table?
For example, with the following data:
SELECT * FROM names;
+----+--------+
| id | name |
+----+--------+
| 1 | google |
| 2 | yahoo |
|...
- 23.6k
471
votes
7
answers
779k
views
must appear in the GROUP BY clause or be used in an aggregate function
I have a table that looks like this caller 'makerar'
cname
wmname
avg
canada
zoro
2.0000000000000000
spain
luffy
1.00000000000000000000
spain
usopp
5.0000000000000000
And I want to select the ...
- 5,039
469
votes
25
answers
386k
views
Is there any difference between GROUP BY and DISTINCT?
The following two queries each give the same result:
SELECT column FROM table GROUP BY column
SELECT DISTINCT column FROM table
Is there anything different in the way these commands are processed, or ...
- 19.6k
466
votes
6
answers
924k
views
SQL query return data from multiple tables
I would like to know the following:
how to get data from multiple tables in my database?
what types of methods are there to do this?
what are joins and unions and how are they different from one ...
465
votes
31
answers
1.2m
views
SQL statement to get column type
Is there a SQL statement that can return the type of a column in a table?
- 7,563
465
votes
4
answers
914k
views
How to get the top 10 values in postgresql?
I have simple question:
I have a postgresql table: Scores(score integer).
How would I get the highest 10 scores the fastest?
UPDATE:
I will be doing this query multiple times and am aiming for the ...
- 6,623
463
votes
19
answers
1.2m
views
How to avoid the "divide by zero" error in SQL?
I have this error message:
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
What is the best way to write SQL code so that I will never see this error message again?
I could ...
- 13.5k
463
votes
26
answers
321k
views
How can I see the raw SQL queries Django is running?
Is there a way to show the SQL that Django is running while performing a query?
- 78.5k
462
votes
13
answers
755k
views
SQL: difference between PARTITION BY and GROUP BY
I've been using GROUP BY for all types of aggregate queries over the years. Recently, I've been reverse-engineering some code that uses PARTITION BY to perform aggregations.
In reading through all ...
- 11.8k
461
votes
11
answers
641k
views
Can a foreign key be NULL and/or duplicate?
Please clarify two things for me:
Can a Foreign key be NULL?
Can a Foreign key be duplicate?
As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to ...
- 21.8k
459
votes
1
answer
248k
views
How to change a PG column to NULLABLE TRUE?
How can I accomplish this using Postgres? I've tried the code below but it doesn't work:
ALTER TABLE mytable ALTER COLUMN mycolumn BIGINT NULL;
- 8,163
458
votes
7
answers
628k
views
SQL Server SELECT into existing table
I am trying to select some fields from one table and insert them into an existing table from a stored procedure. Here is what I am trying:
SELECT col1, col2
INTO dbo.TableTwo
FROM dbo.TableOne
...
- 4,777