Test 2 Review B: Questions
A database for tracking student registration includes a table based on the following schemas.
students(id, name)
Write an SQL query to accomplish each of the following.
a. Insert a new student with an ID of 54321 whose name is Luis Reasoner.
b. Change the name of the student whose ID is 97531 to be Alyssa P. Hacker.
Given a relation accounts(uid, balance), write an SQL statement that removes all rows from the table whose balance is less than 0.05.
A database for tracking bank account deposits includes tables based on the following schemas.
accounts(acct_num, name, birthdate)
deposits(acct_num, date, amount)
Write an SQL query that lists the names of all people who have ever deposited more than $100,000 in a single day. (Note that you should include people who make several smaller deposits in the same day that total to $100,000.)
A database for tracking student registration includes tables based on the following schemas.
students(id, name)
grades(id, num, grade)
sections(num, title, instructor_name)
Write an SQL query to accomplish each of the following.
a. List each instructor and the total number of students taught by that instructor. (Don't worry about whether you double-count the same student taught by an instructor in two different classes. You can assume that instructors all have different names.)
b. List the name of each student taking less than four courses.
A database for tracking customer orders includes tables based on the following schemas.
invoices(invoice_id, customer_name)
orders(invoice_id, item_id, cost)
items(item_id, item_name)
Write an SQL query to accomplish each of the following.
a. For each invoice, list the customer name and the number of items on the invoice.
b. List the names of all customers who have received an invoice containing four or more products costing at least $50 each.
A database for tracking home sales includes tables based on the following schemas.
homes(address, zip, square_feet)
sales(address, zip, date, ssn, amount)
buyers(ssn, name)
Write an SQL query to accomplish each of the following.
a. Display each home's address, ZIP code, and the maximum price per square foot ever paid for that home.
b. Display the name of each buyer who has purchased at least 3 homes and the maximum price ever paid by each such customer.
Given relations described by the following schemas, write an SQL query for each of the following.
students(sid, name)
assignments(sid, assn, points, submit_timestamp)
a. For each person who has completed at least 5 assignments, show the person's name and total points earned across all assignments.
b. Same as previous, except you should only count assignments submitted after timestamp 109. (Earlier assignments should also be omitted from the displayed total.)
A database for tracking customer orders includes tables based on the following schemas.
items(item_id, item_name)
orders(item_id, price, buyer_name)
Write an SQL query that displays each item name followed by the maximum price paid for that item and the buyer who paid hat amount. You may assume that each maximum is unique to its respective item.
Given relations described by the following schemas, write an SQL query that will list the name of each product that has never been ordered.
customers(uid, city)
orders(uid, barcode)
products(barcode, name)
Suppose we have two tables created by the following SQL statements.
CREATE TABLE customers (uid INTEGER PRIMARY KEY, city TEXT);
CREATE TABLE orders (uid INTEGER REFERENCES customers(uid), product TEXT);
If our DBMS respects foreign keys, what would happen for the
following SQL statement
if customers does not include a row with 54321 in the
uid column?
INSERT INTO orders (uid, product) VALUES (54321, 'laptop');What is a database index? Give an example illustrating where an index can be helpful.
Suppose we have relations described by the following schemas,
where the customers and orders relations
are already sorted based on uid.
Give an example SQL query where an additional index would be useful, and
explain why an index would be useful for that query.
customers(uid, city)
orders(uid, product)
Test 2 Review B: Solutions
| a. | INSERT INTO students (id, name) VALUES (54321, 'Luis Reasoner') |
| b. | UPDATE students SET name = 'Alyssa P. Hacker' WHERE id = 97531 |
DELETE FROM accounts WHERE balance < 0.05
SELECT DISTINCT name
FROM accounts JOIN deposits ON accounts.acct_num = deposits.acct_num
GROUP BY deposits.acct_num, date
HAVING SUM(amount) > 100000
| a. | SELECT instructor_name, COUNT(*) |
| b. | SELECT name |
| a. | SELECT customer_name, COUNT(*) |
| b. | SELECT DISTINCT customer_name |
| a. | SELECT homes.address, homes.zip, MAX(amount / square_feet) |
| b. | SELECT name, MAX(amount) |
| a. | SELECT name, SUM(points) |
| b. | SELECT name, SUM(points) |
SELECT item_name, max_price, buyer_name
FROM items JOIN (SELECT item_id, MAX(price) AS max_price
FROM orders GROUP BY item_id) maxes
ON items.item_id = maxes.item_id
JOIN orders ON maxes.item_id = orders.item_id
AND orders.price = max_price
GROUP BY items.item_id
Two of the possibilities:
SELECT name
FROM products LEFT JOIN orders ON products.barcode = orders.barcode
WHERE orders.uid IS NULLSELECT name
FROM products
WHERE barcode NOT IN (SELECT barcode FROM orders)
The DBMS will refuse to perform the insertion.
Given a set of columns, a database index is an additional structure held alongside a table, saying for each combination of column values which rows from the table hold that combination. Each time the table is modified, any associated indexes are updated to remain consistent with the table.
One case where an index would be helpful if in performing a
query that searches on a column that isn't the primary key. For
example, with a students table for which id is
the primary key, we may often want to look students up by name
(as in “SELECT id FROM students WHERE name = 'Burch'”). An index on the name attribute allows going
directly to the relevant row(s), rather than examining each row
as would be necessary without any index.
SELECT uid FROM customers WHERE city = 'Conway'
This query could be executed more quickly if the
customers table included an index on city.
The DBMS could perform a binary search on such an index to find
exactly those rows that it needs to display, allowing it to skip
over almost all of the table.