SQL Tutorial

Last update on October 01 2024 12:45:18 (UTC/GMT +8 hours)

Learn Structured Query Language

The w3resource SQL Tutorial is ideal for SQL beginners, including those without prior experience. Our tutorial offers in-depth guidance, covering everything from basic queries like "SELECT * FROM table_name" to complex operations involving multiple tables. It's a comprehensive resource for both novices and those looking to deepen their SQL knowledge.

From the outset, it's crucial to emphasize that our SQL Tutorial adheres strictly to the ANSI SQL:2003 standard. This standardization is essential because when delving into something as significant as SQL, understanding the version or standard you're working with is paramount.

In creating this SQL tutorial, we've integrated a variety of elements to enhance your learning experience. These elements encompass syntax explanations, query dissections, and visual aids aimed at improving SQL comprehension. Furthermore, we've included 1000+ exercises with solutions and explanations with an online code editor and quizzes. This allows you to conveniently practice SQL concepts and queries right in your web browser for a smooth learning journey.

Contents:

Introduction
What is SQL?
History of SQL
SQL Standard Revisions
Constructs of SQL
Some Key terms of SQL 2003
Database and Table Manipulation
FAQ

Introduction

In June 1970, a groundbreaking paper titled "A Relational Model of Data for Large Shared Data Banks" was published by Dr. E. F. Codd in the Association of Computer Machinery (ACM) journal. Dr. Codd's model has since become widely recognized as the definitive foundation for relational database management systems (RDBMS).

Drawing upon Codd's model, the language known as Structured English Query Language (SEQUEL) was crafted by IBM Corporation at its San Jose Research Center. Initially, it was called SEQUEL, but it's important to note that the official pronunciation of this language is "ESS QUE ELL."

In a significant development in 1979, Oracle introduced the first commercially available implementation of SQL. This marked the beginning of SQL's journey to becoming the standard language for RDBMS. Subsequently, numerous other players entered the arena, solidifying SQL's position as the prevailing language for relational database management systems today..

Note: If you are not habituated with database management system your can learn from here.

What is SQL?

SQL, which stands for Structured Query Language, is a standardized computer language defined by the ANSI (American National Standards Institute). This language is specifically designed for accessing and manipulating database systems. SQL serves as the backbone for managing data within relational database management systems (RDBMS), where data is organized in tabular form, with relationships between data elements also represented in tables.

SQL statements play a pivotal role in database interaction, allowing users to retrieve, update, and manipulate data seamlessly. SQL is compatible with a wide array of database management systems, including but not limited to DB2, MySQL, PostgreSQL, Oracle, SQLite, SQL Server, Sybase, MS Access, and many others. While there exist various SQL language versions, adherence to the ANSI standard ensures compatibility across different systems. Key SQL keywords such as SELECT, UPDATE, DELETE, INSERT, and WHERE are supported universally within these systems, facilitating consistent and standardized database operations.

The image below illustrates interaction with an RDBMS using SQL. It shows how this language serves as a bridge between users and the underlying database, enabling efficient data management and retrieval.

History of SQL

Here is the year wise development history :

1970 E.F. Codd publishes Definition of Relational Model
Fast forward to 1975, and we witness the birth of SQL, thanks to D. Chamberlin.
IBM entered the scene in 1977 with an experimental version called System R, which featured revised SQL.
The early 1980s marked a significant milestone with IBM introducing commercial versions: SQL/DS and DB2, solidifying SQL's presence in the database landscape.
Oracle made a noteworthy move by introducing a commercial version before IBM's SQL/DS, further propelling SQL's prominence.
The years 1981 and 1985 saw the emergence of INGRES, while 1982 and 1986 brought ShareBase into the SQL ecosystem.
In 1984, Data General contributed to the SQL domain.
By 1986, Sybase joined the SQL adopters, expanding the SQL family further.
Remarkably, by the year 1992, SQL had gained immense traction, boasting over 100 SQL products, underlining its widespread adoption and relevance in the world of data management.

SQL Standard Revisions

SQL standards have shaped this language's capabilities and versatility. Here's a concise overview of key SQL standard revisions:

SEQUEL/Original SQL - 1974: SQL began its journey in 1974, initially referred to as SEQUEL, laying the foundation for what would become a pivotal language in data management.
SQL/86 - Formal Standardization: In 1986, SQL took a significant step forward with the ratification and formal acceptance of a standard by ANSI (American National Standards Institute) and ISO (International Standards Organization). This marked a critical moment in SQL's history.
SQL/92 - Major Revision: SQL underwent a major overhaul in 1992, resulting in SQL/92. This revision, known as ISO 9075, introduced Entry Level SQL-92, which was adopted as FIPS 127-2, further cementing SQL's status as a standardized language.
SQL/99 - Expanding Horizons: The SQL/99 standard brought a multitude of enhancements, including regular expression matching, recursive queries (e.g., transitive closure), triggers, support for procedural and control-of-flow statements, non-scalar types, and the incorporation of some object-oriented features (e.g., structured types). SQL evolved to meet diverse data management needs.
SQL/2003 - Embracing XML: In 2003, SQL embraced XML-related features with SQL/XML. This standard also introduced Window functions and auto-generation capabilities, expanding SQL's utility further.
SQL/2006 - XML Integration: SQL/2006 focused extensively on XML support, particularly XQuery, establishing an XML-SQL interface standard that facilitates seamless interaction between SQL and XML data.
SQL/2008 - Added Functionality: The SQL/2008 standard brought INSTEAD OF triggers and the TRUNCATE statement into the SQL arsenal, providing database administrators and developers with more tools for efficient data management.

