Joins....3

3a. Show the games and color of the medal won by the team that includes 'Yan Sen'.
=> select games, color from ttmdjoin team on ttmd.team = team.idwhere name = 'yan sen'

3b. Show the 'gold' medal winners in 2004.
=> select name from ttmd join team on ttmd.team = team.id where color = 'gold' and games = '2004'

3c. Show the name of each medal winner country 'FRA'.
=> select all name from ttmdjoin team on ttmd.team = team.idwhere country='FRA'

Joins....2

2a. Show who won medals in the 'Barcelona' games
SELECT who FROM ttws
JOIN games ON (ttws.games=games.yr)
WHERE city = 'Barcelona'

2b. Show which city 'Jing Chen' won medals. Show the city and the medal color.
select city, color from
ttws join games on ttws.games = games.yr
where who = 'jing chen'


2c. Show who won the gold medal and the city.
select who,city from
ttws join games on ttws.games = games.yr
where color = 'gold'

Joins....

1a. Show the athelete (who) and the country name for medal winners in 2000.
SELECT who, country.name FROM ttms JOIN country ON (ttms.country=country.id) WHERE games = 2000


1b. Show the who and the color of the medal for the medal winners from 'Sweden'.
SELECT who,color from ttms join country on ttms.country = country.idwhere name = 'sweden'


1c. Show the years in which 'China' won a 'gold' medal.
select games,country from ttms join country on ttms.country = country.idwhere name = 'china' andcolor = 'gold'order by games desc

SELECT within SELECT

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

1b. List the name and region of countries in the regions containing 'India', 'Iran'.
select name ,region from bbc where region in (SELECT region from bbc where name in ('India','Iran'))

1c. Show the European countries with a per capita GDP greater than 'United Kingdom'.
select name from bbc where region = (select region from bbc where name = 'United Kingdom') and gdp/population > (Select gdp/population from bbc where name = 'United Kingdom')

1d. Which country has a population that is more than Canada but less than Algeria?
select name from bbc where population > (Select population from bbc where name = 'canada') and population <(select population from bbc where name = 'algeria')

2a. Which countries have a GDP greater than any European country? [Give the name only.]
select name from bbc where gdp> (select max(gdp) from bbc where region = 'europe')

3a. 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)

BBC Country Profile: Aggregate functions

1a. Show the total population of the world.
Select sum(population) from bbc;

1b. List all the regions - just once each.
Select distinct(region) from bbc;

1c. Give the total GDP of Africa
Select sum(gdp) from bbc
where region = 'Africa';

1d. How many countries have an area of at least 1000000
select count(region) from bbc where area >= 1000000;

1e. What is the total population of ('France','Germany','Spain')
select sum(population) from bbc
where name in('France','Germany','Spain');

GROUP BY

In a GROUP BY statement only distinct values are shown for the column in the GROUP BY.
This example shows the continents hosting the Olympics with the count of the number of games held.

SELECT continent, COUNT(yr) FROM games
GROUP BY continent;

BBC table

name	region	area	population	gdp
Afghanistan	Asia	652225	26000000
Albania	Europe	28728	3200000	6656000000
Algeria	Middle East	2400000	32900000	75012000000
Andorra	Europe	468	64000
Angola	Africa	1250000	14500000	14935000000
Antigua and Barbuda	Americas	442	77000	770000000
Argentina	South America	2800000	39300000	146196000000
Armenia	Europe	29743	3000000	3360000000
Australia	Australasia	7700000	20300000	546070000000
Austria	Europe	83871	8100000	261630000000
Azerbaijan	Europe	86600	8500000
Bahamas	Americas	13939	321000	4789320000
Bahrain	Middle East	717	754000	9357140000
Bangladesh	Asia	143998	152600000	67144000000
Barbados	Americas	430	272000	2518720000
Belarus	Europe	207595	9800000	20776000000
Belgium	Europe	30528	10300000	319609000000
Belize	Americas	22965	266000
Benin	Africa	112622	7100000	3763000000
Bhutan	Asia	38364	2400000	1824000000
Bolivia	Americas	1100000	9100000
Bosnia-Hercegovina	Europe	51129	4200000	8568000000
Botswana	Africa	581730	1800000	7812000000
Brazil	South America	8550000	182800000	564852000000
Brunei	Asia	5765	374000
Bulgaria	Europe	110994	7800000	21372000000
Burkina Faso	Africa	274200	13800000	4968000000
Burma	Asia	676552	50700000
Burundi	Africa	27816	7300000
Cambodia	Asia	181035	14800000	4736000000
Cameroon	Africa	465458	16600000	13280000000
Canada	North America	9900000	32000000	908480000000
Cape Verde	Africa	4033	482000	853140000
Central African Republic	Africa	622984	3900000
Chad	Africa	1280000	9100000	2366000000
Chile	South America	756096	16200000	79542000000
China	Asia	9600000	1300000000	1677000000000
Colombia	Americas	1140000	45600000
Comoros	Africa	1862	812000
Costa Rica	Americas	51100	4300000
Croatia	Europe	56594	4400000	28996000000
Cuba	Americas	110860	11300000
Cyprus	Europe	9250	807000	14187060000
Czech Republic	Europe	78866	10200000	93330000000
Democratic Republic of Congo	Africa	2340000	56000000	6720000000
Denmark	Europe	43098	5400000	219510000000
Djibouti	Africa	23200	721000
Dominica	Americas	751	71000	259150000
Dominican Republic	Americas	48072	9000000
East Timor	Asia	14609	857000
Results truncated...

