SQL

a.sql

/* Easy Drop */
DROP TABLE selected_courses;
DROP TABLE students;
DROP TABLE courses;
DROP TABLE teachers;

/* 1: Create table structure */
CREATE TABLE teachers (id INTEGER NOT NULL, name CHAR(8) NOT NULL, title CHAR(10), PRIMARY KEY(id));
CREATE TABLE courses (id INTEGER NOT NULL, name CHAR(10) NOT NULL, teacher_id INTEGER, PRIMARY KEY(id));
CREATE TABLE students (id INTEGER NOT NULL, name CHAR(8) NOT NULL, age SMALLINT, sex CHAR(1), PRIMARY KEY(id));
CREATE TABLE selected_courses (student_id INTEGER, course_id INTEGER, score SMALLINT, PRIMARY KEY(student_id, course_id), FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(course_id) REFERENCES courses(id));



/* 2: Insert data */

/* Student data */
INSERT INTO students VALUES(1, 'LI', 19, 'M');
INSERT INTO students VALUES(2, 'YANG', 19, 'F');
INSERT INTO students VALUES(3, 'WANG', 18, 'F');
INSERT INTO students VALUES(4, 'ZHANG', 20, 'M');
INSERT INTO students VALUES(5, 'SUN', 19, 'F');
INSERT INTO students VALUES(6, 'QIAN', 20, 'M');
INSERT INTO students VALUES(7, 'LIU', 19, 'F');
INSERT INTO students VALUES(8, 'CHEN', 18, 'M');

/* Teacher data */
INSERT INTO teachers VALUES(1, 'WANG', 'JS');
INSERT INTO teachers VALUES(2, 'LIU', 'FJS');
INSERT INTO teachers VALUES(3, 'ZHAO', 'JSHI');
INSERT INTO teachers VALUES(4, 'DING', 'JSHI');

/* Courses data */
INSERT INTO courses VALUES(1, 'MATHS', 2);
INSERT INTO courses VALUES(2, 'ENGLISH', 3);
INSERT INTO courses VALUES(3, 'COMPUTER', 4);
INSERT INTO courses VALUES(4, 'DATABASE', 4);

/* Selected courses data */
INSERT INTO selected_courses VALUES(1, 1, 70);
INSERT INTO selected_courses VALUES(2, 2, 90);
INSERT INTO selected_courses VALUES(1, 3, 75);
INSERT INTO selected_courses VALUES(2, 4, 80);
INSERT INTO selected_courses VALUES(2, 1, 82);
INSERT INTO selected_courses VALUES(1, 2, 80);



/* 3: Alter table */
ALTER TABLE students ADD address VARCHAR(30);
ALTER TABLE students DROP (address);
ALTER TABLE students MODIFY name CHAR(10);



/* 4: Query */
SELECT student_id, score FROM selected_courses WHERE course_id=2;
SELECT id, name FROM students WHERE id IN (SELECT student_id FROM selected_courses WHERE course_id=2);
SELECT students.id, students.name FROM students, selected_courses, courses, teachers WHERE students.id=selected_courses.student_id AND selected_courses.course_id=courses.id AND courses.teacher_id=teachers.id AND teachers.name='LIU';
SELECT student_id FROM selected_courses WHERE course_id=2 OR course_id=4;
SELECT X.student_id FROM selected_courses X, selected_courses Y WHERE X.student_id=Y.student_id AND X.course_id=2 AND Y.course_id=4;
SELECT name, age FROM students WHERE id NOT IN (SELECT student_id FROM selected_courses WHERE course_id=2);
SELECT name FROM students WHERE NOT EXISTS (SELECT * FROM courses WHERE NOT EXISTS (SELECT * FROM selected_courses WHERE selected_courses.student_id=students.id AND selected_courses.course_id=courses.id));
SELECT DISTINCT student_id FROM selected_courses X WHERE NOT EXISTS (SELECT * FROM selected_courses Y WHERE Y.student_id=3 AND NOT EXISTS (SELECT * FROM selected_courses Z where Z.student_id=X.student_id AND Z.course_id=Y.course_id));



