With reference to the data in below two tables: Table: Order OrderID CustomerID

Question

With reference to the data in below two tables:

Table: Order

OrderID    CustomerID EmployeeID OrderDate ShipperID

10308 2    7    1996-09-18                 3

10309 37   3    1996-09-19                 1

10310 77   8    1996-09-20                 2

10319 21   11   1998-09-20                 2

Table: Customer

CustomerID CustomerName         

3          Ann Kooi  

7          Adams Smith

8          Charlie Anthony

Write SQL (use INNER join) to display only Order.OrderId and Customer.CustomerName

Write SQL to display all Orders from Order table even if the corresponding customer rows are not found.

Write SQL to obtain count of total number of rows where OrderDate is older than year 1997.

Write SQL to obtain the row containing the latest order date.

Write a query to update ‘Ann Kooi’ name to ‘Ann Cooi’

Write a query to split CustomerName on First Name and Last Name using the space

Write a query to display CustomerName firstname only. (Eg. Ann, Adams and Charlie)

Note: Please show all the steps and explain all answer

Explanation / Answer

Here is the question was given by you contain a mistake that in table order the values of CoustomerId and Employee Id have been interchanged I have Given you the answer by correcting that.

You should not use the table name as ORDER because of it is a keyword so I changed it to Orders. It is not a good practice to use order as a table name.

Q)
Write SQL (use INNER join) to display only Order.OrderId and Customer.CustomerName

A)
SELECT Orders.OrderID,Customer.CustomerName FROM Orders INNER JOIN Customer ON Customer.CustomerID = Orders.CustomerID;

explanation:
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

Q)
Write SQL to display all Orders from Order table even if the corresponding customer rows are not found
A)
select * From Orders

explanation:
For this its not depend on the 2nd table so you can get all the data with out any joins.

Q)
Write SQL to obtain count of total number of rows where OrderDate is older than year 1997.
A)
SELECT COUNT(*) FROM Orders WHERE YEAR(OrderDate) < 1997

explanation:
Here we used year() function to get the count.

Q)
Write SQL to obtain the row containing the latest order date.
A)
SELECT OrderID,CustomerID,EmployeeID, MAX(OrderDate) AS OrderDate ,ShipperID FROM Orders;
Explanation:
Here I have used Max function for getting the latest date record.

Q)
Write a query to update ‘Ann Kooi’ name to ‘Ann Cooi’

A)
UPDATE Customer SET CustomerName='Ann Cooi' WHERE CustomerName='Ann Kooi';

Explanation:
Here we need to set the name with new name by finding with old name.

Q)
Write a query to split CustomerName on First Name and Last Name using the space
A)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(CustomerName, ' ', 1), ' ', -1) AS FirstName,
SUBSTRING_INDEX(SUBSTRING_INDEX(CustomerName, ' ', 2), ' ', -1) AS LastName
FROM Customer;

Explanation:
Here I have used the SUBSTRING_INDEX function and split the name using ' '.

Q)
Write a query to display CustomerName firstname only. (Eg. Ann, Adams and Charlie).
A)
SELECT SUBSTRING_INDEX(CustomerName, ' ', 1) AS firstName FROM Customer;

Explanation:
Here I have used the SUBSTRING_INDEX function and split the name using ' '.

If you need any clarification on this comment below I will reply you, Happy to help you.

If you like the answer give me thumbs up

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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