Question

Consider the following relations: Please answer in the form of symbol. Thank You

Student (ssn, name, address, major)

Course(code, title)

Registered(ssn,code)

1. List the titles of all courses
2. List the information of the students majoring in ‘CS’
3. List the codes of courses for which at least one student is registered (registered courses
4. List the titles of registered courses
5. List the codes of courses for which no student is registered

Answer 1

As per the question we have to write SQL queries for the tables.

Student (ssn, name, address, major)

Course(code, title)

Registered(ssn,code)

As per above tables we have to implement queries as given below:

1. List the titles of all courses.

2. List the information of the students majoring in ‘CS’.

3. List the codes of courses for which at least one student is registered (registered courses).

4. List the titles of registered courses.

5. List the codes of courses for which no student are registered.

Let’s see queries one by one.

1. List the titles of all courses.

Solution:-

Select title from course;

2. List the information of the students majoring in ‘CS’.

    Solution:-

          Select ssn, name, address from Student

          Where major=’CS’;

3. List the codes of courses for which at least one student is registered (registered courses).

Solution:-

Select code from course

INNER JOIN Student S on Course.name = S.code

Where S.code>1;

4. List the titles of registered courses.

Solution:-

Select C.title from Course C,

INNER JOIN Registered_Course R on c.title = R.Registered_Course

Group by c.title;

5. List the codes of courses for which no student are registered.

Solution:-

Select code from Course

INNER JOIN Student S on Registered.code = S.code

Where code = null;