Lab 4 will introduce the various aspects of the SQL select statement and the methods of retrieving data from the database tables. This lab will also introduce the fundamentals of updating and deleting records. This lab may be completed using either DeVry?s Omnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the OM database tables. The lab will utilize a set of tables that are represented by the ERD ( OM_ERD.docx ) and are created and populated by the script file ( create_OM_db.sql ).

1. The Marketing Department has requested a new report of shipped orders for which the order was placed on either a Saturday or a Sunday. Write a query which displays the order id, order date, shipped date, along with a calculated column labeled ?Order_Day? showing the day of the week the order was placed (use the DAYNAME function). Only display orders that have shipped and were placed on a Saturday or Sunday. Sort by order date with most recent orders at the top.

***Using SELECT DISTINCT and IN statements

2. Using the SUBSTRING and CONCAT functions, write a query to display each customer name as a single field in the format ?Jones, Tom? with a heading of Customer along with the customer_phone field in a nicely formatted calculated column named Phone. For example, a record containing the customer_phone value 6145535443 would be output with parentheses, spaces, and hyphens, like this: (614) 555-5443. Sort by last name.

Transcribed Image Text:

customers PK customer_id customer_first_name customer_last_name customer_address customer_city customer_state customer_zip customer_phone customer_fax PK items order_details PK,FK1 order id INTEGER PK,FK2 item_id INTEGER item_id INTEGER title unit_price VARCHAR(20) VARCHAR(20) VARCHAR(50) VARCHAR(20) order_qty INTEGER FK1 artist_id CHAR(2) CHAR(5) CHAR(10) CHAR(10) INTEGER VARCHAR(40) DECIMAL(9,2) INTEGER PK order_id FK1 FK2 orders -OHPK artist_id order_date DATETIME shipped_date DATETIME customer_id employee_id INTEGER Artists INTEGER INTEGER INTEGER Employees PK employee id INTEGER last_name VARCHAR(20) first_name VARCHAR(20) FK1 manager_id INTEGER artist_name VARCHAR(30) customers PK customer_id customer_first_name customer_last_name customer_address customer_city customer_state customer_zip customer_phone customer_fax PK items order_details PK,FK1 order id INTEGER PK,FK2 item_id INTEGER item_id INTEGER title unit_price VARCHAR(20) VARCHAR(20) VARCHAR(50) VARCHAR(20) order_qty INTEGER FK1 artist_id CHAR(2) CHAR(5) CHAR(10) CHAR(10) INTEGER VARCHAR(40) DECIMAL(9,2) INTEGER PK order_id FK1 FK2 orders -OHPK artist_id order_date DATETIME shipped_date DATETIME customer_id employee_id INTEGER Artists INTEGER INTEGER INTEGER Employees PK employee id INTEGER last_name VARCHAR(20) first_name VARCHAR(20) FK1 manager_id INTEGER artist_name VARCHAR(30)

Answer rating: 100% (QA)

1 Select distinct orderid orderdate shippeddate DAYNAMEorderdate as OrderDay ... View the full answer