Advanced Searching by Raymond (Ray) Montalban

1. Advanced SearchingbyRaymond (Ray) Montalban Relational Databases + SQL Go to: www.mr-montalban.comResources Relational Databases to begin

2. Programme • Discover how relationships between tables can simplify data access • Understand schemas • Understand queries and construct SQL statements • Discover the relationship between relational databases, SQL and life

3. What is the problem here?

4. Starter • You will be given large sheets of paper with tables ‘floating’ around. • In groups of two, draw lines (relationships or links) between the tables using these rules • One field in one table can link to many tables, but only on one field per table. • One field can link to a field in another table

5. Schemas I • A schema is a diagram that shows the relationships or links between entities, which in this case are tables. • Two types of fields • PK: Primary Key – a field used to identify each record as unique • FK: Foreign Key – a field used to join tables together • Three relationship rules • 1 - ∞ = PK – FK • 1 – 1 = PK – PK • ∞ - ∞ = FK – FK

6. Schemas II Students • Class Roll/Register How many times do we see 001A? Students StudentID PK FirstName LastName DateOfBirth Class ClassID PK SubjectName TeacherName 1 1 Class Register Lesson PK StudentID (FK) ClassID (FK) ∞ ∞

7. Queries I • A query is a search for data or information matching a pattern, condition or constraint. • Examples • Displaying all the birthdays of the students for today • Alerting parents when school fees are due • Methods • Query By Example (QBE) • Structured Query Language (SQL)

8. Queries II • SQL is an industry standard that can be used to query data from nearly all database systems currently being used. • Basic structure SELECT FROM WHERE • Explanation • SELECT lists the tables you want to see at the end • FROM specifies which data sets or tables you want to pull the data from • WHERE specifies the conditions or constraints of the query

9. Queries III Schema SQL Questions Display the names of the students who were born on the 2/2/2001SELECTFirstName, LastNameFROM StudentsWHEREDateOfBirth=2/2/2001; Display the subject name taught in a class with ID D205. Students StudentID PK FirstName LastName DateOfBirth Class ClassID PK SubjectName TeacherName 1 1 Register Lesson PK StudentID (FK) ClassID (FK) ∞ ∞

10. Queries IV Schema SQL Questions Display the subject names and teachers for ‘Ray’SELECTClass.SubjectName, Class.TeacherNameFROM Students, Register, ClassWHEREStudents.FirstName = ‘Ray’ANDStudents.StudentID = Register.StudentIDANDRegister.ClassID = Class.ClassID; Students StudentID PK FirstName LastName DateOfBirth Class ClassID PK SubjectName TeacherName 1 1 Register Lesson PK StudentID (FK) ClassID (FK) ∞ ∞

11. Queries VWhat is happening in Question 3? Students Class 1 1 SELECTClass.SubjectName, Class.TeacherNameFROMStudents, Register, ClassWHEREStudents.FirstName = ‘Ray’ ∞ ∞ Register AND Students.StudentID = Register.StudentID AND Register.ClassID= Class.ClassID;

12. Queries VI Schema SQL Questions Display the names of all the students in the class with an ID of D205. In what lesson does ‘Ray’ NOT see Maggie and does NOT see Rowan? Students StudentID PK FirstName LastName DateOfBirth Class ClassID PK SubjectName TeacherName 1 1 Register Lesson PK StudentID (FK) ClassID (FK) ∞ ∞

13. Queries VII SQL Questions Solutions SELECT Students.FirstName, Students.LastNameFROM Students, Register, ClassWHERE Students.StudentID=Register.StudentIDAND Register.ClassID=Class.ClassIDAND Class.ClassID=“D205”; • Display the names of all the students in the class with an ID of D205.

14. Queries VIII SQL Questions Solutions SELECT LessonFROM Students, Register, ClassWHERE Student.StudentID = Register.StudentIDAND Register.ClassID = Class.ClassIDAND NOT Class.TeacherName=‘Maggie’AND NOT Class.TeacherName=‘Rowan’; • In what lesson does ‘Ray’ NOT see Maggie and does NOT see Rowan?

15. Into the deep • Go back to www.mr-montalban.com Resources SQL • Choose a set of exercises to complete then press ‘Submit’. • Complete the exercises on the page, then click ‘Check Answers’ at the bottom. • Correct answers will produce results from the database as shown in the given schema. Incorrect answers will be shown with the correct ones.