N2ttsbLB

1/********************************************************************
2Lab 0 tables creating
3********************************************************************/
4DROP TABLE IF EXISTS STUDENT;
5CREATE TABLE STUDENT (
6  Sno INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique id',
7  Sname VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'name',
8  Ssex ENUM('M','F') NOT NULL DEFAULT 'M' COMMENT 'sex',
9  Sage INT(5) UNSIGNED NOT NULL DEFAULT 18 COMMENT 'age',
10  Sdept VARCHAR(3) NOT NULL DEFAULT '' COMMENT 'WTF',
11  PRIMARY KEY (Sno)
12) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student table';
13
14DROP TABLE IF EXISTS COURSE;
15CREATE TABLE COURSE(
16  Cno INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique id',
17  Cname VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'course name',
18  Cpno INT(5) DEFAULT 0 COMMENT 'wtf',
19  Ccredit INT(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'wtf',
20  PRIMARY KEY (Cno)
21) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='course table';
22
23DROP TABLE IF EXISTS SC;
24CREATE TABLE SC(
25  Sno INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique student id',
26  Cno INT(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'course id',
27  Grade INT(5) DEFAULT '0' COMMENT 'grade',
28  PRIMARY KEY (Sno,Cno)
29) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='course table';
30
31DROP TABLE IF EXISTS S;
32CREATE TABLE S(
33  Sno VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'unique id',
34  Sname VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'supplier name',
35  City VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'grade'
36) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='supplier table';
37
38DROP TABLE IF EXISTS P;
39CREATE TABLE P(
40  Pno VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'unique id',
41  Pname VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'supplier name',
42  Color VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'wtf',
43  Weight INT(5) NOT NULL DEFAULT '1' COMMENT 'weight'
44) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='component table';
45
46DROP TABLE IF EXISTS J;
47CREATE TABLE J(
48  Jno VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'unique id',
49  Jname VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'project name',
50  City VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'city'
51) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='project table';
52
53DROP TABLE IF EXISTS SPJ;
54CREATE TABLE SPJ(
55  Sno VARCHAR(255) NOT NULL COMMENT 'supplier id',
56  Pno VARCHAR(255) NOT NULL DEFAULT '0' COMMENT 'component id',
57  Jno VARCHAR(255) NOT NULL DEFAULT '0' COMMENT 'project id',
58  QTY INT(5) NOT NULL DEFAULT '0' COMMENT 'WTF'
59) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='supplier component project joint table';
60
61/********************************************************************
62Lab 1 insert data into table
63********************************************************************/
64BEGIN;
65INSERT INTO STUDENT VALUES
66  (95001,'李勇' ,'M' ,20, 'CS'),
67  (95002,'刘晨' ,'F' ,19, 'IS'),
68  (95003,'王敏' ,'F' ,18, 'MA'),
69  (95004,'å¼ ç«‹' ,'M' ,18, 'IS');
70
71INSERT INTO COURSE VALUES
72  (1,'数据库 ' ,5,4),
73  (2,'æ•°å­¦  ' , NULL ,2),
74  (3,'信息系统' ,  1, 4),
75  (4,'操作系统' ,  6 ,3),
76  (5,'数据结构' ,  7 ,4),
77  (6,'数据处理' ,  NULL , 2),
78  (7,'C语言 ' ,6 ,4);
79
80INSERT INTO SC VALUES
81  (95001,1,92),
82  (95001,2,85),
83  (95001,3,88),
84  (95002,2,90),
85  (95002,3,80);
86
87INSERT INTO S VALUES
88  ('S1','精益','天津'),
89  ('S2','万胜','北京'),
90  ('S3','东方','北京'),
91  ('S4','丰泰隆','上海'),
92  ('S5','康健	','南京');
93
94
95INSERT INTO P VALUES
96  ('P1','螺母 ','红' ,12),
97  ('P2','螺栓 ','绿' ,17),
98  ('P3','螺丝刀','蓝' ,14),
99  ('P4','螺丝刀','红' ,14),
100  ('P5','凸轮 ','蓝' ,40),
101  ('P6','齿轮 ','红' ,30);
102
103INSERT INTO J VALUES
104  ('J1','三建','北京'),
105  ('J2','一汽 ','长春'),
106  ('J3','弹簧厂','天津'),
107  ('J4','造船厂','天津'),
108  ('J5','机车厂','唐山'),
109  ('J6','无线电厂','常州'),
110  ('J7','半导体厂','南京');
111
112INSERT INTO SPJ VALUES
113  ('S1' , 'P1', 'J1', 200),
114  ('S1' , 'P1', 'J3', 100),
115  ('S1' , 'P1', 'J4', 700),
116  ('S1' , 'P2', 'J2', 100),
117  ('S2' , 'P3', 'J1', 400),
118  ('S2' , 'P3', 'J2', 200),
119  ('S2' , 'P3', 'J4', 500),
120  ('S2' , 'P3', 'J5', 400),
121  ('S2' , 'P5', 'J1', 400),
122  ('S2' , 'P5', 'J2', 100),
123  ('S3' , 'P1', 'J1', 200),
124  ('S3' , 'P3', 'J1', 200),
125  ('S4' , 'P5', 'J1', 100),
126  ('S4' , 'P6', 'J3', 300),
127  ('S4' , 'P6', 'J4', 200),
128  ('S5' , 'P2', 'J4', 100),
129  ('S5' , 'P3', 'J1', 200),
130  ('S5' , 'P6', 'J2', 200),
131  ('S5' , 'P6', 'J4', 500);
132COMMIT ;
133/********************************************************************
134Lab 2 querying within tables(student,course,sc)
135********************************************************************/
136SELECT Sno,Sname FROM STUDENT WHERE Sdept='MA'; #1
137
138SELECT Sno FROM SC GROUP BY Sno;#2
139
140SELECT Sno,Grade FROM SC AS sc LEFT JOIN COURSE AS c ON sc.Cno = c.Cno WHERE c.Cname='æ•°å­¦' ORDER BY Grade DESC,Sno ASC;#3
141
142SELECT Sno,Grade*0.8 FROM SC AS sc LEFT JOIN COURSE AS c ON sc.Cno = c.Cno WHERE c.Cname='æ•°å­¦' AND (sc.Grade>=80 AND sc.Grade<=90);#4
143
144SELECT * FROM STUDENT WHERE Sname LIKE '刘%' AND (Sdept='CS' OR Sdept='MA');#5
145
146SELECT Sno,Cno FROM STUDENT AS st INNER JOIN COURSE AS co WHERE st.Sno NOT IN (SELECT Sno FROM SC GROUP BY Sno) OR co.Cno NOT IN(SELECT Cno FROM SC);#6inefficient
147
148SELECT st.Sno,st.Sname,st.Ssex,st.Sage,st.Sdept,sc.Cno FROM STUDENT AS st LEFT JOIN SC AS sc ON st.Sno=sc.Sno;#7
149
150SELECT st.Sno,st.Sname,sc.Cno,sc.Grade FROM STUDENT AS st LEFT JOIN SC AS sc ON st.Sno=sc.Sno;#8 almost ditto
151
152SELECT st.Sno,st.Sname,sc.Grade FROM STUDENT AS st LEFT JOIN SC AS sc ON st.Sno=sc.Sno WHERE sc.Cno=(SELECT Cno FROM COURSE WHERE Cname='æ•°å­¦') AND sc.Grade>=90 ;#9
153
154#10 wtf?
155
156/********************************************************************
157Lab 3 querying within tables(s,p,j,spj)
158********************************************************************/
159SELECT Sno,Jno FROM SPJ WHERE Jno='J1' AND Pno='P1'; #1
160
161#2 duplicate with #1
162
163SELECT Pno,QTY i_am_not_sure_whether_qty_means_total_supplies FROM SPJ GROUP BY Pno; #3
164
165/********************************************************************
166Lab 4 advanced querying within tables(student,course,sc)
167********************************************************************/
168SELECT st.Sno,st.Sname FROM STUDENT AS st WHERE st.Sno IN (SELECT Sno FROM SC AS sc WHERE sc.Cno=(SELECT Cno FROM COURSE AS co WHERE co.Cname='æ•°å­¦'));#1
169
170SELECT sc.Sno,sc.Grade FROM SC sc WHERE sc.Sno>=(SELECT Sno FROM STUDENT st WHERE st.Sname='李勇') AND sc.Cno=(SELECT co.Cno FROM COURSE co WHERE co.Cname='数学');#2
171
172SELECT * FROM STUDENT WHERE Sdept!='CS' AND Sage<=(SELECT Sage FROM STUDENT WHERE Sdept='CS' ORDER BY Sage DESC LIMIT 1);#3
173
174SELECT * FROM STUDENT WHERE Sdept!='CS' AND Sage<=(SELECT Sage FROM STUDENT WHERE Sdept='CS' ORDER BY Sage ASC LIMIT 1);#3
175
176SELECT Sname FROM STUDENT st LEFT JOIN SC sc ON st.Sno=sc.Sno WHERE sc.Cno=(SELECT Cno FROM COURSE WHERE Cname='æ•°å­¦');#4
177
178SELECT Sname FROM STUDENT st LEFT JOIN SC sc ON st.Sno=sc.Sno WHERE sc.Cno!=(SELECT Cno FROM COURSE WHERE Cname='æ•°å­¦') GROUP BY Sname;#5
179
180SELECT st.Sname FROM STUDENT st INNER JOIN (SELECT required.Sno FROM (SELECT *,COUNT(*)=(SELECT COUNT(*) FROM COURSE) slected_all FROM SC sc GROUP BY SC.Sno) required WHERE required.slected_all=1) result ON st.Sno=result.Sno;#7
181
182SELECT st.Sname,st.Sno FROM STUDENT st INNER JOIN (SELECT * FROM ( SELECT *,COUNT(Cno)=(SELECT COUNT(Cno) FROM SC WHERE Sno='95002') cond FROM SC GROUP BY Cno HAVING Cno IN (SELECT Cno FROM SC WHERE Sno='95002')) required WHERE required.cond=1 GROUP BY Sno) result ON st.Sno=result.Sno;#8
183
184SELECT co.Cno,AVG(sc.Grade) FROM COURSE co LEFT JOIN SC sc ON co.Cno=sc.Cno GROUP BY co.Cno;#9
185
186SELECT st.Sno,required.avg_grade FROM STUDENT st JOIN (SELECT Sno,AVG(Grade) avg_grade,COUNT(Cno) count_cno FROM SC sc WHERE Grade>=60 GROUP BY Sno) required ON st.Sno=required.Sno WHERE required.count_cno>=2 AND required.avg_grade>=60;#10
187
188SELECT st.Sno,required.avg_grade FROM STUDENT st JOIN (SELECT Sno,AVG(Grade) avg_grade,COUNT(Cno) count_cno FROM SC sc WHERE Grade>=60 GROUP BY Sno) required ON st.Sno=required.Sno WHERE required.count_cno>=2 AND required.avg_grade>=60 ORDER BY required.avg_grade DESC;#11
189
190SELECT COUNT(*) pass_count,AVG(sc.Grade) avg_grade FROM SC sc WHERE sc.Grade>=60 GROUP BY sc.Sno ORDER BY avg_grade DESC,pass_count DESC;#12
191
192/********************************************************************
193Lab 5 advanced querying within tables(s,p,j,spj)
194********************************************************************/
195SELECT * FROM SPJ WHERE Jno='J1' AND Pno IN (SELECT p.Pno FROM P p WHERE p.Color='红');#1
196
197SELECT S.Sname FROM S LEFT JOIN (SELECT Sno,SUM(SPJ.QTY) sum_supplies FROM SPJ GROUP BY SPJ.Sno) sumCal ON S.Sno=sumCal.Sno WHERE sumCal.sum_supplies>=1000;#2