SQL语句练习
来源:互联网 发布:万网域名过期多久删除 编辑:程序博客网 时间:2024/04/28 19:38
---------------------------------------------
---------------------------------------------
--1. 选修了java课程的所有2001级的学生姓名
select sname
from students
where grade = 2001 and sid in (
select b.sid
from choices,students b,courses
where choices.cid = courses.cid and courses.cname = 'java' and choices.sid = b.sid
)
---------------------------------------------
---------------------------------------------
---2 请列出每门课程的平均分
select avg(score)
from choices
group by cid
---------------------------------------------
---------------------------------------------
--3. 教授java课程的老师中工资最高的老师姓名
select tname
from teachers
where salary = (
select max(salary)
from teachers
where tid in (
select tid
from choices,courses
where courses.cname = 'java' and choices.cid = courses.cid
)
)
---------------------------------------------
---------------------------------------------
--4. 找出有30个同一年级学生选修的课程名,不显示重复的课程名
select distinct cname
from courses
where cid in (
select cid
from choices join students on choices.sid = students.sid
group by cid ,grade
having count(*) = 30
)
---------------------------------------------
---------------------------------------------
--5. 找出给所有年级都上过课的老师姓名
select tname
from teachers as t
where not exists (
select *
from students as s1
where not exists (
select *
from choices c ,students s2
where c.tid = t.tid and c.sid = s2.sid and s2.grade = s1.grade
)
)
---------------------------------------------
---------------------------------------------
--6. 找出选修课程最多的学生姓名
select sname
from students
where sid in (
select sid
from choices
group by sid
having count(*) >= all (
select count(*)
from choices
group by sid
)
)
---------------------------------------------
---------------------------------------------
--7. 找出没有选修任何课程的学生姓名
select sname
from students
where sid not in (
select sid
from choices
)
---------------------------------------------
---------------------------------------------
--8. 请将教授java课程的老师的工资增加10元。
update teachers
set salary = salary + 10
where tid in (
select choices.tid
from choices,courses
where choices.cid = courses.cid and courses.cname = 'java'
)
---------------------------------------------
---------------------------------------------
--9. 请将2001级选修c++课程的学生成绩置为空
update choices
set score = null
where sid in (
select a.sid
from choices a ,students,courses
where a.sid = students.sid and students.grade = 2001 and courses.cname = 'c++'
and a.cid = courses.cid and a.cid = choices.cid
)
---------------------------------------------
---------------------------------------------
--10. 请找出又教授java又教授c++的老师姓名。
select tname
from teachers
where tid in (
select a.tid
from choices a,choices b,courses x,courses y
where x.cname = 'java' and a.cid = x.cid and y.cname = 'c++' and b.cid = y.cid
and a.tid = b.tid
)
---------------------------------------------
---------------------------------------------
--11. 找出只教了一门课的老师姓名
select tname
from teachers
where not exists (
select *
from choices a ,choices b
where teachers.tid= a.tid and a.tid = b.tid and a.cid != b.cid
)
---------------------------------------------
---------------------------------------------
---12 请找出没有选修java的学生姓名
select sname
from students
where sid not in (
select choices.sid
from choices ,courses
where courses.cname = 'java' and choices.cid = courses.cid
)
---------------------------------------------
---------------------------------------------
---13 请找出没有成绩的学生姓名和课程名
select sname ,cname,score
from students,courses,choices a
where a.score is null and a.sid = students.sid and a.cid = courses.cid
---------------------------------------------
---------------------------------------------
(转自MCS知识社区 longt)
- SQL查询语句练习
- SQL 语句练习
- sql语句练习
- SQL语句练习
- SQL 语句练习
- sql语句练习
- Sql语句练习三
- sql 语句练习
- sql 语句练习
- SQL语句练习
- sql语句小练习
- sql语句练习
- 一些sql语句练习
- Oracle sql语句练习
- sql语句练习
- sql 语句练习
- sql语句练习
- sql语句练习
- WPF下消息识别
- 带优化和不带优化的setjmp 和longjmp函数
- 在Linux下,一个文件也有三种时间,分别是:访问时间、修改时间、状态改动时间
- 黑马程序员---Java基础--08天(面向对象之三多态)
- cocos2d 开关按钮
- SQL语句练习
- 分享2个iOS源码学习网站
- 图的广度优先搜索
- <zz>EM(Expectation Maximization)期望最大化算法
- C#Txtbox格式输入
- Windows窗体间的数据交互(一)
- Windows窗体间的数据交互(二)
- Windows窗体间的数据交互(三)
- 显示窗体的“模式方式”与“非模式方式”