必看,經典sql面試題1(學生表_課程表_成績表_教師表)
表結構:
Student(S#,Sname,Sage,Ssex) 學生表
Course(C#,Cname,T#) 課程表
SC(S#,C#,score) 成績表
Teacher(T#,Tname) 教師表
建表語句 :
CREATE TABLE student ( s# INT, sname nvarchar(32), sage INT, ssex nvarchar(8) ) CREATE TABLE course ( c# INT, cname nvarchar(32), t# INT ) CREATE TABLE sc ( s# INT, c# INT, score INT ) CREATE TABLE teacher ( t# INT, tname nvarchar(16) )
插入測試資料語句 :
insert into Student select 1,N'劉一',18,N'男' union all select 2,N'錢二',19,N'女' union all select 3,N'張三',17,N'男' union all select 4,N'李四',18,N'女' union all select 5,N'王五',17,N'男' union all select 6,N'趙六',19,N'女'
insert into Teacher select 1,N'葉平' union all select 2,N'賀高' union all select 3,N'楊豔' union all select 4,N'周磊'
insert into Course select 1,N'語文',1 union all select 2,N'數學',2 union all select 3,N'英語',3 union all select 4,N'物理',4
insert into SC select 1,1,56 union all select 1,2,78 union all select 1,3,67 union all select 1,4,58 union all select 2,1,79 union all select 2,2,81 union all select 2,3,92 union all select 2,4,68 union all select 3,1,91 union all select 3,2,47 union all select 3,3,88 union all select 3,4,56 union all select 4,2,88 union all select 4,3,90 union all select 4,4,93 union all select 5,1,46 union all select 5,3,78 union all select 5,4,53 union all select 6,1,35 union all select 6,2,68 union all select 6,4,71
問題:
1、查詢“001”課程比“002”課程成績高的所有學生的學號; select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#;
2、查詢平均成績大於60分的同學的學號和平均成績; select S#,avg(score) from sc group by S# having avg(score) >60;
3、查詢所有同學的學號、姓名、選課數、總成績; select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname
4、查詢姓“李”的老師的個數; select count(distinct(Tname)) from Teacher where Tname like '李%';
5、查詢沒學過“葉平”老師課的同學的學號、姓名; select Student.S#,Student.Sname from Student where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平');
6、查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名; select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名; select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='葉平'));
8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名; Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;
9、查詢所有課程成績小於60分的同學的學號、姓名; select S#,Sname from Student where S# not in (select S.S# from Student AS S,SC where S.S#=SC.S# and score>60);
10、查詢沒有學全所有課的同學的學號、姓名; select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名; select distinct S#,Sname from Student,SC where Student.S#=SC.S# and SC.C# in (select C# from SC where S#='1001');
12、查詢至少學過學號為“001”同學所有一門課的其他同學學號和姓名; select distinct SC.S#,Sname from Student,SC where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績; update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平');
14、查詢和“1002”號的同學學習的課程完全相同的其他同學學號和姓名; select S# from SC where C# in (select C# from SC where S#='1002') group by S# having count(*)=(select count(*) from SC where S#='1002');
15、刪除學習“葉平”老師課的SC表記錄; Delete SC from course ,Teacher where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';
16、向SC表中插入一些記錄,這些記錄要求符合以下條件:沒有上過編號“003”課程的同學學號、2、 號課的平均成績; Insert SC select S#,'002',(Select avg(score) from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成績從高到低顯示所有學生的“資料庫”、“企業管理”、“英語”三門的課程成績,按如下形式顯示: 學生ID,,資料庫,企業管理,英語,有效課程數,有效平均分 SELECT S# as 學生ID ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 資料庫 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業管理 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英語 ,COUNT(*) AS 有效課程數, AVG(t.score) AS 平均成績 FROM SC AS t GROUP BY S# ORDER BY avg(t.score)
18、查詢各科成績最高和最低的分:以如下形式顯示:課程ID,最高分,最低分 SELECT L.C# As 課程ID,L.score AS 最高分,R.score AS 最低分 FROM SC L ,SC AS R WHERE L.C# = R.C# and L.score = (SELECT MAX(IL.score) FROM SC AS IL,Student AS IM WHERE L.C# = IL.C# and IM.S#=IL.S# GROUP BY IL.C#) AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.C# = IR.C# GROUP BY IR.C# ); 自己寫的:select c# ,max(score)as 最高分 ,min(score) as 最低分 from dbo.sc group by c#
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69942496/viewspace-2653232/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 必看,經典sql面試題2(學生表_課程表_成績表_教師表)SQL面試題
- --查詢班級表、課程表、學生選課表、學生表、成績表、15(3+2)計算
- 經典教師 學生 成績sql面試題再次來襲2SQL面試題
- sql 經典面試題及答案(選課表)SQL面試題
- DH-SQL(學生資訊表-課程表-選課表)SQL
- 日程表、學生課表外掛
- 經典SQL面試題1SQL面試題
- 當mysql表從壓縮表變成普通表會發生什麼MySql
- SQL面試題 三(單表、多表查詢)SQL面試題
- 動手擼一個微信小程式學生課程表頁面微信小程式
- sql_彙總課堂參與資訊表到課堂評分表SQL
- oracle臨時表的經典學習資料Oracle
- excel按成績高低排名次 excel成績表怎麼排名次Excel
- 分庫分表經典15連問
- [多表查詢] 班級表,學生表。 學生表有 3 個 a_cid. b_cid, c_cid,如何兩表查?
- 微課sql最佳化(12)、表的連線方法(1)-幫助網友最佳化報表SQLSQL
- 學習動態效能表(三)-(1)-v$sqlSQL
- 教學工作表製作軟體
- 【SQL 學習】表連線SQL
- 初學SQL-交叉表SQL
- Oracl 發生鎖表--解鎖sqlSQL
- Excel怎麼做好看的課程表?Excel
- SQL 經典回顧:JOIN 表連線操作不完全指南SQL
- OCP課程11:SQL之使用DDL語句建立和管理表SQL
- 小學教師薪水最高的地方(附原資料表)
- 力扣刷題筆記:207. 課程表力扣筆記
- 查詢各課程成績第一的學生的資訊
- mysql重點題目--查詢“01“課程比“02“課程成績高的學生的資訊及課程分數MySql
- 經典SQL面試題2SQL面試題
- Java Swing+Mysql+beautyEye(介面優美)學校成績管理系統(管理員/學生/教師,資訊管理/選課管理/成績管理)JavaMySql
- LeetCode題解(0210):課程表II(Python)LeetCodePython
- 兩分鐘搞定阿里SQL面試題:億級表合併阿里SQL面試題
- 財務分析經典圖表及製作方法
- C++實現學生成績資訊登記表C++
- 商品SKU表SQLSQL
- SQL表的建立SQL
- SQL乘法表SQL
- PL/SQL 索引表SQL索引