I collected on the following pages various SQL queries and their results on a sample database. They use different SQL functions and will help to learn and understand SQL queries. In addition they help for look up most commonSQL syntax examples. A SQL beginner will get a good overview about the capabilities of SQL. Examples are given for select, count, union, where, having, order, limit, views, NULL, like, subselects and joins.

 
Every query has a short description about it's purpose. Next he SQL query is listed with the result returned by the query. To create the sample database and -data download this file.  All  SQL query examples can be downloaded here. The sample database is a model of asmall database whichuses some data relations also used by facebook: Persons, friends, siblings, cities, addresses and messages.
 
For installation instructions for mySQL check this link and
 
mysql <frampsSQLByExampleData.sql 
mysql --column-names --table <frampsSQLByExample.sql
 
will install the sample sample data and run the sample queries

 

Sample database model
 
frampsSQLByExampleDBModel.jpg
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
   
 
 -- *******************
-- Simple queries to show the contents of the sample database
-- *******************

-- Query persons
select * from Person;


+----+----------+-----------+-----------+-----------+------------+---------+--------+
| id | LastName | FirstName | Father_Id | Mother_Id | Address_Id | Gender  | Height |
+----+----------+-----------+-----------+-----------+------------+---------+--------+
|  1 | Turner   | Peter     |         6 |         5 |          1 | M       |    180 |
|  2 | Turner   | Angie     |      NULL |         8 |          1 | F       |    160 |
|  3 | Turner   | Marvin    |         1 |         2 |          2 | M       |    185 |
|  4 | Turner   | Roxanne   |         1 |         2 |          1 | F       |    155 |
|  5 | Turner   | Wendy     |         8 |      NULL |          3 | F       |    167 |
|  6 | Turner   | Richard   |      NULL |      NULL |          3 | M       |    162 |
|  8 | Wilson   | Hilary    |      NULL |      NULL |          4 | F       |    169 |
|  7 | Wilson   | Woodrow   |      NULL |      NULL |          4 | M       |    174 |
| 10 | Chen     | Walter    |      NULL |      NULL |       NULL | M       |    156 |
|  9 | Robinson | Rick      |      NULL |      NULL |       NULL | M       |    189 |
+----+----------+-----------+-----------+-----------+------------+---------+--------+

-- Query Sibling
select * from Sibling;


+----+---------+-------+
| id | from_Id | to_Id |
+----+---------+-------+
|  1 |       3 |     4 |
|  2 |       4 |     3 |
+----+---------+-------+

-- Query City
select * from City;


+----+-----------+-------+
| id | Name      | ZIP   |
+----+-----------+-------+
|  1 | Rochester | 87656 |
|  2 | Paris     |  1234 |
|  3 | Wien      | 52637 |
|  4 | Berlin    | 12987 |
|  5 | New York  | 65432 |
+----+-----------+-------+

-- Query address
select * from Address;


+----+---------+----------------+------+
| id | City_Id | Street         | No   |
+----+---------+----------------+------+
|  1 |       1 | Bondstreet     | 13   |
|  2 |       2 | PlaceDeLetoile | 45   |
|  3 |       3 | BarneysStreet  | 5    |
|  4 |       4 | ShiningStreet  | 10   |
+----+---------+----------------+------+

-- Query message
select * from Message;

+----+---------+-------+------------------------------------------+
| id | from_Id | to_ID | message                                  |
+----+---------+-------+------------------------------------------+
|  1 |       1 |     2 | Nice to meet you                         |
|  2 |       2 |     1 | I also enjoy to meet you                 |
|  3 |       3 |     1 | My system crashed. Can you help me?      |
|  4 |       3 |     2 | I'm hungry                               |
|  5 |       2 |     3 | Just visit MC'Donalds                    |
|  6 |       1 |     3 | Restart your system                      |
|  7 |       3 |     1 | Could you help me with GMAT?             |
|  8 |       3 |     1 | Don't forget me and buy a big mac for me |
+----+---------+-------+------------------------------------------+

-- Query Friend
select * from Friend;


+----+---------+-------+
| id | from_Id | to_Id |
+----+---------+-------+
|  1 |       3 |     9 |
|  2 |       9 |     3 |
|  3 |       4 |    10 |
|  4 |      10 |     4 |
|  5 |       3 |     9 |
|  6 |       9 |     3 |
+----+---------+-------+

-- Select some columns from the table of all persons
select LastName, Firstname from Person;


