Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
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. custID Output Name 000006798 | Jazmine Elzey 000007543 | Jalen Harrison 00000ac01 | Clark Kent 00000af15 Peter Parker 0000CD123 | Alice Johnson 0000PD456 | Bob Smith phone 706-456-3211 706-944-1230 | 281-360-8004 | 555-867-5309 555-123-4567 | 555-987-6543 | 000234561 | John Doe 202-423-5603 | 001111222 Dante Grante 984-263-1704 | 548618292 | Johnson Washington 954-151-1425 | 555000201 Lebron Smith 919-555-1234 | 555000202 | Kevin Smith 919-555-5678 | 555000301 | Sarah Browne 252-434-9830 | 555000301 | Sarah Browne 336-287-7890 | 835261882 | Jack Williams 336-517-1693 | 920111222 | Mary Price 920111222 | Mary Price 920111227 | Alonzo McKee 336-225-3344 | 704-334-3344 | 337-335-6688 920111233 | Sarah Sheet 336-889-0123 | 920111255 Kyle Haskett 336-768-5555 | 920111266 | Aiden Harbor 336-335-6567 | 920111444 | Miles Brown 920111444 | Miles Brown 920111444 Miles Brown | 920111666 | Jazmine Jenkins | 920111666 | Jazmine Jenkins +- 25 rows in set (0.001 sec) 336-776-2898 919-888-2898 | 980-776-8888 336-285-7777 | 336-777-7777 | 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 ****Output** iID name 21355 11234 Axe GF001 Flashlight Glass Food Storage 10002 Gym Bag S0002 | Himalayan Salst DY001 Instant Dry Yeast W9999 | Insulated Stainless Steel Water Bottle a2345 P0001 11001 Jazmine Stylus Pen for Microsoft Surface Water Container 12002 Water Container Omw12 | web shooters 12 rows in set (0.001 sec) 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 iID *Output* name ❘ price qtyInStock | P0002 | Microsoft Surface Pen | 59.99 | 10 | 50001 | Sea Salt | 11.90 | | 10001 | Water Bottle | 20.99 | 10 75 3 rows in set (0.001 sec) 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. *Output* custID fName Name OID VID ❘ qty | price 555000001 John Doe 2406000111001 | 2 10.00 555000001 John 555000001 |❘ John 24060001 12002 1 20.00 Doe 2406000211001 3 | 10.00 555000001 John Doe 24060002 12002 2 | 20.00 555000301 555000301 Sarah Sarah Browne Browne 555000301 Sarah Browne 24060003 Bmw12 24060003 a2345 24060003 P0001 11254.56 1 32.99 555000301 | Sarah Browne 24060003 | S0002 1 10.99 555000301 | Sarah Browne 24060003 | W9999 | 3 32.99 555000301 Sarah Browne 24868804 | DYB01 | 51 555000301 Sarah Browne 555000301 Sarah Browne 24060004 | GF001 | 24060004 50002 1 26.99 10 10.99 548618292 Johnson Washington 2406010110002 548618292 Johnson | Washington | 24060101 | 50002 548618292 Johnson Washington 24068101 W9999 | 49.99 1 10.99 548618292 Johnson Washington 548618292 Johnson Washington 548618292 Johnson Washington 24060102 S0002 10 10.99 24060102 | W9999 2 32.99 548618292 Johnson Washington 548618292 Johnson Washington 24060104 11234 | 548618292 Johnson Washington | 24060104 | 11001 | 548618292 Johnson Washington 24060105 21355 548618292 | Johnson | Washington | 24060106 | 10002 | 5 | 10.00 150.00 49.99 1 1 24060103 | 11001 24060103 | 50002 5 | 10.00 10.99 215.50 23 rows in set (0.000 sec) 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). | custID *Output* ❘fName | Name OID | 548618292 | Johnson | Washington | 24060101 1 row in set (0.001 sec) ****/

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. custID Output Name 000006798 | Jazmine Elzey 000007543 | Jalen Harrison 00000ac01 | Clark Kent 00000af15 Peter Parker 0000CD123 | Alice Johnson 0000PD456 | Bob Smith phone 706-456-3211 706-944-1230 | 281-360-8004 | 555-867-5309 555-123-4567 | 555-987-6543 | 000234561 | John Doe 202-423-5603 | 001111222 Dante Grante 984-263-1704 | 548618292 | Johnson Washington 954-151-1425 | 555000201 Lebron Smith 919-555-1234 | 555000202 | Kevin Smith 919-555-5678 | 555000301 | Sarah Browne 252-434-9830 | 555000301 | Sarah Browne 336-287-7890 | 835261882 | Jack Williams 336-517-1693 | 920111222 | Mary Price 920111222 | Mary Price 920111227 | Alonzo McKee 336-225-3344 | 704-334-3344 | 337-335-6688 920111233 | Sarah Sheet 336-889-0123 | 920111255 Kyle Haskett 336-768-5555 | 920111266 | Aiden Harbor 336-335-6567 | 920111444 | Miles Brown 920111444 | Miles Brown 920111444 Miles Brown | 920111666 | Jazmine Jenkins | 920111666 | Jazmine Jenkins +- 25 rows in set (0.001 sec) 336-776-2898 919-888-2898 | 980-776-8888 336-285-7777 | 336-777-7777 | 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 ****Output** iID name 21355 11234 Axe GF001 Flashlight Glass Food Storage 10002 Gym Bag S0002 | Himalayan Salst DY001 Instant Dry Yeast W9999 | Insulated Stainless Steel Water Bottle a2345 P0001 11001 Jazmine Stylus Pen for Microsoft Surface Water Container 12002 Water Container Omw12 | web shooters 12 rows in set (0.001 sec) 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 iID *Output* name ❘ price qtyInStock | P0002 | Microsoft Surface Pen | 59.99 | 10 | 50001 | Sea Salt | 11.90 | | 10001 | Water Bottle | 20.99 | 10 75 3 rows in set (0.001 sec) 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. *Output* custID fName Name OID VID ❘ qty | price 555000001 John Doe 2406000111001 | 2 10.00 555000001 John 555000001 |❘ John 24060001 12002 1 20.00 Doe 2406000211001 3 | 10.00 555000001 John Doe 24060002 12002 2 | 20.00 555000301 555000301 Sarah Sarah Browne Browne 555000301 Sarah Browne 24060003 Bmw12 24060003 a2345 24060003 P0001 11254.56 1 32.99 555000301 | Sarah Browne 24060003 | S0002 1 10.99 555000301 | Sarah Browne 24060003 | W9999 | 3 32.99 555000301 Sarah Browne 24868804 | DYB01 | 51 555000301 Sarah Browne 555000301 Sarah Browne 24060004 | GF001 | 24060004 50002 1 26.99 10 10.99 548618292 Johnson Washington 2406010110002 548618292 Johnson | Washington | 24060101 | 50002 548618292 Johnson Washington 24068101 W9999 | 49.99 1 10.99 548618292 Johnson Washington 548618292 Johnson Washington 548618292 Johnson Washington 24060102 S0002 10 10.99 24060102 | W9999 2 32.99 548618292 Johnson Washington 548618292 Johnson Washington 24060104 11234 | 548618292 Johnson Washington | 24060104 | 11001 | 548618292 Johnson Washington 24060105 21355 548618292 | Johnson | Washington | 24060106 | 10002 | 5 | 10.00 150.00 49.99 1 1 24060103 | 11001 24060103 | 50002 5 | 10.00 10.99 215.50 23 rows in set (0.000 sec) 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). | custID *Output* ❘fName | Name OID | 548618292 | Johnson | Washington | 24060101 1 row in set (0.001 sec) ****/

