Database Principle and Application Experiment 2 [Single Table & Multiple Tables & Complex Query <Each operation and sentence diagram>]

1. The purpose of the experiment

1. Master the methods of inserting, modifying and deleting records into database tables using SQL Server integrated environment;
2. Master the methods of using T-SQL statements to insert, modify and deleting records into database tables;
3. Master the methods of querying data using SELECT statements method.


2. Experimental steps

1. Use SSMS to operate the data
(1) The existing data records for the three tables of the student table, the course table and the course selection table in Experiment 1 are shown in the following table. Please complete the entry of the data records in the following table in the SSMS environment.

Insert picture description here
Insert picture description here


Insert picture description here


(2) After completing the entry of the data records of the above three tables, please complete the viewing, modification and deletion operations of the data records in a certain table in the SSMS environment.

2. Use T-SQL statements to manipulate data
(1) For the above three data record tables, use T-SQL statements to complete the insertion of data records.
(2) For the three data record tables entered above, use T-SQL statements to modify and delete data records in a certain table.

3. Use T-SQL statements to query the data in a single table
(1) Query the student ID of students whose test scores are greater than or equal to 90.
(2) Query the student number and name of male students who are older than 20 years old.
(3) Query the name and gender of students who are older than 18 years old and are not students of the School of Information and Mathematics.
(4) Query the details of courses that start with "calculation_" and the penultimate Chinese character is "group".
(5) Query the number of students who have taken elective courses.
(6) According to the current time of the system and the age of the student, query the date of birth of the student (requires a specific output: year-month-day).
(7) Find the student ID and course ID of the student who lacks grades.
(8) Query the situation of all students, and the results are sorted in ascending order by the number of the department, and students in the same department are in descending order by age.
(9) Query the highest score of the student who took the No. 1003 course.
(10) Find the number of each course and the corresponding number of elective courses.

4. Use T-SQL statements to query data in multiple tables
(1) Query students and their courses, grades, etc. (student information should be listed regardless of whether the course is selected or not).
(2) Query information about students whose gender is male and whose course grades are passable, as well as course numbers and grades.
(3) Query the student ID, name and department of the students who have taken the operating system course.
(4) Query the average score of the students taking the optional data structure course.
(5) Query the name, course name and grades of male students whose course scores are above 90 points.
(6) Query the student ID of the student with the highest score among the students who took the No. 1002 course.
(7) Check the student numbers of students who have taken 2 courses or more in the School of Information.
(8) Query the names of female students who have taken at least one course.
(9) Query the names of students who have not scored less than 85 in a subject.
(10) Query the course number and grades of the courses selected by Qian Heng for students whose grades are greater than 80 points.

5. Use T-SQL statements to perform complex queries on data
(1) Query information about students who are studying in the same department as Qian Heng.
(2) Find students of the same department, same age, and same sex.
(3) Inquire about the student ID and name of the students who did not study at least No. 1003 and No. 1004.
(4) Query the course numbers of courses that Qian Heng does not study.
(5) Query the list of students who are older than all students of the School of Information in other departments, and output in a sorted manner.
(6) Query the course numbers of courses selected by only girls.
(7) Query the names of students who have taken all courses.
(8) Query the course number and course name of the courses that all students have taken.
(9) Find the names and ages of male students who are older than the age of all female students.
(10) Count the number of elective students for each course. Only courses with more than 2 (including 2) students are counted. The course number and the number of electives are required to be output. The query results are sorted in descending order of the number of people. If the number of people is the same, they are sorted in ascending order of the course number.


3. Experimental content

1. Use SSMS to manipulate data

Insert picture description here


Insert picture description here


Insert picture description here


2. Use T-SQL statements to manipulate data
(1) For the above three data record tables, use T-SQL statements to complete the insertion of data records.
(2) For the three data record tables entered above, use T-SQL statements to modify and delete data records in a certain table.

3. Use T-SQL statements to perform dataSingle table query
(1) Query the student ID of the student whose test score is greater than or equal to 90.

select Sno from SC where Grade>=90;
Insert picture description here


(2) Query the student number and name of male students who are older than 20 years old.

select Sno ,Sname from Student where(Sage>23 AND Ssex= '男');
Insert picture description here


(3) Query the name and gender of students who are older than 18 years old and are not students of the School of Information and Mathematics.

select Sname,Ssex from Student where(Sage> 18 And Sdept not in('IS','MA'));
Insert picture description here


(4) Query the details of courses that start with "calculation_" and the penultimate Chinese character is "group".

select * from Course where Cname like '操作系统_%原_';
Insert picture description here


(5) Query the number of students who have taken courses.

select COUNT(distinct Sno) from SC;
Insert picture description here


(6) According to the current time of the system and the age of the student, query the date of birth of the student (requires a specific output: year-month-day).

select Student.Sno from Student,SC where Sdept='CS'AND Student.Sno=SC.Sno group by Student.Sno having COUNT(*)>=2;
Insert picture description here


(7) Find the student ID and course ID of the student who lacks grades.

select SC.Sno,SC.Cno
from SC
where SC.Grade is null
Insert picture description here


(8) Query the situation of all students, and the results are arranged in ascending order by the number of the department, and the students in the same department are in descending order by age.

select *from studentorder by Sdept ASC, sage DESC;
Insert picture description here


(9) Query the highest score of the student who took the No. 1003 course.

select SC.Sno,SC.Cno
from SC
where SC.Cno = 1003
and SC.Grade = (
select max(SC.Grade)
from SC
where SC.Cno = 1003)
Insert picture description here


(10) Find the number of each course and the corresponding number of elective courses.

