41. Which are not DCL operations?
Answer: a. Insert, c. Delete, d. Update, f. Commit, g. Rollback
DCL: Grant, Revoke
42. Which of the following is not a DML command?
- Set transaction
- Explain plan
Answer: a. Set transaction, d. Grant, f. Create, g. Alter, h, Revoke.
DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE.
DCL: GRANT, REVOKE
TCL: COMMIT, SAVEPOINT, ROLLBACK, SET TRANSACTION
43. Which of the following are aggregate functions in SQL?
- Order By
- Group By
Answer: Avg, Sum
SQL aggregate functions: AVG, COUNT, FIRST, LAST, MAX, MIN, SUM.
Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values.
44. What is the error in the following query if the students table contains several records?
Select name from students where name = (Select name from students order by name);
- = should be replaced by in operator
- Order by clause in the subquery should be preceded with a group by clause
- Order by clause in the subquery can be used only if the where and group by clauses have been applied
- Group by clause should be applied to the outer query
- An order by clause is not allowed in a subquery
- There is no error
Answer: a, e
45. There is a table t upon which a primary key constraint by the name pk is applied. What will be the correct syntax to drop the constraint?
- Alter table t drop primary key
- Alter table t drop constraint pk
- Drop primary key constraint on table t.
- Alter table t delete primary key
Answer: a, b.
46. 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?
- Alter table t add primary key(c1);
- Alter table t add constraint pk primary key(c1);
- Alter table t add (constraint pk primary key(c1));
- Alter table t add pk constraint primary key(c1);
Answer: a. Alter table t add primary key (c1);
47. Which method should be used to drop the master table if its primary key is being referenced by a foreign key in some other table?
a. Use cascade constraints clause with drop table
b. Disable the foreign key of the detail table
c. Drop the primary and foreign key constraints of both the tables
d. None of the above
Answer: a, b.
48. Examine the description of the STUDENTS table
STD_ID NUMBER (4)
COURSE_ID VARCHAR2 (10)
The aggregate functions valid on the START_DATE column are:
- AVG(start_date, end_date)
Answer: c, e
49. Which of the following statements are not true regarding the primary key?
- There can only be one primary key in a table
- An index is automatically generated upon creation of a primary key
- A primary can accept null values
- A primary key can be composite
- A primary key constraint can be either at the column level or at the table level
- There can be more than one primary key in a table
Answer: c, f
50. Examine the data in the EMPLOYEES table given below:
LAST_NAME DEPARTMENT_ID SALARY
ALLEN 10 3000
MILLER 20 1500
KING 20 2200
DAVIS 30 5000
Which of the following Subqueries work?
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 department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
Answer: c, d