练习题1~5

Execise01 (请写出实现以下功能的sql语句)
1.创建只有一个数据文件的表空间test
  指定保存路径，指定是否可以自动扩展。

2.创建一个名为DAVID的用户，口令为davidpass，设定其默认表空间为test，临时表空间为temp，并授予其连接数据库和创建表对象的权限。同时授予其访问另一用户MARTIN的表TEST的权限。

3.删除用户david及其所拥有的对象。

4.删除表空间test及其内容及数据文件。

sys sys

CREATE TABLESPACE test
DATAFILE 'D:/test_data.dbf' SIZE 1M
AUTOEXTEND ON

CREATE USER David
IDENTIFIED BY davidpass
DEFAULT TABLESPACE test
TEMPORARY TABLESPACE TEMP

GRANT CONNECT to DAVID

CREATE USER MARTIN
IDENTIFIED BY martinpass
DEFAULT TABLESPACE test
TEMPORARY TABLESPACE TEMP

GRANT CONNECT,RESOURCE to MARTIN

martin
CREATE   TABLE   TEST  (
    age  NUMBER(10,0),
    sex  VARCHAR2(30),
    test VARCHAR2(40),
    RELAMASTER   VARCHAR2(100)
)

sys
GRANT SELECT ON MARTIN.TEST TO DAVID

david
select *
from MARTIN.TEST

3.drop user david cascade;

4.DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

 

 

Execise02
使用t_student,t_course,t_grade表，完成以下练习：

1.查询85年以后出生的学生姓名、性别和出生日期
select f_name,f_sex,f_birth from t_student where f_birth>(to_date('1985-01-01','yyyy-mm-dd'));

2.列表显示所有可能的学生选课组合(学号、课程号)
select*from t_student cross join t_course;

3.查询1、2、4班中陈姓同学的信息
select*from t_student where f_name like '陈%'and (f_class=1 or f_class=2 or f_class=4);

4.查询所有及格的学生姓名、所选课程名及所得分数
select t_student.f_name,t_course.f_name,t_grade.f_grade from t_student,t_course,t_grade
where (t_student.f_id=t_grade.f_stuid and t_course.f_id=t_grade.f_courseid)
and t_grade.f_grade>=60;

 

select t_student.f_name, t_course.f_name, f_grade
  from t_student, t_course, t_grade
 where (t_student.f_id = t_grade.f_stuid and
       t_course.f_id = t_grade.f_courseid) and f_grade >= 60

5.统计各门课程的及格人数(课程编号、课程名、及格人数)
select t_course.f_id, t_course.f_name, count(t_grade.f_grade)
from t_grade left join t_course on t_grade.f_courseid = t_course.f_id
where t_grade.f_grade >= 60
group by t_course.f_id, t_course.f_name;或

SELECT T_COURSE.F_ID, T_COURSE.F_NAME, COUNT(T_COURSE.F_ID)
FROM T_COURSE, T_GRADE
WHERE T_GRADE.F_GRADE >= 60 AND T_COURSE.F_ID = T_GRADE.F_COURSEID
GROUP BY T_COURSE.F_ID,t_course.f_name;

6.统计各门课程选课的人数，若课程无人选择则选课人数为0(课程编号、课程名、选课人数)

SELECT F_ID, F_NAME, NVL(COUNT(f_stuid), 0)
  FROM T_COURSE left JOIN T_GRADE
 ON T_COURSE.F_ID = T_GRADE.F_COURSEID
 GROUP BY F_ID, F_NAME;

7.查询未选课程的学生名单(学号、姓名)
select t_student.f_id,t_student.f_name from t_student left join t_grade on
t_student.f_id=t_grade.f_stuid where t_grade.f_courseid is null;

8.查询张三比李四大几个月
  SELECT MONTHS_BETWEEN(T1.F_BIRTH, T2.F_BIRTH)
  FROM T_STUDENT T1, T_STUDENT T2
 WHERE T1.F_NAME = '李四' AND T2.F_NAME = '张三';
 或
SELECT MONTHS_BETWEEN((SELECT T1.F_BIRTH
                        FROM T_STUDENT T1
                       WHERE T1.F_NAME = '张三'),
                      (SELECT T2.F_BIRTH
                         FROM T_STUDENT T2
                        WHERE T2.F_NAME = '李四'))
  FROM DUAL;

