数据库语句练习

查找练习

SELECT student.*,SC.* FROM student, sc 
WHERE student.sno = sc.Sno

SELECT student.sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM student,sc 
WHERE student.sno = SC.sno

SELECT student.sno  FROM student,sc 
WHERE student.sno = SC.sno

SELECT FIRST.cno,SECOND.Cpno FROM course FIRST,course SECOND 
WHERE FIRST.cpno = SECOND.cno

SELECT student.sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM student 
LEFT OUTER JOIN SC ON (Student.Sno = SC.cno)

SELECT student.sno,Sname,Cname,Grade FROM student,sc,course 
WHERE student.sno = SC.sno AND SC.cno = course.Cno

SELECT Sname FROM student 
WHERE sno IN (SELECT sno FROM sc WHERE cno = '2')

SELECT Sno,Sname,Sdept FROM student 
WHERE sdept IN (SELECT sdept FROM student WHERE sname = '����')

SELECT S1.Sno ,S1.Sname,S1.sdept FROM student S1, student S2 
WHERE S1.sdept = S2.sdept AND S2.sname =  '����'

SELECT Sno,Sname FROM student 
WHERE sno IN
	(SELECT sno FROM sc
	WHERE cno IN (
		SELECT cno FROM course WHERE cname = '��Ϣϵͳ')) 

SELECT Student.Sno,Sname FROM student,sc,course 
WHERE student.sno = SC.sno AND SC.Cno = course.cno AND course.cname = '��Ϣϵͳ'

SELECT Sno,Sname,Sdept FROM student 
WHERE sdept = (SELECT sdept FROM student WHERE sname = '����')

SELECT Sno,Cno FROM sc x 
WHERE grade >= (SELECT AVG(grade) FROM sc Y WHERE Y.sno = X.sno)

SELECT Sname,Sage FROM student 
WHERE sage <ANY(SELECT sage From student WHERE sdept = 'CS')

SELECT Sname,Sage FROM student 
WHERE sage < (SELECT MAX(sage) From student WHERE sdept = 'CS')

SELECT Sname,Sage FROM student 
WHERE sage <ALL(SELECT sage From student WHERE sdept = 'CS') AND sdept <> 'CS'

SELECT Sname,Sage FROM student 
WHERE sage < (SELECT MIN(sage) From student WHERE sdept = 'CS')

SELECT Sname FROM student 
WHERE EXISTS(SELECT * FROM sc WHERE sno = student.sno AND cno = '2')

SELECT Sname FROM student 
WHERE NOT EXISTS(SELECT * FROM sc WHERE sno = student.sno AND cno = '2')

SELECT Sno,Sname,Sdept FROM student S1 
WHERE EXISTS
 	(SELECT * FROM student S2 WHERE S2.sdept = S1.sdept AND S2.sdept = S1.sdept AND S2.Sname = '����'