Basic SQL

==== 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, Grade
FROM Student, SC
WHERE Student.Sno=SC.Sno(*);

35.
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND
 SC.Cno = '2' AND
 SC.Grade>90;

36.
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Courese.Cno;

37.
SELECT Sno, Sname,Sdept
FROM Student
WHERE Sdept IN
 (
 SELECT Sdept
 FROM Student
 WHERE Sname = 'Nancy'; 
 )

SELECT Sno, Sname,Sdept
FROM Student
WHERE Sdept =
 (
 SELECT Sdept
 FROM Student
 WHERE Sname = 'Nancy'; 
 )

38.
SELECT Sno, Sname
FROM Student
WHERE Sno IN
 (
 SELECT Sno
 FROM SC
 WHERE Cno IN
  (
  SELECT Cno
  FROM Course
  WHERE Cname='CS'
  )
 );

39.
SELECT Sname, Sage
FROM Student
WHERE Sage<ANY(
  SELECT Sage
  FROM Student
  WHERE Sdept='IS'
  )
 AND Sdept<>'IS';

40.
SELECT Sname, Sage
FROM Student
WHERE Sage<ALL(
  SELECT Sage
  FROM Student
  WHERE Sdept='IS'
  )
 AND Sdept<>'IS';

41.
SELECT Sname
FROM Student
WHERE EXISTS(
  SELECT *
  FROM SC
  WHERE Sno=Student.Sno AND Cno='1'
  );

42.
SELECT Sname
FROM Student
WHERE NOT EXISTS(
  SELECT *
  FROM SC
  WHERE Sno = Student.Sno AND Cno='1'
  );

==== Data Update ====

1.
INSERT
INTO Student
VALUES ('95020', 'Chendong', 'male', 'IS', 18);

2.
INSERT
INTO SC(Sno, Cno)
VALUES('95020', '1');

3.
CREATE TABLE Deptage
 (
 Sdept CHAR(15)
 Avage SMALLINT
 );
INSERT
INTO Deptage(Sdept, Avgage)
 SELECT Sdept, AVG(Sage)
 FROM Student
 GROUP BY Sdept;

4.
UPDATE Student
SET Sage=22
WHERE Sno='95001';

5.
UPDATE Student
SET Sage=Sage+1;

6.
UPDATE SC
SET Grade=0
WHERE 'CS'=
 (
 SELECT Sdept
 FROM Student
 WHERE Student.Sno=SC.Sno
 );

7.
DELETE
FROM Student
WHERE Sno='95019';

8.
DELETE
FROM SC;

9.
DELETE
FROM SC
WHERE 'CS'=
 (
 SELECT Sdept
 FROM Student
 WHERE Student.Sno=SC.Sno
 );