【数据库】相关代码集

 

 

Create Table student
(
    Sno char (9) Primary key , 
    Sname char (20) Unique ,
    Ssex char (2) ,
    Sage smallint , 
    Sdept char(20)

);

Create Table Course
(
    Cno char (4) Primary key ,
    Cname char (40) not Null , 
    Cpno char (4) ,
    Ccredit smallint 
    foreign key (Cpno) References Course(Cno)
);

Create Table SC
(
    Sno char (9) ,
    Cno char (4) , 
    Grade smallint 
    
    Primary key ( Sno , Cno ) ,
    Foreign key ( Sno ) References Student (Sno),
    Foreign key ( Cno ) References Course (Cno)     
    
);

Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215121','李勇','男',20,'CS');
Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215122','刘晨','女',19,'CS');
Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215123','王敏','女',18,'MA');
Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215125','张立','男',19,'IS');


Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('2','数学',NULL,2)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('6','数据处理',NULL,2)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('4','操作系统','6',3)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('7','PASCAL语言','6',4)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('5','数据结构','7',4)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5',4)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('3','信息系统','1',4)

/*
drop table SC;
drop table Course;
drop table student;
*/


Insert Into SC(Sno,Cno,Grade)
values ( '201215121' , '1' , 92 )
Insert Into SC(Sno,Cno,Grade)
values ( '201215121' , '2' , 85 )
Insert Into SC(Sno,Cno,Grade)
values ( '201215121' , '3' , 88 )
Insert Into SC(Sno,Cno,Grade)
values ( '201215122' , '2' , 90 )
Insert Into SC(Sno,Cno,Grade)
values ( '201215122' , '3' , 80 )

 

Create Table student
(
    Sno char (9) Primary key , 
    Sname char (20) Unique ,
    Ssex char (2) ,
    Sage smallint , 
    Sdept char(20)

);

Create Table Course
(
    Cno char (4) Primary key ,
    Cname char (40) not Null , 
    Cpno char (4) ,
    Ccredit smallint 
    foreign key (Cpno) References Course(Cno)
);

Create Table SC
(
    Sno char (9) ,
    Cno char (4) , 
    Grade smallint 
    
    Primary key ( Sno , Cno ) ,
    Foreign key ( Sno ) References Student (Sno),
    Foreign key ( Cno ) References Course (Cno)     
    
);

Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215121','李勇','男',20,'CS');
Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215122','刘晨','女',19,'CS');
Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215123','王敏','女',18,'MA');
Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
values('201215125','张立','男',19,'IS');


Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('2','数学',NULL,2)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('6','数据处理',NULL,2)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('4','操作系统','6',3)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('7','PASCAL语言','6',4)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('5','数据结构','7',4)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5',4)
Insert Into Course(Cno,Cname,Cpno,Ccredit)
values('3','信息系统','1',4)

/*
drop table SC;
drop table Course;
drop table student;
*/


Insert Into SC(Sno,Cno,Grade)
values ( '201215121' , '1' , 92 )
Insert Into SC(Sno,Cno,Grade)
values ( '201215121' , '2' , 85 )
Insert Into SC(Sno,Cno,Grade)
values ( '201215121' , '3' , 88 )
Insert Into SC(Sno,Cno,Grade)
values ( '201215122' , '2' , 90 )
Insert Into SC(Sno,Cno,Grade)
values ( '201215122' , '3' , 80 )


/*1、查询女生中年龄大于19岁的学生姓名*/
Select Sname
From student
where Sage>=19 and Ssex = '女';

/*2、查询计算机系的学生学号、姓名、出生年份，
按出生年份降序显示（给出生年份起个别名）；*/

Select Sno 学生学号 , Sname 姓名 , 2019 - Sage 出生年份
From student 
Order by 2019 - Sage Desc;

/*3、查询没有先行课的课程号、课程名；*/

Select Cno 课程号 ,Cname 课程名 
From Course
Where Cpno IS NULL;

/*4、查询选修了课程的学生学号；*/

Select distinct Sno  学生学号
From SC;

/*5、查询年龄在18到20岁之间的学生学号、姓名、性别；*/
Select Sno 学生学号, Sname 姓名 , Ssex 性别 
From student
Where Sage between 18 and 23;

/*6、查询是计算机系或数学系的学生姓名，年龄；*/
Select Sname 学生姓名 , Sage 年龄
From student
Where Sdept in ('CS','MA');

