SQL: Revision

Derek Bridge

Department of Computer Science,
University College Cork

SQL: Revision

Aims:

• to revise the SQL SELECT, INSERT, DELETE and UPDATE commands
• to learn how to do joins in SQL, the most important kind of SQL query

Relational databases

• A database is a repository of integrated data
• A relational database organises the data into tables
• Mathematically speaking, the tables are n-ary relations
• SQL (Structured Query Language) is a 'standard' language for defining and manipulating relational databases
  • it is a data definition language - it has commands for creating and deleting tables
  • it is a data manipulation language - it has commands for inserting rows into tables, deleting rows and retrieving rows
• Here we see the version of SQL used by MySQL (one of the most popular database management systems)

An example database

Nerdland Bird Sanctuary operates a Fund-A-Fowl scheme whereby members of the public can help pay for the birds' upkeep.

Data definition

CREATE TABLE birds 
(
    bnum int AUTO_INCREMENT,
    name varchar(30),
    species varchar(25),
    PRIMARY KEY (bnum)
);

CREATE TABLE sponsors 
(
    snum int AUTO_INCREMENT,
    name varchar(30),
    address varchar(25),
    PRIMARY KEY (snum)
);

CREATE TABLE donations 
(
    snum int,
    bnum int,
    donation int,
    PRIMARY KEY(snum, bnum)
);

Data manipulation: insertion and deletion

1. Inserting a new sponsor (sponsor number 4): Mary Christmas from Galway

   INSERT INTO sponsors (name, address)
       VALUES ('Mary Christmas', 'Galway');

2. Deleting the newest sponsor (sponsor number 4)

   DELETE FROM sponsors
       WHERE snum = 4;

Data manipulation: updates

1. Changing Donna Kebab's address to Limerick

   UPDATE sponsors 
       SET   address = 'Limerick'
       WHERE snum = 2;

2. Increasing sponsor 3's donation for bird 4 by 100 eurines

   UPDATE donations
       SET   donation = donation + 100
       WHERE snum = 3 
       AND   bnum = 4;

Data manipulation: retrieval from single table

1. Get all attributes of all sponsors who live in Dublin

   SELECT * 
       FROM  sponsors 
       WHERE address = 'Dublin';

2. Get the identification numbers of sponsors who have donated less than 300 eurines for the up-keep of a bird (for each such bird)

   SELECT snum 
       FROM  donations 
       WHERE donation < 300;

3. Get the identification numbers of sponsors who have donated less than 300 eurines for the up-keep of a bird

   SELECT DISTINCT snum 
       FROM  donations
       WHERE donation < 300;

Data manipulation: retrieval from single table exercises

Write SQL queries to retrieve the following:

1. the identification numbers of all the ducks
2. the names of all the ducks
3. the names of all ducks and swans (Warning! English can mislead!)
4. the identification numbers of all birds for which sponsor 1 has made a donation that exceeds 200 eurines

Data manipulation: retrieval from single table exercises

1. Paraphrase into English the following SQL query:

   SELECT bnum, sum(donation)
       FROM     donations
       GROUP BY bnum
       HAVING   sum(donation) > 300;

2. Write an SQL query to retrieve the identification numbers of all birds who have more than one sponsor

Data manipulation: retrieval from multiple tables exercises

Paraphrase into English the following SQL queries:

1. SELECT snum, bnum
       FROM sponsors, birds;

2.         
   SELECT DISTINCT name 
       FROM  sponsors, donations 
       WHERE sponsors.snum = donations.snum;

3.             
   SELECT sponsors.snum, name 
       FROM  sponsors, donations 
       WHERE sponsors.snum = donations.snum 
       AND   bnum = 4;

4.     
   SELECT sponsors.snum, sponsors.name, birds.bnum, birds.name 
       FROM  sponsors, donations, birds
       WHERE sponsors.snum = donations.snum  
       AND   donations.bnum = birds.bnum;

Data manipulation: retrieval from multiple tables exercises

Write an SQL query to retrieve the following: the identification number, name and address of sponsors who have donated money for swans

Data manipulation: retrieval from multiple tables exercises

Write an SQL query to retrieve the following: the species that Dubliners haven't sponsored