Start - how to set up SQL database?

Before you start learning SQL queries on your computer you need to download and install database management tools and connect to a database. You will need:

Download Northwind Database - a sample database provided by Microsoft in early 2000.

Next, download and install data management system - Microsoft® SQL Server® Express.

https://www.microsoft.com/en-us/download/details.aspx?id=55994

Download and install a tool for managing your SQL infrastructure - SQL Server Management Studio (SSMS).

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

In the web you will find multiple tutorials if you face any difficulties during the installation process so I won't go deeper into that. Once the tools are installed start SSMS and type server name to connect. In my case it is: localhost\SQLEXPRESS.

To connect to Northwind database press right mouse button on Databases and choose Restore Database.

Next, Choose Device as a Source. Press Add and find your database. Next, click Ok. Once done Northwind database should be visible under Databases folder. Click New Query to start typing your queries.

Due to the limited space just 10 first rows of a table will be shown! Total number of rows will be mentioned under a table.


SELECT

Selects everything (all rows and columns) from Employees table.

SELECT *

FROM Employees

EmployeeID LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address
1 Davolio Nancy Sales Representative Ms. 1948-12-08 00:00:00.000 1992-05-01 00:00:00.000 507 - 20th Ave. E. Apt. 2A
2 Fuller Andrew Vice President, Sales Dr. 1952-02-19 00:00:00.000 1992-08-14 00:00:00.000 908 W. Capital Way
3 Leverling Janet Sales Representative Ms. 1963-08-30 00:00:00.000 1992-04-01 00:00:00.000 722 Moss Bay Blvd.
4 Peacock Margaret Sales Representative Mrs. 1937-09-19 00:00:00.000 1993-05-03 00:00:00.000 4110 Old Redmond Rd.
5 Buchanan Steven Sales Manager Mr. 1955-03-04 00:00:00.000 1993-10-17 00:00:00.000 14 Garrett Hill
6 Suyama Michael Sales Representative Mr. 1963-07-02 00:00:00.000 1993-10-17 00:00:00.000 Coventry House Miner Rd.
7 King Robert Sales Representative Mr. 1960-05-29 00:00:00.000 1994-01-02 00:00:00.000 Edgeham Hollow Winchester Way
8 Callahan Laura Inside Sales Coordinator Ms. 1958-01-09 00:00:00.000 1994-03-05 00:00:00.000 4726 - 11th Ave. N.E.
9 Dodsworth Anne Sales Representative Ms. 1966-01-27 00:00:00.000 1994-11-15 00:00:00.000 7 Houndstooth Rd.
More columns below
City Region PostalCode Country HomePhone Extension Photo Notes ReportsTo PhotoPath
Seattle WA 98122 USA (206) 555-9857 5467 0x151… Education … 2 http://accweb/employ …
Tacoma WA 98401 USA (206) 555-9482 3457 0x151… Andrew … NULL http://accweb/employ …
Kirkland WA 98033 USA (206) 555-3412 3355 0x151… Janet has … 2 http://accweb/employ …
Redmond WA 98052 USA (206) 555-8122 5176 0x151… Margaret … 2 http://accweb/employ …
London NULL SW1 8JR UK (71) 555-4848 3453 0x151… Steven … 2 http://accweb/employ …
London NULL EC2 7JR UK (71) 555-7773 428 0x151… Michael … 5 http://accweb/employ …
London NULL RG1 9SP UK (71) 555-5598 465 0x151… Robert … 5 http://accweb/employ …
Seattle WA 98105 USA (206) 555-1189 2344 0x151… Laura … 2 http://accweb/employ …
London NULL WG2 7LT UK (71) 555-4444 452 0x151… Anne has … 5 http://accweb/employ …
9 rows in total

Limits selection to particular columns from a table.

SELECT EmployeeID, LastName, FirstName

FROM Employees

EmployeeID LastName FirstName
1 Davolio Nancy
2 Fuller Andrew
3 Leverling Janet
4 Peacock Margaret
5 Buchanan Steven
6 Suyama Michael
7 King Robert
8 Callahan Laura
9 Dodsworth Anne
9 rows in total

No such column like '1'. The number appears as a result of the query. There are nine rows which corresponds to number of rows in Employees table.

SELECT 1

FROM Employees

(No column name)
1
1
1
1
1
1
1
1
1
9 rows in total

Column or table names containing spaces should be in brackets.

SELECT UnitPrice

FROM [Order Details]

UnitPrice
14.00
9.80
34.80
18.60
42.40
7.70
42.40
16.80
16.80
15.60
2155 rows in total

Aliases

Gives alias to a column.

SELECT LastName, FirstName, FirstName + ' ' + LastName As FullName

FROM Employees

LastName FirstName FullName
Davolio Nancy Nancy Davolio
Fuller Andrew Andrew Fuller
Leverling Janet Janet Leverling
Peacock Margaret Margaret Peacock
Buchanan Steven Steven Buchanan
Suyama Michael Michael Suyama
King Robert Robert King
Callahan Laura Laura Callahan
Dodsworth Anne Anne Dodsworth
9 rows in total

Gives alias to a table.

SELECT UnitPrice

FROM [Order Details] As OrderDetails

UnitPrice
14.00
9.80
34.80
18.60
42.40
7.70
42.40
16.80
16.80
15.60
2155 rows in total

LEFT

Returns 25 characters counting from the left.

SELECT LEFT(PhotoPath, 25)

FROM Employees

(No column name)
http//accweb/emmployees/
http//accweb/emmployees/
http//accweb/emmployees/
http//accweb/emmployees/
http//accweb/emmployees/
http//accweb/emmployees/
http//accweb/emmployees/
http//accweb/emmployees/
http//accweb/emmployees/
9 rows in total

Adding DISTINCT to above example will reduce number of results to distinct (different) records.

SELECT DISTINCT(LEFT(PhotoPath, 25))

FROM Employees