Database System Concepts
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
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 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,

 

 

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 (Alphabetical List of Popular Items):
-- List your SELECT statement below.
-- Task 3 (Alphabetical List of Least Items):
-- List your SELECT statement below.
-- Task 4 (Order Information):
-- List your SELECT statement below.
-- Task 5 (Order Frequency):
-- 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 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. custID Output Name 000006798 | Jazmine Elzey 000007543 | Jalen Harrison 00000ac01 | Clark Kent 00000af15 Peter Parker 0000CD123 | Alice Johnson 0000PD456 | Bob Smith phone 706-456-3211 706-944-1230 | 281-360-8004 | 555-867-5309 555-123-4567 | 555-987-6543 | 000234561 | John Doe 202-423-5603 | 001111222 Dante Grante 984-263-1704 | 548618292 | Johnson Washington 954-151-1425 | 555000201 Lebron Smith 919-555-1234 | 555000202 | Kevin Smith 919-555-5678 | 555000301 | Sarah Browne 252-434-9830 | 555000301 | Sarah Browne 336-287-7890 | 835261882 | Jack Williams 336-517-1693 | 920111222 | Mary Price 920111222 | Mary Price 920111227 | Alonzo McKee 336-225-3344 | 704-334-3344 | 337-335-6688 920111233 | Sarah Sheet 336-889-0123 | 920111255 Kyle Haskett 336-768-5555 | 920111266 | Aiden Harbor 336-335-6567 | 920111444 | Miles Brown 920111444 | Miles Brown 920111444 Miles Brown | 920111666 | Jazmine Jenkins | 920111666 | Jazmine Jenkins +- 25 rows in set (0.001 sec) 336-776-2898 919-888-2898 | 980-776-8888 336-285-7777 | 336-777-7777 | 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 ****Output** iID name 21355 11234 Axe GF001 Flashlight Glass Food Storage 10002 Gym Bag S0002 | Himalayan Salst DY001 Instant Dry Yeast W9999 | Insulated Stainless Steel Water Bottle a2345 P0001 11001 Jazmine Stylus Pen for Microsoft Surface Water Container 12002 Water Container Omw12 | web shooters 12 rows in set (0.001 sec)
Transcribed Image Text: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. custID Output Name 000006798 | Jazmine Elzey 000007543 | Jalen Harrison 00000ac01 | Clark Kent 00000af15 Peter Parker 0000CD123 | Alice Johnson 0000PD456 | Bob Smith phone 706-456-3211 706-944-1230 | 281-360-8004 | 555-867-5309 555-123-4567 | 555-987-6543 | 000234561 | John Doe 202-423-5603 | 001111222 Dante Grante 984-263-1704 | 548618292 | Johnson Washington 954-151-1425 | 555000201 Lebron Smith 919-555-1234 | 555000202 | Kevin Smith 919-555-5678 | 555000301 | Sarah Browne 252-434-9830 | 555000301 | Sarah Browne 336-287-7890 | 835261882 | Jack Williams 336-517-1693 | 920111222 | Mary Price 920111222 | Mary Price 920111227 | Alonzo McKee 336-225-3344 | 704-334-3344 | 337-335-6688 920111233 | Sarah Sheet 336-889-0123 | 920111255 Kyle Haskett 336-768-5555 | 920111266 | Aiden Harbor 336-335-6567 | 920111444 | Miles Brown 920111444 | Miles Brown 920111444 Miles Brown | 920111666 | Jazmine Jenkins | 920111666 | Jazmine Jenkins +- 25 rows in set (0.001 sec) 336-776-2898 919-888-2898 | 980-776-8888 336-285-7777 | 336-777-7777 | 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 ****Output** iID name 21355 11234 Axe GF001 Flashlight Glass Food Storage 10002 Gym Bag S0002 | Himalayan Salst DY001 Instant Dry Yeast W9999 | Insulated Stainless Steel Water Bottle a2345 P0001 11001 Jazmine Stylus Pen for Microsoft Surface Water Container 12002 Water Container Omw12 | web shooters 12 rows in set (0.001 sec)
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 iID *Output* name ❘ price qtyInStock | P0002 | Microsoft Surface Pen | 59.99 | 10 | 50001 | Sea Salt | 11.90 | | 10001 | Water Bottle | 20.99 | 10 75 3 rows in set (0.001 sec) 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. *Output* custID fName Name OID VID ❘ qty | price 555000001 John Doe 2406000111001 | 2 10.00 555000001 John 555000001 |❘ John 24060001 12002 1 20.00 Doe 2406000211001 3 | 10.00 555000001 John Doe 24060002 12002 2 | 20.00 555000301 555000301 Sarah Sarah Browne Browne 555000301 Sarah Browne 24060003 Bmw12 24060003 a2345 24060003 P0001 11254.56 1 32.99 555000301 | Sarah Browne 24060003 | S0002 1 10.99 555000301 | Sarah Browne 24060003 | W9999 | 3 32.99 555000301 Sarah Browne 24868804 | DYB01 | 51 555000301 Sarah Browne 555000301 Sarah Browne 24060004 | GF001 | 24060004 50002 1 26.99 10 10.99 548618292 Johnson Washington 2406010110002 548618292 Johnson | Washington | 24060101 | 50002 548618292 Johnson Washington 24068101 W9999 | 49.99 1 10.99 548618292 Johnson Washington 548618292 Johnson Washington 548618292 Johnson Washington 24060102 S0002 10 10.99 24060102 | W9999 2 32.99 548618292 Johnson Washington 548618292 Johnson Washington 24060104 11234 | 548618292 Johnson Washington | 24060104 | 11001 | 548618292 Johnson Washington 24060105 21355 548618292 | Johnson | Washington | 24060106 | 10002 | 5 | 10.00 150.00 49.99 1 1 24060103 | 11001 24060103 | 50002 5 | 10.00 10.99 215.50 23 rows in set (0.000 sec) 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). | custID *Output* ❘fName | Name OID | 548618292 | Johnson | Washington | 24060101 1 row in set (0.001 sec) ****/
Transcribed Image Text: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 iID *Output* name ❘ price qtyInStock | P0002 | Microsoft Surface Pen | 59.99 | 10 | 50001 | Sea Salt | 11.90 | | 10001 | Water Bottle | 20.99 | 10 75 3 rows in set (0.001 sec) 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. *Output* custID fName Name OID VID ❘ qty | price 555000001 John Doe 2406000111001 | 2 10.00 555000001 John 555000001 |❘ John 24060001 12002 1 20.00 Doe 2406000211001 3 | 10.00 555000001 John Doe 24060002 12002 2 | 20.00 555000301 555000301 Sarah Sarah Browne Browne 555000301 Sarah Browne 24060003 Bmw12 24060003 a2345 24060003 P0001 11254.56 1 32.99 555000301 | Sarah Browne 24060003 | S0002 1 10.99 555000301 | Sarah Browne 24060003 | W9999 | 3 32.99 555000301 Sarah Browne 24868804 | DYB01 | 51 555000301 Sarah Browne 555000301 Sarah Browne 24060004 | GF001 | 24060004 50002 1 26.99 10 10.99 548618292 Johnson Washington 2406010110002 548618292 Johnson | Washington | 24060101 | 50002 548618292 Johnson Washington 24068101 W9999 | 49.99 1 10.99 548618292 Johnson Washington 548618292 Johnson Washington 548618292 Johnson Washington 24060102 S0002 10 10.99 24060102 | W9999 2 32.99 548618292 Johnson Washington 548618292 Johnson Washington 24060104 11234 | 548618292 Johnson Washington | 24060104 | 11001 | 548618292 Johnson Washington 24060105 21355 548618292 | Johnson | Washington | 24060106 | 10002 | 5 | 10.00 150.00 49.99 1 1 24060103 | 11001 24060103 | 50002 5 | 10.00 10.99 215.50 23 rows in set (0.000 sec) 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). | custID *Output* ❘fName | Name OID | 548618292 | Johnson | Washington | 24060101 1 row in set (0.001 sec) ****/
Expert Solution
steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Similar questions
Recommended textbooks for you
Database System Concepts
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)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS