1 / 20

Databases and SQL

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.

rupali
Télécharger la présentation

Databases and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

More Related