(No column name)
http//accweb/emmployees/
1 row in total

RIGHT

Returns 25 characters counting from the right.

SELECT RIGHT(HomePhone, 8)

FROM Employees

(No column name)
555-9857
555-9482
555-3412
555-8122
555-4848
555-7773
555-5598
555-1189
555-4444
9 rows in total

SUBSTRING

Extracts a substring from a string (start at position 6, extract 9 characters).

SELECT SUBSTRING(HomePhone, 6, 9)

FROM Employees

(No column name)
 555-9857
 555-9482
 555-3412
 555-8122
555-4848
555-7773
555-5598
 555-1189
555-4444
9 rows in total

LTRIM removes unwanted space(s) from the left, which appeared in the above example.

SELECT LTRIM(SUBSTRING(HomePhone, 6, 9))

FROM Employees

(No column name)
555-9857
555-9482
555-3412
555-8122
555-4848
555-7773
555-5598
555-1189
555-4444
9 rows in total

LTRIM

Removes space(s) from the left.

SELECT LTRIM(' SQL')

FROM Employees

(No column name)
SQL
SQL
SQL
SQL
SQL
SQL
SQL
SQL
SQL
9 rows in total

RTRIM

Removes space(s) from the right.

SELECT RTRIM('SQL ')

FROM Employees

(No column name)
SQL
SQL
SQL
SQL
SQL
SQL
SQL
SQL
SQL
9 rows in total

CONCAT

Joins character strings.

SELECT LastName, FirstName, CONCAT(FirstName,' ',LastName)

FROM Employees

LastName FirstName (No column name)
Davolio Nancy Nancy Davolio
Fuller Andrew Andrew Fuller
Leverling Janet Janet Leverling
Peacock Margaret Margaret Peacock
Buchanan Steven Steven Buchanan
Suyama Michael Michael Suyama
King Robert Robert King
Callahan Laura Laura Callahan
Dodsworth Anne Anne Dodsworth
9 rows in total

Another way of joining character strings.

SELECT LastName, FirstName, FirstName + ' ' + LastName

FROM Employees

LastName FirstName (No column name)
Davolio Nancy Nancy Davolio
Fuller Andrew Andrew Fuller
Leverling Janet Janet Leverling
Peacock Margaret Margaret Peacock
Buchanan Steven Steven Buchanan
Suyama Michael Michael Suyama
King Robert Robert King
Callahan Laura Laura Callahan
Dodsworth Anne Anne Dodsworth
9 rows in total

Operators +, -, *, /

Calculations using arithemetic operators.

SELECT ProductID, UnitPrice, Quantity, Discount, (UnitPrice * (1 - Discount)) * Quantity AS 'Total Sale'

FROM [Order Details]

ProductID UnitPrice Quantity Discount Total Sale
11 14.00 12 0 168
42 9.80 10 0 98
72 34.80 5 0 174
14 18.60 9 0 167.4
51 42.40 40 0 1696
41 7.70 10 0 77
51 42.40 35 0.15 1261.4
65 16.80 15 0.15 214.2
22 16.80 6 0.05 95.75999
57 15.60 15 0.05 222.3
2155 rows in total

UPPER

Returns a character expression with lowercase character data converted to uppercase.

SELECT UPPER(LastName)

FROM Employees

(No column name)
BUCHANAN
CALLAHAN
DAVOLIO
DODSWORTH
FULLER
KING
LEVERLING
PEACOCK
SUYAMA
9 rows in total

LOWER

Returns a character expression with uppercase character data converted to lowercase.

SELECT LOWER(LastName)

FROM Employees

(No column name)
buchanan
callahan
davolio
dodsworth
fuller
king
leverling
peacock
suyama
9 rows in total

GETDATE

Returns current date and time.

SELECT GETDATE()

(No column name)
2018-01-17 21:51:48.227
1 row in total

CURRENT_TIMESTAMP

Returns current date and time.

SELECT CURRENT_TIMESTAMP

(No column name)
2018-01-17 21:51:48.227
1 row in total

DATEPART

Returns year, month, week, day, etc. from the date. In the example refers to a month.

SELECT DATEPART(Month, OrderDate)

FROM Orders

(No column name)
7
7
7
7
7
7
7
7
7
7
830 rows in total

Returns year from a date.

SELECT FirstName, LastName, DATEPART(year, BirthDate) As [Year of birth]

FROM Employees

ORDER BY [Year of birth] ASC

FirstName LastName Year of birth
Margaret Peacock 1937
Nancy Davolio 1948
Andrew Fuller 1952
Steven Buchanan 1955
Laura Callahan 1958
Robert King 1960
Michael Suyama 1963
Janet Leverling 1963
Anne Dodsworth 1966
9 rows in total

In the example query returns age of an employee using GETDATE and DATEPART.

SELECT FirstName, LastName, DATEPART(year, GETDATE()) - DATEPART(year, BirthDate) As Age

FROM Employees

ORDER BY Age DESC

FirstName LastName Age
Margaret Peacock 81
Nancy Davolio 70
Andrew Fuller 66
Steven Buchanan 63
Laura Callahan 60
Robert King 58
Michael Suyama 55
Janet Leverling 55
Anne Dodsworth 52
9 rows in total

DATEDIFF

Find the difference in years, months, weeks, days, etc. between two dates.

SELECT DATEDIFF(YEAR, HireDate, GETDATE())

FROM Employees

(No column name)
26
26
26
25
25
25
24
24
24
9 rows in total

In the example query returns tenure of an employee using GETDATE and DATEDIFF for employee with tenure over 24 years.

SELECT FirstName, LastName, DATEDIFF(YEAR, HireDate, GETDATE()) As Tenure

FROM Employees

WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 24

FirstName LastName Tenure
Nancy Davolio 26
Andrew Fuller 26
Janet Leverling 26
Margaret Peacock 25
Steven Buchanan 25
Michael Suyama 25
6 rows in total

