General Oracle SQL FAQs

Q1. 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: CEF

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

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

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

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

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

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

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

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

Q10.  Which are DML statements? (Choose all that apply)

A.  COMMIT...

B.  MERGE...

C.  UPDATE...

D.  DELETE...

E.  CREATE...

F.  DROP...

Answer: ABCD

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

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

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

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

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

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

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

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

Answer: BC

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

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

Q21.Which operator can be used with a multiple row subquery?

A  **

B  LIKE

C. BETWEEN

D. NOT IN

E. Is

F.  <>

Answer: D

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

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

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

Q25. 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:  F

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

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

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

Q29.  Which three SELECT statements display value is displayed in the calculated

 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

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

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

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

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

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

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

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

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

Q38.  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’

Answer:  D

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

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

Q41. Which three are true explicitly names a constraint

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

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

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

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

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

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

Q47.  Which clause should you use to exclude group results?

A. WHERE

B. HAVING

C. RESTRICT

D. GROUP BY

E. ORDER BY

Answer:  B

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

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

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

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