DS 100/200: Principles and Techniques of Data Science Date: November 16, 2019
Name:
SQL Review
SQL
1. Circle TRUE or FALSE.
(a) True (b) True
(c) True (d) True
False False
False False
SQL is a declarative language that specifies what to produce but not how to compute it.
The primary key of a relation is the column or set of columns that de- termine the values of the remaining column.
The schema of a table consists of the data stored in the table.
The WHERE and HAVING clause can be used interchangeably as they perform the same operation.
Writing SQL Queries
Consider the following schema:
Clowns(cid int, cname text, booth text)
Balloons(bid int, bshape text, bcolor text)
Catalog(cid int, bid int, cost float)
Note: The Catalog table contains prices for Balloons sold by different Clowns standing at certain booths in a fair.
2. How may we query for the top 3 most expensive shapes sold by Whompers LeFou, ignoring the possibility that Whompers could be selling the same shape in different colors?
3. How may we query for the top 3 most expensive shapes sold by Whompers LeFou, taking into consideration the possibility that Whompers could be selling the same shape in different colors by using the highest-priced color of each shape?
1
SQL Review 2
4. Whatistheaveragecostofaredballoonatboothsthatoffermorethan3redshapesperclown? Note that each clown at the booth does not necessarily have to be selling more than 3 shapes.
5. Consider the following real estate schema:
Homes(home id int, city text, bedrooms int, bathrooms int, area int)
Transactions(home id int, buyer id int, seller id int, transaction date date, sale_price int)
Buyers(buyer id int, name text) Sellers(seller id int, name text)
For the query language questions below, fill in the blanks in the answer to complete the query. For each SQL query and nested subquery, please start a new line when you reach a SQL keyword (SELECT, WHERE, AND, etc.). However, do not start a new line for aggregate functions (COUNT, SUM, etc.), and comparisons (LIKE, AS, IN, NOT IN, EXISTS, NOT EXISTS, ANY, or ALL.)
Fill in the blanks in the SQL query to find the duplicate-free set of id¡¯s of all homes in Berkeley with at least 6 bedrooms and at least 2 bathrooms that were bought by ¡°Bobby Tables.¡±
SELECT
FROM
WHERE