Student Selection Schedule

Use students

--1, queries the sname, Ssex, and class columns of all records in the student table.
Select Sname,ssex,class from Student
--2, query teachers all units that are not duplicated depart column.
Select distinct depart from teacher
--3, queries all records of the student table.
SELECT * FROM Student
--4, queries all records in the score table for scores from 60 to 80.
SELECT * from score where degree between and 80
--5, query the records in the score table for grades 85, 86, or 88.
SELECT * from score where degree in (85,86,88)

--6, inquires the student table "95031" class or the sex is "female" the classmate record.
SELECT * FROM student where class= ' 95031 ' or ssex= ' female '
--Here the Where condition is used after the or,or is a logical operator,
--yes or no, any one of these conditions is returned true

--7, queries all records of the student table in descending order of class.
SELECT * FROM student ORDER BY class Desc
--Use here to sort syntax order by column name ASC or DESC, which means ascending or descending order of a column

--8, CNO Ascending, Degree descending queries all records of the score table.
SELECT * FROM Score ORDER by CNO Asc,degree desc
--Here is the sort of two columns, when you sort the two columns,
--priority is given to sorting the first column after order by
--and then sort the second column without affecting the first column sort

--=============================================================
--9, check the number of students in "95031" class.
Select COUNT (*) from student where class= ' 95031 '
--This is used here to aggregate function count (*), which means that the number of data is calculated by the Where selection criteria query

--10, check the student number and course number of the highest score in the score table.
Select top 1 Sno,cno from score order BY degree DESC
--This is used in conjunction with sorting, sorted by fractions, and the first data is checked,
--Use the top keyword, top number, to represent the first few

--11, check the average score of each course
Select Cno,avg (degree) from score Group by CNO
--Used here to aggregate function avg (), and GROUP by
--avg (column) means to find the average of a column
--group by is the grouping of a column, with the same column values grouped in a group
--When group by IS combined with other aggregation functions, it is grouped first, then each group of data is manipulated separately

--12, check the average score for the ' 3-105 ' course.
Select Cno,avg (degree) from score Group by CNO have cno= ' 3-105 '
SELECT * FROM Score

--12, query the average score of a course that has at least 5 students enrolled in the score table and starts with 3.
Select AVG (degree) from score Group by CNO have cno like ' 3% ' and Count (Sno) >=5
-This problem uses the having choice condition, having to cooperate with GROUP by
--meaning to select some of the data that meets the criteria and then operate it in a grouped condition
Between--select and from, only the aggregate function or the GROUP BY column

--13, the query score is greater than 70, and the SNO column is less than 90.
Select Sno from score where degree>70 and degree<90
--use and to connect two selection criteria, because this is said to be greater than less than, so you cannot use between and

--==============================================================

--14, Sname, CNO, and degree columns for all students are queried.
Select Sname,cno,degree from student A,score B where a.sno=b.sno
Select Sname,cno,degree from student join score on Student.sno=score.sno
--This topic first joins two tables, describes the relationship through a where condition, and then queries the data

--15, queries all students for SNO, CNAME, and degree columns.
Select Sno,cname,degree from score a,course b where a.cno=b.cno
--with 14 questions, make a table connection and query the corresponding column

Select B.sno,cname,degree from student A,score B,course C
where A.sno=b.sno and C.cno=b.cno

--16, queries all students for sname, CNAME, and degree columns.
Select Sname,cname,degree from Student A
Inner JOIN score B on A.sno=b.sno
Inner JOIN course C on C.CNO=B.CNO
--This is a three-table union query that joins using join, join table on relationship condition

--17, query "95033" class student's average score.
Select AVG (degree) from score where Sno in (
Select Sno from student where class= ' 95033 ')
--Using a subquery here, in parentheses should be used to put parameters,
--Use Query statements to query out a column for use as parameters, note that a column must be

--18, assume that a grade table is created with 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 ')
--The SNO, CNO and rank columns of all students are now queried (that is, the score corresponding level of the score table is displayed).
Select Sno,cno,degree,rank from Score,grade
where degree between low and UPP
Select *from Grade
--This topic first creates the table, then inserts the data and then queries, joins the table after the connection through the degree and the high and low range establishes the relation

