Databases

Databases and SQL: 

Databases and SQL Structuring your world! Project Challenge 2007

Overview: 

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: 

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?: 

What is a database? Database a collection of related records, organized into tables Table a set of data elements (values) organized by records (horizontal rows) and fields (vertical columns) Record (or row) 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: 

A database table R e c o r d s F i e l d s

How are databases used?: 

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: 

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 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: 

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?: 

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: 

Adding records to a table Use the INSERT query: Example: INSERT INTO friends VALUES (‘Daffy’, ‘Duck’,‘M’,11,’Brunette’,‘daffy@disney.com’, YellowBill’, ’315-555-1213’);

Editing records : 

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: 

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?: 

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?: 

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?: 

What are relational databases?

Relations, primary keys, joins: 

Relations, primary keys, joins

Querying with JOIN: 

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: 

Querying with JOIN Try all the SELECT exercise queries on the Internet Movie Database at: http://sqlzoo.net/3.htm

Questions?: 

Questions?