Important SQL Questions for Exam Preparation

1. Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?

A. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\';

B. SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE;

C. SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';

D. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';

2. The number of records in a table emp is given by statement

A. Select total(*) from emp;
B. Select count(*) from emp;
C. Select sum(*) from emp;
D. Select total from emp;

3. Write a query to Display the Name and studentid of all Students of branch CSE and ECE
in alphabetical order by name.

4. Can you use the SQL JOIN and SQL HAVING clauses in one SQL statement?

A .Yes.

B . No.

C . It depends.

D . Don't know

5. # Which four are valid Oracle constraint types?

A. CASCADE
B. UNIQUE
C. NONUNIQUE
D. CHECK
E. PRIMARY KEY
F. CONSTANT
G. NOT NULL

6. Which of the following is a SQL aggregate function?

A . CAST
B . LEFT
C . MIN
D . RIGHT

7. Which of the following SQL statements can calculate and return the absolute value of -33?

A. SELECT ABS("-33") Absolute FROM DUAL;
B. SELECT ABS('-33') "Absolute" FROM DUAL;
C. SELECT ABS(-33) "Absolute" FROM DUAL;
D. SELECT ABS(-33), Absolute FROM DUAL;

8. # 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

9. What remove all rows from a table without logging the individual row deletions.

A. Delete
B. Drop
C. Truncate
D .Remove

10. Select all record(i.e. First name, salary, job id) from employees table where employee name in
‘Blake’, ‘Scott’, ‘King’ and ‘Ford’.

Condition here is -- names to be taken irrelevant of case .. Suppose for 'Blake' .. You also should select names like -- Blake, Blake, Blake, Blake ... Similarly for other names too .. Please Try .. Will need just few seconds of your .. Might be helpful to many others ...

11. What command is used to encrypt a PL/SQL application?

A. DB_ENCRYPT
B. DBMS_ENCRYPT
C. DBMS_WRAP
D .WRAP

12. Top N analysis requires _____ and _____. (Choose two.)

A. The use of rowed
B. A GROUP BY clause
C. An ORDER BY clause
D. Only an in line view
E. An in line view and an outer query

13. Which is the memory area that is created when a dedicated server process is started, and
contains data and control information for that server process?

A. SGA
B. Streams Pool
C. Shared Pool
D. PGA

14. Write a query to delete only duplicate rows from a table.

15. Where would you look for errors from the database engine?

A .Recovery log
B. Alert log
C. Error log
D. Control file

16. Which character function should be used to return a specified portion of a character string?

A CONCAT
B LENGTH
C INITCAP
D SUBSTR

17. List the names of the employees who are getting the highest salary in their department.

18. There is a column c1 in the table t to which a primary key pk is to be added. What will be the correct syntax?


A Alter table t add primary key(c1);

B Alter table t add constraint pk primary key(c1);

C Alter table t add (constraint pk primary key(c1));

D Alter table t add pk constraint primary key(c1);

19. Which tablespace is used by oracle for Sorting Operations

A Temporary Tablespace
B Undo Tablespace
C System Tablespace
D. All

20. Write a query to display department details where at least two emps are working.

21. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT 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

22. What is database migration and database up gradation ?

23. What is necessary for your query on an existing view to execute successfully?

A The underlying tables must have data.
B You need SELECT privileges on the view.
C The underlying tables must be in the same schema.
D You need SELECT privileges only on the underlying tables.

24. Which SELECT statement will the result 'ello world' from the string 'Hello World'?

A SELECT SUBSTR( 'Hello World',1) FROM dual;
B SELECT INITCAP(TRIM ('Hello World', 1,1)) FROM dual;
C SELECT LOWER(SUBSTR('Hello World', 1, 1) FROM dual;
D SELECT LOWER(SUBSTR('Hello World', 2, 1) FROM dual;
E SELECT LOWER(TRIM ('H' FROM 'Hello World')) FROM dual;

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

A. COMMIT...
B. MERGE...
C. UPDATE...
D. DELETE...
E. CREATE...
F. DROP...

26. Which two tasks can your perform by using 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

27. 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 travelling

28. Display the names and hire dates for all employees who were hired before their managers, along with their manager’s names and hire dates.

29. Write a query that will display the total number of employees and, of the total, the number of employees hired in 1995, 1996, 1997, and 1998

30. Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?
A SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\';
B SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE;
C SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';
D SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';

31. Select the code which shows the countries that end in A or L

A. SELECT name FROM bbc WHERE name LIKE 'a%' AND name LIKE 'l%'
B. SELECT name FROM bbc WHERE name LIKE 'a%' OR name LIKE 'l%'
C . SELECT name FROM bbc WHERE name LIKE '%a' AND name LIKE '%l'
D. SELECT name FROM bbc WHERE name LIKE '%a' OR 'l%'
E. SELECT name FROM bbc WHERE name LIKE '%a' OR name LIKE '%l'

32. Which SELECT statement should you use to extract the year from 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(SYSDATE, 8), 'year') FROM dual;
E SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

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

A NOT NULL
B PRIMARY KEY
C FOREIGN KEY
D CHECK
E UNIQUE

34. 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. Selection, intersection, join
D. Intersection, projection, join
E. Difference, projection, product

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

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

37. Which operator can be used with a multiple-row subquery?
A. =
B. LIKE
C. BETWEEN
D. NOT IN
E. IS
F. <>

38. 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
C. USER_COL_PRIVS_MADE
D. USER_COL_PRIVS

39. Which of the following are all SQL CONTRAINTS:

a) NOT NULL, CREATE INDEX, FOREIGN KEY AND CHECK.
B) PRIMARY KEY, FORCE INDEX, UNIQUE AND DISTINCT.
C) FOREIGN KEY, DEFAULT, CHECK AND UNIQUE.
D) CREATE TABLE, CREATE INDEX, CREATE DATABASE AND CREATE VIEW.

40. 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 for all employees.
D. You want to rank the top three sales representatives who have sold the maximum number of products.

41. What will be the output of the following statement? SELECT LEN(CAST(LEFT('026-100', 3) AS INT))
a) 2
b) 3
c) 7
d) Statement will generate an error.

42. How to convert columns into rows and rows into columns in sql?

43. What will be the output of the following statement? SELECT LEN(CAST(LEFT('026-100', 3) AS INT))
a) 2
b) 3
c) 7
d) Statement will generate an error.

44. Which of the following statements is true concerning routines and triggers?

A. Both consist of procedural code.
B. Both have to be called to operate.
C. Both run automatically.
D. Both are stored in the database.

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

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

47. What is the best data type to store the birthdays of the US Presidents, starting with George Washington's birthday of February 22, 1732?
A) DATETIME
b) INT
c) SMALLDATETIME
d) VARCHAR

48. Write a query to display employee's name hired in year 1990.

49. WRITE a QUERY TO REPLACE SECOND OCCURRENCE OF 'A' WITH 'E' IN LAST_NAME FROM EMPLOYEES TABLE.

50. Write a query to display employees hired on same date ?

51. Write a query to display department's name , location, no of emp in each dept.

52. Which of the following SQL operations demands the use of wild cards comparisons?

A. IN
B. BETWEEN
C. EXISTS
D. LIKE

53. Which is not included in logical structure hierarchy?

A. Tablespace
B. Blocks
C. Datafile
D. Extents



54. Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?

A. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\';

B. SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE;

C. SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';

D. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';