oracle查询练习1

--学生信息表(学号、姓名、年龄、性别、主键[学号])
create table student(
sid int primary key,
sname varchar2(10),
sage int,
ssex varchar2(2));
--学生信息
insert into student values(01,'亚楠',18,'女');
insert into student values(02,'丽丽',22,'女');
insert into student values(14,'俊星',20,'男');
insert into student values(40,'没考好',26,'男');
insert into student values(50,'还没考',20,'男');
insert into student values(60,'学一门',32,'男');
insert into student values(70,'亚楠',30,'男');

--老师信息表(老师号、姓名、主键[老师号])
create table teacher(
tid int primary key,
tname varchar2(10));
--老师信息
insert into teacher values(1,'赵某');
insert into teacher values(2,'鲁某');
--课程信息表(课程号、课程名称、老师号、主键[课程号])
create table course(
cid int primary key,
cname varchar2(10),
tid int);
--课程信息
insert into course(cid,cname,tid) values(001,'TE DC',1);
insert into course(cid,cname,tid) values(002,'TP TC',2);
insert into course(cid,cname,tid) values(003,'LINUX WBT',1);
insert into course(cid,cname,tid) values(004,'DBAATT',2);

--学生成绩信息表(学号、课程号、成绩、主键[学号、课程号])
create table sc(
sid int,
cid int,
score float,
primary key(sid,cid));
--学生成绩信息
insert into sc values(01,1,72);
insert into sc values(01,2,74.5);
insert into sc values(01,3,84);
insert into sc values(02,1,63);
insert into sc values(02,2,57);
insert into sc values(02,3,61);
insert into sc values(14,1,56);
insert into sc values(14,2,67.5);
insert into sc values(14,3,69);
insert into sc values(40,1,45);
insert into sc values(40,2,55);
insert into sc values(40,3,35);
insert into sc values(60,4,35);
insert into sc values(70,1,80);
insert into sc values(70,2,35);

select * from student;--学生信息表
select * from sc;--学生成绩信息表
select * from teacher;--老师信息表
select * from course;--课程信息表

提醒：以下练习中的数据是根据初始化到数据库中的数据来写的sql语句。
–1.查询年龄为20的人的学生信息

select * from student where sage=20; 

–2.查询男生的信息

select * from student where ssex='男';

–3.查询老师号为1的老师姓名

select * from teacher where tid=1;

–4.查询成绩大于80分的学生id

select sid from sc where score>80;

–5.查询成绩小于60分的学生id

select sid from sc where score<60;

–6.查询成绩在70-80的学生id

select sid from sc where score>=70 and score<=80;

select sid from sc where score between 70 and 80;

–7.查询成绩是67、68、69的学生id

select sid from sc where score=67 or score=68 or score=69;

–8.查询课程号为1并且成绩大于60分的学生id

select sid from sc where cid=1 and score>60;

–9.查询学过课程号为1或2的学生id

select sid from sc where cid=1 or cid=2;

–10.查询姓亚的学生信息

select * from student where sname like '亚%';

–11.查询名字中含没的学生信息

select * from student where sname like '%没%';

–12.查询名字中第二个字是没的学生信息

select * from student where sname like '_没%';

–13.更新学生号为50的学生的年龄为25

select * from student where sid=50;

update student set sage=25 where sid=50; 

–14.查询丽丽同学1号课程的成绩

select sid from student where sname='丽丽';

select score from sc where cid=1 and sid=2;

–15.查询成绩大于80分的学生姓名

select sname from student;
select score from sc where score>80;

select * from student where sid = (select sid from sc where score > 80);

–16.查询老师赵某所教的课程名

select * from teacher;
select * from course; 
select * from course where tid=(select tid from teacher where tname='赵某');