Reference no: EM133095466

Scenario 1

Table Customers:(cust)

Table Orders:(order)

These two tables will be joined on the columns id (table customer) and Cust_id (Table Orders)

Question 1. Write a SQL query to get all the customer who had placed orders with aggregated total order cost and total order count.

Return below columns

• Cust.id

• Cust.first_name

• Cust.last_name

• Cust.state

• Total_order_cost

• Total_order_count

Question 2. Write a SQL query to get the list of customers who hadn't place any orders before.

Return below columns:

• Cust.id

• Cust.First_name

• Cust.Last_name

• Cust.Phone_number

Question 3. Write a SQL query to return Customer record with highest aggregated total_order_cost Return below columns:
• Cust.id

• Cust.First_name

• Cust.Last_name

Question 4. Write a SQL query to return Customer record with 2nd highest aggregated total_order_cost Return below columns:
• Cust.id

• Cust.First_name

• Cust.Last_name

Scenario 2

Note:

The employees table stores employee details. There will be a new record inserted whenever there is a
change in the employee's details.

Question 5. Write a SQL Query to return all the employee details with latest record.

Scenario 3

Note:

The employees table stores employee's details. There are duplicated records captured in the table.

Question 6. Write a SQL Query to remove/delete the duplicate row and only keep one record per employee.