下载了SQL50个常用语句,有的不怎么明白
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表 9、查询所有课程成绩小于60分的同学的学号、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 可以这样写么:
select SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and score<60; 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');可以这样写么:
update SC
set score=(select avg(score)
from SC )
from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
就是把SC_2去掉,我就不明白为什么要搞一个别名出来自连呢?44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列
select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#
不是要求超过10人才统计么,答案没给出,正确的要怎样写?48、查询两门以上不及格课程的同学的学号及其平均成绩
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;可否写成这样:
select SC.S#,avg(score)
from Student,SC
where Student.S#=SC.S# and score <60
group by S#
having count(*)>2;
嵌套子查询比较不懂,还有isnull(score,0)是啥意思?
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表 9、查询所有课程成绩小于60分的同学的学号、姓名;
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 可以这样写么:
select SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and score<60; 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');可以这样写么:
update SC
set score=(select avg(score)
from SC )
from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
就是把SC_2去掉,我就不明白为什么要搞一个别名出来自连呢?44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列
select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#
不是要求超过10人才统计么,答案没给出,正确的要怎样写?48、查询两门以上不及格课程的同学的学号及其平均成绩
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;可否写成这样:
select SC.S#,avg(score)
from Student,SC
where Student.S#=SC.S# and score <60
group by S#
having count(*)>2;
嵌套子查询比较不懂,还有isnull(score,0)是啥意思?
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 1.select Student.S# from Student,SC where S.S#=SC.S# and score>60
查出>60分的人的学号.2.使用not in
查出不在1里面的学号就是查询所有课程成绩小于60分的同学的学号、姓名
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;可否写成这样:
select SC.S#,avg(score)
from Student,SC
where Student.S#=SC.S# and score <60
group by S#
having count(*)>2;
嵌套子查询比较不懂,还有isnull(score,0)是啥意思?1.select S# from SC where score <60 group by S# having count(*)>2
查询出有两门课程以上不及格的学号2.select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;
查询在1的的学号即可.isnull(score,0),如果score为null,则设置为0.ISNULL
使用指定的替换值替换 NULL。语法
ISNULL ( check_expression , replacement_value ) 参数
check_expression将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。replacement_value在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。 返回类型
返回与 check_expression 相同的类型。注释
如果 check_expression 不为 NULL,那么返回该表达式的值;否则返回 replacement_value。示例
A. 将 ISNULL 与 AVG 一起使用
下面的示例查找所有书的平均价格,用值 $10.00 替换 titles 表的 price 列中的所有 NULL 条目。USE pubs
GO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO下面是结果集:--------------------------
14.24 (1 row(s) affected)B. 使用 ISNULL
下面的示例为 titles 表中的所有书选择书名、类型及价格。如果一个书名的价格是 NULL,那么在结果集中显示的价格为 0.00。USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
ISNULL(price, 0.00) AS Price
FROM titles
GO下面是结果集:Title Type Price
--------------- ------------ --------------------------
The Busy Execut business 19.99
Cooking with Co business 11.95
You Can Combat business 2.99
Straight Talk A business 19.99
Silicon Valley mod_cook 19.99
The Gourmet Mic mod_cook 2.99
The Psychology UNDECIDED 0.00
But Is It User popular_comp 22.95
Secrets of Sili popular_comp 20.00
Net Etiquette popular_comp 0.00
Computer Phobic psychology 21.59
Is Anger the En psychology 10.95
Life Without Fe psychology 7.00
Prolonged Data psychology 19.99
Emotional Secur psychology 7.99
Onions, Leeks, trad_cook 20.95
Fifty Years in trad_cook 11.95
Sushi, Anyone? trad_cook 14.99 (18 row(s) affected)
select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#
不是要求超过10人才统计么,答案没给出,正确的要怎样写?select C# as 课程号,count(*) as 人数
from sc
group by C#
having count(*) > 10
order by 人数 desc , c#