Introduction to SQL

Derek Bridge

Department of Computer Science,
University College Cork

Introduction to SQL

Aims:

• to revise databases (introduced in CS1105)
• to learn some SQL for creating tables
• to learn some SQL for inserting data into tables
• to learn some SQL for retrieving data from tables

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

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

Data definition: databases

• To create a database

  CREATE DATABASE database_name;

  (We've done this for you!)
• To delete a database

  DROP DATABASE database_name;

  (Don't do this to the one we created for you!)
• The commands (CREATE, DROP, etc.) are not case-sensitive, but your table and column names are

Data definition: creating and deleting tables

• To create a table

  CREATE TABLE table_name (
  column_name1 data_type,
  column_name2 data_type,
  ...
PRIMARY KEY (column_names) );

  • Data types include int and varchar(n) (strings of max. length n)
  • One or more columns will form the primary key: the database management system will not allow two rows to have duplicate values for the primary key
• To delete a table

  DROP TABLE table_name;

Data definition: example

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

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

Data manipulation: insertion and deletion

• To insert a row into a table

  INSERT INTO table_name (column_name1, column_name2, ...)
  VALUES (value1, value2, ...);

• To delete rows from a table, e.g.

  DELETE FROM table_name
  WHERE column_name = some_value;

Data manipulation: example

INSERT INTO birds (name, species)
 VALUES ('Stevie', 'swan');

INSERT INTO birds (name, species)
 VALUES ('Mandy', 'moorhen');

INSERT INTO donations (snum, bnum, donation)
 VALUES (100, 1, 256);

• Common to use single quotes for strings but MySQL allows double quotes too
• There is also an UPDATE command for changing values in rows

Data manipulation: retrieval

• To retrieve rows from a table

  SELECT column_names
  FROM table_names
  WHERE conditions

  These are called queries
• Use SELECT * FROM... to retrieve all columns
• Use SELECT DISTINCT column_names FROM ... to return only distinct values
• Conditions use =, <>, <, <=, >, >=, ...
• Combined conditions using AND and OR

Data manipulation: exercises

Paraphrase into English the following SQL queries:

SELECT * FROM sponsors 
 WHERE address = 'Dublin';

SELECT snum FROM donations 
 WHERE donation < 300;

SELECT bnum FOM donations
 WHERE donation < 300;

Data manipulation: 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 identification numbers of all birds where the sponsor is number 100 and the sponsorship amount exceeds 200
4. the names of all ducks and swans (Warning! English can mislead!)

Data manipulation: retrieval

• We often want to combine the data from more than one table
• Cartesian product: pairing each row from sponsors with each row from donations (in that order):

  SELECT * FROM sponsors, donations;


• Natural join: combining the tables like Cartesian product but binding them together through a shared column:

  SELECT * FROM sponsors, donations;
  WHERE sponsors.num = donations.snum;

Data manipulation: exercises

Paraphrase into English the following SQL queries:

SELECT name FROM sponsors, donations 
 WHERE sponsors.num = donations.snum;

SELECT DISTINCT name FROM sponsors, donations 
 WHERE sponsors.num = donations.snum 
   AND bnum = 5;

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

Data manipulation: exercises

Write SQL queries to retrieve the following:

• the addresses of sponsors who have donated money for swans