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.

Filter by
Sorted by
Tagged with
5049votes
27answers
2.5mviews

What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?
user avatar
4054votes
37answers
4.9mviews

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it ...
user avatar
  • 42.7k
3069votes
43answers
3.3mviews

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?
user avatar
  • 31.8k
2773votes
28answers
2.0mviews

How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; ...
2747votes
7answers
1.0mviews

How does database indexing work? [closed]

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How ...
user avatar
  • 80.1k
2486votes
54answers
4.3mviews

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C ...
user avatar
  • 36k
2238votes
38answers
3.3mviews

Finding duplicate values in a SQL table

It's easy to find duplicates with one field: SELECT email, COUNT(email) FROM users GROUP BY email HAVING COUNT(email) > 1 So if we have a table ID NAME EMAIL 1 John asd@asd.com 2 Sam ...
user avatar
  • 32.2k
2215votes
47answers
2.8mviews

How to concatenate text from multiple rows into a single text string in SQL Server

Consider a database table holding names, with three rows: Peter Paul Mary Is there an easy way to turn this into a single string of Peter, Paul, Mary?
user avatar
  • 26.9k
2023votes
44answers
3.3mviews

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?
user avatar
  • 31.8k
2010votes
3answers
1.9mviews

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?
user avatar
  • 30.7k
1871votes
4answers
3.0mviews

Inserting multiple rows in a single SQL query? [duplicate]

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("...
user avatar
1735votes
32answers
2.3mviews

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into ...
user avatar
  • 20.4k
1732votes
45answers
1.4mviews

How to query MongoDB with "like"

I want to query something with SQL's like query: SELECT * FROM users WHERE name LIKE '%m%' How can I achieve the same in MongoDB? I can't find an operator for like in the documentation.
user avatar
  • 184k
1730votes
20answers
1.5mviews

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My ...
user avatar
1688votes
31answers
4.1mviews

How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
user avatar
1664votes
27answers
3.0mviews

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the ...
user avatar
  • 38.1k
1655votes
41answers
471kviews

Table Naming Dilemma: Singular vs. Plural Names [closed]

Academia has it that table names should be the singular of the entity that they store attributes of. I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table ...
1582votes
22answers
1.5mviews

What is the difference between UNION and UNION ALL?

What is the difference between UNION and UNION ALL?
user avatar
  • 51.1k
1575votes
16answers
1.8mviews

How can I delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER ...
user avatar
  • 16.3k
1507votes
33answers
3.0mviews

Find all tables containing column with specified name - MS SQL Server

Is it possible to query for table names which contain columns being LIKE '%myName%' ?
user avatar
  • 26.3k
1501votes
27answers
1.7mviews

SQL select only rows with max value on a column [duplicate]

I have this table for documents (simplified version here): id rev content 1 1 ... 2 1 ... 1 2 ... 1 3 ... How do I select one row per id and only the greatest rev? With the above data, the ...
user avatar
1494votes
7answers
299kviews

What are the options for storing hierarchical data in a relational database?

Good Overviews Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the ...
1484votes
16answers
1.8mviews

How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the ...
user avatar
  • 18.8k
1476votes
24answers
1.5mviews

How to reset AUTO_INCREMENT in MySQL

How can I reset the AUTO_INCREMENT of a field? I want it to start counting from 1 again.
user avatar
  • 18.3k
1393votes
15answers
796kviews

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, ...
user avatar
  • 30.4k
1342votes
3answers
1.7mviews

Using group by on multiple columns

I understand the point of GROUP BY x. But how does GROUP BY x, y work, and what does it mean?
user avatar
  • 52.6k
1298votes
17answers
1.1mviews

How can I list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?
user avatar
  • 47.5k
1285votes
14answers
1.1mviews

SQL Server - Best way to get identity of inserted row?

What is the best way to get IDENTITY of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each. Can someone please explain ...
user avatar
  • 475k
1243votes
6answers
767kviews

Difference between JOIN and INNER JOIN

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there ...
user avatar
  • 156k
1208votes
14answers
1.9mviews

How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip ...
user avatar

15 30 50 per page
1
2 3 4 5
20828