Oracle高级0.docVIP

Oracle高级0 students表结构 CREATE TABLE students ( student_id NUMBER(5) CONSTRAINT student_pk PRIMARY KEY, monitor_id NUMBER(5), name VARCHAR2(10) NOT NULL, sex VARCHAR2(6) CONSTRAINT sex_chk CHECK(sex IN (男,女)), dob DATE, specialty VARCHAR2(10) ); departments表结构 CREATE TABLE departments( department_id NUMBER(3) CONSTRAINT department_pk PRIMARY KEY, department_name VARCHAR2(8) NOT NULL, address VARCHAR2(40) ); teachers表结构 CREATE TABLE teachers ( teacher_id NUMBER(5) CONSTRAINT teacher_pk PRIMARY 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 ) ); courses表结构 CREATE TABLE courses( course_id NUMBER(5) CONSTRAINT course_pk PRIMARY KEY, course_name VARCHAR2(30) NOT NULL, credit_hour NUMBER(2) ); students_grade表结构 CREATE TABLE students_grade( student_id NUMBER(5) CONSTRAINT students_grade_fk_students REFERENCES students(student_id), course_id NUMBER(5) CONSTRAINT students_grade_fk_courses REFERENCES courses(course_id), score NUMBER(4,1) ); grades表结构 CREATE TABLE grades ( grade_id NUMBER(1) CONSTRAINT grade_pk PRIMARY KEY, low_score NUMBER(4, 1), high_score NUMBER(4, 1), grade VARCHAR2(6) ); 学生（姓名，生日）学生生日清单（字符串连接） SELECT name||生日是： ||dob AS 学生生日清单 FROM students; 利用函数NVL2处理NULL SELECT name AS 姓名, NVL2(bonus,bonus+wage,wage) AS 月总收入 FROM teachers; 利用函数COALESCE()处理NULL SELECT name AS 姓名, COALESCE(bonus+wage,wage) AS 月总收入 FROM teachers; 字符型 SELECT student_id, name, specialty, dob FROM students ORDER BY 2 DESC; 5.1 子查询 5.1.1 单行子查询 1．在WHERE 子句中使用子查询 例5.1_1 SELECT * FROM Teachers WHERE wage (SELECT AVG(wage) FROM Teachers); //查询部门工资高于最低部门平均工资的部门和平均工资 2．在HAVING子句中使用子查询 SELECT department_id, AVG(wage) AS 平均工资 FROM Teachers GROUP BY department_id HAVING AVG(wage) (SELECT MIN(AVG(wage))