Table structure:
Student (sno, sname, gender, age. deptno)
Department (name, name)
Course (cno, cname, grade, teacher number)
Teacher (tno, tname)
stu_cors(id，stuno，corsno，score)
-Search for students who study jsp and list their names.
Choose a student name
From students
where SnO in(select stuno from stu _ CORS where CORS no =(select cno from course where cname = ' JSP '))
go to
Choose students. sno
Connect stu _ corson student.sno = stu _ cors.stuno from the students.
Internal joining course on corsno=course.cno
Where course.cname='jsp'
Select sname
From students
Among them, sno in(select stu_cors.stuno
Join the course on stu_cors. CORSNO = course. CNO where course. CNAME =' JSP' from within STU _ CORS.
-Second, search how many students study java courses.
Select COUNT(stuno) as "the number of Java learners".
From Stuckel
Where corsno = (choose cno from the course, where cname='java')
-3. Search how many students are studying in each course.
Select course.cname, count (stu _ cors.stuno) as the number of students.
Stu_cors from stu_cors.corsno=course.cno joined the course internally.
Group by course. cname
-How many students have studied jsp and servlet?
Select COUNT(sno) as the number of jsp students in JSP and servlet courses.
From students
Among them, sno in(select stu_cors.stuno
Stu_cors from stu_cors.corsno=course.cno joined the course internally.
Where course.cname='jsp') and
sno in(select stu_cors.stuno
Stu_cors from stu_cors.corsno=course.cno joined the course internally.
where course.cname='jsp ')
-How many students have taken more than two courses?
Select corsno, count (stun) as "the number of students studying more than two courses".
From Stuckel
Group by Cors without having count (stun) > 2 was ordered by Cole Si Nuo.
-6. Retrieve the courses that no students have studied and the course names.
Select cname
From course
Where cno is absent (corsno is selected from stu_cors).
-How many students does Teacher A have?
Select COUNT(stuno) as "the number of students of Teacher Zhang".
From Stuckel
Where is corsno (choose a course. cno
Join the teachers in the curriculum from within the curriculum.
Where teacher.tname=' Zhang Hua')
How many teachers are there, and what are the courses taught by each teacher?
select teacher.tno，course.cname
Join the teacher's curriculum from the curriculum
group by teacher.tno，course.cname
-ten, search how many students each teacher has.
Select teacher.tno, teacher.tname, count (stu _ cors.stuno) as the number of students.
Teachers from teacher.tno=course.cno join the course internally.
Course.cno = internal connection on stu _ cors.corsno stu_cors
group by teacher.tno，teacher . tname order by teacher . tno
-Eleven search teachers who teach more than two courses.
Choose a teacher. Tno, calculate (of course. Cno) is defined as "a teacher who teaches more than two courses"
Join the course from within the teacher.
Group by teacher. tnohaving (count (course. cno) > 2) is ordered by teachers. tno
-13. Search the names of students in the courses taught by Teacher A, all of whom have studied these courses.
Select sname
From students
Where sno (stuno is selected from the internal connection of stu_cors)
-Query the names of courses with more than one elective course and the total number of elective courses in each course, and sort the results in ascending order.
Choose a teacher. tno，count (stu _ cors。 Stuno) as the "total number of students selected"
Join the course from within the teacher.
Course.cno = internal connection on stu _ cors.corsno stu_cors
group by teacher . tno having(COUNT(stu _ CORS . stuno)& gt； 350) Sort by quantity (stu_cors.stuno) asc
-Check the number of students whose scores in each course are higher than those of other students in this course.
Choose stuno
From Stuckel
where
Group by corsno
-Inquire about each student's student number and name, and the number of courses that the student has performed well.
select student.sno，student.sname，max(stu_cors.score)
Connect stu _ corson student.sno = stu _ cors.stuno from the students.
Group by student.sno, student.sname
-Inquire about the student number, name and department of a student younger than the software engineering student.
-Inquire about the student numbers, names and ages of students in each department who are older than the average age of the department.
Select sno, sname and age.
From students
Press deptno, sno, sname, age having (age >; AVG (age))
-Inquire about the student number and name of the students who have not selected courses.
Choose sno, sname
From students
Where sno is absent (select sno from stu_cors).
go to
-Inquire about the student numbers of students whose scores in elective courses are higher than their average scores, and the class numbers of elective courses.
Choose Stuno, Cole Si Nuo
From Stuckel
By stuno, corsno, Score Having (Score >; AVG (score))
-Inquire about each student and elective courses (external connection is required)
select student.sno，student.sname，course.cname
Connect stu _ corson student.sno = stu _ cors.stuno from the students.
The left external connection course on stu _ cors.corsno = course.cno.
Sort by students. no
go to
-Give yourself an example, and realize an example with any and all predicates, one for each.
-Look up the names and ages of students from other departments who are younger than all the students in the astronomy department.
Select name, age and department number.
From students
Where the age is < any (select students. age
Join a department from within students on student.deptno=department.dno.
Where department.dname=' astronomical department') and
Deptno & lt& gt (Select dno from departments, where dname =' Astronomy Department')
go to
-Query the information of all students. The query results are arranged in ascending order by the department name of the department, and the students in the same department are arranged in descending order by age.
select student.sno，student.sname，department.dname，student.ssex，student.age
From students, departments
Where student.deptno = department.dno
Sort by department. Name ASC, student. Age DESC
go to
-Query the average score of students whose number of courses is equal to or greater than the number of students and the number of courses.
Choose stun, convert (numeric (8 8,2), AVG (score)) as "average grade" and COUNT(corsno) as "number of courses".
From fraction
Group by stuno having (count (corsno) > 2) is ordered by stuno.
go to
-Inquire about the course scores of students majoring in computer science who have taken data structure courses, and request to list the students' names, course names and grades.
select student.sname，course.cname，score.score
Students' internal connection scores from student.sno=score.stuno
Internal link courses on score.corsno=course.cno
In which course.cname='c 199' is ordered by students.
go to
-Inquire about students' course selection, including those who have taken classes and those who have not.
Select students. Sname, count (score. Corsno) as "course selection status"
Add the score on student.sno=score.stuno from the students' left.
Group by students. user name
go to
-Inquire about the student numbers and grades of students who have participated in the "C06" course and whose grades are higher than the average grade of this course.
Select stuno, corsno and score.
From fraction
Where corsno= (choose cno from the course, where cname='c 100') and
Score & gt
-Delete the course selection records of all students who failed the computer department.
Select id, stuno, corsno, score.
From fraction
where score & lt 10
go to
-Create a course selection view view view00 1 for computer majors, including student number, name, gender, age, department, course number, course name and course selection results.
Create view view score
be like
select student.sno，student.sname，student.ssex，student.age，department.dname，course.cno，course.cname，score.score
From students, departments, grades, courses
Among them, student.deptno = department.dno and student.sno=score.stuno and score.corsno = course.cno are not sorted by student.sno.
go to
-Create an index on the age column of the student table, named ix_age.
Create an index ix_age for students (ages)
go to