COMP 150, Lecture Notes 22, Spring 2002

Lecture #22 (4 April 2002)

Outline:

A very nice website titled, A Gentle Introduction to SQL, is provided by Andrew Cumming of the School of Computing of Napier University in the UK. It provides several nice tutorials allowing you to form your own SQL queries on existing databases (and it goes into far more depth than we will do in this course).

Databases we can play with:

Information on all countries, according to the "CIA World Factbook" from 1995.
(the current Factbook can be found at www.cia.gov/cia/publications/factbook/.)

This information has been placed into a single table titled cia as follows:

table 'cia'

name	region	area	population	gdp
Afghanistan	Asia	652000	25838797	21000000000
Albania	Europe	28748	3490435	5600000000
Algeria	Africa	2381740	31193917	147600000000
.	.	.	.	.
.	.	.	.	.

Here are some practice problems.

Here is a self-test tutorial you can try.

Here are the answers to the tutorial.

Information on movies and their stars, according to the Internet Movie Database from 1997. To avoid duplicaiton, this database design uses three separate tables

table 'movie'

id	title	yr	score	votes
1	Star Wars	1977	8.8	53567
2	Shawshank Redemption, The	1994	9	44974
3	Pulp Fiction	1994	8.6	43993
4	Titanic	1997	7.2	43371
.	.	.	.	.
.	.	.	.	.

table 'actor'

id	name
1	Woody Allen
2	Clint Eastwood
3	Robert De Niro
4	Sean Connery
.	.
.	.

table 'casting'

movieid	actorid	ord
972	588	1
849	588	2
1575	588	3
47	590	4
.	.	.
.	.	.

A more complete explanation of these fields can be found here.

Here is a self-test tutorial you can try. (Please note, we have only covered enough to get you through queries 1a-1b, 2a-2c, 3a-3e. Queries 4a-4e involve operations we have not covered.)

Here are the answers to the tutorial.

comp150 Class Page
mhg@cs.luc.edu

Last modified: 4 April 2002

Lecture #22 (4 April 2002)

"A Gentle Introduction to SQL"