Question

Use a single SQL statement to create a relational table and to load into the table department name, subject code, year of running and session of running that offered by the departments.

Note that a running subject offered by a department means a lecturer of the department has been assigned to teach the subject.

Next, enforce the appropriate consistency constraints on the new table.

   When ready use SELECT statement to list the contents of the relational table created and filled with data in the ascending order of the department name, then in the ascending order of code, year running, and session running.      

Finally, drop the relational table created in this subtask.  

Answer 1

Pre requisites

SQL> create table department(dept_name varchar(15) primary key, dept_location varchar(10));

Table created.

SQL> create table lecturer(lecturer_id varchar(3) primary key, name varchar(15),
dept_name varchar(15) references department(dept_name));

Table created.
SQL> create table coruse(course_code varchar(5) primary key, subject_code varchar(5),
dept_name varchar(15) references department(dept_name), year_of_running number(5),
session_of_running varchar(10), teacher varchar(3) references lecturer(lecturer_id));

Table created.

SQL> insert into department values('CSE', 'campus 1');

1 row created.

SQL> insert into department values('ECE', 'campus 1');

1 row created.

SQL> insert into department values('ME', 'campus 2');

1 row created.

SQL> insert into department values('Civil', 'campus 2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from department;

DEPT_NAME DEPT_LOCAT
--------------- ----------
CSE campus 1
ECE campus 1
ME campus 2
Civil campus 2

SQL> insert into lecturer values('C01', 'Jack','CSE');

1 row created.

SQL> insert into lecturer values('C02', 'Jones','CSE');

1 row created.

SQL> insert into lecturer values('C03', 'Jenifer','CSE');

1 row created.

SQL> insert into lecturer values('C04', 'Keily','CSE');

1 row created.

SQL> insert into lecturer values('E01', 'Kashyap','ECE');

1 row created.

SQL> insert into lecturer values('E02', 'Karuna','ECE');

1 row created.

SQL> insert into lecturer values('E03', 'Susi','ECE');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from lecturer;

LEC NAME DEPT_NAME
--- --------------- ---------------
C01 Jack CSE
C02 Jones CSE
C03 Jenifer CSE
C04 Keily CSE
E01 Kashyap ECE
E02 Karuna ECE
E03 Susi ECE

7 rows selected.

SQL> desc coruse;
Name Null? Type
----------------------------------------- -------- ----------------------------
COURSE_CODE NOT NULL VARCHAR2(5)
SUBJECT_CODE VARCHAR2(5)
DEPT_NAME VARCHAR2(15)
YEAR_OF_RUNNING NUMBER(5)
SESSION_OF_RUNNING VARCHAR2(10)
TEACHER VARCHAR2(3)

SQL> insert into coruse values('CS11', 'Java', 'CSE', 2020, 'spring', 'C01');

1 row created.

SQL> insert into coruse values('CS21', 'DB', 'CSE', 2020, 'spring', 'C02');

1 row created.

SQL> insert into coruse values('CS23', 'CO', 'CSE', 2020, 'spring', 'C03');

1 row created.

SQL> insert into coruse values('EC23', 'MP', 'ECE', 2020, 'Summer', 'E01');

1 row created.

SQL> insert into coruse values('EC24', 'MC', 'ECE', 2019, 'Fall', 'E02');

1 row created.

SQL> select * from coruse;

COURS SUBJE DEPT_NAME YEAR_OF_RUNNING SESSION_OF TEA
----- ----- --------------- --------------- ---------- ---
CS11 Java CSE 2020 spring C01
CS21 DB CSE 2020 spring C02
CS23 CO CSE 2020 spring C03
EC23 MP ECE 2020 Summer E01
EC24 MC ECE 2019 Fall E02

The above table cretation and data populations should already exist in your database system in order to fulfull your question requirements. Following are the actual queries for your question.

Actual query for your question;

SQL> create table coursedept1
as(select coruse.dept_name, subject_code, year_of_running, session_of_running from coruse, lecturer
where coruse.teacher= lecturer.lecturer_id and coruse.dept_name=lecturer.dept_name);

Table created.

SQL> select * from coursedept1;

DEPT_NAME SUBJE YEAR_OF_RUNNING SESSION_OF
--------------- ----- --------------- ----------
CSE Java 2020 spring
CSE DB 2020 spring
CSE CO 2020 spring
ECE MP 2020 Summer
ECE MC 2019 Fall

SQL> select * from coursedept1 order by dept_name;

DEPT_NAME SUBJE YEAR_OF_RUNNING SESSION_OF
--------------- ----- --------------- ----------
CSE Java 2020 spring
CSE DB 2020 spring
CSE CO 2020 spring
ECE MP 2020 Summer
ECE MC 2019 Fall

SQL> select * from coursedept1 order by subject_code;

DEPT_NAME SUBJE YEAR_OF_RUNNING SESSION_OF
--------------- ----- --------------- ----------
CSE CO 2020 spring
CSE DB 2020 spring
CSE Java 2020 spring
ECE MC 2019 Fall
ECE MP 2020 Summer

SQL> select * from coursedept1 order by year_of_running;

DEPT_NAME SUBJE YEAR_OF_RUNNING SESSION_OF
--------------- ----- --------------- ----------
ECE MC 2019 Fall
CSE DB 2020 spring
ECE MP 2020 Summer
CSE Java 2020 spring
CSE CO 2020 spring

SQL> select * from coursedept1 order by session_of_running;

DEPT_NAME SUBJE YEAR_OF_RUNNING SESSION_OF
--------------- ----- --------------- ----------
ECE MC 2019 Fall
ECE MP 2020 Summer
CSE DB 2020 spring
CSE Java 2020 spring
CSE CO 2020 spring

SQL> drop table coursedept1;

Table dropped.