[Solved]-Use Single Sql Statement Following Questions 1 Give Listing Ssns First Names Class Descrip Q37275266

Use only a single SQL statement for each of thefollowing questions

1. Give a listing of all the ssns,first namesand the class descriptions of all the classes the students aretaking. If there are no class_descriptions display ‘No descriptionis available yet’. (USE NVL)

2. Give a listing of only the lname and theclass_code for students who are taking ‘Introduction to Cprogramming’. (Inner join)

3. Give a listing of all the class_descriptionsand the number of students enrolled in each class for all studentswho are older than the average age where the total number ofstudents for the class is more than 1 student. Order by the numberof students. If there is no class description replace it with’Other Classes’ (Note: Take it in steps. First do all those who areolder than the average age, then do the group by, then add thehaving clause and then the order and then combine everythingtogether)

4. Give a listing of all the classes for whichno students are enrolled in (use in or not in clause)(subquery)
  
5. Give a listing of all the students who are notenrolled in any classes (Note: Use Exists or not Exists)
  
6. create a new table that contains the list ofall the students and class_descriptions. Include In this table thelist of all students who are not enrolled in any classes (displayno classes). If there are no class descriptions then display ‘nodescription’ (Use combination of inner join, union and minus)(Note: minus will deal with the students who are not enrolled inany classes)
  
7. repeat question 6 using a combination of innerjoin, union and not exists (Note: Not exists will deal with thestudents who are not enrolled in any classes)
  
8. create a view. We want to find out whichcourses are being taken by the different students for all thosewhose age is greater than the average age. Give a listing of thecourse descriptions and student names (Inner join)

  
9. We want to find out the courses that eachstudent is not enrolled in. Give a listing of the coursedescriptions, and the students (lname) who are not taking thatspecific course (Use a cartesian product and union it with aminus)

10 . Use the system catalog tables to display theresults to find out the following:(Note show me the SQL syntaxalong with your results) Only a single SQL statement for eachquestion.
   a) Primary key name and the columnsthat make up the primary key for student table
   b) Unique key name and the columnsthat make up the unique key for the student table
   c) Foreign key name, the columns thatmake up the foreign key and the columns it references in the parenttable for student_class table
   d) Name of all the check constraintsand their conditions for the student table

Data Table

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 primarykey(ssn,class_code);
alter table class add constraint clss_pk primarykey(class_code);
alter table student_class add constraint st_fk foreign key (ssn)references student;
alter table student add constraint student_ukunique(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’);

Expert Answer

---

Answer to Use only a single SQL statement for each of the following questions 1. Give a listing of all the ssns,first names and th… . . .