CASE, WHEN, THEN, ELSE, END

Evaluates a list of conditions. Next, returns one of possible result expressions.

SELECT OrderID, Quantity,

CASE

WHEN Quantity < 5 THEN 'Small quantity'

WHEN Quantity BETWEEN 5 AND 20 THEN 'Medium quantity'

ELSE 'Big quantity'

END AS 'Quantity range'

FROM [Order Details]

OrderID Quantity Quantity range
10248 12 Medium quantity
10248 10 Medium quantity
10248 5 Medium quantity
10249 9 Medium quantity
10249 40 Big quantity
10250 10 Medium quantity
10250 35 Big quantity
10250 15 Medium quantity
10251 6 Medium quantity
10251 15 Medium quantity
2155 rows in total

ORDER BY

Sorts data in either ascending or descending order. In the example sorted in ascending order by last name.

! No need to type ASC. By default, data is be sorted in ascending order.

SELECT LastName, FirstName

FROM Employees

ORDER BY LastName ASC;

LastName FirstName
Buchanan Steven
Callahan Laura
Davolio Nancy
Dodsworth Anne
Fuller Andrew
King Robert
Leverling Janet
Peacock Margaret
Suyama Michael
9 rows in total

In the example data is sorted in descending order by employee's birth of date.

SELECT LastName, FirstName, BirthDate

FROM Employees

ORDER BY BirthDate DESC;

LastName FirstName BirthDate
Dodsworth Anne 1966-01-27 00:00:00.000
Leverling Janet 1963-08-30 00:00:00.000
Suyama Michael 1963-07-02 00:00:00.000
King Robert 1960-05-29 00:00:00.000
Callahan Laura 1958-01-09 00:00:00.000
Buchanan Steven 1955-03-04 00:00:00.000
Fuller Andrew 1952-02-19 00:00:00.000
Davolio Nancy 1948-12-08 00:00:00.000
Peacock Margaret 1937-09-19 00:00:00.000
9 rows in total

Data sorted by several columns.

SELECT LastName, FirstName, City

FROM Employees

ORDER BY City, LastName;

LastName FirstName City
Leverling Janet Kirkland
Buchanan Steven London
Dodsworth Anne London
King Robert London
Suyama Michael London
Peacock Margaret Redmond
Callahan Laura Seattle
Davolio Nancy Seattle
Fuller Andrew Tacoma
9 rows in total

Again, data sorted by several columns.

SELECT LastName, FirstName, City

FROM Employees

ORDER BY City ASC, LastName DESC;

LastName FirstName City
Leverling Janet Kirkland
Suyama Michael London
King Robert London
Dodsworth Anne London
Buchanan Steven London
Peacock Margaret Redmond
Davolio Nancy Seattle
Callahan Laura Seattle
Fuller Andrew Tacoma
9 rows in total

WHERE

The WHERE clause filters for rows that meet certain criteria.

In the example city equals 'London'.

SELECT CustomerID, CompanyName, ContactName, City

FROM Customers

WHERE City = 'London';

CustomerID CompanyName ContactName City
AROUT Around the Horn Thomas Hardy London
BSBEV B's Beverages Victoria Ashworth London
CONSH Consolidated Holdings Elizabeth Brown London
EASTC Eastern Connection Ann Devon London
NORTS North/South Simon Crowther London
SEVES Seven Seas Imports Hari Kumar London
6 rows in total

In the example quantity is greater than 110.

SELECT *

FROM [Order Details]

WHERE Quantity >110

OrderID ProductID UnitPrice Quantity Discount
10398 55 19.20 120 0.1
10451 55 19.20 120 0.1
10515 27 43.90 120 0
10595 61 28.50 120 0.25
10678 41 9.65 120 0
10711 53 32.80 120 0
10764 39 18.00 130 0.1
10776 51 53.00 120 0.05
10894 75 7.75 120 0.05
11072 64 33.25 130 0
10 rows in total

In the example quantity is greater or equal to 120.

SELECT *

FROM [Order Details]

WHERE Quantity >=120

OrderID ProductID UnitPrice Quantity Discount
10398 55 19.20 120 0.1
10451 55 19.20 120 0.1
10515 27 43.90 120 0
10595 61 28.50 120 0.25
10678 41 9.65 120 0
10711 53 32.80 120 0
10764 39 18.00 130 0.1
10776 51 53.00 120 0.05
10894 75 7.75 120 0.05
11072 64 33.25 130 0
10 rows in total

In the example quantity is smaller or equal to 10.

SELECT *

FROM [Order Details]

WHERE Quantity <=10

OrderID ProductID UnitPrice Quantity Discount
10248 42 9.80 10 0
10248 72 34.80 5 0
10249 14 18.60 9 0
10250 41 7.70 10 0
10251 22 16.80 6 0.05
10257 39 14.40 6 0
10258 32 25.60 6 0.2
10259 21 8.00 10 0
10259 37 20.80 1 0
10262 56 30.40 2 0
608 rows in total

In the example Title is different than 'Sales Representative'.

SELECT EmployeeID, LastName, FirstName, Title

FROM Employees

WHERE Title <> 'Sales Representative';

EmployeeID LastName FirstName Title
2 Fuller Andrew Vice President, Sales
5 Buchanan Steven Sales Manager
8 Callahan Laura Inside Sales Coordinator
3 rows in total

BETWEEN

The BETWEEN operator selects values within a given range.

In the example Unit Price value is between 10 and 15.

! BETWEEN is inclusive.

SELECT *

FROM [Order Details]

WHERE UnitPrice BETWEEN 10 AND 15;

OrderID ProductID UnitPrice Quantity Discount
10248 11 14.00 12 0
10253 31 10.00 20 0
10253 39 14.40 42 0
10255 16 13.90 35 0
10256 77 10.40 12 0
10257 39 14.40 6 0
10257 77 10.40 15 0
10260 70 12.00 21 0.25
10261 35 14.40 20 0
10263 16 13.90 60 0.25
412 rows in total