/*7、查询课程名含有‘系统’的全部信息；*/
Select Cno 课程号 , Cname 课程名称 , Cpno 先行课号 , Ccredit 学分
From Course
Where Cname Like '%系统%';

/*8、查询学号倒数第二位是‘2’的学生姓名、院系；*/
Select Sno 学号 , Sname 学生姓名 , Sdept 院系
From student 
Where Sno Like '%2_' ;

/*9、查询被选修了的课程号；*/
Select distinct Cno 已选修的课程号
From SC ;

/*10、查询学分大于3的课程号和课程名；*/
Select Cno  课程号 , Cname 课程名 , Ccredit 学分
From Course 
Where Ccredit > 3 ;

//按照数据库 （1号课）从高到低 排序

select grade *
from SC
where Cno = '1'
order by grade Desc ;

//把所有课的成绩从高到低排序
select grade *
from SC
where grade is not NULL
order by grade Desc ;

//统计每门课的可好，及其最高成绩
select Cno , max( grade )
from SC
group by Cno ;

//按照每个人的总成绩降序
select Sno , sum(grade)
from SC
where grade is not NULL
group by Sno
order by sum(grade) Desc ;

 

 

1、统计每个同学超过80分的课程的数目超过5门，输出对应的学号及课程号。

方法1:

Select Sno , Cno 
from SC
Group by Sno having count( grade > 80 ) > 5 ;

方法2:
Select Sno , Cno
from SC
where grade > 80
group by Sno having Count(Cno) > 5;


2、间接先行课
Select A.Cno 课程号, B.Cpno 间接先行课
from course A  , course B
where A.cpno = B.Cno;

3、查询与“刘晨”同学是同一个学院的学生 ,输出对应的学号，姓名，性别
Select B.Sno , B.Sname , B.Ssex
from student A , student B
where A.Sname = '刘晨' and A.Sdept = B.Sdept

嵌套查询
1、查询和“刘晨”同学同一个专业的学生，输出Sno,Sname
Select Sno, Sname
from student
where Sdept in(

select Sdept
from student
where Sname = '***'
);

2、查询有间接先行课的课程号
Select Cno
from Course
where Cpno in(

select Cno
from Course
where Cpno is Not Null
);

3、查询"数据库" 高于平均分的 学生->学号

Select Sno
from SC
where grade >
(
     select AVG(grade)
     from SC
     where Cno in (
          select Cno 
          from Course
          where Cname = '数据库' 
     )      
) and Cno in (
     select Cno
     from Course
     where Cname = '数据库'
);

方法2:
select Sno
from SC,course
where Sc.cno = course.Cno and Cname = '数据库' 
and grade > 
(
     select AVG(grade)
     from Sc , Course
     where Sc.cno = course.Cno and Cname = '数据库'
)

实验名称：实验三：数据库连接查询和聚集函数
实验类型：设计型

实验要求和目的：
1、熟练掌握聚集函数
2、掌握连接查询的使用


实验步骤：
一、初始化数据库，还原三个表及数据；
二、实现以下查询：
1、查询每个学生的平均成绩；（输出学号、平均成绩）
2、查询每个学生的平均成绩；（输出学号、姓名、平均成绩）
3、查询数据库课程的最高分；
4、查询选修了1号课或3号课的学生姓名；
5、查询每个学院的学生人数；
6、查询计算机系的女生所有信息；
7、查询跟刘晨一个院系的学生学号、姓名；
8、查询选修人数超过2个人的课程号、课程名；
9、查询男女生人数；
10、按总成绩降序显示学生的学号、姓名、总成绩；

/*1、查询每个学生的平均成绩；（输出学号、平均成绩）*/
Select Sno 学号 , AVG(grade)平时成绩
From SC 
group by Sno ;

/*2、查询每个学生的平均成绩；（输出学号、姓名、平均成绩）*/
Select student.Sno 学号 , Sname 姓名 , AVG(grade) 平时成绩
From student , SC 
where student.Sno = SC.Sno
group by student.Sno , Sname ;

/*3、查询'数据库课程'的最高分；*/
Select Course.Cname , MAX(Grade)最高分
From SC,Course
Where Course.Cno = Course.Cno And Course.Cname = '数据库'
Group by Cname ;

