Oracle Database Classic Exercises and Answers

Recently downloaded a set of Oracle database exercises (no answer), I practice the answer on Oracle, if the wrong place, please do not take it amiss. Save to the blog to facilitate their own later lookup.

Related tables:

/* Student table
/CREATE TABLE Student
(
    Sno varchar2 () primary key not NULL,
    sname varchar2 (),
    Sage Number (2),
    ssex varchar2 (5)
)/
 
* Teacher Table/
CREATE TABLE Teacher 
(
   TNO varchar2 () primary key,
   tname varchar2 ()
) 

/* Schedule
/CREATE TABLE course (CNO varchar2 (), CNAME varchar2 (), TNO varchar2 (
    20),
    constraint Pk_course primary KEY (Cno,tno)
) 

/* Results table
/CREATE TABLE SC 
(
    sno varchar2), CNO varchar2 (a), score number
    (4,2),
    Constraint PK_SC primary KEY (SNO,CNO)
)

Related data:

/* Initialize student table student*/INSERT into student values (' s001 ', ' John ', 23, ' Male ');
INSERT into student values (' s002 ', ' Dick ', 23, ' Male ');
INSERT into student values (' s003 ', ' Wu Peng ', 25, ' Male ');
INSERT into student values (' s004 ', ' Qin Qin ', 20, ' female ');
INSERT into student values (' s005 ', ' Wang Li ', 20, ' female ');
INSERT into student values (' s006 ', ' Appl ', 21, ' Male ');
INSERT into student values (' s007 ', ' Liu Yu ', 21, ' Male ');
INSERT into student values (' s008 ', ' Shang ', 21, ' female ');
INSERT into student values (' s009 ', ' Chen Yixiao ', 23, ' female ');

INSERT into student values (' s010 ', ' Vanessa ', 22, ' female ');
/* Initialize teacher table teacher */INSERT INTO teacher values (' t001 ', ' irrepressible Liu Yang ');
Insert into teacher values (' t002 ', ' Shanyan ');


Insert into teacher values (' t003 ', ' Humingshing '); 
/* Initialization Schedule course*/INSERT into course values (' c001 ', ' j2se ', ' t002 ');
Insert into course values (' c002 ', ' java web ', ' t002 ');
Insert into course values (' c003 ', ' SSH ', ' t001 ');
Insert into course values (' c004 ', ' Oracle ', ' t001 ');
Insert into course values (' c005 ', ' sql server 2005 ', ' t003 '); Insert into course values (' c006 ', ' C #', ' t003 ');
Insert into course values (' c007 ', ' JavaScript ', ' t002 ');
Insert into course values (' c008 ', ' div+css ', ' t001 ');
Insert into course values (' c009 ', ' PHP ', ' t003 ');


Insert into course values (' c010 ', ' EJB3.0 ', ' t002 ');
/* Initialization score table sc*/INSERT into SC values (' s001 ', ' c001 ', 78.9);
INSERT into SC values (' s002 ', ' c001 ', 80.9);
INSERT into SC values (' s003 ', ' c001 ', 81.9);
INSERT into SC values (' s004 ', ' c001 ', 60.9);
INSERT into SC values (' s001 ', ' c002 ', 82.9); 

INSERT into SC values (' s002 ', ' c002 ', 72.9);
INSERT into SC values (' s005 ', ' c003 ', 78.9);
INSERT into SC values (' s006 ', ' c004 ', 50.9);
INSERT into SC values (' s007 ', ' c005 ', 81.9);
INSERT into SC values (' s008 ', ' c006 ', 50.9);
INSERT into SC values (' s005 ', ' c007 ', 42.9); 
INSERT into SC values (' s006 ', ' c008 ', 72.9);
INSERT into SC values (' s005 ', ' c009 ', 52.9);

 INSERT into SC values (' s006 ', ' c010 ', 92.9);

Exercises:

/*1, query "c001" course is higher than the "c002" course of all students of the school number;*/  select distinct C.sno from SC C where (select A.score from SC a where A.sno = C.sno and a.cno = ' c001 ') > (select A.score from SC a where A.sno = C.sno and a.cno = ' c002 ')   /* 2, query average score greater than 60 students ' school number and average score;*/  select * FROM (Select a . Sno, AVG (score) Avgscore from SC a GROUP by A.sno) where Avgscore >  /*3, check all students ' School number, name, course number, total score;  */&nbs
P
                    Select C.sno, C.sname, NVL (d.coursenum, 0), NVL (D.totalscore, 0) from student C-left join (select A.sno, A.sname, Count (b.sno) coursenum, sum (b.score) Totalscore fro M student A, SC b where A.sno = B.sno GROUP by A.sno, A.sname) d on c.sno = D.sno &nbsp
