210 likes | 370 Views
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.
E N D
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 • 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 F i e l d s Records
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_nameFROM presidentWHERE 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’,‘daffy@disney.com’,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
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’
Querying with JOIN • Try all the SELECT exercise queries on the Internet Movie Database at:http://sqlzoo.net/3.htm
Questions? That's all, folks!!!