SQL Test Answer Part-05

 

41. Which are not DCL operations?

  1. Insert
  2. Grant
  3. Delete
  4. Update
  5. Revoke
  6. Commit
  7. Rollback

Answer: a. Insert, c. Delete, d. Update, f. Commit, g. Rollback

DCL: Grant, Revoke

42. Which of the following is not a DML command?

  1. Set transaction
  2. Explain plan
  3. Update
  4. Grant
  5. Insert
  6. Create
  7. Alter
  8. Revoke

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?

  1. Avg
  2. Select
  3. Order By
  4. Sum
  5. Union
  6. Group By
  7. Having

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);

  1. = should be replaced by in operator
  2. Order by clause in the subquery should be preceded with a group by clause
  3. Order by clause in the subquery can be used only if the where and group by clauses have been applied
  4. Group by clause should be applied to the outer query
  5. An order by clause is not allowed in a subquery
  6. 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?

  1. Alter table t drop primary key
  2. Alter table t drop constraint pk
  3. Drop primary key constraint on table t.
  4. 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?

  1. Alter table t add primary key(c1);
  2. Alter table t add constraint pk primary key(c1);
  3. Alter table t add (constraint pk primary key(c1));
  4. 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)

START_DATE DATE

END_DATE DATE

The aggregate functions valid on the START_DATE column are: 

  1. SUM(start_date)
  2. AVG(start_date)
  3. COUNT(start_date)
  4. AVG(start_date, end_date)
  5. MIN(start_date)

Answer: c, e

49. Which of the following statements are not true regarding the primary key?

  1. There can only be one primary key in a table
  2. An index is automatically generated upon creation of a primary key
  3. A primary can accept null values
  4. A primary key can be composite
  5. A primary key constraint can be either at the column level or at the table level
  6. 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

Comments

comments

Leave a Comment

Time limit is exhausted. Please reload CAPTCHA.