New pages

Most visited

Learning SQL by example

Details
Category: Database
Also available:  German (DE) 
Hits: 7314
Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

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 --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 ***

 Comments are welcome. But in order to reject spam posts please consider following rules:
  1. Comments with string http are rejected with message You have no rights to use this tag
  2. All comments are reviewed by hand and thus it usually takes one day until a comment will be published.