SQL statements in Oracle Classic exercises

Create a student table

Create a score table

Create a teacher table

Create a grade Table

--1, Query the sname, Ssex, and class columns of all records in the student table. SELECT S.sname, S.ssex, S.classFrom student S--2, query teachers all units that are not duplicated depart column. SELECT DISTINCT depart from teacher--3, querying all records of the student table. SELECT*From student--4, query all records in the score table with scores from 60 to 80. SELECT* FROM score SC WHERE sc.degree between -and the--5, query the records in the score table for grades 85, 86, or 88. SELECT* FROM score SC WHERE Sc.degree in ( -, the, the)--6, Query the Student table "95031"class or gender" is a "female" student record. SELECT* FROM student s WHERE s.ssex='female'OR S.class='95031'--7, querying all records of the student table in descending order of class. SELECT* FROM student s ORDER by S.classDESC--8, CNO in ascending order, degree in descending order to query all records of the score table. SELECT*From score SC ORDER by SC.CNO, degree DESC--9Query95031The number of students in the class. SELECT COUNT (*) from student S WHERE S.class='95031'--Ten, query the student number and course number of the highest score in the score table. SELECT* FROM score SC WHERE sc.degree=(SELECT MAX (degree) from score)-- All-- OneQuery3- theThe average score of the course. SELECT Sc.cno, AVG (sc.degree) from score SC WHERE sc.cno='3-105'GROUP by Sc.cno-- A, the average score of a course that has at least 5 students enrolled in the score table and starts with 3. SELECT Sc.cno, AVG (sc.degree) from score SC WHERE Sc.cno like'3%'GROUP by Sc.cno have COUNT (SC.CNO) >=5-- -, the query minimum score is greater than 70, the highest score is less than 90 sno column. SELECT* FROM score SC WHERE sc.degree between -and --- -, query all students for Sname, CNO, and degree columns. SELECT S.sname, Sc.cno, sc.degree from student S left joins score SC on S.sno=Sc.snoselect s.sname, Sc.cno, sc.degree from student S, score SC WHERE S.sno=Sc.sno-- the, query all students for SNO, CNAME, and degree columns. Select S.sno, (select C.cname from course C WHERE c.cno=SC.CNO) CNAME, sc.degree from student S left joins score SC on s.sno=Sc.sno-- -, query all students for sname, CNAME, and degree columns. Select S.sname, (select C.cname from course C WHERE c.cno=SC.CNO) CNAME, sc.degree from student S left joins score SC on s.sno=Sc.sno-- -Query95033"The average of the courses selected by the class. SELECT SC. CNO, AVG (SC. degree) from score SC WHERE SC. SNO in (SELECT SNO from STUDENT S WHERE s.class='95033') GROUP by SC. CNO-- -, and now query all students Sno, CNO and rank column. SELECT SC. SNO, SC. CNO, G.rank from score SC left joins GRADE G on SC. degree between G.low and G.upp-- +, inquiry elective "3- the"Course performance is higher than"109"The record of all the students in the student's grade. SELECT*From STUDENT S WHERE s.sno in (SELECT SC. SNO from score SC WHERE SC. CNO='3-105'and SC. Degree>All (SELECT degree from score SC1 WHERE SC1. SNO=109))
--20, query score the students who have chosen more than one course of study are the records of non-highest scores.

SELECT R.sno, R.degree
From Score R
WHERE R.degree not in (SELECT MAX (r.degree) from score R GROUP by R.sno)
and R.sno in
(SELECT R.sno from score R GROUP by R.sno have COUNT (R.sno) > 1)

