The employee table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
SALARY NUMBER(7,2)
You need to display the names of employees on more than an average salary of all employees. Evaluate the SQL statement.
SELECT last_name, first_name FROM employee WHERE salary < AVG(salary);
Which change should you make to achieve the desired results?
A: Change the function in the Where clause.
B: Move the function to the select clause and add a group clause.
C: Use a sub query in the where clause to compare the average salary value.
D: Move the function to the select clause and add a group by clause and a having clause.
ANSWER: C
EXPLANATION: Answer C shows the correct way to change query, because function AVG can not be used in WHERE clause.
INCORRECT ANSWERS:
A: Usage of function AVG is correct.
B: This query does not require grouping to extract correct information from the table.
D: This query does not require to use GROUP BY and HAVING clauses to extract correct information from table.
You need to analyze how long your orders to be shipped from the date that the order is placed. To do this you must create a report that displays the customer number, date order, date shipped and the number of months in whole numbers from the time the order is placed to the time the order is shipped. Which statement produces the required results?
A: SELECT custid, orderdate, shipdate, ROUND(MONTHS_BETWEEN(shipdate,orderdate)) ‘Time Taken’ FROM ord;
B: SELECT custid, orderdate, shipdate, ROUND(DAYS_BETWEEN(shipdate,orderdate))/30 FROM ord;
C: SELECT custid, orderdate, shipdate, 5 ROUND OFF (shipdate-orderdate) ‘Time Taken’ FROM ord;
D: SELECT custid, orderdate, shipdate, MONTHS_BETWEEN (shipdate,orderdate) ‘Time Taken’ FROM ord;
ANSWER: A
EXPLANATION: Answer A shows the number of months (rounded to integer) between the date of order and the date of shipment.
INCORRECT ANSWERS:
B: Function, function DAYS_BETWEEN shows number of days between shipping date and order date.
C: Incorrect function ROUND OFF.
D: This command will show not rounded to integer value, like 8.6451613.
You need to create a report to display the ship date and order totals of your ordid table. If the order has not been shipped your report must display not shipped. If the total is not available your report must say not available. In the ordid table the ship date column has a data type of date the total column has a data type of number. Which statement do you use to create this report?
A: SELECT ordid, shipdate ‘Not shipped’, total ‘Not available’ FROM order;
B: SELECT ordid, NVL(shipdate, ‘Not shipped’), NVL (total, ‘Not available’) FROM order;
C: SELECT ordid, NVL(TO_CHAR(shipdate), ‘Not shipped’), NVL(TO_CHAR(total), ‘Not available’) FROM order;
D: SELECT ordid, TO_CHAR(shipdate, ‘Not shipped’), TO_CHAR(total, ‘Not available’) FROM order;
ANSWER: C
EXPLANATION: Answer C shows correct syntax of command NVL.
INCORRECT ANSWERS:
A: This command will show ALL data with name substitution of columns shipdate and total.
B: Incorrect usage for NVL command, because shipdate and total are needed to be converted into VARCHAR2 type with TO_CHAR function. Both parameters of NVL command have to have the same data type.
D: Incorrect syntax. TO_CHAR command is used just to convert data type into VARCHAR2 data type, it have nothing to do with NULL values in columns.
Contato