Question

This refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script.

Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. Please test your SQL statements in Workbench

1.       Using an INNER JOIN, select the order_id, order_date, shipped_date, fname, and customer_phone from the orders and customers tables. The fname is a column alias and should be formatted as “Smith, John” (last name, first name). Result table should be in order of order_id in ascending order.

2.       Write a SELECT statement that returns these columns using a JOIN of three tables:

a.       order_date from the orders table

b.       shipped_date from the orders table

c.       order_qty from the order_details table

d.       title from the items table

e.       artist from the items table

f.        unit_price from the items table

g.       Result table should be in order of order_date in ascending order

3.       Expand the SELECT statement from the previous question to include the customer last name and first initial from the customers table. The name should be formatted as “Lastname, F.” and given the column alias cname.

Order table is making by yourself. Please do as soon as possible. Please use the tidy tools for SQL proper format.

Answer 1

Solution:

SQL queries have been given below.

Kindly let me know if you require additional information.

Hope that helps.

Query 1 - Will give the required columns by using the INNER JOIN. (syntax has been validated)

The column names for 'firstname' and 'lastname' have been assumed, in case they are existing as a different column names in the table, kindly replace the text c.lastname, ", ", c.firstname - with the column names from database table.

select
  o.order_id,
  o.order_date,
  o.shipped_date,
  CONCAT(c.lastname, ", ", c.firstname) as fname,
  c.customer_phone
from
  Customers c
  INNER JOIN Orders o on c.Customer_id = o.Customer_id
Order by
  o.order_id

Query 2 : it has been assumed that there is a column item_id, which is primary key of items table and a foreign key in the order_details table. In case the column name is known by a different name in the database, kindly replace the item_id by actual column name.

Select
  o.order_date,
  o.shipped_date,
  od.order_qty,
  i.title,
  i.artist,
  i.unit_price
From
  orders o
  INNER JOIN order_details od on o.order_id = od.order_id
  INNER JOIN items i on od.item_id = i.item_id
order by
  o.order_date

Query 3

Select
  o.order_date,
  o.shipped_date,
  od.order_qty,
  i.title,
  i.artist,
  i.unit_price,
  CONCAT(
    c.lastname,
    ", ",
    SUBSTRING(c.firstname, 1, 1),
    "."
  ) as cname
from
  Customers c
  INNER JOIN Orders o on c.Customer_id = o.Customer_id
  INNER JOIN order_details od on o.order_id = od.order_id
  INNER JOIN items i on od.item_id = i.item_id
order by
  o.order_date