Scribd
Upload
3 views12 pages

Week

The document outlines the Week 3 content for the DSBA/HCIP 6160 Database Systems course, including a quiz and homework exercises related to SQL queries on a Pizza and college application database. It provides specific questions for students to answer using SQL, focusing on querying single and multiple tables. Additionally, it mentions upcoming homework and lab requirements for the course.

Uploaded by

asdfasdflkjlkjasldkf
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views12 pages

Week

The document outlines the Week 3 content for the DSBA/HCIP 6160 Database Systems course, including a quiz and homework exercises related to SQL queries on a Pizza and college application database. It provides specific questions for students to answer using SQL, focusing on querying single and multiple tables. Additionally, it mentions upcoming homework and lab requirements for the course.

Uploaded by

asdfasdflkjlkjasldkf
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DSBA/HCIP 6160 Database

Systems
Week 3
Quiz 1
• 20 minutes
• Closed book/notes/internet
HW2 - RA Exercise Pizza DB
• Q1: Find all pizzas eaten by at least one female
over the age of 20.

• Q2: Find the names of all females who eat at least


one pizza served by Straw Hat. (Note: The pizza
need not be eaten at Straw Hat.)

• Q3: Find all pizzerias that serve at least one pizza


for less than $10 that either Amy or Fay (or both)
eat.
SQL sub-
languages
SQL Concepts
• Select statement

– []: optional
– SQL produces multisets
Exercise: college application DB
Querying one table
• Students with GPA>3.7 and HS<1000

• ID and name of students with GPA >= 3.0 and GPA <=
3.5

• ID of students who applied to Stanford or UC Berkeley


or UCSD

• Colleges who received one or more applications to CS;


do not show the same college more than once
SQL Concepts
• Select statement

– LIMIT starting_row, #_of_rows_to_return


• or LIMIT #_of rows_to_return (i.e., starting with the
first row)
Exercise (cont.): college application DB
Querying one table
• IDs and names of students with GPA >= 3.0 and GPA <=
3.5; sort the results first by name alphabetically and
within a name by GPA from high to low

• Students with GPA>3.7 and HS<1000; only top 3


students ranked by GPA should be returned

• ID of students who applied to and heard from one of


the following colleges: Stanford, UC Berkeley, UCSD.
(i.e., the application decision must not be null)
SQL Concepts (cont.)
• Like ‘%xyz%’
• As
Exercise (cont.): college application DB
Querying one table

• Names and GPAs of students with HS>1000 who have


‘son’ in their name
– Modify to -> whose name ends with ‘son’ ?

• IDs and names of students with GPA >= 3.0 and GPA
<= 3.5; use an alias, StudentID, for the sID column
Exercise (cont.): college application DB
Querying multiple tables

• Names and GPAs of students with HS>1000 who


applied to CS and were rejected

• Names and GPAs of students with HS>1000 who


applied to CS at a college with enr>20,000 and were
rejected

• Pairs of colleges in same state


• Videos/readings posted for Week 4

• Homework 3 posted
– Due Sep 11th 11:59pm

• Lab 1 next week


– MySQL installed before class
– textbook Appendix A or B

You might also like