/*4、查询选修了1号课或3号课的学生姓名；*/
Select distinct Sname 姓名
from student , SC 
where SC.Sno = student.Sno and (SC.Cno = 1 or SC.Cno = 3) ;

/*5、查询每个学院的学生人数；*/
Select Sdept 学院,COUNT(*) 学生人数
from student
group by Sdept ;

/*6、查询计算机系的女生所有信息；*/
Select Sno 学号 , Sname 姓名 , Ssex 性别 , Sage 年龄 , Sdept 学院
from student 
where Ssex = '女' and Sdept = 'CS';

/*7、查询跟刘晨一个院系的学生学号、姓名；*/
Select A.Sno 学生学号 , A.Sname 姓名
from student A , student B 
where B.Sdept  = A.Sdept and B.Sname = '刘晨' ;

/*8、查询选修人数超过2个人的课程号、课程名；*/
Select SC.Cno 课程号 , Cname 课程名
from SC , Course 
where SC.Cno = Course.Cno 
group by SC.Cno , Cname having COUNT(Sno) >= 2 ;


/*9、查询男女生人数；*/
Select Ssex 性别, COUNT(Sno) 人数
from student 
group by Ssex ;

/*10、按总成绩降序显示学生的学号、姓名、总成绩；*/
Select stu.Sno 学号 , Sname 姓名 , SUM(Grade) 总成绩
from student stu , SC
where stu.Sno = SC.Sno 
group by stu.Sno , Sname 
order by SUM(Grade) DESC ;

1、利用嵌套查询：与刘晨同专业的姓名和学号

select Sno,Sname 
from Student
where Sdept in(
     Select Sdept
     From Student
     where Sname = '刘晨'
)

2、查询选修了课程名为“信息系统”的学生学号，姓名

Select Sno , Sname 
From Student 
Where Sno in (
     Select Sno 
     from Sc 
     where Cno in (
           Select Cno
           From Course
           where Cname = '信息工程'
     )
)    


3、查询比自身平均分高的那门课
Select Cno
From Sc X
where Grade > (
      select AVG(grade)
      from Sc Y
      where Y.Sno = X.Sno
)

4、查询除CS外的专业的学生，比CS中任意一个年纪大的学号和姓名。
Select Sno , Sname
from Student
where age < ANY(
     Select Sage 
     from Student
     where Sdept = 'CS'
)and Sdept <> 'CS'

5、查询选修了全部课程的学生姓名

Select Sname 
from Student 
Where NOT EXIST (
    Select *          (Cno)
    from Course 
    where NOT EXIST(
           Select *
           From Sc
           where Sc.Cno = Course.Cno and Sc.Sno = Student.Sno
    )
)

/*1、查询选修了1号课的学生姓名（用2种方法实现）*/

/* 方法1 - 嵌套查询*/
Select Sname 学生姓名
From student 
where Sno in (
    Select Sno 
    From SC
    where Cno = '1' 
);

/* 方法2 - 外部连接*/
Select Sname 学生姓名
From SC , student 
where SC.Sno = student.Sno and SC.Cno = '1' ;

/* 方法3 - Exists*/
Select Sname 学生姓名
From student 
where Exists(
    select *
    From SC
    where Cno = '1' and student.Sno = SC.Sno 
);

/* 2、查询没有选修1号课的学生学生姓名 */

/* 利用NOT in 语句*/
Select Sname 学生姓名
From student 
where Sno Not in(
    select Sno
    from SC
    where student.Sno = SC.Sno and SC.Cno = '1' 
);

/* 利用NOT EXISTS语句*/
Select Sname 学生姓名
From student
where NOT EXISTS(
    select *
    From SC
    where student.Sno = SC.Sno and SC.Cno = '1' 
);


/*3、查询没有选修数据库的学生姓名*/
Select Sname 学生姓名
From student
where EXISTS(
    Select *
    from Course
    where Cname = '数据库' and NOT EXISTS(
        Select *
        From SC
        where student.Sno = SC.Sno and Course.Cno = SC.Cno 
    )
);

/*4、查询计算机学院男生中比该学院所有女生年龄都大的学号、姓名、年龄；*/
Select Sno 学号 , Sname 姓名 , Sage 年龄
from student A
where A.Ssex = '男'
and A.Sage > ALL(
    Select Sage 
    From student B
    where B.Ssex = '女' and A.Sdept = B.Sdept 
)
and A.Sdept = 'CS'
;

