- 110 Views
- Uploaded on

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

- 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

- 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

- Table name
- Column Names
- Each column must have a data type
- Constraints
- Primary key
- Foreign key
- Referential integrity

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

- A query is a way to extract data that satisfies some condition from a database
- The condition is expressed as a logic expression

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

- 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

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

- sqlite> .output U:ct100A.sql
- sqlite> .dump student

Restore Table

- sqlite> .read ct100A.sql

Quit SQLite

- sqlite> .quit

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

Download Presentation

Connecting to Server..