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 names of all ducks and swans (Warning! English can mislead!)
2. 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

1. Compare this:

   SELECT *
       FROM sponsors JOIN donations;

2. with this:

   SELECT * 
       FROM sponsors JOIN donations 
       ON   sponsors.snum = donations.snum;

Data manipulation: retrieval from multiple tables exercises

Paraphrase into English the following SQL queries:

1.             
   SELECT sponsors.snum, name 
       FROM  sponsors JOIN donations 
       ON    sponsors.snum = donations.snum 
       WHERE bnum = 4;

2.     
   SELECT sponsors.snum, sponsors.name, birds.bnum, birds.name 
       FROM sponsors JOIN donations JOIN birds
       ON   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

Hint: subquery