Example: Learning database query. Creation of student information tables, primary foreign key relationships, and query instances of 45 questions. Main points of knowledge in the page 45, and page 65 page

Create DATABASE Shujuku

Use Shujuku

Use Cangku

Go

--Note: Foreign key to this relationship is not necessarily a key

--2: When you create a foreign key, the primary key for the linked table is established

--3: When a foreign key is created, the value of the foreign key must be derived from the primary key value

--4: When you want to delete a primary key table, you must first delete the foreign key table information

CREATE TABLE student--Student form

(

Sno varchar (+) NOT NULL PRIMARY key,--student primary key

sname varchar () NOT NULL,

Ssex varchar () NOT NULL,

Sbirthdy date,

Class varchar (50)

)

Select *from Student

INSERT into student values (' 1 ', ' Zeng Hwa ', ' Male ', ' 1987-09-01 ', ' 95033 ')

INSERT into student values (' 2 ', ' Li Ming ', ' Male ', ' 1985-10-01 ', ' 95031 ')

INSERT into student values (' 3 ', ' Wang Li ', ' female ', ' 1986-02-20 ', ' 95033 ')

INSERT into student values (' 4 ', ' Li June ', ' male ', ' 1986-01-23 ', ' 95033 ')

INSERT into student values (' 5 ', ' Wang Fang ', ' female ', ' 1985-02-10 ', ' 95031 ')

INSERT into student values (' 6 ', ' Xiao June ', ' male ', ' 1991-06-03 ', ' 95031 ')

DROP TABLE Student

Go

CREATE TABLE course--Curriculum

CNO varchar () NOT NULL primary key,

CNAME varchar (NOT NULL),

TNO varchar (references) teacher (TNO) NOT NULL,--teacher foreign key

)

Insert into course values (' 3-105 ', ' computer ', ' 825 ')

Insert into course values (' 3-245 ', ' OS ', ' 804 ')

Insert into course values (' 6-166 ', ' digital circuit ', ' 856 ')

Insert into course values (' 9-888 ', ' Advanced math ', ' 831 ')

Select *from Course

DROP TABLE Course

Go--cno Course number--sno student number--tno teacher number

CREATE TABLE score--score sheet

(

Sno varchar (references) student (SNO) NOT NULL,--student foreign key

CNO varchar (References) course (CNO) not NULL,--course foreign key

degree Decimal (4,1)

)

Select *from Score

INSERT into score values (' 1 ', ' 3-245 ', 86)

INSERT into score values (' 1 ', ' 3-245 ', 75)

INSERT into score values (' 2 ', ' 3-245 ', 68)

INSERT into score values (' 2 ', ' 3-105 ', 56)

INSERT into score values (' 3 ', ' 3-105 ', 76)

INSERT into score values (' 3 ', ' 3-105 ', 84)

INSERT into score values (' 4 ', ' 3-105 ', 85)

INSERT into score values (' 4 ', ' 3-105 ', 86)

INSERT into score values (' 5 ', ' 3-105 ', 87)

INSERT into score values (' 5 ', ' 6-166 ', 88)

INSERT into score values (' 6 ', ' 6-166 ', 89)

INSERT into score values (' 6 ', ' 6-166 ', 80)

DROP TABLE Score

Go

CREATE TABLE teacher--teacher tables

(

TNO varchar (primary) key NOT NULL,--Teacher primary key

Tname varchar () NOT NULL,

Tsex varchar () NOT NULL,

Tbirthday date,

Prof varchar (50),

Depart varchar (NOT NULL)

)

Insert into teacher values (' 804 ', ' Li bin ', ' Male ', ' 1958-12-02 ', ' associate Professor ', ' Computer Department ')

Insert into teacher values (' 856 ', ' Zhang Xu ', ' Male ', ' 1969-03-12 ', ' lecturer ', ' Electronic Engineering Department ')

Insert into teacher values (' 825 ', ' Wang Ping ', ' female ', ' 1972-05-05 ', ' ta ', ' computer Department ')

Insert into teacher values (' 831 ', ' Liu Bing ', ' female ', ' 1977-08-14 ', ' ta ', ' Electronic Engineering ')

