A set of Oracle SQL exercises and answers

Create table student (
Sno varchar2 (10) primary key,
Sname varchar2 (20 ),
Sage number (2 ),
Ssex varchar2 (5)
);
Create table teacher (
Tno varchar2 (10) primary key,
Tname varchar2 (20)
);
Create table course (
Cno varchar2 (10 ),
Cname varchar2 (20 ),
Tno varchar2 (20 ),
Constraint pk_course primary key (cno, tno)
);
Create table SC (
Sno varchar2 (10 ),
Cno varchar2 (10 ),
Score number (4, 2 ),
Constraint pk_ SC primary key (sno, cno)
);
/******* Initialize the data in the student table ******/
Insert into student values ('s001 ', 'zhang san', 23, 'male ');
Insert into student values ('s002 ', 'lily', 23, 'male ');
Insert into student values ('s003 ', 'wu peng', 25, 'male ');
Insert into student values ('s004 ', 'qinqin', 20, 'female ');
Insert into student values ('s005 ', 'lily', 20, 'female ');
Insert into student values ('s006 ', 'ripbo', 21, 'male ');
Insert into student values ('s007 ', 'Liu Yu', 21, 'male ');
Insert into student values ('s008 ', 'xiao Rong', 21, 'female ');
Insert into student values ('s009', 'chen Xiaoxiao ', 23, 'female ');
Insert into student values ('s010 ', 'chen mei', 22, 'female ');
Commit;
/******************* Initialize the instructor table ****************** *****/
Insert into teacher values ('t001', 'liuyang ');
Insert into teacher values ('t002 ', 'xiaoyan ');
Insert into teacher values ('t003 ', 'hu start ');
Commit;
********************** ******/
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 100', '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 ');
Commit;
********************** */
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 ('s003 ', 'c002', 81.9 );
Insert into SC values ('s001 ', 'c003', '59 ');
Commit;
 
 
Exercise:
Note: The data in the following exercises is an SQL statement written based on the data initialized to the database. Please note that.
 
 
1. query the student IDs of all students whose "c001" courses are higher than "c002;
2. query the student ID and average score of students whose average score is greater than 60;
3. query the student ID, name, number of course selections, and total score of all students;
4. query the number of instructors surnamed "Liu;
5. query the student ID and name of the student who has not learned the course "Yan;
6. query the student ID and name of the student who has learned "c001" and has also learned the "c002" course;
7. query the student ID and name of all students who have learned the courses taught by instructor Yan;
8. query the student ID and name of all students whose score is lower than the course number c001;
9. query the student ID and name of all students whose course scores are less than 60;
10. query the student ID and name of the student who has not completed all the courses;
11. query the student ID and name of at least one course with the student ID "s001;
12. query the student ID and name of at least one student whose student ID is "s001;
13. Change the average score of the Course taught by Yan in the SC table;
14. query the student ID and name of other students whose courses are identical to those of s001;
15. Delete the SC table record for learning the course "Yan;
16. insert some records into the SC table. These records must meet the following requirements: average scores of students whose student ID is no longer "c002" and whose student ID is "c002;
17. query the highest score and lowest score of each subject: displayed as follows: course ID, highest score, lowest score
18. The average score of each subject ranges from high to high and the pass rate.
19. query the average scores of different courses taught by different teachers from high to low.
20. Print the score of each subject in Statistics. Number of students in each score segment: course ID, course name, [100-85], [85-70], [70-60], [<60]
21. query the records of the top three scores in each subject: (parallel scores are not considered)
22. query the number of students selected for each course
23. Check the student ID and name of all students who have selected only one course.
24. query the number of boys and girls
25. query the student list with the last name "Zhang"
26. query the list of same-name students and count the number of students with the same name
27. List of students born in 1981 (note: the type of the Sage column in Student table is number)
28. query the average scores of each course. The results are sorted in ascending order based on the average scores. The average scores are the same and the courses are sorted in descending order based on the course number.
29. query the student ID, name, and average score of all students whose average score is greater than 85
30. query the names and scores of students whose course names are "databases" and whose scores are less than 60.
31. query the Course selections of all students;
32. query the name, course name, and score of any course whose score is over 70;
33. query failed courses and arrange them in ascending order by course number
34. query the student ID and name of the student whose course number is c001 and whose score is higher than 80;
35. Number of students selected for the course
36. query the names of the students with the highest scores and their scores of the students who take the course "Yan ".
37. query each course and the number of optional students
38. query the student ID, course number, and score of the same student with different course scores
39. query the first two of the best scores for each course
40. count the number of optional students in each course (only when the number of students exceeds 10 ). The course number and the number of electives are required to be output. The query results are sorted in descending order of the number of students. If the number of students is the same, they are listed in ascending order.
41. Retrieve student IDs that take at least two courses
42. query the course number and name of all optional courses for all students
43. query the names of students who have not learned any course taught by instructor Yan.
44. query the student ID and average score of two or more failed courses
45. Search for students whose scores are less than 60 in the "c004" course in descending order
46. Delete the score of "c001" course of "s002"
 
 
 
