SQL is a standard language for storing, manipulating and retrieving data in databases.

Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.

SQL is a standard language for accessing and manipulating databases.

Introduction to SQL

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

SQL is a Standard – BUT….

Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

Using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

  • An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
  • To use a server-side scripting language, like PHP or ASP
  • To use SQL to get the data you want
  • To use HTML / CSS to style the page

SQL Syntax

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.

In this tutorial we will use the well-known Northwind sample database (included in MS Access and MS SQL Server).

Keep in Mind That…

  • SQL keywords are NOT case sensitive: select is the same as SELECT

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Some of The Most Important SQL Commands

  • SELECT – extracts data from a database
  • UPDATE – updates data in a database
  • DELETE – deletes data from a database
  • INSERT INTO – inserts new data into a database
  • CREATE DATABASE – creates a new database
  • ALTER DATABASE – modifies a database
  • CREATE TABLE – creates a new table
  • ALTER TABLE – modifies a table
  • DROP TABLE – deletes a table
  • CREATE INDEX – creates an index (search key)
  • DROP INDEX – deletes an index

SQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2, … FROM table_name;

Here, column1, column2, … are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, … FROM table_name;

SQL WHERE Clause

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

WHERE Syntax

SELECT column1, column2, … FROM table_name WHERE condition;

Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!

SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

  • The AND operator displays a record if all the conditions separated by AND are TRUE.
  • The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, … FROM table_name WHERE condition1 AND condition2 AND condition3 …;

OR Syntax

SELECT column1, column2, … FROM table_name WHERE condition1 OR condition2 OR condition3 …;

NOT Syntax

SELECT column1, column2, … FROM table_name WHERE NOT condition;

SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Syntax

SELECT column1, column2, … FROM table_name ORDER BY column1, column2, … ASC|DESC;

SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

INSERT INTO table_name VALUES (value1, value2, value3, …);

SQL NULL Values

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names FROM table_name WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!

SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

SQL TOP, LIMIT or ROWNUM Clause

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;

MySQL Syntax:

SELECT column_name(s) FROM table_name WHERE condition LIMIT number;

Oracle Syntax:

SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;

SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

MIN() Syntax

SELECT MIN(column_name) FROM table_name WHERE condition;

MAX() Syntax

SELECT MAX(column_name) FROM table_name WHERE condition;

SQL COUNT(), AVG() and SUM()Functions

The COUNT() function returns the number of rows that matches a specified criteria.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

COUNT() Syntax

SELECT COUNT(column_name) FROM table_name WHERE condition;

AVG() Syntax

SELECT AVG(column_name) FROM table_name WHERE condition;

SUM() Syntax

SELECT SUM(column_name) FROM table_name WHERE condition;

SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

  • % – The percent sign represents zero, one, or multiple characters
  • _ – The underscore represents a single character

Note: MS Access uses a question mark (?) instead of the underscore (_).

The percent sign and the underscore can also be used in combinations!

LIKE Syntax

SELECT column1, column2, … FROM table_name WHERE columnN LIKE pattern;

Tip: You can also combine any number of conditions using AND or OR operators.

Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

LIKE Operator Description
WHERE CustomerName LIKE ‘a%’ Finds any values that start with “a”
WHERE CustomerName LIKE ‘%a’ Finds any values that end with “a”
WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a_%_%’ Finds any values that start with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ Finds any values that start with “a” and ends with “o”

SQL Wildcards Characters

A wildcard character is used to substitute any other character(s) in a string.

Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

  • % – The percent sign represents zero, one, or multiple characters
  • _ – The underscore represents a single character

Note: MS Access uses a question mark (?) instead of the underscore (_).

In MS Access and SQL Server you can also use:

  • [charlist] – Defines sets and ranges of characters to match
  • [^charlist] or [!charlist] – Defines sets and ranges of characters NOT to match

The wildcards can also be used in combinations!

Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

LIKE Operator Description
WHERE CustomerName LIKE ‘a%’ Finds any values that starts with “a”
WHERE CustomerName LIKE ‘%a’ Finds any values that ends with “a”
WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a_%_%’ Finds any values that starts with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ Finds any values that starts with “a” and ends with “o”

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, …);

or:

SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);

SQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

SQL Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of the query.

Alias Column Syntax

SELECT column_name AS alias_name FROM table_name;

Alias Table Syntax

SELECT column_name(s) FROM table_name AS alias_name;

SQL Joins

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

SQL INNER JOIN  SQL LEFT JOIN  SQL RIGHT JOIN  SQL FULL OUTER JOIN

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

SQL INNER JOIN

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

RIGHT JOIN Syntax

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT JOIN

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!

FULL OUTER JOIN Syntax

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name =table2.column_name;

SQL FULL OUTER JOIN

SQL Self JOIN

A self JOIN is a regular join, but the table is joined with itself.

Self JOIN Syntax

SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;

SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

  • Each SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order

UNION Syntax

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.

SQL GROUP BY Statement

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

GROUP BY Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

HAVING Syntax

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

SQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns true if the subquery returns one or more records.

EXISTS Syntax

SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

SQL ANY and ALL Operators

The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

ANY Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

ALL Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

SQL SELECT INTO Statement

The SELECT INTO statement copies data from one table into a new table.

SELECT INTO Syntax

Copy all columns into a new table:

SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition;

Copy only some columns into a new table:

SELECT column1, column2, column3, … INTO newtable [IN externaldb] FROM oldtable WHERE condition;

The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.

SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

  • INSERT INTO SELECT requires that data types in source and target tables match
  • The existing records in the target table are unaffected

INSERT INTO SELECT Syntax

Copy all columns from one table to another table:

INSERT INTO table2 SELECT * FROM table1 WHERE condition;

Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM table1 WHERE condition;

SQL CASE Statement

The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

CASE Syntax

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;

SQL Stored Procedures for SQL Server

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name AS sql_statement GO;

Execute a Stored Procedure

EXEC procedure_name;

SQL Comments

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.


Single Line Comments

Single line comments start with –.

Any text between — and the end of the line will be ignored (will not be executed).

Multi-line Comments

Multi-line comments start with /* and end with */.

Any text between /* and */ will be ignored.

Tratto dal sito: https://www.w3schools.com/sql/default.asp