--习题1：写出LAST_NAME列和FIRST_NAME列中间加一个空格连在一起输出，查找出列名为my$name的sql语句
select e.last_name || ' ' || e.first_name as my$name from s_emp e;
select e.last_name || ' ' || e.first_name "my$name" from s_emp e;
---扩充：查找S_EMP表中员工userid为**的入职时间**的信息---
select '员工姓名为'||e.last_name ||''||e.first_name ||'的入职时间'||e.start_date "员工的入职时间" from s_emp e;
---时间只显示年月日--
select '员工姓名为'||e.last_name ||' '||e.first_name ||'的入职时间'|| to_char(e.start_date,'yyyy-mm-dd')
"员工的入职时间" from s_emp e;
--习题2：请查找出表s_emp中，title列不重复的内容
select distinct title from s_emp;
--习题3：s_emp表，查找出员工的id、last_name、年薪，请按照员工的manager_id降序排列，年薪从多到少排序
select id,manager_id,salary*12 "年薪" from s_emp order by manager_id desc,"年薪" desc;
--习题4：在s_emp表中查找出薪水大于1500并小于2000的员工ID、last_name、first_name、salary信息。
select userid,first_name,last_name,salary from s_emp e where salary between 1500 and 2000;
--WHERE SALARY>=1500 AND SALARY<=2000;
--习题5：请在s_emp表中查找出列ID，last_name，筛选出last_name以大写字母M开头或者是小写字母t结尾的信息
select id,last_name from s_emp where last_name like 'M%' or last_name like '%t';
--习题6：找出S_EMP表中，START_DATE是91年的日期，并且以2017.3.28此种格式输出
select to_char(start_date,'YYYY.MM.DD') from s_emp where start_date like '%91';
--习题7：从表S_CUSTOMER,S_REGION中查找出顾客表的NAME、REGION_ID列和区域表的name列，REGION_ID为5号区域
select c.name,c.region_id,r.name from s_customer c,s_region r where c.region_id = r.id and regoin_id = 5;
--习题8：请统计出表S_ORD中，11号销售人员（SALES_REP_ID=11）的顾客数量,以及他的TOTAL平均值
select count(*),avg(total) from s_ord where sales_rep_id = 11;
--习题9：在s_customer表中，统计出信用等级credit_rating为GOOD的顾客数，统计列名为#Cust
select credit_rating,count(*) "#Cust" from s_customer where credit_rating = 'GOOD' group by credit_rating;
--习题10：请用左外连接的oracle语法以及标准sql语法重写以上事例
SELECT E.LAST_NAME,E.ID,C.NAME
FROM S_EMP E,S_CUSTOMER C
WHERE C.SALES_REP_ID=E.ID(+)
ORDER BY E.ID;
SELECT E.LAST_NAME,E.ID,C.NAME
FROM S_CUSTOMER C LEFT OUTER JOIN S_EMP E
ON C.SALES_REP_ID=E.ID
ORDER BY E.ID;
--习题11：在表s_emp中，查询出title列和非'VP'开头的title薪水总和列，此列用别名 PAYROLL显示，查询出该职位的所有员工薪水总和需要大于5000，PAYROLL列按升序排列
SELECT TITLE,SUM(SALARY) PAYROLL
FROM S_EMP
WHERE TITLE NOT LIKE 'VP%'
GROUP BY TITLE
HAVING SUM(SALARY)>5000
ORDER BY PAYROLL;
--习题12：在表s_emp表中，查询出列last_name,title,salary，请仅使用where子查询方法，不能使用group by和having子句，查询出小于salary平均值的员工信息
SELECT LAST_NAME,TITLE,SALARY
FROM S_EMP
WHERE SALARY<(SELECT AVG(SALARY)
FROM S_EMP);
--习题13:请根据下面提供的表S_PHONE创建它的子表S_STUDENT2，
--对于表S_STUDENT2的创建要求是：
--ID列 数值最大位数为10，主键列
--NAME列 最大20个字符，不能为空
--AGE列 数值最大位数为3，检查约束为age大于1小于160
--SEX列 最大字符数为6，检查约束sex是male或female
--GENDER列 最大字符数为20，不能为空
--PHONE列 数值最大位数为11，具有唯一性，在表级别把此列设为外键列，其父表是S_PHONE
--以上所有列的约束条件需要自定义名称，名称请按照'表-列-约束条件'规则定义
--S_STUDENT2表创建好后，请在两张表中分别插入不同数据，验证约束条件是否生效
--提供：
create table s_phone( --创建一个phone父表
id number(11) primary key); --创建id列为11位数字型，主键约束
create table s_student2(
id number(10) constraint pk_s_student2_id primary key, --列名，数字型长度10，约束名，列主键约束
name varchar2(20) constraint nn_s_student2_name not null, --列名，字符型长度20，约束名，列非空约束
age number(3) constraint ck_s_student2_age check(age between 1 and 160),--列检查约束
sex varchar2(6) constraint ck_s_student2_sex check(sex in('male','female')),--列检查约束
gender varchar2(20) constraint nn_s_student2_gender not null, --列非空约束
phone number(11) constraint uk_s_student2_phone unique, --列唯一约束
constraint fk_s_student2_phone foreign key(phone) references s_phone(id) --外键约束
);
insert into s_phone values(13004158667); --在表s_phone中插入数据
insert into s_student2 values(1,'JACK',1,'male','高三一班',13004158667);--插入数据，phone与s_phone中的id一样
--习题14：请对习题13创建的表，分别进行增加列、对增加的列修改其数据类型、增加约束条件、列的重命名、删除列、删除表
ALTER TABLE S_STUDENT2
ADD (CARDID NUMBER(30));
ALTER TABLE S_STUDENT2
MODIFY(CARDID VARCHAR2(18));
ALTER TABLE S_STUDENT2
ADD CONSTRAINT UK_S_STUDENT_CARDID UNIQUE (CARDID);
ALTER TABLE S_STUDENT2
RENAME COLUMN CARDID TO CARD_ID;
DROP TABLE S_STUDENT2;
--习题15：请根据习题14方法创建的表和插入的数据，再使用NSERT INTO子查询方法创建新表和插入数据，并验证其数据类型、约束条件是否生效，创建表代码如下：
CREATE TABLE S_STUDENT2
(
ID NUMBER(10)
CONSTRAINT PK_S_STUDENT2_ID PRIMARY KEY,
NAME VARCHAR2(20)
CONSTRAINT NN_S_STUDENT2_NAME NOT NULL,
AGE NUMBER(3)
CONSTRAINT CK_S_STUDENT2_AGE CHECK(AGE BETWEEN 1 AND 160),
SEX VARCHAR2(6)
CONSTRAINT CK_S_STUDENT2_SEX CHECK(SEX IN('male','female')),
GENDER VARCHAR2(20)
CONSTRAINT NN_S_STUDENT2_GENDER NOT NULL,
PHONE NUMBER(11)
CONSTRAINT UK_S_STUDENT2_PHONE UNIQUE,
CONSTRAINT FK_S_STUDENT2_PHONE
FOREIGN KEY (PHONE)
REFERENCES S_PHONE(ID)
);
INSERT INTO S_PHONE
VALUES (13376410072);
INSERT INTO S_STUDENT2
VALUES (1,'CLEMET',28,'female','博士生一班',13376410072);
COMMIT;
INSERT INTO S_PHONE
VALUES (15972725151);
INSERT INTO S_STUDENT2
VALUES (2,'JOMM',26,'female','博士生二班',15972725151);
COMMIT;
INSERT INTO S_PHONE
VALUES (18063239145);
INSERT INTO S_STUDENT2
VALUES (3,'HOAT',25,'male','博士生三班',18063239145);
COMMIT;
---------------------------------15题答案-----
CREATE TABLE S_STUDENT3
AS
SELECT ID,NAME,AGE,SEX,PHONE
FROM S_STUDENT2
WHERE ID=1;
INSERT INTO S_STUDENT3
SELECT ID,NAME,AGE,SEX,PHONE
FROM S_STUDENT2
WHERE ID in(2,3);
--习题16：将S_EMP表中41号部门的员工数据查出，存入到视图中，查看数据是否正确存入，删除视图
CREATE VIEW V_EMP
AS
SELECT ID,LAST_NAME,TITLE
FROM S_EMP
WHERE DEPT_ID=41;
SELECT * FROM V_EMP;
SELECT ID,LAST_NAME,TITLE
FROM S_EMP
WHERE DEPT_ID=45;
--删除视图
DROP VIEW V_EMP;
--习题17：请为S_EMP表创建一个序列，步长、开始值、最大、最小值请自定义，使用新建的SEQUENCE向表中插入几条数据，删除序列并删除插入的数据
--创建SEQUENCE
CREATE SEQUENCE SEQ_S_EMP
INCREMENT BY 2
START WITH 60
MAXVALUE 9999999
NOCACHE
NOCYCLE;
--使用SEQUENCE
INSERT INTO S_EMP(ID,LAST_NAME,FIRST_NAME,USERID,START_DATE,MANAGER_ID,TITLE,DEPT_ID,SALARY)
VALUES (SEQ_S_EMP.NEXTVAL,'SS','WANG','WANGSS1','2017/2/13',1,'President',50,8888);
--删除SEQUENCE
DROP SEQUENCE SEQ_S_EMP;
--习题1：写出LAST_NAME列和FIRST_NAME列中间加一个空格连在一起输出，查找出列名为my$name的sql语句
select e.last_name || ' ' || e.first_name as my$name from s_emp e;
select e.last_name || ' ' || e.first_name "my$name" from s_emp e;
---扩充：查找S_EMP表中员工userid为**的入职时间**的信息---
select '员工姓名为'||e.last_name ||''||e.first_name ||'的入职时间'||e.start_date "员工的入职时间" from s_emp e;
---时间只显示年月日--
select '员工姓名为'||e.last_name ||' '||e.first_name ||'的入职时间'|| to_char(e.start_date,'yyyy-mm-dd')
"员工的入职时间" from s_emp e;
--习题2：请查找出表s_emp中，title列不重复的内容
select distinct title from s_emp;
--习题3：s_emp表，查找出员工的id、last_name、年薪，请按照员工的manager_id降序排列，年薪从多到少排序
select id,manager_id,salary*12 "年薪" from s_emp order by manager_id desc,"年薪" desc;
--习题4：在s_emp表中查找出薪水大于1500并小于2000的员工ID、last_name、first_name、salary信息。
select userid,first_name,last_name,salary from s_emp e where salary between 1500 and 2000;
--WHERE SALARY>=1500 AND SALARY<=2000;
--习题5：请在s_emp表中查找出列ID，last_name，筛选出last_name以大写字母M开头或者是小写字母t结尾的信息
select id,last_name from s_emp where last_name like 'M%' or last_name like '%t';
--习题6：找出S_EMP表中，START_DATE是91年的日期，并且以2017.3.28此种格式输出
select to_char(start_date,'YYYY.MM.DD') from s_emp where start_date like '%91';
--习题7：从表S_CUSTOMER,S_REGION中查找出顾客表的NAME、REGION_ID列和区域表的name列，REGION_ID为5号区域
select c.name,c.region_id,r.name from s_customer c,s_region r where c.region_id = r.id and regoin_id = 5;
--习题8：请统计出表S_ORD中，11号销售人员（SALES_REP_ID=11）的顾客数量,以及他的TOTAL平均值
select count(*),avg(total) from s_ord where sales_rep_id = 11;
--习题9：在s_customer表中，统计出信用等级credit_rating为GOOD的顾客数，统计列名为#Cust
select credit_rating,count(*) "#Cust" from s_customer where credit_rating = 'GOOD' group by credit_rating;
--习题10：请用左外连接的oracle语法以及标准sql语法重写以上事例
SELECT E.LAST_NAME,E.ID,C.NAME
FROM S_EMP E,S_CUSTOMER C
WHERE C.SALES_REP_ID=E.ID(+)
ORDER BY E.ID;
SELECT E.LAST_NAME,E.ID,C.NAME
FROM S_CUSTOMER C LEFT OUTER JOIN S_EMP E
ON C.SALES_REP_ID=E.ID
ORDER BY E.ID;
--习题11：在表s_emp中，查询出title列和非'VP'开头的title薪水总和列，此列用别名 PAYROLL显示，查询出该职位的所有员工薪水总和需要大于5000，PAYROLL列按升序排列
SELECT TITLE,SUM(SALARY) PAYROLL
FROM S_EMP
WHERE TITLE NOT LIKE 'VP%'
GROUP BY TITLE
HAVING SUM(SALARY)>5000
ORDER BY PAYROLL;
--习题12：在表s_emp表中，查询出列last_name,title,salary，请仅使用where子查询方法，不能使用group by和having子句，查询出小于salary平均值的员工信息
SELECT LAST_NAME,TITLE,SALARY
FROM S_EMP
WHERE SALARY<(SELECT AVG(SALARY)
FROM S_EMP);
--习题13:请根据下面提供的表S_PHONE创建它的子表S_STUDENT2，
--对于表S_STUDENT2的创建要求是：
--ID列 数值最大位数为10，主键列
--NAME列 最大20个字符，不能为空
--AGE列 数值最大位数为3，检查约束为age大于1小于160
--SEX列 最大字符数为6，检查约束sex是male或female
--GENDER列 最大字符数为20，不能为空
--PHONE列 数值最大位数为11，具有唯一性，在表级别把此列设为外键列，其父表是S_PHONE
--以上所有列的约束条件需要自定义名称，名称请按照'表-列-约束条件'规则定义
--S_STUDENT2表创建好后，请在两张表中分别插入不同数据，验证约束条件是否生效
--提供：
create table s_phone( --创建一个phone父表
id number(11) primary key); --创建id列为11位数字型，主键约束
create table s_student2(
id number(10) constraint pk_s_student2_id primary key, --列名，数字型长度10，约束名，列主键约束
name varchar2(20) constraint nn_s_student2_name not null, --列名，字符型长度20，约束名，列非空约束
age number(3) constraint ck_s_student2_age check(age between 1 and 160),--列检查约束
sex varchar2(6) constraint ck_s_student2_sex check(sex in('male','female')),--列检查约束
gender varchar2(20) constraint nn_s_student2_gender not null, --列非空约束
phone number(11) constraint uk_s_student2_phone unique, --列唯一约束
constraint fk_s_student2_phone foreign key(phone) references s_phone(id) --外键约束
);
insert into s_phone values(13004158667); --在表s_phone中插入数据
insert into s_student2 values(1,'JACK',1,'male','高三一班',13004158667);--插入数据，phone与s_phone中的id一样
--习题14：请对习题13创建的表，分别进行增加列、对增加的列修改其数据类型、增加约束条件、列的重命名、删除列、删除表
ALTER TABLE S_STUDENT2
ADD (CARDID NUMBER(30));
ALTER TABLE S_STUDENT2
MODIFY(CARDID VARCHAR2(18));
ALTER TABLE S_STUDENT2
ADD CONSTRAINT UK_S_STUDENT_CARDID UNIQUE (CARDID);
ALTER TABLE S_STUDENT2
RENAME COLUMN CARDID TO CARD_ID;
DROP TABLE S_STUDENT2;
--习题15：请根据习题14方法创建的表和插入的数据，再使用NSERT INTO子查询方法创建新表和插入数据，并验证其数据类型、约束条件是否生效，创建表代码如下：
CREATE TABLE S_STUDENT2
(
ID NUMBER(10)
CONSTRAINT PK_S_STUDENT2_ID PRIMARY KEY,
NAME VARCHAR2(20)
CONSTRAINT NN_S_STUDENT2_NAME NOT NULL,
AGE NUMBER(3)
CONSTRAINT CK_S_STUDENT2_AGE CHECK(AGE BETWEEN 1 AND 160),
SEX VARCHAR2(6)
CONSTRAINT CK_S_STUDENT2_SEX CHECK(SEX IN('male','female')),
GENDER VARCHAR2(20)
CONSTRAINT NN_S_STUDENT2_GENDER NOT NULL,
PHONE NUMBER(11)
CONSTRAINT UK_S_STUDENT2_PHONE UNIQUE,
CONSTRAINT FK_S_STUDENT2_PHONE
FOREIGN KEY (PHONE)
REFERENCES S_PHONE(ID)
);
INSERT INTO S_PHONE
VALUES (13376410072);
INSERT INTO S_STUDENT2
VALUES (1,'CLEMET',28,'female','博士生一班',13376410072);
COMMIT;
INSERT INTO S_PHONE
VALUES (15972725151);
INSERT INTO S_STUDENT2
VALUES (2,'JOMM',26,'female','博士生二班',15972725151);
COMMIT;
INSERT INTO S_PHONE
VALUES (18063239145);
INSERT INTO S_STUDENT2
VALUES (3,'HOAT',25,'male','博士生三班',18063239145);
COMMIT;
---------------------------------15题答案-----
CREATE TABLE S_STUDENT3
AS
SELECT ID,NAME,AGE,SEX,PHONE
FROM S_STUDENT2
WHERE ID=1;
INSERT INTO S_STUDENT3
SELECT ID,NAME,AGE,SEX,PHONE
FROM S_STUDENT2
WHERE ID in(2,3);
--习题16：将S_EMP表中41号部门的员工数据查出，存入到视图中，查看数据是否正确存入，删除视图
CREATE VIEW V_EMP
AS
SELECT ID,LAST_NAME,TITLE
FROM S_EMP
WHERE DEPT_ID=41;
SELECT * FROM V_EMP;
SELECT ID,LAST_NAME,TITLE
FROM S_EMP
WHERE DEPT_ID=45;
--删除视图
DROP VIEW V_EMP;
--习题17：请为S_EMP表创建一个序列，步长、开始值、最大、最小值请自定义，使用新建的SEQUENCE向表中插入几条数据，删除序列并删除插入的数据
--创建SEQUENCE
CREATE SEQUENCE SEQ_S_EMP
INCREMENT BY 2
START WITH 60
MAXVALUE 9999999
NOCACHE
NOCYCLE;
--使用SEQUENCE
INSERT INTO S_EMP(ID,LAST_NAME,FIRST_NAME,USERID,START_DATE,MANAGER_ID,TITLE,DEPT_ID,SALARY)
VALUES (SEQ_S_EMP.NEXTVAL,'SS','WANG','WANGSS1','2017/2/13',1,'President',50,8888);
--删除SEQUENCE
DROP SEQUENCE SEQ_S_EMP;
存笔记