CREATE TABLE Student (
Sno Int PRIMARY KEY,
Sname Char (20),
Sex char (2),
Birthday datetime,
class int
)
CREATE TABLE Teacher (
TNO Int PRIMARY KEY,
Tname Char (20),
Sex char (2),
Birthday datetime,
Prof Char (10),
Depart Char (20)
)
CREATE TABLE Course (
CNO char (primary key),
CNAME char (20),
TNO int foreign key references teacher (TNO)
)
CREATE TABLE Score (
Sno int foreign key references student (SNO),
CNO char (Foreign) key references course (CNO),
degree int
)
INSERT into student values (108, ' Zeng Hua ', ' Male ', ' 09/01/1977 ', 95033);
INSERT into student values (105, ' Kuanming ', ' Male ', ' 10/02/1975 ', 95031);
INSERT into student values (107, ' Wang Li ', ' female ', ' 01/23/1976 ', 95033);
INSERT into student values (101, ' Li June ', ' male ', ' 02/20/1976 ', 95033);
INSERT into student values (109, ' Wang Fang ', ' female ', ' 02/10/1975 ', 95031);
INSERT into student values (103, ' army ', ' Male ', ' 06/03/1974 ', 95031); insert into teacher values (804, ' sung ', ' Male ', ' 12/02/1958 ', ' Associate Professor ', ' computer Department '); Insert into teacher values (856, ' Li Xu ', ' Male ', ' 03/12/1969 ', ' lecturer ', ' Electronic Engineering Department '); Insert into teacher values (825, ' Wang Ping ', ' female ', ' 05/05/1972 ', ' ta ', ' computer Department ');
Insert into teacher values (831, ' Liu Bing ', ' female ', ' 08/14/1977 ', ' ta ', ' Electronic Engineering Department ');
Insert into course values (' 3-105 ', ' Introduction to Computers ', 825); Insert into course values (' 3-245 ', ' operating system ', 804); Insert into course values (' 6-166 ', ' digital circuit ', 856); Insert into course values (' 9-888 ', ' Advanced math ', 825);
INSERT into score values (103, ' 3-245 ', 86);
INSERT into score values (109, ' 3-245 ', 68); INSERT into score values (105, ' 3-245 ', 75); INSERT into score values (103, ' 3-105 ', 92);
INSERT into score values (105, ' 3-105 ', 88);
INSERT into score values (109, ' 3-105 ', 76); INSERT into score values (101, ' 3-105 ', 64); INSERT into score values (107, ' 3-105 ', 91); INSERT into score values (108, ' 3-105 ', 78); INSERT into score values (101, ' 6-166 ', 85); INSERT into score values (107, ' 6-166 ', 79); INSERT into score values (108, ' 6-166 ', 81); DROP TABLE Student
drop TABLE Teacher
DROP TABLE Course
DROP TABLE Score
--1, lists all records in the student table.
--sname, sex, and class columns.
Select Sname,sex,class from Student
--2, shows the teacher all the units that are not duplicated depart column. --select distinct depart from teacher
--3, displays all records of the student table. --select * FROM student--4. Displays all records from 60 to 80 in the score table
--select * from score where degree>=60 and degree<=80--5. Displays all records for score in 85,86,88 table
--select * from score where degree in (85,86,88)--6. Show table the records of the students in the mid-shift or sex for the female
--select *from student where class= ' 95033 ' and sex= ' women '--7. Display all records in a table in descending order of class
--select * FROM Student order by class desc--8. Display all records in the table in CNO ascending degree descending order
--select * FROM Student ORDER by CNO Asc,degree desc--9. Number of students showing the class
--select Count (*), COUNT (Sno) from student where class= ' 95033 '--10. Show the highest score in the score table student number and course number
--select Max (degree), Nim (degree), AVG (degree) from score
--select * FROM score where degree= (select Max (degree) from score)--11. Show "3-105" The average score of the course--12. Shows the average number of course numbers that have at least 5 students enrolled in the score table and begin with 3
--select Cno,count (*), AVG (degree) from score where CNO as ' 3% ' GROUP by CNO have Count (CNO) >=5--13. Show minimum score greater than 70, maximum score less than 9 0 of Sno Columns
--select Sno from score GROUP by SNO have Max (degree) <90 and min (degree) >70
--select Sno from score where MAX (degree) <90 and min (degree) >70--14. Show Sname, CNO, and degree columns for all students
--select sname, CNO, degree from score,student where Student.sno=score.sno
--select sname, CNO, degree from score joins student on student.sno=score.sno--15. Show Sname,cname and degree for all students
--select Sname,cname,degree from Student,score,course where Student.sno=score.sno and Score.cno=course.cno
--select Sname,cname,degree from student joins score on Student.sno=score.sno joins course on score.cno=course.cno--16. List "9 5033 "average of selected courses
--select avg (degree) from score where Sno on (select Sno from student where class= ' 95033 ')--17. Show elective "3-105" course scores higher than the "109" number of students Record of all the students in the performance
--select * FROM score where degree> (select degree from score where sno=109 and cno= ' 3-105 ') and CNO = ' 3-105 '--18. Show Scor E A record of a non-highest score in a student who has a number of elective courses select * from score where Sno in (select Sno from Score GROUP by Sno have Count (Sno) >1) and Degree not in (select Max (degree) from score Group by CNO)--20. The Sno, sname, birthday of all students born in the same year as the student number "108"
--select Sno,sname,birthday from student where day (birthday) = (select Day (birthday) from student where sno=108)--21. Show "Zhang Xu "The students ' grades in the teacher's class
--select * FROM score where cno= (select CNO form course where tno= (select TNO from teacher where tname= ' XXX '))
--select tracher.*, course.*, score.* from Teacher,course,score where Teacher.tno=course.tno and Course.cno=score.cno and Tname= ' XXX '
--select tracher.*, course.*, score.* from teacher join course on Teacher.tno=course.tno join score on course.cno=score.cn o where tname= ' XXX '
--22. Shows the name of the teacher who has an extra 5 students in a course
--select tname from teacher where TNO in (select Tno from Course where CNO in (select CNO from Score GROUP by CNO has Cou NT (*) >=5))--23. Show records of all students in "95033" and "95031" classes
--select * FROM student where class = ' 95033 ' or class= ' 95031 '--24. Show course numbers with 85 + scores
--select Distindt CNO from score where degree>85
--the same display once--25. Show the "computer department" the teacher's teaching schedule
--select * from score where CNO in (select CNO from Course where TNO in (select TNO from teacher where depart = ' Computer Department ')--27. Show CNO, Sno, and degree for students with a minimum of 3-245 courses in the course numbered "3-105" and ranked in degree from highest to lowest
--select Cno,sno,degree from score where cno= ' 3-105 ' and Degree>any (select degree from C=score where cno= ' 2_245 ')
--select Cno,sno,degree from score where cno= ' 3-105 ' and degree> (select min (degree) from C=score where cno= ' 2_245 ')--28 .
--29, List the tname and depart of all classroom teachers
--select Tname,depart from teacher where TNO on (select TNO from Course)--30, list all non-lecture teachers tname and depart
--select Tname,depart from teacher where TNO not in (select TNO from Course)--31, lists the names, genders, and birthdays of all teachers and classmates.
--select Sname,sex,birthday from student Union select Tname,sex,birthday from TEACHER--*32, search for courses containing student "103" students study number
--select distinct SNO from Score X where isn't exists (select * from score y where y.sno=103 and NOT exists (SELECT * FROM SC Ore z where Z.sno=x.sno and Z.cno=y.cno)--*33, retrieves the name of the student taking all courses
--select sname from student where Sno in (select Sno from Score GROUP by SNO have count (*) = (select count (*) from Cou RSE) is not always right, it is wrong to welcome correct
Database Simple Practice Build Table +select
