Learn Structured Query Language
SQL Tutorial of w3resource aims to meet the need of a beginner to learn SQL without any prior experience. Having said that, it by no means superficial. On the contrary, it offers all the material one needs to successfully build a database and write SQL queries ranging from a one liner like "SELECT * FROM table_name" to fairly non-trivial ones taking multiple tables in the account.
At the outset, we need to tell you, this SQL Tutorial adheres to SQL:2003 standard of ANSI. This is important because if you are learning something as important as SQL, there is no point learning if you don't know which version or standard you are studying.
We have diligently added as many features as possible while creating this SQL Tutorial. There is Syntax, Query, Explanation of a query and pictorial presentation to help you understand concepts better. On top of these, we have hundreds of Exercises with an online editor, quizzes. So you may practice concepts and queries without leaving your browser.
In June 1970 Dr. E. F. Codd published the paper, "A Relational Model of Data for Large Shared Data Banks" in the Association of Computer Machinery (ACM) journal. Codd's model is now accepted as the definitive model for relational database management systems (RDBMS).
Using Codd's model the language, Structured English Query Language (SEQUEL) was developed by IBM Corporation in San Jose Research Center. The language was first called SEQUEL but Official pronunciation of SQL is ESS QUE ELL.
In 1979 Oracle introduced the first commercially available implementation of SQL. Later other players join in the race. Today, SQL is accepted as the standard RDBMS language.
Note: If you are not habituated with database management system your can learn from here.
What is SQL?
SQL stands for Structured Query Language and it is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. It is used for managing data in relational database management system which stores data in the form of tables and relationship between data is also stored in the form of tables. SQL statements are used to retrieve and update data in a database.
SQL works with database programs like DB2, MySQL, PostgreSQL, Oracle, SQLite, SQL Server, Sybase, MS Access and much more. There are many different versions of the SQL language, but to be in compliance with the ANSI standard, they support the major keyword such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others. The following picture shows the communicating with an RDBMS using SQL.
History of SQL
Here is the year wise development history :
- 1970 E.F. Codd publishes Definition of Relational Model
- 1975 Initial version of SQL Implemented (D. Chamberlin)
- IBM experimental version: System R (1977) w/revised SQL
- IBM commercial versions: SQL/DS and DB2 (the early 1980s)
- Oracle introduces commercial version before IBM's SQL/DS
- INGRES 1981 & 85
- ShareBase 1982 & 86
- Data General (1984)
- Sybase (1986)
- by 1992 over 100 SQL products
SQL Standard Revisions
- SEQUEL/Original SQL - 1974
- SQL/86 - Ratification and acceptance of a formal SQL standard by ANSI (American National Standards Institute) and ISO (International Standards Organization).
- SQL/92 - Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.
- SQL/99 - Added regular expression matching, recursive queries (e.g. transitive closure), triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features (e.g. structured types).
- SQL/2003 - Introduced XML-related features (SQL/XML), Window functions, Auto generation.
- SQL/2006 - Lots of XML Support for XQuery, an XML-SQL interface standard.
- SQL/2008 - Adds INSTEAD OF triggers, TRUNCATE statement.
Constructs of SQL
Here is list of the key elements of SQL along with a brief description:
- Queries : Retrieves data against some criteria.
- Statements : Controls transactions, program flow, connections, sessions, or diagnostics.
- Clauses : Components of Queries and Statements.
- Expressions : Combination of symbols and operators and a key part of the SQL statements.
- Predicates : Specifies conditions.
Some Key terms of SQL 2003
To know the key terms of SQL 2003, you should know the statement classes of both SQL 92 AND SQL 2003, since both are used to refer SQL features and statements.
In SQL 92, SQL statements are grouped into following categories:
- Data manipulation : The Data Manipulation Language (DML) is the subset of SQL which is used to add, update and delete data.
- Data definition : The Data Definition Language (DDL) is used to manage table and index structure. CREATE, ALTER, RENAME, DROP and TRUNCATE statements are to name a few data definition elements.
- Data control : The Data Control Language (DCL) is used to set permissions to users and groups of users whether they can access and manipulate data.
- Transaction : A transaction contains a number of SQL statements. After the transaction begins, all of the SQL statements are executed and at the end of the transaction, permanent changes are made in the associated tables.
- Procedure : Using a stored procedure, a method is created which contains source code for performing repetitive tasks.
In SQL 2003 statements are grouped into seven categories which are called classes. See the following table :
|SQL data statements||SELECT, INSERT, UPDATE, DELETE|
|SQL connection statements||CONNECT, DISCONNECT|
|SQL schema statements||ALTER, CREATE, DROP|
|SQL control statements||CALL, RETURN|
|SQL diagnostic statements||GET DIAGNOSTICS|
|SQL session statements||SET CONSTRAINT|
|SQL transaction statements||COMMIT, ROLLBACK|
PL-SQL, TSQL and PL/pgSQL
- PL/SQL - Procedural Language/Structured Query Language ( PL/SQL) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database.
- TSQL - Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL.
- PL/pgSQL - Procedural Language/PostgreSQL(PL/pgSQL) is a procedural programming language supported by the PostgreSQL.
Database and Table Manipulation
|CREATE DATABASE database_name||Create a database|
|DROP DATABASE database_name||Delete a database|
|CREATE TABLE "table_name" ("column_1" "column_1_data_type", "column_2" "column_2_data_type", ... )||Create a table in a database.|
|ALTER TABLE table_name ADD column_name column_datatype||Add columns in an existing table.|
|ALTER TABLE table_name DDROP column_name column_datatype||Delete columns in an existing table.|
|DROP TABLE table_name||Delete a table.|
|CHARACTER(n)||Character string, fixed length n.|
|CHARACTER VARYING(n) or
|Variable length character string, maximum length n.|
|BINARY(n)||Fixed-length binary string, maximum length n.|
|BOOLEAN||Stores truth values - either TRUE or FALSE.|
|BINARY VARYING(n) or
|Variable length binary string, maximum length n.|
|INTEGER(p)||Integer numerical, precision p.|
|SMALLINT||Integer numerical precision 5.|
|INTEGER||Integer numerical, precision 10.|
|BIGINT||Integer numerical, precision 19.|
|DECIMAL(p, s)||Exact numerical, precision p, scale s.|
|NUMERIC(p, s)||Exact numerical,
precision p, scale s.
(Same as DECIMAL ).
|FLOAT(p)||Approximate numerical, mantissa precision p.|
mantissa precision 7.
mantissa precision 16.
|DOUBLE PRECISION||Approximate numerical
mantissa precision 16.
|Composed of a number of integer fields, representing an absolute point in time, depending on sub-type.|
|INTERVAL||Composed of a number of integer fields, representing a period of time, depending on the type of interval.|
|COLLECTION (ARRAY, MULTISET)||ARRAY(offered in SQL99) is a set-length and ordered the collection of elements.|
|XML||Stores XML data. It can be used wherever a SQL data type is allowed, such as a column of a table.|
|CREATE INDEX index_name ON table_name (column_name_1, column_name_2, ...)||Create a simple index.|
|CREATE UNIQUE INDEX index_name ON table_name (column_name_1, column_name_2, ...)||Create a unique index.|
|DROP INDEX table_name.index_name||Drop a index.|
|SQL Arithmetic Operator||Arithmetic operators are addition(+), subtraction(-), multiplication(*) and division(/). The + and - operators can also be used in date arithmetic.|
|SQL Comparison Operator||A comparison (or relational) operator is a mathematical symbol which is used to compare two values.|
|SQL Assignment operator||In SQL the assignment operator ( = ) assigns a value to a variable or of a column or field of a table.|
|SQL Bitwise Operator||The bitwise operators are & ( Bitwise AND ), | ( Bitwise OR ) and ^ ( Bitwise Exclusive OR or XOR ). The valid datatypes for bitwise operators are BINARY, BIT, INT, SMALLINT, TINYINT, and VARBINARY.|
|SQL Logical Operator||The Logical operators are those that are true or false. The logical operators are AND , OR, NOT, IN, BETWEEN, ANY, ALL, SOME, EXISTS, and LIKE.|
|SQL Unary Operator||The SQL Unary operators perform such an operation which contain only one expression of any of the datatypes in the numeric datatype category.|
Insert, Update and Delete:
|INSERT INTO table_name VALUES (value_1, value_2,....)
INSERT INTO table_name (column1, column2,...) VALUES (value_1, value_2,....)
|Insert new rows into a table.|
|UPDATE table_name SET column_name_1 = new_value_1, column_name_2 = new_value_2 WHERE column_name = some_value||Update one or several columns in rows.|
|DELETE FROM table_name WHERE column_name = some_value||Delete rows in a table.|
SELECT column_name(s) FROM table_name
|Select data from a table.|
|SELECT * FROM table_name||Select all data from a table.|
|SELECT DISTINCT column_name(s) FROM table_name||Select only distinct (different) data from a table.|
|SELECT column_name(s) FROM table_name WHERE column operator value AND column operator value OR column operator value AND (... OR ...) ...||Select only certain data from a table.|
|SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...)||The IN operator may be used if you know the exact value you want to return for at least one of the columns.|
|SELECT column_name(s) FROM table_name ORDER BY row_1, row_2 DESC, row_3 ASC, ...||Select data from a table with sort the rows.|
|SELECT column_1, ..., SUM(group_column_name) FROM table_name GROUP BY group_column_name||The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to individual group.|
|SELECT column_name(s) INTO new_table_name FROM source_table_name WHERE query||Select data from table(S) and insert it into another table.|
|SELECT column_name(s) IN external_database_name FROM source_table_name WHERE query||Select data from table(S) and insert it in another database.|
|Aggregate Function||This function can produce a single value for an entire group or table. Some Aggregate functions are -
|Arithmetic Function||A mathematical function executes a mathematical operation usually based on input values that are provided as arguments, and return a numeric value as the result of the operation.
Some Arithmetic functions are -
|Character Function||A character or string function is a function which takes one or more characters or numbers as parameters and returns a character value. Some Character functions are -
|SQL EQUI JOIN||The SQL EQUI JOIN is a simple SQL join uses the equal sign(=) as the comparison operator for the condition. It has two types - SQL Outer join and SQL Inner join.
SQL INNER JOIN returns all rows from tables where the key record of one table is equal to the key records of another table.
SQL OUTER JOIN returns all rows from one table and only those rows from the secondary table where the joined condition is satisfying i.e. the columns are equal in both tables.
|SQL NON EQUI JOIN||The SQL NON EQUI JOIN is a join uses comparison operator other than the equal sign like >, <, >=, <= with the condition.|
|SQL_Statement_1 UNION SQL_Statement_2||Select all different values from SQL_Statement_1 and SQL_Statement_2|
|SQL_Statement_1 UNION ALL SQL_Statement_2||Select all values from SQL_Statement_1 and SQL_Statement_2|
|CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition||Create a virtual table based on the result-set of a SELECT statement.|
SQL tutorial of w3resource is a comprehensive tutorial to learn SQL. We have followed SQL:2003 standard of ANSI. There are hundreds of examples given in this tutorial. Output are shown with Oracle 10G/MySQL. Often outputs are followed by a pictorial presentation and explanation for better understanding. You will hardly find a vendor neutral SQL tutorial covering SQL in such great detail. Following is a list of the features we have included in our tutorials :
- A simple but thorough description.
- SQL Syntax.
- Description of the Parameters used in the SQL command.
- Sample table with data.
- SQL command.
- Explanation of the SQL command.
- The output of the SQL command.
- Model database.
- Online practice.
- SQL stands for Structured Query Language.
- SQL is easy to learn.
- SQL is an ANSI standard computer language.
- SQL allows us to access a database.
- SQL use to access and manipulate data in various databases like Oracle, Sybase, Microsoft SQL Server, DB2, Access, MySQL, PostgreSQL and other database systems.
- SQL execute queries against a database.
- SQL can insert new records into a database.
- SQL can update records in a database.
- SQL can delete records from a database.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Next: SQL Data Types
SQL: Tips of the Day
Altering a column to be nullable:
Assuming SQL Server-
ALTER TABLE Merchant_Pending_Functions ALTER COLUMN NumberOfLocations INT NULL
Replace INT with your actual datatype.
Database: SQL Server
- Exercises: Weekly Top 12 Most Popular Topics
- Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- SQL Exercises, Practice, Solution - SUBQUERIES
- C Programming Exercises, Practice, Solution : For Loop
- Python Exercises, Practice, Solution
- Python Data Type: List - Exercises, Practice, Solution
- C++ Basic: Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - exercises on Employee Database
- SQL Exercises, Practice, Solution - exercises on Movie Database
- SQL Exercises, Practice, Solution - exercises on Soccer Database
- C Programming Exercises, Practice, Solution : Recursion