“大数据技术开发与应用”实验报告
DQL1. 查询两表的数据
SELECT * from stuinfo,stumarks;
copy2.查询男学员名单
SELECT * from stuinfo where stusex='男';
copy3.查询笔试成绩优秀的学员情况（成绩在75~100之间)
SELECT* from stumarks b inner join stuinfo a on a.STUNO = b.stuNo and writtenExam BETWEEN 75 and 100;
copy-- 5.统计笔试考试平均分和机试考试平均分-- ？空值问题
SELECT AVG(writtenExam) ,AVG(LabExam)FROM stumarks;
copy6.统计参加本次考试的学员人数
SELECT count(ExamNo)FROM stumarks;
copy7.查询没有通过考试的人数（笔试或机试小于60分）-- ？空值问题
SELECT count(ExamNo)FROM stumarks WHERE (writtenExam <60 or LabExam<60);
copy8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分--
SELECT ExamNo,stuNo,writtenExam,LabExam,(writtenExam+LabExam)/2 FROM stumarks ;
copy9.排名次（按平均分从高到低排序），显示名次、学号、平均分-- ?
select row_number()over(order by (writtenExam+LabExam)/2 desc), stuno,(writtenExam+LabExam)/2 from stumarks order by (writtenExam+LabExam)/2 DESC
copy-- 10.机试成绩都提5分--
SELECT ExamNo,stuNo,writtenExam+5,LabExam+5 from stumarks;
copy-- 11.机试成绩100分封顶（加分后超过100分的，按100分计算）--
select stuno,writtenexam,(case when labexam+5>100 then 100 else labexam+5 end) from stumarks;
copy-- 12.笔试不及格的提到及格，60-90分的加5分，90分以上加3分（但不能超过100）--
select stuno,(case when writtenexam<60 then 60 when writtenexam>60 and writtenexam<90 then writtenexam+5 when writtenexam+3>100 then 100 else writtenexam+3 end)from stumarks;
copy作业2
作业3
use mydb1;
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50728
Source Host : localhost:3306
Source Schema : personal_test
Target Server Type : MySQL
Target Server Version : 50728
File Encoding : 65001
Date: 05/02/2021 11:58:34
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for bonus
-- ----------------------------
DROP TABLE IF EXISTS `bonus`;
CREATE TABLE `bonus` (
`ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`sal` decimal(7, 2) NULL DEFAULT NULL,
`comm` decimal(7, 2) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(4) NOT NULL,
`dname` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`loc` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');
-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`mgr` int(4) NULL DEFAULT NULL,
`hiredate` date NULL DEFAULT NULL,
`sal` decimal(7, 2) NULL DEFAULT NULL,
`comm` decimal(7, 2) NULL DEFAULT NULL,
`deptno` int(2) NULL DEFAULT NULL,
PRIMARY KEY (`empno`) USING BTREE,
INDEX `fk_deptno`(`deptno`) USING BTREE,
CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(1) NULL DEFAULT NULL,
`losal` decimal(7, 2) NULL DEFAULT NULL,
`hisal` decimal(7, 2) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES (1, 700.00, 1200.00);
INSERT INTO `salgrade` VALUES (2, 1201.00, 1400.00);
INSERT INTO `salgrade` VALUES (3, 1401.00, 2000.00);
INSERT INTO `salgrade` VALUES (4, 2001.00, 3000.00);
INSERT INTO `salgrade` VALUES (5, 3001.00, 9999.00);
SET FOREIGN_KEY_CHECKS = 1;
-- 使用scott/tiger用户下的emp表和dept表完成下列练习
-- sal：薪金
-- comm：佣金
-- --1．列出至少有一个员工的所有部门
select dname from dept where deptno in(select deptno from emp);
-- --2．列出薪金比“SMITH”多的所有员工。
SELECT *from emp where sal >(SELECT sal from emp where ename ='SMITH')
-- --3．列出所有员工的姓名及其直接上级的姓名。(自连接)
SELECT a.ename , (SELECT ename from emp b where b.empno =a.mgr) as boss_name from emp a;
-- --4．列出受雇日期晚于其直接上级的所有员工。(自连接)
SELECT a.ename FROM emp a WHERE a.hiredate<(SELECT hiredate from emp b WHERE b.empno =a.mgr);
-- --5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接)
SELECT a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno from dept a left join emp b on a.deptno=b.deptno;
-- --6．列出所有“CLERK”（办事员）的姓名及其部门名称。
select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
-- --7．列出最低薪金大于1500的各种工作。
select distinct job as HighSalJob from emp group by job having min(sal)>1500;
-- --8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp where deptno=(select deptno from dept where dname='SALES');
-- --9．列出薪金高于公司平均薪金的所有员工。
select ename from emp where sal>(select avg(sal) from emp);
-- --10．列出与“SCOTT”从事相同工作的所有员工。
select ename from emp where job=(select job from emp where ename='SCOTT');
-- --11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select a.ename,a.sal from emp a where a.sal in (select b.sal
from emp b where b.deptno=30) and a.deptno & amp;<amp;
-- --12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
-- --13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal
from emp a group by deptno;
-- --14．列出所有员工的姓名、部门名称和工资。 内连接
select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a;
-- --15.列出所有部门的详细信息和部门人数。
select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;
-- --16．列出各种工作的最低工资。
select job,avg(sal) from emp group by job;
-- --17．列出MANAGER（经理）的最低薪金。
select deptno,min(sal) from emp where job='MANAGER' group by deptno;
-- --18．列出所有员工的年工资，按年薪从低到高排序。
select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
copy作业4
use mydb2;
CREATE TABLE student
(
s INT,
sname varchar(32),
sage INT,
ssex varchar(8)
)
CREATE TABLE course
(
c INT,
cname varchar(32),
t INT
);
CREATE TABLE sc
(
s INT,
c INT,
score INT
);
CREATE TABLE teacher
(
t INT,
tname varchar(16)
);
insert into Student select 1,N'刘一',18,N'男' from dual union
select 2,N'钱二',19,N'女' from dual union select 3,N'张三',17,N'男' from dual union
select 4,N'李四',18,N'女' from dual union select 5,N'王五',17,N'男' from dual union
select 6,N'赵六',19,N'女' from dual;
insert into Teacher select 1,N'叶平' from dual union all select 2,N'贺高' from dual union
all select 3,N'杨艳' from dual union all select 4,N'周磊' from dual ;
insert into Course select 1,N'语文',1 from dual union all select 2,N'数学',2 from dual union
all select 3,N'英语',3 from dual union all select 4,N'物理',4 from dual;
insert into SC select 1,1,56 from dual union all select 1,2,78 from dual union
all select 1,3,67 from dual union all select 1,4,58 from dual union all select 2,1,79 from dual union all select 2,2,81 from dual union all select 2,3,92 from dual union all select 2,4,68 from dual union all select 3,1,91 from dual union all select 3,2,47 from dual union all select 3,3,88 from dual union all select 3,4,56 from dual union all select 4,2,88 from dual union all select 4,3,90 from dual union all select 4,4,93 from dual union all select 5,1,46 from dual union
all select 5,3,78 from dual union all select 5,4,53 from dual union all select 6,1,35 from dual union all select 6,2,68 from dual union all select 6,4,71 from dual;
select * from sc;
-- --1.查询“语文”课程比“数学”课程成绩高的所有学生的学号；
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.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
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.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select distinct S,Sname from Student,SC where Student.S=SC.S and SC.C in (select C from SC where S='1001');
-- --12.查询和学号为“6”同学所有课数量相等的其他同学的学号和姓名；
select S from SC where C in (select C from SC where S='6')
group by S having count(*)=(select count(*) from SC where S='6');
-- --13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；
select distinct SC.S,Sname
from Student,SC
where Student.S=SC.S and C in (select C from SC where S='001');
-- --14.查询各科成绩最高和最低的分：以如下形式显示：课程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
);
-- --15.按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT t.C AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C=course.C
GROUP BY t.C
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
copy
0
请
登录
后发表评论
最新评论
暂无评论~
实验数据
学习时间 0分钟
操作时间 0分钟
按键次数 0次
实验次数 8次
报告字数 13941字
是否完成 完成
评分
未评分
