SQL gentle tutorials

Exercises

Using the SELECT statement.
1a. SELECT name, region, population FROM bbc.
SELECT population FROM bbc
  WHERE name = 'France'
SELECT name, population/area FROM bbc
  WHERE area > 5000000
SELECT name , region
  FROM bbc
  WHERE area < 2000
    AND gdp > 5000000000

Which of Ceylon, Iran, Persia and Sri Lanka is the name of a country?
The word IN allows us to check if an item is in a list.
SELECT name FROM bbc
  WHERE name IN ('Sri Lanka', 'Ceylon',
                 'Persia',    'Iran')

What are the countries beginning with D?
The word LIKE permits pattern matching - % is the wildcard.

SELECT name FROM bbc
  WHERE name LIKE 'D%'

Which countries are not too small and not too big?
BETWEEN allows range checking - note that it is inclusive.

SELECT name, area FROM bbc
  WHERE area BETWEEN 207600 AND 244820

Simple use of SELECT and WHERE

These statements take the form :

SELECT attribute-list
   FROM table-name
   WHERE condition

• attribute-list
• • This is usually a comma separated list of attributes (field names)
  • Expressions involving these attributes may be used. The normal mathematical operators +, -, *, / may be used on numeric values. String values may be concatenated using ||
  • To select all attributes use *
  • The attributes in this case are: name, region, area, population and gdp
• table-name
  • In these examples the table is always bbc.
• condition
• • This is a boolean expression which each row must satisfy.
  • Operators which may be used include AND, OR, NOT, >, >=, =, <, <=
  • The LIKE operator permits strings to be compared using 'wild cards'. The symbols _ and % are used to represent a single character or a sequence of characters. Note that MS Access SQL uses ? and * instead of _ and % .
  • The IN operator allows an item to be tested against a list of values.
  • There is a BETWEEN operator for checking ranges.

List each country name where the population is larger than 'Russia'.
SELECT name FROM bbc
  WHERE population>
     (SELECT population FROM bbc
      WHERE name='Russia')

Find the largest country in each region:
SELECT region, name, population FROM bbc x
  WHERE population >= ALL
    (SELECT population FROM bbc y
        WHERE y.region=x.region
          AND population>0)

You can put two or more strings together using the concatentate operator. The SQL standard says you should use || but there are many differences between the main vendors.
SELECT region || name
  FROM bbc

List a number of SELECT statements separated by the UNION key word. Be sure that you have the same number of columns in each of the SELECT statements.

SELECT name FROM customer
UNION
SELECT name FROM  employee
UNION 
SELECT name FROM artist


SELECT name FROM actor WHERE name LIKE 'Z%'
UNION
SELECT title FROM movie WHERE title LIKE 'Z%'



-------

The LIKE command allows "Wild cards". A % may be used to match and string, _ 
will match any single character.
The example shows countries begining with Z. The country Zambia matches 
because ambia matches with the %.
SELECT name FROM bbc
WHERE name LIKE 'Z%'

---
The LIKE command is case sensitive - to do a case insensitive search you should force 
the searched field and the search term into lower (or upper) case. 
Without this we would miss "Bahamas, The"
SELECT name FROM scott.cia
WHERE lower(name) LIKE '%the%'
----
Where one of the results returned is calculated (for example with an aggregate) the column name may be assigned arbitrarily
You should be able to specify a column name.

SELECT region, SUM(population) AS x
  FROM cia
GROUP BY region
------------
I am trying to join two tables with the same name. 
I am required to use an inner join Also I need to show the managers name. 
Question is: Join the employee table with the employee table and department table. 
Show the employee id, name, dept code, manager id, manager name, managers department code, 
and name for that (mgr's) department. 
We have a self join, each copy of the table is given an "alias" 
- here we 
use w for the worker and b for the boss. We can treat these as different 
tables.
You get an inner join by default 
- this means that Robin (who has no boss) 

does not show up in the results.
----
CREATE TABLE employee(
  employee_id INTEGER PRIMARY KEY,
  first_name VARCHAR(10),
  dept_code VARCHAR(10),
  manager_id INTEGER REFERENCES employee);
INSERT INTO employee VALUES (1,'Robin','Eng',NULL);
INSERT INTO employee VALUES (2,'Jon','SoC',1);
INSERT INTO employee VALUES (3,'Andrew','SoC',2);
INSERT INTO employee VALUES (4,'Alison','SoC',2);
SELECT w.first_name as worker, b.first_name as boss
 FROM employee w, employee b
WHERE w.manager_id = b.employee_id
-------------
You can access columns where the name contains a space.
CREATE TABLE SpaceMonster("Account Balance" INT);
INSERT INTO SpaceMonster VALUES (42);
SELECT "Account Balance" FROM SpaceMonster
-----