| hahahha |
MySQL |
SQLSever |
| |
select name 姓名,
case
when sex=1 then '男'
else '女'
end as '性别'
from employees |
select name,sex=
case
when sex=1 then '男'
else '女'
end
from employees |
| |
select employeeid,income-outcome as '实际收入' from salary |
select employeeid,realincome=income-outcome from salary |
| |
Create table new_table_name (Select * from old_table_name); |
select employeeid,income into 收入在1500以上员工 from salary where income > 1500 |
| |
|
|
| |
|
|
| |
|
|
| |
|
|
-- 一、创建教学系统的数据库，表，以及数据
--student(sno,sname,sage,ssex) 学生表
--course(cno,cname,tno) 课程表
--sc(sno,cno,score) 成绩表
--teacher(tno,tname) 教师表
|
--14、查询每门课程被选修的学生数
select cno,count(score)as '选课人数'
from sc
group by cno
|
--9、查询所有课程成绩小于60分的同学的学号、姓名
select sno,sname from student where sno not in
(
select distinct sno from sc where score >= 60 )
|
--3、查询所有同学的学号、姓名、选课数、总成绩
select sc.sno,student.sname,count(sc.score) as '选课数',sum(sc.score) as '总成绩'
from sc,student where sc.sno = student.sno
group by sc.sno,student.sname
|
|
-- -- 6、查询同时学过课程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.sno=b.sno
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' )
|
--7、查询学过“叶平”老师所教所有课程的所有同学的学号、姓名
select sno,sname from student where sno in
(
select sno from sc,course where sc.cno = course.cno and course.tno in
(
select tno from teacher where tname = '叶平'
)
)
|
--38、查询最受欢迎的课程****************
select cno 课程名, count(cno) 选修人数
from sc group by cno
having count(cno) = (select top 1 count(cno) from sc group by cno order by count(cno) desc)
|
|
-- -- 22、查询每门课程的平均成绩，结果按平均成绩升序排列
select cno,avg(score) from sc
group by cno
order by avg(score)
|
-- -- 10、查询没有学全所有课的同学的学号、姓名
select student.sno,student.sname from student,sc where student.sno = sc.sno
group by student.sno
having count(sc.score) < (select count(*)from course) |
--37、查询全部学生都选修的课程的课程号和课程名*******
select sc.cno 课程号,cname 课程名 from sc,course
where sc.cno = course.cno
group by sc.cno,cname
having count(sno) = (select count(sname) from student )
|
|
-- -- 39、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select sname from student where sno not in
(
select sno from sc where cno in
(
select cno from course where tno in
(
select tno from teacher where tname = '叶平'
)
)
)
select sname from student
where sno not in
(
select sno from sc,course,teacher
where sc.cno = course.cno and course.tno = teacher.tno and teacher.tname='叶平'
)
|
--32、查询不同课程成绩相同的学生的学号、课程号、学生成绩**********
select sc.sno,sc.cno,sc.score from sc,
(select sc.sno,sc.cno,sc.score from sc)r
where r.score=sc.score and r.cno<>sc.cno
|
--33、查询每门课程成绩最好的前两名的学生ID******************
select * from sc a
where score in
(select top 2 score from sc where a.cno = sc.cno order by sc.score desc)
order by a.cno,a.score desc
|
|
--34、查询各单科状元
select* from sc a
where score = ( select top 1 score from sc where a.cno = sc.cno order by sc.score desc)
order by a.cno,a.score desc
|
--20、查询同名同姓学生名单，并统计同名人数 ********************
select sname,count(sno)
from student
group by sname having count(sno) > 1
|
--14、查询和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) and
count(cno) = (select count(cno) from sc where sno = 2)
--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
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
--24、查询学生平均分及其名次
select
(select count(1)
from (select distinct avg(score) as pjf from sc group by sno) as t2
where pjf >= t1.pjf) as 名次,
sno as 学号,
pjf as 平均分
from (select sno, avg(score) as pjf from sc group by sno) as t1
|
| |
|
|
浙公网安备 33010602011771号