首页 > 试题广场 >

Student(S#,Sname,Sage,Ssex) 学生

[问答题]
Student(S#,Sname,Sage,Ssex) 学生表,

Course(C#,Cname,T#) 课程表,

SC(S#,C#,score) 成绩表,

Teacher(T#,Tname) 教师表 

其中,S#代表学生号,C#代表课程号,T#代表教师号。

根据上述描述,请写出如下的查询语句:

1、查询姓“李”的老师的个数;

2、查询没学过“叶平”老师课的同学的学号、姓名;

3、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名。

1.先求出所有的001的S#
2.查出所有002的且符合 001的数据
select Student.* from SC,Course,Student where SC.S#=Course.C# and Student.S#=SC.S# and Course.Cname='002' and S# in
(select S# from SC,Course where SC.S#=Course.C# and Course.Cname='001')
发表于 2020-08-16 19:44:53 回复(0)
1.select  count(*) from Teacher where Tname like "李%"
2.select  S#,Sname from Student where S#   NOT IN(select   S#    from   SC   where   C#    =   (select   C#   from Course  where  T#   =   (select   T#  from Teacher where Tname =  '叶平')))   
3.select   S# ,  Sname  from   Student   where   S#   in   (select    S#    from   SC    where    C#    =  "001"   )

发表于 2019-09-22 17:18:06 回复(0)
1、select count(*) from Teacher where Tname  like '李%'

2、select S#,Sname from Student where S# not in(select S# from SC where C# in(select C# from Course,Teacher where Tname='叶平' and 
Course.T#  =  Teacher.T#))

3、select S#,Sname from Student where S# in
(select S# from SC where C# = 001 or C# = 002 
group by S#
having count(S#)=2)
发表于 2019-09-16 15:41:26 回复(0)

发表于 2019-08-15 15:21:56 回复(0)
1、SELECT COUNT(*) AS 李老师个数 FROM teacher WHERE teacher.Tname LIKE '李%'; 2、SELECT student.`S#` as 学号, student.Sname as 姓名 FROM student WHERE student.`S#` NOT IN ( SELECT sc.`S#` FROM course, teacher, sc WHERE sc.`C#` = course.`C#` AND course.`T#` = teacher.`T#` AND teacher.Tname = '叶平'); 3、SELECT student.`S#` as 学号, student.Sname as 姓名 FROM student WHERE student.`S#` in (SELECT sc.`S#` FROM sc, course WHERE sc.`C#` = course.`C#` AND course.`C#` = 1 AND student.`S#` IN (SELECT sc.`S#` FROM sc as scores WHERE scores.`C#` = 2) );
编辑于 2019-07-22 14:24:37 回复(0)
1、
select count(*)
from Teacher
where Tname like '李%';

2、
select Sname, S#
from Student  s
join in SC  sc
where  s.S#=sc.S# 
and C# in(
select C#
form Course c
join in Teacher   T
where  c.T#=T.T#
and Tname='叶平'
)

3、
select S#,Sname
from Student s
join in SC sc
where sc.S#=s.S#
and C#=001 and C#=002






     
发表于 2019-07-20 10:10:10 回复(1)
1. select count(*) from Teacher where Tname like '李%'; 
2. select S#, Sname from Student where S# not in (select distinct (SC.S#) from Teacher, SC, Course where SC.C# = Course.C#  and Course.T# = Teacher.T# and Teacher.Tname = "叶平");
3. select Student.S#, Student.Sname from Student, SC where Student.S# = SC.S# and SC.C# = "001" and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002' )
发表于 2019-07-03 23:30:38 回复(0)
1.select count(*) from Teacher where Tname like "李%";
2.select S#,Sname from Student where S# not in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平'));
3.select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
发表于 2019-06-10 10:24:54 回复(0)