SELECT TOP

Limits selection to a specified number of records.

In the example top 10 rows are returned.

SELECT TOP 10 UnitPrice FROM [Order Details]

UnitPrice
14.00
9.80
34.80
18.60
42.40
7.70
42.40
16.80
16.80
15.60
10 rows in total

In the example top 5% of rows is returned.

SELECT TOP 5 PERCENT UnitPrice FROM [Order Details]

UnitPrice
14.00
9.80
34.80
18.60
42.40
7.70
42.40
16.80
16.80
15.60
108 rows in total

In the example top 10 Unit Price rows are returned and sorted by Unit Price.

SELECT TOP 10 UnitPrice, Quantity FROM [Order Details]

ORDER BY UnitPrice ASC

UnitPrice Quantity
2.00 25
2.00 60
2.00 24
2.00 20
2.00 8
2.00 60
2.00 49
2.00 50
2.00 20
2.00 20
10 rows in total

AND, OR, NOT

Comparisons with NULL and the three-valued logic (3VL). SQL query can result in True, False or Unknown (NULL). The following table presents possible combinations:

p q p OR q p AND q p = q
True True True True True
True False True False False
True Unknown True Unknown Unknown
False True True False False
False False False False True
False Unknown Unknown False Unknown
Unknown True True Unknown Unknown
Unknown False Unknown False Unknown
Unknown Unknown Unknown Unknown Unknown
p NOT p
True False
False True
Unknown Unknown

In the example companies starting with the letter "L" and located in France are returned.

For more information about the "Like" operator scroll down.

SELECT *

FROM Customers

WHERE CompanyName LIKE 'L%' AND Country = 'France'

CustomerID CompanyName ContactName ContactTitle Address City Region
LACOR La corne d'abondance Daniel Tonini Sales Representative 67, avenue de l'Europe Versailles NULL
31000 LAMAI La maison d'Asie Annette Roulet Sales Manager 1 rue Alsace-Lorraine Toulouse
More columns below
PostalCode Country Phone Fax
78000 France 30.59.84.10 30.59.85.11
NULL France 61.77.61.10 61.77.61.11
2 rows in total

In the example companies starting with the letter "L" or located in Poland are returned.

SELECT *

FROM Customers

WHERE CompanyName LIKE 'L%' OR Country = 'Poland'

CustomerID CompanyName ContactName ContactTitle Address City Region
LACOR La corne d'abondance Daniel Tonini Sales Representative 67, avenue de l'Europe Versailles NULL
LAMAI La maison d'Asie Annette Roulet Sales Manager 1 rue Alsace-Lorraine Toulouse NULL
LAUGB Laughing Bacchus Wine Cellars Yoshi Tannamuri Marketing Assistant 1900 Oak St. Vancouver BC
LAZYK Lazy K Kountry Store John Steel Marketing Manager 12 Orchestra Terrace Walla Walla WA
LEHMS Lehmanns Marktstand Renate Messner Sales Representative Magazinweg 7 Frankfurt a.M. NULL
LETSS Let's Stop N Shop Jaime Yorres Owner 87 Polk St. Suite 5 San Francisco CA
LILAS LILA-Supermercado Carlos González Accounting Manager Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto Lara
LINOD LINO-Delicateses Felipe Izquierdo Owner Ave. 5 de Mayo Porlamar I. de Margarita Nueva Esparta
LONEP Lonesome Pine Restaurant Fran Wilson Sales Manager 89 Chiaroscuro Rd. Portland OR
WOLZA Wolski Zajazd Zbyszek Piestrzeniewicz Owner ul. Filtrowa 68 Warszawa NULL
More columns below
PostalCode Country Phone Fax
78000 France 30.59.84.10 30.59.85.11
31000 France 61.77.61.10 61.77.61.11
V3F 2K1 Canada (604) 555-3392 (604) 555-7293
99362 USA (509) 555-7969 (509) 555-6221
60528 Germany 069-0245984 069-0245874
94117 USA (415) 555-5938 NULL
3508 Venezuela (9) 331-6954 (9) 331-7256
4980 Venezuela (8) 34-56-12 (8) 34-93-93
97219 USA (503) 555-9573 (503) 555-9646
01-012 Poland (26) 642-7012 (26) 642-7012
10 rows in total

In the example companies starting with the letter "L" and located in France or Germany are returned.

SELECT *

FROM Customers

WHERE CompanyName LIKE 'L%' AND (Country = 'France' OR Country = 'Germany')

CustomerID CompanyName ContactName ContactTitle Address City Region
LACOR La corne d'abondance Daniel Tonini Sales Representative 67, avenue de l'Europe Versailles NULL
LAMAI La maison d'Asie Annette Roulet Sales Manager 1 rue Alsace-Lorraine Toulouse NULL
LEHMS Lehmanns Marktstand Renate Messner Sales Representative Magazinweg 7 Frankfurt a.M. NULL
More columns below
PostalCode Country Phone Fax
78000 France 30.59.84.10 30.59.85.11
31000 France 61.77.61.10 61.77.61.11
60528 Germany 069-0245984 069-0245874
3 rows in total

In the example companies starting with the letter "L" and not located in France are returned.

SELECT *

FROM Customers

WHERE CompanyName LIKE 'L%' AND NOT Country = 'France'