--19, query Elective "3-105" course performance is higher than the "109" student scores of all the students record.
Select *from Student where Sno in (
Select Sno from score where cno= ' 3-105 ' and degree> (
Select degree from score where cno= ' 3-105 ' and sno= ' 109 '
)
)
Select *from student,score where cno= ' 3-105 ' and degree>
(select degree from score where cno= ' 3-105 ' and sno= ' 109 ')
--the most in-one subquery finds out the score of the 3-105 class for student 109th, and then compares it as a parameter
--a subquery in the middle is the number of students who have a higher score than the one they just found.
--and then finally find out all the information based on these numbers.

--20, query score the number of students who choose to learn more than the highest score of the record.
Select *from Score as a where Sno in (
Select Sno from score as B group by Sno have COUNT (SNO) >1)
and Degree < (select Max (degree) from score as C where c.cno=a.cno)
--This problem uses subqueries to query the number of numbers greater than 1 after grouping by school number,
--then check the score information according to the study number and to meet the score of the outside table is less than the maximum score of each group after grouping

--21, the query score is higher than the school number is "109", the course number is "3-105" all records of the results.
Select A.sname,a.sno,b.cno,b.degree from student A,score B where a.sno=b.sno
and B.degree>all (select degree from score where sno= ' 109 ' and cno= ' 3-105 ')
--Use the ALL keyword here, all in conjunction with the relational operator > < = to use,
--This indicates that the data for degree joins satisfies the criteria that are greater than all the data in the degree column that is queried later

--22, inquiry, and student number 108 are the SNO, sname and Sbirthday of all students born in the same year.
Select Sno,sname,sbirthday from Student
Where year (sbirthday) =
Year ((select Sbirthday from student where sno= ' 108 '))--and sno<> ' 108 '
--This problem uses a time date function that takes a year to get the year in time to return an integer value

--23, inquires "Wang Ping" the student achievement which the teacher teaches.
Select A.tname,b.degree,c.cno from teacher A,score B,course C
where A.tno=c.tno and B.cno=c.cno and Tname= ' Wang Ping '

Select *from score where Cno in (select Cno from Course where Tno on (select Tno from Teacher where Tname= ' Wang Ping '))
--Connecting queries, plus selection criteria and relationships

--24, the name of the teacher who has more than 5 students who have enrolled in a course.
Select Tname from teacher where TNO in (
Select TNO from Course where CNO in (
Select CNO from Score GROUP by CNO have Count (Sno) >5)
)
--This problem uses subqueries, logic is to check the number of people more than 5 of the course number
-Re-check the teacher number corresponding to these course numbers
--Re-check the teacher's name according to the teacher's number and then combine it backwards.

--25, check the records of 95033 classes and 95031 classes of all students.
SELECT * FROM student where class= ' 95033 ' or class= ' 95031 '
--a logical or conditional correlation query that satisfies any one of the criteria to query out the data

--26, the inquiry existence has 85 points above the course CNO.
Select distinct CNO from score where degree>85
--Use the DISTINCT keyword to go back and add where to select criteria to filter

--27, the results of the "computer department" teacher's teaching schedule.
Select CNO, degree from score where CNO in (
Select CNO from Course where TNO in (
Select TNO from teacher where depart= ' Computer Department '))
--sub-query, according to the calculation system can only query the teacher number
--Find out the corresponding course number after the teacher number, then check the score list according to the course number.

--28, inquires into the tname and prof of teachers with different titles of "computer department" and "Electronic Engineering department".
Select Tname,prof from Teacher where prof<>
(select Prof from Teacher GROUP by Prof have Count (Prof) >=2)
--The second method of solution
Select *from Teacher where Prof not in (
Select Prof from Teacher where depart= ' computer system ' and Prof in (
Select Prof from Teacher where depart= ' electronic Engineering Department '))

