SQL – Joining two or more tables

A database consists of three relations, namely STUDENT, ENROLL and LECTURE. The data for these relations is given by the following tables:

Express each of the following queries in SQL and also write the answer of the query

1)      List major of all students.

2)      For each student, list his/her name and the code all courses he/she enrolls in

3)      List the lecture time for all courses enrolled in by the student whose name is Jamal

4)      List the name and major of all the students who do not enroll in any course.

ANSWER

  1) SELECT DISTINCT Major

FROM STUDENT;

Economy

Communication

Management

Computer Sc

Mathematic

2) SELECT Name, Code

          FROM STUDENT, ENROLL

                  WHERE STUDENT.Stud_id= ENROLL.Stud_id;

Jamal	ECO4405
Putih	COM1200
Bakar	ECO4405
Bakar	SAK2500
Remi	COM2000
Remi	MTK4100
Remi	SAK2500

  3) SELECT Time                        

      FROM STUDENT S, ENROLL E, LECTURE L

            WHERE S.Stud_id = E.Stud_id AND E.Code= L.Code AND Name=’Jamal’;

MWF3

4)      SELECT Name, Major

FROM STUDENT

      WHERE Stud_id NOT IN

                  (SELECT Stud_id FROM ENROLL);

Gibson	Management
Rohaya	Computer Sc