You need to input this file in Oracle live SQL (all studentsshould have registered to obtain a free student account).

Part A

Run the data set in Oracle live SQL.

An error might occur on the drop statements at the start of theexecution but ignore that.

Reason: The first lines of the file (DROP xxx) are put there incase you run the program more than once make sure that all tablesare visible and contain data.

Once you have the data set up and running you will find a numberof tables.

Part B

Provide a list of courses by course ID and name (intro etc.) andthe number of students who took each course. The table is requestedby an Administrator, who is not familiar with databases. Hence itis required that the tables are arranged for easyreading.

1. Which tables have the information we require to provide thelist requested? Give the table names (1 point)

2. Once you have merged those tables (Hint: use INNER JOIN) use SQL query to find a new table where the shared column is CNO.Give the SQL command you used to create this table. (1point)

3. However, the course information is repeated for eachstudent. We actually just want the number of students per course.When we see a sentence with "number", "per", "sum", "average" weknow that we need to perform two actions: (1 point)

3.1 group data that matches on some value

3.2 perform an arithmetic or statistical operation on thegroup

For 3.1 we need to remove the duplicates to put the datainto sets (Hint: use GROUP BY), for 3.2 we need to countthe number of students (Hint: use COUNT(COL) )

We now have almost enough information, but we also want thecourse name, which we know is available as it is a column in thetable in the previous step. We need to add this column (C.CNO) toboth the SELECT and the GROUP BY.

Provide the SQL query you use to achieve section 3.

4. Finally, The table is requested by an Administrator, whois not familiar with databases (1 point).

The field headings are not easily understood by a layperson. Tofix this, we can name each column in a more user-friendly way.(Hint use AS )

Provide a snapshot of the final database look

REM drop all the tables. Note that you need to drop the

REM dependent table first before dropping the base tables.

drop table Reg;

drop table Student;

drop table Course;

REM Now create all the tables.

create table Student(sid char(10) primary key,

sname varchar(20) not null, gpa float, major char(10), dobDATE);

create table Course(cno char(10) primary key,

cname varchar(20) not null, credits int, dept char(10));

create table Reg( sid references Student(sid) on deletecascade,

cno references Course(cno) on delete cascade,

grade char(2),

primary key (sid, cno));

REM Now insert all the rows.

insert into Student values('111', 'Joe', 3.5 , 'MIS','01-AUG-2000');

insert into Student values('222', 'Jack', 3.4 , 'MIS','12-JAN-1999');

insert into Student values('333', 'Jill', 3.2 , 'CS','15-MAY-1998');

insert into Student values('444', 'Mary', 3.7 , 'CS','17-DEC-2001');

insert into Student values('555', 'Peter', 3.8 , 'CS','19-MAR-1999');

insert into Student values('666', 'Pat', 3.9, 'Math','31-MAY-2000');

insert into Student values('777', 'Tracy', 4.0, 'Math','18-JUL-1997');

insert into Course values('c101', 'intro', 3 , 'CS');

insert into Course values('m415', 'database', 4 , 'Bus');

insert into Course values('m215', 'programming', 4 , 'Bus');

insert into Course values('a444', 'calculus', 3 , 'Math');

insert into Reg values('111', 'c101', 'A');

insert into Reg values('111', 'm215', 'B');

insert into Reg values('111', 'm415', 'A');

insert into Reg values('222', 'm215', 'A');

insert into Reg values('222', 'm415', 'B');

insert into Reg values('333', 'c101', 'A');

insert into Reg values('444', 'm215', 'C');

insert into Reg values('444', 'm415', 'B');

insert into Reg values('555', 'c101', 'B');

insert into Reg values('555', 'm215', 'A');

insert into Reg values('555', 'm415', 'A');

insert into Reg values('666', 'c101', 'A');