6 SQL Query questions: Write a query to display an invoice for order 693 includi

Question

6 SQL Query questions:

Write a query to display an invoice for order 693 including the title, quanty, price, and a calculated column to display the line item subtotal (quantity times price) with a heading of Subtotal.

Display the order id, last name, title , and quantity for any items where customers have ordered 2 or more of a particular item in an order.

The employees table contains a list of employees of a company. An employee may be managed by another employee. To denote an employee’s manager, the employee_id of the manager is entered into the manager_id field of the employee reporting to that manager. In order to produce a list of which managers supervise which employees, the table may be joined to itself using these two attributes (employee_id, manager_id). This is known as a SELF-JOIN. Use a SELF-JOIN on the employees table to produce a result containing two columns: the concatenated last_name and first_name of the manager, and the concatenated last_name and first_name of the employee. Label the first-column supervisor, and the second-column employee. Order the output by the supervisor’s last_name and first_name.

Write a query to display the order id, order date, ship date, and customer last name for all SHIPPED orders placed on the internet (no employee id). Show the most recently shipped orders first.

Write a query that displays the order id, order date, and employee name as a single field labeled Employee for each order that an employee assisted with.

Write a query that displays the order id, order date, customer name as a single field labeled Customer for all orders for the sales rep Thomas Hardy, sorted by customer last name.

Explanation / Answer

I have considered below table structure:

1.Orders:

orderid, cutomerId, employeeId, shipDate,OrderDate, quantity, price, title

2. Employees

employeeId, LastName,firstName, managerId

3. Customers

CustomerID, CustomerFirstName, CustomerLastName

Below are the queries in sequence of questions:

1. select title, quantity, price, (quantity*price) as Subtotal from Orders
where orderId = 693;

2. select o.orderId, c.customerLastName, o.quantity from orders o INNER JOIN
Customers c on o.CustomerID = c.CustomerID
group by o.CustomerID
having count(orderid) >2;

3. select (a.lastName+a.firstName) as supervisor, (b.lastName+b.firstName) as employee from employees a, employees b
where b.managerId = a.employeeId
order by a.lastName and a.firstName;

4. Select o.orderId, o.orderdate,o.shipDate,c.customerLastName
from orders o inner join customer c
on o.CustomerID = c.CustomerID
where o.EmployeeID is Null
order by shipDate;

5. SELECT orderId, orderdate, (e.FirstName+" "+e.LastName) as Name from Orders o
INNER JOIN Employees e on e.EmployeeID = o.EmployeeID;

6. SELECT o.orderId, o.orderdate,(c.customerFirstName+ c.customerLastName) as customerName from ((orders o
INNER JOIN Customers c on o.CustomerID = c.CustomerID)
INNER JOIN Employees e on o.EmployeeID = e.EmployeeID)
where (e.FirstName +" "+ e.LastName) = 'Thomas Hardy'
order by customerLastName;

Just check all queries.. These are self explanatory because I have used all full names.

In case of any issue please revert back.

Related Questions

Navigate

• Browse (All)
• Browse 6
• Subjects

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