/* 5: p112, 3.2 */
/* 3.2.1 */
SELECT id, name FROM students WHERE age<17 AND sex='F';
/* 3.2.2 */
SELECT courses.id, courses.name FROM courses, students, selected_courses WHERE courses.id=selected_courses.course_id AND students.id=selected_courses.student_id AND students.sex='M';
/* 3.2.3 */
SELECT teachers.id, teachers.name FROM teachers, courses, students, selected_courses WHERE courses.teacher_id=teachers.id AND courses.id=selected_courses.course_id AND students.id=selected_courses.student_id AND students.sex='M';
/* 3.2.4 */
SELECT DISTINCT student_id FROM selected_courses GROUP BY student_id HAVING COUNT(*)>=2;
/* 3.2.5 */
INSERT INTO selected_courses VALUES (4, 2, 90);
SELECT X.student_id FROM selected_courses X, selected_courses Y WHERE X.course_id=Y.course_id AND X.student_id=2 AND Y.student_id=4;
/* 3.2.6 */
SELECT courses.id FROM courses WHERE courses.id NOT IN (SELECT DISTINCT courses.id FROM courses, students, selected_courses WHERE selected_courses.course_id=courses.id AND students.name='WANG' AND selected_courses.student_id=students.id);
/* 3.2.7 */
INSERT INTO selected_courses VALUES (3, 2, 90);
INSERT INTO selected_courses VALUES (5, 2, 90);
INSERT INTO selected_courses VALUES (6, 2, 90);
INSERT INTO selected_courses VALUES (7, 2, 90);
INSERT INTO selected_courses VALUES (8, 2, 90);
SELECT * FROM courses WHERE NOT EXISTS (SELECT * FROM students WHERE NOT EXISTS (SELECT * FROM selected_courses WHERE students.id=selected_courses.student_id AND courses.id=selected_courses.course_id));
/* 3.2.8 */
INSERT INTO courses VALUES (5, 'Religion', 2);
INSERT INTO selected_courses VALUES (1, 5, 90);
SELECT DISTINCT students.id FROM students WHERE NOT EXISTS (
	SELECT * FROM courses, teachers WHERE teachers.id=courses.teacher_id AND teachers.name='LIU' AND NOT EXISTS (
		SELECT * FROM selected_courses WHERE selected_courses.course_id=courses.id AND selected_courses.student_id=students.id));




/* 6: p112, 3.7 */
/* 3.7.1 */
SELECT COUNT(DISTINCT selected_courses.course_id) FROM selected_courses;
/* 3.7.2 */
SELECT AVG(students.age) FROM students WHERE students.sex='F' AND EXISTS (SELECT * FROM selected_courses WHERE selected_courses.student_id=students.id AND selected_courses.course_id=4);
/* 3.7.3 */
SELECT courses.name, teachers.name, AVG(selected_courses.score) FROM selected_courses, courses, teachers WHERE selected_courses.course_id=courses.id AND selected_courses.course_id=courses.id AND teachers.id=courses.teacher_id AND teachers.name='LIU' GROUP BY selected_courses.course_id, courses.name, teachers.name;
/* 3.7.4 */
SELECT courses.id, COUNT(selected_courses.course_id) FROM courses, selected_courses WHERE selected_courses.course_id=courses.id GROUP BY courses.id HAVING COUNT(selected_courses.course_id)>10 ORDER BY COUNT(selected_courses.course_id) DESC, courses.id ASC;
/* 3.7.5 */
INSERT INTO students VALUES (1000, 'Scott', 2, 'M');
SElECT students.name FROM students WHERE students.id > (SELECT students.id FROM students WHERE students.name='WANG') AND students.age < (SELECT students.age FROM students WHERE students.name='WANG');
/* 3.7.6 */
INSERT INTO selected_courses VALUES (1000, 1, NULL);
SELECT selected_courses.student_id, selected_courses.course_id FROM selected_courses WHERE selected_courses.score IS NULL;
/* 3.7.7 */
SELECT students.name, students.age FROM students WHERE students.name LIKE 'L%';
/* 3.7.8 */
SELECT students.name, students.age FROM students WHERE students.sex='M' AND students.age > (SELECT AVG(students.age) FROM students WHERE students.sex='F');
/* 3.7.9 */
SELECT students.name, students.age FROM students WHERE students.sex='M' AND students.age > (SELECT MAX(students.age) FROM students WHERE students.sex='F');



/* Extra fun */
ALTER TABLE teachers RENAME TO te;
ALTER TABLE te RENAME TO teachers;
ALTER TABLE teachers RENAME COLUMN title TO zx;
ALTER TABLE teachers RENAME COLUMN zx TO title;
CREATE SEQUENCE teachers_seq START WITH 11 INCREMENT BY 1;
INSERT INTO teachers(teachers.name, teachers.title, teachers.id) VALUES ('A', 'b', teachers_seq.nextval);
INSERT INTO teachers(teachers.name, teachers.title, teachers.id) VALUES ('B', 'c', teachers_seq.nextval);


