Mysql面试题2.doc

Mysql面试题2 涓嶉敊鐨剆ql闈㈣瘯棰樎?聽 Student(s_no,sname,age,sex) 瀛︾敓琛? Course(c_no,cname,t_no) 璇剧▼琛? SC(s_no,c_no,score) 鎴愮哗琛? Teacher(t_no,Tname) 鏁欏笀琛? 闂锛? 1銆佹煡璇⑩€?01鈥濊绋嬫瘮鈥?02鈥濊绋嬫垚缁╅珮鐨勬墍鏈夊鐢熺殑瀛﹀彿锛? 聽聽select a.s_no from (select s_no,score from SC where c_no=001) a,(select s_no,score 聽聽from SC where c_no=002) b 聽聽where a.scoreb.score and a.s_no=b.s_no; 2銆佹煡璇㈠钩鍧囨垚缁╁ぇ浜?0鍒嗙殑鍚屽鐨勫鍙峰拰骞冲潎鎴愮哗锛? 聽 聽 select s_no,avg(score) 聽 聽 from sc 聽 聽 group by s_no having avg(score) 60; 3銆佹煡璇㈡墍鏈夊悓瀛︾殑瀛﹀彿銆佸鍚嶃€侀€夎鏁般€佹€绘垚缁╋紱 聽聽select Student.s_no,Student.sname,count(SC.c_no),sum(score) 聽聽from Student left Outer join SC on Student.s_no=SC.s_no 聽聽group by Student.s_no,sname 4銆佹煡璇㈠鈥滄潕鈥濈殑鑰佸笀鐨勪釜鏁帮紱 聽聽select count(distinct(Tname)) 聽聽from Teacher 聽聽where Tname like 鏉?; 5銆佹煡璇㈡病瀛﹁繃鈥滃彾骞斥€濊€佸笀璇剧殑鍚屽鐨勫鍙枫€佸鍚嶏紱 聽 聽 select Student.s_no,Student.sname 聽 聽 from Student聽聽 聽 聽 where s_no not in (select distinct( SC.s_no) from SC,Course,Teacher where聽聽SC.c_no=Course.c_no and Teacher.t_no=Course.t_no and Teacher.Tname=鍙跺钩); 6銆佹煡璇㈠杩団€?01鈥濆苟涓斾篃瀛﹁繃缂栧彿鈥?02鈥濊绋嬬殑鍚屽鐨勫鍙枫€佸鍚嶏紱 聽聽select Student.s_no,Student.sname from Student,SC where Student.s_no=SC.s_no and SC.c_no=001and exists( Select * from SC as SC_2 where SC_2.s_no=SC.s_no and SC_2.c_no=002); 7銆佹煡璇㈠杩団€滃彾骞斥€濊€佸笀鎵€鏁欑殑鎵€鏈夎鐨勫悓瀛︾殑瀛﹀彿銆佸鍚嶏紱 聽聽select s_no,sname 聽聽from Student 聽聽where s_no in (select s_no from SC ,Course ,Teacher where SC.c_no=Course.c_no and Teacher.t_no=Course.t_no and Teacher.Tname=鍙跺钩 group by s_no having count(SC.c_no)=(select count(c_no) from Course,Teacher聽聽where Teacher.t_no=Course.t_no and Tname=鍙跺钩)); 8銆佹煡璇㈣绋嬬紪鍙封€?02鈥濈殑鎴愮哗姣旇绋嬬紪鍙封€?01鈥濊绋嬩綆鐨勬墍鏈夊悓瀛︾殑瀛﹀彿銆佸鍚嶏紱 聽聽Select s_no,sname from (select Student.s_no,Student.sname,score ,(select score from SC SC_2 where SC_2.s_no=Student.s_no and SC_2.c_no=002) score2 聽聽from Student,SC where Student.s_no=SC.s_no and c_no=001) S_2 where score2 60); 10銆佹煡璇㈡病鏈夊鍏ㄦ墍鏈夎鐨勫悓瀛︾殑瀛﹀彿銆佸鍚嶏紱 聽 聽 select Student.s_no,Student.sname 聽 聽 from Student,SC 聽 聽 where Student.s_no=SC.s_no group by聽聽Studen