--习题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;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

--习题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;

存笔记

posted on 2022-02-16 22:42 三天乐趣阅读(126) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· oracle练习题-1

· oracle练习题-scott实例

· OraclePractice

· oracle基础技术点全记录

· Mysql 5.7 学习

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· .NET Core 中如何实现缓存的预热？
· 三行代码完成国际化适配，妙~啊~
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？

2025年3月

日

一

二

三

四

五

六

昵称：三天乐趣
园龄： 5年10个月
粉丝： 15
关注： 13

+加关注

搜索

常用链接

最新随笔

我的标签

积分与排名

随笔分类

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论