Select *from Teacher

drop TABLE Teacher

Go

1. Query the sname, Ssex, and class columns of all records in the student table.

Select Sname,ssex,class from Student

2, the inquiry teacher all units namely does not duplicate depart column.

Select distinct depart from teacher

3. Query all records of student table.

Select*from Student

4. Query all records of the scores in the score table.

Select*from score where degree between and 80

5. Check the records of the scores in the score table.

Select*from score where degree in (85,86,88)

6, inquires the student table "" The class or the sex is "the female" the classmate record.

Select*from student where class= ' 95031 ' and ssex= ' women '

7. Query the student table for all records in descending order of class.

Select*from Student ORDER BY class Desc

8, in CNO Ascending, Degree descending query score all records of the table.

Select*from score ORDER by CNO ASC, Degree desc

9. Check the number of students in the class.

Select COUNT (*) from student where class= ' 95031 '

10. Check the student number and course number of the highest score in the score table. (sub-query or sort)

Select Sno,cno from score where degree= (select MAX (degree) from score)

Select Top 1* Sno,cno from score order BY degree desc-why not?

11. Check the average score of each course.

Select AVG (degree) from score where cno= ' 3-245 '

12. Inquire about the average scores of the courses that are at least famous students in the score table and begin with. --Key topics

Select Cno,avg (degree) degrees from score where CNO like ' 3% ' GROUP by CNO have COUNT (CNO) >

13, the query score is greater than, less than the Sno column.

Select Sno from score where degree between and 90

14. Sname, CNO and degree columns for all students are queried.

Select Sname, cno,degree from student joins score on Student.sno=score.sno

15. Check the SNO, CNAME and degree columns of all students. --Key topics

Select Sno,cname,degree from score join course on SCORE.CNO=COURSE.CNO

16. Check the sname, CNAME and degree columns of all students.

Select Sname,cname,degree from student join score on Student.sno=score.sno

Join Course on COURSE.CNO=SCORE.CNO

17. Check the average score of the class students.

Select AVG (degree) from score where Sno in (select Sno from student where class= ' 95033 ')

18, suppose to use the following command to establish a grade table:--key topics

CREATE TABLE Grade

(

The low int is not NULL,

UPP int NOT NULL,

[rank] varchar (50)

)

Insert into grade values (90,100, ' A ')

Insert into grade values (80,89, ' B ')

Insert into grade values (70,79, ' C ')

Insert into grade values (60,69, ' D ')

Select Sno,cno,rank from score join grade on degree between low and UPP order by rank desc-Table Join method

Select Sno,cno, (select RANK from grade where score.degree between low and UPP) level from score--subquery

The SNO, CNO and rank columns of all students are now queried.

19, the query elective "-105" course performance is higher than "" "student scores of all the students record. --Key topics

SELECT * FROM score where cno= ' 3-105 ' and degree> (select degree from score where sno= ' 2 ' and cno= ' 3-105 ')

20, the inquiry score to choose to learn many courses of the students score is the record of non-highest scores. --key topics, do not understand

--select x.* from Score x where degree< (select Max (y.degree) from score y where Y.sno=x.sno)

Select *from score where Sno in (select Sno from Score GROUP by Sno

Having COUNT (*) >1)

and degree not in (select MAX (degree) from score where Sno in (select Sno from Score Group by Sno have COUNT (SNO) >1))

-----check the student's score information for the highest score of each course

Select*from score a where degree not in (select MAX (degree) from score B cno have a.cno=b.cno)

Select*from score a where degree not in (select MAX (degree) from score b where a.cno=b.cno)

--Eliminate the highest score for each course chosen for multiple courses

Select*from score a where degree not in (select MAX (degree) from score b where a.cno=b.cno) and Sno in

(select CNO from Score Group by CNO have COUNT (*) >1)

21, the query score is higher than the number of "", the course number is "-105" all records of the results.

Select *from Score where degree> (select degree from score where sno= ' 2 ' and cno= ' 3-105 ')

