Q. Scott issues the SQL statements: to be done
CREATE TABLE dept
(deptno number(2)
dname VARCHAR2(14)
loc VARCHAR2(13));
GRANT SELECT ON DEPT TO SUE;
If Sue needs to select from Scott’s DEPT table, which command should she use?
A. SELECT * FROM DEPT
B. SELECT * FROM SCOTT. DEPT.
C. SELECT * FROM DBA.SCOTT.DEPT.
D. SELECT * FROM ALL_USERS WHERE USER_NAME = ‘SCOTT’ AND TABLE NAME= ‘DEPT’;
Answer: B
Q. Click the Exhibit button and examine the data in the EMPLOYEES and EMP_HIST tables.
EMPLOYEES
EMPLOYEE_ID NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
EMP_HIST
EMPLOYEE_ID NAME JOB_ID SALARY
101 Smith SA_CLERK 2000
103 Chris IT_CLERK 2200
104 John HR_CLERK 2000
105 Smith AD_ASST 3000
108 Jennifer HR_MGR 4500
The EMP_HIST table is updated at the end of every year. The employee ID, name, job ID, and salary of each existing employee are modified with the latest date. New employee details are added to the table.
Which statement accomplishes this task?
A. UPDATE emp_hist SET employee_id, name, job_id, salary =
(SELECT employee id, name, job_id, salary FROM employees)
WHERE employee_id IN (SELECT employee_id FROM employees),
B. MERGE INTO emp_hist eh USING employees e ON (eh. Employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE SET eh. Name= e.name, Ch.job_id = e.job_id, Eh. Salary = e.salary
WHEN NOT MATCHED THEN INSERT (eh.employee_id,eh.name,eh.job_id,eh.salary) VALUES (e.employee_id, e.name, e.job_id, e.salary);
C. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE emp_hist
SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary
WHEN NOT MATCHED THEN INSERT INTO emp_hist
VALUES (e.employee_id, e.name, e.job_id, e.salary);
D. MERGE INTO emp_hist eh USING employees e
WHEN MATCHED THEN UPDATE emp_hist
SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary
WHEN NOT MATCHED THEN INSERT INTO emp_hist
VALUES (e.employee_id, e.name, e.job_id, e.salary);
Answer: B
Q. Click the Exhibit button to examine the data of the EMPLOYEES table
EMPLOYEES (EMPLOYEE ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 230 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 HR_MGR 5000
106 Bryan 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Evaluate this SQL statement:
SELECT e.employee_id “emp_id”, e.emp_name “Employee”, e.salary,
m.employee_id “Mgr_id”, m.emp_name “Manager” FROM employees e,employees m
WHERE e.mgr_id = m.employee_id AND e.salary > 4000 What is its output?
A.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000 Bob
120 Ravi 6500 110 Ravi
108 Jennifer 6500 110 Jennifer
103 Chris 4200 120 Chris
105 Diana 5000 108 Diana
B.
Emp_id Employee Salary Mgr_id Manager
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer
C.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer
D.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000 110 Bob
120 Ravi 6500 120 Ravi
108 Jennifer 6500 108 Jennifer
109 Chris 4200 105 Chris
105 Diana 5000 105 Diana
E. The SQL statement produces an error.
Answer: B
Q. Click the Exhibit button and examine the data in the EMPLOYEES table.
LAST_NAME DEPARTMENT_ID SALARY
Get 2 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
….
Which three subqueires work? (Choose three)
A. SELECT * FROM employees Where salary > (SELECT MIN(salary)
FROM employees GROUP BY department_id)
B. SELECT * FROM employees WHERE salary = (SELECT AVG (salary)
FROM employees GROUP BY department_id)
C. SELECT distinct department-id FROM employees
WHERE salary> ANY (SELECT AVG (salary) FROM employees GROUP BY department_id)
D. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG (salary)
FROM employees GROUP BY department_id)
E. SELECT last_name FROM employees WHERE salary> ANY (SELECT MAX (salary)
FROM employees GROUP BY department_id)
F. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG (salary)
FROM employees GROUP BY AVG (SALARY))
Answer: CDE
Q. Click the Exhibit button and examine the data on the EMPLOYEES table
EMPLOYEES
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 3500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
On the EMPLOYEES table, EMPLOYEE_ID is the primary key.
MGR_ID is the ID of managers and refers to the EMPLOYEE_ID.
The JOB_ID column is a NOT NULL column
Evaluate This DELETE statement
DELETE employee_id, salary, job_id FROM employees WHERE dept_id = 90
Why does the DELETE statement fail when you execute it?
A. there is no row with dept_id 90 in the EMPLOYEES table
B. you cannot delete the JOB_ID column because it is a NOT NULL column
C. you cannot specify column names in the DELETE clause of the DELETE statement.
D. You cannot delete the EMPLOYEE_ID column because it is the primary key of the table
Answer: C
Q. Check the Exhibit button to examine the structures of the Employees and TAX tables Employees.
EMPLOYEE_ID NUMBER NOT NULL. PRIMARY KEY
EMP_NAME VARCHAR(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_TO column
DEPARTMENT_ID NUMBER Foreign Employee_ID column of the DEPARTMENT table
TAX
MIN_SALARY NUMBER
MAX_SALARY NUMBER
TAX_PERCENT NUMBER Percentage tax for given salary range
You need find the percentage tax applicable for each employee. Which SQL statement would you use?
A. SELECT employee_id salary, tax_present FROM employee, tax t
WHERE e salary BETWEEN t.min_salary AND t.max_salary,
B. SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE e.salary> Lmin_salary,tax_percent
FROM employees e, tax t
WHERE MIN(e salary)= t.min_salary
C. SELECT employee_id, salary, tax_percent FROM employees e, tax t
WHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary;
D. You cannot find the information because there is no common column between the two tables.
Answer : A
Q . Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12.JAN-2000 15 10000
101 09-MAR-2000 40 8000
102 09-MAR-2000 35 12500
103 15-MAR-2000 15 12000
104 25-JUN-2000 15 6000
105 18-JUL-2000 20 5000
106 18-JUL-2000 35 7000
107 21-JUL-2000 20 6500
108 04-AUG-2000 10 8000
CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Linda New York
Evaluate the SQL statement:
SELECT * FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Smith')
What is the result when the query is executed?
A.
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
102 09-MAR-2000 35 12500
106 18-JUL-2000 35 7000
108 04-AUG-2000 10 8000
B.
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
102 09-MAR-2000 35 12500
106 18-JUL-2000 35 7000
C.
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
108 04-AUG-2000 10 8000
D. The query fails because the subquery returns more than one row.
E. The query fails because the outer query and the inner query are using different tables.
Answer: D
Q. Click the Exhibit button to examine the structure of the EMPOLOYEES, DEPARTMENTS and TAX tables.
EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL primary key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER Reference EMPLOYEE_ID Column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID TO column of the DEPARTMENT table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL primary key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER Reference MGR_ID column of the EMPLOYEES table
TAX
MIN_SALARY NUMBER
MAX_SALARY NUMBER
TAX_PERCENT NUMBER
For which situation would you use a nonequijoin query?
A. to find the tax percentage for each of the employees
B. to list the name, job id, and manager name for all the employees
C. to find the name, salary and the department name of employees who are not working with Smith
D. to find the number of employees working for the Administrative department and earning less than 4000
E. to display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned
Answer: A
Q. Click the Exhibit button to examine the structure of the EMPLOYEES, DEPARTMENTS and LOCATIONS tables.
EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMNET_ID column of the DEPARTMENTS table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table
LOCATION_ID NUMBER Foreign key to LOCATION_ID column of the LOCATIONS table
LOCATIONS
LOCATIONS_ID NUMBER NOT NULL, Primary Key
CITY VARCHAR2(30)
Which two SQL statements produce the ;name, department name, and the city of all the employees who earn more than 10000? (Choose Two).
A. SELECT emp_name, department_name, city
FROM employees e
JOIN departments d
USING (department_id)
JOIN locations 1
USING (location_id)
WHERE salary > 10000;
B. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
JOIN ON (e. department_id = d. department id)
AND (d.location_id = 1.location_id)
AND salary > 10000;
C. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
WHERE salary > 1000;
D. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
WHERE e.department_id = d.department_id
AND d.location_id = 1.location_id
AND salary > 10000;
E. SELECT emp_name, department_name, city
FROM employees e
NATURAL JOIN departments, locations
WHERE salary > 10000;
Answer: BD
Q . Click the Exhibit button to examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)
EMPLOYEE_ID EMP_NINE DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 HR_MGR 5000
106 Bryan 40 110 AD_ASST 5000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee’s manager, for all the employees who have a manager and earn more than 4000?
A. SELECT employee_id ”Emp_id”, emp_name “Employee”.
Salary,
Employee_id “Mgr_id”, emp_name “Manager”
FROM employees
WHERE salary > 4000
B. SELECT e.employee_id “Emp_id”, e.emp_name “Employee”
e.salary
m employee_id “Mgr_id”, m.emp_name “Employee”.
FROM employees e.employees m
WHERE e.mgr_id = m.mgr_id
AND e.salary > 4000;
C. SELECT e. employee_id “Emp_id”. E.emp_name “Employee”
e.salary
m employee_id “Mgr_id” m.emp_name “Manager”
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000
D. SELECT e.employee_id”Emp_id” e.emp_name “Employee”
e.salary.
m.mgr_id “Mgr_id”, m.emp_name “Employee”,
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;
Answer: C
Q. Click the Exhibit button and examine the data in the EMPLOYEES and EMP_HIST tables.
EMPLOYEES
EMPLOYEE_ID NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
EMP_HIST
EMPLOYEE_ID NAME JOB_ID SALARY
101 Smith SA_CLERK 2000
103 Chris IT_CLERK 2200
104 John HR_CLERK 2000
105 Smith AD_ASST 3000
108 Jennifer HR_MGR 4500
The EMP_HIST table is updated at the end of every year. The employee ID, name, job ID, and salary of each existing employee are modified with the latest date. New employee details are added to the table. Which statement accomplishes this task?
A. UPDATE emp_hist
SET employee_id, name, job_id, salary =
(SELECT employee id, name, job_id, salary
FROM employees)
WHERE employee_id IN
(SELECT employee_id
FROM employees),
B. MERGE INTO emp_hist eh
USING employees e
ON (eh. Employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET eh. Name= e.name,
Ch.job_id = e.job_id,
Eh. Salary = e.salary
WHEN NOT MATCHED THEN
INSERT (eh.employee_id,eh.name,eh.job_id,eh.salary) VALUES (e.employee_id, e.name, e.job_id, e.salary);
Answer: B
Q. Click the Exhibit button to examine the data of the EMPLOYEES table
EMPLOYEES (EMPLOYEE ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 230 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 HR_MGR 5000
106 Bryan 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Evaluate this SQL statement:
SELECT e.employee_id “emp_id”, e.emp_name “Employee”, e.salary,
m.employee_id “Mgr_id”, m.emp_name “Manager”
FROM employees e,employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000
What is its output?
A.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000 Bob
120 Ravi 6500 110 Ravi
108 Jennifer 6500 110 Jennifer
103 Chris 4200 120 Chris
105 Diana 5000 108 Diana
B.
Emp_id Employee Salary Mgr_id Manager
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer
C.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer
D.
Emp_id Employee Salary Mgr_id Manager
110 Bob 8000 110 Bob
120 Ravi 6500 120 Ravi
108 Jennifer 6500 108 Jennifer
109 Chris 4200 105 Chris
105 Diana 5000 105 Diana
E. The SQL statement produces an error.
Answer: B
Q. Click the Exhibit button and examine the data in the EMPLOYEES table.
LAST_NAME DEPARTMENT_ID SALARY
Get 2 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
….
Which three subqueires work? (Choose three)
A. SELECT *
FROM employees
Where salary > (SELECT MIN(salary)
FROM employees
GROUP BY department_id)
B. SELECT *
FROM employees
WHERE salary = (SELECT AVG (salary)
FROM employees
GROUP BY department_id)
C. SELECT distinct department-id
FROM employees
WHERE salary> ANY (SELECT AVG (salary)
FROM employees
GROUP BY department_id)
D. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG (salary)
FROM employees
GROUP BY department_id)
E. SELECT last_name
FROM employees
WHERE salary> ANY (SELECT MAX (salary)
FROM employees
GROUP BY department_id)
F. SELECT department_id
FROM employees
WHERE salary > ALL (SELECT AVG (salary)
FROM employees
GROUP BY AVG (SALARY))
Answer: CDE
Q . Click the Exhibit button and examine the data on the EMPLOYEES table
EMPLOYEES
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 3500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID.
The JOB_ID column is a NOT NULL column
Evaluate This DELETE statement
DELETE employee_id, salary, job_id
FROM employees
WHERE dept_id = 90
Why does the DELETE statement fail when you execute it?
A. there is no row with dept_id 90 in the EMPLOYEES table
B. you cannot delete the JOB_ID column because it is a NOT NULL column
C. you cannot specify column names in the DELETE clause of the DELETE statement.
D. You cannot delete the EMPLOYEE_ID column because it is the primary key of the table
Answer: C
Q . Check the Exhibit button to examine the structures of the Employees and TAX tables
Employees.
EMPLOYEE_ID NUMBER NOT NULL. PRIMARY KEY
EMP_NAME VARCHAR(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_TO column
DEPARTMENT_ID NUMBER Foreign Employee_ID column of the DEPARTMENT table
TAX
MIN_SALARY NUMBER
MAX_SALARY NUMBER
TAX_PERCENT NUMBER Percentage tax for given salary range
You need find the percentage tax applicable for each employee. Which SQL statement would you use?
A. SELECT employee_id salary, tax_present
FROM employee, tax t
WHERE e salary BETWEEN t.min_salary AND t.max_salary,
B. SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE e.salary> Lmin_salary,tax_percent
FROM employees e, tax t
WHERE MIN(e salary)= t.min_salary
WHERE MIN(e salary)= t.max_salary
D. You cannot find the information because there is no common column between the two tables.
Answer : A