A word document consisting of: Your SQL statements written as text where I can t
ID: 3803425 • Letter: A
Question
A word document consisting of:
Your SQL statements written as text where I can test your work by copying and paste your statement into the SQL server.
Standard size (not small) screen capture for executing your SQL statements in SQL server
In homework 2, you wrote SQL statements to do the following:
Create a new table and name it customers
Use SQL to create at least 10 different rows consisting of CustomerId (primary key), FirstName, LastName (required) , Street Address (required) , City (required), State (required), ZipCode (required), PhoneNumber (optional), email (required) ; Title (e.g., Mr. Ms. Optional ). Make sure that one of your customers has the same name like you. At least one of the customers has an and underscore as part of their name ‘_’
Below is an example, please make your own data(create your own data)
C_ID
Title
First_Name
Last_Name
Street_Address
City
State
Zip_Code
Phone_Number
1
Dr.
Tracy
Mikes
12345 South Main Road
New York
NY
74364-1123
(918) 555-1234
Mikes@il.com
10
Baron
Ludwig
vonWunderkind
12345 West Tillameeko Street
Chicago
IL
74366-1123
(918) 555-0123
Hotpants@il.com
2
Mrs.
Ann
Berlin
12345 West Third Street
Dyer
IN
73460-1123
(918) 555-2345
Berlin@yaho.com
3
Dr.
John
Druitt
12345 East Main Street
Spring Field
MO
73005-1123
(918) 555-3456
Druitt@google.com
4
Ms.
Annabelle
Smith
12345 North Rodeo Street
Tulsa
OK
74101-1123
(918) 555-4567
Smith@bing.com
5
Lord
Duke
Earl
12345 South Elvis Boulevard
Adair
OK
74330-1123
(918) 555-5678
Earl@mapquest.com
6
Duke
Earl
Smith
12345 West Sycamore Street
Langley
OK
74350-1123
(918) 555-6789
Smith@aol.com
7
Duchess
Fergie
Giepher
12345 East Second Street
Ketchum
OK
74349-1123
(918) 555-7890
Giepher@dogpile.com
8
NULL
Jack
Rabbit
12345 North South Street
Grove
OK
74344-1123
(918) 555-8901
Rabbit@infoquest.com
9
NULL
Jill
Hill
12345 South Grandma Way
Strang
OK
74367-1123
(918) 555-9012
Hill@yourmom.com
Create a second table called product with at least 10 different rows of product ID, Product Name, Brand, Price, Quantity at Hand, Date Product was added
Sample data below: please make your own data:
ProductID
ProductName
Brand
Price
Quantity
DateAdded
100
Radio
Sony
29.99
30
8/22/2012
101
Clock
LG
19.99
15
6/13/2012
102
Printer
HD
49.99
244
9/1/2012
103
Okama GameSphere
Wintendo
29.99
46
8/22/2012
104
Crockpot
Equate
99.99
25
2/14/2012
105
Widget
Bony
10.99
25
5/30/2012
106
Map
Fony
119.99
63
4/15/2012
107
Donkey
Brony
22.99
20
1/1/2012
108
Toaster
Tony
35.99
37
3/27/2012
109
Beef Wellington
Phat
19.99
47
6/16/2012
Create a business transactions table with at least 5 different records showing what customersID bought what productsID, on what date, and what was the quantity they bought, and the method of payment. Make sure that you add at least 3 different transactions based on you being the customer.
Sample data below: please make your own data
C_ID P_ID PurchaseDate QuantityPurchased PaymentMethod
1 101 2012-04-13 1 Visa
1 105 2012-07-22 2 Visa
1 109 2012-09-22 15 Visa
4 106 2012-02-22 7 AmEx
1 103 2012-06-09 3 Amex
3 108 2011-07-17 6 Cash
Use the tables created previously (in homework 2) to answer the following homework # 4 questions:
Make sure that your statements produce at least one row that satisfies each query requirements. If you don’t have data to match the query below, add needed data before running the query.
Write an SQL query to display the customer’s name, email address, the item they bought, quantity purchased, price paid, and method of payment listed in ascending order by customers full name.
Write an SQL query to display customer’s full name, email address, the item they bought, listed in ascending order by customer’s full name for customers who bought Radio or printer.
Write an SQL query that uses aggregate functions to list total amount of money spent by each customer, and the number of items sold to each customer grouped by customer name and sorted according to the customer names
Write an SQL query that uses nested query to list all customers who didn’t buy a radio or a printer.
Use aggregate functions to list the name of customer’s r who bought more than one item and the number of items the customers bought.
C_ID
Title
First_Name
Last_Name
Street_Address
City
State
Zip_Code
Phone_Number
1
Dr.
Tracy
Mikes
12345 South Main Road
New York
NY
74364-1123
(918) 555-1234
Mikes@il.com
10
Baron
Ludwig
vonWunderkind
12345 West Tillameeko Street
Chicago
IL
74366-1123
(918) 555-0123
Hotpants@il.com
2
Mrs.
Ann
Berlin
12345 West Third Street
Dyer
IN
73460-1123
(918) 555-2345
Berlin@yaho.com
3
Dr.
John
Druitt
12345 East Main Street
Spring Field
MO
73005-1123
(918) 555-3456
Druitt@google.com
4
Ms.
Annabelle
Smith
12345 North Rodeo Street
Tulsa
OK
74101-1123
(918) 555-4567
Smith@bing.com
5
Lord
Duke
Earl
12345 South Elvis Boulevard
Adair
OK
74330-1123
(918) 555-5678
Earl@mapquest.com
6
Duke
Earl
Smith
12345 West Sycamore Street
Langley
OK
74350-1123
(918) 555-6789
Smith@aol.com
7
Duchess
Fergie
Giepher
12345 East Second Street
Ketchum
OK
74349-1123
(918) 555-7890
Giepher@dogpile.com
8
NULL
Jack
Rabbit
12345 North South Street
Grove
OK
74344-1123
(918) 555-8901
Rabbit@infoquest.com
9
NULL
Jill
Hill
12345 South Grandma Way
Strang
OK
74367-1123
(918) 555-9012
Hill@yourmom.com
Explanation / Answer
CREATE TABLE CUSTOMERS(
C_ID INT NOT NULL,
Title VARCHAR(20),
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20) NOT NULL,
Street_Address CHAR(25),
City CHAR(25),
State CHAR(25),
Zip_Code CHAR(25),
Phone_Number CHAR(25),
email CHAR(25),
PRIMARY KEY (C_ID)
);
CREATE TABLE product (
ProductID INT NOT NULL,
ProductName VARCHAR(20) NOT NULL,
Brand VARCHAR(20) NOT NULL,
Price FLOAT,
Quantity INTEGER,
DateAdded Date,
PRIMARY KEY (ProductID)
);
CREATE TABLE transactions (
C_ID INT NOT NULL,
P_ID INT NOT NULL,
PurchaseDate Date,
QuantityPurchased INTEGER,
PaymentMethod VARCHAR(20),
PRIMARY KEY (C_ID, P_ID, PurchaseDate)
);
INSERT INTO CUSTOMERS (C_ID,Title, first_name, last_name, Street_Address,City,State, Zip_Code, Phone_Number,email)
VALUES (1, 'Cust1', 'Mr', 'CustF1','CustL1', null, null, null,'23123-2131-213','sdsa@fdffds.dad');
INSERT INTO CUSTOMERS (C_ID,Title, first_name, last_name, Street_Address,City,State, Zip_Code, Phone_Number,email)
VALUES (2, 'Cust2', 'Mr', 'CustF2','CustL2', null, null, null,'23123-2131-213','sdsa@fdffds.dad');
INSERT INTO CUSTOMERS (C_ID,Title, first_name, last_name, Street_Address,City,State, Zip_Code, Phone_Number,email)
VALUES (3, 'Cust3', 'Mr', 'CustF3','CustL3', null, null, null,'23123-2131-213','sdsa@fdffds.dad');
INSERT INTO CUSTOMERS (C_ID,Title, first_name, last_name, Street_Address,City,State, Zip_Code, Phone_Number,email)
VALUES (4, 'Cust4', 'Mr', 'CustF4','CustL4', null, null, null,'23123-2131-213','sdsa@fdffds.dad');
INSERT INTO CUSTOMERS (C_ID,Title, first_name, last_name, Street_Address,City,State, Zip_Code, Phone_Number,email)
VALUES (5, 'Cust5', 'Mr', 'CustF5','CustL5', null, null, null,'23123-2131-213','sdsa@fdffds.dad');
INSERT INTO CUSTOMERS (C_ID,Title, first_name, last_name, Street_Address,City,State, Zip_Code, Phone_Number,email)
VALUES (6, 'Cust6', 'Mr', 'CustF6','CustL6', null, null, null,'23123-2131-213','sdsa@fdffds.dad');
INSERT INTO CUSTOMERS (C_ID,Title, first_name, last_name, Street_Address,City,State, Zip_Code, Phone_Number,email)
VALUES (7, 'Cust7', 'Mr', 'CustF7','CustL7', null, null, null,'23123-2131-213','sdsa@fdffds.dad');
INSERT INTO product (ProductID,ProductName,Brand,Price,Quantity, DateAdded)
VALUES (1, 'Radio', 'Brand1', 10.5, 2, '8/22/2014');
INSERT INTO product (ProductID,ProductName,Brand,Price,Quantity, DateAdded)
VALUES (2, 'Prod2', 'Brand2', 4.5, 9, '8/22/2012');
INSERT INTO product (ProductID,ProductName,Brand,Price,Quantity, DateAdded)
VALUES (3, 'Printer', 'Brand3', 7.5, 2, '8/12/2012');
INSERT INTO product (ProductID,ProductName,Brand,Price,Quantity, DateAdded)
VALUES (4, 'Prod4', 'Brand4', 17.3, 6, '8/02/2017');
INSERT INTO product (ProductID,ProductName,Brand,Price,Quantity, DateAdded)
VALUES (5, 'Prod5', 'Brand5', 1.5, 2, '8/22/2012');
INSERT INTO transactions (C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
VALUES (1, 1, '4/13/2012', 1, 'Visa');
INSERT INTO transactions (C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
VALUES (1, 2, '4/13/2012', 1, 'AmEx');
INSERT INTO transactions (C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
VALUES (1, 2, '3/17/2012', 1, 'Cash');
INSERT INTO transactions (C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
VALUES (3, 1, '4/13/2013', 1, 'AmEx');
INSERT INTO transactions (C_ID,P_ID,PurchaseDate,QuantityPurchased,PaymentMethod)
VALUES (2, 3, '4/13/2012', 1, 'Visa');
I have taken a random data, you may odify it accordingly
1) Write an SQL query to display the customer’s name, email address, the item they bought, quantity purchased, price paid, and method of payment listed in ascending order by customers full name.
select c.first_name + ' ' + c.last_name as full_name, c.email, p.ProductName as item, t.QuantityPurchased, t.QuantityPurchased*p.Price as price_paid, t.PaymentMethod
from CUSTOMERS c, product p, transactions t
where t.C_ID = c.C_ID
and t.P_ID = p.ProductID
order by full_name;
2.) Write an SQL query to display customer’s full name, email address, the item they bought, listed in ascending order by customer’s full name for customers who bought Radio or printer.
select c.first_name + ' ' + c.last_name as full_name, c.email, p.ProductName as item
from CUSTOMERS c, product p, transactions t
where t.C_ID = c.C_ID
and t.P_ID = p.ProductID
and (p.ProductName='Radio' or p.ProductName='Printer')
order by full_name;
3) Write an SQL query that uses aggregate functions to list total amount of money spent by each customer, and the number of items sold to each customer grouped by customer name and sorted according to the customer names
select c.first_name + ' ' + c.last_name as full_name, c.email, SUM(t.QuantityPurchased * p.Price) as amount_spent, COUNT(t.P_ID) as items_sold
from CUSTOMERS c, product p, transactions t
where t.C_ID = c.C_ID
and t.P_ID = p.ProductID
group by t.C_ID, c.first_name, c.last_name, c.email
order by full_name;
4) Write an SQL query that uses nested query to list all customers who didn’t buy a radio or a printer.
select c.first_name + ' ' + c.last_name as full_name
from CUSTOMERS c
where c.C_ID not in(
select t.c_id from product p, transactions t where t.P_ID = p.ProductID and (p.ProductName='Radio' or p.ProductName='Printer')
)
order by full_name;
5) Use aggregate functions to list the name of customer’s r who bought more than one item and the number of items the customers bought.
select c.first_name + ' ' + c.last_name as full_name, count(t.p_id) as purachase_count
from CUSTOMERS c, transactions t
where c.C_ID in(
select t.c_id as items from transactions t group by t.C_ID having count(t.p_id) > 1
) and t.C_ID = c.C_ID
group by t.c_id, c.first_name , c.last_name
order by full_name;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.