Structured Query Language (commonly abbreviated as SQL) is a database Information Retrieval computer language founded in the 1970s, and used to place commands and execute queries on stores of data within a given database structure. 
- SQL-92/99/2003 Reserved Words - overview: http://developer.mimer.se/validator/sql-reserved-words.tml
- BNF Grammars for SQL-92/99/2003: http://savage.net.au/SQL/
- SQL-99 official spec: http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=26196 (costs money)
- SQL-2003: http://www.wiscorp.com/SQL2003Features.pdf
- Web SQL Database (part of "HTML5 family"): http://www.w3.org/TR/webdatabase/
See: SQL Schema
The most common operation in SQL databases is the query, which is performed with the declarative
SELECT retrieves data from a specified table, or multiple related tables, in a database. While often grouped with Data Manipulation Language (DML)  statements, the standard
SELECT query is considered separate from SQL DML, as it has no persistent effects on the data stored in a database. Note that there are some platform-specific variations of
SELECT that can persist their effects in a database, such as the
SELECT INTO syntax that exists in some databases.
SQL queries allow the user to specify a description of the desired result set, but it is left to the devices of the Database Management System (DBMS) to plan, optimize, and perform the physical operations necessary to produce that result set in as efficient a manner as possible. An SQL query includes a list of columns to be included in the final result immediately following the
SELECT keyword. An asterisk ("
*") can also be used as a "wildcard" indicator to specify that all available columns of a table (or multiple tables) are to be returned.
SELECT is the most complex statement in SQL, with several optional keywords and clauses, including:
FROMclause which indicates the source table or tables from which the data is to be retrieved. The
FROMclause can include optional
JOINclauses to join related tables to one another based on user-specified criteria.
WHEREclause includes a comparison predicate, which is used to restrict the number of rows returned by the query. The
WHEREclause is applied before the
GROUP BYclause. The
WHEREclause eliminates all rows from the result set where the comparison predicate does not evaluate to True.
GROUP BYclause is used to combine, or group, rows with related values into elements of a smaller set of rows.
GROUP BYis often used in conjunction with SQL aggregate functions or to eliminate duplicate rows from a result set.
HAVINGclause includes a comparison predicate used to eliminate rows after the
GROUP BYclause is applied to the result set. Because it acts on the results of the
GROUP BYclause, aggregate functions can be used in the
ORDER BYclause is used to identify which columns are used to sort the resulting data, and in which order they should be sorted (options are ascending or descending). The order of rows returned by an SQL query is never guaranteed unless an
ORDER BYclause is specified.
- SQL Functions Guide: http://www.sentex.net/~pkomisar/SQL/4_Functions.html
The following is an example of a
SELECT query that returns a list of expensive books. The query retrieves all rows from the books table in which the price column contains a value greater than 100.00. The result is sorted in ascending order by title. The asterisk (*) in the select list indicates that all columns of the books table should be included in the result set.
SELECT * FROM books WHERE price > 100.00 ORDER BY title;
The example below demonstrates the use of multiple tables in a join, grouping, and aggregation in an SQL query, by returning a list of books and the number of authors associated with each book.
SELECT books.title, count(*) AS Authors FROM books JOIN book_authors ON books.isbn = book_authors.isbn GROUP BY books.title;
Example output might resemble the following:
Title Authors ---------------------- ------- SQL Examples and Guide 3 The Joy of SQL 1 How to use Wikipedia 2 Pitfalls of SQL 1 How SQL Saved my Dog 1
(The underscore character "_" is often used as part of table and column names to separate descriptive words because other punctuation tends to conflict with SQL syntax. For example, a dash "-" would be interpreted as a minus sign.)
Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the books table, the above query could be rewritten in the following form:
SELECT title, count(*) AS Authors FROM books NATURAL JOIN book_authors GROUP BY title;
However, many vendors either don't support this approach, or it requires certain column naming conventions. Thus, it is less common in practice.
Data retrieval is very often combined with data projection when the user is looking for calculated values and not just the verbatim data stored in primitive data types, or when the data needs to be expressed in a form that is different from how it's stored. SQL allows the use of expressions in the select list to project data, as in the following example which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price.
SELECT isbn, title, price, price * 0.06 AS sales_tax FROM books WHERE price > 100.00 ORDER BY title;
First, there are the standard Data Manipulation Language (DML) elements. DML is the subset of the language used to add, update and delete data:
INSERTis used to add rows (formally tuples) to an existing table, for example:
INSERT INTO my_table (field1, field2, field3) VALUES ('test', 'N', NULL);
UPDATEis used to modify the values of a set of existing table rows, eg:
UPDATE my_table SET field1 = 'updated value' WHERE field2 = 'N';
DELETEremoves zero or more existing rows from a table, eg:
DELETE FROM my_table WHERE field2 = 'N';
MERGEis used to combine the data of multiple tables. It is something of a combination of the
UPDATEelements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called an "upsert".
Transactions, if available, can be used to wrap around the DML operations:
BEGIN WORK, or
BEGIN TRANSACTION, depending on SQL dialect) can be used to mark the start of a database transaction, which either completes completely or not at all.
COMMITcauses all data changes in a transaction to be made permanent.
ROLLBACKcauses all data changes since the last
ROLLBACKto be discarded, so that the state of the data is "rolled back" to the way it was prior to those changes being requested.
COMMIT statement has been executed, the changes cannot be rolled back. In other words, its meaningless to have
ROLLBACK executed after
COMMIT statement and vice versa.
ROLLBACK interact with areas such as transaction control and locking. Strictly, both terminate any open transaction and release any locks held on data. In the absence of a
START TRANSACTION or similar statement, the semantics of SQL are implementation-dependent.
Example: A classic bank transfer of funds transaction.
START TRANSACTION; UPDATE ACCOUNTS SET AMOUNT=AMOUNT-200 WHERE ACCOUNT_NUMBER=1234; UPDATE ACCOUNTS SET AMOUNT=AMOUNT+200 WHERE ACCOUNT_NUMBER=2345; IF ERRORS=0 COMMIT; IF ERRORS<>0 ROLLBACK;
The second group of keywords is the Data Definition Language (DDL). DDL allows the user to define new tables and associated elements. Most commercial SQL databases have proprietary extensions in their DDL, which allow control over nonstandard features of the database system.
The most basic items of DDL are the
CREATEcauses an object (a table, for example) to be created within the database.
DROPcauses an existing object within the database to be deleted, usually irretrievably.
TRUNCATEdeletes all data from a table (non-standard, but common SQL statement).
ALTERstatement permits the user to modify an existing object in various ways -- for example, adding a column to an existing table.
CREATE TABLE my_table ( my_field1 INT, my_field2 VARCHAR (50), my_field3 DATE NOT NULL, PRIMARY KEY (my_field1, my_field2) );
The third group of SQL keywords is the Data Control Language (DCL). DCL handles the authorization aspects of data and permits the user to control who has access to see or manipulate data within the database. Its two main keywords are:
GRANTauthorizes one or more users to perform an operation or a set of operations on an object.
REVOKEremoves or restricts the capability of a user to perform an operation or a set of operations.
GRANT SELECT, UPDATE ON my_table TO some_user, another_user
- To BLOB or not to BLOB, that is the question -- Whether to store string in BLOB, or CHAR, or VARCHAR ?: http://www.volny.cz/iprenosil/interbase/ip_ib_strings.htm
- Oracle 11g -- PIVOT - Present information in a spreadsheet-type crosstab report from any relational table: http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html
- Microsoft SQL Server -- PIVOT & UNPIVOT: http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
- PIVOT and UNPIVOT in Sql Server explained: http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/
- Simple Way To Use Pivot In SQL Query: http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
- How to Use SQL PIVOT To Compare Two Tables in Your Database: http://java.dzone.com/articles/how-use-sql-pivot-compare-two
- The Awesome PostgreSQL 9.4 / SQL:2003 FILTER Clause for Aggregate Functions: http://blog.jooq.org/2014/12/30/the-awesome-postgresql-9-4-sql2003-filter-clause-for-aggregate-functions/
- ANSI-standard SQL supports double dash,
--, as a single line comment identifier (some extensions also support curly brackets or C style
/* comments */for multi-line comments).
SELECT * FROM inventory -- Retrieve everything from inventory table
- SQL Fiddle: http://sqlfiddle.com/ (real-time SQL editor/tester)
- Online SQL table building tool: http://gaesql.appspot.com/ (based on wwwsqldesigner)
- Instant (online) SQL Formatter: http://www.dpriver.com/pp/sqlformat.htm
- phpMyAdmin: http://www.phpmyadmin.net
- IDE one: http://ideone.com/
- wwwsqldesigner -- Visual web-based SQL modelling tool (supports multiple DBMS): http://code.google.com/p/wwwsqldesigner/ | wwwsqldesigner - demo
- SQL (schema) Validator: http://developer.mimer.com/validator/
- Visual Studio - Object Relational Designer (O/R Designer): http://msdn.microsoft.com/en-us/library/bb384429.aspx
- Comparison of different SQL implementations: http://troels.arvin.dk/db/rdbms/
- 10 Frequently asked SQL Query Interview Questions: http://java67.blogspot.ca/2013/04/10-frequently-asked-sql-query-interview-questions-answers-database.html
- 10 SQL Tricks That You Didn’t Think Were Possible: http://blog.jooq.org/2016/04/25/10-sql-tricks-that-you-didnt-think-were-possible/
- SQL Antipatterns (BOOK): https://pragprog.com/book/bksqla/sql-antipatterns
- SQL Performance ExplainED: https://winand.at/developers#sql-performance-explained
- A Guide to SQL Naming Conventions: http://https://blog.jooq.org/2019/10/29/a-guide-to-sql-naming-conventions/
- SQL Syntax guide: http://www.mckoi.com/database/SQLSyntax.html
- SQL -- INSERT Statement: http://www.techonthenet.com/sql/insert.php
- Common MySQL Queries: www.artfulsoftware.com/infotree/queries.php
- How to find missing values in a sequence with SQL: http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/
- SQL technique - VIEWs & INDEXes: http://www.tomjewett.com/dbdesign/dbdesign.php?page=views.php
- Let's Create... Our Own SQL Editor: http://netbeans.dzone.com/news/lets-create-our-own-sql-editor
- 10 Common Mistakes Java Developers Make when Writing SQL: http://java.dzone.com/articles/10-common-mistakes-java
- Domain Logic and SQL: http://martinfowler.com/articles/dblogic.html
- Have You Ever Wondered About the Difference Between NOT NULL and DEFAULT?: http://java.dzone.com/articles/have-you-ever-wondered-about
- How to Clone an SQL Record: http://www.av8n.com/computer/htm/clone-sql-record.htm#sec-nontrivial
- How to Remove Duplicate Rows in SQL Server 2008 using the "HAVING" function: java.dzone.com/articles/how-remove-duplicate-rows-sql
- 10 Frequently asked SQL Query Interview Questions (with possible Answers): http://java67.blogspot.sg/2013/04/10-frequently-asked-sql-query-interview-questions-answers-database.html
- Copy Table With Data From One Database to Another in SQL Server 2012: http://www.c-sharpcorner.com/UploadFile/rohatash/copy-table-with-data-from-one-database-to-another-in-sql-ser/
- How to Generate a Script (SQL Server Management Studio): https://technet.microsoft.com/en-us/library/ms178078(v=sql.105).aspx
- MS-SQL Create Primary Keys: https://msdn.microsoft.com/en-us/library/ms189039.aspx
- How to convert INT column to IDENTITY in the MS SQL Server: http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx
- How to iterate through a result set by using Transact-SQL in MS-SQL Server: https://support.microsoft.com/en-us/kb/111401
- Update column to random string (for assigned PW): http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111592
- Fastest way to remove non-numeric characters from a VARCHAR in SQL Server: http://stackoverflow.com/a/6529463
- T-SQL -- Removing all non-Numeric Characters from a String: http://social.technet.microsoft.com/wiki/contents/articles/18735.t-sql-removing-all-non-numeric-characters-from-a-string.aspx
- T-SQL trim   (and other non-alphanumeric characters): http://stackoverflow.com/a/879018
- How to pad a string with leading zeros?: http://www.sqlusa.com/bestpractices/pad/ (many approaches to padding and removing padding from columns)
- SQL Server - How to Left Pad a Number with Zeros: http://www.tech-recipes.com/rx/30469/sql-server-how-to-left-pad-a-number-with-zeros/
- Padding identity column values with zeros: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0624cbcc-7c8f-4386-a7b8-a720f759e3be/padding-identity-column-values-with-zeros?forum=transactsql
- SQL SERVER – Random Number Generator Script – SQL Query: http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/
- SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display: http://blog.sqlauthority.com/2009/03/10/sql-server-pad-ride-side-of-number-with-0-fixed-width-number-display/
- Extracting numbers with SQL Server: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server/
- Generating Random Numbers in SQL Server Without Collisions: https://www.mssqltips.com/sqlservertip/3055/generating-random-numbers-in-sql-server-without-collisions/
- Learn SQL by Calculating Customer Lifetime Value Part 1 -- Setup, Counting and Filtering: https://blog.treasuredata.com/blog/2014/12/05/learn-sql-by-calculating-customer-lifetime-value-part-1/
- Learn SQL by Calculating Customer Lifetime Value Part 2 -- GROUP BY and JOIN: https://blog.treasuredata.com/blog/2015/02/05/learn-sql-by-calculating-customer-lifetime-value-part-2/
- A beginner’s guide to SQL CROSS JOIN: https://vladmihalcea.com/sql-cross-join/
- SQL CROSS APPLY – A Beginner’s Guide: https://vladmihalcea.com/sql-cross-apply/
- Working with the SQL Server command line (sqlcmd): https://www.sqlshack.com/working-sql-server-command-line-sqlcmd/
- How Adding a UNIQUE Constraint on a One-to-One Relationship Helps Performance: https://dzone.com/articles/how-adding-a-unique-constraint-on-a-one-to-one-rel
- SQL SERVER – How to Validate Syntax and Not Execute Statement – An Unexplored Debugging Tip: http://blog.sqlauthority.com/2014/01/20/sql-server-how-to-validate-syntax-and-not-execute-statement-an-unexplored-debugging-tip/
- Using a Simple SQL Server Bulk Insert to View and Validate Data: https://www.mssqltips.com/sqlservertip/3446/using-a-simple-sql-server-bulk-insert-to-view-and-validate-data/
- Use Float or Decimal for Accounting Application Dollar Amount?: http://stackoverflow.com/questions/61872/use-float-or-decimal-for-accounting-application-dollar-amount#66678
- How to store decimal values in SQL Server?: http://stackoverflow.com/questions/813287/how-to-store-decimal-values-in-sql-server#813297
- To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?: https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
- How to format SQL using the command line: https://vladmihalcea.com/format-sql-command-line/ (uses remote/3rd-party API by SQL-Format)
- wikipedia: SQL
- wikipedia: B+ tree
- SQL Murder Mystery: https://mystery.knightlab.com/ (use your SQL skills to find out whodunnit)
- SQL Server 2000 User-Defined Functions: http://msdn.microsoft.com/en-us/library/aa214363(v=sql.80).aspx
- Some SQL servers allow user-defined functions: http://www.sqlteam.com/article/user-defined-functions
- How an ORM works: http://giorgiosironi.blogspot.com/2009/12/how-orm-works.html
- How do I escape single quotes in SQL queries?: http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F
- SQL-2008 now an approved ISO International Standard: http://iablog.sybase.com/paulley/2008/07/sql2008-now-an-approved-iso-international-standard/
- No to SQL? Anti-database movement gains steam: http://www.computerworld.com/s/article/9135086/No_to_SQL_Anti_database_movement_gains_steam_
- 3 Reasons Why It's Okay to Stick With SQL: http://java.dzone.com/articles/3-reasons-why-its-okay-stick
- Yet Another 10 Common Mistakes Java Developer Make When Writing SQL: http://www.dzone.com/links/r/yet_another_10_common_mistakes_java_developers_ma.html
- An Introduction to SQL Server Clusters: http://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/
- Why Your SQL Server Cluster Shouldn’t Be Virtualized: http://www.brentozar.com/archive/2012/09/why-your-sql-server-cluster-shouldnt-be-virtualized/
- "sqlcmd" utility - all parameters available: http://technet.microsoft.com/en-us/library/ms162773(v=sql.105).aspx
- Starting "sqlcmd": http://technet.microsoft.com/en-us/library/ms166559(v=sql.105).aspx
- "sqlcmd" tutorial: http://www.youtube.com/watch?v=gtyZmDBg5D4 (peculiar thing about MS-SQL is that you need to type "go" on line immediately following closing off a Query/StoredProcedure/Trigger, etc)
- Get list of databases from MS-SQL Server: http://stackoverflow.com/questions/147659/get-list-of-databases-from-sql-server
- SQL SERVER – 2005 List All Tables of Database: http://blog.sqlauthority.com/2007/06/26/sql-server-2005-list-all-tables-of-database/
- Track Data Changes (SQL Server): http://msdn.microsoft.com/en-us/library/bb933994.aspx
- Tracking Changes in SQL Server 2012: http://sqlmag.com/sql-server-2012/tracking-changes-sql-server-2012
- Primary key as text: http://stackoverflow.com/questions/15477005/primary-key-as-text
- What's the best practice for primary keys in tables?: http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables
- How do you like your primary keys?: http://stackoverflow.com/questions/404040/how-do-you-like-your-primary-keys
- What is the order of records in a table with a composite primary key: http://stackoverflow.com/questions/13190720/what-is-the-order-of-records-in-a-table-with-a-composite-primary-key/13191075
- SQL join -- WHERE clause vs. ON clause: http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause
- Sqlcmd: Error: near command 'Ë': https://stackoverflow.com/questions/35769154/sqlcmd-error-near-command-Ë
- With the Linux “cat” command, how do I show only certain lines by number: https://unix.stackexchange.com/questions/288521/with-the-linux-cat-command-how-do-i-show-only-certain-lines-by-number
- sqlcmd - Run Transact-SQL Script Files: https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-run-transact-sql-script-files?view=sql-server-2017
- SQL Standards process/info: http://www.jcc.com/sql.htm
- Data Manipulation Language (DML)
- INTO Clause (Transact-SQL), SQL Server 2005 Books Online, Microsoft, 2007: http://msdn2.microsoft.com/en-us/library/ms188029(SQL.90).aspx (accessed 2007-06-17)
- Say No to Venn Diagrams When Explaining JOINs (in SQL): http://dzone.com/articles/say-no-to-venn-diagrams-when-explaining-joins
- Pivot tables in SQL Server. A simple sample: http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx
- Pivoting Without Aggregation: http://sqlmag.com/t-sql/pivoting-without-aggregation
- Convert Rows to columns using 'Pivot' in SQL Server: http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server
- Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*): https://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count#188682