Choose a different student.snum, sname.
From sc, a student
Where sc.snum=student.snum and score & lt60.
-2 List the names and ages of students who have taken computer courses (only the date of birth is listed in the table).
Select student.sname, (year(getdate())-year(birthday)) as the age.
From students
Where's snum?
(
Select sc.snum
From sc, of course, [part]
Where sc.secnum = [section]. Secnum and course.cnum = [section]. Cnum and course.dept =' Computer Department'
)
-3. Search the names and departments of students who have taken database technology courses.
Select student, name, student and department.
From students
Where are the students?
(
Select snum
From sc, [part], of course.
Where sc.secnum=[section]. Secnum and course.cnum=[section]. cnum
And cname=' database technology'
)
-4 List the students who have taken all courses.
Choose from students *
A place that doesn't exist
(
Select * from the course
A place that doesn't exist
(
Select * from sc, section
Where sc.secnum=section.secnum, student.snum=sc.snum
)
)
-5 Search the list of students whose scores are above 80 in each course.
Choose from students *
Where's snum?
(
Select snum
From sc
Group by snum
Having min (score) > =80
)
-6 Search the list of students who have won scholarships (with an average score of more than 90 points in each course)
Choose from students *
Where's snum?
(
Select snum
From sc
Group by snum
Having min > =80 and avg > =90.
)
-7 Search the list and grades of students who have passed college English, and sort them from high to low.
select student.snum，student.sname，sc.score
From sc, a student
Where are the students?
(
Select snum from sc, section and course.
Where sc.secnum = section.secnum and course.cnum = section.cnum and cname =' College English'
)
Sort desc by score
-8 Count the number of elective students in each course, and the output is listed as course number and number of students.
Select cnum as the course number, and count(*) the number of students.
From sc part
Where section.secnum=sc.secnum
Group by cnum
-9 Inquire about the names and departments of students who have attended database technology but not advanced mathematics.
Select student, name, student and department.
From students
Where are the students?
(
Choose different snum from sc, section and course.
Where sc.secnum = section.secnum and section.cnum = course.cnum and course.cname =' College English'
)
student We're not here.
(
Choose different snum from sc, section and course.
Where sc.secnum = section.secnum and section.cnum = course.cnum and course.cname =' advanced mathematics'
)
-1 1 Statistics on the number of people who choose courses and fail in each course.
Select cnum, count(*) as the number of selected students, in case when.
(
Select the count (*) from the sc section.
Where sc.secnum=section.secnum and score & lt60.
Group by cnum
) is empty, then 0 otherwise (
Select the count (*) from the sc section.
Where sc.secnum=section.secnum and score & lt60.
Group by cnum
) ends with a failed number.
From sc, part
Where sc.secnum=section.secnum
group by section.cnum