SQL Server database (Job explanation and review)

--The first question queries the sname, Ssex, and class columns of all records in the student table.
Select Sname,ssex,class from Student

--The second question queries all the teachers ' units that are not duplicates of the depart column.
Select distinct depart from Teacher

--the third question queries all records of the student table.
SELECT * FROM Student

--The fourth question queries the score table for all records from 60 to 80.
SELECT * from score where degree between and 80

--The fifth query score a record of 85, 86 or 88 in the table.
SELECT * from score where degree in (' 85 ', ' 86 ', ' 88 ')

--The Sixth Query student table in the "95031" class or sex for "female" students record.
SELECT * FROM student where class= ' 95031 ' or ssex= ' female '

--Question seventh queries all records of the student table in descending order of class.
SELECT * FROM student ORDER BY Class DESC

--Question eighth queries all records of the score table in CNO Ascending, degree descending order.
SELECT * FROM Score ORDER by Cno Asc,degree desc

--The number of students in the "95031" class is queried in question Nineth.
Select COUNT (*) from student where class= ' 95031 '--* can be converted to primary key value

--The tenth question inquires 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)

--The 11th question inquires the average score of each course.
Select Cno,avg (degree) as average split from score group by Cno

Select Cname from Course where Cno in (select Cno A from score group by Cno)
Union
Select Cno,avg (degree) from score Group by Cno

-The 12th question is the average score of a course in which at least 5 students are enrolled in the score table and begin with 3.
Select AVG (degree) from score where Cno like ' 3% ' GROUP by Cno have COUNT (Cno) >4

--The 13th Question query score is greater than 70, less than 90 of the SNO column.
Select Sno from score where degree between and 90

--Question 14th queries All students for sname, CNO and degree columns.
Select Sname,cno,degree from student joins score on student. Sno=score.sno

--Question 15th queries All students for SNO, CNAME, and degree columns.
Select Sno,cname,degree from score join Course on COURSE.CNO=SCORE.CNO

--Question 16th queries all students for sname, CNAME, and degree columns.
Select Student. Sname,cname,degree from student joins score on student. Sno=score.sno Join Course on COURSE.CNO=SCORE.CNO

--the 17th question inquires the average score of "95033" class students.
Select AVG (degree) from score where Sno in (select Sno from student where class= ' 95033 ')

Select AVG (degree) from score,student where student. Sno=score.sno and class= ' 95033 '

--The 18th question assumes that a grade table is created using the following command:
CREATE table grade (Low Int,upp Int,rank char (1))
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 ')
Insert into grade values (0,59, ' E ')
--Sno, CNO and rank columns are now available for all students.

Select Sno,cno,degree,[rank] from grade join score on score.degree between Low and UPP

Select Sno,cno,degree,[rank] from Score,grade where degree between low and UPP

--The 19th query elective "3-105" course performance is higher than the "109" student scores of all the students record.
SELECT * from Student,score where score.cno= ' 3-105 ' and student. Sno=score.sno and score.degree> (select degree from score where cno= ' 3-105 ' and sno= ' 109 ')

--the 20th query score the number of students who choose to learn more than the highest score of the record.
SELECT * from score a WHERE degree < (select MAX (degree) from score b where a.cno=b.cno) and Sno in (select Sno from Scor E GROUP BY Sno have Count (*) >1)

--The 21st query result is higher than the school number of "109", the course number is "3-105" of all records.
SELECT * from Student,score where student. Sno=score.sno and score.degree> (select degree from score where cno= ' 3-105 ' and sno= ' 109 ')

Sno, Sname and Sbirthday of all students who were born in the same year as the 22nd query and student number 107.
Select Sno,sname,sbirthday from student where year (student. Sbirthday) = (select year (sbirthday) from student where sno= ' 107 ')

--The 23rd question inquires "Zhang Xu" the student achievement which the teacher teaches.
--select degree from Score,teacher,course where Teacher.tname= ' Zhang Xu ' and Teacher.tno=course.tno and Course.cno=score.cno

Select Sno,cno,degree from score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname= ' Zhang Asahi '))

--the 24th question is the name of the teacher who has more than 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))

--The 25th question inquires the records of 95033 classes and 95031 classes of all students.
SELECT * FROM student where class= ' 95033 ' or class= ' 95031 '

--The 26th question query existence has 85 points above the course CNO.
Select distinct CNO from score where degree>85

--the 27th question inquires into the results table of the teachers ' courses taught by the "computer department".
Select Sno,cno, degree from score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where depart= ' Computer system '))

--The 28th query "computer Department" and "Electronic Engineering department" different titles of teachers Tname and Prof. Working with related sub-queries
Select Tname,prof from Teacher a where Prof not in (select Prof from Teacher b where A.depart!=b.depart)

--the 29th question inquires into the CNO, Sno and degree of the students with the elective number "3-105" and the grade at least higher than the elective number "3-245" course, and is sorted by degree from highest to lowest order.
Select Cno,sno,degree from Score a where (select degree from score b where cno= ' 3-105 ' and B.sno=a.sno) >= (select degree From score C where cno= ' 3-245 ' and C.sno=a.sno] ORDER by degree DESC

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

