1 / 24

CT 100 Week 6

CT 100 Week 6. SQL. Quiz 6 Vocabulary. All quiz 5 vocabulary and the following new terms and the following new terms. 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.

kevin-blake
Download Presentation

CT 100 Week 6

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 6 SQL

  2. Quiz 6 Vocabulary • All quiz 5 vocabulary and the following new termsand the following new terms. • 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

  3. Quiz 6 Problems • Translate an English language statement into a Boolean expression • Determine if an expression is a well formed Boolean expression • 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

  4. Faculty Table

  5. Student Table

  6. Advisor Relationship

  7. Creating Tables • Table name • Column Names • Each column must have a data type • Constraints • Primary key • Foreign key • Referential integrity

  8. Creating Tables • create table Faculty(fid varchar(10) primary key, first varchar(20), last varchar(30), department varchar(30)) • create table Student(sid varchar(10) primary key, first varchar(20), last varchar(30), major1 varchar(30), major2 varchar(30), advisor varchar(10) references Faculty(fid))

  9. Insert Rows • insert into Faculty values (‘0001’, ‘David’, ‘Hilbert’, ‘Mathematics’) • insert into Student values (‘005’, ’Joshua’,’Goldberg’, ‘Mathematics’, ‘Philosophy’, ‘0001’) • Single quotes around values are required for varchar types • Varchars are examples of what are usually called strings. Quotes are used to identify literal strings

  10. Queries • A query is a way to extract data that satisfies some condition from a database • The condition is expressed as a logic expression

  11. Queries • Find the names of Faculty in the Mathematics department • Select first, last from Faculty where department = ‘Mathematics’ • Find the names of Students who are Mathematics majors • Select first, last from Student where major1 = ‘Mathematic’s or major2 = ‘Mathematics’

  12. General Format • Create table • Create table TABLENAME(COLUMN NAMES and DATA TYPES) • Insert • Insert into TABLENAME values (COLUMN VALUES) (COLUMN VALUES) … • Select • Select COLUMN NAMES from TABLENAMES where CONDITION

  13. In Class Exercise Student Table

  14. In Class Exercise • create table student(sid varchar(10) primary key, first varchar(20), last varchar(30), major1 varchar(30), major2 varchar(30)); • insert into student values ('001','Jane','Green','Computer Science',''); • select * from student; • insert into student values ('002','Mike','Brown','Mathematics','Computer Science'), ('003','Alex','James','Radiation Therapy',''); • insert into student values ('004','Sue','Lee','Radiation Therapy','Computer Science'); • insert into student values ('005','Sam','Brower','Mathematics','Computer Science'); • select * from student;

  15. In Class Exercise • select first, last from Student where major2 = ''; • select first,last from Student where major1 = 'Computer Science' or major2 = 'Computer Science'; • select first,last from Student where (major1 = 'Mathematics' and major2 = 'Computer Science') or (major1 = 'Computer Science' and major2 = 'Mathematics');

  16. Save Tables • sqlite> .output U:ct100A.sql • sqlite> .dump student

  17. Restore Table • sqlite> .read ct100A.sql

  18. Quit SQLite • sqlite> .quit

  19. Practice Problems • Given the faculty table shown on the next page what are the results of the following queries • select first, last from Faculty where department = ‘Philosophy’ • select fid from Faculty where department = ‘Mathematics’ • select fid, last from Faculty where department = ‘Mathematics’ or department = ‘Physics’ • select last from Faculty where department = ‘Mathematics’ and department = ‘Physics’

  20. Faculty Table

  21. Practice Problems • Given the student table shown on the next page what are the results of the following queries • Select first, last from Student where major1 = ‘Mathematics’ • Select majors2 from Student where major1 = ‘Computer Science’ • Select first, last from Student where major1 = ‘Mathematics’ or major2 = ‘Mathematics’ • Select first, last from Student where major1 = ‘Philosophy’ and major2 = ‘Computer Science’ • Select first, last from Student where major2 = ‘’

  22. Student

  23. Practice Problems • Given the definition of the student and faculty tables write SQL select statements to answer the following questions. • Find the fids of faculty in the English department • Find the first name and last name of students who are History majors • Find the last name of students who are both History majors and Computer Science majors • Find the last names of faculty whose first name is Janet and who is a member of the Biology department

  24. Practice Problems • Find the last names of students who are Biology majors or Chemistry majors • Find the last names of faculty in the English department or the History department • Find the last names of students who are not Mathematics majors • Find the last names of students who are not History majors but are English majors

More Related