select SC.Cno,count(distinct SC.Sno) as count
from SC
group by SC.Cno
having count(distinct SC.Sno) >=1
Insert picture description here


4. Use T-SQL statements to perform dataMulti-table query
(1) Inquire about students and their courses, grades, etc. (no matter whether courses are selected or not, students’ information must be listed).

select Student. Sname,Course.Cname,SC.Grade from Student,SC,Course where student.sno = sc.sno AND sc.cno = course.cno
Insert picture description here


(2) Query information about students whose gender is male and whose course grades are passing, as well as course numbers and grades.

select Student.*,Course.Cno,SC.Grade from Student,Course,SC where Ssex='男' AND SC.Grade>=60 AND sc.cno = course.cno AND student.sno = sc.Sno
Insert picture description here


(3) Query the student ID, name and department of the students who have studied the operating system course.

from student
order by Sdept ASC, sage DESC
SELECT DISTINCT
student. sno,sname,
sdept
FROM
student,course,sc
WHERE
student.sno = sc. sno
AND course.Cno = sc.Cno
AND Cname ='操作系统'
Insert picture description here


(4) Query the average score of the students taking the optional data structure course.

SELECT
AvG (score) As'数据结构平均分'
FROM
course,sc
WHERE
course.Cno = sc.CnoAND Cname =’数据结构’
Insert picture description here


(5) Query the name, course name and grades of male students whose course scores are above 90 points.

select Student.Sname,Course.Cname,SC.Grade
from SC
inner join Student on SC.Sno = Student.Sno
inner join Course on SC.Cno = Course.Cno
where SC.Grade > 90
and Student.Ssex ='男'
Insert picture description here


(6) Query the student ID of the student with the highest score among the students who took the No. 1002 course.

select SC.Sno,SC.Cno
from SC
where SC.Cno = 1002
and SC.Grade = (
select max(SC.Grade)
from SC
where SC.Cno = 1002)
Insert picture description here


(7) Check the student numbers of students who have taken 2 courses or more in the School of Information.

select Student.Sno from Student,SC where Sdept='CS'AND Student.Sno=SC.Sno group by Student.Sno having COUNT(*)>=2;
Insert picture description here


(8) Query the names of female students who have taken at least one course.

select distinct Student.Sname from Student,SC where Ssex= '女' AND Student.Sno=SC.Sno
Insert picture description here


(9) Query the names of students who have not scored less than 85 in a subject.

select sname
from sc INNER JOIN student
ON sc.sno = student. sno
group by sname
having MIN(score) >= 85
Insert picture description here


(10) Query the course number and grade of the course selected by Qian Heng of student whose grade is greater than 80 points.

select Course.Cname,SC.Grade
from SC
inner join Student on SC.Sno = Student.Sno
inner join Course on SC.Cno = Course.Cno
where SC.Grade > 90
and Student.Sname='钱横'
Insert picture description here


5. Use T-SQL statements to perform dataComplex query
(1) Query information about students studying in the same department as Qian Heng.

select * from Student where Sdept=(select Student.Sdept from Student where Sname='钱横')
Insert picture description here


(2) Find students of the same department, same age, and same sex.

select * from Student where Sdept in(select Sdept from Student group by Sdept,Sage,Ssex having COUNT(*)>1)
Insert picture description here


(3) Inquire about the student ID and name of the students who have not taken at least No. 1003 and No. 1004 courses.

select distinct Sno,Sname from Student where Sno not in(select Sno from SC where Cno in(1003,1004))
Insert picture description here


(4) Query the course numbers of courses that Qian Heng does not study.

select Cno from Course where Cno not in(select distinct Cno from Student s,SC where s.Sno=sc.Sno and s.Sname like '钱横')
Insert picture description here


(5) Query the list of students who are older than all students in the School of Information in other departments, and sort the output.

select distinct S.* from Student s where Sdept!='IS ' and s.Sage>(select MAX(Sage) from Student where Sdept='IS')
Insert picture description here


(6) Query the course numbers of courses that only girls choose.

select * from Course where Cno not in (select distinct c.Cno from Course c,Student s,SC where c.Cno= sc.Cno and s.Sno=SC.Sno and s.Ssex='男')
Insert picture description here


(7) Query the names of students who have taken all courses.

select Student.Sname
from Student
where not exists (
select *
from Course
where not exists (
select *
from SC
where Student.sno=SC.sno and Course.cno=sC.cno
)
)
Insert picture description here


(8) Query the course number and course name of the courses that all students have taken.

select Course.Cno,Course.Cname
from Course
where Course.Cno in (
select SC.Cno
from SC
group by SC.Cno
having count(1) = (select count(distinct SC.Sno) from SC)
)
Insert picture description here


(9) Find the names and ages of male students who are older than the age of all female students.

select Student.Sname,Student.Sage
from Student
where Ssex = '男'
and Student.Sage > (
select max(Student.Sage)
from Student
where Student.Ssex = '女')
Insert picture description here


(10) Count the number of elective students for each course. Only courses with more than 2 (including 2) students are counted. The course number and elective number are required to be output. The query results are arranged in descending order of number. If the number of people is the same, they are sorted in ascending order of course number.

select SC.Cno,count(distinct SC.Sno) as count
from SC
group by SC.Cno
having count(distinct SC.Sno) >= 2
order by count(distinct SC.Sno) desc, SC.Cno asc
Insert picture description here

Fourth, the experiment summary

Through this experiment, I learned data query, modification and deletion, familiar with the SQL Server2012 operation interface, understand the logical components of the database system; familiar with the SQL Server management console operating environment; master the use of SQL Server Management Studio and SQL statements to create databases and tables Method; master the modification method of database and table structure; master the basic operation of database management.