看我学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 LOGGING
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "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 studentinfo
  
  commit;
 --建立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;