LAB OVERVIEW Lab 4 will introduce the various aspects of the SQL select statemen

Question

LAB OVERVIEW

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).

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

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.

Question 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

Question 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.

Database Model for Labs 4-7 customers PK customer id INTEGER orders PK order id INTEGER customer_first_name VARCHAR(20) customerlast name VARCHAR(20) customer_address VARCHAR(50) customer_city customer_state customer_zip customer phone CHAR(10) customer fax order_date DATETIME shipped_date DATETIME VARCHAR(20) CHAR(2) CHAR(5) FK1 customer id INTEGER FK2 employee_id INTEGER CHAR(10) Employees order details id INTEGER PK,FK1 order id INTEGER PK,FK2 item idINTEGER last_name VARCHAR(20) first name VARCHAR(20) FK1 manager idINTEGER order qty INTEGER tems Artists PK item id INTEGER PK artist id INTEGER title unit_price DECIMAL(9,2) VARCHAR(40) artist_name VARCHAR(30) FK1 artist id INTEGER

Explanation / Answer

Question 1

Select distinct order_id, order_date, shipped_date, DAYNAME(order_date) as 'Order_Day' from orders where shipped_date IS NOT NULL and DAYNAME(order_date) IN('Saturday' , 'Sunday') order by order_date desc;

Question 2

Select CONCAT(customer_first_name ,', ',customer_last_name) as Customer , CONCAT( '(' ,SUBSTRING(customer_phone,1,3) , ')' ,SUBSTRING(customer_phone,4,3),'-' , SUBSTRING(customer_phone,7,4) ) as Phone from customers order by customer_last_name;

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.