CustomerID CompanyName ContactName ContactTitle Address City Region
LAUGB Laughing Bacchus Wine Cellars Yoshi Tannamuri Marketing Assistant 1900 Oak St. Vancouver BC
LAZYK Lazy K Kountry Store John Steel Marketing Manager 12 Orchestra Terrace Walla Walla WA
LEHMS Lehmanns Marktstand Renate Messner Sales Representative Magazinweg 7 Frankfurt a.M. NULL
LETSS Let's Stop N Shop Jaime Yorres Owner 87 Polk St. Suite 5 San Francisco CA
LILAS LILA-Supermercado Carlos González Accounting Manager Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto Lara
LINOD LINO-Delicateses Felipe Izquierdo Owner Ave. 5 de Mayo Porlamar I. de Margarita Nueva Esparta
LONEP Lonesome Pine Restaurant Fran Wilson Sales Manager 89 Chiaroscuro Rd. Portland OR
More columns below
PostalCode Country Phone Fax
V3F 2K1 Canada (604) 555-3392 (604) 555-7293
99362 USA (509) 555-7969 (509) 555-6221
60528 Germany 069-0245984 069-0245874
94117 USA (415) 555-5938 NULL
3508 Venezuela (9) 331-6954 (9) 331-7256
4980 Venezuela (8) 34-56-12 (8) 34-93-93
97219 USA (503) 555-9573 (503) 555-9646
7 rows in total

IN

IN operator in the WHERE clause is useful to filter data by a list of values. It is similar to multiple OR conditions.

In the example companies operating in Argentina or Mexico are returned.

SELECT CustomerID, CompanyName, Country

FROM Customers

WHERE Country IN ('Argentina', 'Mexico')

CustomerID CompanyName Country
ANATR Ana Trujillo Emparedados y helados Mexico
ANTON Antonio Moreno Taquería Mexico
CACTU Cactus Comidas para llevar Argentina
CENTC Centro comercial Moctezuma Mexico
OCEAN Océano Atlántico Ltda. Argentina
PERIC Pericles Comidas clásicas Mexico
RANCH Rancho grande Argentina
TORTU Tortuga Restaurante Mexico
3 rows in total

In the example companies operating in countries other than Argentina or Mexico are returned.

SELECT CustomerID, CompanyName, Country

FROM Customers

WHERE Country NOT IN ('Argentina', 'Mexico')

CustomerID CompanyName Country
ALFKI Alfreds Futterkiste Germany
AROUT Around the Horn UK
BERGS Berglunds snabbköp Sweden
BLAUS Blauer See Delikatessen Germany
BLONP Blondesddsl père et fils France
BOLID Bólido Comidas preparadas Spain
BONAP Bon app' France
BOTTM Bottom-Dollar Markets Canada
BSBEV B's Beverages UK
CHOPS Chop-suey Chinese Switzerland
83 rows in total

The following SQL statement selects all customers that are from the same countries as the suppliers.

SELECT CustomerID, CompanyName, ContactName, City

FROM Customers

WHERE City IN (SELECT City FROM Suppliers)

CustomerID CompanyName ContactName City
ALFKI Alfreds Futterkiste Maria Anders Berlin
AROUT Around the Horn Thomas Hardy London
BSBEV B's Beverages Victoria Ashworth London
COMMI Comércio Mineiro Pedro Afonso Sao Paulo
CONSH Consolidated Holdings Elizabeth Brown London
EASTC Eastern Connection Ann Devon London
FAMIA Familia Arquibaldo Aria Cruz Sao Paulo
MEREP Mère Paillarde Jean Fresnière Montréal
NORTS North/South Simon Crowther London
PARIS Paris spécialités Marie Bertrand Paris
14 rows in total

The following SQL statement selects all Suppliers that are from the same countries as the customers.

SELECT SupplierID, CompanyName, ContactName, City

FROM Suppliers

WHERE City IN (SELECT City FROM Customers)

SupplierID CompanyName ContactName City
1 Exotic Liquids Charlotte Cooper London
10 Refrescos Americanas LTDA Carlos Diaz Sao Paulo
11 Heli Süßwaren GmbH & Co. KG Petra Winkler Berlin
18 Aux joyeux ecclésiastiques Guylène Nodier Paris
25 Ma Maison Jean-Guy Lauzon Montréal
5 rows in total

LIKE

LIKE operator determines if a character string matches a pattern. Wildcard characters are % (percent) and _ (underscore).

A % matches any string with zero or more characters. While an _ matches any single character.

In the example companies starting with the letter "S" are returned.

! It doesn't matter whether the letter is in upper or lower case.

SELECT *

FROM Customers

WHERE Country Like 's%'

CustomerID CompanyName ContactName ContactTitle Address City Region
BERGS Berglunds snabbköp Christina Berglund Order Administrator Berguvsvägen 8 Luleå NULL
BOLID Bólido Comidas preparadas Martín Sommer Owner C/ Araquil, 67 Madrid NULL
CHOPS Chop-suey Chinese Yang Wang Owner Hauptstr. 29 Bern NULL
FISSA FISSA Fabrica Inter. Salchichas S.A. Diego Roel Accounting Manager C/ Moralzarzal, 86 Madrid NULL
FOLKO Folk och fä HB Maria Larsson Owner Åkergatan 24 Bräcke NULL
GALED Galería del gastrónomo Eduardo Saavedra Marketing Manager Rambla de Cataluña, 23 Barcelona NULL
GODOS Godos Cocina Típica José Pedro Freyre Sales Manager C/ Romero, 33 Sevilla NULL
RICSU Richter Supermarkt Michael Holz Sales Manager Grenzacherweg 237 Genève NULL
ROMEY Romero y tomillo Alejandra Camino Accounting Manager Gran Vía, 1 Madrid NULL
More columns below
PostalCode Country Phone Fax
S-958 22 Sweden 0921-12 34 65 0921-12 34 67
28023 Spain (91) 555 22 82 (91) 555 91 99
3012 Switzerland 0452-076545 NULL
28034 Spain (91) 555 94 44 (91) 555 55 93
S-844 67 Sweden 0695-34 67 21 NULL
08022 Spain (93) 203 4560 (93) 203 4561
41101 Spain (95) 555 82 82 NULL
1203 Switzerland 0897-034214 NULL
28001 Spain (91) 745 6200 (91) 745 6210
9 rows in total

Companies which names end with "ssen".

SELECT CompanyName

FROM Customers

WHERE CompanyName Like '%ssen'

