3522vip-澳门新葡亰平台官网-www.3522vip.com

3522vip☞(www.rivieraquest.com)能够为大家带来真正的真钱享受,澳门新葡亰平台官网开创业内先河,注册,开户,登录开始体验不同的娱乐世界,全国第一家以娱乐产品为主体对象的专业平台,菲律宾全资子公司成立,天天免费68周周再送168。

3522vip > 网络数据库 > sql 语句练习与答案_MsSql_脚本之家

原标题:sql 语句练习与答案_MsSql_脚本之家

浏览次数:119 时间:2019-11-23

1学生表student S#学号,sname姓名,difdate日期,班级grade 2课程表 course c#课程号 ,名字cname 3成绩单score s#学号 c#课程号 成绩score --1统计每个班级有多少人 select grade,count from ze_student group by grade; --2、2007级的各学生的平均成绩 没有成绩的为0; select a.sname,(select avg from ze_score b where b.s#=a.s#) from ze_student a where grade=2007; --3 每科 平均成绩和最高成绩 最低成绩 2007级 保留2位小数点 四舍五入 select b.c#,avg,min from ze_student a,ze_score b where b.s# = a.s# and a.grade =2007 group by b.c#; --4 给2007级 数学加5分 update ze_score set score=nvl 5 where s# in (select s# from ze_student where grade=2007) and c# =(select c# from ze_course where cname='数学'); --5 90分以上的为优秀 90到85为良好,60分 不及格 各人平均成绩 select s#, c, case when c>=90 then '优秀' when c<90 and c>=60 then '及格' else '不及格' end as jige from (select s#,avg as c from ze_score group by s# ) order by jige desc; --6 求同月出生的 人数 select to_char as 月份,count as 出生人数 from ze_student group by to_char; --7 各科的及格率和平均成绩 截取 保留2位 --及格率 select c#,avgas 平均成绩,sumas 总成绩, count as 各科人数, trunc(sum( case when nvl>60 then '1' else '0' end)/count as 及格率 from ze_score group by c#; --每人的及格率 select s#, avgas 平均成绩,sumas 总成绩, count as 总科目, sum( case when nvl>60 then 1 else 0 end )/count as 及格率 from ze_score group by s#; --8删除 姓名是张三 的大学语文 成绩 select * from ze_score where s# in (select s# from ze_student where sname in '张三') and c#=(select c# from ze_course where cname ='大学语文'); --9 将数学替换成高等数学 update ze_course set cname='高等数学'where cname like '%数学%'; --10 格式化 ,显示 将学号修改成S开头 不足12位补0; --查询 select concat as s# from ze_score ; select concat as s# from ze_student ; --格式化 update ze_score set s#= concat; update ze_student set s#= concat; 四个足球队 select a.name,b.name from qiu a,qiu b where a.name

 

作为一枚Java后端开发者,数据库知识必不可少,对数据库的掌握熟悉度的考察也是对这个人是否有扎实基本功的考察。特别对于初级开发者,面试可能不会去问框架相关知识,但是绝对不会不去考察数据库知识,这里收集一些常见类型的SQL语句,无论对于平常开发还是准备面试,都会有助益。

基本表结构:

        student(sno,sname,sage,ssex)学生表
        course(cno,cname,tno) 课程表
        sc(sno,cno,score) 成绩表

*        teacher(tno,tname) 教师表*

* *

101,查询课程1的成绩比课程2的成绩高的所有学生的学号
select a.sno from
(select sno,score from sc where cno=1) a,
(select sno,score from sc where cno=2) b
where a.score>b.score and a.sno=b.sno

 

102,查询平均成绩大于60分的同学的学号和平均成绩*
select a.sno as "学号", avg(a.score) as "平均成绩" 
from
(select sno,score from sc) a 
group by sno having avg(a.score)>60
*

* *

103,查询所有同学的学号、姓名、选课数、总成绩*
select a.sno as 学号, b.sname as 姓名,
count(a.cno) as 选课数, sum(a.score) as 总成绩
from sc a, student b
where a.sno = b.sno
group by a.sno, b.sname
*

