ct 100 week 6
Download
Skip this Video
Download Presentation
CT 100 Week 6

Loading in 2 Seconds...

play fullscreen
1 / 24

CT 100 Week 6 - PowerPoint PPT Presentation


  • 110 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' CT 100 Week 6' - kevin-blake


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
quiz 6 vocabulary
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
quiz 6 problems
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
creating tables
Creating Tables
  • Table name
  • Column Names
    • Each column must have a data type
  • Constraints
    • Primary key
    • Foreign key
      • Referential integrity
creating tables1
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))
insert rows
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
queries
Queries
  • A query is a way to extract data that satisfies some condition from a database
  • The condition is expressed as a logic expression
queries1
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’
general format
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
in class exercise
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;
in class exercise1
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\');
save tables
Save Tables
  • sqlite> .output U:ct100A.sql
  • sqlite> .dump student
restore table
Restore Table
  • sqlite> .read ct100A.sql
quit sqlite
Quit SQLite
  • sqlite> .quit
practice problems
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’
practice problems1
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 = ‘’
practice problems2
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
practice problems3
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
ad