CompanyName
Blauer See Delikatessen
Drachenblut Delikatessen
Königlich Essen
Old World Delicatessen
4 rows in total

Customers which name consists of 8 signs (8 x underscore). Including space(s) and signs such as apostrophe.

SELECT CompanyName

FROM Customers

WHERE CompanyName Like '________'

CompanyName
Bon app'
1 row in total

Customers where second letter in the name is "a".

SELECT CompanyName

FROM Customers

WHERE CompanyName Like '_a%'

CompanyName
Cactus Comidas para llevar
Eastern Connection
Familia Arquibaldo
Galería del gastrónomo
Hanari Carnes
La corne d'abondance
La maison d'Asie
Laughing Bacchus Wine Cellars
Lazy K Kountry Store
Magazzini Alimentari Riuniti
18 rows in total

Customers where first letter in the name is "c" and the last one is "r".

SELECT CompanyName

FROM Customers

WHERE CompanyName Like 'c%r'

CompanyName
Cactus Comidas para llevar
1 row in total

Null Values

A field that contains a NULL value has no value. NULL value is different from a zero value or a field that contains spaces. A field with a NULL value has been left blank when a record has been created.

It is not possible to compare NULL values against operators, such as =, <, or <>. Instead, WHERE IS NULL or WHERE IS NOT NULL are used as shown in examples below.

SELECT FirstName, LastName, Title, Region

FROM Employees

WHERE Region IS NULL

FirstName LastName Title Region
Steven Buchanan Sales Manager NULL
Michael Suyama Sales Representative NULL
Robert King Sales Representative NULL
Anne Dodsworth Sales Representative NULL
4 rows in total

No results are shown since WHERE IS NULL clause has to be used to return NULL records.

SELECT FirstName, LastName, Title, Region

FROM Employees

WHERE Region = 'NULL'

FirstName LastName Title Region
0 rows in total

Similarly, no results are shown since WHERE IS NULL clause has to be used to return NULL records.

SELECT FirstName, LastName, Title, Region

FROM Employees

WHERE Region = ''

FirstName LastName Title Region
0 rows in total

Error, no records returned.

SELECT FirstName, LastName, Title, Region

FROM Employees

WHERE Region = 0

Error returned Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value 'WA' to data type int.

rows in total

Returns records where region is not NULL.

SELECT FirstName, LastName, Title, Region

FROM Employees

WHERE Region IS NOT NULL

FirstName LastName Title Region
Nancy Davolio Sales Representative WA
Andrew Fuller Vice President, Sales WA
Janet Leverling Sales Representative WA
Margaret Peacock Sales Representative WA
Laura Callahan Inside Sales Coordinator WA
5 rows in total

In the example region's NULL values are replaced with "Unknown".

SELECT FirstName, LastName, Title, ISNULL(Region, 'Unknown')

FROM Employees

WHERE Region IS NULL

FirstName LastName Title (No column name)
Steven Buchanan Sales Manager Unknown
Michael Suyama Sales Representative Unknown
Robert King Sales Representative Unknown
Anne Dodsworth Sales Representative Unknown
4 rows in total

SELECT DISTINCT

DISTINCT statement returns only distinct (unique) values.

In the example distinct countries are returned.

SELECT DISTINCT Country

FROM Customers

Country
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland
21 rows in total

In the example distinct customer IDs are returned from the Orders table.

SELECT DISTINCT CustomerID

FROM Orders

CustomerID
ALFKI
ANATR
ANTON
AROUT
BERGS
BLAUS
BLONP
BOLID
BONAP
BOTTM
89 rows in total

COUNT, AVG, SUM

COUNT function returns a count of the number of data values. SUM function returns the sum of the data values. AVG function returns the average of the data values.

The example shows number of customer IDs from Customers table.

SELECT COUNT(CustomerID)

FROM Customers

(No column name)
91
1 row in total

The example shows number of countries from Customers table.

! The query doesn't remove duplicates. Countries are counted multiple times.

SELECT COUNT(Country)

FROM Customers

(No column name)
91
1 row in total

The example shows number of countries from Customers table.

! Duplicates are removed thanks to DISTINCT statement. Query counts unique countries.

SELECT COUNT(DISTINCT Country)

FROM Customers

(No column name)
21
1 row in total

Average value of discount granted on condition that any discount was granted.

! There is no limit in terms of decimal places.

SELECT AVG(Discount)

FROM [Order Details]

WHERE Discount > 0

(No column name)
0,144439142966434
1 row in total

Average value of discount granted on condition that any discount was granted.

Limited to two decimal places.

SELECT ROUND(AVG(Discount),2)

FROM [Order Details]

WHERE Discount > 0

(No column name)
0,14
1 row in total

Ordered quantity in total.

SELECT SUM(Quantity)

FROM [Order Details]

(No column name)
51317
1 row in total

MIN, MAX

Min and Max functions show the smallest value and the largest value respectively.

In the example the product with the highest price.

SELECT MAX(UnitPrice)

FROM Products

(No column name)
263.50
1 row in total

In the example the product with the lowest price.

SELECT MIN(UnitPrice)

FROM Products

(No column name)
2.50
1 row in total

GROUP BY

GROUP BY is used to collect data across multiple records and group the results by one or more columns. The clause typically also involves aggregates, such as COUNT, MAX, SUM, AVG, etc.

In the example grouping by country.

SELECT Country

FROM Customers

GROUP BY Country

Country
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland
21 rows in total

In the example grouping by title and aggregating (counting) by employee ID.

SELECT COUNT(EmployeeID), Title

FROM Employees

GROUP BY Title

ORDER BY COUNT(EmployeeID) DESC

(No column name) Title
6 Sales Representative
1 Vice President, Sales
1 Inside Sales Coordinator
1 Sales Manager
4 rows in total

HAVING

HAVING clause filters records that work on summarized GROUP BY results.

In the example Having clause is used as a filter of grouped results.

