DBMS with SQL
desg
section_d-query_errors.docxSECTION D
2)Display the item number and total cost for each order line (total cost = no of items X item cost). Name the calculated column TOTAL COST.
Answer:
SELECT item_number, no_of_items * item_cost “TOTAL COST”
FROM ORDER_LINE
4)Display the order number and client number from the ORDER table. Output the result in the format. Client <clientno> ordered <orderno>
Answer:
SELECT ‘Client ‘+ clientno+’ordered ‘+ orderno AS result
FROM ORDER
6)Display the client name and order date for all orders using the traditional method.
Answer:
SELECT name, order_date
FROM
CLIENT c INNER JOIN ORDER o
ON (c.clientno = o.clientno);
7)Repeat query (7) but also display all clients who have never ordered anything.
Answer:
SELECT name, order_date
FROM
CLIENT c LEFT OUTER JOIN ORDER o
ON (c.clientno = o.clientno);
8) Display the client name and order date for all orders using the natural join keywords.
SELECT name, order_date
FROM
CLIENT NATURAL JOIN ORDER;
9) Display the client name and order date for all orders using the JOIN . . . USING method.
SELECT name, order_date
FROM CLIENT c JOIN ORDER o
USING (clientno);
10) Display the client number, order date and shipping date for all orders where the shipping date is between three and six months after the order date.
SELECT clientno, order_date, shipping_date
FROM
CLIENT c,
ORDER o,
ORDER_LINE ol
WHERE c.clientno = o.clientno
AND o.orderno = ol.orderno
AND shipping_date BETWEEN ADD_MONTHS(shipping_date,3) AND ADD_MONTHS(shipping_date,6);
16) Display the order number, order line number and the shipping date. If the shipping date is null, display the string <not shipped yet>.
SELECT orderno, order_line_number, NVL(shipping_date,’<not shipped yet>’)
FROM ORDER_LINE
18)Display the clientno and total value for all orders placed by that client. Output the result in the following format: Client <clientno> has placed orders to the value of <total value>
SELECT ‘Client ‘+clientno+’ has placed order to the value of ‘+ SUM(no_of_items*item_cost)
FROM ORDER_LINE
GROUP BY clientno
19) Display all clients whose name begins with the letter J or contains the letter M anywhere or contains E as the third letter.
SELECT *
FROM CLIENT
WHERE UPPER(name) LIKE ‘J%’
OR upper(name) LIKE ‘%M%’
OR
Upper(name) LIKE ‘??E%’
20)Using a set operator, display the client number of all clients who have never placed an order.
Answer:
SELECT clientno
FROM CLIENT
MINUS
SELECT clientno
FROM ORDER
21)Using a set operator, display the client number of all clients who have ever placed an order and whose name does not contain the string Sm.
SELECT clientno
FROM CLIENT
WHERE INSTR(name,’Sm’) = 0
INTERSECT
SELECT clientno
FROM ORDER
23)Display the client name for all clients who have placed an order where any order line has more than 3 items. Do not use a table join anywhere in your query.
SELECT name
FROM CLIENT c,
ORDER o,
ORDER_LINE ol
WHERE c.clientno = o.clientno
AND o.orderno = ol.orderno
AND ol.no_of_items> 3
26)Display the earliest shipping date in the format: DD/MON/YYYY
SELECT to_date(MIN(shipping_date),’DD/MON/YYYY’)
FROM ORDER_LINE
27)Display the order number and the number of months since the order was shipped for all orders that have been shipped in the last year (365 days). (Hint: Unshipped orders will have a null value).
SELECT o.orderno, MONTHS_BETWEEN(shipping_date,order_date)
FROM ORDER o,
ORDER_LINE ol
WHERE o.orderno = ol.orderno
AND shipping_date BETWEEN sysdate AND sysdate-365
29)Display the surname for all employees who earn less the average salary of all those employees in the department with the lowest average salary.
Answer:
SELECT lastname
FROM EMPLOYEE e
WHERE salary < (SELECT MIN(avg_salary) FROM (SELECT AVG(salary) avg_salary, department
FROM EMPLOYEE
GROUP BY department))
30)Display the client number and the total number of orders the client has placed for all clients who have placed at least one order in 2011. (Hint: Use exists).
U did not done this query and my Professor needs it.
