Test 2 Review B

[1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12]

Problem R2b.1.

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.

a.	`INSERT INTO students (id, name) VALUES (54321, 'Luis Reasoner')`
b.	`UPDATE students SET name = 'Alyssa P. Hacker' WHERE id = 97531`

Problem R2b.2.

Given a relation accounts(uid, balance), write an SQL statement that removes all rows from the table whose balance is less than 0.05.

DELETE FROM accounts WHERE balance < 0.05

Problem R2b.3.

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

SELECT DISTINCT name

FROM accounts JOIN deposits ON accounts.acct_num = deposits.acct_num

GROUP BY deposits.acct_num, date

HAVING SUM(amount) > 100000

Problem R2b.4.

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.	`SELECT instructor_name, COUNT(*) FROM grades JOIN sections ON grades.num = sections.num GROUP BY instructor_name`
b.	`SELECT name FROM students JOIN grades ON students.id = grades.id GROUP BY students.id HAVING COUNT(*) < 4`

Problem R2b.5.

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.	`SELECT customer_name, COUNT(*) FROM invoices JOIN orders ON invoices.invoice_id = orders.invoice_id GROUP BY invoices.invoice_id`
b.	`SELECT DISTINCT customer_name FROM invoices JOIN orders ON invoices.invoice_id = orders.invoice_id WHERE cost >= 50 GROUP BY invoices.invoice_id HAVING COUNT(*) >= 4`

Problem R2b.6.

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.

a.	`SELECT homes.address, homes.zip, MAX(amount / square_feet) FROM homes JOIN sales ON homes.address = sales.address AND homes.zip = sales.zip GROUP BY homes.address, homes.zip`
b.	`SELECT name, MAX(amount) FROM buyers JOIN sales ON buyers.ssn = sales.ssn GROUP BY buyers.ssn HAVING COUNT(*) >= 3`

Problem R2b.7.

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 10⁹. (Earlier assignments should also be omitted from the displayed total.)

a.	`SELECT name, SUM(points) FROM students JOIN assignments ON students.sid = assignments.sid GROUP BY students.sid HAVING COUNT(*) >= 5`
b.	`SELECT name, SUM(points) FROM students JOIN assignments ON students.sid = assignments.sid WHERE submit_timestamp >= 1000000000 GROUP BY students.sid HAVING COUNT(*) >= 5`

Problem R2b.8.

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.

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

Problem R2b.9.

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)

Two of the possibilities:

SELECT name FROM products LEFT JOIN orders ON products.barcode = orders.barcode WHERE orders.uid IS NULL
SELECT name FROM products WHERE barcode NOT IN (SELECT barcode FROM orders)

Problem R2b.10.

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');

The DBMS will refuse to perform the insertion.

Problem R2b.11.

What is a database index? Give an example illustrating where an index can be helpful.

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.

Problem R2b.12.

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)

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.