Databases and SQL

1. Databases and SQL Structuring your world! Project Challenge 2007

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

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

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

5. A database table F i e l d s Records

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

7. Database queries • SELECT queries are used to extract information from a database:Example:SELECT first_name, last_nameFROM presidentWHERE state = ‘NY’;

8. 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);

9. Still more database queries • Try all the SELECT exercise queries on the BBC Country Profiles database at:http://sqlzoo.net/1.htm

10. 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));

11. 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’);

12. Editing records • Use the UPDATE query: Example: UPDATE friends SET phone=‘315-555-1234’WHERE first_name=‘Daffy’ and last_name = ‘Duck’;

13. Deleting records from a table • Use the DELETE query: Example: DELETE FROM friends WHERE first_name=‘Daffy’ and last_name=‘Duck’;

14. 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’;

15. How to use a DB table? • Try all the SELECT exercise queries on the Nobel Laureates database at:http://sqlzoo.net/1b.htm

16. What are relational databases?

17. Relations, primary keys, joins

18. Querying with JOIN SELECT name FROM casting JOIN actor ON casting.actorid=actor.id JOIN movie ON casting.movieid=movie.idWHERE actor.name = ‘Humphrey Bogart’

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

20. Questions? That's all, folks!!!