Introduction to Databases Queries

1. Introduction to Databases Queries CS 146

2. Sample Database: CANDY_CUSTOMER CANDY_PURCHASE CANDY_CUST_TYPE CANDY_PRODUCT

3. Field: column of similar data values Record: row of related fields Table: set of related rows Basic Database Vocabulary Record Field

4. Sidenote: Database History • All pre-1960’s systems used file-based data • First database: Apollo project • Goal: to not store duplicate data in multiple locations • Used a hierarchicalstructure • Created relationships using pointers • Pointer: hardware address

5. Example Hierarchical Database UniversityStudent UniversityCourse *Pointer – physical location (as a number) to the start of the referenced data

6. Problems with Hierarchical Databases • Relationships are all one-way; to go the other way, you must create a new set of pointers • Pointers are hardware/hard drive-specific • VERY hard to move to new hardware • Applications must be custom-written • Usually in COBOL

7. Relational Databases • Circa 1972 • E.J. Codd • “Normalizing” relations • Store data items only once • With the exception that foreign keys can be duplicated • Stores data in a tabular format • Creates relationships through sharing key fields

8. Key Fields • Primary key: uniquely identifies a record UniversityInstructor Primary keys

9. Class Discussion • What is the primary key of each table in the CANDY database? • How can you tell if a field is a primary key?

10. Special Types of Primary Keys • Composite PK: made by combining 2 or more fields to create a unique identifier • Consider the CANDY_PURCHASE table… • Surrogate PK: ID generated by the DBMS solely as a unique identifier

11. Duplication Considerations • When data values appear multiple times, there is duplication • Problems: • Space • Data becomes inconsistent over time UniversityStudent

12. Key Fields (continued) • Foreign key • Field that is a primary key in another table • Serves to create a relationship UniversityInstructor Primary keys Foreign keys UniversityStudent

13. Class Discussion • What are the foreign keys in the CANDY database? • Does a table HAVE to have foreign keys? • When would you use them? • How can you tell if a field is a foreign key?

14. Rules for Relational Database Tables (non-negotiable) • Every record has to have a non-NULL and unique PK value • Every FK value must be defined as a PKin its parent table

15. Structure of a Database Client Workstations Database Server DB Data DBMS

16. Database Structure • A database consists of multiple user accounts • Your area in the database is called your user schema • Identified by your username and password • Each user schema contains database objects that you create • Tables • Views • Stored programs • Etc.

17. Query Browser • Example: Oracle SQL developer

18. Database Queries • Query: command to perform an operation on a database object • Create • Insert • Modify • View • Delete • Structured Query Language (SQL) • Standard query language for relational databases

19. MySQL Query Browser Type query: Click Execute:

20. Query Conventions • Not case-sensitive • Convention: reserved words in all-caps, user-supplied values (table names, field names, etc.) in lower-case letters • Queries can span multiple lines • Semi-colon marks the end of a line

21. Retrieving Data From a Single Table • Syntax: • SELECT column1, column2, … • FROM schema.tablename • WHERE search_condition • SELECT candycust_id, candycust_name • FROM candy_customer • WHERE cust_id = 1

22. Retrieving all Fields or Records • To retrieve all fields in the table: use the "*" wildcard character • To retrieve all records in a table: omit the search condition SELECT * FROM tablename WHERE search_condition

23. How many fields and how many records will the following query retrieve? • 7 fields and 14 records • 14 fields and 7 records • 7 fields and 9 records • None of the above SELECT * FROM candy_purchase;

24. Search Conditions • General format: FieldName Operator TargetValue • Operators: =, <, >, <=, >=, <> or != • Examples: • PROD_ID = 1 • POUNDS > 5 • STATUS != 'PAID'

25. Search Conditions (continued) • Number: just type the number • Text string: • Case-sensitive • Enclose in single quotes • Date: • Enter as a text string in ‘dd-mon-yy' format: WHERE purch_date = ‘28-Oct-04’

26. Which records will the following query retrieve? • Purch_id values 2, 3, 5, 7, 8 • Purch_id values 2, 7, 8, 9 • Purch_id values 2, 7, 8 • None of the above SELECT * FROM candy_purchase WHERE pounds >= 5

27. Which records will the following query retrieve? • Purch_id values 1, 2, 3, 4, 6, 8 • Purch_id values 5, 7, 9 • All purch_id records will be returned • No purch_id records will be returned • An error will occur SELECT * FROM candy_purchase WHERE status = 'Paid'

28. Searching for NULL Values • NULL: undefined • Search conditions for NULL and non-NULL values: WHERE column_name IS NULL WHERE column_name IS NOT NULL

29. Combining Multiple Search Conditions • AND: query only retrieves records for which both conditions are true WHERE Condition1 AND Condition2 • OR: query retrieves records for which either condition is true WHERE Condition1 OR Condition2

30. Using AND and OR in Search Conditions • Every expression must be well-formed: • Do this: • Not this: WHERE purch_date > ‘28-Oct-04' AND purch_date < ‘1-Nov-04’ WHERE purch_date > ’28-Oct-04' AND < ‘1-Nov-04'

31. Which records will the following query retrieve? • Purch_id values 4, 6, 8 • Purch_id values 1, 2, 3, 4, 5, 9, 12, 13 • Purch_id values 1, 2, 3, 4, 5, 9, 10, 11, 12, 13 • None of the above SELECT * FROM candy_purchase WHERE delivery_date IS NULL AND status = 'PAID'

32. Which records will the following query retrieve? • Purch_id values 1, 2, 3, 4, 6, 7, 8, 12 • Purch_id values 1, 2, 3, 4, 12 • Purch_id values 1, 2, 3, 4, 5, 9, 12, 13 • None of the above SELECT * FROM candy_purchase WHERE delivery_date = NOT NULL AND status = 'PAID'