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)

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)

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,

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.