-the 30th question inquires into the CNO, Sno and degree of the students with the elective number "3-105" and higher than the elective number "3-245" course.
Select Cno,sno,degree from Score a where (select degree from score b where cno= ' 3-105 ' and B.sno=a.sno) > (select degree From score C where cno= ' 3-245 ' and C.sno=a.sno)

--Question 31st queries all teachers and classmates for name, sex and birthday.
SELECT DISTINCT Sname as name,ssex as sex,sbirthday as birthday from student
Union
SELECT DISTINCT Tname as name,tsex as sex,tbirthady as birthday from Teacher

--the 32nd question inquires into the name, sex and birthday of all "female" teachers and "female" classmates.
SELECT DISTINCT Sname as name,ssex as sex,sbirthday as birthday from student where ssex= ' women '
Union
SELECT DISTINCT Tname as name,tsex as sex,tbirthady as birthday from Teacher where tsex= ' women '

-The 33rd question is the result table of the students who have a lower average score than the course.
Select Sno,cno,degree from Score a where a.degree< (select AVG (degree) from score b where a.cno=b.cno)

--The 34th question inquires the tname and depart of all the teachers in the classroom.
Select Tname,depart from Teacher where tname in (select distinct tname from Teacher,course,score where Teacher.tno=course. Tno and Course.cno=score.cno)

Select Tname,depart from Teacher where TNO in (select Tno from Course where Cno in (select distinct Cno from score))

--The 35th question inquires the tname and depart of all teachers who have not lectured.
Select Tname,depart from Teacher where Tname not in (select distinct tname from Teacher,course,score where Teacher.tno=cou Rse. Tno and Course.cno=score.cno)
--the 36th question inquires at least 2 boys ' class number.
Select Class fromstudent where ssex= ' man ' GROUP by Class has COUNT (*) >1

--the 37th question inquires student The student record that does not have surname "Wang" in the table.
SELECT * FROM student where Sname don't like (' King% ')

--Question 38th queries the name and age of each student in the student table.
Select Sname,year (GETDATE ())-year (sbirthday) from student

--Question 39th queries the maximum and minimum sbirthday date values in the student table.
Select Max (sbirthday) as Maximum, Min (sbirthday) as min from student

--The 40th question is to query all records in the student table in order of class number and age from large to small.
SELECT * FROM Student ORDER by Class Desc,sbirthday ASC

-The 41st question is about "male" teachers and their courses.
Select Tname,cname from Teacher,course where tsex= ' man ' and Teacher.tno=course.tno

--The 42nd question query the highest score classmate's Sno, CNO and degree column.
Select Sno,cno,degree from score where degree= (select MAX (degree) from score)

Select top 1* from score order BY degree DESC

--The 43rd query and "Li June" with the sex of all students sname.
Select Sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and Sname not in (' Li June ')

--The 44th query and "Li June" with the same sex and classmates sname.
Select Sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and Sname not in (' Li June ') and class= (select Class from student where Sname= ' Li June ')

--The 45th question inquires all the "male" students ' grades in the course of "Introduction to Computer".
Select Sno,degree from score where Sno in (select Sno from student where ssex= ' man ') and Cno in (select Cno from Course wher E cname= ' Introduction to Computers ')

Review

1. Database operation
Create DATABASE name
Drop database name
Use database name
The interval between go two SQL statements

2. Table operation
CREATE TABLE table name (column name type other, column name type, others)
Primary KEY Primary Key
Identity self-growing column
Not NULL non-null
Unique only
References foreign key References primary table name (primary table primary key column)
A foreign key is typically associated from a column of a table to the primary key of the primary table
drop table table Name drop tables

3. Data manipulation

Increase
Insert into table name values (value for each column)

Insert into table name (column name) VALUES (column value)

Modify

Update table name set column name = value, column name = value where filter condition

Delete

Delete from table name where filter criteria

Inquire
1. Simple query
SELECT * FROM table name

Select Column name from table name

Select Column name as alias from table name

2.
Conditional query

SELECT * FROM table name where Condition 1

SELECT * FROM table name where Condition 1 or Condition 2

SELECT * FROM table name where Condition 1 and condition 2

3. Range query between and

SELECT * FROM table name where column name between value and value 2

4. Discrete query

SELECT * FROM table name where column name in (data list)

5, Fuzzy query like
SELECT * FROM table name where column name like '%_ '

6. Sort Query desc Descending

SELECT * FROM table name order BY column name

7. Group Query

SELECT * FROM table name Group BY column name having condition

8, paged query top n go to the top n values
Select top N Table name

9, go to re-query

Select DISTINCT column name from table meter

10. Aggregate function (statistical function)
Select COUNT (*) from table name

Select Sun (column name) from table name

AVG Max min

Second, advanced query

1. Connection Query
Column extension Join on

Extended Union for UNION query rows

2, sub-query
(1) Unrelated subqueries
The subquery does not have a relationship with the parent query, and the subquery can be executed separately

(2) Related sub-query
When a subquery executes, it must use the contents of the parent query as a condition

SQL Server database (Job explanation and review)