创建表student,并插入数据
drop table if EXISTS student;
CREATE table student (
Sno int PRIMARY KEY,
Sname varchar(10),
Ssex varchar(5),
Sage TINYINT,
Sdept varchar(20)
);
insert into Student values
('9512101','李勇','男',19,'计算机系'),
('9512102','刘晨','男',20,'计算机系'),
('9512103','王敏','女',20,'计算机系'),
('9521101','张立','男',22,'信息系'),
('9521102','吴宾','女','21','信息系'),
('9521103','张海','男',20,'信息系'),
('9531101','钱小平','女',18,'数学系'),
('9531102','王大力','男',19,'数学系');
创建表course,并插入数据
drop table if EXISTS course ;
create table course(
Cno varchar(10) PRIMARY key ,
Cname varchar(10),
Ccredit TINYINT,
Cemester TINYINT
);
insert into course values
('C01', '计算机文化学', 3 , 1),
('C02', 'VB' , 2 , 3),
('C03', '计算机网络', 4 , 7),
('C04', '数据库基础 ', 6 , 6),
('C05', '高等数学' , 8, 2),
('C06', '数据结构,', 5, 4);
创建表sc,并插入数据
drop table if EXISTS sc ;
create table sc(
Sno int ,
Cno varchar(10),
Grade int ,
XKLB varchar(10)
);
insert into SC values
('9512101','c01',90,'必修'),
('9512101','c02',86,'选修'),
('9512101','c06',NULL,'必修'),
('9512102','c02',78,'选修'),
('9512102','c04',66,'必修'),
('9521102','c01',82,'选修'),
('9521102','c02',75,'选修'),
('9521102','c04',92,'必修'),
('9521102','c05',50,'必修'),
('9521103','c02',68,'选修'),
('9521103','c06',NULL,'必修'),
('9531101','c01',80,'选修'),
('9531101','c05',95,'必修'),
('9531102','c05',85,'必修');
题1:查询全体学生的学号与姓名。
select sno,sname from student ;
题2:查询全体学生的姓名,学号和所在系。
select sno,sname, Sdept from student ;
题3:查询全体学生的记录。
select * from SC join Student on Student.Sno=SC.Sno;
题4:查询全体学生的姓名及其出生年份。
select sname,2024-Sage as 出生年份 from student;
题5:在选课表(SC)中查询有哪些学生选修了课程,并列出学生的学号。
select distinct sno from sc ;
题6:查询计算机系全体学生的姓名。
select sname from student where Sdept ='计算机系' ;