Simple use of SELECT and WHERE

• 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.

A common mistake made with boolean expressions

Boolean expressions often read like "normal" English however the syntax is not as rich. For example we might say name equals 'France' or 'Germany' however the corresponding boolean expression is name='France' OR name='Germany'

Functions

ABS Function:

ABS returns the absolute value. The output is positive even if the input is negative:

  ABS(x) = x if x>=0
ABS(x) = -x if x<0

ABS can be useful for testing values that are "close". For example this query shows each country that has area that is roughly 70 thousand. The value 70000 is the target value, 500 is the "tolerance" so the test ABS(area-70000)<500 style="" border="1">name area Georgia 69700 Ireland 70182

CASE

CASE allows you to return different values under different conditions.

If there no conditions match (and there is not ELSE) then NULL is returned.

  CASE WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE def_value
END

SELECT name, population
 ,CASE WHEN population<1000000>
name
 
population
 
result
 
 
 
Monaco
 
32000
 
small
 
 
 
Liechtenstein
 
34000
 
small
 
 
 
Bahamas
 
321000
 
small
 
 
 
The Maldives
 
338000
 
small
 
 
 
Brunei
 
374000
 
small
 
 
 
Malta
 
397000
 
small
 
 
 
Armenia
 
3000000
 
medium

Select statements.

Show who won the 1962 prize for Literature.
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'

2) Show the year and subject that won 'Albert Einstein' his prize.
select yr,subject from nobel where winner like 'Albert Einstein';

3)Give the name of the 'Peace' winners since the year 2000, including 2000.
select winner from nobel where subject = 'peace' and yr >= 2000;

4) Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.
select * from nobel where subject = 'Literature' and yr between 1980 and 1989;

5) Show all details of the presidential winners: ('Theodore Roosevelt', 'Woodrow Wilson', 'Jed Bartlet', 'Jimmy Carter')
select * from nobel where winner in ('Theodore Roosevelt', 'Woodrow Wilson', 'Jed Bartlet', 'Jimmy Carter');

6) Show the winners with first name John
select winner from nobel where winner like 'john%';

SQL - Platforms

SQL - MySQL and PostgreSQL

MySQL and PostgreSQL are open source database programs rich in functionality and flexibility. They are often the choice of web developers and small businesses simply because they get the job done for a very reasonable price. Also they will go anywhere and can operate on nearly every operating system available.

SQL - SQL Server

Microsoft's SQL Server is steadily on the rise in the commercial world gaining popularity slowly. This platform has a GUI "Windows" type interface and is also rich with functionality. A free trial version can be downloaded at the Microsoft web site, however it is only available to Windows users.

SQL - DB2 and Oracle

By far the selection of choice for large corporations is either Oracle or DB2. Companies that have large ties to IBM stick to their DB2 software whereas others have made the switch to Oracle. These systems run on personal computers as well as large corporate mainframes.

The Database Model

Dr. Edgar F. Codd was the founding father of what is known as the relational model of databases. In 1970, he published a groundbreaking article "A Relational Model of Data for Large Shared Data Banks." Included within the article were 12 rules of relational databases. These rules are as follows (paraphrased).

1. Information Rule
   All data in the database should be represented as values in tables.
2. Guaranteed Access
   Every piece of data should be accesible by using the table name, primary key, and a column name.
3. Treatment of NULL Values
   Null values must be treated as incomplete data pieces. Nulls are not to be confused with zeros.
4. Active-Online Relational Catalog
   A database is represented at a logical level of tables.
5. Sublanguage
   Having one supported language with a well-defined syntax.
6. Updating Views
   All views should be updated through the system.
7. Set-level Insertion, Update, and Deletion
   System must support set-time insert, update, and deletion operations.
8. Data Independence (Physical)
   Alterations to the way data is stored must not alter the application itself.
9. Data Independence (Logical)
   Altering tables, columns, and/or rows must not alter the application itself.
10. Integrity Independence
    The language of the database must define integrity rules.
11. Distribution Independence
    Distributing the database to numerous locations should be anonymous and existing applications should be unaffected.
12. Nonsubversion
    If the system uses a low level interface to record data, then there must be a higher level interface used when administrating.

The largest of corporations follow these rules of cataloging information to this very day.

Four Principles of Database Design

SQL - Four Principles of Database Design

When designing and implementing a database, keep in mind these four guidelines.

Atomicity:

Your coded statements flow without the constant need to update or "fix" your data.

Consistency:

Your statements are either executed 100% or fail 100%, do not implement code that partially works.

Isolation:

Keep data files and logs away from public eyes, and limit the number of users with administration access to your database.

Durability:

Maintain reliable servers with plenty of storage space and back-up systems that save transactions immediately.