100 SQL Best Interviwe Question and Answer

                                                         
1. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A. INSERT INTO employees
VALUES (NULL.'John','Smith');
B. INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Smith');
C. INSERT INTO employees
VALUES (1000, 'John', 'Smith');
E. INSERT INTO employees (employee_id)
VALUES (1000);
F. INSERT INTO emloyees (employee_id_first_name,last_name)
VALUES (1000,'John', 'Smith');
Answer: CE F

2. Click the Exhibit button and examine the data in the EMPLOYEES table
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
...
Which three subqueries 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 > ANY (SELECT MAX (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

3.  Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR2(30)
SALARY NUMBER(8,2)
Which statement shows the maximum salary paid in each job category of each department?
A.  SELECT dept_id, job_cat, MAX (salary)
FROM employees
WHERE salary > MAX (salary);
B.  SELECT dept_id, job_cat, MAX (salary)
FROM employees
GROUP BY dept_id, job_cat
C.  SELECT dept_id, job_cat, MAX(salary)
FROM employees;
D.  SELECT dept_id, job_cat, MAX (salary)
FROM employees
GROUP BY dept_id;
E.  SELECT dept_id, job_cat, MAX (salary)
FROM employees
GROUP BY dept_id, job_cat, salary;
Answer: B

4.  Which SELECT statement will get the result 'elloworld' fromt the string 'HelloWorld'?
A.  SELECT SUBSTR ('HelloWorld',1) FROM dual;
B.  SELECT INITCAP(TRIM('HellowWorld', 1,1) FROM dual
C.  SELECT LOWER (SUBSTR ('HellowWorld', 2,1) FROM dual
D.  SELECT LOWER (SUBSTR('HellowWorld', 2,1) FROM dual
E.  SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual
Answer:  E

5.  Management has asked you to calculate the value 12* salary* commission_pct for all the employees in the EMP table.  The EMP table contains these columns:
LAST NAME VARCHAR2(35) NOT NULL
SALARY NUMBER(9,2)    NOT NULL
COMMISSION_PCT NUMBER(4,2)
Which statement ensures that a value is displayed in the calculated column for all employees?
A.  SELECT last_name, 12 * salary* commission_pct
FROM emp;
B.  SELECT last_name, 12 * salary* (commission_pct,0)
FROM emp;
C.  SELECT last_name, 12 * salary* (nvl(commission_pct,0)
FROM emp;
D.  SELECT last_name, 12 * salary* (decode(commission_pct,0))
FROM emp;
Answer: C

6.  Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHAR2(10)
START_DATE DATE
END_DATE DATE
Which two aggregate functions are valid on the START_DATE column? (Choose Two)
A.  SUM(start_date)
B.  AVG (start_date)
C.  COUNT (start_date)
D.  AVG(start_date, end_date)
E.  MIN (start_date)
F.  MAXIMUM (start_date)
Answer: CE

7.  From SQL*Plus, you issue this SELECT statement:
SELECT *
FROM orders;
You use this statement to retrieve data from a database table for _______________. (Choose all that apply)
A.  updating
B.  viewing
C.  deleting
D.  inserting
E.  truncating
Answer: BD

8.  Click the Exhibit button examine the data from the EMP table.
EMP_ID DEPT_ID COMMISSION
1 10 500
2 20 1000
3 10
4 10 600
5 30 800
6 30 200
7 10
8 20 300

The COMMISSION column shows the monthly commission earned by the employee.
Which three tasks would require subqueries or joins in order to be performed in a single step? (Choose three)
A.  deleting the records of employees who do notearn commission
B.  increasing the commission of employee 3 by the average commission earned in department 20
C.  finding the number ofemployees who do NOT  earn commission and are working fordepartment 20
D.  inserting into the table a new employee 10 who works for deartment 20 and earns a commission that is equal to the commission earned by employee 3
E.  creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and COMMISSION  of the EMP table
F.  decreasing the commission by 150 for the employees who are working in department 30 and earning a commission of more than 800.
Answer:  BDE

9.  Which four statements correctly describe functions that are available in SQL? (Choose four)
A.  INSTR returns the numeric position of a named character
B.  NVL 2 returns the first non-null expression in theexpression list.
C.  TRUNCATE rounds the column, expression, or value to n decimal places
D.  DECODE translates an expression after comparing it to each search value
E.  TRIM trims the leading or trailing characters (or both) from a character string.
F.  NVL  compares two expressions and returns null if they are equal, or the first expression if they are not equal.
G.  NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.
Answer : ADEG

10.  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

11.  Which two statements are true regarding the ORDER BY clause? (Choose two)
A. The sort is in ascending order by default
B. The sort is in descending order by default
C.  The ORDER BY clause must precede the WHERE clause.
D.  The ORDER BY clause is executed on the client side
E.  The ORDER BY clause comes last in the SELECT statement
F.  The ORDER BY clause is executed first in the query execution.
Answer: AE

12.  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

13.  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<b.maxsal;
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

14.  Which two tasks can you perform using only the TO_CHAR  function? (Choose two).

A.  convert 10 to 'TEN'
B.  convert '10' to 10
C.  convert '10' to '10'
D.  convert'TEN' to 10
E. Convert a date to a character expression
F.  convert a character expression to a date
Answer: BE

15.  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

16.  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

17.  Which SQL statement defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?
A.  CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk FOREIGN KEY deptno
REFERENCES dept deptno);
B.  CREATE TABLE EMP
(empno NUMBER(4),
ename VARCHAR2(35),
deptno NUMBER(7,2)
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C.  CRETE TABLE EM
(empno NUMBER(4),
ename VARCHAR2(35)
deptno NUMBER (7,2) NOT NULL,
CONSTRAINT em_deptno_fk REFERENCES dept (deptno)
FOREIGN KEY (deptno));
D.  CREATE TABLE EMP (empno NUMBER (4),
ename VARCHAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp deptno fk REFERENCES dept (deptno));
Answer: B

18.  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

19.  Which are DML statements? (Choose all that apply)
A.  COMMIT...
B.  MERGE...
C.  UPDATE...
D.  DELETE...
E.  CREATE...
F.  DROP...
Answer: ABCD

20.  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

21.  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

22. In which scenario would an index be most useful?
A.  The indexed column is declared as NOT NULL.
B.  The indexed columns are used in the FROM clause
C.  The indexed columns are part of an expression
D.  The indexed columns contains a wide range of values.
Answer: D  

23.  Which two are attributes of /SQL* Plus? (Choose two).
A.  /SQL * Plus commands cannot be abbreviated
B.  /SQL* Plus commands are accessed from a browser.
C.  /SQL*Plus commands are used to manipulate data in tables
D.  /SQL* Plus command manipulate table definitions in the database
E.  /SQL* Plus is the Oracle proprietary interface for executing SQL statements.
Answer: CE

24.  Which three statements about subqueries are true? (Choose three).
A.  A single row subquery can retrieve only one column and one row
B.  A single row subquery can retrieve only one row but many columns
C.  A multiple row subquery can retrieve multiple rows and multiple columns
D.  A multiple row subquery can be compared using the ">" operator
E.  A single row subquery can use the IN operator
F.  A multiple row subquery can use the "=" operator
Answer: BCD

25.  When should you create a role? (Choose two)
A.  to simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement
B.  to grant a group of related privileges to a user
C.  When the number of people using the database is very high
D.  to simplify the process of granting and revoking privileges
E.  to simplify profile maintenance for a user who is constantly traveling.
Answer:  BD

26.  Which clause would you use in a  SELECT statement to limit the display to those employees whose salary is greater than 5000?
A.  ORDER BY SALARY > 5000
B.  GROUP BY SALARY > 5000
C.  HAVING SALARY > 5000
D.  WHERE SALARY > 5000
Answer: D

27.  Which four are correct guidelines for naming database tables? (Choose four)
A.  Must begin with either a number or a letter
B.  Must be 1-30 characters long
C.  should not be an Oracle Server reserved word.
D.  must contain only A-Z, a-z, 0-9, _,*, and #
E.  must contain only A-Z, a-z, 0-9, _, $, and #
F.  must begin with a letter
Answer:  BCEF

28.  Which two statements about sequences are true? (Choose two)
A.  You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.
B.  You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
C.  You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence
D.  You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.
E.  If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.
F.  You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Answer: BC

29.  The EMP table contains these columns:
LAST NAME VARCHAR2(25)
SALARY NUMBER (6,2)
DEPARTMENT_ID NUMBER(6)
What is true about this SQL statement?
A.  The SQL statement displays the desired results
B.  The column in the WHERE clause should be changed to display the desired results.
C.  The operator in the WHERE clause should be changed to display the desired results
D.  The WHERE clause should be changed to use an outer join to display the desired results.
Answer:  C

30.  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

31.  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 boks
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

32. 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

33. Which operator can be used with a multiple row subquery?
A **
B LIKE
C. BETWEEN
D. NOT IN
E. Is
F. <>
Answer: D

34. 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

35. Which statement explicitly names a constraint?
A. ALTER TABLE student_grades
ADD FOREIGN KEY (student_id) REFERENCES students (student_id);
B.  ALTER TABLE student_grades
ADD CONSTRAINT NAME=student_id_fk
FOREIGN KEY (student_id) REFERENCES student(student_id);
C.  ALTER TABLE student_grades
ADD  CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id);
D.  ALTER TABLE  student grades
ADD NAMED CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id)
F. ALTER TABLE student grades
ADD NAME student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id)
Answer:  C

36. You need to display the last names of those employees who have the letter “A” as the second character in their names.  Which SQL statement displays the required results?
A. SELECT last_name
FROM EMP
WHERE last_name LIKE’_A%;
B. SELECT last_name
FROM EMP
WHERE last name=’*A%
C. SELECT last_name
FROM EMP
WHERE last name =’* _A%;
D.  SELECT last_name
FROM EMP
WHERE last name LIKE ‘* a%
Answer: A

37. In which case would you use a FULL OUTER JOIN?
A. Both tables have NULL values
B. You want all unmatched data from one table
C. You want all matched data from both tables
D. You want all unmatched data from both tables
E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table.
Answer:  D

38. Which two statements about creating constraints are true? (Choose two)
A. Constraint names must start with SYS_C.
B. All constraints must be defined at the column level
C. Constraints can be created after the table is created
D. Constraints can be created at the same time the table is created
E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view
Answer:  CD

39. Examine the SQL statements that creates ORDERS table:
CREATE TABLE orders
(SER_NO NUMBER UNIQUE,
ORDER_ID NUMBER
ORDER_DATE DATE NOT NULL,
STATUS VARCHAR2(10) CHECK (status IN (‘CREDIT’, ‘CASH’)),
PROD_ID NUMBER REFERENCES PRODUCTS (PRODUCT_ID),
ORD_TOTAL NUMBER,
PRIMARY KEY (order id, order date));
For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)
A. SER_NO
B. ORDER_ID
C. STATUS
D. PROD_ID
E. PRD_TOTAL
F. Composite index on ORDER_ID and ORDER_DATE
Answer: AF

40. You are granted the CREATE VIEW privilege.  What does this allow you to do?
A. create a table view
B. create a view in any scheme
C. create a view in your schema
D. create a sequence view in any schema
E. create a view that is accessible by everyone
F. create a view only if it is based on tables that you created
Answer:  C

41. 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

42. Which three SELECT statements display 2000 in the format “$2,000.00”? (Choose Three).
A.  SELECT TO_CHAR (2000, ‘$#,###.##’)
FROM dual;
B.  SELECT TO_CHAR (2000, ‘$0,000.00’)
FROM dual
C.  SELECT TO_CHAR (2000, ‘$9,999.00’)
FROM dual;
D.  SELECT TO_CHAR (2000, ‘$9,999.99’)
FROM dual;
E.  SELECT TO_CHAR (2000, ‘$2,000.00’)
FROM dual;
F.  SELECT TO_CHAR (2000, ’$N, NNN.NN’)
FROM dual
Answer:  BCD

43. Evaluate the SQL statement
DROP TABLE DEPT;
Which four statements are true of the SQL statement? (Choose four)
A. You cannot roll back this statement
B. All pending transactions are committed
C. All views based on the DEPT table are deleted
D. All indexes based on the DEPT table are dropped
E. All data in the table is deleted, and the table structure is also deleted
F. All data in the table is deleted, but the structure of the table is retained
G. All synonyms based on the DEPT table are deleted
Answer:  ABDE

44. Which statement describes the ROWID data type?
A. binary data up to 4 gigabytes
B. character data up to 4 gigabytes
C. raw binary data of variable length up to 2 gigabytes
D. binary data stored in an external file, up to 4 gigabytes
E. a hexadecimal string representing the unique address of a row in its table
Answer:  E

45. 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

46. 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

47. 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

48. Which two statements complete a transaction? (Choose two)
A. DELETE employees;
B. DESCRIBE employees
C. ROLLBACK TO SAVEPOINT C;
D. GRANT TABLE employees
E. ALTER TABLE employees
SET UNUSED COLUMN sal;
F. SELECT MAX (sal)
FROM employees
WHERE department_id = 20;
Answer:  CE

49. Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHAR(30)
SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only if the minimum salary is less than 5000 and maximum salary is more than 15000?
A. SELECT  dept_id, MIN (salary), MAX (salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX (salary) > 15000;
B. SELECT dept_id, MIN (salary), MAX (salary)
FROM employees
WHERE MIN (salary) < 5000 AND MAX (salary)  15000
GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
HAVING MIN (salary) < 5000 AND MAX (salary)
D. SELECT dept_id MIN (salary), MAX (salary)
FROM employees
GROUP BY dept_id
HAVING MIN(salary) < 5000 AND MAX (salary) < 15000
E. SELECT dept_id,MIN (salary), MAX (salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN (salary) < 5000 AND MAX (salary) > 15000;
Answer: D

50. The DBA issues this SQL command:
CREATE USER scott
INDENTIFIED by tiger;
What privileges does the user Scott have at this point?
A. no privileges
B. only the SELECT  privilege
C. only the CONNECT privilege
D. all the privileges of a default user
Answer: A

51. The EMPLOYEES table has these columns
LAST_NAME VARCHAR2 (35)
SALARY NUMBER (8,2)
HIRE_DATE DATE
Management wants to add a default value to the SALARY column.  You plan to alter the table by using this SQL statement:

ALTER TABLE EMPLOYEES
MODIFY (SALARY DEFAULT 5000);
Which is true about your ALTER statement?
A. Column definitions cannot be altered to add DEFAULT values
B. A change to the DEFAULT value affects only subsequent insertions to the table
C. Column definitions cannot be altered to add DEFAULT values for columns with a NUMBER data type.
D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
Answer:  B

52. Which substitution variable would you use if you want to reuse the variable value without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D

53. 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

54. What is true about the WITH GRANT OPTION clause?
A. It allows a grantee DBA privileges
B. It is required syntax for object privileges
C. It allows privileges on specified columns of tables
D. It is used to grant an object privilege on a foreign key column
E. It allows the grantee to grant object privileges to other users and roles
Answer:  E

55. 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’
E. 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

56. Which constraint can be defined only at the column level?
A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY
E. FOREIGN KEY
Answer:  B

57. In which scenario would Top N analysis be the best solution?
A. You want to identify the most senior employee in the company
B. You want to find the manager supervising the largest number of employees
C. You want to identify the person who makes the highest salary of all employees
D. You want to rank the top three sales representatives who have sold the maximum number of products
Answer:  D

58. Examine the structure of the EMPLOYEES  and NEW EMPOYEES tables:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE

NEW EMPLYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)

Which MERGE statement is valid?

A. MERGE INTO new_employees e
USING employees e
ON (e.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
e.name = e.first_name //’,’// e.last_name
WHEN NOT MATCHED THEN
INSERT VALUES (e.employee_id, e.first_name//’,
‘//e.last_name);
B. MERGE new_employee c
USING  employees e
ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET
c.name = e first_name//’,’// e.last_name
WHEN NOT MATCHED THEN
INSERT VALUES (e.employee_id, e.first_name//’.
‘//e.last_name);
C. MERGE INTO new employees c
USING  employees e
ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN
UPDATE SET
e.name = e.fist //’,’// e.last_name
WHEN NOT MATCHES THEN
INSERT VALUES (e.employee_id, e.first _name//’,
‘//e.last_name);
D. MERGE new_employees c
FROM employees c
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
e.name = e.first_name //’,’// e.last_name
WHEN NOT MATCHED THEN
INSERT INTO new_employees VALUES (e.employee_id, e.first_name//”.
‘//e.last_name);
Answer:  A

59. Which three are true regarding the use of outer joins? (Choose three.)
A. You cannot use IN operator in a condition that involves an outerjoin
B. You use (+) on both sides of the WHERE condition to perform an outerjoin
C. You use (*) on both sides of the WHERE condition to perform an outerjoin.
D. You use an outerjoin to see only the rows that do not meet the join condition
E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin
F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator
Answer:  DEF

60. 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

61. Which statement creates a new user?
A. CREATE USER susan
B. CREATE OR REPLACE USER susan
C. CREATE NEW USER susan
DEFAULT,
D. CREATE USER susan
INDENTIFIED BY blue
E. CREATE NEW USER susan
IDENTIFIED BY blue
F. CREATE OR REPLACE USER susan
IDENTIFIED BY blue;
Answer: D

62. The STUDENT_GRADES table has these columns
STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4,3)
The registrar has requested a report listing the students’ grade point averages (GPA), stored from highest grade point average to lowest within each semester, starting from the earliest date.  Which statement accomplishes this?
A. SELECT student)_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end DESC, gpa DESC;
B. SELECT  student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end ASC, gpa ASC;
C. SELECT  student _id, semester_end, gpa
FROM  student_grades
ORDER BY semester_end, gpa DESC;
D. SELECT student_id, semester_end, gpa
FROM  student_grades
ORDER BY gpa DESC, semester_end DESC;
E. SELECT student-id, semester_end, gpa
FROM student_grades
ORDER BY gpa DESC, semester_end ASC,
Answer:  C

63. You need to change the definition of an existing table.  The COMMERCIALS table needs its DESCRIPTION  column changed to hold varying length characters up to 2000 bytes.  The column can currently hold 1000 bytes per value.  The table contains 20000 rows.
Which statement is valid?
A. ALTER TABLE commercial
MODIFY (description CHAR2(2000))
B. ALTER TABLE commercials
CHANGE (description CHAR2(2000))
C. ALTER TABLE commercials
CHANGE (description varchar2(2000))
D. ALTER TABLE commercials
MODIFY (description VARCHAR2(2000))
E. You cannot increase the size of a column if the able has rows.
Answer:  D

64. What does the TRUNCATE statement do?
A. removes the table
B. removes all rows from a table
C. shortens the tale to 10 rows
D. removes all columns from a table
E. removes foreign keys from a table
Answer;  B

65. 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

66. Which is an /SQL * Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer; D

67. Which SELECT statement should you use to extract the year form the system date and display it in the format “1998”?
A. SELECT TO_CHAR(SYSDATE, ‘yyyy’)
FROM dual
B. SELECT TO_DATE(SYSDATE,’yyyy’)
FROM dual
C. SELECT DECODE (SUBSTR (SYSDATE, 8), ‘YYYY’)
FROM dual
D. SELECT DECODE  (SUBSTR (SYSATE, 8),’year’)
FROM dual
E. SELECT TO_CHAR (SUBSTR(SYSDATE, 8,2),’yyyy’)
FROM dual
Answer:  A

68. 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

69. A subquery can be used to _________.
A. create groups of data
B. sort data in a specific order
C. convert data to a different format
D. retrieve data based on an unknown condition
Answer:  D



70. Which clause should you use to exclude group results?
A. WHERE
B. HAVING
C. RESTRICT
D. GROUP BY
E. ORDER BY
Answer:  B

71. Scott issues the SQL statements:
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

72. 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

73. 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

74. What is true about joining tables through an equation?
A. you can join a maximum of two tables through an equation
B. you can join a maximum, of two columns through an equation
C. you specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.
E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.
Answer: E

75. You need to calculate the total of all salaries in the accounting department.  Which group function should you use?
A. MAX
B. MIN
C. SUM
D. COUNT
E. TOTAL
F. LARGEST
Answer: C

76. 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

77. The EMP table has these columns:
ENAME VARCHAR2(35)
SALARY NUMBER (8,2)
HIRE_DATE DATE
Management wants a list of names of employees who have been with the company for more than five yeas.  Which SQL statement displays the required results?
A. SELECT ENAME
FROM EMP
WHERE SYSDATE-HIRE_DATE>5
B. SELECT ENAME
FROM EMP
WHERE HIRE_DATE-SYSDATE > 5
C. SELECT ENAME
FROM EMP
WHERE (SYSDATE-_DATE)/365 > 5
D. SELECT ENAME
FROM EMP
WHERE (SYSDATE-HIRE_DATE)* 365 > 5
Answer:  C

78. You would like to display the system date in the format *Monday, 01 June, 2001*
Which SELECT statement  should you use?
A. SELECT TO_DATE (SYSDATE, ‘FMDAY, DD Month, YYYY’)
FROM dual
B. SELECT TO_CHAR(SYSDATE, ‘FMDD, DY Month ‘YYY’)
FROM dual
C. SELECT TO_CHAR(SYSDATE, ‘FMDay, DD Month YYYY’)
FROM dual
D. SELECT TO_CHAR(SYSDATE, ‘FMDAY, DDD Month, YYYY’)
FROM dual
E. SELECT TO_DATES(SYSDATE,’FMDY, DDD Month, YYYY’)
FROM dual
Answer:  C





79. The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER (4) NOT NULL
CUSTOMER_NAME VARCHAR2(100)
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS            VARCHAR2(50)
STATE_ADDRESS            VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTAL_CODE VARCHAR2(12)
CUSTOEMR_PHONE VARCHAR2(20)
Which statement finds the rows in the CUSTOMERS table that do not have a postal code
A. SELECT customer_id, customer_name
FROM customers
WHERE postal_code CONTAINS NULL
B. SELECT  customer_id, customer name
FROM  customers
WHERE posta_code=’______________’
C. SELECT customer_id, customer_name
FROM customers
WHERE postal_code IS NULL
D. SELECT customer_id, customer_name
FROM customers
WHERE postal code IS NVL
E. SELECT customer_id, customer_name
FROM customers
WHERE postal_code=NULL
Answer:  C

80. Evaluate this SQL statement
SELECT e.employee_id, (15*e.salary) + .(5* e.commission_pct)
+ (s.sales amount* (.35* e.bonus)) AS CALC_VALUE
FROM employees e,sales s
WHERE e.employee_id = s.emp_id
What will happen if you remove al the parentheses from the calculation?
A. The value displayed in the CALC_VALUE column will be lower
B. The value displayed in the CALC_VALUE column will be higher
C. There will be no difference in the value displayed in the CALC_VALUE column
D. An error will be reported.
Answer:  C

81. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator”=” What happens when the main query is executed?
A. the main query executes with the first value returned by the subquery
B. the main query executes with the last value returned by the subquery
C. the main query executes with all the values returned by the subquery
D. the main query fails because the multiple-row subquery cannot be used with the comparison operator.
E. You cannot define multiple-row subquery in the WHERE clause of a SQL query
Answer:  D

82. which three statements correctly describe the functions and use of constraints? (Choose three)
A. constraints provide data independence
B. constraint make complex queries easy
C. constraints enforce rules at the view level
D. constraints enforce rules at the table level
E. constraints prevent the deletion of a table if there are dependencies
F. constraints prevent the deletion of an index if there are dependencies
Answer:  ACD


83. Which two are character manipulation functions? (Choose two)
A. TRIM
B. REPLACE
C. TRUNC
D. TO_DATE
E. MOD
F. CASE
Answer:  AB

84. You need to create a view EMP_VU.  The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20.Which SQL statement would you use to crete the view EMP_VU?
A. CREATE VIEW emp_vu AS
SELECT  employees
WHERE department_id 1N (10,20)
B. CREATE VIEW emp_vu AS
` SELECT *
FROM employees
WHERE department_id IN (10,20)
WITH READ ONLY
C. CREATE VIEW emp_vu AS
SELECT *
FROM employees
WHERE department_id IN (10,20)
WITH CHECK OPTION
D. CREATE FORCE VIEW emp_vu AS
SELECT  *
FROM employees
WHERE department_id IN (10, 20)
NO UPDATE
Answer:  C

85.  Evaluate these two SQL statemens
SELECT last_name, salary, hire_date
FROM EMPLOYEES
ORDER BY salary DESC
SELECT last_name, salary, hire_date
FROM EMPLOYEES
ORDER BY 2 DESC
What is true about them?
A. the two statements produce identical results
B. the second statement returns a syntax error
C. there is no need to specify DESC because the results are sorted in descending order by default
D. the two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statements
Answer: A

86. 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

87. Which two statements accurately describe a role? (Choose two)
A. a role can be given to a maximum of 1000 users
B. a user can have access to a maximum of 10 roles
C. a role can have a maximum of 100 privileges contained in it.
D. Privileges are given to a role by using the CREATE ROLE statement.
E. A role is a named group of related privileges that can be granted to the user
F. A user can have access to several roles, and several users can be assigned the same role.
Answer:  EF

89. You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table
Some of the employees may not have a phone number available.
Which data manipulation operation do you perform?
A. MERGE
B. INSERT
C. UPDATE
D. ADD
E. ENTER
F. You cannot enter the phone number for the existing employee records
Answer: C

90. Which two statements about subqueries are true? (Choose two)
A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is refered to in its subquery, unless table B is included in the main query’s FROM clause.
C. A SQL query statement cannot display data from table B that is refered to in its subquery, without including table B in its own FROM clause.
D. A single row subqery can retrieve data from more than one table
E. A single row subqery cannot be used in a condition where the LIKE operator is used for comparison.
F. A multiple-row subquery cannot be used in a condition where the LIKE operation is used for comparison.
Answer: BD




91. Examine the structure of the STUDENTS table
STUDENT_ID NUMBER NOT NULL., Primary Key
STUDENT_NAME VARCHAR2(30)
COURSE_ID VARCHAR2(10) NOT NULL
MARKS NUMBER
START_DATE DATE
FINISH_DATE DATE
You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?
A. SELECT student_id, marks, ROWNUM “Rank”
FROM student
WHERE ROWNUM <= 10
AND finish_data BETWEEN ’01-JAN-99’ AND ’31-DEC-99’
AND course_id=’INT_SQL’
ORDER BY marks DESC;
B. SELECT student_id, marks, ROWID “Rank”
FROM students
WHERE ROWID <= 10
AND finish_data BETWEEN ’01-JAN-99’ AND ’31-DEC-99’
AND course_id=’INT_SQL’
ORDER BY marks;
C. SELECT student_id, marks ROWNUM “Rank”
FROM (SELECT student_id, marks)
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN ’01-JAN99’ AND
’31-DEC-99’
AND COURSE_ID = ‘INT_SQL’
ORDER BY marks desc:
D. SELECT student_id, marks ROWNUM” Rank:
FROM (SELECT student_id.marks
FROM students
ORDER BY marks DESC )
WHERE ROWNUM <= 10
AND finish_date BETWEEN ’01-JAN99’ AND ’31-DEC-99’
AND course_id = ‘INT_SQL;’
Answer: D

92. The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTE_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
THE CUSTOMER_ID column is the primary key for the table  which two statements find the number of customer? (Choose two.)

A. SELECT TOTAL (*)
FROM customers;
B. SELECT COUNT (*)
FROM customers;
C. SELECT TOTAL (customer_id)
FROM customer;
D. SELECT COUNT(costomer_id)
FROM customer;
E. SELECT COUNT(customers)
FROM customers;
F. SELECT TOTAL (customer_name)
FROM customers;
Anser : BD

93. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
A. immediately after the SELECT clause
B. before the WHERE clause
C. before the FROM clause
D. after the ORDER BY clause
E. after the WHERE clause
Answer : E

94. Which two are true about aggregate functions? (Choose two)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.
Answer BD

95. For which two constrains does the Oracle Server implicitly create a unique index? (Choose two)

A. NOT NULL
B. PRIMARY KEY
C. FOREIGN KEY
D. CHECK
E. UNIQUE
Answer: BE

96. 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

97. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
You issue these statements:
CREATE table new_emp (employee_id NUMBER, name VARCHAR2 (30));
INSERT INTO new_emp SELECT employee_id, last_name from employees;
Savepoint s2;
Delete from new_emp;
Rolback to s2;
Delete from new_emp where employee_id = 180;
UPDATE new_emp set name = ‘James’;
Rolback to s2;
UPDATE new_emp set name = ‘James’ WHERE employee_id = 180;
Rollback;
At the end of this transaction, what is true?

A. You have no rows in the table.
B. You have an employee with the name of James
C. You cannot roll back to the same savepoint more than once.
D. Your last update fails to update any rows because employee ID 180 was already deleted.

Answer : A
 
98. Which / SQL* Plus feature can be used to replace values in the where clause?
A. Substitution variables
B. replacement variables
C. prompt variables
D. instead-of variables
E. This feature cannot be implemented through / SQL*Plus
Answer : A

99.Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;
What will be displayed?
A. 0
B. 1
C. 0.00
D. an error statement
Answer : A

100. Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME   VARCHAR2(25)
DEPARTMENT_ID NUMBER
SALARY NUMBER

101.What is the correct syntax for an inline view?
A SELECT  a last_name, a salary, a department_id,
b.maxsal
FROM employees a,
(SELECT department_id, max(salary)maxsal
FROM employees
GROUP BY department_id)b
WHERE a department_id = department-id
AND a_salary<b.maxsal;
B. SELECT a. last name, a salary, a. department_id
FROM employees a
WHERE  a. department_id IN
(SELECT department_id
FROM employees b
GROUP BY department_id having salary =
SELECT max(salary) from employees
C. SELECT  a last_name,  a salary, a.department_id
FROM employees a
WHERE a salary =
SELECT max(salary)
FROM employees b
WHERE a department_id = department_id);
D. SELECT  a last_name, a salary, a.department_id
FROM employees a
WHERE (a department_id, a salary) IN
(SELECT department_id, a salary) IN
(SELECT department_id max(salary)
FROM employees b
GROUP BY department_id
ORDER BY department_id);
Answer : A

101. Examine the structure of the EMPLOYEES table:
 
EMPLOYEE_ID NUMBER NOT NULL
EMP_ID VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT ‘SA_REP’
SAL NUMBER
COMM_PCT NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
you need to update the records of emloyees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below:
JOB_ID Default value specified for this column definition
SAL maximum salary earned for the_job ID SA_REP
COMM_PCT Default value is specified for the column, the value should be NULL
DEPARTMENT_ID: Supplied by the user during run time through substitution variable
which UPDATE statement meets the requirements?
A. UPDATE employees
SET job_id=DEFAULT
AND Sal=(SELECT MAX(sal)
FROM emoployees
WHERE job_id='SA_REP'
AND comm_pet=DEFALUT
AND department_id =&did
WHERE employee_id IN (103, 115),
B.  UPDATE employees
SET job_id = DEFAULT
AND Sal = MAX(sal)
AND comm_pct = DEFAULT OR NULL
AND department _id = & did
WHERE employee_id IN (103,115)
AND  ob_id = 'SA_REP'
C.  UPDATE employees
SET job_id = DEFAULT
Sal = (SELECT MAX (sal)
FROM employees
WHERE job_id = 'SA_REP')
comm_pct = DEFAULT,
department _id = &did
WHERE employee_id IN (103,115)
D.  UPDATE emplouees
SET job_id = DEFAULT
sal = MAX (sal)
comm_pct = DEFAULT
department_id = &did
WHERE employee_id IN (103,115)
AND job_id = 'SA_REP'
E.  UPDATE employees
SET job_id = DEFAULT
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = 'SA_REP')
comm_pct = DEFAULT OR NULL,
department_id = &did
WHEREemployee_id IN (103,115)
Answer: C

102. Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
A.  USER_TAB_PRIVS_MADE
B.  USER_TAB_PRIVS_RECD
C.  USER_COL_PRIVS_MADE
D.  USER_COL_PRIVS_RECD
Answer:  D

103.  Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)
A.  TIMESTAMP
B.  INTERVAL MONTH TO DAY
C.  INTERVAL DAY TO SECOND
D.  INTERVAL YEAR TO MONTH
E.  TIMESTAMP WITH DATABASE TIMEZONE
Answer:  ACD

104. Examine the structure of the EMPLOYEES table:
column name data type remarks
EMPLOYEE_ID NUMBER NOT NULL, primary key
LAST_NAME VARCHAR2(30)
FIRST_NAME VARCHAR2(30)
JOB_ID NUMBER
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER
You need to create an index called NAME IDX on the first name and last name fields of the EMPLOYEES table.  Which SQL statement would you use to perform this task?
A. CREATE INDEX NAME_IDX (first_name, last_name)
B. CREATE INDEX NAME_IDX (first_name AND last_name)
C.   CREATE INDEX NAME_IDX
ON (first_name, last_name)
D. CREATE INDEX NAME_IDX
ON employees (first_name AND  last_name)
E. CREATE INDEX NAME_IDX
ON employees (first_name, last_name)
 F. CREATE INDEX NAME_IDX
FOR employees (first_name, last_name)
Answer:  E

105. 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

106 Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMP e.DEPARTMENT d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A.  selection, projection, join
B.  difference, projection, join
C.  intersection, projection, join
D.  difference, projection, product
Answer:  A

107. You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column.  The table is currently empty.  Which statement accomplishes this task?
A.  ALTER TABLE students
ADD PRIMARY KEY student_id;
B. ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
C.ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY stuent_id;
D.  ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E.  ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id)
Answer: D

108. Which syntax turns an existing constraint on?
A.  ALTER TABLE table_name
ENABLE constrain_name
B.  ALTER TABLE table_name
STATUS = ENABLE CONSTRAINT constrain_name
C.  ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name
D.  ALTER TABLE table_name
STATUS ENABLE CONSTRAINT constraint_name
E.  ALTER TABLE table_name
TURN ON CONSTRAINT costrant_name
F.  ALTER TABLE table_name
TURN ON CONSTRAINT constraint_name
Answer:  C

109. Which two statements about views are true? (Choose two)
A.  A view can be created as read only
B.  A view can be created as a join on two or more tables.
C.  A view cannot have an ORDER BY clause in the SELECT statement.
D.  A view cannot be created with a GROUP BY clause in the SELECT statement.
E.  A view must have aliases defined for the column names in the SELECT statement.
Answer: AB

110. The database adminsrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL  schema, because many users frequentlyuse this table.
As a user of the database, you created a table called HR in your chema. What happens when you execute this query?
SELECT *
FROM HR;
A.  you obtain the results retrieved from the public synonym HR created by the database administrator
B.  you obtain the results retrieved form the HR table that belongs to your schema.
C.  you get an error message because you cannot retrieve from a table that has te same ame as a public synonym
D.  you obtain the results retrieved from both the public synonym HR and the HR table that belongs to your shema, as a Cartesian product.
E.  You obtain the results retrieved form both the public synonym HR and the HR table that belongs to your shema, as a FULL JOIN.
Answer:  B

111. You need to give the MANAGER role the ability to select from insert into and modify existing rows in the STUDENT_GRADES table.  Anyone given this MANAGER role should be able to pass those privileges on to others.  Which statement accomplishes this.
A.  GRANT select, insert, update
ON student_grades
TO manager;
B.  GRANT  select, insert, update
ON student_grades
TO ROLE  manager
C.  GRANT select, insert, modify
ON student_grades
TO ROLE manager
C.  GRANT select, insert, modity
ON  student_grades
TO manager
WITH GRANT OPTION;
D.  GRANT select, insert, update
ON student_grades
TO manager
WITH GRANT OPTION
E.  GRANT  select, insert, update
ON student_grades
TO ROLE manager
WITH GRANT OPTION;
F.  GRANT select, insert, modify
ON  student_grades
TO ROLE manager
WITH GRANT OPTION
Answer:  D


112. Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Get z 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000

DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts
40 Administration
You want to retrieve all employees whether or not they have matching departments in the departments table.  Which query would you use?
A.  SELECT last_name, department_name
FROM employees, departments(+);
B.  SELECT last_name, department_name
FROM employees JOIN departments(+);
C.  SELECT last_name, department_name
FROM employees(+) e JOIN departments d
ON  (e.department_id = d.departement_id);
D.  SELECT last_name, department_name
FROM emplouees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
E.  SELECT  last_name, department_name
FROM  employees (+), departments
ON (e.departments_id = department_id);
F.  SELECT last_name, departement_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d. department_id);
Answer:  F

Posted By: http://educationexampoint.blogspot.in