Lab $5$ Assignment $-$Basic SQL Query Task $1($Customer Information$)$: Retrieve all customers' ID$,$ name, and phone$($s$)$ and display the results as the following output. Note that the results are sorted based on the customer's ID in ascending order. Using ANSI JOIN when pulling data from several tables.

Task $2($Alphabetical List of Popular Items$)$: Retrieve ID and name of items that were sold in

the first week of June $2024.$ The results should be sorted in ascending order based on the

names of items as in the following output. Using ANSI JOIN when pulling data from

several tables

Task $3($Alphabetical List of Least Items$)$: List items that customers have never ordered. The

results should be sorted in ascending order based on the names of items as in the

following output. Using ANSI JOIN when pulling data from several tables Task $4($Order Information$)$: List the ID and name of customers who placed at least one order

in June $2024.$ The display information should also include order ID$,$ item ID$,$ quantity,

and price as in the following output.

$\frac{?}{?}**************************Output**************************************************************$

$*******************************************************************************************************\frac{*}{?}$

Task $5($Order Frequency$)$: List the ID and name of customers who purchase more than three

times in June $2024.$ The display information should also include the order ID$.$ Hints: Use

GROUP BY and HAVING clauses to filter the qualified customer$($s$).$

$\frac{?}{?}**************************$ Output $**************************************************************$

$*******************************************************************************************************\frac{*}{?}$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 rec

CST $235$

June $13,2024,$ at $7$:$00$ PM

Lab Instructions:

You should work individually for this assignment. Create a new SQL script file$($ using

Notepad or Notepad$++)$ and save it as Lab$5$xxxx$.$sql $($where xxxx is your last name$).$ Write

your full name on the first page of the script file. Where required, write your answers in this

script file. You need to submit only this script file.

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 payAmount, payMethod,

custID$)$

Contain $($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 Contain references table OrdersPlaces, and

the column iID of relation Contain 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 lab$4$YourLastName.sql$.$ Then, submit it to

the Assignment Link $-$ Lab $5$ Assignment.