; * 4, query surname "Liu" the number of teachers; */  select COUNT (*) from teacher a where a.tname like ' Liu% '/* 5, the query did not learn the "Shanyan" teacher class student number, name; &nbs p;*/  Select B. Sno, E.sname from SC B left join student e on b.sno = E.sno where B.score <-b.cno in (select C.cno from course c WHERE C.tno = (select D.tno from teacher D where d.tname = ' Shanyan '))  /*6, query science 
         "C001" and has also learned the number of "c002" class student number, name; */select C.sno, D.sname from (select B.sno Sno, COUNT (*) Numcount from SC b where B.cno in (' c001 ', ' c002 ') group by Sno) C left JOIN student D on c.sno = D.sno where c.num  Count = 2    /*7, query learn number and name of all classes taught by "Shanyan" teacher, * *   Select A.sno, a.sname from student a where a.sno
                                    In (select D.sno to SC D where d.cno in (select C.cno)
                                     From teacher B, course c where B.tno = C.tno

and b.tname = ' Shanyan '));   /*8, inquiry course number "c002" score than the course number "c001" course of all students of the school number, name; *   Select A.sno, a.sname from student a where a.sno In (select B.sno from SC B, sc c where b.cno = ' c001 ' and c.cno
= ' c002 ' and B.sno = C.sno and B.score > C.score);    /*9, inquires the student number, the name of all the course score less than 60 points  */     Select A.sno, a.sname from Student a W


Here A.sno in (select B.sno from SC b where b.score < 60); /*10, inquires the student number, name; */     Select A.sno of all classes, a.sname from student a where a.sno in (select C.sno from (select B.sno, Count (b.cno) Cno_num from student A, SC b where a.s No = B.sno GROUP by B.sno) C where C.cno_num < (select count (D.CNO) from Course D)) or a.

Sno Not in (SELECT DISTINCT E.sno from SC e);
/*11, inquires at least one course and the student number is "s001" schoolmate learns the same schoolmate the student number and the name; * *   SELECT * FROM student;
SELECT * from teacher;
SELECT * from course; SELECT * from sc;    /*12, query at least learn number for "s001" classmate AllThe other students of a class number and name;    13, the "SC" table in the "Shanyan" Teacher's class results are changed to the average grade of the course;   14, Query and "s001" Students learn the same course with the same number of other students and names; 15, delete learning "Shanyan" Teacher class SC Table Records;       16, to the SC table insert some records, These records require the following conditions: No numbered "c002" course of students, "c002" the average score of the        course;        17, inquires the highest and lowest points in each section: the course ID, the highest score, the lowest score      18, according to the average grade of each section from low to high and the percentage of the passing rate from high to low        19, inquires the different teacher to teach different courses average score from high to low show     20, statistical printing of the results of various subjects, each score segment number: Course ID, course name, [100-85],[85-70 ],[70-60],[ <60]    21, check the records of the top three of the scores of the subjects: (regardless of the performance of the situation)       22, Check the number of students selected for each course     23, the query out of only one course of all students to learn number and name     24, query male and female number     25, query last name "Zhang" student list      26, query the same name of same-sex students list, and statistics of the same name     27, 1981-Born students list ( Note: The type of sage column in student table is number)      28, the average score of each course is queried, the results are ranked in ascending order of average score, and the average score is the same, in descending order by course number       29, query the average score is greater than 85 of all students ' School number, name and average grade     30, inquiry courseName and score of students with a "database" with a score of less than 60     31, check all students ' elective;    32, inquire about the name, course title and score of any course score above 70 points;     33, check the failing courses, and according to the course number from large to small ranked     34, the inquiry course number is c001 and the course score of 80 points above the student's number and name;     35, the number of students who have chosen the course   37, inquires the various courses and the corresponding elective number     38, inquires the student number, course number, student achievement     39 of the students with the same grade of different courses. Check the top two of the best results     40, the number of students enrolled in each course (more than 10 courses are counted). Request the output of course number and elective number, the results are in descending order, if the number is the same, in ascending order by course number      41, to retrieve at least two courses of student number     42, Check the course number and course name     43 of all students elective courses, and inquire about the student name     44 of any course taught by the "Shanyan" teacher, and the number of students who inquired for two or more failing courses and their average score
     45, retrieve "c004" course score is less than 60, by the score descending order of classmate number     46, delete "s002" classmate's "c001" Course results  */