-- +, the query score is higher than the school number as "109", Course number"3- the"All records of the scores. SELECT*From score SC1 WHERE SC1. Degree>Any (SELECT SC. Degree from score SC WHERE SC. SNO=109and SC. CNO='3-105') ORDER by Sc1.sno/*the difference between Any,all,some*/-- A, inquiry and study number 108 students of the same year were born of all students Sno, sname and Sbirthday column. SELECT*From STUDENT S WHERE to_char (S.sbirthday,'yyyy') =(SELECT to_char (S1. Sbirthday,'yyyy') from STUDENT S1 WHERE S1. SNO='108') and S.sno!='108'-- at, query "Zhang Xu" teachers to teach students results. SELECT SC.*From COURSE C left JOIN TEACHER T on C.tno=T.tno left joins score SC on SC. CNO=C.cno and T.tname='Zhang Xu'-- -, the name of the teacher who has more than 5 students who have enrolled in a course. SELECT t.tname from COURSE C left joins TEACHER T on C.tno=T.tno left joins score SC on SC. CNO=c.cno GROUP by t.tnamehaving COUNT (*) >5-- -, check the records of 95033 classes and 95031 classes of all students. SELECT* FROM student s WHERE S.classIn (95033,95031)-- -, there are more than 85 grades of courses Cno.select* FROM score SC WHERE sc.degree > --- -, and the results of the "computer department" Teachers ' teaching courses were queried. SELECT*From score SC WHERE SC. CNO in (SELECT c.cno from COURSE C, TEACHER T WHERE c.tno=T.tno and T.depart='Computer Department')-- -, and inquires into the tname and prof of teachers with different titles of "computer department" and "Electronic Engineering department". SELECT T.tname, t.prof from TEACHER T WHERE depart='Computer Department'and PROF not in (SELECT PROF from TEACHER WHERE depart='Department of Electronic Engineering');-- in, the query elective number is "3- the"And the result is at least higher than the elective number."3-245The classmates ' cno,sno and degree are sorted by degree from high to low. SELECT*From score WHERE degree> Any (SELECT degree from score WHERE CNO ='3-245') ORDER by degree DESC;-- -, the query elective number is "3- the"And the score is higher than the elective number."3-245"The CNO, Sno and Degree.select of the classmates* from score where degree > all (SELECT degree from score where cno='3-245') ORDER by degree DESC;-- to, check the name, sex and birthday of all teachers and classmates. SELECT T.tname, T.tsex, t.tbirthday from TEACHER tunion allselect s.sname, S.ssex, s.sbirthday from STUDENT S-- +, query the name, sex and birthday of all "female" teachers and "female" classmates. SELECT*From (select T.tname, T.tsex, t.tbirthday to TEACHER T UNION all SELECT s.sname, S.ssex, S. Sbirthday from STUDENT S) R WHERE r.tsex='female'-- -, and the results of students who have a lower average score than the course. SELECT*From score SC1, (SELECT SC. CNO, AVG (SC. degree) AVD from the score sc GROUP by SC. CNO) R WHERE SC1. CNO=R.cno and SC1. Degree<R.AVD-- the, query all instructor's tname and Depart.select T.tname, t.depart from teacher T-- *, the Tname and depart of all teachers who have not been lectured. SELECT*From TEACHER T where T.tno not in (select C.tno from COURSE C WHERE c.cno in (select SC. CNO from score SC))-- $, check the class number of at least 2 men. SELECT S.classFrom student s GROUP by S.classHaving COUNT (*) >=2--Panax Notoginseng, query the student table, not the surname "Wang" student records. SELECT* FROM student s WHERE S.sname isn't like'Wang%'-- -, query the name and age of each student in the student table. SELECT S.sname, ROUND (sysdate-s.sbirthday)/365) age from student Sselect S.sname, To_char (Sysdate,'yyyy')-To_char (S.sbirthday,'yyyy') from student s-- the, querying the largest and smallest sbirthday date values in the student table. SELECT MAX (S.sbirthday), MIN (S.sbirthday) from student s-- +, querying all records in the student table in the order of class number and age from large to small. SELECT* FROM student s ORDER by S.classDESC, S.sbirthday-- A, query "male" teachers and their courses. SELECT* From course C, teacher T WHERE C.tno=t.tno and t.tsex='male'-- the, Query the SNO, CNO and degree columns of the highest score. SELECT S.sno, SC. CNO, SC. degree from STUDENT S, score SC WHERE S.sno=SC. SNO and SC. Degree=(SELECT MAX (degree) from score)-- +, inquiries and "Li June" the same sex with all the students of the Sname.select*From STUDENT S WHERE s.sname!='Li June'and S.ssex= (SELECT DISTINCT SSEX from STUDENT WHERE SNAME ='Li June')-- -, inquiries and "Li June" with the same sex and classmates sname.select*From STUDENT S WHERE s.sname!='Li June'and S.ssex= (SELECT DISTINCT SSEX from STUDENT WHERE SNAME ='Li June') and S.class= (SELECT DISTINCT CLASS from STUDENT WHERE SNAME ='Li June')--45, check all the "male" students of the course "Introduction to Computer", select S.sname, S.ssex, SC.*From score SC, COURSE C, STUDENT S WHERE SC. CNO=C.cno and S.sno=SC. SNO and C.cname='Introduction to Computers'and S.ssex='male'

SQL statements in Oracle Classic exercises