/*5、查询比自己平均成绩低的学生学号、课程号、课程名；*/
Select Sno 学生学号 , X.Cno , Course.Cname 
From SC X, course
where X.Cno = Course.Cno  and Grade < (
    Select AVG(Grade)
    From SC Y
    where Y.Sno = X.Sno
);

/*6、查询每个学生的选课情况；（输出学号、姓名、课程号、成绩）在kingbase 里实现；*/
Select stu.Sno 学号 , Sname 姓名 , Cno 课程号 , Grade 成绩
From student stu, SC 
where stu.Sno = SC.Sno ;

/*7、查询选修了1号课和3号课的学生姓名；*/

/*方法1 ：利用两次查询取交集*/
Select Sname 学生姓名
from student
where Sno in (
    Select Sno
    From SC
    where SC.Sno = student.Sno and Cno = '1'
) and Sno in (
    Select Sno
    From SC
    where SC.Sno = student.Sno and Cno = '3'
);

/*方法2 ：直接利用Union语句*/
Select Sname 学生姓名
from student
where Sno in(

    Select Sno
    from SC 
    where Cno = '1'
    
    intersect
    
    Select Sno
    from SC 
    where Cno = '3'
);


/*---------------------------------*/

/*1、将数据库课程的学分改为5；*/
/*注释 ：未修改前为 ：4学分*/
Update Course
Set Ccredit = 5
where Cname = '数据库';

/*2、删除刘晨的所有信息；*/
Delete 
From SC 
where Sno in(
    Select Sno
    From student
    where SC.Sno = student.Sno and Sname = '刘晨'
);

Delete
From student
where Sname = '刘晨'

1、查询每个学生的选课数量 

Select Sno , Count(Cno)
from Sc
Group by Sno
having Count(*) = 5 ;

2、查询选了所有的课程的 学生

Select Sname
from Student
Where Sno in(
     Select Sno 
     From Sc 
     Group by Sno
     Having count(*) = (
             Select count(*)
             from course 
     )
)

3、查询至少选修了学生2015122选修的全部课程的学生学号
方法1:

Select distinct Sno
From Sc Scx
where Not exists(
     Select *
     from Sc Scy
     where Scy.Sno = '2015122' and
     not exists(
           select *
           from Sc Scz
           where Scz.Sno = Scx.Sno and
                     Scz.Cno = Scy.Sno)    
     ) 
);

方法2:
Select Distinct Sno
from Sc
where Cno in(
        Select Cno
        from Sc 
        where Sno = '2015122'
)
Group by Sno
Having count(*) = (
      Select count(*)
      from Sc
      where Sno = '2015122'   
);

10-16 课堂讲解事例

1、查询　每门课都及格　的学生的学号

Select distinct Sno
from Sc A
where Not exists(
     Select *
     from Sc B
     where A.Sno = B.Sno and B.Grade < 60
);

2、查询　既有　“课程大于90分”　又有“课程不及格”　的学生的学号
Select distinct A.Sno
from Sc A
where exists(
    Select *
    from Sc B
    where A.Sno = B.Sno and Grade > 90 
) and exists (
    Select *
    from Sc C
    where A.Sno = C.Sno and Grade < 60
)
Group by Sno


3、查询 "平均分不及格的课程号"　和　"平均成绩"

select Cno , AVG(Grade)
from Sc
Group by Cno
having AVG(Grade) < 60



4、找出至少选修了２号学生选修过的全部课程的学生号
Select Sno
from Sc
where Cno in(
    Select Cno
    from Sc
    where Sno = '201215122'
)
Group by Sno
Having count(*) = (
    Select count(*)
    from Sc
    where Sno = '201215122'
);

5、查询7号课程没有考试成绩的学生学号

Select Sno
from Sc
where Cno = '7' and Grade is NULL


6、查询7号课程成绩在90分以上或60分以下的学生学号

Select Sno
from Sc 
where Cno = '7'  and ( Grade < 60 or Grade > 90 )



7、查询课程名以"数据"两个字开头的所有课程的课程号和课程名

Select Cno , Cname
from Course
where Cname like '数据%'


8、查询每个学生所有课程的平均成绩,输出学生学号和平均成绩

Select Sno , AVG(Grade)
from Sc
group by Sno


