資料庫-SQL & MySQL

SQL經典練習題

資料出處：SQL經典練習題

資料準備

建立Students, Courses, Scores, Teachers四個資料表格及在四個表格中加入一些練習用的資料(請將下列的SQL程式碼剪貼至phpMyAdmin)

-- phpMyAdmin SQL Dump
-- version 4.9.5
-- https://www.phpmyadmin.net/
--
-- 主機： localhost:3306
-- 產生時間： 2021 年 05 月 19 日 10:36
-- 伺服器版本： 10.3.29-MariaDB
-- PHP 版本： 7.3.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 資料庫： `u1085100_SQL_Practice`
--

-- --------------------------------------------------------

--
-- 資料表結構 `Courses`
--

CREATE TABLE `Courses` (
  `cno` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tno` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- 傾印資料表的資料 `Courses`
--

INSERT INTO `Courses` (`cno`, `cname`, `tno`) VALUES
('3-105', '計算機概論', '825'),
('3-245', '作業系統', '804'),
('6-166', '電子電路', '856'),
('9-888', '統計數學', '100');

-- --------------------------------------------------------

--
-- 資料表結構 `Scores`
--

CREATE TABLE `Scores` (
  `sno` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cno` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `degree` decimal(10,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- 傾印資料表的資料 `Scores`
--

INSERT INTO `Scores` (`sno`, `cno`, `degree`) VALUES
('103', '3-245', 86.0),
('105', '3-245', 75.0),
('109', '3-245', 68.0),
('103', '3-105', 92.0),
('105', '3-105', 88.0),
('109', '3-105', 76.0),
('101', '3-105', 64.0),
('107', '3-105', 91.0),
('108', '3-105', 78.0),
('101', '6-166', 85.0),
('107', '6-106', 79.0),
('108', '6-166', 81.0);

-- --------------------------------------------------------

--
-- 資料表結構 `Students`
--

CREATE TABLE `Students` (
  `sno` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `sname` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `ssex` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `sbirthday` datetime DEFAULT NULL,
  `class` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- 傾印資料表的資料 `Students`
--

INSERT INTO `Students` (`sno`, `sname`, `ssex`, `sbirthday`, `class`) VALUES
('108', '涂華明', '男', '1977-09-01 00:00:00', '95033'),
('105', '陳昌國', '男', '1975-10-02 00:00:00', '95031'),
('107', '王麗春', '女', '1976-01-23 00:00:00', '95033'),
('101', '李子萱', '男', '1976-02-20 00:00:00', '95033'),
('109', '王長芳', '女', '1975-02-10 00:00:00', '95031'),
('103', '蔡子信', '男', '1974-06-03 00:00:00', '95031'),
('108', '何明皓', '男', '1977-09-01 00:00:00', '95033'),
('105', '侯昌君', '男', '1975-10-02 00:00:00', '95031'),
('107', '林玉麗', '女', '1976-01-23 00:00:00', '95033'),
('101', '李淑芬', '男', '1976-02-20 00:00:00', '95033'),
('109', '王秀惠', '女', '1975-02-10 00:00:00', '95031'),
('103', '王子君', '男', '1974-06-03 00:00:00', '95031'),
('108', '曾華國', '男', '1977-09-01 00:00:00', '95033'),
('105', '陳昌盛', '男', '1975-10-02 00:00:00', '95031'),
('107', '王麗麗', '女', '1976-01-23 00:00:00', '95033'),
('101', '李予萱', '男', '1976-02-20 00:00:00', '95033'),
('109', '王聿芳', '女', '1975-02-10 00:00:00', '95031'),
('103', '陸子文', '男', '1974-06-03 00:00:00', '95031');

-- --------------------------------------------------------

--
-- 資料表結構 `Teachers`
--

CREATE TABLE `Teachers` (
  `tno` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tname` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tsex` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tbirthday` datetime NOT NULL,
  `prof` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `depart` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- 傾印資料表的資料 `Teachers`
--

INSERT INTO `Teachers` (`tno`, `tname`, `tsex`, `tbirthday`, `prof`, `depart`) VALUES
('804', '李允誠', '男', '1958-12-02 00:00:00', '副教授', '資訊工程系'),
('856', '張旭久', '男', '1969-03-12 00:00:00', '講師', '電子工程系'),
('825', '王照萍', '女', '1972-05-05 00:00:00', '助教', '資訊工程系'),
('831', '劉冰冰', '女', '1977-08-14 00:00:00', '助教', '電子工程系');
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

  練習題目：

1. 查詢Students表中的所有記錄的Sname、Ssex和Class列。
2. 查詢教師所有的單位且不重複的Depart列。
3. 查詢Students表的所有記錄。
4. 查詢Score表中成績在60到80之間的所有記錄。
5. 查詢Score表中成績為85、86或88的記錄。
6. 查詢Students表中“95031”班或性別為“女”的同學記錄。
7. 以class降序查詢Students表的所有記錄。
8. 以cno升序、degree降序查詢Scores表的所有記錄。
9. 查詢“95031”班的學生人數。
10. 查詢Scores表中的最高分的學生學號和課程號。
11. 查詢‘3-105’號課程的平均分。
12. 查詢Scores表中至少有5名學生選修的並以3開頭的課程的平均分數。
13. 查詢最低分大於70，最高分小於90的sno列。
14. 查詢所有學生的sname、cno和degree列。
15. 查詢所有學生的sno、cname和degree列。
16. 查詢所有學生的sname、cname和degree列。
17. 查詢“95033”班所選課程的平均分數。
18. 假設使用如下命令建立了一個grade表： create table grade(low   number(3,0), upp   number(3),rank   char(1)); insert into grade values(90,100,’A’); insert into grade values(80,89,’B’); insert into grade values(70,79,’C’); insert into grade values(60,69,’D’); insert into grade values(0,59,’E’); commit;
19. 現查詢所有同學的sno、cno和rank列。
20. 查詢選修“3-105”課程的成績高於“109”號同學成績的所有同學的記錄。
21. 查詢score中選學一門以上課程的同學中分數為非最高分成績的記錄。
22. 查詢成績高於學號為“109”、課程號為“3-105”的成績的所有記錄。
23. 查詢和學號為108的同學同年出生的所有學生的sno、sname和sbirthday列。
24. 查詢“張旭久“教師任課的學生成績。
25. 查詢選修某課程的同學人數多於5人的教師姓名。
26. 查詢95033班和95031班全體學生的記錄。
27. 查詢存在有85分以上成績的課程cno.
28. 查詢出“資訊工程系“教師所教課程的成績表。
29. 查詢“資訊工程系”與“電子工程系“不同職稱的教師的tname和prof。
30. 查詢選修編號為“3-105“課程且成績至少高於選修編號為“3-245”的同學的cno、sno和degree,並按degree從高到低次序排序。
31. 查詢選修編號為“3-105”且成績高於選修編號為“3-245”課程的同學的cno、sno和degree.
32. 查詢所有教師和同學的sname、ssex和sbirthday.
33. 查詢所有“女”教師和“女”同學的sname、ssex和sbirthday.
34. 查詢成績比該課程平均成績低的同學的成績表。
35. 查詢所有任課教師的tname和depart.
36. 查詢所有未講課的教師的tname和depart.
37. 查詢至少有2名男生的班號。
38. 查詢Students表中不姓“王”的同學記錄。
39. 查詢Students表中每個學生的姓名和年齡。
40. 查詢Students表中最大和最小的sbirthday日期值。
41. 以班號和年齡從大到小的順序查詢Students表中的全部記錄。
42. 查詢“男”教師及其所上的課程。
43. 查詢最高分同學的sno、cno和degree列。
44. 查詢和“李軍”同性別的所有同學的sname.
45. 查詢和“李軍”同性別並同班的同學sname.
46. 查詢所有選修“計算機概論”課程的“男”同學的成績表

 

解答：

• 查詢Students表中的所有記錄的Sname、Ssex和Class列。

SELECT Sname, Ssex, Class FROM Students;

• 查詢教師所有的單位且不重複的Depart列。

SELECT DISTINCT depart FROM Teachers;

• 查詢Students表的所有記錄。

SELECT * FROM Students;

• 查詢Score表中成績在60到80之間的所有記錄。

SELECT *
FROM Scores
WHERE degree BETWEEN 60 AND 80;

• 查詢Score表中成績為85、86或88的記錄。

SELECT *
FROM Scores
WHERE degree IN (85,86,88);

• 查詢Students表中“95031”班或性別為“女”的同學記錄。

SELECT *
FROM Students
WHERE class='95031' OR ssex='女';

• 以class降序查詢Students表的所有記錄。

SELECT *
FROM Students
ORDER BY class DESC;

• 以cno升序、degree降序查詢Scores表的所有記錄。

SELECT *
FROM Scores
ORDER BY cno,degree DESC;

• 查詢“95031”班的學生人數。

SELECT COUNT(1) AS StuNum
FROM Students
WHERE class='95031';

• 查詢Scores表中的最高分的學生學號和課程編號。

SELECT sno,cno
FROM Scores
ORDER BY degree DESC
LIMIT 1;

• 查詢‘3-105’號課程的平均分。

SELECT AVG(degree)
FROM Scores
WHERE cno='3-105';

• 查詢Scores表中至少有5名學生選修的並以3開頭的課程的平均分數。

SELECT cno,AVG(degree)
FROM Scores
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(sno) >= 5;

• 查詢最低分大於70，最高分小於90的sno列。

SELECT sno
FROM Scores
GROUP BY sno
HAVING MAX(degree)<90 AND MIN(degree)>70;

• 查詢所有學生的sname、cno和degree列。

SELECT sname, cno, degree
FROM Students INNER JOIN Scores 
ON(Students.sno=Scores.sno)
ORDER BY sname;

• 查詢所有學生的sno、cname和degree列。
• 查詢所有學生的sname、cname和degree列。
• 查詢“95033”班所選課程的平均分數。
• 假設使用如下命令建立了一個grade表： create table grade(low   number(3,0), upp   number(3),rank   char(1)); insert into grade values(90,100,’A’); insert into grade values(80,89,’B’); insert into grade values(70,79,’C’); insert into grade values(60,69,’D’); insert into grade values(0,59,’E’); commit;

 

• 現查詢所有同學的sno、cno和rank列。
• 查詢選修“3-105”課程的成績高於“109”號同學成績的所有同學的記錄。
• 查詢score中選學一門以上課程的同學中分數為非最高分成績的記錄。
• 查詢成績高於學號為“109”、課程號為“3-105”的成績的所有記錄。
• 查詢和學號為108的同學同年出生的所有學生的sno、sname和sbirthday列。
• 查詢“張旭久“教師任課的學生成績。
• 查詢選修某課程的同學人數多於5人的教師姓名。
• 查詢95033班和95031班全體學生的記錄。
• 查詢存在有85分以上成績的課程cno.
• 查詢出“資訊工程系“教師所教課程的成績表。
• 查詢“資訊工程系”與“電子工程系“不同職稱的教師的tname和prof。
• 查詢選修編號為“3-105“課程且成績至少高於選修編號為“3-245”的同學的cno、sno和degree,並按degree從高到低次序排序。
• 查詢選修編號為“3-105”且成績高於選修編號為“3-245”課程的同學的cno、sno和degree.
• 查詢所有教師和同學的sname、ssex和sbirthday.
• 查詢所有“女”教師和“女”同學的sname、ssex和sbirthday.
• 查詢成績比該課程平均成績低的同學的成績表。
• 查詢所有任課教師的tname和depart.
• 查詢所有未講課的教師的tname和depart.
• 查詢至少有2名男生的班號。
• 查詢Students表中不姓“王”的同學記錄。
• 查詢Students表中每個學生的姓名和年齡。
• 查詢Students表中最大和最小的sbirthday日期值。
• 以班號和年齡從大到小的順序查詢Students表中的全部記錄。
• 查詢“男”教師及其所上的課程。
• 查詢最高分同學的sno、cno和degree列。
• 查詢和“李軍”同性別的所有同學的sname.
• 查詢和“李軍”同性別並同班的同學sname.
• 查詢所有選修“計算機概論”課程的“男”同學的成績表

參考資源：

• SQL 敘述
• SQL語法教學
• MySQL資料庫系統開發理論與實務