custID Name 000006798 Jazmine Elzey 000007543 Jalen Harrison 000234561 001111222 548618292 00000ac01 Clark Kent Peter Parker 00000af15 0000С0123 0000PD456 Alice Johnson Bob Smith John Doe Dante Grante Johnson Washington 555000001 John Doe 555000002 Jane Smith 555000201 555000202 555000203 555000205 555000301 555000302 Lebron Smith Kevin Smith Jake Frost Antho Anthony Smith Sarah Browne Terry Donaldson 835261882 Jack Williams 920111221 Larry Taylor 920111222 | 920111224 Mary Price Norh Lee 920111227 Alonzo McKee 920111227 | 920111233 | Sarah Sheet 920111255 Kyle Haskett 920111266 920111444 920111666 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***************************** price qtyInStock ❘ iID name Ocr78 red boots | 36.30 | Omw12 web shooters 42.30 11234 Flashlight 15.50 S0002 Himalayan Salst | 10.99 4 rows in set (0.000 sec) 1 21 4 5 ******/ 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 24020001 24020004 ordDate shippingDate 24021001 | 2024-02-20 | 2024-02-22 240200062024-02-11 | 2024-02-11 2400a674 2024-02-19 2024-02-21 2024-02-08 | 2024-02-09 2024-02-10 | 2024-02-11 24020005 2024-02-10 2024-02-11 24020003 2024-02-10 24020002 2024-02-10 24022222 receivalDate | Pay Amount paymethod custID 2024-02-24 37,636.80 Debit 000006798 2024-02-11 92.97 Visa 920111221 2024-02-27 84.60 Master 00000af15 2024-02-09 65.98 Visa 920111227 2024-02-11 65.95 Visa 920111227 2024-02-11 56.96 Visa 920111266 2024-02-10 2024-02-10 43.98 Master 920111222 2024-02-11 2024-02-11 32.99 Visa 920111266 24020000 2024-02-23 2024-02-26 2024-02-08 | 2024-02-08 2024-02-27 2024-02-08 31.00 10.99 Visa Master 920111222 920111222 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 | 1 row in set (0.000 sec) *** 10 | 38,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. /***********Output************************* custID | Number of Orders in February 2024 920111222 920111227 | 920111266 3 rows in set (0.001 sec) ************************ 3 2 2| ********/

custID Name 000006798 Jazmine Elzey 000007543 Jalen Harrison 000234561 001111222 548618292 00000ac01 Clark Kent Peter Parker 00000af15 0000С0123 0000PD456 Alice Johnson Bob Smith John Doe Dante Grante Johnson Washington 555000001 John Doe 555000002 Jane Smith 555000201 555000202 555000203 555000205 555000301 555000302 Lebron Smith Kevin Smith Jake Frost Antho Anthony Smith Sarah Browne Terry Donaldson 835261882 Jack Williams 920111221 Larry Taylor 920111222 | 920111224 Mary Price Norh Lee 920111227 Alonzo McKee 920111227 | 920111233 | Sarah Sheet 920111255 Kyle Haskett 920111266 920111444 920111666 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*************** price qtyInStock ❘ iID name Ocr78 red boots | 36.30 | Omw12 web shooters 42.30 11234 Flashlight 15.50 S0002 Himalayan Salst | 10.99 4 rows in set (0.000 sec) 1 21 4 5 / 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 24020001 24020004 ordDate shippingDate 24021001 | 2024-02-20 | 2024-02-22 240200062024-02-11 | 2024-02-11 2400a674 2024-02-19 2024-02-21 2024-02-08 | 2024-02-09 2024-02-10 | 2024-02-11 24020005 2024-02-10 2024-02-11 24020003 2024-02-10 24020002 2024-02-10 24022222 receivalDate | Pay Amount paymethod custID 2024-02-24 37,636.80 Debit 000006798 2024-02-11 92.97 Visa 920111221 2024-02-27 84.60 Master 00000af15 2024-02-09 65.98 Visa 920111227 2024-02-11 65.95 Visa 920111227 2024-02-11 56.96 Visa 920111266 2024-02-10 2024-02-10 43.98 Master 920111222 2024-02-11 2024-02-11 32.99 Visa 920111266 24020000 2024-02-23 2024-02-26 2024-02-08 | 2024-02-08 2024-02-27 2024-02-08 31.00 10.99 Visa Master 920111222 920111222 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 | 1 row in set (0.000 sec) * 10 | 38,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. /*Output***** custID | Number of Orders in February 2024 920111222 920111227 | 920111266 3 rows in set (0.001 sec) 3 2 2| /

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

See similar textbooks

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,
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.

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