Question

9	We want to find out the courses that each student is not enrolled in. Give a listing of the course descriptions, and the students (lname) who are not taking that specific course (Use a cartesian product and union it with a minus)

drop table student_class;
drop table student;
drop table class;

create table student
(
ssn char(11) not null,
lname varchar(40) not null,
fname varchar(20) not null,
phone char(12),
address varchar(40),
city varchar(20),
state char(2),
zip char(5),
dob date,
salary number
);

create table class
(
   class_code varchar2(10),
   class_description varchar2(30)
);

create table student_class
(
   ssn char(11) not null,
   class_code varchar2(10) not null  
);

alter table student add constraint student_pk primary key (ssn);
alter table student_class add constraint stu_cla_pk primary key(ssn,class_code);
alter table class add constraint clss_pk primary key(class_code);
alter table student_class add constraint st_fk foreign key (ssn) references student;
alter table student add constraint student_uk unique(lname,fname);
create index clss_desc_indx on class (class_description);

insert into student
values('409-56-7008', 'Bennet', 'Abraham',
'', '6223 Bateman St.', 'Berkeley', 'CA', '94705','26-feb-1988',10000);

insert into student
values ('213-46-8915', 'Green', 'Marjorie',
'', '309 63rd St. #411', 'Oakland', 'CA', '94618','25-feb-1989',20000 );

insert into student
values('238-95-7766', 'Gren', 'Cheryl',
'415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705',null,45000);

insert into student
values('998-72-3567', 'Greeenr', 'Albert',
'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152','24-feb-1992',15000);

insert into student
values('427-17-2319', 'Dull', 'Ann',
'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301','23-feb-1993',30000);

insert into student
values('672-71-3249', 'Yokomoto', 'Akiko',
'415 935-4228', '3 Silver Ct.', 'Walnut Creek', 'CA', '94595','22-feb-1994',35000);

insert into student
values('267-41-2394', 'O''Leary', 'Michael',
'409-56-7008', '22 Cleveland Av. #14', 'San Jose', 'CA', '95128','12-feb-1995',32000);

insert into student
values('472-27-2349', 'Gringlesby', 'Burt',
'707 938-6445', 'PO Box 792', 'Covelo', 'NY', '95428','12-feb-1996',34000);

insert into student
values('527-72-3246', 'Greene', 'Morningstar',
'615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN', '37215','11-feb-1995',25000);

insert into student
values('999-00-0000', 'Al', 'Cal',
'615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN', '37215','06-feb-1998',22000);

