SQL实训示例.docx

1、SQL实训示例10月24日查询当前系统时间select sysdate from dual;PL/SQL序模板set serveroutput on begin dbms_output.put_line(这是第一个PL/SQL程序); end; /创建课本中的范例数据库，students表,teachers表,departments表,courses表,students_grade表,grades表；新建的用户并赋予其权限Conn system/oracle;create user wanghui identified by wanghui;grant create session, crea

2、te user, create view,create table to wanghui;为用户指定表空间alter user wanghui default tablespace users quota 20m on users;conn wanghui/wanghui;创建studentscreate table students (student_id number(5) constraint student_pk primary key, monitor_id number(5), name varchar2(10) not null, sex varchar2(6) constrai

3、nt sex_chk check(sex in(男,女), dob date, specialty varchar2(10) );创建departmentscreate table departments (department_id number(3)constraint department_pk primary key,department_name varchar2(8) not null,address varchar2(40);创建teacherscreate table teachers(teacher_id number(5)constraint teacher_pk prim

4、ary key,name varchar2(8) not null,title varchar2(6),hire_date date default sysdate,bonus number(7,2),wage number(7,2),department_id number(3)constraint teachers_fk_departments references departments(department_id);创建coursescreate table courses(course_id number(5)constraint course_pk primary key,cour

5、se_name varchar2(30) not null,credit_hour number(2);创建students_gradecreate table students_grade(student_id number(5)constraint students_grade_fk_studentsreferences students(student_id),course_id number(5)constraint students_grade_fk_coursesreferences courses(course_id),score number(4,1);创建gradescrea

6、te table grades( grade_id number(1) constraint grade_pk primary key, low_score number(4,1), high_score number(4,1), grade varchar2(6) );查看表结构Desc students;添加students表数据insert into studentsvalues(10101,null,王晓芳,女,07-5月-1988,计算机);insert into studentsvalues(10205,null,李秋枫,男,25-11月-1990,自动化);insert into

7、 studentsvalues(10102,10101,刘春苹,女,12-8月-1991,计算机);insert into studentsvalues(10301,null,高山,男,08-10月-1990,机电工程);insert into studentsvalues(10207,10205,王刚,男,03-4月-1987,自动化);insert into studentsvalues(10112,10101,张纯玉,男,21-7月-1989,计算机);insert into studentsvalues(10318,10301,张冬云,女,26-12月-1989,机电工程);inser

8、t into studentsvalues(10103,10101,王天仪,男,26-12月-1989,计算机);insert into studentsvalues(10201,10205,赵风雨,男,25-10月-1990,自动化);insert into studentsvalues(10105,10101,韩刘,男,3-8月-1991,计算机);insert into studentsvalues(10311,10301,张扬,男,08-5月-1990,机电工程);insert into studentsvalues(10213,10205,高淼,男,11-3月-1987,自动化);i

9、nsert into studentsvalues(10212,10205,欧阳春岚,女,12-3月-1989,自动化);insert into studentsvalues(10314,10301,赵迪帆,男,22-9月-1989,机电工程);insert into studentsvalues(10312,10301,白菲菲,女,07-5月-1988,机电工程);insert into studentsvalues(10328,10301,曾程程,男,null,机电工程);insert into studentsvalues(10128,10101,白昕,男,null,计算机);inser

10、t into studentsvalues(10228,10205,林紫寒,女,null,自动化);添加departments表数据insert into departmentsvalues(101, 信息工程,1号教学楼);insert into departmentsvalues(102, 电气工程,2号教学楼);insert into departmentsvalues(103, 机电工程,3号教学楼);添加teachers表数据insert into teachersvalues(10101, 王彤,教授,01-9月-1990,1000,3000,101);insert into te

11、achersvalues(10104,孔世杰,副教授,06-7月-1994,800,2700,101);insert into teachersvalues(10103,邹人文,讲师,21-1月-1996,600,2400,101);insert into teachersvalues(10106,韩冬梅,助教,01-8月-2002,500,1800,101);insert into teachersvalues(10210,杨文化,教授,03-10月-1989,1000,3100,102);insert into teachersvalues(10206,崔天,助教,05-9月-2000,5

12、00,1900,102);insert into teachersvalues(10209,孙晴碧,讲师,11-5月-1998,600,2500,102);insert into teachersvalues(10207,张珂,讲师,16-8月-1997,700,2700,102);insert into teachersvalues(10308,齐沈阳,高工,03-10月-1989,1000,3100,103);insert into teachersvalues(10306,车东日,助教,05-9月-2001,500,1900,103);insert into teachersvalues

13、(10309,臧海涛,工程师,29-6月-1999,600,2400,103);insert into teachersvalues(10307,赵昆,讲师,18-2月-1996,800,2700,103);insert into teachersvalues(10128,王晓,null,05-9月-2007,null,1000,101);insert into teachersvalues(10328,张笑,null,29-9月-2007,null,1000,103);insert into teachersvalues(10228,赵天宇,null,18-9月-2007,null,1000

14、,102);添加courses表数据insert into coursesvalues(10101, 计算机组成原理,4);insert into coursesvalues(10201, 自动控制原理,4);insert into coursesvalues(10301, 工程制图,3);insert into coursesvalues(10102, C+语言程序设计,3);insert into coursesvalues(10202, 模拟电子技术,4);insert into coursesvalues(10302, 理论力学,3);insert into coursesvalues

15、(10103, 离散数学,3);insert into coursesvalues(10203, 数字电子技术,4);insert into coursesvalues(10303, 材料力学,3);添加students_grade表数据insert into students_gradevalues(10101,10101,87);insert into students_gradevalues(10101,10201,100);insert into students_gradevalues(10101,10301,79);添加grades表数据insert into gradesvalu

16、es(1,0,59, 不及格);insert into gradesvalues(2,60,69, 及格);insert into gradesvalues(3,70,79, 中等);insert into gradesvalues(4,80,89, 良好);insert into gradesvalues(5,90,100, 优秀);查看数据Select * from students;Select * from teachers;Select * from departments;Select * from courses;Select * from students_grade;Sele

17、ct * from grades;单表查询select name,title,wage,hire_date from teachers;select name,title,wage, to_char(hire_date,YYYY-MM-DD) FROM teachers;select * from students;select student_id,name,sex,specialty,dob from students;select specialty from students;select distinct specialty from students;改变输出select name

18、 as 姓名,dob as 生日 from students;select name| 生日是 |dob as 学生生日清单 from students;select name as 姓名,bonus+wage as 月总收入 from teachers;空值处理select name as 姓名,nvl(bonus,0)+wage as 月总收入 from teachers;select name as 姓名,nvl2(bonus,bonus+wage,wage) as 月总收入 from teachers;select name as 姓名,coalesce(bonus+wage,wage

19、) as 月总收入 from teachers;条件查询Select name ,hire_date,title,wage From teachers where wage=2000;Select student_id,name,specialty,dobFrom students where specialty=计算机;Select student_id,name,specialty,dobFrom students where dob2200;子查询Select * from teachers Where wage(select avg(wage) from teachers);Selec

20、t course_id,course_name from coursesWhere course_id not in (select course_idFrom students_grade);Select * from teachersWhere wageall(select avg(wage) from teachers group by department_id);Select * from teachersWhere (department_id,wage) in(select department_id,min(wage)from teachers group by departm

21、ent_id);Select * from teachers t1where wage(select avg(wage) from teachers t2where t2.department_id=t1.department_id);select course_id,course_name from courses cwhere exists( select 2 from students_grade sgwhere sg.course_id=c.course_id);select course_id,course_name from courses cwhere not exists( s

22、elect 2 from students_grade sgwhere sg.course_id=c.course_id);select department_id,department_name from departmentswhere department_id in(select department_id from teachers);建立辅修课程表MINORScreate table minors(minor_id number(5)constraint minor_pk primary key,minor_name varchar2(30) not null,credit_hou

23、r number(2);为辅修课程表插入数据Insert into minors values(10101,计算机组成原理,4);Insert into minors values(10201,自动控制原理,4);Insert into minors values(10301,工程制图,3);建立课程courses2表create table courses2(course_id number(5)constraint course2_pk primary key,course_name varchar2(30) not null,credit_hour number(2);为课程course

24、s2表插入数据Insert into courses2 values(10201,自动控制原理,4);Insert into courses2 values(10301,工程制图,3);使用集合操作符Select course_id, course_name, credit_hourFrom coursesUnion allSelect minor_id,minor_name, credit_hourFrom minors;Select course_id, course_name, credit_hourFrom coursesUnion Select minor_id,minor_name

25、, credit_hourFrom minors;Select course_id, course_name, credit_hourFrom coursesintersectSelect minor_id,minor_name, credit_hourFrom minors ;Select course_id, course_name, credit_hourFrom coursesminusSelect minor_id,minor_name, credit_hourFrom minors;Select course_id, course_name, credit_hourFrom coursesunionSelect minor_id,minor_name, credit_hourFrom minors order by course_name;Select course_id, course_nam