Given the following table definition:
SALES

————————————–
INVOICE_NO CHAR(20) NOT NULL
SALES_DATE DATE
SALES_PERSON VARCHAR(25)
REGION CHAR(20)
SALES_AMT DECIMAL(9,2)
Which of the following queries will return SALES information, sorted by SALES_PERSON, from A
to Z, and SALES_DATE, from most recent to earliest?

Given the following statement:
SELECT hyear, AVG(salary)
FROM (SELECT YEAR(hiredate) AS hyear, salary
FROM employee WHERE salary > 30000)
GROUP BY hyear
Which of the following describes the result if this statement is executed?

Which two of the following statements are true about the HAVING clause?

Given the following table definitions:
DEPARTMENT
————————–
DEPTNO CHAR(3)
DEPTNAME CHAR(30)
MGRNO INTEGER
ADMRDEPT CHAR(3)
EMPLOYEE
————————–
EMPNO INTEGER
FIRSTNAME CHAR(30)
MIDINIT CHAR
LASTNAME CHAR(30)
WORKDEPT CHAR(3)
Which of the following statements will list every employee number and last name, along with the
employee number and last name of their manager, including employees that have not been
assigned to a manager?

Given the following table:
EMPLOYEE
EMPID NAME INSTRUMENT
— ————— —–
1 Jagger, Mick 01
2 Richards, Keith 02
3 Wood, Ronnie 02
4 Watts, Charlie 03
5 Jones, Darryl 04
6 Leavell, Chuck 05
If the following query is executed:
SELECT name,
CASE WHEN instrument = ’01’ THEN ‘HARMONICA’
WHEN instrument = ’02’ THEN ‘GUITAR’
WHEN instrument = ’03’ THEN ‘DRUMS’
ELSE ‘UNKNOWN’
END AS instrument
FROM employee
What will be the results?

Given the following UPDATE statement:
UPDATE employees SET workdept =
(SELECT deptno FROM department WHERE deptno = ‘A01’)
WHERE workdept IS NULL
Which of the following describes the result if this statement is executed?

Given the following table:
CURRENT_EMPLOYEES
————————–
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
PAST_EMPLOYEES
————————–
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
Assuming both tables contain data, which of the following statements will NOT successfully add
data to table CURRENT_EMPLOYEES?

Given the following table definition:
SALES
————————–
SALES_DATE DATE
SALES_PERSON CHAR(20)
REGION CHAR(20)
SALES INTEGER
Which of the following SQL statements will remove all rows that had a SALES_DATE in the year

1995?

Given the following table definition:
EMPLOYESS
————————–
EMP ID INTEGER
NAME CHAR(20)
DEPT CHAR(10)
SALARY DECIMAL (10, 2)
COMMISSION DECIMAL (8, 2)
Assuming the DEPT column contains the values ‘ADMIN’, ‘PRODUCTION’, and ‘SALES’, which of
the following statements will produce a result data set in which all ADMIN department employees
are grouped together, all PRODUCTION department employees are grouped together, and all
SALES department employees are grouped together?

The following SQL statement:
DELETE FROM tab1 WHERE CURRENT OF csr1 WITH RR
Is used to perform which type of delete operation?