测试文件

SELECT *
FROM employees;
2.2
SELECT employee_id,first_name,salary,(salary+100)*12
FROM employees;
2.3
SELECT '姓'||last_name||' '||  '名'||first_name
FROM employees;
2.4
SELECT DISTINCT job_id
FROM employees;
3.1
SELECT *
FROM employees
WHERE last_name='Chen';
3.2
SELECT *
FROM employees
WHERE hire_date>'09-7月-1997' AND job_id<>'IT_PROG';
3.3
SELECT *
FROM employees
WHERE last_name LIKE '__a%';
3.4
SELECT *
FROM employees
WHERE  department_id NOT IN('10','20','110');
3.5
SELECT *
FROM employees
WHERE department_id='50'
ORDER BY salary DESC ,first_name ASC;
3.6
SELECT *
FROM employees
WHERE manager_id IS NULL;
3.7
SELECT last_name,salary,department_id
FROM employees
WHERE salary>=4500 AND (department_id='50' OR department_id='60');8.1
SELECT * 
FROM departments
INSERT INTO departments (department_id,
                         department_name,
                         location_id
                         )
VALUES('300','QQQ','1500');
INSERT INTO departments (department_id,
                         department_name,
                         location_id
                         )
VALUES('310','TTT','1700');
8.2
INSERT INTO departments(department_id ,department_name)
VALUES ('320','F1');
INSERT INTO departments(department_id ,department_name)
VALUES ('330','F2');
8.3.1
INSERT INTO departments (department_id,
                         department_name,
                         manager_id,
                         location_id
                         )
VALUES('350','人力资源','100','1700');
8.3.2
SAVEPOINT a;
8.3.3
SELECT *
FROM departments
WHERE department_id='350';
8.3.4
DELETE FROM departments
WHERE department_id>200;
8.3.5
SAVEPOINT b;
8.3.6
SELECT *
FROM departments;
8.3.7
UPDATE departments
SET manager_id=110
WHERE department_id='10';
8.3.8
SELECT *
FROM departments;
8.3.9
ROLLBACK TO b;
8.3.11
SELECT *
FROM departments;9.1
CREATE TABLE date_test(
             d DATE);
SELECT *
FROM date_test;
INSERT INTO date_test(d  
 )
VALUES  ('18-8月-1998');
INSERT INTO date_test(d  
 )
VALUES (SYSDATE);
9.2
CREATE TABLE dtest
AS 
SELECT * FROM departments
WHERE department_id<200;
SELECT *
FROM dtest;
9.3
CREATE TABLE empl
AS 
SELECT * FROM employees
WHERE department_id<50;
SELECT *
FROM empl;
9.4
CREATE TABLE sstudent (
       sno CHAR(4) PRIMARY KEY,
       sname VARCHAR(8), 
       sage NUMBER NOT NULL,
 CONSTRAINT sstudent_sname_uni UNIQUE(sname));
 SELECT *
 FROM sstudent;
9.5
CREATE TABLE sc (
       sno CHAR(4),
       cname VARCHAR(8)  PRIMARY KEY, 
       grade NUMBER, 
CONSTRAINT sc_sno_fk FOREIGN KEY(sno) REFERENCES  sstudent(sno));
SELECT *
FROM sc;
9.6
ALTER TABLE sstudent ADD(sex CHAR(2) DEFAULT'女');
9.7
ALTER TABLE sstudent MODIFY sname CHAR(10);
SELECT *
FROM sstudent;
9.8
ALTER TABLE sstudent MODIFY sage DEFAULT NULL;
9.9
ALTER TABLE sc ADD CONSTRAINT sc_grade_chk CHECK (grade>=1 AND grade<=100);
9.10
ALTER TABLE sc DROP CONSTRAINT sc_sno_fk;