9、查询选修7号课的学生的学号、姓名、性别
Select A.Sno 学号 , A.Sname 姓名 , A.Ssex 性别
from student A , Sc B
where A.Sno = B.Sno and B.Cno = '2'



10、查询有30名以上的学生选修的课程号
Select Cno
from Sc
Group by Cno
Having count(*) >= 30 ;

11、查询至今没有考试考试不及格的学生学号

Select Sno
from Sc A
where Not exists(
    Select *
    from Sc B
    where B.Sno = A.Sno and ( B.Grade is NULL or B.Grade < 60 )
)
group by Sno



12、查询没有选修1号课的学生 输出其姓名(用3种方法实现)

方法1:
Select distinct A.Sno
from Sc A
where ( Select Cno 
     from Sc B 
where A.Sno = B.Sno and B.Cno = '1' ) is NULL

方法2:
Select Sno
from Student
Except
Select Sno
from Sc
where Cno = '1'


方法3:
Select distinct Sno , Sname 
from Student 
where not exists (
    select *
    from Sc
    where Cno = '1' and student.Sno = Sc.Sno
)

13、查询至少选修了刘晨选的全部课程的学生学号
Select Sno
from Sc 
where Cno in ( 
    Select Cno
    from Sc , Student 
    where student.Sno = Sc.sno and Sname ='刘晨'
)
Group by Sno
having count(*) = (
    Select count(*)
    from Sc , Student 
    where student.Sno = Sc.sno and Sname ='刘晨'
)

14、查询每个学院学生的平均年龄,并把结果插入到一个新表Sdep_Avg(Sdep,age);

Create table Dept_age(
    Sdept char(20) ,
    Avg_age smallint
);

Insert into
dept_age( Sdept , Avg_age )
Select Sdept , AVG( Sage )
from Student 
group by Sdept ;

15、将"刘晨"的信息系统课程成绩改为90分

Update SC
Set Grade = 90 
where Sno in (
    Select Sc.Sno
    from Student , Sc
    where Student.Sno = Sc.Sno and Student.Sname = '刘晨'
)

查询选修了1号课 "或" 2号课的学生学号

Select Sno
From Sc
where Cno = '1'
UNION
Select Sno
From Sc
where Cno = '2'

查询选修了1号课 "和" 2号课的学生学号
Select Sno
from Sc
where Cno = '1' 
Intersect
select Sno
from Sc
where Cno = '2'


查询没有选修1号课的学生学号
方法1:
Select distinct A.Sno
from Sc A
where(
     select Cno
     from Sc B
     where A.Sno = B.Sno and B.Cno = '1'   
) is NULL

方法2:
Select Sno
from Sc 
Except
Select Sno
from Sc
where Cno = '1'

方法3:
Select distinct Sno
from Sc
where Cno = '1' And
Group by Sno
Having count(*) = 0

方法4:
Select distinct Sno
from Sc A
where Not exist(
     Select *
     from Sc B
     where A.Sno = B.Sno and B.Cno = '1'
)

Create table student
(
    Sno char(9) ,
        Constraint StdentKey primary key(Sno) ,
    Sname char(20) 
        Constraint C1 Not Null ,
    Ssex char(2) 
        Constraint C2 Check( Ssex in('男','女') ) ,
    Sage Smallint 
        Constraint C3 Check( 15 <= Sage AND Sage <= 35 ),
    Sdept char(20) 
        Constraint C4 Not Null 
);

Create table Course(
    Cno Char(9) ,

    Cname Char(9) 
        Constraint C1 Unique ,

    Cpno Char(9) ,
    
        Constraint CourseForKey foreign key ( Cpno )
        References Cno( Cpno ) ,

    Ccredit Smallint 
        Constraint C2 Not Null ,

    Constraint CourseKey primary key ( Cno )
);

Create table SC (
    Sno Char(9) ,
    Cno Char(9) ,
    Grade Smallint
        Constraint C1 Check( 0 <= Grade and Grade <= 100 ) ,
    Constraint SCKey1 primary key ( Sno , Cno )
);

Alter table Student 
    Drop Constraint C3 ;
Alter table Student
    Add Constraint C3 Check( 15 <= Sage and Sage <= 50 )


Create table s_count(
    Ssex Char(9),
    Scount Smallint 
);


Insert Into S_count 
values( ‘男’ , 0 )