+----------+-----------+
| LastName | Firstname |
+----------+-----------+
| Turner   | Peter     |
| Turner   | Angie     |
| Turner   | Marvin    |
| Turner   | Roxanne   |
| Turner   | Wendy     |
| Turner   | Richard   |
| Wilson   | Hilary    |
| Wilson   | Woodrow   |
| Chen     | Walter    |
| Robinson | Rick      |
+----------+-----------+

-- Query persons and sort by firstname
select LastName, FirstName from Person order by FirstName;

+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Turner   | Angie     |
| Wilson   | Hilary    |
| Turner   | Marvin    |
| Turner   | Peter     |
| Turner   | Richard   |
| Robinson | Rick      |
| Turner   | Roxanne   |
| Chen     | Walter    |
| Turner   | Wendy     |
| Wilson   | Woodrow   |
+----------+-----------+

-- Query persons and sort by lastname and firstname
select LastName, FirstName from Person order by LastName, FirstName;


+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Chen     | Walter    |
| Robinson | Rick      |
| Turner   | Angie     |
| Turner   | Marvin    |
| Turner   | Peter     |
| Turner   | Richard   |
| Turner   | Roxanne   |
| Turner   | Wendy     |
| Wilson   | Hilary    |
| Wilson   | Woodrow   |
+----------+-----------+

-- Query persons and sort by lastname descending and firstname ascending
select LastName, FirstName from Person order by LastName desc, FirstName asc;


+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Wilson   | Hilary    |
| Wilson   | Woodrow   |
| Turner   | Angie     |
| Turner   | Marvin    |
| Turner   | Peter     |
| Turner   | Richard   |
| Turner   | Roxanne   |
| Turner   | Wendy     |
| Robinson | Rick      |
| Chen     | Walter    |
+----------+-----------+

-- Select only persons with a given LastName
select LastName, Firstname from Person
    where LastName = 'Turner';


+----------+-----------+
| LastName | Firstname |
+----------+-----------+
| Turner   | Peter     |
| Turner   | Angie     |
| Turner   | Marvin    |
| Turner   | Roxanne   |
| Turner   | Wendy     |
| Turner   | Richard   |
+----------+-----------+

-- Select some columns from the table and give the column names German descriptions
select LastName as Nachname, Firstname as Vorname from Person;


+----------+---------+
| Nachname | Vorname |
+----------+---------+
| Turner   | Peter   |
| Turner   | Angie   |
| Turner   | Marvin  |
| Turner   | Roxanne |
| Turner   | Wendy   |
| Turner   | Richard |
| Wilson   | Hilary  |
| Wilson   | Woodrow |
| Chen     | Walter  |
| Robinson | Rick    |
+----------+---------+
 


-- *******************
-- NULL values
-- *******************

-- NULL values are NOT identical
select NULL = NULL;


+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

-- NULL values are identical if special operator <=> is used
select NULL <=> NULL;


+---------------+
| NULL <=> NULL |
+---------------+
|             1 |
+---------------+

-- tests for NULL equality
select NULL is null,NULL is not null;


+--------------+------------------+
| NULL is null | NULL is not null |
+--------------+------------------+
|            1 |                0 |
+--------------+------------------+

-- count(*) counts all rows whereas count(columnName) counts rows which don't have NULL in the column
select count(*) as 'Total # of Persons', count(Father_Id) as 'Person has father' from Person;


+--------------------+-------------------+
| Total # of Persons | Person has father |
+--------------------+-------------------+
|                 10 |                 4 |
+--------------------+-------------------+

-- Select persons which don't have a known father
select LastName, FirstName from Person
    where Father_Id is null;


+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Turner   | Angie     |
| Turner   | Richard   |
| Wilson   | Hilary    |
| Wilson   | Woodrow   |
| Chen     | Walter    |
| Robinson | Rick      |
+----------+-----------+

-- Select persons which know their father
select LastName, FirstName from Person
    where Father_Id is not null;


+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Turner   | Peter     |
| Turner   | Marvin    |
| Turner   | Roxanne   |
| Turner   | Wendy     |
+----------+-----------+

-- Select persons which down't know both parents
select LastName, FirstName from Person
    where (Father_Id is not null
        and Mother_Id is null)
    or
        (Father_Id is null
        and Mother_Id is not null);


+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Turner   | Angie     |
| Turner   | Wendy     |
+----------+-----------+

-- NULL does not match any like statement
select NULL like '%', NULL not like '%';
select 'art' like 'a%', 'art' not like '%';


+---------------+-------------------+
| NULL like '%' | NULL not like '%' |
+---------------+-------------------+
|          NULL |              NULL |
+---------------+-------------------+
+-----------------+--------------------+
| 'art' like 'a%' | 'art' not like '%' |
+-----------------+--------------------+
|               1 |                  0 |
+-----------------+--------------------+
 


