郑州绿博园地址,黑帮龙虎斗,数码宝贝第四部国语全集
题目如下:
1.查询sc表中的全部数据。
2. 查询计算机系学生的姓名和年龄
3.查询成绩在70~80分的学生的学号、课程号和成绩
4.查询计算机系年龄在18~20岁的男生姓名和年龄
s.查询c001课程的最高分
6.查询计算机系学生的最大年龄和最小年龄
7.统计每个系的学生人数
8.统计每]课程的选课人数和最高成绩。
9.统计每个学生的选课门数和考试总成绩，并按选课]数升序显示结果。
10.列出总成绩超过200的学生的学号和总成绩
11.查询选了c002课程的学生姓名和所在系
12.查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果
13.查询与vb在同一学期开设的课程的课程名和开课学期
14.查询与李勇年龄相同的学生的姓名、所在系和年龄
15.查询哪些课程没有学生选修，列出课程号和课程名
16.查询每个学生的选课情况,包括未选课的学生，列出学生的学号、姓名、选的课程号
17.查询计算机系哪些学生没有选课，列出学生姓名
18.查询计算机系年龄最大的三个学生的姓名和年龄
19.列出“vb"课程考试成绩前三名的学生的学号、姓名、所在系和vb成绩
20.查询选课门]数最多的前2位学生，列出学号和选课门数
代码如下:
我要评论-- 1
select *
from sc;
-- 2
select s.sname, s.sage
from student s
where s.sdept = n'计算机系';
-- 3
select sc.sno, sc.cno, sc.grade
from sc sc
where sc.grade between 70 and 80;
-- 4
select s.sname, s.sage
from student s
where s.sdept = n'计算机系'
and s.sage in (18, 20)
and s.ssex = n'男';
-- 5
select max(sc.grade) as max_grade
from sc sc
group by sc.cno
having sc.cno = 'c001';
-- 6
select max(s.sage) as max_age, min(s.sage) as min_age
from student s
group by s.sdept
having s.sdept = '计算机系';
-- 7
select concat(s.sdept, ' : ', count(s.sno)) as stu_nums
from student s
group by s.sdept;
-- 8
select sc.cno as cno, count(sc.sno) as c_nums, max(sc.grade) as max_grade
from sc sc
group by sc.cno;
-- 9
select count(sc.cno) as c_nums, sum(sc.grade) as sum_grades
from sc sc
group by sc.sno
order by c_nums;
-- 10
select sc.sno, sum(sc.grade) as sum_grades
from sc sc
group by sc.sno
having sum(sc.grade) > 200;
-- 11
select s.sname, s.sdept
from sc sc
inner join student s
on sc.cno = 'c002';
-- 12
select s.sname, sc.cno, sc.grade
from sc sc
inner join student s on sc.sno = s.sno
group by s.sname, sc.cno, sc.grade
having sc.grade > 80
order by sc.grade desc;
-- 13
select c.cno, c.semester
from course c
where c.semester = (select semester from course where cname = 'vb')
and c.cname <> 'vb';
-- 14
select s.sname, s.sdept, s.sage
from student s
where s.sage = (select sage from student where sname = n'李勇')
and s.sname <> n'李勇';
-- 15
select c.cno, c.cname
from course c
where c.cno not in (select sc.cno from sc sc);
--16
select s.sno,
s.sname,
cno=stuff((
select ',' + trim(c.cno)
from course c,
sc sc1
where s.sno = sc1.sno
and sc1.cno = c.cno
for xml path ('')), 1, 1, '')
from sc sc
right join student s on sc.sno = s.sno
group by s.sno, s.sname;
-- 17
select s.sname
from student s
where s.sno not in (select sc.sno from sc sc);
-- 18
select
top 3
s.sname
,
s.sage
from student s
where s.sdept = n'计算机系'
order by s.sage;
-- 19
select
top 3
s.sno
,
s.sname
,
s.sdept
,
sc.grade
from course c
inner join sc sc on c.cno = sc.cno
inner join student s on sc.sno = s.sno
where c.cname = 'vb';
--20
select
top 2
sc.sno
,
count(sc.cno) as course_nums
from sc sc
group by sc.sno;
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论