Insert Into S_count
values( ‘女’ , 0 )

Create trigger count_student 
After Insert On Student 
for each Row
Begin
    if (new.Ssex = ‘男’)
        Update S_count
        Set Scount = Scount + 1 
        where Ssex = ‘男’
    end if

    if (new.Ssex = ‘女’ )
        Update S_count
        Set Scount = Scount + 1
        where Ssex = ‘女’
    end if
End;

Update S_count
set Scount = 0 ;

Delete 
from Student ;


Insert Into
Student
values( '20172899' ,'hhz','男',18,'CS');

Insert Into
Student
values( '20172900' ,'haz','女',18,'CS');

Insert Into
Student
values( '20172901' ,'wb','男',22,'CS');

1、新建用户u1、u2，u3 （connect类型）
2、将对student、course、sc三个表的查询、增删改权限授予u1,同时u1具有继续授予其他用户的权利；

grant select , update , delete 
on table student , course , Sc
to u1 
with grant option

3、以u1身份登录，将对三个表的查询权利授给u2；

grant select
on table student , courst , Sc
to u2


4、以u1身份登录，将对三个表的查询，增加、修改的权利授给u3；

grant select,update,delete
on table student , courst , Sc
to u3

5、回收u1的对三个表的增删改权利

revoke select, update , delete 
on table student , course , Sc
from u1

P71页习题
1、求供应工程J1零件的供应商号码Sno

Select dinstinct Sno from SPJ
where jno = 'J1';

2、求供应工程J1零件P1的供应商号码Sno

Select Sno from SPJ
where Jno = 'J1' and Pno = 'p1';

3、求供应工程J1零件为红色的供应商号码Sno
Select Sno
from P , SPJ
where SPJ.Pno = P.pno AND jno = 'J1' AND color = '红'

4、求没有使用天津供应商生产的红色零件的工程号Jno

Select jno from J
Except
Select jno from SPJ , P , S
where 
        SPJ.pno = P.pno 
AND SPJ.Sno = S.Sno 
AND city = '天津' 
AND color = '红'

5、求至少用了供应商S1所供应的全部零件的工程号Jno
Select distinct jno 
from SPJ A
where not exists(
       Select *
       From SPJ B
       where Sno = 'S1' AND
                  not exists(
                         Select *
                         from SPJ C
                         where C.Jno = A.Jno 
                                    AND C.Sno = 'S1' AND C.pno = B.pno
                  )   
)

---

11 - 1 课堂讲解事例

1、查询没有选修过课的学号

　　

2、查询没有不及格的学生学号

　　

3、查询没有选过课的学生 输出其 学号  、姓名

4、查询选择1号课 或者 2号课的学生学号

　　

5、查询选择1号课 和 2号课的学生学号

　　

6、查询选修了全部课程的学生号码和姓名

　　

---

11月6号 课堂讲解事例

1、求供应工程J1零件的供应商号码Sno；

　　

2、求供应工程J1零件P1的供应商号码Sno；

　　

3、求供应工程J1零件为红色的供应商号码Sno；

　　

4、求没有使用天津供应商生产的红色零件的工程号Jno；

　　

5、求至少用了供应商S1所供应的全部零件的工程号Jno；

　　

或者

 　　

---

 设有关系数据库(S,SC,C),其中

S = ( S# 学号, Sn 姓名, Sa 年龄, Sex 性别)

Sc = ( S# 学号, C# 课号 , Cn 课程名)

C = C( C# 课程号 , Cn 课程名 , Tn　任课教师名字)

(1).查询LI老师所授课课程的课程号和课程名称

　　

(2).查询年龄大于23岁的男学生学号和课程号

　　 

(3).查询WANG学生没选修的课程号

　　

(4).查询至少选修了两门课程的学生学号

 　　

(5).查询全部学生都选修的课程的课程号和课程名称

　　  

---

S(Sno 学号,Sname 学生姓名, age 年龄, sex 性别)

C(Cno 课程号, Cname 课程名, Pcno 先行课号)

T(Tno 教师号, Tname 教师名, dep 专业)

Sc(Sno 学号, Cno 课程号, Tno 教师号, grade 成绩)

(1).求选修所有课程并且成绩为A的学生姓名

　　 

(2).求选修了王平老师教的所有课程的学生名

　　

(3).求不选修信息系老师开设的课程的学生名

 　　

---