看我学sql 基础（一） select 语句

看我学sql 基础（一） select 语句

建表插入语句

--建立StudentInfo表结构CREATE TABLE "SCOTT"."STUDENTINFO" (	"SNO" VARCHAR2(12 BYTE) NOT NULL ENABLE, "SNAME" VARCHAR2(20 BYTE), "SEX" VARCHAR2(4 BYTE), "BIRTHDAY" DATE, "DEPART" VARCHAR2(30 BYTE), "MAJOR" VARCHAR2(50 BYTE), "LENGSCH" NUMBER, CONSTRAINT "STUDENTINFO_PK" PRIMARY KEY ("SNO")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGINGTABLESPACE "USERS"  ENABLE) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGTABLESPACE "USERS" ;--插入示例数据insert into scott.studentinfo(sno,sname, sex, birthday, depart, major, lengsch) values('201001903051','辛月娟','女',TO_DATE('1990-4-27','yyyy-mm-dd'),'经济管理系','工商管理(本科)',4);insert into scott.studentinfo(sno,sname, sex, birthday, depart, major, lengsch) values('201001903057','谭文娟','女',TO_DATE('1990-11-12','yyyy-mm-dd'),'计算机系','计算机科学与技术（本科）',4);insert into scott.studentinfo(sno,sname, sex, birthday, depart, major, lengsch) values('201001903029','明梅','女',TO_DATE('1990-4-27','yyyy-mm-dd'),'计算机系','计算机科学与技术（本科）',4);insert into scott.studentinfo(sno,sname, sex, birthday, depart, major, lengsch) values('201011002031','徐雁','男',TO_DATE('1990-4-27','yyyy-mm-dd'),'计算机系','信息管理与信息系统(1＋3)',4);insert into scott.studentinfo(sno,sname, sex, birthday, depart, major, lengsch) values('201011002032','秦赵璇','女',TO_DATE('1990-4-27','yyyy-mm-dd'),'计算机系','信息管理与信息系统(1＋3)',4);insert into scott.studentinfo(sno,sname, sex, birthday, depart, major, lengsch) values('201011801017','王鹏飞','男',TO_DATE('1990-4-27','yyyy-mm-dd'),'服装系','服装设计与工程(专科)',3);insert into scott.studentinfo(sno,sname, sex, birthday, depart, major, lengsch) values('201011801018','哈菲菲','女',TO_DATE('1990-4-27','yyyy-mm-dd'),'服装系','服装设计与工程(1＋3)',4);select * from studentinfocommit;--建立CourseInfo表create table scott.CourseInfo(Cno char(6),Cname varchar(50),Cpno char(6),Credit decimal(2,1),CONSTRAINT Courseinfo_PK primary key(Cno));-- --插入示例数据insert into scott.CourseInfo(cno,cname,cpno,credit) values('140173','数据库系统概论','140176',3);insert into scott.CourseInfo(cno,cname,cpno,credit) values('140174','C语言程序设计','',2);insert into scott.CourseInfo(cno,cname,cpno,credit) values('140175','操作系统','140176',3);insert into scott.CourseInfo(cno,cname,cpno,credit) values('140176','数据结构','140174',3.5);insert into scott.CourseInfo(cno,cname,cpno,credit) values('140178','计算机英语','',2);insert into scott.CourseInfo(cno,cname,cpno,credit) values('140179','软件工程','140173',2);insert into scott.CourseInfo(cno,cname,cpno,credit) values('140180','计算机组成原理','',4);--建立TeacherInfo表create table scott.TeacherInfo(TeacherID char(5),Tname varchar(20),Sex char(4),Depart varchar(30),ProfTitle varchar(10),Degree char(6),constraint TeacherInfo_PK primary key(TeacherID));-- --插入示例数据insert into teacherinfo(teacherid, tname, sex, depart, proftitle, degree) values('04001','白红霞','女','计算机系','教授','博士');insert into teacherinfo(teacherid, tname, sex, depart, proftitle, degree) values('09001','安宁','女','计算机系','讲师','本科');insert into teacherinfo(teacherid, tname, sex, depart, proftitle, degree) values('09004','董敏','女','化学系','教授','博士');insert into teacherinfo(teacherid, tname, sex, depart, proftitle, degree) values('09006','冯李宁','男','计算机系','副教授','本科');insert into teacherinfo(teacherid, tname, sex, depart, proftitle, degree) values('06067','范美丽','女','数学系','讲师','硕士');insert into teacherinfo(teacherid, tname, sex, depart, proftitle, degree) values('09011','华玉山','男','计算机系','助教','硕士');insert into teacherinfo(teacherid, tname, sex, depart, proftitle, degree) values('09016','王洪亮','男','计算机系','副教授','本科');--建立TeacherInfo表create table scott.TeachTasksInfo(TeachTaskID char(26),Cno char(6),TeacherID char(5),AcadeYear char(9),Term char(1),ExamMethod varchar(10),Depart varchar(30),TimeTable varchar(50),constraint TeachTasksInfo_PK primary key(TeachTaskID));-- --插入示例数据insert into scott.TeachTasksInfo(TeachTaskID, Cno, TeacherID, AcadeYear,Term,ExamMethod,Depart,TimeTable) values('(2009-2010-1)-140173-04001','140173','04001','2009-2010','1','考试','计算机系','周二第3,4节{第1-20周}');insert into scott.TeachTasksInfo(TeachTaskID, Cno, TeacherID, AcadeYear,Term,ExamMethod,Depart,TimeTable) values('(2009-2010-2)-140176-09006','140176','09006','2009-2010','2','考查','计算机系','周一第1,2节{第1-20周}');insert into scott.TeachTasksInfo(TeachTaskID, Cno, TeacherID, AcadeYear,Term,ExamMethod,Depart,TimeTable) values('(2009-2010-2)-140178-06067','140178','06067','2009-2010','2','考试','数学系','周三第1,2节{第1-20周}');insert into scott.TeachTasksInfo(TeachTaskID, Cno, TeacherID, AcadeYear,Term,ExamMethod,Depart,TimeTable) values('(2009-2010-1)-140180-09016','140180','09016','2009-2010','1','考查','计算机系','周二第3,4节{第1-20周}');--建立SC表create table SC(Sno char(12),TeachTaskID char(26),Grade decimal(5,1),constraint SC_PK primary key(Sno,TeachTaskID));insert into scott.SC(teachtaskid,sno, grade) values('(2009-2010-1)-140173-04001','201001903057','84.2');insert into scott.SC(teachtaskid,sno, grade) values('(2009-2010-1)-140173-04001','201001903029','87.7');insert into scott.SC(teachtaskid,sno, grade) values('(2009-2010-1)-140173-04001','201011002031','84.2');insert into scott.SC(teachtaskid,sno, grade) values('(2009-2010-2)-140176-09006','201001903051','84.2');insert into scott.SC(teachtaskid,sno, grade) values('(2009-2010-2)-140176-09006','201001903057','84.2');insert into scott.SC(teachtaskid,sno, grade) values('(2009-2010-2)-140176-09006','201001903029','84.2');insert into scott.SC(teachtaskid,sno, grade) values('(2009-2010-2)-140178-06067','201011002031','84.2');insert into scott.SC(teachtaskid,sno, grade) values('(2009-2010-2)-140178-06067','201001903057','84.2');-- select * from courseinfo