或者:

*selectstudent.sno as 学号, student.sname as 姓名,
 count(sc.cno) as 选课数, sum(score) as 总成绩
from student left Outer join sc on student.sno = sc.sno
group by student.sno, sname

*104,查询姓“张”的老师的个数

selectcount(distinct(tname)) from teacher where tname like '张%‘
或者:
select tname as "姓名", count(distinct(tname)) as "人数" 
from teacher 
where tname like'张%'
group by tname

 

105,查询没学过“张三”老师课的同学的学号、姓名
select student.sno,student.sname from student
where sno not in (select distinct(sc.sno) from sc,course,teacher
where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='张三')

 

106,查询同时学过课程1和课程2的同学的学号、姓名
select sno, sname from student
where sno in (select sno from sc where sc.cno = 1)
and sno in (select sno from sc where sc.cno = 2)
或者:

selectc.sno, c.sname from
(select sno from sc where sc.cno = 1) a,
(select sno from sc where sc.cno = 2) b,
student c
where a.sno = b.sno and a.sno = c.sno
或者:

select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno=1
and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

 

107,查询学过“李四”老师所教所有课程的所有同学的学号、姓名
select a.sno, a.sname from student a, sc b
where a.sno = b.sno and b.cno in
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四')

或者:

select a.sno, a.sname from student a, sc b,
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四') e
where a.sno = b.sno and b.cno = e.cno

 

108,查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名
select a.sno, a.sname from student a,
(select sno, score from sc where cno = 1) b,
(select sno, score from sc where cno = 2) c
where b.score > c.score and b.sno = c.sno and a.sno = b.sno

 

109,查询所有课程成绩小于60分的同学的学号、姓名
select sno,sname from student
where sno not in (select distinct sno from sc where score > 60)

 

110,查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名
select distinct a.sno, a.sname
from student a, sc b
where a.sno <> 1 and a.sno=b.sno and
b.cno in (select cno from sc where sno = 1)

或者:

select s.sno,s.sname 
from student s,
(select sc.sno 
from sc
where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1
group by sc.sno)r1
where r1.sno=s.sno

 

Java知音公众号整理一些各大公司常用的面试笔试题,供大家在每天闲暇之余学习其中几道题目,日积月累,等到出去面试时,一切都水到渠成,面试时就自然会游刃有余了。

图片 1

 

作为一枚Java后端开发者,数据库知识必不可少,对数据库的掌握熟悉度的考察也是对这个人是否有扎实基本功的考察。特别对于初级开发者,面试可能不会去问框架相关知识,但是绝对不会不去考察数据库知识,这里收集一些常见类型的SQL语句,无论对于平常开发还是准备面试,都会有助益。

基本表结构:

        student(sno,sname,sage,ssex)学生表
        course(cno,cname,tno) 课程表
        sc(sno,cno,score) 成绩表

*        teacher(tno,tname) 教师表*

 

111、把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩
update sc set score = (select avg(sc_2.score) from sc sc_2 wheresc_2.cno=sc.cno)
from course,teacher where course.cno=sc.cno and course.tno=teacher.tno andteacher.tname='王五'

112、查询和编号为2的同学学习的课程完全相同的其他同学学号和姓名
这一题分两步查:

1,

select sno
from sc
where sno <> 2
group by sno
having sum(cno) = (select sum(cno) from sc where sno = 2)

2,
select b.sno, b.sname
from sc a, student b
where b.sno <> 2 and a.sno = b.sno
group by b.sno, b.sname
having sum(cno) = (select sum(cno) from sc where sno = 2)

113、删除学习“王五”老师课的sc表记录
delete sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'

114、向sc表中插入一些记录,这些记录要求符合以下条件:
将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩

insert sc select sno, 3, (select avg(score) from sc where cno = 2)
from student
where sno not in (select sno from sc where cno = 3)

115、按平平均分从高到低显示所有学生的如下统计报表:
-- 学号,企业管理,马克思,UML,数据库,物理,课程数,平均分

