Computer Science 101 Web Access to Databases

1. Computer Science 101Web Access to Databases SQL – Basic Queries

2. SQL Introduction • Structured Query Language • Originally called SEQUEL from Structured English QUEry Language • SQL has a standard • SQL provides both • Data Definition Language (DDL) • Data Manipulation Language (DML) • DBMS may provide higher level approach, but SQL is important for tougher queries, using host programming or web programming.

3. SQL - Basic Queries • Basic form of SQL query: SELECT <attribute list> FROM <table list> [WHERE <condition>] • <attribute list> would be a list of columns separated by commas, etc. • [ ] enclose optional clauses • SQL is not case sensitive

4. SQL - Basic Queries -Conceptually • SELECT <attribute list> FROM <table list> [WHERE <condition>] • Step 1: Make “long” rows by combining rows from the various tables in FROM clause in every possible way – each row in first table with each row in second table with each row in third table, etc. • Step 2: Keep the long rows that satisfy WHERE condition. • Step 3: Keep only columns specified in SELECT.

5. SQL - Basic Queries • Example: Pose a query to get the names of all students: SELECT LastName, FirstName FROM Students

6. SQL - Basic Queries (cont.) • Names of freshmen students

7. SQL - Basic Queries (cont.) • Names of students from Fredericksburg

8. SQL - Basic Queries (cont.) • Names of students from Fredericksburg

9. SQL - Basic Queries (cont.) • Names of students from Fredericksburg

10. SQL - Basic Queries (cont.) • Dump the Students table: SELECT * FROM Students • Get names of departments having majors in our database • SELECT Department FROM Majors • SELECT DISTINCT Department FROM Majors

11. SQL - Basic Queries –Joining tables based on foreign key • Connecting students with their advisors: we need to have AdvisorID = FacultyID Students(StudentID,FirstName, LastName, ClassYear, City, State, Zip, BirthDate, AdvisorID, Term) Faculty (FacultyID, FirstName, LastName, Phone, EMail)

12. SQL - Basic Queries –Joining tables based on foreign key • Connecting majors with their department chairs: we need to have ChairID = FacultyID Majors(MajorID, MajorName, Department, ChairID) Faculty (FacultyID, FirstName, LastName, Phone, EMail)

13. SQL - Basic Queries –Joining tables • Student last name, advisor last name • OR (aliasing)

14. SQL - Basic Queries –Joining tables

15. SQL - Basic Queries –Joining tables based on intermediate table • Connecting students with their interests:we need to have Students.StudentID = StudentInterest.StudentID AND StudentInterest.InterestID = Interests.InterestID Students(StudentID,FirstName, LastName, ClassYear, City, State, Zip, BirthDate, AdvisorID, Term) StudentInterest(StudentID, InterestID) Interests(InterestID, InterestName, Category, URL)

16. SQL - Basic Queries –Joining tables based on intermediate table • Connecting students with majors:we need to have Students.StudentID = StudentMajor.StudentID AND StudentMajor.MajorID = Majors.MajorID Students(StudentID,FirstName, LastName, ClassYear, City, State, Zip, BirthDate, AdvisorID, Term) StudentMajor(StudentID, MajorID) Majors(MajorID, MajorName, Department, ChairID)

17. SQL - Basic Queries –Joining tables (cont.) • Student last name, first name, name of NFL team student is interested in.

18. SQL - Basic Queries –Joining tables (cont.)