Answer:
 
1.
*********************************
Select a. * from
(Select * from SC a where a. cno = 'c001'),
(Select * from SC B where B. cno = 'c002 ') B
Where a. sno = B. sno and a. score> B. score;
*********************************
Select * from SC
Where a. cno = 'c001'
And exists (select * from SC B where B. cno = 'c002' and a. score> B. score
And a. sno = B. sno)
*********************************
2.
*********************************
Select sno, avg (score) from SC group by sno having avg (score)> 60;
*********************************
3.
*********************************
Select a. *, s. sname from (select sno, sum (score), count (cno) from SC group by sno) a, student s where a. sno = s. sno
*********************************
4.
*********************************
Select count (*) from teacher where tname like 'Liu % ';
*********************************
5.
*********************************
Select a. sno, a. sname from student
Where a. sno
Not in
(Select distinct s. sno
From SC s,
(Select c .*
From course c,
(Select tno
From teacher t
Where tname = 'xiaoyan ') t
Where c. tno = t. tno) B
Where s. cno = B. cno)
*********************************
Select * from student st where st. sno not in
(Select distinct sno from SC s join course c on s. cno = c. cno
Join teacher t on c. tno = t. tno where tname = 'xiaoyan ')
*********************************
6.
*********************************
Select st. * from SC
Join SC B on a. sno = B. sno
Join student st
On st. sno = a. sno
Where a. cno = 'c001' and B. cno = 'c002' and st. sno = a. sno;
*********************************
7.
*********************************
Select st. * from student st join SC s on st. sno = s. sno
Join course c on s. cno = c. cno
Join teacher t on c. tno = t. tno
Where t. tname = 'xiaoyan'
*********************************
8.
*********************************
Select * from student st
Join SC a on st. sno = a. sno
Join SC B on st. sno = B. sno
Where a. cno = 'c002' and B. cno = 'c001' and a. score <B. score
*********************************
9.
*********************************
Select st. *, s. score from student st
Join SC s on st. sno = s. sno
Join course c on s. cno = c. cno
Where s. score <60
*********************************
10.
*********************************
Select stu. sno, stu. sname, count (SC. cno) from student stu
Left join SC on stu. sno = SC. sno
Group by stu. sno, stu. sname
Having count (SC. cno) <(select count (distinct cno) from course)
==========================================
Select * from student where sno in
(Select sno from
(Select stu. sno, c. cno from student stu
Cross join course c
Minus
Select sno, cno from SC)
)
==========================================
*********************************
11.
*********************************
Select st. * from student st,
(Select distinct a. sno from
(Select * from SC),
(Select * from SC where SC. sno = 's001') B
Where a. cno = B. cno) h
Where st. sno = h. sno and st. sno <> 's001'
*********************************
12.
*********************************
Select * from SC
Left join student st
On st. sno = SC. sno
Where SC. sno <> 's001'
And SC. cno in
(Select cno from SC
Where sno = 's001 ')
*********************************
13.
*********************************
Update SC c set score = (select avg (c. score) from course a, teacher B
Where a. tno = B. tno
And B. tname = 'xiaoyan'
And a. cno = c. cno
Group by c. cno)
Where cno in (
Select cno from course a, teacher B
Where a. tno = B. tno
And B. tname = 'xiaoyan ')
*********************************
14.
*********************************
Select * from SC where sno <> 's001'
Minus
(
Select * from SC
Minus
Select * from SC where sno = 's001'
)
*********************************
15.
*********************************
Delete from SC
Where SC. cno in
(
Select cno from course c
Left join teacher t on c. tno = t. tno
Where t. tname = 'xiaoyan'
)
*********************************
16.
*********************************
Insert into SC (sno, cno, score)
Select distinct st. sno, SC. cno, (select avg (score) from SC where cno = 'c002 ')
From student st, SC
Where not exists
(Select * from SC where cno = 'c002' and SC. sno = st. sno) and SC. cno = 'c002 ';
*********************************
17.
*********************************
Select cno, max (score), min (score) from SC group by cno;
*********************************
18.
*********************************
Select cno, avg (score), sum (case when score> = 60 then 1 else 0 end)/count (*)
As pass rate
From SC group by cno
Order by avg (score), pass rate desc
*********************************
19.
*********************************
Select max (t. tno), max (t. tname), max (c. cno), max (c. cname), c. cno, avg (score) from SC, course c, teacher t
Where SC. cno = c. cno and c. tno = t. tno
Group by c. cno
Order by avg (score) desc
*********************************
20.
*********************************
Select SC. cno, c. cname,
Sum (case when score between 85 and 100 then 1 else 0 end) AS "[100-85]",
Sum (case when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
Sum (case when score between 60 and 70 then 1 else 0 end) AS "[70-60]",
Sum (case when score <60 then 1 else 0 end) AS "[<60]"
From SC, course c
Where SC. cno = c. cno
Group by SC. cno, c. cname;
*********************************
21.
*********************************
Select * from
(Select sno, cno, score, row_number () over (partition by cno order by score desc) rn from SC)
Where rn <4
*********************************
22.
*********************************
Select cno, count (sno) from SC group by cno;
*********************************
23.
*********************************
Select SC. sno, st. sname, count (cno) from student st
Left join SC
On SC. sno = st. sno
Group by st. sname, SC. sno having count (cno) = 1;
*********************************
24.
*********************************
Select ssex, count (*) from student group by ssex;
*********************************
25.
*********************************
Select * from student where sname like 'sheet % ';
*********************************
26.
*********************************
Select sname, count (*) from student group by sname having count (*)> 1;
*********************************
27.
*********************************
Select sno, sname, sage, ssex from student t where to_char (sysdate, 'yyyy')-sage = 1988
*********************************
28.
*********************************
Select cno, avg (score) from SC group by cno order by avg (score) asc, cno desc;
*********************************
29.
*********************************
Select st. sno, st. sname, avg (score) from student st
Left join SC
On SC. sno = st. sno
Group by st. sno, st. sname having avg (score)> 85;
*********************************
30.
*********************************
Select sname, score from student st, SC, course c
Where st. sno = SC. sno and SC. cno = c. cno and c. cname = 'oracle 'and SC. score <60
*********************************
31.
*********************************
Select st. sno, st. sname, c. cname from student st, SC, course c
Where SC. sno = st. sno and SC. cno = c. cno;
*********************************
32.
*********************************
Select st. sname, c. cname, SC. score from student st, SC, course c
Where SC. sno = st. sno and SC. cno = c. cno and SC. score> 70
*********************************
33.
*********************************
Select SC. sno, c. cname, SC. score from SC, course c
Where SC. cno = c. cno and SC. score <60 order by SC. cno desc;
*********************************
34.
*********************************
Select st. sno, st. sname, SC. score from SC, student st
Where SC. sno = st. sno and cno = 'c001' and score> 80;
*********************************
35.
*********************************
Select count (distinct sno) from SC;
*********************************
36.
*********************************
Select st. sname, score from student st, SC, course c, teacher t
Where
St. sno = SC. sno and SC. cno = c. cno and c. tno = t. tno
And t. tname = 'xiaoyan 'and SC. score =
(Select max (score) from SC where SC. cno = c. cno)
*********************************
37.
*********************************
Select cno, count (sno) from SC group by cno;
*********************************
38.
*********************************
Select a. * from SC a, SC B where a. score = B. score and a. cno <> B. cno
*********************************
39.
*********************************
Select * from (
Select sno, cno, score, row_number () over (partition by cno order by score desc) my_rn from SC t
)
Where my_rn <= 2
*********************************
40.
*********************************
Select cno, count (sno) from SC group by cno
Having count (sno)> 10
Order by count (sno) desc, cno asc;
*********************************
41.
*********************************
Select sno from SC group by sno having count (cno)> 1;
|
Select sno from SC group by sno having count (sno)> 1;
*********************************
42.
*********************************
Select distinct (c. cno), c. cname from course c, SC
Where SC. cno = c. cno
|
Select cno, cname from course c
Where c. cno in
(Select cno from SC group by cno)
*********************************
43.
*********************************
Select st. sname from student st
Where st. sno not in
(Select distinct SC. sno from SC, course c, teacher t
Where SC. cno = c. cno and c. tno = t. tno and t. tname = 'xiaoyan ')
*********************************
44.
*********************************
Select sno, avg (score) from SC
Where sno in
(Select sno from SC where SC. score <60
Group by sno having count (sno)> 1
) Group by sno
*********************************
45.
*********************************
Select sno from SC where cno = 'c004 'and score <90 order by score desc;
*********************************
46.
*********************************
Delete from SC where sno = 's002 'and cno = 'c001 ';
*********************************

Author solookin