PART A

Run the data set in Oracle Live SQL$.$

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

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

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

PART B

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

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

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

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

$3\mathrm{.}1$ group data that matches on some value

$3\mathrm{.}2$ perform an arithmetic or statistical operation on the group

For $3\mathrm{.}1$ we need to remove the duplicates to put the data into sets $($HINT: use GROUP BY$),$ for $3\mathrm{.}2$ we need to count the number of students $($HINT: use COUNT$($COL$))$

We now have almost enough information, but we also want the course name, which we know is available as it is a column in the table in the previous step. We need to add this column

drop table Reg;

drop table Student;

drop table Course;

REM Now create all the tables.

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

sname yarchar$(20)$ not null, gpa float, major char$(10),$ dob DATE$)$;

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

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

create table Reg$($ siid references Student$($sidd$)$ on delete cascade,

cno references Course$($cnos$)$ on delete cascade,

grade char$(2),$

primary key $($

s$.$id$,$ cnoo$))$;

REM Now insert all the rows.

insert into Student values$(\text{'}111\text{'},$ 'Joe', $3\mathrm{.}5,$ 'MIS', $\text{'}01-$AUG$-2000\text{'})$ if

insert into Student values$(\text{'}222\text{'},$ 'Jack', $3\mathrm{.}4\text{'},$ 'MIS', $\text{'}12-$JAN$-1999\text{'})$;

insert into Student values$(\text{'}333\text{'},$ 'Jill', $3\mathrm{.}2,\text{'}\text{'}$CS$\text{'},\text{'}15-$MAY$-1998\text{'})$;

insert into Student values$(\text{'}444\text{'},$ 'Mary', $3\mathrm{.}7\text{'},\text{'}$CS$\text{'},\text{'}17-$DEC$-2001\text{'})$;

insert into Student values$(\text{'}555\text{'},$ 'Peter', $3\mathrm{.}8\text{'},\text{'}$CS$\text{'},\text{'}19-$MAR$-1999\text{'})$;

insert into Student values$(\text{'}666\text{'},$ 'Pat', $3\mathrm{.}9,$ 'Math', $\text{'}31-$MAY$-2000\text{'})$;

insert into Student values$(\text{'}777\text{'},$ 'Tracy', $4\mathrm{.}0,$ 'Math', $\text{'}18-$JUL$-1997\text{'})$;

insert into Course values$(\text{'}$c$101\text{'},$ 'intro', $3,\text{'}$CS$\text{'})$;

insert into Course values$(\text{'}$m$415\text{'},$ 'database', $4,$ 'Bus'$)$;

insert into Course values$(\text{'}$m$215\text{'},$ 'programming', $4,$'Bus'$)$;

insert into Course values$(\text{'}$a$444\text{'},$ 'calculus', $3\text{'},$ 'Math'$)$;

insert into Reg values$(\text{'}111\text{'},\text{'}$c$101\text{'},\text{'}$A$\text{'})$;

insert into Reg values$(\text{'}111\text{'},\text{'}$m$215\text{'},\text{'}$B$\text{'})$;

insert into Reg values$(\text{'}111\text{'},\text{'}$m$415\text{'},\text{'}$A$\text{'})$;

insert into Reg values$(\text{'}222\text{'},\text{'}$m$215\text{'},\text{'}$A$\text{'})$;

insert into Reg values$(\text{'}222\text{'},\text{'}$m$415\text{'},\text{'}$B$\text{'})$;

insert into Reg values$(\text{'}333\text{'},\text{'}$c$101\text{'},\text{'}$A$\text{'})$;

insert into Reg values$(\text{'}444\text{'},\text{'}$m$215\text{'},\text{'}$C$\text{'})$;

insert into Reg values$(\text{'}444\text{'},\text{'}$m$415\text{'},\text{'}$B$\text{'})$;

insert into Reg values$(\text{'}555\text{'},\text{'}$C$101\text{'},\text{'}$B$\text{'})$;

insert into Reg values$(\text{'}555\text{'},\text{'}$m$215\text{'},\text{'}$A$\text{'})$;

insert into Reg values$(\text{'}555\text{'},\text{'}$m$415\text{'},\text{'}$A$\text{'})$;

insert into Reg values$(\text{'}666\text{'},\text{'}$c$101\text{'},\text{'}$A$\text{'})$;