-- *******************
-- views to simplify queries
-- *******************

-- Create view which returns persons which have a father only
create or replace view motherOnly as
    select LastName, FirstName from Person
        where (Father_Id is not null
            and Mother_Id is null);

-- Create view which returns persons which have a mother only
create or replace view fatherOnly as
    select LastName, FirstName from Person
        where (Father_Id is null
            and Mother_Id is not null);

-- Select persons which either know father or mother
select * from fatherOnly
union
select * from motherOnly;

+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Turner   | Angie     |
| Turner   | Wendy     |
+----------+-----------+
 


-- *******************
-- group queries
-- *******************

-- Count number of women and men with the same lastname
select LastName, Gender, count(*) as Count from Person
    group by LastName,Gender;

+----------+---------+-------+
| LastName | Gender  | Count |
+----------+---------+-------+
| Chen     | M       |     1 |
| Robinson | M       |     1 |
| Turner   | F       |     3 |
| Turner   | M       |     3 |
| Wilson   | F       |     1 |
| Wilson   | M       |     1 |
+----------+---------+-------+

-- Count number of persons with a given name and give the result column another name
select count(*) as 'Number of people' from Person
    where LastName = 'Turner';


+------------------+
| Number of people |
+------------------+
|                6 |
+------------------+

-- Count number of men an women
select Gender,count(*) as 'Number of people' from Person
    group by Gender;
 
+---------+------------------+
| Gender  | Number of people |
+---------+------------------+
| F       |                4 |
| M       |                6 |
+---------+------------------+
 
 
-- Count number of men an women and give gender a better name
select If(Gender = 'M',"Men","Woman") as Gender ,count(*) as 'Number of people' from Person
    group by Gender;


+---------+------------------+
| Gender  | Number of people |
+---------+------------------+
| F       |                4 |
| M       |                6 |
+---------+------------------+

-- Show women and men which with the same lastname if there are more than 2
select LastName, Gender, count(*) as Count from Person
    group by LastName,Gender
    having count(*) > 2;

+----------+---------+-------+
| LastName | Gender  | Count |
+----------+---------+-------+
| Turner   | F       |     3 |
| Turner   | M       |     3 |
+----------+---------+-------+


 
-- *******************
-- calculations
-- *******************

-- Show height of persons
select LastName, FirstName, height as Avg from Person
    order by height;


+----------+-----------+------+
| LastName | FirstName | Avg  |
+----------+-----------+------+
| Turner   | Roxanne   |  155 |
| Chen     | Walter    |  156 |
| Turner   | Angie     |  160 |
| Turner   | Richard   |  162 |
| Turner   | Wendy     |  167 |
| Wilson   | Hilary    |  169 |
| Wilson   | Woodrow   |  174 |
| Turner   | Peter     |  180 |
| Turner   | Marvin    |  185 |
| Robinson | Rick      |  189 |
+----------+-----------+------+

-- Show smallest person
select LastName, FirstName, height as Avg from Person
    order by height
    limit 1;


+----------+-----------+------+
| LastName | FirstName | Avg  |
+----------+-----------+------+
| Turner   | Roxanne   |  155 |
+----------+-----------+------+

-- Show biggest person
select LastName, FirstName, height as Avg from Person
    order by height desc
    limit 1;


+----------+-----------+------+
| LastName | FirstName | Avg  |
+----------+-----------+------+
| Robinson | Rick      |  189 |
+----------+-----------+------+

-- Count min, max, avg of height of persons
select Min(height) as Min, Max(height) as Max, Avg(height) as Avg from Person;


+------+------+----------+
| Min  | Max  | Avg      |
+------+------+----------+
|  155 |  189 | 169.7000 |
+------+------+----------+

-- Select person with the maximum and minimum height
select LastName, FirstName, Height from Person
    where height = (select max(height) from Person)
        or height = (select min(height) from Person);


+----------+-----------+--------+
| LastName | FirstName | Height |
+----------+-----------+--------+
| Turner   | Roxanne   |    155 |
| Robinson | Rick      |    189 |
+----------+-----------+--------+

-- Select persons with a height above average
set @avg = (select Avg(height) from Person);
select LastName, FirstName, Height as Avg from Person
    where Height > @avg
    order by height desc;


+----------+-----------+------+
| LastName | FirstName | Avg  |
+----------+-----------+------+
| Robinson | Rick      |  189 |
| Turner   | Marvin    |  185 |
| Turner   | Peter     |  180 |
| Wilson   | Woodrow   |  174 |
+----------+-----------+------+

-- or
select LastName, FirstName, Height as Avg from Person
    where Height > (select avg(height) from Person)
    order by height desc;


