1 / 15

CT 100 Week 7

CT 100 Week 7. SQL Queries. Quiz 7 Vocabulary. Logic Gate An electronic device that implements a Boolean Operator Relational Database A database in which all the data is stored in relations. Relations are commonly thought of as tables with rows and columns. SQL

falala
Download Presentation

CT 100 Week 7

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. CT 100 Week 7 SQL Queries

  2. Quiz 7 Vocabulary • Logic Gate • An electronic device that implements a Boolean Operator • Relational Database • A database in which all the data is stored in relations. Relations are commonly thought of as tables with rows and columns. • SQL • The standard relational database language. It can be used to specify the structure of the database, to query the database and to modify the contents of the database.

  3. Quiz 7 Vocabulary • Primary key • One or more columns that uniquely identify each row in a table • Foreign key • One or more columns in one table the references the primary key in another table

  4. Quiz 7 Vocabulary • Referential Integrity • A foreign key value is either null or its value appears in the referenced table • Cartesian Product of Tables • The Cartesian product of two tables is a new table containing every row of the first table combined with every row of the second table • Join of Tables • The Join of two tables is a new table in which rows in the first table are combined with rows in the second table based on a common column. The common column does not have to have the same name in both tables and usually represents a primary key/foreign key relationship

  5. Quiz 7 Problems • Implement a Boolean expression with AND, OR and NOT gates • Show the result of SQL queries for the student/faculty database • Write SQL queries for the student/faculty database

  6. Example Faculty Table

  7. Example Student Table

  8. Advisor Relationship

  9. Select Faculty.fid, Student.sid, Student.advisorFrom Faculty, Student

  10. Select Faculty.fid, Student.sid, Student.advisor From Faculty, StudentWhere Faculty.fid = Student.advisor

  11. Select Student.first, Student.last from Faculty, Student where Faculty.fid = Student.advisor and Faculty.department = Student.major1

  12. Select Student.first, Student.last from Faculty, Student where Faculty.fid = Student.advisor and (Faculty.department = Student.major1 or Faculty.department = Student.major2)

  13. Practice Problems • What is the result of the following SQL queries? • Select Student.first, Student.last from Faculty, Student where fid = advisor and department != major1 and department != major2 • Select Faculty.first, Faculty.last from Faculty, Student where fid = advisor and department != major1 and department != major2

  14. Practice Problems • Write SQL select statements to answer the following queries? • Find the majors of students whose first name is Jan. • Find the first and last names of faculty in the Classics department. • Find the department of the advisors of students named Jan.

  15. Practice Problems • Write SQL select statements to answer the following queries? • Find the first and last name of each student who is advised by a faculty member in the Philosophy department. • Find the first and last name of each faculty member who advises a Mathematics major • Find the first and last name of each faculty member who advises a Chemistry major

More Related