insert into student
values('172-32-1176', 'White', 'Johnson',
'408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'ma', '94025','05-feb-1999',23000);

insert into student
values('712-45-1867', 'del Castillo', 'Innes',
'615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', '48105','04-feb-1982',23500);

insert into student
values('846-92-7186', 'Hunter', 'Sheryl',
'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301','03-feb-1979',18000);

insert into student
values('486-29-1786', 'Locksley', 'Chastity',
'415 585-4620', '18 Broadway Av.', 'San Francisco', 'CA', '94130','02-feb-1978',15500);

insert into student
values('648-92-1872', 'Blotchet-Halls', 'Reginald',
'503 745-6402', '55 Hillsdale Bl.', 'Corvallis', 'OR', '97330','01-feb-1977',43000);

insert into class
values('37','Database Programming');

insert into class
values('3','Introduction to Computers');

insert into class
values('32','Introduction to C programming');

insert into class
values('34','Intro to principles');

insert into class
values('14A','Operating systems');

insert into class
values('1','');

insert into class
values('55','');

insert into student_class
values('409-56-7008', '37');

insert into student_class
values ('213-46-8915', '32');

insert into student_class
values('998-72-3567', '3');

insert into student_class
values('998-72-3567', '55');

insert into student_class
values('427-17-2319', '34');

insert into student_class
values('672-71-3249', '3');

insert into student_class
values('267-41-2394', '34');

insert into student_class
values('472-27-2349', '32');

insert into student_class
values('527-72-3246', '1');

insert into student_class
values('172-32-1176', '37');

insert into student_class
values('712-45-1867', '37');

insert into student_class
values('846-92-7186', '32');

insert into student_class
values('486-29-1786', '1');

insert into student_class
values('648-92-1872', '55');

***************************************************************************************************

Answer 1

create table student(ssn char(11) not null,
                   lname varchar(40) not null,
                   fname varchar(20) not null,
                   phone char(12),
                   address varchar(40),
                   city varchar(20),
                   state char(2),
                   zip char(5),
                   dob date,
                   salary number);
                  
create table class(class_code varchar2(10),
                   class_description varchar2(30));

create table student_class(ssn char(11) not null,
                           class_code varchar2(10) not null);
                          
                          
alter table student add constraint student_pk primary key (ssn);

alter table student_class add constraint stu_cla_pk primary key(ssn,class_code);

alter table class add constraint clss_pk primary key(class_code);

alter table student_class add constraint st_fk foreign key (ssn) references student;

alter table student add constraint student_uk unique(lname,fname);

create index clss_desc_indx on class (class_description);

insert into student values('409-56-7008', 'Bennet', 'Abraham','', '6223 Bateman St.', 'Berkeley', 'CA', '94705','26-feb-1988',10000);
insert into student values ('213-46-8915', 'Green', 'Marjorie','', '309 63rd St. #411', 'Oakland', 'CA', '94618','25-feb-1989',20000 );
insert into student values('238-95-7766', 'Gren', 'Cheryl','415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705',null,45000);
insert into student values('998-72-3567', 'Greeenr', 'Albert','801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152','24-feb-1992',15000);
insert into student values('427-17-2319', 'Dull', 'Ann','415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301','23-feb-1993',30000);
insert into student values('672-71-3249', 'Yokomoto', 'Akiko','415 935-4228', '3 Silver Ct.', 'Walnut Creek', 'CA', '94595','22-feb-1994',35000);
insert into student values('267-41-2394', 'O''Leary', 'Michael','409-56-7008', '22 Cleveland Av. #14', 'San Jose', 'CA', '95128','12-feb-1995',32000);
insert into student values('472-27-2349', 'Gringlesby', 'Burt','707 938-6445', 'PO Box 792', 'Covelo', 'NY', '95428','12-feb-1996',34000);
insert into student values('527-72-3246', 'Greene', 'Morningstar','615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN', '37215','11-feb-1995',25000);
insert into student values('999-00-0000', 'Al', 'Cal','615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN', '37215','06-feb-1998',22000);
insert into student values('172-32-1176', 'White', 'Johnson','408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'ma', '94025','05-feb-1999',23000);
insert into student values('712-45-1867', 'del Castillo', 'Innes','615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', '48105','04-feb-1982',23500);
insert into student values('846-92-7186', 'Hunter', 'Sheryl','415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301','03-feb-1979',18000);
insert into student values('486-29-1786', 'Locksley', 'Chastity','415 585-4620', '18 Broadway Av.', 'San Francisco', 'CA', '94130','02-feb-1978',15500);
insert into student values('648-92-1872', 'Blotchet-Halls', 'Reginald','503 745-6402', '55 Hillsdale Bl.', 'Corvallis', 'OR', '97330','01-feb-1977',43000);

insert into class values('37','Database Programming');

insert into class values('3','Introduction to Computers');
insert into class values('32','Introduction to C programming');

insert into class values('34','Intro to principles');

insert into class values('14A','Operating systems');

insert into class values('1','');

insert into class values('55','');

insert into student_class values('409-56-7008', '37');

insert into student_class values ('213-46-8915', '32');

insert into student_class values('998-72-3567', '3');

insert into student_class values('998-72-3567', '55');

insert into student_class values('427-17-2319', '34');

insert into student_class values('672-71-3249', '3');

insert into student_class values('267-41-2394', '34');

insert into student_class values('472-27-2349', '32');

insert into student_class values('527-72-3246', '1');

insert into student_class values('172-32-1176', '37');

insert into student_class values('712-45-1867', '37');

insert into student_class values('846-92-7186', '32');

insert into student_class values('486-29-1786', '1');

insert into student_class values('648-92-1872', '55');

select * from student;
select * from class;
select * from student_class;

QUERIES

select ssn,class_code
from class,student
where (ssn,class_code) not in (select ssn,class_code from student_class);

select NVL(class_description,'No description available yet') class_description,lname,ssn,class_code
from class,student
where (ssn,class_code) not in (select ssn,class_code from student_class);

SQL select Fron student; LNAME SSH FNAME PHONE ADDRESS CITY ST ZIP DOB SALARY 89-56-7008 Bennet 213-46-8915 Green 238-95-7766 Gren 998-72-3567 Greeenr 427-17-2319 Dull 672-71-3249 Yokonoto 267-41-2394 O'Leary 472-27-2349 Gringlesby 527-72-3246 Greene 999-80 8008 A1 172-32-1176 White 712-5-1867 del Castillo 846-92-7186 Hunter 486-29-1786 Locksley 618-92-1872 Blotchet-Halls Abrahanm Marjorie Cheryl 6223 Batenan St 309 63rd St. #411 Berkeley Oakland Berkeley Salt Lake City Palo Alto Walnut Creek San Jose Covelo Nashuille Hashuille Menlo Park Ann Arbor Palo Alto San Francisco Corvallis CA 94785 26-FEB-88 C 94618 25-FEB-89 CA 947 05 UT 84152 2 CA 94301 23-FEB-93 CA 94595 22-FEB CA 95128 12 NY 95428 12-FEB-96 TN 37215 TN 37215 86-FEB-98 na 94025 05-FEB HI 8105 0 C 94301 03-FEB CA 94130 OR 973 15 548-7723 589 Darwin Ln 801 826-0752 67 Seventh Av. 415 836-7128 3410 Blonde St 415 935-4228 3 silver ct Jy09-56-7008 22 Cleveland Au. #14 707 938-6445 PO Box 792 615 297-2723 22 Graybar House Rd 615 297-2723 22 Graybar House Rd 408 496-7223 10932 Bigge Rd 615 996-8275 2286 Cran P1- #8ó 15 836-7128 3419 Blonde St 15 585-4620 18 Broaduay Au 503 745-602 55 Hillsdale Bl 45000 Akiko Michael Morningstar Johnson Sheryl Chastity 15 rows selected SQL> select Fron class select from class ERROR at line 1: ORA-00942: table or vieu does not exist SQL> select fron student class; SSN CLASS CODE 172-32-1176 37 213-46-8915 32 267-1-239A 3 409-56-7008 37 427-17-2319 34 472-27-2349 32 486-29-1786 1 527-72-3246 1 6h8-92-1872 55 672-71-3249 3 12-55-1867 37 846-92-7186 32 998-72-3567 3 998-72-3567 55 14 rows selected

SQL> select From class; CLASS CODE CLASS DESCRIPTION 37 Database Progranming Introduction to Computers Introduction to C progranming Intro to principles Operating systems 32 34 14A 1 7 rows selected

SQL> select ssn,class_code 2 From class,student 3 where (ssn,class_code) not in (select ssn,class_code from student class); SSN CLASS_CODE 172-32-117ό 1 213-46-8915 1 238-95-7766 1 267-41-2394 1 409-5ό-7008 1 427-17-2319 1 472-27-2349 1 6418-92-1872 1 672-71-3249 1 712-45-1867 1 846-92-7186 1 998-72-35671 999-00-0000 1 172-32-1176 14A 213-46-8915 14A 238-95-7766 14A 267-41-2394 14A 409-56-7008 14A 427-17-2319 14A 472-27-2349 14A 48ό-29-178ό 14A 527-72-3246 14A 648-92-1872 14A 672-71-3249 14A 712-45-186714A 846-92-7186 14A 998-72-3567 14A 999-00 0000 14A 172-32-117ό 3 213-46-8915 3 238-95-7766 3 267-41-2394 3 409-5ó-7008 3 427-17-2319 3 472-27-2349 3 486-29-1786 3 527-72-324ό 3 648-92-1872 3 712-45-1867 3 84ό-92-7186 3 999-90-0009 3 172-32-1176 32 238-95-7766 32 267-11-230h 32

267-41-2394 32 409-56-7008 32 427-17-2319 32 48ό-29-178ό 32 527-72-3246 32 648-92-1872 32 672-71-3249 32 712-45-18ό7 32 998-72-3567 32 999-00- 0000 32 172-32-1176 34 213-46-8915 34 238-95-7766 34 409-56-7008 34 472-27-2349 34 48ό-29-178ό 34 527-72-3246 34 648-92-1872 34 672-71-3249 34 712-45-18ό7 34 846-92-7186 34 998-72-3567 34 999-0 0000 34 213-46-8915 37 238-95-7766 37 267-41-2394 37 427-17-2319 37 472-27-2349 37 486-29-1786 37 527-72-3246 37 648-92-1872 37 ό72-71-3249 37 846-92-7186 37 998-72-3567 37 999-00-0000 37 172-32-117ό 55 213-46-8915 55 238-95-7766 55 267-41-2394 55 409-5ό-7008 55 427- 17-2319 55 472-27-2349 55 486-29-1786 55 527-72-324ό 55 672-71-3249 55 712-45-1867 55 84ό-92-7186 55 999-00-0000 55 91 rows selected.

SQL> select NUL (class_description, 'No description available yet class_description,1name,ssn,cla SQL> 2 From class,student 3 where (ssn,class code) not in (select ssn,class code from student class); CLASS CODE CLASS DESCRIPTION LNAME SSN Database Progranming Database Programming Database Programming Database Programming Database Programming Database Programming Database Programming Database Programming Database Programming Database Programming Database Programming Database Programming Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to Computers Introduction to C programming Bennet Introduction to C programming Gren Introduction to C programming Greeenr Introduction to C programming Dull Introduction to C programming Yokonoto Introduction to C programming 0'Leary Introduction to C programming Greene Introduction to C programming Al Introduction to C programming White Introduction to C programming del Castillo Introduction to C programming Locksley Introduction to C programming Blotchet-Halls Green Gren Greeenr 213-46-8915 37 238-95-7766 37 998-72-3567 37 427- 17-2319 37 672-71-3249 37 267-41-2394 37 472-27-2349 37 527-72-3246 37 999-00- 0000 37 846-92-7186 37 486-29-1786 37 648-92-1872 37 409-56-7008 3 213-46-8915 3 238-95-7766 3 427-17-2319 3 267-41-2394 3 472-27-2349 3 527-72-3246 3 999- 00-0000 3 172-32-1176 3 712-45-1867 3 84ό-92-718ό 3 486-29-1786 3 648-92-1872 3 409-56-7008 32 238-95-7766 32 998-72-3567 32 427-17-2319 32 672-71-3249 32 267-41-2394 32 527-72-3246 32 999-00- 0000 32 172-32-1176 32 712-45-18ό7 32 486-29-1786 32 648-92-1872 32 Yokomoto 0 Leary Gringlesby Greene Hunter Locksley Blotchet-Halls Bennet Green Gren 0'Leary Gringlesby Greene White del Castillo Hunter Locksley Blotchet-Halls