Select Prof,tname from Teacher where Prof not in (
Select Prof from Teacher where depart= ' computer system ' and
Prof in (select Prof from Teacher where depart= ' electronic Engineering Department '))
and depart in (' Computer department ', ' Electronic Engineering Department ')
--29, inquires the elective number is "3-105" the course and the result is at least higher than the elective number "3-245" Schoolmate's CNO, Sno and degree, and presses degree from high to low order.
Select Cno,sno,degree from score where cno= ' 3-105 ' and degree >any (
Select degree from score where cno= ' 3-245 ')
ORDER BY degree DESC
--The first solution using Any,any is to satisfy more than any one, or you can use the value greater than the smallest to manipulate the override any
Select Cno,sno,degree from score where cno= ' 3-105 ' and degree > (
Select min (degree) from score where cno= ' 3-245 ')
ORDER BY degree DESC

--30, inquires the elective number is "3-105" and the result is higher than the elective number "3-245" the classmate's CNO, Sno and degree.
Select Cno,sno,degree from score where cno= ' 3-105 ' and
Degree >all (select degree from score where cno= ' 3-245 ') Order BY degree DESC
--This problem uses a subquery and then uses all to manipulate the 3-105 to be more than all 3-245 to filter out

--31, check the name, sex and birthday of all teachers and classmates.
Select Sname name, Ssex sex, sbirthday birthday from student
Union
Select Tname,tsex,tbirthday from teacher
--This is the two table data up and down, using the Union keyword to vertically splicing,
--then the data type of the above column corresponds to the data type of the column below

--32 the name, sex and birthday of all "female" teachers and "female" classmates.
Select Sname name, Ssex sex, sbirthday birthday from student where ssex= ' woman '
Union
Select Tname,tsex,tbirthday from teacher where tsex= ' woman '
-This is the same as above, except that each query adds a where to filter the selection

--33, the results of students who have a lower average score than the course.

Select Sno,cno,degree from Score a where degree< (
Select AVG (degree) from score B group by CNO have B.cno=a.cno)
--The problem is to use subqueries and group them in a subquery, and to establish a filter relationship between the grouped data and the outer column values.

--34, query all the teachers tname and depart.
Select Tname,depart from teacher where TNO on (select TNO from Course)

--sub-query, go to the Teacher's table to check the teacher's label in the course table, the teacher number in the course table as a parameter to use

--35 inquires the Tname and depart of all teachers who have not lectured.
Select Tname,depart from teacher where TNO not in (select TNO from Course)
--just go to the opposite range, add not,not in front of the meaning of right and wrong

--36, check the class number of at least 2 boys.
Select class from student where ssex= ' Man ' GROUP by class has count (Sno) >=2
--Grouping plus having filter

--37, query student table in the name of "Wang" classmate Records.
SELECT * FROM student where sname don't like ' King% '
--This is used to a like fuzzy query, as if the meaning is the meaning, in the string to add%,% to represent any string of characters

--38, query the name and age of each student in the student table.
Select Sname,year (getdate ())-year (Sbirthday) age from student
--Use the current date function getdate () Here, and use the year function () and subtract

--39, queries the student table for the maximum and minimum sbirthday date values.
Select Max (sbirthday), Min (sbirthday) from student
--This topic uses aggregate functions to take the maximum minimum value

--40, check all records in the student table in order of class and age from large to small.
SELECT * FROM student order by class Desc,sbirthday ASC
--use sort order by, and then sort by class to row sbirthday without affecting class ordering

--41, query "male" teachers and their courses.
Select Tname,cname from teacher A,course b where a.tno=b.tno and tsex= ' male '
--table join, two tables through the relationship splicing, and then add conditional filtering

--42, Sno, CNO, and degree columns for the highest scores of students.
Select top 1 Cno,sno,degree from score order BY degree DESC
--using order by to sort fractions, take the first one using top to get the first piece of data

--43, inquiries and "Li June" with the sex of all the students of the sname.
Select sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and sname <> ' Li June '
--subquery, find out the gender of Li June as the parameter of where selection condition, then and is excluded Li June, use to unequal number <>

--44, inquiries 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 <> ' Li June ' and class in (select Class from student where Sname= ' Li June ')
--use subqueries to isolate Li June's class and gender as parameters for where selection criteria, and connect with and to select criteria

--45, query all of the "male" students who took 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= ' Introduction to Computers ')
--sub-query, a fundamental reason for subqueries is to find a column to use as a parameter

--======================================================================
SELECT * FROM Student
SELECT * FROM Score
SELECT * FROM Course
SELECT * FROM teacher

Student Selection Schedule