Constructs of SQL

Here is list of the key elements of SQL along with a brief description:

Queries:Queries are SQL's workhorses. They retrieve data from a database based on specified criteria. Whether you need to fetch specific records, perform calculations, or aggregate data, queries are the means to do so.
Statements: SQL statements are versatile and essential. They control various aspects of database interactions, including transactions, program flow, connections, sessions, and diagnostics. SQL statements play a pivotal role in managing data flow within a database system.
Clauses: Clauses are integral components of queries and statements. They serve as building blocks, allowing you to refine and customize your SQL operations. Clauses enable you to filter data, sort results, and dictate how data should be manipulated.
Expressions: Expressions drive SQL statements. Expressions comprise symbols and operators. Expressions allow you to perform calculations, comparisons, and data transformations. They are the core elements that enable you to derive meaningful insights from your data.
Predicates: Predicates are essential for specifying conditions within SQL operations. Whether it's filtering records with a WHERE clause or defining join conditions, predicates determine which data meets specific criteria. They are crucial for precision in data retrieval and manipulation.

Some Key terms of SQL

Data Manipulation: This class, known as the Data Manipulation Language (DML), focuses on operations that involve adding, updating, and deleting data. DML statements are pivotal for modifying the content of a database, ensuring data accuracy and relevance.
Data Definition: The Data Definition Language (DDL) is instrumental in managing the structure of tables and indexes within a database. Statements such as CREATE, ALTER, RENAME, DROP, and TRUNCATE are fundamental elements of DDL, enabling the creation, modification, and removal of database objects.
Data Control: The Data Control Language (DCL) empowers administrators to set permissions for users and user groups, determining whether they have the authority to access and manipulate data. DCL statements are crucial for enforcing security and access control measures.
Transaction: Transactions play a pivotal role in ensuring the consistency and integrity of a database. A transaction encompasses a series of SQL statements. Once a transaction begins, all these statements are executed, and upon its successful completion, permanent changes are made to the associated tables. Transactions are vital for maintaining data reliability.
Procedure: Procedures, a part of SQL 92, introduce the concept of creating reusable methods that contain source code for executing repetitive tasks. Stored procedures streamline database operations by encapsulating logic and promoting code reusability.

In SQL, statements are grouped into seven categories which are called classes. See the following table :

Class	Example
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

Command	Description
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.

Data Types:

Data Type	Description
CHARACTER(n)	Character string, fixed length n.
CHARACTER VARYING(n) or VARCHAR(n)	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 VARBINARY(n)	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.
REAL	Approximate numerical mantissa precision 7.
FLOAT	Approximate numerical mantissa precision 16.
DOUBLE PRECISION	Approximate numerical mantissa precision 16.
DATE TIME TIMESTAMP	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.

Index Manipulation:

Command	Description
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 Operators:

Operators	Description
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:

Command	Description
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:

Command	Description
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.

Functions:

SQL functions	Description
Aggregate Function	This function can produce a single value for an entire group or table. Some Aggregate functions are - SQL Count function SQL Sum function SQL Avg function SQL Max function SQL Min function
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 - abs() ceil() floor() exp() ln() mod() power() sqrt()
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 - lower() upper() trim() translate()

Joins:

Name	Description
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.

Name

Description

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.

Union:

Command	Description
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

View:

Command	Description
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: Frequently Asked Questions

What is SQL used for?

SQL is used for managing and manipulating data in relational database management systems (RDBMS). It allows users to query, update, and delete data from databases.

What are the main components of an SQL statement?

An SQL statement typically consists of clauses like SELECT, FROM, WHERE, and may also include keywords like JOIN, GROUP BY, and ORDER BY, depending on the specific operation.

What's the difference between SQL and MySQL?

SQL is a language for managing databases, while MySQL is a specific database management system that uses SQL as its query language. MySQL is just one of many RDBMS options available.

What are the different types of SQL statements?

SQL statements can broadly be categorized into Data Manipulation Language (DML) statements (e.g., SELECT, INSERT, UPDATE, DELETE), Data Definition Language (DDL) statements (e.g., CREATE, ALTER, DROP), and Data Control Language (DCL) statements (e.g., GRANT, REVOKE).

What are primary keys and foreign keys in SQL?

A primary key is an unique identifier for a record in a table, ensuring each row has a distinct identity. A foreign key, on the other hand, establishes a link between tables, typically referencing the primary key of another table.

What is a SQL JOIN, and how does it work?

SQL JOIN is used to combine rows from two or more tables based on a related column between them. Common joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

What are SQL transactions and why are they significant?

SQL transactions are sequences of one or more SQL statements treated as a single unit of work. They are important for ensuring data consistency and integrity, allowing you to make multiple changes to a database as a single operation.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Next: SQL Data Types