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 placed 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 cust_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');
Ans: B
Explanation:
This query will return the order ID, customer ID, and order total for the orders that are placed on the same day that Martin places his orders.
Incorrect Answers:
A: This query returns only Martin’s orders for July 18, 2000 and July 21, 2002, not orders of others that were placed on the same day that Martin placed his orders.
C: This query uses incorrect sub-query to extract dates when Martin placed his orders.
D: This query will return only Martin’s orders.
Q. Evaluate the SQL statement:
1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (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 line 1.
B. The statement produces an error at line 3.
C. The statement produces an error at line 6.
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.
Ans: E
Explanation:
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. This query is example of an inline view which is the sub-query in the FROM clause of the main query. The sub-query can be a SELECT statement that utilizes joins, the GROUP BY clause, or the ORDER BY clause.
Incorrect Answers:
A: The statement does not produce an error at line 1.
B: The statement does not produce an error at line 3.
C: The statement does not produce an error at line 6.
D: The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all EMPLOYEES, NOT DEPARTMENTS, who earn less than the maximum salary in their department.
Q. 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
Ans: C, E
Explanation:
TO_CHAR(x) function is used to convert the value x to a character or converts a date to a character string using formatting conventions.
Incorrect Answers:
A: This function cannot convert the number to the string representing number spelled out.
B: TO_CHAR() function cannot convert the character value to a number. TO_NUMBER() function does this.
D: This function is not able to convert the string representing number spelled out to the number itself.
F: TO_CHAR() function cannot convert a character expression to a date. TO_DATE() function does this.
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 the 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.
Ans: B
Explanation:
It will be error generated because there are 2 child records in the EMPLOYEES table with department number you try to delete from the DEPARTMENTS table.
Incorrect Answers:
A: The row with department ID 40 will not be deleted because of the child records in the EMPLOYEES table.
C: Neither the row with department ID 40 will not be deleted not child records in the EMPLOYEES table will be deleted.
D: It will be error when you try to execute the DELETE statement, no rows will be deleted in the EMPLOYEES or the DEPARTMENTS tables.
E: It will be error when you try to execute the DELETE statement, no rows will be deleted in the EMPLOYEES or the DEPARTMENTS tables.
F: The statement fails because of constraint violation not because there are no columns specifies in the DELETE clause of the DELETE statement.
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 synonym for the EMP_DEPT_LOC_VU by using the command
CREATE LOCAL SYNONYMDEL_VU FOR mary.emp dept_LOC_VU;
Then he can prefix the columns with this synonym.
D. Scott can create a synomym for the EMP_DEPT_LOC_VU by using the command
CREATE LOCAL SYNONYMDEL_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.
Ans: B
Explanation:
Correct syntax to create a local synonym is CREATE SYNONYM synonym_name. With PUBLIC keyword you can create public synonym.
Incorrect Answers:
A: There is no PRIVATE keyword for the CREATE SYNONYM command.
C: There is no LOCAL keyword for the CREATE SYNONYM command.
D: This SQL statement shows incorrect syntax to create a synonym.
E: Synonyms can be created not only for tables but for other objects also.
F: Scott can create synonym for Mary’s view because she granted SELECT privilege to Scott on this view.
Q. Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?
A. CREATE TABLE EMP (
empno NUMBER(4),
ename VARCNAR2(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 VARCNAR2(35),
deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C. CREATE TABLE EMP (
empno NUMBER(4),
ename VARCNAR2(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 VARCNAR2(35),
deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno_fk REFERENCES dept (deptno));
Ans: B
Explanation:
This statement provides correct syntax to define the FOREIGN KEY constraint on the DEPTNO column of the EMP table.
Incorrect Answers:
A: There is incorrect syntax, because list of columns and column for the constraint need to be surrounded with the brackets.
C: It is incorrect to use FOREIGN KEY keywords to define constraint on the table. It can be used to add integrity constraint to existing table.
D: It is incorrect to use FOREIGN KEY keywords to define constraint on the table. It can be used to add integrity constraint to existing table.
Q. Evaluate the set of SQL statements:
CREATE TABLE dept (deptbi BYNVER (2) dname VARCHAR2(14), Ioc VARCNAR2(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.
Ans: A
Explanation:
The structure of the DEPT table will be displayed because the CREATE TABLE statement is DDL operation and it cannot be rolled back because implicit commit occurs on the database when a user exits iSQL*Plus or issues a data-definition language (DDL) command such as a create table statement, user to create a database object, or an alter table statement, used to alter a database object.
Incorrect Answers:
B: The ROLLBACK statement has nothing to do with the storage space of the DEPT table.
C: The DESCRIBE DEPT statement does not produce the error. It displays the structure of the DEPT table.
D: The COMMIT statement does not need to be introduced because implicit commit occurs on the database after creation of the table.
Q. Which are DML statements? (Choose all that apply)
A. COMMIT
B. MERGE
C. UPDATE
D. DELETE
E. CREATE
F. DROP
Ans: B, C, D
Explanation:
MERGE, UPDATE and DELETE commands are data manipulation language (DML) statements.
Incorrect Answers:
E: CREATE is a data definition language (DDL) command.
F: DROP is a data definition language (DDL) command.
DDL (Data definition language) statements: CREATE, ALTER, DROP.
DML (Data manipulation language) statements: SELECT, INSERT, DELETE, UPDATE, MERGE, COMMIT, ROLLBACK, ...
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
NUMBRT
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_id=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);
Ans: C
Explanation:
This query shows correct JOIN ON clause syntax and provides equivalent to the above SQL statement.
Incorrect Answers:
A: This statement will show data only for the EMPLOYEES table with records that have department ID from DEPARTMENTS table, not join result of two tables.
B: NATURAL join selects rows from the tables that have equal values in all matched columns (same column names). If the columns having the same names have different datatypes, an error is returned.
D: There is incorrect usage of JOIN clause with USING keyword.
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;
Ans: B
Explanation:
This SQL statement provides correct syntax to generate the alias Annual Salary for the calculated column SALARY*12.
Incorrect Answers:
A: Alias can be surrounded with double quotation marks, not with single. Oracle error will be generated in this case.
C: Alias needs to be surrounded with double quotation marks, it cannot just follow by the AS keyword.
D: You cannot use any function as alias, so this SQL statement will fail.
Q. 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.
Ans: D
Explanation:
Index will be useful if the indexed column contains a wide range of values. Especially B-tree indexes will work better for tables with a wide range of values. But for tables just with some distinct values bitmap indexes will be more helpful.
Incorrect Answers:
A: Oracle automatically creates index for NOT NULL columns, so you don’t need create an index yourself.
B: There are only table names in the FROM clause, not columns.
C: Index may be will not work if the indexed columns are part of an expression. You need to avoid expressions if you want to use index.
Q. Which two are attributes of iSQL*Plus? (Choose two).
A. iSQL*Plus commands cannot be abbreviated.
B. iSQL*Plus commands are accessed from a browser.
C. iSQL*Plus commands are used to manipulate data in tables.
D. iSQL*Plus command manipulate table definitions in the database.
E. iSQL*Plus is the Oracle proprietary interface for executing SQL statements.
Ans: C, D
Explanation:
iSQL*Plus commands can be used to manipulate data in tables and iSQL*Plus commands manipulate table definitions in the database.
Incorrect Answers:
A: iSQL*Plus commands can be abbreviated. Like command DESCRIBE can be abbreviated as DESC, or SELECT as SELE.
B: iSQL*Plus commands are not accessed from a browser.
E: iSQL*Plus is not only the Oracle proprietary interface for executing SQL statements.
Q. 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.
Ans: B, C, E
Explanation:
A single row sub-query can retrieve only one row but many columns. A multiple row subquery can retrieve one row or multiple rows and multiple columns. A single row sub-query can be used with IN operator.
Incorrect Answers:
A: A single row sub-query can retrieve only one row, but many columns.
D: A multiple row sub-query can be compared by using the “>” operator unless it’s “> ANY” or “> ALL” type of queries.
F: A multiple row sub-query cannot use the “=” operator.
Q. 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
Ans: B, D
Explanation:
You should use roles to grant a group of relative privileges to a user. You grant the appropriate privileges to the role and after that grant this role to specific users. So it allows you to simplify the process of granting and revoking privileges.
Incorrect Answers:
A: Roles have nothing to do with simplifying the process of creating new users.
C: Roles are very useful when the number of people using the database is very high, but you can also successfully use roles to reduce administration task in databases with small amount of users.
E: Roles have nothing to do with user who is constantly traveling.
Q. 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
Ans: D
Explanation:
You need to use the WHERE clause to limit the display to those employees whose salary is greater then 5000.
Incorrect Answers:
A: The ORDER BY clause will just sort data, but it will not limit them.
B: You cannot use the GROUP BY clause with conditions. Oracle error will be generated.
C: The HAVING clause may be used only in conjunction with the GROUP BY clause.
Q. 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
Ans: B, C, E, F
Explanation:
Oracle database object must begin with a letter and can usually be between 1 and 30 characters long, except for databases (which have a maximum of eight characters) and database links (with a maximum of 128 characters). Name cannot be an Oracle Server reserved word. Name must contain only A-Z, a-z, 0-9, _, $, and #.
Incorrect Answers:
A: Database tables may not begin with number.
D: It cannot contain symbols “+” or “*”.
Q. 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.
Ans: B, C
Explanation:
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. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
Incorrect Answers:
A: You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
D: 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.
E: This statement is not correct. There is no limitation like that in Oracle.
F: You use CYCLE clause, not REUSE, when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Q. The EMP table contains these columns:
LAST NAME
VARCHAR2(25)
SALARY
NUMBER(6,2)
DEPARTMENT_ID
NUMBER(6)
You need to display the employees who have not been assigned to any department.
You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP
WHERE DEPARTMENT_ID = NULL;
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.
Ans: C
Explanation:
The operator in the WHERE clause should be changed to display the desired results. There are times when you want to substitute a value in place of NULL. Oracle provides this functionality with a special function, called NVL(). You cannot use operation equal with NULL, but you can achieve desired results using NVL() function after the WHERE clause.
Incorrect Answers:
A: The SQL statement will generate an error because you cannot use operation equal with NULL.
B: The column in the WHERE clause should not be changed to display the desired results.
D: Since there is only one table used in this query you don’t need to use outer join to display the desired results.
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 is 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.
Ans: C
Explanation:
The statement returns an error at the WHERE clause because group function AVG() cannot be used in the WHERE clause. Group functions can be used in SELECT clause and GROUP BY clause. They allow you to perform data operations on several values in a column of data as though the column were one collective group of data.
Incorrect Answers:
A: The statement does not execute successfully because an error will be generated.
B: The statement returns an error at the WHERE, not at the SELECT clause.
D: The statement returns an error at the WHERE, not at the ORDER BY clause.
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_date < '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 BETWEEN 500 AND 900 AND purchase_date < '21-JAN-2001' ORDER BY purchase_date DESC;
Ans: D
Explanation:
This statement provides required results.
Incorrect Answers:
A: This query will show books with price in range $500 and $900, not less then $500 or greater than $900.
B: This query will show books with prices exactly $500 or $900, not less then $500 or greater than $900.
C: This order will not show correct rows because of incorrect syntax in the WHERE clause..