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_city customer_state customer_zip customer_first_name VARCHAR(20) customer_last_name VARCHAR(20) customer_address VARCHAR(50) VARCHAR(20) PK INTEGER CHAR(2) CHAR(5) customer_phone CHAR(10) customer_fax CHAR(10) order_details PK,FK1 order_id INTEGER PK,FK2 item_id INTEGER order_qty INTEGER Database Model for Labs 4-7 items item_id INTEGER title VARCHAR(40) unit_price DECIMAL(9,2) FK1 artist_id INTEGER orders PK order_id INTEGER order_date DATETIME shipped_date DATETIME FK1 customer_id INTEGER FK2 employee_id INTEGER Artists OH -OHPK artist_id INTEGER PK FK1 artist_name VARCHAR(30) Employees employee id INTEGER last_name VARCHAR(20) first_name VARCHAR(20) manager_id INTEGER customers PK customer id customer_city customer_state customer_zip customer_first_name VARCHAR(20) customer_last_name VARCHAR(20) customer_address VARCHAR(50) VARCHAR(20) PK INTEGER CHAR(2) CHAR(5) customer_phone CHAR(10) customer_fax CHAR(10) order_details PK,FK1 order_id INTEGER PK,FK2 item_id INTEGER order_qty INTEGER Database Model for Labs 4-7 items item_id INTEGER title VARCHAR(40) unit_price DECIMAL(9,2) FK1 artist_id INTEGER orders PK order_id INTEGER order_date DATETIME shipped_date DATETIME FK1 customer_id INTEGER FK2 employee_id INTEGER Artists OH -OHPK artist_id INTEGER PK FK1 artist_name VARCHAR(30) Employees employee id INTEGER last_name VARCHAR(20) first_name VARCHAR(20) manager_id INTEGER

Answer rating: 100% (QA)

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