SQL语句|PTA题目集

R10-26 查询工作年限大于38年员工的姓名、岗位、部门编号及工作年限信息

作者 蒋维

单位 浙江树人学院

查询工作年限大于38年员工的姓名、岗位、部门编号及工作年限信息

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

 CREATE TABLE `dept` (
  `deptno` decimal(2,0) NOT NULL,
  `dname` varchar(14) DEFAULT NULL,
  `loc` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ;

CREATE TABLE `emp` (
  `empno` decimal(4,0) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` decimal(4,0) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` decimal(2,0) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ;

CREATE TABLE `salgrade` (
  `grade` decimal(10,0) NOT NULL,
  `losal` decimal(10,0) DEFAULT NULL,
  `hisal` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`grade`)
);

表样例

请在这里给出上述表结构对应的表样例。例如

emp表：

dept表：

salgrade表：

输出样例:

请在这里给出输出样例。

 select ename,job,emp.deptno,Now()-Year(hiredate) as 工作年限
from emp

R10-27 创建ViewALL视图。

作者 李翔坤

单位 大连东软信息学院

本题目要求编写SQL语句，基于学生选课数据库的几张表，创建ViewALL视图，查询所有学生选修课程的学生学号、姓名、专业、课程号、课程名、学分、成绩。

提示：请使用CREATE VIEW语句作答。

表结构:

create table if not exists Student(
  sno char(8)  primary key,
  sname varchar(10) not null,
  gender char(2) check(gender='男' or gender='女'),
  birthdate  date,
  major varchar(20)  default '软件工程'
  );
 
create table if not exists Course(
  cno char(10) primary key,
  cname varchar(20) not null,
  ccredit int check(ccredit>0), 
  semester int check(semester>0),  
  period int  check(period>0)    
  );

create table if not exists Teacher(
   Tno char(15) primary key,
   Tname varchar(10) not null,
   gender char(2),
   deptname varchar(50) ,  
   title varchar(20)     
);

create table if not exists SC(  
  scid  int auto_increment  primary key,
  sno char(8)   references Student(sno),
  cno char(10)  references Course(cno),
  tno char(15)  references Teacher(tno),
  grade int check(grade>=0 and grade<=100),
  gpoint  decimal(2,1),   
  memo  text(100)  
  );

表样例

Student表：

sno	sname	gender	birthdate	major
21012101	李勇	男	2005-10-20	计算机科学
21012102	刘晨	男	2006-5-5	计算机科学
21012103	王晓敏	女	2005-10-6	计算机科学
21021101	李佳睿	男	2006-3-30	软件工程
21021102	吴宾	男	2005-9-21	软件工程
21021103	张海	男	2005-10-20	软件工程
21031101	钱晓萍	女	2006-6-1	网络工程
21031102	王大力	男	2005-11-15	网络工程
21041101	于洋	男	2006-3-15	数据科学
21041102	郭霖	男	2006-3-2	数据科学

Course表：

cno	cname	ccredit	semester	period
c01	计算机文化基础	2	1	32
c02	C语言	6	2	96
c03	数据结构	4	3	64
c04	Java编程思想	4	3	64
c05	数据库原理	3	4	48
c06	计算机网络	4	5	64
c07	操作系统	4	6	64
c08	软件工程	4	6	64

Teacher表：

tno	tname	gender	deptname	title
t200104m12101	赵飞	男	软件工程	教授
t200306m12132	孙林	男	软件工程	副教授
t200608f12205	王慧慧	女	计算机科学	副教授
t200703m12218	张兵	男	计算机科学	副教授
t200703m12219	宋奕	男	计算机科学	教授
t201005m12306	周鹏	男	网络工程	副教授
t201208m12308	韩志刚	男	网络工程	讲师
t201512m12401	傅淼	男	数据科学	副教授
t201803f12405	刘芳芳	女	数据科学	讲师

SC表：

scid	sno	cno	tno	grade	gpoint	memo
1	21012101	c01	t200306m12132	90	null	null
2	21012101	c02	t200703m12218	86	null	null
3	21012101	c03	t200703m12218	null	null	缺考
4	21012102	c02	t200703m12218	78	null	null
5	21012102	c03	t200703m12218	66	null	null
6	21021102	c01	t200306m12132	82	null	null
7	21021102	c02	t200608f12205	75	null	null
8	21021102	c03	t200306m12132	null	null	缓考
9	21021102	c05	t201803f12405	50	null	null
10	21021103	c02	t200703m12218	68	null	null
11	21021103	c04	t201208m12308	92	null	null
12	21031101	c01	t200306m12132	80	null	null
13	21031101	c02	t200608f12205	95	null	null
14	21041102	c02	t200608f12205	56	null	null
15	21041102	c05	t201803f12405	88	null	null

输出样例:

视图 ViewALL

学号	姓名	专业	课程号	课程名	学分	成绩
21012101	李勇	计算机科学	c01	计算机文化基础	2	90
21012101	李勇	计算机科学	c02	C语言	6	86
21012101	李勇	计算机科学	c03	数据结构	4
21012102	刘晨	计算机科学	c02	C语言	6	78
21012102	刘晨	计算机科学	c03	数据结构	4	66
21021102	吴宾	软件工程	c01	计算机文化基础	2	82
21021102	吴宾	软件工程	c02	C语言	6	75
21021102	吴宾	软件工程	c03	数据结构	4
21021102	吴宾	软件工程	c05	数据库原理	3	50
21021103	张海	软件工程	c02	C语言	6	68
21021103	张海	软件工程	c04	Java编程思想	4	92
21031101	钱晓萍	网络工程	c01	计算机文化基础	2	80
21031101	钱晓萍	网络工程	c02	C语言	6	95
21041102	郭霖	数据科学	c02	C语言	6	56
21041102	郭霖	数据科学	c05	数据库原理	3	88

create view ViewALL as
select Student.sno as 学号,Student.sname as 姓名,Student.major as 专业,Course.cno as 课程号,Course.cname as 课程名,Course.ccredit as 学分,SC.grade as 成绩
from SC
    join Student on SC.sno=Student.sno
    join Course on SC.cno=Course.cno

R10-28 基于视图ViewALL的查询。 

作者 李翔坤

单位 大连东软信息学院

基于视图ViewALL，查询刘晨选修了C语言课程的成绩。

视图表样例:

ViewALL视图：

学号	姓名	专业	课程号	课程名	学分	成绩
21012101	李勇	计算机科学	c01	计算机文化基础	2	90
21012101	李勇	计算机科学	c02	C语言	6	86
21012101	李勇	计算机科学	c03	数据结构	4
21012102	刘晨	计算机科学	c02	C语言	6	78
21012102	刘晨	计算机科学	c03	数据结构	4	66
21021102	吴宾	软件工程	c01	计算机文化基础	2	82
21021102	吴宾	软件工程	c02	C语言	6	75
21021102	吴宾	软件工程	c03	数据结构	4
21021102	吴宾	软件工程	c05	数据库原理	3	50
21021103	张海	软件工程	c02	C语言	6	68
21021103	张海	软件工程	c04	Java编程思想	4	92
21031101	钱晓萍	网络工程	c01	计算机文化基础	2	80
21031101	钱晓萍	网络工程	c02	C语言	6	95
21041102	郭霖	数据科学	c02	C语言	6	56
21041102	郭霖	数据科学	c05	数据库原理	3	88

输出样例:

学号	姓名	专业	课程号	课程名	学分	成绩
21012102	刘晨	计算机科学	c02	C语言	6	78

 

select 学号,姓名,专业,课程号,课程名,学分,成绩
from ViewALL
where 姓名 like '刘晨'
    and 课程名 like 'C语言';

R10-29 创建基于视图的视图 

作者 李翔坤

单位 大连东软信息学院

基于上题中的视图ViewALL，创建“计算机科学”专业学生选修课程的视图JSJView，包含学生学号、姓名、课程号、课程名和成绩。

ViewALL视图结构:

create view ViewALL (学号, 姓名, 专业, 课程号, 课程名, 学分, 成绩)
as SELECT Student.sno, sname, major, Course.cno, cname, ccredit, grade 
FROM student join sc on student.sno=sc.sno join course on course.cno=sc.cno;

ViewALL视图数据样例

ViewALL视图：

学号	姓名	专业	课程号	课程名	学分	成绩
21012101	李勇	计算机科学	c01	计算机文化基础	2	90
21012101	李勇	计算机科学	c02	C语言	6	86
21012101	李勇	计算机科学	c03	数据结构	4
21012102	刘晨	计算机科学	c02	C语言	6	78
21012102	刘晨	计算机科学	c03	数据结构	4	66
21021102	吴宾	软件工程	c01	计算机文化基础	2	82
21021102	吴宾	软件工程	c02	C语言	6	75
21021102	吴宾	软件工程	c03	数据结构	4
21021102	吴宾	软件工程	c05	数据库原理	3	50
21021103	张海	软件工程	c02	C语言	6	68
21021103	张海	软件工程	c04	Java编程思想	4	92
21031101	钱晓萍	网络工程	c01	计算机文化基础	2	80
21031101	钱晓萍	网络工程	c02	C语言	6	95
21041102	郭霖	数据科学	c02	C语言	6	56
21041102	郭霖	数据科学	c05	数据库原理	3	88

新视图JSJView 输出数据样例:

学号	姓名	专业	课程号	课程名	学分	成绩
21012101	李勇	计算机科学	c01	计算机文化基础	2	90
21012101	李勇	计算机科学	c02	C语言	6	86
21012101	李勇	计算机科学	c03	数据结构	4
21012102	刘晨	计算机科学	c02	C语言	6	78
21012102	刘晨	计算机科学	c03	数据结构	4	66

 create view JSJView as
select 学号,姓名,课程号,课程名,成绩
from ViewALL
where 专业 like '计算机科学';

 

R10-30 查询工资最高的员工信息：

 

作者 李翔坤

单位 大连东软信息学院

查询工资最高的员工信息：

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE employees (  
    employee_id INT PRIMARY KEY,  
    first_name VARCHAR(50),  
    last_name VARCHAR(50),  
    department VARCHAR(50),  
    salary INT  
);

表样例

employees表：

输出样例:

 select employee_id,first_name,last_name,department,salary
from employees
where salary>=all(select salary
                 from employees)