sql

Consider the following schema −

STUDENTS(student_code, first_name, last_name, email,

phone_no, date_of_birth, honours_subject, percentage_of_marks);

Which query will display the names and honours subjects of all students and if a student has not yet been given a honours subject yet, then it should display ‘No Honours Yet’.

• select first_name, last name, nvl(honours_subject, ‘No Honours Yet’) from students;

• select first_name, last name, nvl2(honours_subject, ‘No Honours Yet’) from students;

• select first_name, last name, honours_subject, from students;

• select first_name, last name, nullif(honours_subject, ‘No Honours Yet’) from students;

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 the ON clause

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

• select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h join location l on(subject_code);

• select h.subject_name, l.department_name, h.department_head, l.city from honours_subject h, location l on(subject_code);

• None

Which of the following query will result in an error?

• select dept_id, avg(salary) from employees group by dept_id;

• select avg(salary) from employees group by dept_id;

• select dept_id, job_id, avg(salary) from employees group by dept_id, job_id;

• select dept_id, count(name) from employees;

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

• It is a referential integrity constraint.

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

• A foreign key value cannot be null.

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

The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T WHERE CUSTOMER_T. CUSTOMER_ID = ORDER_T. CUSTOMER_ID

• Equi-join

• Natural join

• Outer join

• Cartesian Product