SELECT Country AS 'Customer''s country', COUNT(CustomerID) AS 'Number of customers'

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5

ORDER BY COUNT(CustomerID) DESC

Customer's country Number of customers
USA 13
France 11
Germany 11
Brazil 9
UK 7
5 rows in total

EXISTS

EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. Subqueries are nested queries that provide data to the enclosing query.

In the example products are returned on condition that supplier is from London.

SELECT ProductName

From Products

WHERE EXISTS (SELECT * FROM Suppliers WHERE SupplierID = Products.SupplierID AND City = 'London')

ProductName
Chai
Chang
Aniseed Syrup
3 rows in total

In the example products are returned on condition that supplier is not from London and product category is "Beverages".

SELECT ProductName

From Products

WHERE EXISTS (SELECT * FROM Suppliers WHERE SupplierID = Products.SupplierID AND NOT City = 'London')

AND EXISTS (SELECT * FROM Categories WHERE CategoryID = Products.CategoryID AND CategoryName = 'Beverages')

ProductName
Guaraná Fantástica
Sasquatch Ale
Steeleye Stout
Côte de Blaye
Chartreuse verte
Ipoh Coffee
Laughing Lumberjack Lager
Outback Lager
Rhönbräu Klosterbier
Lakkalikööri
10 rows in total

ANY, ALL

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.

In the example records are returned since at least one of the products meet the condition in terms of quantity.

SELECT ProductName

From Products

WHERE ProductID = ANY (SELECT ProductID FROM [Order Details] WHERE Quantity > 110)

ProductName
Schoggi Schokolade
Chartreuse verte
Jack's New England Clam Chowder
Manjimup Dried Apples
Perth Pasties
Pâté chinois
Sirop d'érable
Wimmers gute Semmelknödel
Rhönbräu Klosterbier
9 rows in total

There are no results since one of the conditions is not met.

SELECT ProductName

From Products

WHERE 77 > ALL (SELECT ProductID FROM [Order Details])

ProductName
0 rows in total

Since all of the conditions are met all 77 results are shown.

SELECT ProductName

From Products

WHERE 78 > ALL (SELECT ProductID FROM [Order Details])

ProductName
Alice Mutton
Aniseed Syrup
Boston Crab Meat
Camembert Pierrot
Carnarvon Tigers
Chai
Chang
Chartreuse verte
Chef Anton's Cajun Seasoning
Chef Anton's Gumbo Mix
77 rows in total

SQL Joins

A SQL JOIN combines records from two or more tables. Possible joins in the picture below.

SQL joins

INNER JOIN

Returns rows when the joining key between both tables matches.

SELECT EmployeeID, Territories.TerritoryDescription

FROM EmployeeTerritories

INNER JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID;

EmployeeID TerritoryDescription
1 Wilton
1 Neward
2 Westboro
2 Bedford
2 Georgetow
2 Boston
2 Cambridge
2 Braintree
2 Louisville
3 Atlanta
49 rows in total

LEFT JOIN

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

! If we use RIGHT JOIN and switch the order of tables in query the same results will be obtained which will be shown in the next example.

! Similar results were obtained in above INNER JOIN query which means there are records in table A (EmployeeTerritories) which don't match table B (Territories).

SELECT EmployeeID, Territories.TerritoryDescription

FROM EmployeeTerritories

LEFT JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID

ORDER BY EmployeeID, Territories.TerritoryDescription

EmployeeID TerritoryDescription
1 Wilton
1 Neward
2 Westboro
2 Bedford
2 Georgetow
2 Boston
2 Cambridge
2 Braintree
2 Louisville
3 Atlanta
49 rows in total

Using RIGHT JOIN to obtain the same result as above. Order of tables in query was altered.

SELECT EmployeeID, Territories.TerritoryDescription

FROM Territories

RIGHT JOIN EmployeeTerritories ON Territories.TerritoryID = EmployeeTerritories.TerritoryID

ORDER BY EmployeeID, Territories.TerritoryDescription

EmployeeID TerritoryDescription
1 Wilton
1 Neward
2 Westboro
2 Bedford
2 Georgetow
2 Boston
2 Cambridge
2 Braintree
2 Louisville
3 Atlanta
49 rows in total

Three-table left join example.

SELECT EmployeeTerritories.EmployeeID, Employees.FirstName + ' ' + Employees.LastName AS FullName,

Territories.TerritoryDescription

FROM Employees

LEFT JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID

LEFT JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID

EmployeeID FullName TerritoryDescription
1 Nancy Davolio Wilton
1 Nancy Davolio Neward
2 Andrew Fuller Westboro
2 Andrew Fuller Bedford
2 Andrew Fuller Georgetow
2 Andrew Fuller Boston
2 Andrew Fuller Cambridge
2 Andrew Fuller Braintree
2 Andrew Fuller Louisville
3 Janet Leverling Atlanta
49 rows in total

RIGHT JOIN

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

! If we use LEFT JOIN and switch the order of tables in query the same results will be obtained which will be shown in the next example.

! There are 4 more records than in case of similar LEFT JOIN example. This is because in 4 cases there are no matches which results in NULL values.

SELECT EmployeeID, Territories.TerritoryDescription

FROM EmployeeTerritories

RIGHT JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID

ORDER BY EmployeeID, Territories.TerritoryDescription

EmployeeID TerritoryDescription
NULL Austin
NULL Bentonville
NULL Columbia
NULL Dallas
1 Neward
1 Wilton
2 Bedford
2 Boston
2 Braintree
2 Cambridge
53 rows in total

Using RIGHT JOIN to obtain the same result as above. Order of tables in query was altered.

SELECT EmployeeID, Territories.TerritoryDescription

FROM Territories

LEFT JOIN EmployeeTerritories ON Territories.TerritoryID = EmployeeTerritories.TerritoryID

ORDER BY EmployeeID, Territories.TerritoryDescription

