Study Guides (400,000)
US (230,000)
UC-Irvine (3,000)
COMPSCI (100)
Final

# COMPSCI 122A Lecture Notes - Lecture 1: Data Science, Mysql, Adobe After EffectsExam

Department
Computer Science
Course Code
COMPSCI 122A
Professor
Kenneth Shea
Study Guide
Final

This preview shows pages 1-2. to view the full 8 pages of the document.
Last Name: Sun First Name: Bolun Student ID: 52332355
1. [10pts] For all customers who have placed at least 3 orders with a per-order total price greater than
\$100, list their user_id, first_name and last_name. Expected result row(s): 2
a) [7pts] SQL Query:
SELECT User.user_id, User.first_name, User.last_name
FROM User, Customers, Orders
where User.user_id = Customers.user_id
and User.user_id = Orders.customer_id
and total_price >= 100
group by User.user_id, User.first_name, User.last_name
having count(*) >= 3;
b) [3pts] Result:

Only pages 1-2 are available for preview. Some parts have been intentionally blurred.

2. [10pts] For each product in the ‘Pet Care’ category, find the product id, product name, and the highest
quantity of the product stocked by any of the stores. Rank those products by this quantity from high to
low and show only the top 12 products. Expected result row(s): 12
a) [7pts] SQL Query:
SELECT DISTINCT Products.product_id, Products.name, MAX(StockedBy.qty)
from Products, StockedBy
where Products.product_id =StockedBy.product_id
and Products.category = 'Pet Care'
group by Products.product_id, Products.name
order by MAX(StockedBy.qty)DESC
limit 12;
b) [3pts] Result:

Unlock to view full version

Only pages 1-2 are available for preview. Some parts have been intentionally blurred.

3. [10pts] For all products that have been ordered by at least 5% of all customers, list their product id,
product name, and the number of customers who have ordered them. Expected result row(s): 5
a) [7pts] SQL Query:
SELECT DISTINCT Products.product_id, Products.name, COUNT(*)
from Products, Orders, Customers, OrderItems
where Orders.customer_id = Customers.user_id
and Orders.order_id = OrderItems.order_id
and Products.product_id= OrderItems.product_id
group by Products.product_id,Products.name
having count(*)>=(select count(*)*0.05
from Customers);
b) [3pts] Result: