SQL Online Quiz

Q 1 - Consider the following schema −

STUDENTS(student_code, first_name, last_name, email, 
         phone_no, date_of_birth, honours_subject, percentage_of_marks);

Which of the following query would display names of all the students whose honours subject is English, or honours subject is Spanish and percentage of marks more than 80?

A - select first_name, last name from students where (honours_subject = “English” or honours_subject = “Spanish” ) and percentage_of_marks > 80;

B - select first_name, last name from students where honours_subject = “English” or honours_subject = “Spanish” and percentage_of_marks > 80;

C - select first_name, last name from students where honours_subject = “English” and honours_subject = “Spanish” or percentage_of_marks > 80;

D - select first_name, last name from students where (honours_subject = “English”) and honours_subject = “Spanish” and percentage_of_marks > 80;

Q 2 - What is returned by ROUND(789.8389, 2)?

A - 789.84

B - 789.83

C - 78

D - 789.00

Q 3 - For some particular assignment, you need to compare two values, if both are equal, the result would be null, and if the values are not equal then the first value should be returned. Which function should you use?

A - NVL

B - NVL2

C - NULLIF

D - COALESCE

Q 4 - Consider the following schema −

HONOURS_SUBJECT(subject_code, subject_name, department_head);

LOCATIONS(subject_code, department_name, location_id, city);

Select the right query for retrieving records from the tables HONOURS_SUBJECT and LOCATIONS with a left outer join

A - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h left outer join location l on(h.subject_code = l.subject_code);

B - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h left outer join location l on(subject_code);

C - select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h left join location l on(h.subject_code = l.subject_code);

D - None of the above.

Q 5 - You want to calculate the minimum percentage of marks obtained under each honours group students, where the minimum marks is more than the minimum marks in economics department. Under which clause should the subquery be?

A - WHERE clause

B - FROM clause

C - HAVING clause

D - None of the above.

Q 6 - Which of the following is not true about multiple-row subqueries?

A - Multiple row subqueries return multiple rows from the outer SELECT statement.

B - Multiple row subqueries return multiple rows from the inner SELECT statement.

C - Multiple row subqueries use multiple-row comparison operators.

D - All of the above.

Q 7 - Consider the following schema −

LOCATIONS(subject_code, department_name, location_id, city);

Which code snippet will alter the table LOCATIONS and delete the column named CITY?

A - MODIFY TABLE locations DROP (city varchar2(30));

B - ALTER TABLE locations DROP COLUMN city;

C - ALTER TABLE locations DROP (city);

D - None of the above.

Q 8 - Which of the following is not true about a FOREIGN KEY constraint?

A - It is a referential integrity constraint.

B - It establishes a relationship between a primary key or a unique key in the same table or a different table.

C - A foreign key value cannot be null.

D - A foreign key value must match an existing value in the parent table.

Q 9 - Which of the following code will delete a sequence named loc_seq?

A - delete sequence loc-seq;

B - drop sequence loc_seq;

C - delete primary key loc_sec;

D - drop primary key loc_sec;

Q 10 - Which statement is used for allocating system privileges to the users?

A - CREATE

B - GRANT

C - REVOKE

D - ROLE