Transcript Databases and SQL - Clarkson University

Databases and SQL
Structuring your world!
Project Challenge 2007
Overview



What is a database?
How are databases used?
Database queries



U.S. presidents
BBC country profiles
How to create a DB table?



Adding records to a table
Editing records
Deleting records from a table
Overview

How to use a DB table?



Finding a date for Saturday night
Nobel Prize winners: http://sqlzoo.net/1b.htm
What are relational databases?



Relations, primary keys and joins
Querying a relational database using JOIN
Internet Movie Database: http://sqlzoo.net/3.htm
What is a database?

Database


Table


a set of data elements (values) organized by records
(horizontal rows) and fields (vertical columns)
Record (or row)


a collection of related records, organized into tables
a single, structured data item in a table
Field (or column)

a set of data values of a particular simple type, one for
each row of the table
A database table
Fields
R
e
c
o
r
d
s
First name
Last name
State
Date of birth
John F.
Kennedy
MA
5/29/1917
Lyndon B.
Johnson
TX
8/27/1908
Richard M.
Nixon
CA
1/9/1913
Gerald R.
Ford
NE
7/14/1913
James E.
Carter
GA
10/1/1924
Ronald W.
Reagan
IL
2/6/1911
George H.W.
Bush
MA
6/24/1924
William J.
Clinton
AR
8/19/1946
George W.
Bush
CT
7/6/1946
How are databases used?

Computer databases allow users to






Add,
Edit, and
Delete records;
Extract records using specific criteria; and to
Extract aggregate data from collections of records
Database transactions are accomplished
through queries
Database queries

SELECT queries are used to extract
information from a database:
Example:
SELECT first_name, last_name
FROM president
WHERE state = ‘NY’;
More database queries

More examples
SELECT first_name, last_name
FROM president
WHERE birth_date like ’19%’;
SELECT count(*) FROM president
WHERE state = ‘VT’;
SELECT first_name, last_name
FROM president
WHERE state IN (ME,NH,VT,MA,CT,RI);
Still more database queries

Try all the SELECT exercise queries on the
BBC Country Profiles database at:
http://sqlzoo.net/1.htm
How to create a DB table?

Use CREATE TABLE query, specifying fields and column types:
Example:
CREATE TABLE friends (
first_name varchar(25),
last_name varchar(25),
gender enum(‘M’, ‘F’),
grade smallint unsigned,
hair_color varchar(20),
email varchar(30),
screen_name varchar(25),
phone char(12));
Adding records to a table

Use the INSERT query:
Example:
INSERT INTO friends VALUES (‘Daffy’,
‘Duck’,‘M’,11,’Brunette’,‘[email protected]’,
YellowBill’, ’315-555-1213’);
Editing records

Use the UPDATE query:
Example:
UPDATE friends
SET phone=‘315-555-1234’
WHERE first_name=‘Daffy’ and
last_name = ‘Duck’;
Deleting records from a table

Use the DELETE query:
Example:
DELETE FROM friends
WHERE first_name=‘Daffy’ and
last_name=‘Duck’;
How to use a DB table?

Finding a date:
SELECT first_name, screen_name, phone
FROM friends
WHERE gender=‘F’ and
grade > 10 and
hair_color=‘Red’;
How to use a DB table?

Try all the SELECT exercise queries on the
Nobel Laureates database at:
http://sqlzoo.net/1b.htm
What are relational databases?
Relations, primary keys, joins
id
name
2359 Humphrey Bogart
id
title
1969 Casablanca
1920 The Big Sleep
movieid actorid ord
1969
2359
1
1920
2359
1
Querying with JOIN
SELECT name
FROM casting
JOIN actor ON casting.actorid=actor.id
JOIN movie ON casting.movieid=movie.id
WHERE actor.name = ‘Humphrey Bogart’
Querying with JOIN

Try all the SELECT exercise queries on the
Internet Movie Database at:
http://sqlzoo.net/3.htm
Questions?