1．查询StudentInfo表中所有的学生的详细信息，SQL代码如下所示：
select * from STUDENTINFO;
2．查询StudentInfo表中所有学生的学号、姓名、系别、专业等信息，SQL代码如下所示：
select sname,sex,depart,major from STUDENTINFO;
3．查询全体学生的学号、姓名及年龄信息，SQL代码如下所示：
select sno,sname,(TO_DATE('2020-02-28','yyyy-mm-dd')-BIRTHDAY)/365 from studentinfo;
4．查询全体学生的学号、姓名及年龄信息，并且为年龄列定义别名为“Age”。SQL代码如下所示：
select sno,sname,(TO_DATE('2020-02-28','yyyy-mm-dd')-BIRTHDAY)/365 as Age from studentinfo;
5．查询有学生选修的教学计划的教学计划号。SQL代码如下所示：
select TeachTaskID FROM sc ;
6．查询有学生选修的教学计划的教学计划号，并去掉重复行。SQL代码如下所示：
select DISTINCT TeachTaskID FROM sc ;
7．查询所有学生的基本信息，结果按学号降序排列。SQL代码如下所示：
select * from studentinfo order by sno desc;
8．查询所有学生的基本信息，结果按性别降序排列NULL值在末位。SQL代码如下所示：
select * from studentinfo order by sex desc nulls last;
9．查询所有学生的基本信息，结果按姓名排序，姓名相同者按学号进行排序。SQL代码如下所示：
select * from studentinfo order by sname,sno;
10．查询StudentInfo表中学号为201001903029的学生的详细信息，SQL代码如下所示：
select * from studentinfo where sno = '201801002015' ;
11．查询出生年月在1991-03-15日以后的学生的详细信息，SQL代码如下所示：
select * from studentinfo where birthday > TO_DATE('1991-03-15','yyyy-mm-dd');
12．查询成绩在70-80之间的学生的学号及教务任务号等信息，SQL代码如下所示：
select sno,teachtaskid from sc where grade<80 and grade>70;
13．查询不是计算机系和服装系学生的姓名、性别和专业，SQL代码如下所示：
select sname,sex,major from studentinfo where DEPART not in ('计算机系','服装系');
14．查询2009-2010学年第1学期学生选课的学生号、课程号及成绩，SQL代码如下所示：
select * from sc where TEACHTASKID like '(2009-2010-1)%';
15．查询名字最后一个字为“娟”，且名字为三个字的学生详细信息，SQL代码如下所示：
select * from studentinfo where sname like '__娟';
16．查询 CourseInfo表中没有先行课的课程号与课程名称，SQL代码如下所示：
select CNO,CNAME from courseinfo where cpno is null;
17．查询在计算机系的女学生的详细信息。SQL代码如下所示：
select * from studentinfo where sex = '女' and depart = '计算机系';
18．查询是教授或者是博士的教师详细信息。SQL代码如下所示：
select * from teacherinfo where PROFTITLE = '教授' or DEGREE = '本科';
19．查询在2009-2010年第2学期，选修140176课程的学生学号及成绩，查询结果按成绩降序排列。SQL代码如下所示：
select sno,grade from sc where  TEACHTASKID like '(2009-2010-2)-140176%' order by grade desc;
20．查询计算机系学生的总人数，SQL代码如下所示：
select count(*) from studentinfo where depart = '计算机系';
21．查询在2009-2010年第2学期，选修140176课程中的最高分，SQL代码如下所示：
select max(grade) from sc where  TEACHTASKID like '(2009-2010-2)-140176%';
22．查询各教学计划号及相应的选课人数，SQL代码如下所示
select teachtaskid,count(teachtaskid) as num from sc group by teachtaskid;
23．查询在2009-2010年第2学期选修了2门以上课程的学生学号，SQL代码如下所示：
select sno from sc where teachtaskid like '(2009-2010-2)%' group by sno having count(*)>2;