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, IName, password) Phones (custID, phone) ■Items (ilD, name, price, qtyInStock) ■ Orders Places (olD, 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 Orders Places that references table Customers, ⚫ the column oID of relation Contains references table Orders Places, and the column ¡ID of relation Contains references table Items, For your convenience, the SQL statements for creating these tables are available in the file retailDB.sql. Download it on your computer and run it to build the retailDB database on the MySQL database server. Write one SQL query statement for each of the following queries and run them on the MySQL Database Server. The query answers must not contain duplicates, and you should use the SQL keyword DISTINCT only when necessary. Execute your queries based on these tables. Please put query (SELECT) statements into a SQL script file and save it as lab4YourLastName.sql. Then, submit it to the Assignment Link - Lab 4 Assignment. 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 00000ac01 Clark Hent 0000uf15 Peter Parker 9000CD123 0000РО456 000234561 001111222 548618292 Alice Johnson Bob Smith John Doe Dante Grante Johnson Washington 555000002 Jane Smith 555000001 John Doe 555000201 Lebron Smith 555000282 Kevin Smith 555000283 Jake Frost 555000205 Anthony Smith 555000301 Sarah Browne 555000302 Terry Donaldson 835261882 Jack Williams 920111224 920111227 920111233 Larry Tayler Mary Price North Lee Alonzo Mcke Sarah Sheet 928111255 Kyle Haskett Aiden Harbor 920111444 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 price qtyInStock @cr78 red boots Onw12 web shooters 36.30 42.30 11234 Flashlight 15.50 s0882 | Himalayan Salst 10.99 4 rows in set (0.000 sec) *****/ 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 pay Amount as the following output. ************Output** orate shippingDate receivalDate | Pay Amount | paymethod CustID 3020-03-22 Debit 2004-02-11 2004-02-21 2024-02-09 2024-02-11 Visa Master Visa 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 1 row in set (0.000 sec) 10 | 30,122.22 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. CustID ***Output* 920111222 920111227 Number of Orders in February 2024 3 920111266 3 rows in set (0.001 sec) 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: YourNameGoes Here * 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.
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, IName, password) Phones (custID, phone) ■Items (ilD, name, price, qtyInStock) ■ Orders Places (olD, 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 Orders Places that references table Customers, ⚫ the column oID of relation Contains references table Orders Places, and the column ¡ID of relation Contains references table Items, For your convenience, the SQL statements for creating these tables are available in the file retailDB.sql. Download it on your computer and run it to build the retailDB database on the MySQL database server. Write one SQL query statement for each of the following queries and run them on the MySQL Database Server. The query answers must not contain duplicates, and you should use the SQL keyword DISTINCT only when necessary. Execute your queries based on these tables. Please put query (SELECT) statements into a SQL script file and save it as lab4YourLastName.sql. Then, submit it to the Assignment Link - Lab 4 Assignment. 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 00000ac01 Clark Hent 0000uf15 Peter Parker 9000CD123 0000РО456 000234561 001111222 548618292 Alice Johnson Bob Smith John Doe Dante Grante Johnson Washington 555000002 Jane Smith 555000001 John Doe 555000201 Lebron Smith 555000282 Kevin Smith 555000283 Jake Frost 555000205 Anthony Smith 555000301 Sarah Browne 555000302 Terry Donaldson 835261882 Jack Williams 920111224 920111227 920111233 Larry Tayler Mary Price North Lee Alonzo Mcke Sarah Sheet 928111255 Kyle Haskett Aiden Harbor 920111444 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 price qtyInStock @cr78 red boots Onw12 web shooters 36.30 42.30 11234 Flashlight 15.50 s0882 | Himalayan Salst 10.99 4 rows in set (0.000 sec) *****/ 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 pay Amount as the following output. ************Output** orate shippingDate receivalDate | Pay Amount | paymethod CustID 3020-03-22 Debit 2004-02-11 2004-02-21 2024-02-09 2024-02-11 Visa Master Visa 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 1 row in set (0.000 sec) 10 | 30,122.22 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. CustID ***Output* 920111222 920111227 Number of Orders in February 2024 3 920111266 3 rows in set (0.001 sec) 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: YourNameGoes Here * 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.
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
Make sure that the SQL script file can be run successfully in MySQL and show the outcome of the whole code on MySQL please
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 9 images
Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education