Homework 4 SQL: Join, Integrity Rules 1. Create a file create.sql where you crea
ID: 3724780 • Letter: H
Question
Homework 4 SQL: Join, Integrity Rules 1. Create a file create.sql where you create the following tables and insert all test records. Make sure you have set echo on in the beginning of the file. Spool your output to create.txt Run create-sgl until there is no error. Tumma print out of the create.txt file. Courses Students Num Andy IN 38 101 101 102 103 S380 B IS385 A IS380 A IS355 B S380 Database Lee IS385 Systems S355 elecomm Lee Andy IS 102 Betty FIN 03 Cindy IS 1.8 Smith Field definitions: SNum varchar2(3) SName varchar2(25) Major varchar2(20) GPA Number(3,2) CNum varchar2(8) Grade varchar2(1) CTitle varchar(25) Instr varchar2(25) Note: a) The tables must have appropriate primary key and foreign key declaration. b) Since It is likely that you will run create.sql many times and you will run into the following problem. The first time you run it, Oracle creates the S, C, and G tables. The second time you run it, Oracle will not let you create the tables again since the tables are already in place. It is a common practice to drop the tables at first before you create them, like this: drop table enroliments: drop table courses drop table students: create table studentsi create table Courses create table Enroliments 34 Spring 2018 IS 380 Lecture Notes 2. Create a file query.sql where you write SQL code of questions question number clearly with comments. Make sure you have set echo on in the beginning of the file and spool to query.txt. Run query.sal file until there is no error. Turn in a print out of the query.txt file, Please label the 1. Display CNum, tle and Grade of courses Andy took. 2. Display SNum and SName of students who took IS380. 3. Display SNum and SName of students who took IS380 and received an 'A 4. Display SNum and SName of students who are IS major and took IS380. 5. Display Snum and Sname of 'IS" student who received an 'A in IS380"Explanation / Answer
create.sql:
set echo on;
spool create.txt
drop table Enrollments;
drop table Students;
drop table Courses;
create table Students(SNum varchar2(3) primary key, Sname varchar2(25), Major varchar2(20), GPA number(3,2));
create table Courses(CNum varchar2(8) primary key, Ctitle varchar2(25), Instr varchar2(25));
create table Enrollments(SNum varchar2(3) references Students, CNum varchar2(8) references Courses, Grade varchar2(1), primary key(SNum,CNum));
insert into Students values('101','Andy','IS',3.2);
insert into Students values('102','Betty','FIN',1.8);
insert into Students values('103','Cindy','IS',3.8);
insert into Courses values('IS380','Database','Lee');
insert into Courses values('IS385','Systems','Smith');
insert into Courses values('IS355','Telecomm','Lee');
insert into Enrollments values('101','IS380','B');
insert into Enrollments values('101','IS385','A');
insert into Enrollments values('102','IS380','A');
insert into Enrollments values('103','IS355','B');
spool off;
create.txt
> drop table Enrollments
table ENROLLMENTS dropped.
> drop table Students
table STUDENTS dropped.
> drop table Courses
table COURSES dropped.
> create table Students(SNum varchar2(3) primary key, Sname varchar2(25), Major varchar2(20), GPA number(3,2))
table STUDENTS created.
> create table Courses(CNum varchar2(8) primary key, Ctitle varchar2(25), Instr varchar2(25))
table COURSES created.
> create table Enrollments(SNum varchar2(3) references Students, CNum varchar2(8) references Courses, Grade varchar2(1), primary key(SNum,CNum))
table ENROLLMENTS created.
> insert into Students values('101','Andy','IS',3.2)
1 rows inserted.
> insert into Students values('102','Betty','FIN',1.8)
1 rows inserted.
> insert into Students values('103','Cindy','IS',3.8)
1 rows inserted.
> insert into Courses values('IS380','Database','Lee')
1 rows inserted.
> insert into Courses values('IS385','Systems','Smith')
1 rows inserted.
> insert into Courses values('IS355','Telecomm','Lee')
1 rows inserted.
> insert into Enrollments values('101','IS380','B')
1 rows inserted.
> insert into Enrollments values('101','IS385','A')
1 rows inserted.
> insert into Enrollments values('102','IS380','A')
1 rows inserted.
> insert into Enrollments values('103','IS355','B')
1 rows inserted.
> spool off
query.sql
set echo on;
spool query.txt
--1.
select distinct(c.CNum), c.Ctitle, e.Grade from Courses c, Enrollments e, Students s where s.Sname = 'Andy' and s.Snum = e.Snum and e.CNum = c.CNum;
--2.
select distinct(s.SNum), s.Sname from Students s, Enrollments e where e.CNum = 'IS380' and e.SNum = s.SNum;
--3.
select distinct(s.SNum), s.Sname from Students s, Enrollments e where e.CNum = 'IS380' and e.Grade = 'A' and e.SNum = s.SNum;
--4.
select distinct(s.SNum), s.Sname from Students s, Enrollments e where s.Major = 'IS' and e.CNum = 'IS380' and e.SNum = s.SNum;
--5.
select distinct(s.SNum), s.Sname from Students s, Enrollments e where e.SNum = s.SNum and s.Major = 'IS' and e.CNum = 'IS380' and e.Grade ='A' ;
spool off;
query.txt
> --1.
> select distinct(c.CNum), c.Ctitle, e.Grade from Courses c, Enrollments e, Students s where s.Sname = 'Andy' and s.Snum = e.Snum and e.CNum = c.CNum
CNUM CTITLE GRADE
-------- ------------------------- -----
IS380 Database B
IS385 Systems A
> --2.
> select distinct(s.SNum), s.Sname from Students s, Enrollments e where e.CNum = 'IS380' and e.SNum = s.SNum
SNUM SNAME
---- -------------------------
101 Andy
102 Betty
> --3.
> select distinct(s.SNum), s.Sname from Students s, Enrollments e where e.CNum = 'IS380' and e.Grade = 'A' and e.SNum = s.SNum
SNUM SNAME
---- -------------------------
102 Betty
> --4.
> select distinct(s.SNum), s.Sname from Students s, Enrollments e where s.Major = 'IS' and e.CNum = 'IS380' and e.SNum = s.SNum
SNUM SNAME
---- -------------------------
101 Andy
> --5.
> select distinct(s.SNum), s.Sname from Students s, Enrollments e where e.SNum = s.SNum and s.Major = 'IS' and e.CNum = 'IS380' and e.Grade ='A'
no rows selected
> spool off
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.