实验 1
实验类别：验证性
ORACLE 数据库 SQL 语句练习实验
实验级别：必做 实验时数：6 学时
开课单位：计算机与软件学院软件工程系 一、实验目的： 1、 了解 DBMS 系统的功能、软件组成； 2、掌握利用 SQL 语句定义、操纵数据库的方法。 二、实验要求：
1、在课外安装相关软件并浏览软件自带的帮助文件和功能菜单，了解 DBMS 的功能、结 构； 2、创建一个有两个关系表的数据库；（建议采用 ORACLE ISQLPLUS） 3、数据库、关系表定义； 4、学习定义关系表的约束(主键、外键、自定义)； 5、了解 SQL 的数据定义功能；了解 SQL 的操纵功能； 6、 掌握典型的 SQL 语句的功能； 7、 了解视图的概念； 三、实验设备： 计算机、数据库管理系统如 DB2，Oracle 等软件。 四、建议的实验步骤： 0、关闭 drcom 软件，或深大登陆客户机。登陆 oracle 见附件 1 1、使用 SQL 语句建立关系数据库模式及数据如下；（注：若为日期，因为该 oracle 服务器 采用中文内核，所以日期必须写成 17-12 月-90） EMP: EMPNO 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 3258 4422
点。提交时，题目及答案分开按照要求上交至 5. 问题分析 6. 实验心得 7. 诚信承诺
2
3
附件一、Oracle ISQLPLUS 登录地址
1）登录 http://192.168.109.233/isqlplus 输入用户名： scott 密码：tiger SID： CIE 在出现的窗体内输入 SQL语句。并按“执行”。
TURNER ADAMS JAMES FORD
15675 28-SEP-97 24000 01-MAY27500 90 19500 09-JUN82500 88 18500 19-APR87 11900 17-NOV83 12500 21500 08-SEP-92
02-APR89
MILLER STEVENS GREEN
ENAME JOB MGR HIREDATE SAL COMM 6400 4250 3500 DEPTNO 30 30 20 30 30 10 20 6250 10 30 20 30 20 2750 10 50 50 20
SMITH
ALLEN JONES WARD
SALESMAN SALESMAN SALESMAN MANAGER MANAGER PRESIDENT SALESMAN CLERK CLERK ANALYST MANAGER
CLERK
7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782 4422 7839 1
17-Dec-90
20-FEB-89 19000 22-FEB-93 18500 26850
13750
MARTIN CLARK KING BLAKE SCOTT
20 30 40 50 RESEARCH SALES OPERATIONS MARKETING
LOC
LONDON
PRESTON LIVERPOOL STAFFORD
LUTON
2、用 SQL 定义数据库的关系表； 注：（每位同学在各自创建的图表名字后面添加自己学号末三位以示区分，如 EMP112 等） 3、定义各个关系的字段和自定义的数据完整性约束； 4、确定关系表的主键、外键； 5、对照帮助文件和教材理解主键和外键的约束规则； 6、分别为关系表添加记录； 7、保存数据库以便进行后续实验。（运用 commit 命令） 8、理解 SQL 语句和关系运算的关系； 9、练习典型的 SQL 语句，对第 6 步实验中已建立的表做查询、插入、更新、删除等操 作； 10、检查同学作业的思考题是否存在问题。 注：以上具体步骤可参见帮助文件 SQL handbook 或相关书籍。
创建了数据库以及更改数据之后，请输入 COMMIT；命令保存数据。
创建数据库语句： create table dept( DEPTNO DNAME LOC NUMBER(2) primary key, VARCHAR2(14) not null, VARCHAR2(13) not null);
插入： insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','LONDON');
五、 实验 1 实验报告要求格式（按照实验报告模板完成）： 1、实验目的： 2、实验要求： 3、实验设备和实验环境： 4、实验内容： 4.1 完成练习题：附件 2 的 SQL Handbook 中 EX1--EX6，每个练习各完整完成其中两题。 完整完成题目必须包括：题目，sql 语句，运行出来的结果。 4.2. 自拟 6 道 SQL 题目，题目要求中英文，6 题中一定要涵盖所有 SQL ppt 提及的知识
This book has been designed to help you learn SQL as it has to be learnt by doing, not by teaching. It is therefore in your best interest to work your way through it (10-15 hours work) systematically. You may be asked to submit some of the answers to the exercises, for an assignment and may be asked specific details in an exam. SQL (Structured Query Language) is a relational database language. Amongst other things
删除：DELETE FROM Dept WHERE deptno = '30';
更改:UPDATE Dept SET LOC = ‘SHENZHEN’ WHERE deptno=20；
4
3 4 5-6 6 7 8 9 10 11-16 17-18 19 20 21 22 23-7 28-30 31 32-4 35 36-7 38-9 40 41-4 45 46-8 49 50-1 52 53 54 55-8 59 60-1 62-71 72-3 74-5 76-7 78 Introduction - PLEASE READ Logging in and out Editing SQL statements SAVE commands Running files. The SPOOL command SELECT data Creating the tables you need The Data for the examples The Basic SELECT statement Exercise 1 JOINing tables Joining a table to itself Outer Joins Exercise 2 SQL functions Group Functions Exercise 3 Date Functions Exercise 4 GROUP BY The HAVING clause Exercise 5 Subqueries Exercise 6 Adding, Updating and Deleting data The COMMIT statement CREATE tables Data types ALTER/MODIFY DROP tables VIEWS Exercise 7 Set Operators Output Formatting Procedures /Parameters SQL Dictionary SQL*PLUS dictionary Logical Operators
ANALYST SALESMAN MANAGER CLERK
13250 23-MAY18500 96 24750 14-Jan03-DEC-95 94 03-DEC-91 23-JAN95 24-Jul-95
6548
BARNES
CLERK
4422
16-Jan95
11950
50
DEPT: DNAME DEPTNO ACCOUNTING 10
the language consists of statements to insert, update, query and protect data. Although SQL is not a DBMS, for simplicity in this manual SQL will be considered as a DBMS as well as a language. Of course, in the places where it is necessary, a distinction will be drawn. There are a few things to note about SQL as a database language, because it is a relational database language, SQL may be grouped with the non-procedural database languages. By non-procedural it is meant that users (with the help of the various statements) have only to specify which data they want and not how this data must be found. C++, Java and VB are examples of Procedural languages. It also means that there are no variables, IF statements or loop constructs. Because it is non procedural, it is very difficult to teach, and the only way to learn it is by working through this book and picking up how certain results can be achieved. SQL can be used in two ways. First, interpretively: an SQL statement is entered at a terminal or PC and immediately processed or interpreted. The result is also visible immediately. This is known as interactive SQL. The second way is known as embedded SQL. The SQL statements are embedded in a program written in another, procedural language. Results of these statements are not immediately visible to the user, but are processed by the 'enveloping' program. In this module we shall be assuming the interpretive use of SQL.