SQL面試筆試經典題(Part 1)

来源:http://www.cnblogs.com/hemiy/archive/2016/12/18/6069274.html
-Advertisement-
Play Games

本文是在Cat Qi的原貼的基礎之上，經本人逐題分別在MySql資料庫中實現的筆記，持續更新... 參考原貼：http://www.cnblogs.com/qixuejia/p/3637735.html 01 表結構 Student(Sno,Sname,Sage,Ssex) 學生表 Course(C ...


本文是在Cat Qi的原貼的基礎之上，經本人逐題分別在MySql資料庫中實現的筆記，持續更新...

參考原貼：http://www.cnblogs.com/qixuejia/p/3637735.html 

01 表結構

　　Student(Sno,Sname,Sage,Ssex) 　　 學生表 
　　Course(Cno,Cname,Tno) 　　　　　 課程表 
　　SC(Sno,Cno,score) 　　　　　　　　 成績表 
　　Teacher(Tno,Tname) 　　　　　　　教師表

02 建表及插入測試數據

　　（1） 建表：

 1 DROP TABLE IF EXISTS student ;
 2 DROP TABLE IF EXISTS course ;
 3 DROP TABLE IF EXISTS sc ;
 4 DROP TABLE IF EXISTS teacher ;
 5 
 6 CREATE TABLE Student
 7 (
 8 Sno int,
 9 Sname varchar(32),
10 Sage int,
11 Ssex varchar(8)
12 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
13 
14 CREATE TABLE Course 
15   ( 
16      Cno    INT, 
17      Cname varchar(32), 
18      Tno    INT 
19   )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
20 
21 CREATE TABLE Sc 
22 ( 
23 Sno    INT, 
24 Cno   INT,
25 score INT 
26 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
27 
28 CREATE TABLE Teacher 
29   ( 
30      Tno    INT, 
31      Tname varchar(16) 
32   )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
View Code

　　【註】MySQL資料庫建表時需要添加“ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci”命令，否則中文會發生亂碼。

　　（2） 插入測試數據：

 1  insert into Student select 1,'劉一',18,'男' union all
 2  select 2,'錢二',19,'女' union all
 3  select 3,'張三',17,'男' union all
 4  select 4,'李四',18,'女' union all
 5  select 5,'王五',17,'男' union all
 6  select 6,'趙六',19,'女' 
 7  
 8  insert into Teacher select 1,'葉平' union all
 9  select 2,'賀高' union all
10  select 3,'楊艷' union all
11  select 4,'周磊';
12  
13  insert into Course select 1,'語文',1 union all
14  select 2,'數學',2 union all
15  select 3,'英語',3 union all
16  select 4,'物理',4;
17  
18  insert into SC 
19  select 1,1,56 union all 
20  select 1,2,78 union all 
21  select 1,3,67 union all 
22  select 1,4,58 union all 
23  select 2,1,79 union all 
24  select 2,2,81 union all 
25  select 2,3,92 union all 
26  select 2,4,68 union all 
27  select 3,1,91 union all 
28  select 3,2,47 union all 
29  select 3,3,88 union all 
30  select 3,4,56 union all 
31  select 4,2,88 union all 
32  select 4,3,90 union all 
33  select 4,4,93 union all 
34  select 5,1,46 union all 
35  select 5,3,78 union all 
36  select 5,4,53 union all 
37  select 6,1,35 union all 
38  select 6,2,68 union all 
39  select 6,4,71;
View Code

03 問題及實現代碼

　　（1）查詢“1”課程比“2”課程成績高的所有學生的學號； 

select a.sno from
(select sno,score from sc where cno=1) a,
(select sno,score from sc where cno=2) b
where a.sno=b.sno and a.score>b.score;

 　　（2）查詢平均成績大於60分的同學的學號和平均成績； 

 select Sno,AVG(Score) as AvgScore 
 from SC
 group by Sno
 having AVG(Score)>60

 　　（3）查詢所有同學的學號、姓名、選課數、總成績； 

 select student.sno,student.sname,count(sc.cno),sum(sc.score) from
 student left outer join sc
 on student.sno = sc.sno
 group by student.sno
 order by student.sno;

 　　（4）查詢姓“李”的老師的個數； 

 select count(distinct tname) as count
 from teacher
 where tname like '李%';

 　　（5）查詢沒學過“葉平”老師課的同學的學號、姓名；

 select s.sno,s.sname 
 from student s
 where s.sno not in
 (
	select distinct(sc.sno) from sc ,course c,teacher t
	where sc.cno = c.cno and c.tno = t.tno and t.tname = '葉平'
 )

　　（6）查詢學過“1”並且也學過編號“2”課程的同學的學號、姓名；

 select s.sno,s.sname from 
 student s,
 (select sno from sc where cno=1) a,
 (select sno from sc where cno=2) b
 where s.sno = a.sno and a.sno = b.sno;

　　方法二 用exist函數

 select s.Sno,s.Sname
 from Student s,SC sc
 where s.Sno=sc.Sno and sc.Cno=1 and exists
 (
     select * from SC sc2 where sc.Sno=sc2.Sno and sc2.Cno=2
 )

　　（7）查詢學過“葉平”老師所教的所有課的同學的學號、姓名；

 select s.sno,s.sname 
 from student s,teacher t,
 course c left outer join sc
 on c.cno = sc.cno 
 where t.tname="葉平" and t.tno = c.cno and s.sno = sc.sno ;

　　或者：

 select s.sno,s.sname 
 from student s
 where s.sno in 
 (
	select sc.sno
	from sc,course c,teacher t
	where c.cno=sc.cno and c.tno=t.tno and t.tname ="葉平"
	group by sc.sno
	having count(sc.cno)=
	(
		select count(c1.cno)
		from course c1,teacher t1
		where c1.tno=t1.tno and t1,tname ="葉平"
	)
 );

　　（8）查詢課程編號“2”的成績比課程編號“1”課程低的所有同學的學號、姓名；

 select s.sno,s.sname
 from student s
 where s.sno in
 (
	select a.sno from  
	(select sno,score from sc where cno=2) a,
	(select sno,score from sc where cno=1) b
	where a.sno = b.sno and a.score < b.score
 );

　　（9）查詢有課程成績小於60分的同學的學號、姓名；

 

select s.sno,s.sname
 from student s,sc
 where sc.score<60 and s.sno=sc.sno
 group by s.sno;

 

　　（10）查詢沒有學全所有課的同學的學號、姓名；

 select s.sno,s.sname
 from student s
 where s.sno not in
 (
	select sc.sno from sc
	group by sc.sno
	having count(distinct sc.cno)=
	(
		select count(distinct c.cno) from course c
	)
 );

　　（11）查詢至少有一門課與學號為“1”的同學所學相同的同學的學號和姓名；

 select distinct(s.sno),s.sname 
 from student s,sc 
 where s.sno=sc.sno and sc.cno in 
 (
	select distinct(cno) from sc where sno=1
 );

　　（12）查詢至少學過學號為“1”同學所有一門課的其他同學學號和姓名；

 select distinct(s.sno),s.sname 
 from student s,sc 
 where s.sno=sc.sno and s.sno != 1 and sc.cno in 
 (
	select distinct(cno) from sc where sno=1
 );

　　（13）把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績；

 

 update sc set score =
 (
	select avg(sc1.score) from sc sc1,course c,teacher t
	where sc1.cno = c.cno and c.tno = t.tno and t.tname="葉平"
 )
 where cno in
 (
	select cno from course c,teacher t 
	where c.tno = t.tno and t.tname="葉平"
 );

　　（14）查詢和“2”號的同學學習的課程完全相同的其他同學學號和姓名；

 select s.sno,s.sname 
 from student s
 where s.sno != 2 and s.sno in
 (
	select distinct(sno) from sc
	where cno in (select cno from sc where sno=2)
	group by sno
	having count(distinct cno)=
	(
		select count(distinct cno) from sc where sno=2
	)
 );

　　（15）刪除學習“葉平”老師課的SC表記錄；

 delete from sc where cno in
 (
	select c.cno from course c,teacher t
	where c.tno = t.tno and t.tname="葉平"
 );

　　（16）向SC表中插入一些記錄，這些記錄要求符合以下條件：①沒有上過編號“2”課程的同學學號作為學號；②將“2”號課程的平均成績作為其成績； 

 insert into sc
 select s.sno,2,(select avg(score) from sc where cno=2)
 from student s
 where s.sno not in (select distinct(sno) from sc where cno=2);

　　（17）按平均成績從低到高顯示所有學生的“語文”、“數學”、“英語”三門的課程成績，按如下形式顯示： 學生ID,語文,數學,英語,有效課程數,有效平均分； 【此處已補回15題中被刪除的數據】 

 select sc0.sno as "學生ID",
 (select score from sc where sno=sc0.sno and cno =1) as "語文" ,
 (select score from sc where sno=sc0.sno and cno =2) as "數學" ,
 (select score from sc where sno=sc0.sno and cno =3) as "英語" ,
 count(sc0.cno) as "有效課程數",
 avg(sc0.score) as "有效平均分"
 from sc sc0
 group by sc0.sno
 order by avg(sc0.score);

　　（18）查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分； 

 select cno as "課程ID",max(score) as "最高分",min(score) as "最低分"
 from sc 
 group by cno;

　　（19）按各科平均成績從低到高和及格率的百分數從高到低順序；

 select sc.cno as "課程ID",
 c.cname as "課程名稱",
 avg(sc.score) as "平均分", 
 100*sum(case when sc.score >= 60 then 1 else 0 end)/count(sc.score) as "Percent(%)"
 from sc ,course c
 where sc.cno = c.cno
 group by sc.cno
 order by avg(sc.score) desc ;

　　（20）查詢如下課程平均成績和及格率的百分數(備註：需要在1行內顯示): 企業管理（2），OO&UML （3），資料庫（4） 

 select
 sum(case when cno=2 then score else 0 end)/sum(case when cno=2 then 1 else 0 end) as "企業管理平均成績",
 100*sum(case when cno=2 and score >= 60 then 1 else 0 end)/sum(case when cno=2 then 1 else 0 end) as "企業管理及格率(%)",
 sum(case when cno=3 then score else 0 end)/sum(case when cno=3 then 1 else 0 end) as "OO&UML平均成績",
 100*sum(case when cno=3 and score >= 60 then 1 else 0 end)/sum(case when cno=3 then 1 else 0 end) as "OO&UML及格率(%)",
 sum(case when cno=4 then score else 0 end)/sum(case when cno=4 then 1 else 0 end) as "資料庫平均成績",
 100*sum(case when cno=4 and score >= 60 then 1 else 0 end)/sum(case when cno=4 then 1 else 0 end) as "資料庫及格率(%)"
 from sc;

　　（21）查詢不同老師所教不同課程平均分從高到低顯示； 

 select t.tname as "老師姓名",
 c.cname as "課程名稱",
 avg(sc.score) as "平均分" 
 from sc,teacher t,course c
 where t.tno=c.tno and c.cno=sc.cno
 group by t.tno
 order by avg(sc.score) desc;

　　（22）查詢如下課程成績第 3 名到第 6 名的學生成績單：企業管理（1），馬克思（2），UML （3），資料庫（4）  

 select distinct 
      SC.Sno As "學生學號", 
      Student.Sname as "學生姓名" , 
      T1.score as "企業管理", 
      T2.score as "馬克思", 
      T3.score as "UML", 
      T4.score as "資料庫", 
      ifnull(T1.score,0) + ifnull(T2.score,0) + ifnull(T3.score,0) + ifnull(T4.score,0) as "總分" 
      from Student,SC  left join SC as T1 
                      on SC.Sno = T1.Sno and T1.Cno = 1
            left join SC as T2 
                      on SC.Sno = T2.Sno and T2.Cno = 2 
            left join SC as T3 
                      on SC.Sno = T3.Sno and T3.Cno = 3 
            left join SC as T4 
                      on SC.Sno = T4.Sno and T4.Cno = 4 
      where student.Sno=SC.Sno 
      order by ifnull(T1.score,0) + ifnull(T2.score,0) + ifnull(T3.score,0) + ifnull(T4.score,0) desc ;

　　

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Html5 直接插入排序
    直接插入排序演算法(Straight Insertion Sort)，是排序演算法中簡單的一種演算法，基本思想如下： 將一個記錄插入到已排序好的有序表中，從而得到一個新，記錄數增1的有序表。即：先將序列的第1個記錄看成是一個有序的子序列，然後從第2個記錄逐個進行插入，直至整個序列有序為止。 要點：設立哨兵 ...
  • React.js入門筆記(再續)：評論框的實現
    本案例來自React.js中文官網對應內容。 一. 運行環境 二. 組件架構 App下有兩個子組件 （評論列表）和 （評論區），其中 下又有一個子組件 （評論） Comment包括一個h2的評論人名稱，一個span的評論內容，獲取數據之後，Comment組件以數組的形式傳入CommentList。 ...
  • node.js處理post請求
    1.html 2.app.js ...
  • Sass學習之路(5)——變數
    1.定義變數:Sass中定義變數的關鍵字是'$'(畢竟程式員缺錢)，並使用冒號(:)進行賦值，例如： $width:200px;//定義了一個名為width的變數，值為200px 2.普通變數和預設變數: 普通變數便是我們在大括弧外用上面的方式聲明的變數，可全局使用。 預設變數需要在聲明的變數後加上 ...
  • 簡單理解冒泡排序
    關於排序，其實不管是哪種語言，都有它內置的排序函數，我們要用的時候調用就行了，既然如此，我們為什麼還要講這個東西呢？我想，其實，我們講排序更多是在於排序中包含的思想演算法，因為，演算法對於電腦來說相當重要，一個好的演算法能夠讓電腦的效率達到事半功倍的效果，所以，演算法是電腦語言中一門相當熱門的課程，它 ...
  • node.js如何處理請求的路由
    var http = require( 'http' ) var handlePaths = [] /** * 初始化路由配置數組 */ function initRotute() { handlePaths.push( '/' ) handlePaths.push( '/login' ) hand... ...
  • hbase集群安裝與部署
    1、相關環境 centos7 hadoop2.6.5 zookeeper3.4.9 jdk1.8 hbase1.2.4 本篇文章僅涉及hbase集群的搭建，關於hadoop與zookeeper的相關部署參見上篇文章http://www.cnblogs.com/learn21cn/p/6184490. ...
  • MongoDB權威指南第二版學習筆記（四）—增刪改文檔下
    $slice 如果希望數組的最大長度是固定的，那麼可以將 $slice 和 $push 組合在一起使用，就可以保證數組不會超出設定好的最大長度。$slice 的值必須是負整數。 假設$slice的值為10，如果$push 後的數組的元素個數小於10，那麼所有元素都會保留。反之，只有最後那10個元素會 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類