+----------+-----------+------+
| LastName | FirstName | Avg  |
+----------+-----------+------+
| Robinson | Rick      |  189 |
| Turner   | Marvin    |  185 |
| Turner   | Peter     |  180 |
| Wilson   | Woodrow   |  174 |
+----------+-----------+------+
 


-- *******************
-- Joins
-- Zusammenfassungen
-- *******************

-- Query parents of persons
select p1.FirstName,p2.LastName as ParentLastName,
        p2.FirstName as ParentFirstName
    from Person as p1
        inner join Person as p2
    on (p1.Father_Id = p2.id or p1.Mother_Id = p2.id);


+-----------+----------------+-----------------+
| FirstName | ParentLastName | ParentFirstName |
+-----------+----------------+-----------------+
| Peter     | Turner         | Wendy           |
| Peter     | Turner         | Richard         |
| Angie     | Wilson         | Hilary          |
| Marvin    | Turner         | Peter           |
| Marvin    | Turner         | Angie           |
| Roxanne   | Turner         | Peter           |
| Roxanne   | Turner         | Angie           |
| Wendy     | Wilson         | Hilary          |
+-----------+----------------+-----------------+

-- Query which persons are parents and remove duplicates
select distinct p2.LastName as ParentLastName,
        p2.FirstName as ParentFirstName
    from Person as p1
        inner join Person as p2
    on (p1.Father_Id = p2.id or p1.Mother_Id = p2.id);

+----------------+-----------------+
| ParentLastName | ParentFirstName |
+----------------+-----------------+
| Turner         | Wendy           |
| Turner         | Richard         |
| Wilson         | Hilary          |
| Turner         | Peter           |
| Turner         | Angie           |
+----------------+-----------------+

-- Query sibling of a person
select p1.LastName, p1.FirstName
    from Person as p1
        inner join Sibling as s
        inner join Person as p2
    on p1.id = s.from_Id and s.to_Id = p2.id
        and p1.FirstName != 'Marvin';


+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Turner   | Roxanne   |
+----------+-----------+

-- Query message send by one person
select p1.FirstName as 'From',
        p2.FirstName as 'To',
        m.message
    from Person as p1
        inner join Message as m
        inner join Person as p2
    on p1.id = m.from_Id
        and m.to_Id = p2.id
    where (select id from Person where FirstName = 'Marvin');


+--------+--------+------------------------------------------+
| From   | To     | message                                  |
+--------+--------+------------------------------------------+
| Peter  | Angie  | Nice to meet you                         |
| Angie  | Peter  | I also enjoy to meet you                 |
| Marvin | Peter  | My system crashed. Can you help me?      |
| Marvin | Angie  | I'm hungry                               |
| Angie  | Marvin | Just visit MC'Donalds                    |
| Peter  | Marvin | Restart your system                      |
| Marvin | Peter  | Could you help me with GMAT?             |
| Marvin | Peter  | Don't forget me and buy a big mac for me |
+--------+--------+------------------------------------------+

-- Query messages replied to one person
select p2.FirstName as 'From',
        p1.FirstName as 'To',
        m.message
    from Person as p1
        inner join Message as m
        inner join Person as p2
    on p1.id = m.to_Id
        and m.from_Id = p2.id
    where (select id from Person where FirstName = 'Marvin');
 
+--------+--------+------------------------------------------+
| From   | To     | message                                  |
+--------+--------+------------------------------------------+
| Peter  | Angie  | Nice to meet you                         |
| Angie  | Peter  | I also enjoy to meet you                 |
| Marvin | Peter  | My system crashed. Can you help me?      |
| Marvin | Angie  | I'm hungry                               |
| Angie  | Marvin | Just visit MC'Donalds                    |
| Peter  | Marvin | Restart your system                      |
| Marvin | Peter  | Could you help me with GMAT?             |
| Marvin | Peter  | Don't forget me and buy a big mac for me |
+--------+--------+------------------------------------------+
 
-- Query messages sent and replied from one person
-- Abfrage der Meldungen, die von einer Person geschickt und gesendet wurden
select p1.FirstName,
        If(m.from_Id = p1.id,'Sent to','Received from') 'S/R',
        p2.Firstname,
        m.message
    from Person as p1
        inner join Message as m
        inner join Person as p2
    on p1.id = m.to_Id
        and m.from_Id = p2.id
        or p2.id = m.to_Id
        and m.from_Id = p1.id
    where (select id from Person where FirstName = 'Marvin')
    order by p1.LastName, p1.FirstName;