select sno as 学号
,max(case when cno = 1 then score end) AS 企业管理
,max(case when cno = 2 then score end) AS 马克思
,max(case when cno = 3 then score end) AS UML
,max(case when cno = 4 then score end) AS 数据库
,max(case when cno = 5 then score end) AS 物理
,count(cno) AS 课程数
,avg(score) AS 平均分
FROM sc
GROUP by sno
ORDER by avg(score) DESC

116、查询各科成绩最高分和最低分:

以如下形式显示:课程号,最高分,最低分
*select cno as 课程号, max(score) as 最高分, min(score) 最低分
from sc group by cno

select  course.cno as '课程号'
,MAX(score) as '最高分'
,MIN(score) as '最低分'
from sc,course
where sc.cno=course.cno
group by course.cno*

117、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT t.cno AS 课程号,
max(course.cname)AS 课程名,
isnull(AVG(score),0) AS 平均成绩,
100
SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/count(1) AS 及格率
FROM sc t, course
where t.cno = course.cno
GROUP BY t.cno
ORDER BY 及格率 desc*

118、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 

企业管理(001),马克思(002),UML (003),数据库(004) 
select 
avg(case when cno = 1 then score end) as 平均分1,
avg(case when cno = 2 then score end) as 平均分2,
avg(case when cno = 3 then score end) as 平均分3,
avg(case when cno = 4 then score end) as 平均分4,
100
sum(case when cno = 1 and score > 60 then 1 else 0 end) / sum(casewhen cno = 1 then 1 else 0 end) as 及格率1,
100 * sum(case when cno = 2 and score > 60 then 1 else 0 end) / sum(casewhen cno = 2 then 1 else 0 end) as 及格率2,
100 * sum(case when cno = 3 and score > 60 then 1 else 0 end) / sum(casewhen cno = 3 then 1 else 0 end) as 及格率3,
100 * sum(case when cno = 4 and score > 60 then 1 else 0 end) / sum(casewhen cno = 4 then 1 else 0 end) as 及格率4
from sc*

119、查询不同老师所教不同课程平均分, 从高到低显示
select max(c.tname) as 教师, max(b.cname) 课程, avg(a.score) 平均分
from sc a, course b, teacher c
where a.cno = b.cno and b.tno = c.tno
group by a.cno
order by 平均分 desc

或者:
select r.tname as '教师',r.rname as '课程' , AVG(score) as '平均分'
from sc,
(select  t.tname,c.cno as rcso,c.cname as rname
from teacher t ,course c
where t.tno=c.tno)r
where sc.cno=r.rcso
group by sc.cno,r.tname,r.rname 
order by AVG(score) desc

