SQL数据查询练习(MySQL)

• 最近在帮一些同事培训学习SQL，综合网上的例子，我这边自己选了一些经典的过来做为例子然后整理了一版，以下查询基于MySQL 8
• 表建立和数据准备

-- 老师表
CREATE TABLE `teacher` (
  `tno` varchar(20) NOT NULL,
  `tname` varchar(20) NOT NULL,
  `tsex` varchar(20) NOT NULL,
  `tbirthday` datetime DEFAULT NULL,
  `prof` varchar(20) DEFAULT NULL,
  `depart` varchar(20) NOT NULL,
  PRIMARY KEY (`tno`)
)
INSERT INTO `teacher` VALUES ('804','李诚','男','1958-12-02 00:00:00','副教授','计算机系')
,('825','王萍','女','1972-05-05 00:00:00','助教','计算机系')
,('831','刘冰','女','1977-08-14 00:00:00','助教','电子工程系')
,('856','张旭','男','1969-03-12 00:00:00','讲师','电子工程系');
-- 课程表
CREATE TABLE `course` (
  `cno` varchar(20) NOT NULL,
  `cname` varchar(20) NOT NULL,
  `tno` varchar(20) NOT NULL,
  PRIMARY KEY (`cno`)
)
INSERT INTO `course` VALUES 
('3-105','计算机导论','825'),('3-245','操作系统','804')
,('6-166','数字电路','856'),('9-888','高等数学','831');

-- 学生表
CREATE TABLE `student` (
  `sno` varchar(20) NOT NULL,
  `sname` varchar(20) NOT NULL,
  `ssex` varchar(20) NOT NULL,
  `sbirthday` datetime DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sno`)
)
INSERT INTO `student` VALUES ('101','曾华','男','1977-09-01 00:00:00','95033')
,('102','匡明','男','1975-10-02 00:00:00','95031')
,('103','王丽','女','1976-01-23 00:00:00','95033')
,('104','李军','男','1975-02-10 00:00:00','95033')
,('105','王芳','女','1974-06-03 00:00:00','95031')
,('106','陆君','男','1974-06-03 00:00:00','95031')
,('107','王尼玛','男','1976-02-20 00:00:00','95033')
,('108','张全蛋','男','1975-02-10 00:00:00','95031')
,('109','赵铁柱','男','1974-06-03 00:00:00','95031')
,('110','赵英俊'