Database System Concepts
Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
Bartleby Related Questions Icon

Related questions

Question

Task:
Let us consider the following relational database. The primary key column(s) of each table 
is denoted by an underline. The foreign keys are italicized.
Schema:
▪ Customers (custID, fName, lName, password)
▪ Phones (custID, phone)
▪ Items (iID, name, price, qtyInStock)
▪ OrdersPlaces (oID, ordDate, shippingDate, receivalDate receivalDate, payMethod, 
custID)
▪ Contains (oID, iID, price, qty)
Specifically, the foreign keys for this database are as follows:
• the column custID of relation Phones that references table Customers, 
• the column custID of relation OrdersPlaces that references table Customers, 
• the column oID of relation Contains references table OrdersPlaces, and
• the column iID of relation Contains references table Items,

 

Assignment Submission Instructions:
This is an individual assignment – no group submissions are allowed. Submit a script file that 
contains the SELECT statements by assigned date. The outline of the script file lists as follows:
/* ********************************************************************************
 * Name: YourNameGoesHere *
 * Class: CST 235 *
 * Section: *
 * Date: *
 * I have not received or given help on this assignment: YourName *
 ***********************************************************************************/
USE RetailDB;
####### Tasks: Write SQL Queries #########
 
-- Task 1 (Customer Information):
-- List your SELECT statement below.
-- Task 2 (Inventory Control): 
-- List your SELECT statement below.
-- Task 3 (Monthly Sale Information):
-- List your SELECT statement below.
-- Task 4 (Monthly Sale Analysis): 
-- List your SELECT statement below.
-- Task 5 (Frequent purchasing customers):
-- List your SELECT statement below.

 

Make sure the SQL script file can be run successfully in MySQL and show the outcome of the code on MySQL