22. Sno, Sname and Sbirthday of all students who were born in the same year as the students of the inquiry and study number. --Key topics

Select Sno,sname,sbirthdy from student where year (sbirthdy) = (select year (Sbirthdy) from student where sno= ' 3 ')

23, inquires "Zhang Xu" the Teacher classroom student achievement.

Select degree from score where CNO in (select CNO from Course where tno= (select TNO from teacher where Tname= ' Zhang Xu '))

24. The number of students who have enrolled in a course is more than the name of the teacher.

Select Tname from teacher where TNO in (select Tno from course where CNO =

(select CNO from Score Group by CNO have COUNT (CNO) >5))

25. Check the records of all the students in the class and class. --Key topics

Select *from student join score on Student.sno=score.sno where class in (' 95033 ', ' 95031 ')

26, the inquiry existence has the above achievement the course CNO.

Select CNO from score where degree>85

27. Find out the results table of the "computer Department" teacher teaching course.

Select *from score where CNO in (select CNO from Course where TNO in (select TNO from teacher where depart= ' computer system '))

28. Query the Tname and prof of teachers with different titles of "computer department" and "Electronic Engineering department"

Select Tname, prof from teacher where Prof not in (select Prof from teacher group by Prof have COUNT (Prof) >1)

)

29, inquires the elective number "105" course and the result is at least higher than the elective number "245" Schoolmate's CNO, Sno and degree, and according to degree from high to low order.

Select*from score where cno= ' 3-105 ' and degree> (select MAX (degree) from score where cno= ' 3-245 ') Order by degree ASC

30, inquires the elective number is "105" and the result is higher than the elective number "-245" the classmate's CNO, Sno and degree.

Select*from score where cno= ' 3-105 ' and degree> (select MAX (degree) from score where cno= ' 3-245 ')

31. Check the name, sex and birthday of all teachers and classmates.

Select Sname, Ssex,sbirthdy from student

Union

Select Tname,tsex,tbirthday from teacher

32, query All "female" teacher and "female" classmate's name, Sex and birthday.

Select Sname,ssex,sbirthdy from student where ssex= ' woman '

Union

Select Tname,tsex,tbirthday from teacher where tsex= ' woman '

33. Check the scores of students who have a lower average score than the course.

Select *from Score where degree < (select AVG (degree) from score)

34. Check the Tname and depart of all classroom teachers.

Select Tname from teacher where TNO in (select Tno from Course where CNO in (select CNO from Score))

35. Inquire about the Tname and depart of all teachers who have not lectured.

Select Tname from teacher where TNO not in (select TNO from Course where CNO in (select CNO from Score))

36. Inquire at least the class number of the famous boys.

Select class from student group by class has COUNT (Class) >=2

37, inquires the student table the surname "the king" the classmate record.

Select*from student where sname not a like ' King% '

38. Check the name and age of each student in the student table.

Select Sname,datediff (Year,sbirthdy,getdate ()) age from student

39. Query the maximum and minimum sbirthday date values in the student table.

Select MAX (Sbirthdy), MIN (Sbirthdy) from student

40. Check all records in the student table in order of class number and age from large to small.

Select*from Student ORDER BY class Desc,sbirthdy ASC

41. Query "male" teachers and their courses.

Select Tname, CNAME from teacher join course on Teacher.tno=course.tno where tsex= ' man '

42. Check the SNO, CNO and degree columns of the students with the highest score.

Select*from score where degree in (select MAX (degree) from score)

43, inquiries and "Li June" with the gender of all students sname.

Select sname from student where Ssex in (select Ssex from student where Sname= ' Li June ')

44, inquiries and "Li June" with the same sex and classmates sname.

Select sname from student where Ssex in (select Ssex from student where Sname= ' Li June ') and

Class in (select Class from student where Sname= ' Li June ')

45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course.

Select *from score where Sno in (select Sno from student where ssex= ' man ')

and CNO in (select CNO from course where cname= ' computer ')

Example: Learning database query. Creation of student information tables, primary foreign key relationships, and query instances of 45 questions. Main points of knowledge in the page 45, and page 65 page