Q. The EMPLOYEES table has these columns:
LAST_NAME VARCHAR2(35)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER (5,2)
You want todisplay the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?
A. SELECT last_name, (salary*12)* commission_Pct FROM EMPLOYEES;
B. SELECT last_name, (salary*12)* IFNULL(commission_pct,0) FROM EMPLOYEES;
C. SELECT last_name, (salary*12)* NVL2(commission_pct,0) FROM EMPLOYEES;
D. SELECT last_name, (salary*12)* NVL(commission_pct,0) FROM EMPLOYEES;
Answer: D
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
Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin paced his orders?
A. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin' AND ord_date IN ('18-JUL-2000'; 21-JUL-2000');
B. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id=(SELECT cust_id FROM
customers WHERE cust_name= 'Martin'));
C. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cst_name='Martin');
D. SELECT ord_id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin')
Answer: B
Q. Evaluate the SQL statement:
1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4 FROM employees
5 GROUP BY dept_id)b
6 WHERE a.dept_id = b.dept_id 7 AND a.sal
What is the result of the statement?
A. The statement produces an error at line1.
B. The statement produces an error at line3.
C. The statement produces an error at line6.
D. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary than the maximm salary aid in the company.
E. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Answer: E
Q. Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
EMP_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 - 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 - SI_DIR - 6500
DEPARTMENTS
DEPARTMENT_ID - DEPARTMENT NAME
10 - Admin
20 - Education
30 - IT
40 - Human Resources
Also examine the SQL statements that create the EMPLOYEES and DEPARTMENTS tables:
CREATE TABLE departments
(department_id NUMBER PRIMARY KEY, department_name VARCHAR2(30));
CREATE TABLE employees
(EMPLOEE_ID NUMBER PRIMARY KEY, EMP_NAME VARCHAR2(20),
DEPT_ID NUMBER REFERENCES departments (department_id) MGR_ID NUMBER REFERENCES employees(employee id), JOB_ID VARCHAR2(15).
SALARY NUMBER);
On the EMPLOYEES table, EMPLOYEE_ID is the primary key MGR_ID is the ID of mangers and refers to the EMPLOYEE_ID DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table
On the DEPARTMENTS table, DEPARTMENT_ID is the primary key. Examine this DELETE statement: DELETE FROM departments WHERE department id=40; What happens when you execute the DELETE statement?
A. Only the row with department ID 40 is deleted in the DEPARTMENTS table.
B. The statement fails because there are child records in the EMPLOYEES table with department ID 40.
C. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.
D. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 106 and 110 and the employees working under employee 110 are deleted from the EMPLOYEES table.
E. The row with department ID 40 is deleted in the DEPARTMENTS table. Also all the rows in the EMPLOYEES table are deleted.
F. The statement fails because there are no columns specified in the DELETE clause of the DELETE statement.
Answer: B
Q. Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view. Which option enables Scott to eliminate the need to qualify the view with the name MARY.EMP_DEPT_LOC_VU each time the view is referenced?
A. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE PRIVATE SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym
B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.
C. Scott can create a synoym for the EMP_DEPT_LOC_VU by using the command CREATE LOCAL SYNONYM EDL_VU FOR mary.emp dept_LOC_uv; then he can prefix the columns with the synonym.
D. Scott can create a synomym for the EMP_DEPT_LOC_VU by using the command CRETE LOCAL SYNONYM EDL_VU ON mary(EMP_DEPT_LOC_VU); then he can prefix the columns with this synonym
E. Scott cannot create a synonym because synonyms can be created only for tables.
F. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and grant SELECT privilege on that synonym to Scott.
Answer: B
Q. Evaluate the set of SQL statements:
CREATE TABLE dept (deptbi NUMBER (2) dname VARCHAR2(14), Ioc VARCHAR2(13)); ROLLBACK;
DESCRIBE DEPT What is true about the set?
A. The DESCRIBE DEPT statement displays the structure of the DEPT table
B. The ROLLBACK statement frees the storage space occupied by the DEPT table.
C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist
D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
Answer: A
Q. Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
Column name Data Type Remarks
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 DEPARTMENT_ID column of the DEPARTMENT table
DEPARTMENTS
Column name Data Type Remarks
DEPARTMENT_ID NUMBER NOT NULL, Primary key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table
Evaluate this SQL statement;
SELECT employee_id, e.department_id, department_name, salary
FROM employees e, departments d
WHERE e. department_ud=d.department_id;
Which SQL statement is equivalent to the above SQL statement?
A. SELECT employee_id, department_id, department_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);
B. SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments d ON e.department_id=d.department_id;
C. SELECT employee_id, department_id, department_name, salary FROM employees e JOIN departments d ON e.department_id=d.department_id;
D. SELECT employee_id, department_id, department_name, salary FROM employees JOIN departments USING (e.department_id, d.department_id);
Answer: C
Q. Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12'Annual Salary' FROM employees;
B. SELECT ename, salary* 12 "Annual Salary" FROM employees
C. SELECT ename, salary* 12 AS Annual Salary FROM employees;
D. SELECT ename, salary* 12 AS INITCAP("ANNUAL SALARY") FROM employees
Answer:B
Q. Examine the description of the MARKS table:
STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30)
SUBJ1 NUMBER(3)
SUBJ2 NUMBER(3)
SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects
Examine this SELECT statement based on the MARKS table:
SELECT subj1+subj2 total_marks, std_id
FROM marks
WHERE subj1 > AVG (subj1) AND subj2 > AVG (subj2)
ORDER BY total_marks;
What us the result of the SELECT statement?
A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.
B. The statement returns an error at the SELECT clause
C. The statement returns an error at the WHERE clause
D. The statement returns an error at the ORDER BY clause
Answer: C
Q. You want to display the titles of books that meet these criteria:
1. Purchased before January 21, 2001
2. Price is less than $ 500 or greater than $ 900
You want to sort the result by their date of purchase, starting with the most recently bought book. Which statement should you use?
A. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '21 - Jan-2001' ORDER BY purchase_date;
B. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_dae< '21-jan-2001' ORDER BY purchase date ASC;
C. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
D. SELECT BOOK_title FROM books WHERE price < 500 OR>900 AND purchase_date<'21-JAN-2001' ORDER BY purchase date DESC;
E. SELECT book_title FROM books WHERE (price< 500 OR price> 900) AND purchase date> '21 - JAN-2001' ORDER BY purchase date ASC;
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. You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was created) How do you obtain the definition of the view?
A. Use the DESCRIBE command on the EMP_DEPT VU view
B. Use the DEFINE VIEW command on the EMP_DEPT VU view
C. Use the DESCRIBE VIEW command on the EMP_DEPT VU view
D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view
E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view
F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view
Answer: D
Q. You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables
EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME
The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table.
You want to modify the view by adding a fourth column, MANAGER_Id of NUMBER data type from the EMPLOYEES table. How can you accomplish this task?
A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER),
B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);
C. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name Department_name, manager_id FROM employees e, departments d WHERE department_id = d.department_id;
D. MODIFY VIEW emp_depat_vu AS SELECT employee_id, employee_name, Department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT emplouee_id, employee_ name, Department_name, manager _id FROM employees e, departments d WHERE e.department_id=d.department_id;
F. You must remove the existing view first, and then run the CRATE VIEW command with a new column list to modify a view.
Answer: E
Q. Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which UPDATE statement is valid?
A. UPDATE new_employees SET name=(SELECT last_name|| First_name FROM employees WHERE employee_id = 180)
B. UPDATE new_employees SET name = (SELECT Last_name || first_name FROM employees) WHERE employee_id = 180
C. UPDATE new_employees SET name = (SELECT last_name|| First_name FROM employees WHERE employee_id = 180 WHERE employee_id = (SELECT employee_id FROM new employees),
D. UPDATE new_employees SET name = (SELECT last name|| First_name FROM employees WHERE employee_id= (SELECT employee_id WHERE employee_id FROM new_employees)) WHERE employee_id = 180,
Answer: A
Q. You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address.
The CUSTOMER table has these columns:
CUST_ID NUMBER(4) NOT NULL
CUST_NAME VARCHAR2(100) NOT NULL
CUST_ADDRESS VARCHAR2(150)
CUST_PHONE VARCHAR(20)
Which SELECT statement accomplishes this task?
A. SELECT * FROM customers
B. SELECT name, address FROM customers;
C. SELECT id, name, address, phone FROM customers;
D. SELECT cust_name, cust_address FROM customers;
E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;
Answer: D
Q. Examine the structure of the EMPLOYEES table:
Column name Data type Remarks
EMPOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID
column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column Of the DEPARTMENTS table
You need to create a view called EMP_VU that allows the users to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?
A. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, Department_id FROM employees WHERE mgr_id IN (102,120);
B. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120);
C. CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTAL SAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;
D. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees
Answer: B
Q. The STUDENT_GRADES table has these columns
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER (4,3)
The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplishes this?
A. SELECT AVERAGE(gpa) FROM student_grades WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000'
B. SELECT COUNT (gpa) FROM student grades WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000'
C. SELECT MID (gpa) FROM student_grades WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000' D. SELECT AVG (gpa) FROM student_grades WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000'
D. SELECT SUM (gpa) FROM student_grades WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000' F. SELECT MEDIAN (gpa) FROM student_grades WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000'
Answer: D
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 "Manager" 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 "manager" FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;
E. SELECT e.employee_id "Emp_id", e.emp_name "Employee" ,e.salary, m.mgr_id "Mgr_id", m.emp_name "Manager" FROM employees e, employees m WHERE e.employee_id = m.employee_id AND e.salary > 4000;
Answer: C
Q. the ORDERS table has these columns
ORDER_ID NUMBER(4) NOT NULL
CUSTOMER_ID NUMBER(12) NOT NULL
ORDER_TOTAL NUMBER(10,2)
The ORDERS table tracks the Order number, the order total and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 200.00 dollars? (Choose Two).
A. SELECT customer_id, order_id, order_total FROM orders RANGE ON order_total (100 AND 2000) INCLUSIVE
B. SELECT customer_id, order_id, order_total FROM orders HAVING order total BETWEEN 100 and 2000
C. SELECT customer_id, order_id, order_total FROM orders WHERE order_total BETWEEN 100 and 2000
D. SELECT customer_id, orde_id, order_total FROM orders WHERE order_total >= 100 and <=2000 E. SELECT customer_id, order_id, order _total FROM orders WHERE order_total>= 100 and order_total <=2000.
Answer: CE
Q. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
SALARY NUMBER(6,2)
COMMISSION_PCT NUMBER(6)
You need to write a query that will produce these results:
1. Display the salary multiplied by the commission_pct
2. Exclude employees with a zero commission_pct
3. Display a zero for employees with a null commission value
Evaluate the SQL statement:
SELECT LAST_NAME, SALARY * COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
What does the statement provide?
A. all of the desired results
B. two of the desired results
C. one of the desired results
D. an error statement
Answer: C