351-hw2-sp24-v2

docx

School

University of Southern California *

*We aren’t endorsed by this school

Course

225

Subject

Computer Science

Date

Apr 3, 2024

Type

docx

Pages

2

Uploaded by GrandWillpowerLeopard3

Report
Homework 2: SQL DSCI 351 – Spring 2024 Release: 2/5, 2024, Monday Due: 11:59pm, 2/16, 2024, Friday Points: 100 Consider the following tables in the database for managing an online store that you have seen in homework 1. drop table order_entry, orders, product, company, customer; create table company(name varchar(20) primary key, stock int); insert into company values('apple', 180); insert into company values('lenovo', 250); create table product(name varchar(20) primary key, price real, category varchar(20), cname varchar(20) not null, foreign key (cname) references company(name)); insert into product values('iphone15', 1000, 'cell', 'apple'); insert into product values('iphone14', 800, 'cell', 'apple'); insert into product values('t450s', 1500, 'laptop', 'lenovo'); create table customer(id varchar(10) primary key, name varchar(20), email varchar(20)); insert into customer values('c100', 'john', 'john@usc.edu'); insert into customer values('c101', 'mary', 'mary@usc.edu'); create table orders(order_no varchar(10) primary key, cid varchar(10), addr varchar(10), foreign key (cid) references customer(id)); insert into orders values('o100', 'c100', 'LA'); insert into orders values('o101', 'c101', 'SFO'); create table order_entry(order_no varchar(10), pname varchar(20), quantity int, primary key (order_no, pname, quantity), foreign key (order_no) references orders(order_no), foreign key (pname) references product(name)); insert into order_entry values('o100', 'iphone15', 2); insert into order_entry values('o100', 't450s', 1); insert into order_entry values('o101', 'iphone14', 3);
Write an SQL query for each of the following questions. 1) Find out which company makes the most expensive product. Note that there might be more than one such company. 2) Find out which company makes only a single type of products (e.g., only laptops). 3) Find out which companies make only laptops (i.e., it does not make any other products). Note you are NOT allowed to use aggregation and group by in this question. 4) Find out which companies make both laptops and cell phones. Note you are NOT allowed to use aggregation and group by in this question. 5) Find out which companies make laptops but do not make cell phones. 6) Find out names and emails of customers whose email address ends with "@usc.edu" and has ordered a laptop from the store. 7) Find out how many orders are shipped into customers living in LA and contain at least one iphone15. 8) Find out which orders have both laptops and cell phones. Return unique order numbers. Do NOT use group by and aggregation in this query. 9) Find out, for each customer (identified by customer id), how much money the customer has spent in ordering the products from the store. 10) Find out names of customers who have placed the most orders. Submission Instructions: 1. Submit only one file named <firstname>_<lastname>.sql, containing all 10 queries. Any code outside these queries should be commented out. 2. DO NOT submit zip file. 3. Failing to follow the above guidelines will incur a 20% penalty.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

Midterm 2015-2018 answers.pdf
1.docx
notbook #1.docx
LR.docx
LC_Copy of Bascope_Chapter_1-1_Heart_Rate_Start.xlsx
mid2.pdf
worksheet_hw3.docx
worksheet_hw2.docx
Module 1 quiz.pdf
Lab-Report-Template (1).docx
Lab-Report-Template (2).docx
Haris Rao Lab 4.docx
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SEE MORE TEXTBOOKS