SELECT courses.teacher_id FROM courses WHERE courses.id IN (SELECT selected_courses.course_id FROM selected_courses WHERE selected_courses.course_id IN (SELECT courses.id FROM courses) GROUP BY selected_courses.course_id HAVING AVG(selected_courses.score) > 80)

实验一（2014.9.28）
一、说明
在Oracle 10g中，用户可以通过Client/Server方式或Browser/Server方式使用数据库。无论使用哪种方式，都要保证Oracle 10g的服务器已经正常启动，否则用户无法连接到数据库服务器后正常工作。
在我们的实验室环境中，Oracle 10g的服务器通常已经作为一个例程启动并运行在后台。可在任务管理器中查看服务oracleServiceORCL是否在运行，若是则表示已启动一个例程；若未运行，则要启动它。

二、通过登录，进入操作环境
有两种途径可登录Oracle 服务器。

1. SQL plus的图形窗口登录
   直接点击桌面上的SQL Plus图标 ，进入SQL plus的登录窗口
   在其中填入oracle系统的用户名和密码即可。一般操作用普通账号scott/tiger（系统安装时的一个默认账号）登录即可。
2. 通过命令行登录

1） 打开windows系统的”命令行控制台”，具体方法为：（“开始—>所有程序—>附件—>命令提示符”）
2） 输入sqlplus scott/tiger
3） 出现“已连接”
至此表明已正常连接。

考虑scott/tiger可能被锁定，而特权用户sys在SQL plus的图形窗口登录不方便，可在SQL plus的图形窗口先用System/oracle账号登录，然后改用sys/oracle连接。但是要注意特殊命令格式SQL>conn sys/oracle as sysdba

如果出现“ORA-12560: TNS: 协议适配器错误”，请到是否启动。如果没有启动，启动它。

如果提示帐户被锁定。可使用以下方法来解锁：
1） 再打开一个“命令行控制台”口，用sys帐户登录sql*plus，命令为：

sqlplus / as sysdba
2) 在SQL>后面输入以下语句对账户scott解锁：
alter user scott account unlock;

输入以下语句对账户scott修改密码
alter user scott identified by tiger;

 解锁之后，再用scott登录。

三、第一次试验内容

1. 参照教材P78 例3.1创建教师表、课程表、学生表、选课表。
2. 参照教材P98例3.25，并基于自己设计的实例数据，向上述四张表插入数据。

学生表
‘S1’,’LI’,19,’M’
‘S2’,’YANG’,19,’F’
‘S3’,’WANG’,18,’F’
‘S4’,’ZHANG’,20,’M’
'S5','SUN',19,'F'
'S6','QIAN',20,'M'
'S7,'LIU',19,'F'
'S8,'CHEN',18,'M'

教师表
‘T1’,’WANG’,’JS’
‘T2’,’LIU’,’FJS’
‘T3’,’ ZHAO’,’JSHI’
‘T4’,’ DING’,’JSHI’

课程表
‘C1’,’MATHS’,’T2’
‘C2’,’ENGLISH’,’T3’
‘C3’,’COMPUTER’,’T4’
‘C4’,’DATABASE’,’T4’

选课表
S1 C1 70
S2 C2 90
S1 C3 75
S2 C4 80
S2 C1 82
S1 C2 80

注意插入数据的顺序，从而体会完整性约束的意义和效果。

1. 完成教材P80表结构的修改。
2. 完成教材中3.3节的所有数据查询例子。
3. 完成教材P112的习题3.2，习题3.7

使用技巧：

1. 将命令保存在一个.sql文件中，然后使用start c:\a.sql 将文件内容调入缓冲区运行，用run命令运行缓冲区的命令；
   可在命令行状态下使用edit命令启动编辑一个文本文件的窗口（edit c:\a.sql）。
2. 将屏幕显示输出到文件的方法
   （1） 可在命令行状态下使用spool命令建立一个假脱机文件，如SPOOL c:\aa，就会在C盘根目录下建立一个名为aa.LST的假脱机文件。
   （2） 之后，在屏幕上的所有输出都将同时输出到文件中。
   （3） 欲使其真正输出到文件中，必须执行命令 SPOOL OUT。
   使用EDIT打开文件，查看文件中的内容。
3. 将缓冲区的内容输出到文件的方法
   SAVE c:\ aa1.sql
   该命令用缓冲区中的内容替换文件中的内容，如果要追加到文件中，则使用
   SAVE c:\ aa1.sql append格式。