EmployeeID TerritoryDescription
NULL Austin
NULL Bentonville
NULL Columbia
NULL Dallas
1 Neward
1 Wilton
2 Bedford
2 Boston
2 Braintree
2 Cambridge
53 rows in total

Three-table right join example.

SELECT EmployeeTerritories.EmployeeID, Employees.FirstName + ' ' + Employees.LastName,

Territories.TerritoryDescription

FROM Employees

RIGHT JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID

RIGHT JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID

EmployeeID (No column name) TerritoryDescription
2 Andrew Fuller Westboro
2 Andrew Fuller Bedford
2 Andrew Fuller Georgetow
2 Andrew Fuller Boston
2 Andrew Fuller Cambridge
2 Andrew Fuller Braintree
5 Steven Buchanan Providence
9 Anne Dodsworth Hollis
9 Anne Dodsworth Portsmouth
1 Nancy Davolio Wilton
53 rows in total

FULL JOIN

Returns all records from both Table A and Table B regardless there are matches or not.

SELECT EmployeeID, Territories.TerritoryDescription

FROM EmployeeTerritories

FULL JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID

ORDER BY EmployeeID, Territories.TerritoryDescription

EmployeeID (No column name) TerritoryDescription
2 Andrew Fuller Westboro
2 Andrew Fuller Bedford
2 Andrew Fuller Georgetow
2 Andrew Fuller Boston
2 Andrew Fuller Cambridge
2 Andrew Fuller Braintree
5 Steven Buchanan Providence
9 Anne Dodsworth Hollis
9 Anne Dodsworth Portsmouth
1 Nancy Davolio Wilton
53 rows in total

Example shows combination of full and left table joins.

SELECT EmployeeTerritories.EmployeeID, Employees.FirstName + ' ' + Employees.LastName,

Territories.TerritoryDescription

FROM Employees

FULL JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID

LEFT JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID

EmployeeID (No column name) TerritoryDescription
1 Nancy Davolio Wilton
1 Nancy Davolio Neward
2 Andrew Fuller Westboro
2 Andrew Fuller Bedford
2 Andrew Fuller Georgetow
2 Andrew Fuller Boston
2 Andrew Fuller Cambridge
2 Andrew Fuller Braintree
2 Andrew Fuller Louisville
3 Janet Leverling Atlanta
53 rows in total

SELF JOIN

Self joins connects rows with other rows in the same table.

In the example Andrew Fuller doesn't appear as he reports to NULL. We may assume there is no one also above in hierarchy.

SELECT a.LastName AS Employee, b.LastName AS Supervisor

FROM Employees a, Employees b

WHERE b.EmployeeID = a.ReportsTo

Employee Supervisor
Davolio Fuller
Leverling Fuller
Peacock Fuller
Buchanan Fuller
Suyama Buchanan
King Buchanan
Callahan Fuller
Dodsworth Buchanan
8 rows in total

In the example we assume that Andrew Fuller reports to himself.

SELECT a.LastName AS Employee, b.LastName AS Supervisor

FROM Employees a, Employees b

WHERE b.EmployeeID = ISNULL(a.ReportsTo, 2)

Employee Supervisor
Davolio Fuller
Fuller Fuller
Leverling Fuller
Peacock Fuller
Buchanan Fuller
Suyama Buchanan
King Buchanan
Callahan Fuller
Dodsworth Buchanan
9 rows in total

UNION

The UNION operator combines the results of two or more SELECT statements. In order to use UNION columns in tables have to match. Duplicated pairs are removed.

SELECT City, Country FROM Suppliers

UNION

SELECT City, Country FROM Employees

City Country
Ann Arbor USA
Annecy France
Bend USA
Berlin Germany
Boston USA
Cuxhaven Germany
Frankfurt Germany
Göteborg Sweden
Kirkland USA
Lappeenranta Finland
33 rows in total

The UNION ALL operator combines the results of two or more SELECT statements, however duplicated pairs are not removed. London, UK appears multiple times

SELECT City, Country FROM Suppliers

UNION ALL

SELECT City, Country FROM Employees

City Country
London UK
New Orleans USA
Ann Arbor USA
Tokyo Japan
Oviedo Spain
Osaka Japan
Melbourne Australia
Manchester UK
Göteborg Sweden
38 rows in total

INSERT INTO

Inserts a new record into a table.

INSERT INTO Employees (LastName, FirstName, Title)

VALUES ('Janus', 'Piotr', 'Sales Representative')

LastName FirstName Title
Davolio Nancy Sales Representative
Fuller Andrew Vice President, Sales
Leverling Janet Sales Representative
Peacock Margaret Sales Representative
Buchanan Steven Sales Manager
Suyama Michael Sales Representative
King Robert Sales Representative
Callahan Laura Inside Sales Coordinator
Dodsworth Anne Sales Representative
Janus Piotr Sales Representative
(1 row affected)

UPDATE

Updates a record in a table.

UPDATE Employees

SET FirstName = 'Peter', LastName = 'Janusz'

WHERE EmployeeID = 10

LastName FirstName Title
Davolio Nancy Sales Representative
Fuller Andrew Vice President, Sales
Leverling Janet Sales Representative
Peacock Margaret Sales Representative
Buchanan Steven Sales Manager
Suyama Michael Sales Representative
King Robert Sales Representative
Callahan Laura Inside Sales Coordinator
Dodsworth Anne Sales Representative
Janusz Piotr Sales Representative
(1 row affected)

DELETE

Deletes a record from a table.

DELETE FROM Employees

WHERE EmployeeID = 10

LastName FirstName Title
Davolio Nancy Sales Representative
Fuller Andrew Vice President, Sales
Leverling Janet Sales Representative
Peacock Margaret Sales Representative
Buchanan Steven Sales Manager
Suyama Michael Sales Representative
King Robert Sales Representative
Callahan Laura Inside Sales Coordinator
Dodsworth Anne Sales Representative
(1 row affected)