数据库小练习-2018-8-30
一.设有成绩表如下所示
1.查询两门及两门以上不及格的学生姓名
2.查询学生平均分
3.查询姓名是张三的学生 成绩和
4.将学生信息按照 分数倒序
5.获取学生信息中 分数最低的学生姓名和分数最高的学生姓名
6.查询两门及两门以上不及格同学的平均分。
编号 姓名 科目 分数
1 张三 数学 90
2 张三 语文 50
3 张三 地理 40
4 李四 语文 55
5 李四 政治 45
6 王五 政治 30
7 李四 数学 80
8 王五 语文 70
解答：
create table student_list(id int(10) primary key auto_increment,name varchar(10) not null,object varchar(10),score int(10));
insert into student_list values(1,'张三','数学',90);
insert into student_list(name,object,score) values('张三','语文',50);
insert into student_list(name,object,score) values('张三','地理',40);
insert into student_list(name,object,score) values('李四','语文',55);
insert into student_list(name,object,score) values('李四','政治',45);
insert into student_list(name,object,score) values('王五','政治',30);
insert into student_list(name,object,score) values('李四','数学',80);
insert into student_list(name,object,score) values('王五','语文',70);
1.查询两门及两门以上不及格的学生姓名
select name from student_list where score< 60 group by name having count(*)>=2;
2.查询学生平均分
SELECT name, avg(score)
FROM student_list
GROUP BY name;
3.查询姓名是张三的学生 成绩和
SELECT name,sum(score)
FROM student_list
where name='张三';
4.将学生信息按照 分数倒序
select * from student_list order by score DEsc
5.获取学生信息中 分数最低的学生姓名和分数最高的学生姓名
select name,score from student_list where score =(select max(score) from student_list);
select name,score from student_list where score =(select max(score) from student_list) or score =(select min(score) from student_list);
6.查询两门及两门以上不及格同学的平均分。
select name, avg(score) as '平均成绩' from student_list group by name having sum(score<60)>=2;
二.写出 SQL语句的格式 : 插入 ，更新 ，删除
表名 user
name tel content date
张三 13333663366 大专毕业 2006-10-11
张三 13612312331 本科毕业 2006-10-15
张四021-55665566 中专毕业 2006-10-15
(a).有一新记录(小王 13254748547 高中毕业 2007-05-06)请用SQL语句新增至表中
(b).请用sql语句把张三的时间更新成为当前系统时间
(c).请写出删除名为张四的全部记录
解答：
a. insert into user(name,tel,content,date)values('小王' ,'13254748547',' 高中毕业',' 2007-05-06');
b. UPDATE emp SET date = DATE(NOW()) WHERE ename = "张三";
update user set date=convert(datetime,getdate(),108) where name='张三';
update user set Date ='2013-09-06' where Date='2006-10-11';
c. DELETE FROM stu WHERE sname = '张四';
三.写出 SQL语句的格式 :对emp表进行查询操作
SELECT * FROM emp LIMIT 0, 5;
SELECT* FROM emp LIMIT 3, 10;
1.找出奖金高于工资的雇员
2.找出奖金高于工资60%的雇员
3.找出部门10中所有经理和部门20中所有店员的信息
4.找出部门10中所有经理(MANAGER)，部门20中所有店员，既不是经理又不是店员但其
5.薪资大于或等于2000的所有员工的信息。
6.查询没有奖金或者奖金低于100的员工信息
7.查询姓名不带”R”的员工姓名
8.显示员工的姓名和入职时间，根据入职时间，将最老的员工排放在最前面。
9.显示所有员工的姓名、工作和工资，按照工作的降序排序，若工作相同则按工资升序排序。
解答：
1. select ename from emp where mgr > ifnull(comm,0);
2. select ename from emp where ifnull(comm,0) > mgr*0.6;
3. select ename,deptno from emp where ename=(select ename from emp where job ='MANAGER' and deptno=10) or deptno=20;
4. select ename,job,deptno,mgr from emp where mgr >=2000 and job !='manager' and deptno !=30;
5. select ename,comm from emp where ifnull(comm,0) < 100;
6. select ename,comm from emp where ifnull(comm,0) < 100;
7. select * from emp where ename not like '%R%';
8. select ename,hiredate from emp order by hiredate;
9. select ename,job,mgr from emp order by job,mgr desc;