select*from t_student;
select*from t_course;
select f_grade from t_grade where f_grade>=60;
select months_between(systime
SELECT MONTHS_BETWEEN((SELECT F_BIRTH
                        FROM T_STUDENT
                       WHERE F_NAME = '张三'),
                      (SELECT F_BIRTH
                         FROM T_STUDENT
                        WHERE F_NAME = '李四'))
  FROM DUAL;

 

 

Execise03
1. 使用book表（bookid,bookname,author,pub_time,price），完成以下练习：
  显示符合以下条件的书籍的名称：
  (1)购买于2001年1月21日之前出版的书；
  (2)价格低于500美元或超过900美元.

create table book(
bookid int primary key,
bookname varchar2(20) not null,
author varchar(20) not null,
price int);

alter table book add pub_time date;
select*from book;

insert into book values(1,'english','莉莉',15,to_date('2000-11-22','yyyy-mm-dd'));
insert into book values(2,'maths','韩梅梅',25,to_date('2006-6-1','yyyy-mm-dd'));
insert into book values(3,'note','小刚',3000,to_date('2001-1-21','yyyy-mm-dd'));
insert into book values(4,'sql','路西',800,to_date('2007-8-15','yyyy-mm-dd'));

1.
select bookname from book where pub_time<to_date('2001-1-21','yyyy-mm-dd');

2.
select bookname from book where price<500 or price>900;

2.检查EMPLOYEES表的结果
EMP_ID  NUMBER(4) not null  主键
LAST_NAME VARCHAR2(30) not null
FIRST_NAME VARCHAR2(30)
DEPT_ID  NUMBER(2) 外键关联到表DEPT（表DEPT包括DEPT_ID、DEPT_NAME两个字段）
JOB_CAT  VARCHAR2(30) [说明]JOB_CAT为工作类别
SALARY  NUMBER(8,2) 0-50000之间
（1）创建表EMPLOYEES和DEPT，满足题目中的要求，并给出测试数据
（2）编写一个语句，只有当最低工资少于5000而且最高工资超过15000时，才显示部门ID以及该部门支付的最低工资和最高工资.
3.参照上题给出的EMPLOYEES表，编写一个语句，显示各部门的每个工作类别中支付的最高工资.
create table dept(
dept_id NUMBER(2) primary key,
dept_name varchar2(20));

create table employees(
emp_id  NUMBER(4) not null,
last_name VARCHAR2(30) not null,
first_name VARCHAR2(30),
dept_id number(2),
job_cat varchar2(30),
salary number(8,2),
constraint ll primary key(emp_id),
constraint oo foreign key(dept_id) references dept(dept_id));

insert into dept values(1,'市场');
insert into dept values(4,'科研');

select*from dept;

insert into employees values(1000,'aa','a1',1,'门卫',2000);
insert into employees values(1001,'ab','a2',2,'经理',5500);
insert into employees values(1002,'ag','a7',4,'总监',16000);
insert into employees values(1003,'ac','a3',4,'实习生',2000);
insert into employees values(1004,'ad','a4',3,'CEO',20000);
insert into employees values(1005,'ae','a5',3,'专员',4000);
insert into employees values(1006,'af','a6',2,'区域经理',10000);
insert into employees values(1007,'am','a8',4,'实习生',2100);

select *from employees;

2.
select dept_id,min(salary),max(salary) from employees group by dept_id having max(salary)>15000 and min(salary)<5000;

3.
select job_cat,max(salary) from employees group by job_cat;

 

Execise04
1.显示1-17的随机整数
[提示]dbms_random.random内置包函数可产生随机数

 

select (1+ABS(MOD(dbms_random.random,17))) from dual;
2.张三比李四大几个月

select months_between(t2.f_birth, t1.f_birth)
from t_student t1,t_student t2
where t1.f_name='张三' and t2.f_name='李四';

 

 

Execise05
1.创建一个名为Stock_Received表，其中包括Stock_ID、Stock_Date、Cost列。根据Stock_Date列为该表创建3个范围分区，然后根据Cost将表分为8个散列分区，共创建24个子分区.
create table stock_received(
stock_id int ,
stock_date date,
cost number(10)
)
PARTITION BY RANGE (stock_date)
SUBPARTITION BY HASH (stock_id)
SUBPARTITIONS 8
(
  
    PARTITION S1 VALUES LESS THAN (TO_DATE('01/7月/2001',
    'DD/MON/YYYY')),
    PARTITION S2 VALUES LESS THAN (TO_DATE('01/9月/2001',
    'DD/MON/YYYY')),
    PARTITION S3 VALUES LESS THAN (MAXVALUE)
);
select*from stock_received;