==== Table Operation ====
1.CREATE TABLE Student( Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) UNIQUE, SSex CHAR(1), Sage INT, Sdept CHAR(15),);
2.ALTER TABLE Student ADD Scome DATE;
ALTER TABLE Student MODIFY Sage SMALLINT;
ALTER TABLE Student DROP UNIQUE(Sname);
3.DROP TABLE Student;
==== Index ====
1.CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
2.DROP INDEX Stusname;
==== Query ====
1.SELECT Sno, Sname FROM Student;
2.SELECT Sname, Sno, Sdept FROM Student;
3.SELECT * FROM Student;
4.SELECT Sname, 1996-Sage FROM Student;
5.SELECT Sname, 'Year fo Birth:', 1996-Sage, ISLOWER(Sdept) FROM Student;
SELECT Sname NAME, 'Year of Birth:'BIRTH, 1996-Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENT FROM Student;
6.SELECT DISTINCT Sno FROM SC;
SELECT ALL Sno FROM SC;
SELECT Sno FROM SC;
7.SELECT Sname FROM Student WHERE Sdept='CS';
8.SELECT Sname, Sage FROM Student WHERE Sage<20;
SELECT Sname, Sage FROM Student WHERE NOT Sage>=20;
9.SELECT DISTINCT Sno FROM Course WHERE Grade<60;
10.SELECT Sname, Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
11.SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
12.SELECT Sname, Ssex FROM Student WHERE Sdept IN ('IS', 'MA', 'CS');
13.SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('IS', 'MA', 'CS');
14.SELECT * FROM Student WHERE Sno LIKE '95001';
15.SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE 'Liu%';
16.SELECT Sname FROM Student WHERE Sname LIKE 'DAVID_ _';
17.SELECT Sname, Sno FROM Student WHERE Sname LIKE '_ _David%';
18.SELECT Sname FROM Student WHERE Sname NOT LIKE 'Liu%';
19.SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE'\';
20.SELECT * FROM Course WHERE Cname LIKE 'DB\_%i_ _' ESCAPE'\';
21.SELECT Sno, Cno FROM SC WHERE Grade IS NULL;
22.SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
23.SELECT Sname FROM Student WHERE Sdept='CS' AND Sage<20;
SELECT Sname, Ssex FROM Student WHERE Sdept='IS' OR Sdept='MA' OR Sdept='CS';
24.SELECT Sno, Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;
25.SELECT * FROM Student ORDER BY Sdept, Sage DESC;
26.SELECT COUNT(*) FROM Student;
27.SELECT COUNT(DISTINCT Sno) FROM SC;
28.SELECT AVG(Grade) FROM SC WHERE Cno='1';
29.SELECT MAN(Grade) FROM SC WHERE Cno='1';
30.SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;
31.SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3;
32.SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno;
33.SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student,Sno=SC.Sno;
34.SELECT FIRST>Cno, SECOND>Cpno FROM Course FIRST, Courese SECOND WHERE FIRST.Cpno=SECOND.Cno;
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SCWHERE Student.Sno=SC.Sno(*);
35.SELECT Student.Sno, SnameFROM Student, SCWHERE Student.Sno = SC.Sno AND SC.Cno = '2' AND SC.Grade>90;
36.SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, CourseWHERE Student.Sno = SC.Sno AND SC.Cno = Courese.Cno;
37.SELECT Sno, Sname,Sdept FROM StudentWHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname = 'Nancy'; )
SELECT Sno, Sname,Sdept FROM StudentWHERE Sdept = ( SELECT Sdept FROM Student WHERE Sname = 'Nancy'; )
38.SELECT Sno, SnameFROM StudentWHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname='CS' ) );
39.SELECT Sname, SageFROM StudentWHERE Sage<ANY( SELECT Sage FROM Student WHERE Sdept='IS' ) AND Sdept<>'IS';
40.SELECT Sname, SageFROM StudentWHERE Sage<ALL( SELECT Sage FROM Student WHERE Sdept='IS' ) AND Sdept<>'IS';
41.SELECT SnameFROM StudentWHERE EXISTS( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1' );
42.SELECT SnameFROM StudentWHERE NOT EXISTS( SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1' );
==== Data Update ====
1.INSERT INTO StudentVALUES ('95020', 'Chendong', 'male', 'IS', 18);
2.INSERTINTO SC(Sno, Cno)VALUES('95020', '1');
3.CREATE TABLE Deptage ( Sdept CHAR(15) Avage SMALLINT );INSERTINTO Deptage(Sdept, Avgage) SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept;
4.UPDATE StudentSET Sage=22WHERE Sno='95001';
5.UPDATE StudentSET Sage=Sage+1;
6.UPDATE SCSET Grade=0WHERE 'CS'= ( SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno );
7.DELETEFROM StudentWHERE Sno='95019';
8.DELETE FROM SC;
9.DELETE FROM SCWHERE 'CS'= ( SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno );
相关资源：Basic SQL Language