+-----------+---------------+-----------+------------------------------------------+
| FirstName | S/R           | Firstname | message                                  |
+-----------+---------------+-----------+------------------------------------------+
| Angie     | Received from | Peter     | Nice to meet you                         |
| Angie     | Sent to       | Marvin    | Just visit MC'Donalds                    |
| Angie     | Received from | Marvin    | I'm hungry                               |
| Angie     | Sent to       | Peter     | I also enjoy to meet you                 |
| Marvin    | Received from | Peter     | Restart your system                      |
| Marvin    | Sent to       | Peter     | Don't forget me and buy a big mac for me |
| Marvin    | Sent to       | Peter     | My system crashed. Can you help me?      |
| Marvin    | Received from | Angie     | Just visit MC'Donalds                    |
| Marvin    | Sent to       | Peter     | Could you help me with GMAT?             |
| Marvin    | Sent to       | Angie     | I'm hungry                               |
| Peter     | Received from | Marvin    | Could you help me with GMAT?             |
| Peter     | Received from | Angie     | I also enjoy to meet you                 |
| Peter     | Sent to       | Marvin    | Restart your system                      |
| Peter     | Sent to       | Angie     | Nice to meet you                         |
| Peter     | Received from | Marvin    | Don't forget me and buy a big mac for me |
| Peter     | Received from | Marvin    | My system crashed. Can you help me?      |
+-----------+---------------+-----------+------------------------------------------+

-- Query number of messages sent and replied from all persons
-- Abfrage der Anzahl von Meldungen, die von allen Personen gesendet oder empfangen wurden
select p1.FirstName,
        If(m.from_Id = p1.id,'Sent','Received') 'S/R',
        count(*) as 'Messages'
    from Person as p1
        inner join Message as m
        inner join Person as p2
    on p1.id = m.to_Id
        and m.from_Id = p2.id
        or p2.id = m.to_Id
        and m.from_Id = p1.id
    group by p1.FirstName,p1.LastName,m.from_Id = p1.id
    order by p1.LastName, p1.FirstName;


+-----------+----------+----------+
| FirstName | S/R      | Messages |
+-----------+----------+----------+
| Angie     | Sent     |        2 |
| Angie     | Received |        2 |
| Marvin    | Received |        2 |
| Marvin    | Sent     |        4 |
| Peter     | Received |        4 |
| Peter     | Sent     |        2 |
+-----------+----------+----------+

-- Query persons with their full address
-- Abfrage der Personen mit ihrer vollständigen Adresse
select LastName, FirstName, c.Name, c.ZIP, a.Street, a.No
    from Person as p
        inner join City as c
        inner join Address as a
    on p.Address_Id = a.id and a.City_Id = c.id
    order by LastName, FirstName;


+----------+-----------+-----------+-------+----------------+------+
| LastName | FirstName | Name      | ZIP   | Street         | No   |
+----------+-----------+-----------+-------+----------------+------+
| Turner   | Angie     | Rochester | 87656 | Bondstreet     | 13   |
| Turner   | Marvin    | Paris     |  1234 | PlaceDeLetoile | 45   |
| Turner   | Peter     | Rochester | 87656 | Bondstreet     | 13   |
| Turner   | Richard   | Wien      | 52637 | BarneysStreet  | 5    |
| Turner   | Roxanne   | Rochester | 87656 | Bondstreet     | 13   |
| Turner   | Wendy     | Wien      | 52637 | BarneysStreet  | 5    |
| Wilson   | Hilary    | Berlin    | 12987 | ShiningStreet  | 10   |
| Wilson   | Woodrow   | Berlin    | 12987 | ShiningStreet  | 10   |
+----------+-----------+-----------+-------+----------------+------+

-- Query siblings which have the same friends
select concat(L1,' ',F1) as person,concat(L2,' ',F2) as sibling, concat(p.LastName,' ',p.FirstName) as 'common friend' from Friend as f
    inner join ( /* subselect to select all siblings */
        select p1.Lastname as L1, p1.FirstName as F1, p2.LastName as L2, p2.FirstName as F2,s.*
        from Person as p1
            inner join Sibling as s
            inner join Person as p2
        on p1.id = s.from_Id
            and p2.id = s.to_Id
            and p1.id > p2.id) as s /* remove duplicates */
    inner join Person as p /* join to get name of common friend */
        where f.from_Id = s.from_Id and p.id = f.to_Id;


+----------------+---------------+---------------+
| person         | sibling       | common friend |
+----------------+---------------+---------------+
| Turner Roxanne | Turner Marvin | Chen Walter   |
+----------------+---------------+---------------+

Add comment
Note
eMail is hidden and not visible to the public. It's used to inform you about new comments.