120、查询如下课程成绩均在第3名到第6名之间的学生的成绩:
-- [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

select top 6 max(a.sno) 学号, max(b.sname) 姓名,
max(case when cno = 1 then score end) as 企业管理,
max(case when cno = 2 then score end) as 马克思,
max(case when cno = 3 then score end) as UML,
max(case when cno = 4 then score end) as 数据库,
avg(score) as 平均分
from sc a, student b
where a.sno not in 

(select top 2 sno from sc where cno = 1 order by score desc)
  and a.sno not in (select top 2 sno from sc where cno = 2 order by scoredesc)
  and a.sno not in (select top 2 sno from sc where cno = 3 order by scoredesc)
  and a.sno not in (select top 2 sno from sc where cno = 4 order by scoredesc)
  and a.sno = b.sno
group by a.sno

 

 

* *

Java知音公众号整理一些各大公司常用的面试笔试题,供大家在每天闲暇之余学习其中几道题目,日积月累,等到出去面试时,一切都水到渠成,面试时就自然会游刃有余了。

01
子查询: A表是外来专家申请的学院系研究表
         B表是外来专家申请的研究所研究表
---查询是否有外来人员违规申请学院系又申请研究所的相关人员信息

create table univ_subject(name varchar2(12) not null,
 per_id number not null,dept_name varchar2(20));

insert into univ_subject values('gaoqianjing',1001,'xinxixi');

insert into univ_subject values('wangbing',1002,'wulixi');

insert into univ_subject values('liming',1003,'huaxuexi');

create table colle_subject(colle_name varchar2(20),per_id number);

insert into colle_subject values('dianziyanjiu',1001);
insert into colle_subject values('wuliyanjiu',1005);

--
SQL> select name, per_id from univ_subject where per_id in(select per_id from colle_subject);

02A学生表,B学生特长表=》查询所有学生的特长是啥,无特产空值;外连接
 SQL>  create table students
      (
         st_id    varchar2(20),
         name  varchar2(10),
         age      number(2),
         tol_score   number(3)
      ) ;
   insert into students values('973231','wangbindu',22,501);
   insert into students values('973232','zhuzhijing',21,538);

   insert into students values('973233','gaojing',21,576);

SQL>  create table student_skill
     (
        st_id  varchar2(20),
        skill    varchar2(20)
    );
  insert into student_skill values('973231','lanqiu');
  insert into student_skill(st_id) values('973232');

  insert into student_skill values('973233','zuqiu');

select a.name,b.skill from students a left join student_skill b on
 (a.st_id=b.st_id);

03集合运算:UNION/UNION ALL /INTERSECT/MINUS

还是上表:使用union,

SQL> delete student_skill where st_id=973232;
SQL> select st_id from students;
973231
973232
973233

SQL> select st_id from student_skill;
973231
973233

SQL> select st_id from students
  2   union
  3   select st_id from student_skill;

ST_ID

973231
973232
973233        union  union all区别,都是交集 自己本身独有的,
union交集去重复,取一个值,all不去重复

SQL> select st_id from students
  2   union all
  3   select st_id from student_skill;

ST_ID

973231
973232
973233
973231
973233

----------------------------------intersect:只取交集,重复
SQL> select st_id from students
     intersect
     select st_id from student_skill;

ST_ID

973231
973233

select st_id from students
     minus
     select st_id from student_skill;
A-B

--反向:no rows selected  空值

select st_id from student_skill
     minus
     select st_id from students;

04:自我连接 连接符》查询员工表,员工 work for (经理名) manager_name
SQL> select a.ename||' work for '||b.ename from scott.emp  
     a join scott.emp b on (a.mgr=b.empno);

05:简单视图,查询部门工资 名称;
create or replace view bumengongzi as 
 select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b where a.deptno=b.deptno group by a.dname;
insufficient privileges
SQL> grant select any table to yang;
SQL> select * from bumengongzi;

DEPT            TOTAL_SAL


ACCOUNTING          16650
RESEARCH             8675
SALES                9400

06:合计视图:   显示部门工资,奖金,所有发的钱
SQL> update emp set comm=0 where comm is null;

create or replace view emp_he as select deptno,sum(sal) gongzi,sum(comm)
 jiangjin,sum(sal) sum(comm) zonghe from scott.emp group by deptno;
SQL> grant create view to scott;
SQL> select * from emp_he;

    DEPTNO     GONGZI   JIANGJIN     ZONGHE


        30       9400       2200      11600
        20       8675          0       8675
        10      16650        300      16950

07:描述父子关系:
select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null connect by prior empno=mgr
NAME                      EMPNO        MGR
KING                       7839
    JONES                  7566       7839
        SCOTT              7788       7566
            ADAMS          7876       7788

SQL> select lpad('',4*(level-1))||ename ename,empno,mgr from scott.emp
  2   start with mgr is not null
  3   connect by empno=prior mgr;

08:
设三个关系:
学生表S( SNO学号, SNAME名字  , AGE出生日期 , SEX性别)
成绩SC(SNO学号,  CNO 课程编号  , GRADE 成绩 )
课程表 C (CNO课程编号,课程名称  CNAME ,教师编号 TEACHER)

1/查询所有科目都及格的学生学号?
select sno from sc having(min(grade))>=60 group by sno;

2/查询:分数》90又有不及格的学生学号?
select sno from sc where grade>90 and sno in (select sno from sc
 where grade <60);

3/查询平均分不及格的课程号和平均成绩
 select cno,avg(grade) from sc having(avg(grade))< 60 group by cno;

4/查询至少选修过2号学生选修的全部课程,的学生号;
select distinct sno,sname from s where 
 exists(select sno from sc where 
 exists( select cno from sc where sno=2) ) and sno not in(2);

只知道学号;第一步根据学号,找出选修课程
            第二步,根据选秀的课程编号,找出选修了该课程的学生学号
            第三步,根据课程的学生学号,排除2号自己所有学生

有人提供答案看不懂:select distinct sno from sc as scx
 where not exists (select * from sc as scy 
    where scy.sno='2' and not exists (select * from sc scz
        where scz.sno=scx.sno and scz.sno=scy.cno));

5:求各门课程去掉一个最高分和最低分后的平均分;
正常情况: select avg(grade) from sc group by cno;
select avg(grade) from sc where grade not in(select
 min(grade) from sc group by cno)
and  not in(select
 max(grade) from sc group by cno));