Task 1 (Customer Information): Retrieve the ID and name of all customers and display the
results as the following output.
/*************Output*****************************
custID
Name
000006798
Jazmine Elzey
000007543
Jalen Harrison
00000ас01 Clark Kent
00000af15 Peter Parker
0000CD123
0000Р0456
000234561
001111222
548618292
555000001
555000002
555000201
555000202
Alice Johnson
Bob Smith
John Doe
Dante Grante
Johnson Washington
John Doe
Jane Smith
Lebron Smith
Kevin Smith
555000203
Jake Frost
555000205
Anthony Smith
555000301 Sarah Browne
555000302
835261882
920111221
920111222
920111224
920111227
920111233
920111255
920111266
920111444
920111666
Terry Donaldson
Jack Williams
Larry Taylor
Mary Price
Norh Lee
Alonzo McKee
Sarah Sheet
Kyle Haskett
Aiden Harbor
Miles Brown
Jazmine Jenkins
27 rows in set (0.001 sec)
********
****************/
Task 2 (Inventory Control): Retrieve information on items with a quantity in stock below
10. Its results should be the same as the following output.
/************* *Output*****************************
❘ iID
name
price qtyInStock |
0cr78
Omw12
red boots
web shooters
36.30
42.30
11234
Flashlight
15.50
S0002
Himalayan Salst
10.99
4 rows in set (0.000 sec)
1245
expand button
Transcribed Image Text:Task 1 (Customer Information): Retrieve the ID and name of all customers and display the results as the following output. /*************Output***************************** custID Name 000006798 Jazmine Elzey 000007543 Jalen Harrison 00000ас01 Clark Kent 00000af15 Peter Parker 0000CD123 0000Р0456 000234561 001111222 548618292 555000001 555000002 555000201 555000202 Alice Johnson Bob Smith John Doe Dante Grante Johnson Washington John Doe Jane Smith Lebron Smith Kevin Smith 555000203 Jake Frost 555000205 Anthony Smith 555000301 Sarah Browne 555000302 835261882 920111221 920111222 920111224 920111227 920111233 920111255 920111266 920111444 920111666 Terry Donaldson Jack Williams Larry Taylor Mary Price Norh Lee Alonzo McKee Sarah Sheet Kyle Haskett Aiden Harbor Miles Brown Jazmine Jenkins 27 rows in set (0.001 sec) ******** ****************/ Task 2 (Inventory Control): Retrieve information on items with a quantity in stock below 10. Its results should be the same as the following output. /************* *Output***************************** ❘ iID name price qtyInStock | 0cr78 Omw12 red boots web shooters 36.30 42.30 11234 Flashlight 15.50 S0002 Himalayan Salst 10.99 4 rows in set (0.000 sec) 1245
Task 3 (Monthly Sale Information): List February's ordering information and sort the results
in descending order based on the pay amount. Rename and format the column
payAmount as the following output.
/*************Output*****
**************
OID
ordDate
shippingDate | receivalDate | Pay Amount paymethod | custID
24021001 | 2024-02-20
240200062024-02-11
2024-02-22
2024-02-11
2024-02-24
2024-02-11
37,636.80
92.97
Debit
Visa
000006798
920111221
2400a6742024-02-19
2024-02-21
2024-02-27
84.60
Master
00000af15
240200012024-02-08
240200042024-02-10
2024-02-09
2024-02-11
2024-02-09
65.98
Visa
920111227
2024-02-11
65.95
Visa
920111227
240200052024-02-10
2024-02-11
2024-02-11
Visa
920111266
24020003 2024-02-10 2024-02-10
240200022024-02-10 | 2024-02-11
240222222024-02-23 2024-02-26
2024-02-10
43.98
Master
920111222
2024-02-11
2024-02-27
2024-02-08
32.99
Visa
| 920111266
31.00
Visa
10.99
Master
920111222
920111222
24020000 | 2024-02-08 | 2024-02-08
10 rows in set (0.000 sec)
************************************************/
Task 4 (Monthly Sale Analysis): List the total number and the total amount of orders placed
in February 2024. Rename the columns and format one of the columns as
indicated in the following output.
*****************************
/*************Output**
Total Number of Orders in February 2024 | Total Amount of Order in February 2024
10 38,122.22
1 row in set (0.000 sec)
************************************************/
Task 5 (Frequent purchasing customers): List the ID of customers with two or more orders
in February 2024 and the total number of orders this month. Rename one of the
columns as the following output.
/***********Output*************************
custID
920111222
920111227
920111266
Number of Orders in February 2024
322
3 rows in set (0.001 sec)
expand button
Transcribed Image Text:Task 3 (Monthly Sale Information): List February's ordering information and sort the results in descending order based on the pay amount. Rename and format the column payAmount as the following output. /*************Output***** ************** OID ordDate shippingDate | receivalDate | Pay Amount paymethod | custID 24021001 | 2024-02-20 240200062024-02-11 2024-02-22 2024-02-11 2024-02-24 2024-02-11 37,636.80 92.97 Debit Visa 000006798 920111221 2400a6742024-02-19 2024-02-21 2024-02-27 84.60 Master 00000af15 240200012024-02-08 240200042024-02-10 2024-02-09 2024-02-11 2024-02-09 65.98 Visa 920111227 2024-02-11 65.95 Visa 920111227 240200052024-02-10 2024-02-11 2024-02-11 Visa 920111266 24020003 2024-02-10 2024-02-10 240200022024-02-10 | 2024-02-11 240222222024-02-23 2024-02-26 2024-02-10 43.98 Master 920111222 2024-02-11 2024-02-27 2024-02-08 32.99 Visa | 920111266 31.00 Visa 10.99 Master 920111222 920111222 24020000 | 2024-02-08 | 2024-02-08 10 rows in set (0.000 sec) ************************************************/ Task 4 (Monthly Sale Analysis): List the total number and the total amount of orders placed in February 2024. Rename the columns and format one of the columns as indicated in the following output. ***************************** /*************Output** Total Number of Orders in February 2024 | Total Amount of Order in February 2024 10 38,122.22 1 row in set (0.000 sec) ************************************************/ Task 5 (Frequent purchasing customers): List the ID of customers with two or more orders in February 2024 and the total number of orders this month. Rename one of the columns as the following output. /***********Output************************* custID 920111222 920111227 920111266 Number of Orders in February 2024 322 3 rows in set (0.001 sec)
Expert Solution
Check Mark
Knowledge Booster
Background pattern image
Similar questions
Recommended textbooks for you
Text book image
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Text book image
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Text book image
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
Text book image
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education