6:查询七号课程没有考试成绩的学生学号?
select sno from sc where cno='7' and grade is null;

7:查询7号课程成绩在90分以上或60分以下的学生学号。
select sno from sc where cno='7' and grade >90 or grade <60;

select sno from sc where cno='7' and grade not between 90 and 60;

8:查询每个学生所有课程的平均成绩,输出学生学号和平均成绩;
select sno,cno,avg(grade) from sc group by sno,cno;

9:查询每门课程的选修人数,输出课程号和选修人数。
select count(*),cno from sc group by cno;

10:查询7号课程的学生的学号、姓名、性别。
select sno,sname,sex from s where exists(select * from sc
 where cno='7');

11:查询选修7号课程的学生的平均年龄:

select avg(sysdate-sex) from s where exists(select * from sc
 where cno='7');

12:查询有30名以上学生选修的课程号
select cno from sc having(count(*))>30 group by cno;

13:查询至今没有考试不及格的学生学号:
select sno from sc where exists
(select count(sno) from sc where grade>60  
 having(count(sno)>(select count(distinct cno) from sc)  group by sno);

14:查询所有考试成绩的平均分相同的学生学号分组
select sno,sum(grade) from sc group by sno;

学生表S( SNO学号, SNAME名字  , AGE出生日期 , SEX性别)
成绩SC(SNO学号,  CNO 课程编号  , GRADE 成绩 )
课程表 C (CNO课程编号,课程名称  CNAME ,教师编号 TEACHER)

exists

15:找出选修课程号为C2的学生学号与成绩
select sno,grade,cno from sc where cno='C2';

16:找出选修课程号为C4的学生学号与姓名
select a.sno,a.sname from s a join sc on (a.sno=sc.sn0 and cno='C4');

17:找出选修课程名为maths的学生学号与名称;
select a.sno,a.sname from s a join sc b on (a.sno=b.sno) join
 c on (b.cno=c.cno and c.cname='Maths');

18:找出选修课程为C2或C4的学生学号
select distinct sno from sc where cno in (c2,c4);
select distinct sno from sc where cno='c2' or cno='c4';

19:找出选修课程号C2和C4的学生学号
select distinct sno from sc where cno='C2' and cno='C4';xxxxxx

select distinct sno from sc where cno='C2' and sno in(
select sno from sc where cno='C4');

20:找出不学C2课程的学生姓名和年龄
select a.sname,(sysdate-age)/365 from s a join sc b on (a.sno=b.sno and b.cno not in('C2'));

转换可以通过hr,查询验证信息
select a.last_name,trunc((sysdate-hire_date)/365) from employees a join departments b
 on(a.department_id=b.department_id and b.department_id not in (20));
相减,天数,

21:找出选修了数据库课程的所有学生姓名;
select distinct a.sname from s join sc on(s.sno=sc.sno )
 join c on (sc.sno=c.sno and c.name='shujuku');

22:找出数据库课程不及格的女生姓名;
select  a.sname from s join sc on(s.sno=sc.sno and a.sex='nv'and s.grade<60)
 join c on (sc.sno=c.sno and c.name='shujuku');

23:找出各门课程的平均成绩,输出课程名和平均成绩
select c.cname,avg(grade) from c join sc on (sc.cno=c.cno) group by c.cname;

24:找出各个学生的平均成绩,输出学生姓名和平均成绩
select s.sname,avg(grade) from s join sc on (s.sno=sc.sno) group by sname,sno;

25:找出至少30个学生选修的课程名
select cname from c where cno in (select cno from sc having(count(*))>=30 group by cno where sc.cno=c.cno);

26:找出选修了不少于三门课程的学生姓名;
select sname from s join sc on (s.sno=sc.sno and sc.sno in(select sno
 from sc having(count(*))>3 group by sno));

select sname from s where sno in (select sno from sc where having(count(*))>3 group by sno);

27:找出各门课程的成绩均不低于90分的学生姓名;
select sname from s join sc on (s.sno=sc.sno) group by sc.sno having(min(grade))>=90;

28:找出数据库课程成绩不低于该门课程平均分的学生姓名;

找出每门课程的平均分;
select sname from s;
select avg(grade) from sc group by cno;
select cno from c where cname='shujuku';

select sname from s join sc on(s.sno=sc.sno) join c on (sc.cno=c.cno and c.cname='shujuku')
 where sc.sno in(select sno from sc grade>(select avg(grade) from sc group by cno));

或者
select sname from s,sc,c where s.sno=sc.sno and sc.cno=c.cno and cname='shujuku' and
 grade > (select avg(grade) from sc,c where sc.cno=c.cno and c.name='shujuku');

29:找出每个课程的男女学生平均年龄和人数;

--男多少
select count(*) from s where sex='nan';

--平均年龄
 select   avg(trunc((sysdate-hire_date)/365)) from s;

select avg(trunc((sysdate-hire_date)/365)),count(*) 
 from s join sc on (s.sno=sc.sno) group by cno,sex;

30:查询每门课程的及格率:

-课程及格的人数;
select cno,count(*) a from sc  where grade>=60 group by cno;

-课程总人数;
select cno,count(*) b from sc  group by cno;

--
select cno,a/b from (select cno,count(*) a from sc  where
 grade>=60 group by cno) a join (select cno,count(*) b 
from sc  group by cno) b on (a.cno=b.cno);

这种情况经常查询,可以建立视图;
create view v_a(cno,cren) as select cno,count(*) from sc group by cno;

create view v_b(cno,cren_jige) as select cno,count(*) from sc
 where grade>=60 group by cno;

--select v_a.cno,cren_jige/cren from v_a,v_b where v_a.cno=v_b.cno;

31:查询平均分不及格的学生的学号,姓名,平均分;
select sc.sno,s.sname,avg(grade) from sc join s on(sc.sno=s.sno)
 havging(avg(grade))<60 group by sc.sno,s.sname;

select sno,avg(grade) from sc where sno in(select sno from sc
 having(avg(grade))<60 group by sno);

32:查询平均分不及格的学生人数;

select count(*) from sc
 having(avg(grade))<60 group by sno; ----每个学生几门不及格的科目;

select count(*) from sc where sno in(select sno from sc
 having(avg(grade))<60 group by sno);

33:表:YWY
列:yname 业务员姓名
    ono 办公室编号
    ysex性别
    salary工资
    yno 业务员编号

FP
    fdate 时间
    kno 客户号
    fmoney 金额
    yno 业务员编号

KH客户信息表
    客户号 kno
    客户姓名kname
    客户电话 phone

 34:--查询工资在1000到3000元之间男性业务员姓名和办公室编号;
 35:--查询各个办公室的业务员人数,输出办公室编号和对应的人数;
 36:--查询每个客户在2002年5月份购买的总金额,输出客户号和对应的总金额;
select kno,sum(fmoney) from fp where 
 fdate between to_date('2002/05/01','yyyy/mm/dd') and ('2002/05/31','yyyy/mm/dd')
 group by kno;
 37:--查询每个在2002年5月购买次数超过5次的所有客户号
select kno from fp having(count(*))>5 and fdate  between to_date
('2002/05/01','yyyy/mm/dd') and to_date('2002/05/31','yyyy/dd/dd')
  group by kno;

38:查询各办公室男性和女性业务员的平均工资
select ono,ysex,avg(salary) from ywy group by ono,ysex;

39:查询2002年5月曾经在王海亮业务员手中购买过商品的客户号、客户姓名和联系电话;
select yno from ywy where yname='wanghailiang';
select distinct kno from fp where yno=(select yno from ywy where yname='wanghailiang')
 and fdate between to_date('2002/05/01','yyyy/mm/dd') and
 to_date('2002/05/31','yyyy/mm/dd');

select kno,kname,phone from kh where kno in (上方 );

40:查询所有工资比1538高的业务员编号、姓名、工资;
select salary from ywy where yno=1538;
select yno,yname,salary from ywy where salary > (上);

41:查询所有与1538号业务员在同一个办公室的其他业务员的编号和姓名;
42:查询销售金额最高的业务员的编号;
select yno from ywy from yno =(select yno from fp having(fmoney)>=
all(select sum(fmoney)) group by yno);

43:查询所有业务员的编号、姓名、工资及工资比他高的其它业务员的平均工资;
select a.yno,a.yname,a.salary,avg(b.salary) from ywy a join ywy b
 on (a.yno=b.yno) where a.salary<b.salary group by a.ynoa.yname,a.salary
;

------------------以下开始练习子查询、exists用法--------------------
44:
已知关系模式:
S(sno,sname,sex,sage,sdept);
学号、姓名、性别、出生日期
sc(sno,cno,grade)
学号、课程编号、成绩
c(cno,cname,teacher)
课程编号、课程名称、教师

44:查询选修了数据库课程的学生学号和姓名;
select sno,sname from s where sno in(select sno from sc
 where cno=(select cno from c where cname='SHUJUKU'));

=使用exists;
select sno,sname from s where exists(select * from sc where sc.sno=s.sno
 and exists(select * from c where c.cno=sc.cno and c.cname='SHUJUKU'));

45:查询没有选修数据库课程的学生学号和姓名:

select sno,sname from s where sno not in(select sno from sc
 where cno=(select cno from c where cname='SHUJUKU'));

=使用exists;
select sno,sname from s where not exists(select * from sc where sc.sno=s.sno
 and exists(select * from c where c.cno=sc.cno and c.cname='SHUJUKU'));

46:查询只选修了数据库课程的学生学号和姓名;
select sno,sname from s where sno not in(sno from sc where cno not in(
select cno from c where cname='SHUJUKU'));
--找出数据库的课程编号,选取除了数据库之外的所有课程编号;
--找出选取了除数据库之外的任一课程的学生学号
--排除这些选取了任一课程的学生学号,剩下的唯一 空值;
--需要cno课程编号Not null;

--EXISTS

select sno,sname from s where not exists(
select * from sc where sc.sno=s.sno and not exists(
select * from c where cname='SHUJUKU' and c.cname=sc.cno))

47:查询选修了全部课程,而且每门课程都在80分以上的学生学号 姓名;

选择了全部课程的学号,
select sno from sc having(count(*))=(select count(distinct cno) from c)

 group by sno;

select sno from sc having(min(grade))>80 group by sno;

select department_id from employees having(min(salary))>2000 group by department_id;
select department_id from employees having(count(*))=5 group by

department_id;

select sno,sname from s where sno in
(select sno from sc having(count(*))=(select count(distinct cno) from c)
 group by sno 
 intersect
 select sno from sc having(min(grade))>80 group by sno);
OK

本文由3522vip发布于网络数据库,转载请注明出处:sql 语句练习与答案_MsSql_脚本之家

关键词: 3522vip

上一篇:Microsoft SQL SERVER 2008技术内幕 T-SQL语言基础(上)

下一篇:没有了