Demo
/
learn_oracle_notebook
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
learn_oracle_notebook/course/05-practive.sql

742 lines
13 KiB
MySQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

/*
@Author: Fromsko
@GitHub: https://github.com/fromsko
@DataTime: 2024/9/13 15:20
@Finished: 2024/9/13 19:24
@Description: Oracle 高级教程 - 练习
*/
-- 1、查询员工表中薪水大于5600的员工姓名和薪水
SELECT
ENAME,
SALARY
FROM
EMP_WHC
WHERE
SALARY > 5600;
-- 2、查询员工表中员工号为1008的员工姓名和部门号
SELECT
ENAME,
DEPTNO
FROM
EMP_WHC
WHERE
EMPNO = 1008;
-- 3、查询员工表中薪水不在6000到8000的员工的姓名和薪水
SELECT
ENAME,
SALARY
FROM
EMP_WHC
WHERE
SALARY NOT BETWEEN 6000 AND 8000
AND SALARY IS NOT NULL;
-- 4、查询员工表中20和30号部门的员工姓名和部门号
SELECT
ENAME,
DEPTNO
FROM
EMP_WHC
WHERE
DEPTNO=20
OR DEPTNO = 30;
-- 5、查询员工表中没有领导的员工姓名及职位，按职位排序
SELECT
ENAME,
JOB
FROM
EMP_WHC
WHERE
MGR IS NULL
ORDER BY
JOB;
-- 6、查询员工表中有奖金的员工姓名、薪水、奖金，按薪水降序排列
SELECT
ENAME,
SALARY,
BONUS
FROM
EMP_WHC
WHERE
BONUS IS NOT NULL
ORDER BY
SALARY DESC;
-- 7、查询员工表中职位的第二个字符是'a'的员工姓名
SELECT
*
FROM
EMP_WHC
WHERE
JOB LIKE '_a%';
/*
SELECT
ENAME,
POSITION
*/
-- 8、查询部门表中部门名字和所在城市
SELECT
DNAME,
LOCATION AS POSITION
FROM
DEPT_WHC;
-- 9、查询员工表中不重复的职位
SELECT
JOB
FROM
(
SELECT
JOB,
COUNT(JOB) JOB_NUM
FROM
EMP_WHC
WHERE
JOB IS NOT NULL
GROUP BY
JOB
)
WHERE
JOB_NUM = 1;
-- 10、查询员工表中员工工号、姓名、薪水， 以及薪水提高20%后的结果
SELECT
EMPNO,
ENAME,
SALARY,
NVL(SALARY, 0) * 1.2 DEVOP_SALARY
FROM
EMP_WHC;
-- 11、查询员工的姓名和薪水，条件限定为薪水 必须大于7200， 并对查询结果按入职时间进行排序，早入职排在前面， 晚入职排在后面
SELECT
ENAME,
SALARY
FROM
EMP_WHC
WHERE
NVL(SALARY, 0) > 7200
ORDER BY
HIREDATE DESC;
-- 12、查询除了'研发部'部门还有哪些部门
SELECT
DNAME
FROM
DEPT_WHC
WHERE
DNAME != '研发部';
-- =========================================================================================
--- 现有数据表Customer,其结构如下：
/*
cust_id int primary key,
cname varchar(25) not null,
birthday date,
account number(5)
*/
CREATE TABLE CUSTOMER (
CUST_ID INT PRIMARY KEY,
CNAME VARCHAR(25) NOT NULL,
BIRTHDAY DATE,
ACCOUNT NUMBER(5)
);
-- 插入数据1-5
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
1,
'John',
TO_DATE('1990-01-15', 'YYYY-MM-DD'),
1000
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
2,
'Amy',
TO_DATE('1985-11-20', 'YYYY-MM-DD'),
1500
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
3,
'Tom',
NULL,
NULL
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
4,
'Emily',
TO_DATE('1992-07-05', 'YYYY-MM-DD'),
2000
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
5,
'David',
TO_DATE('1988-03-10', 'YYYY-MM-DD'),
800
);
-- 插入数据6-10
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
6,
'Linda',
TO_DATE('1987-12-25', 'YYYY-MM-DD'),
1200
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
7,
'Michael',
TO_DATE('1995-05-18', 'YYYY-MM-DD'),
0
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
8,
'Sarah',
TO_DATE('1991-09-30', 'YYYY-MM-DD'),
500
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
9,
'Jason',
TO_DATE('1993-04-08', 'YYYY-MM-DD'),
1800
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
10,
'Michelle',
TO_DATE('1994-08-12', 'YYYY-MM-DD'),
300
);
-- 插入数据11-15
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
11,
'Daniel',
TO_DATE('1989-06-27', 'YYYY-MM-DD'),
2500
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
12,
'Sophia',
TO_DATE('1995-02-14', 'YYYY-MM-DD'),
700
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
13,
'Andrew',
TO_DATE('1992-10-01', 'YYYY-MM-DD'),
0
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
14,
'Jessica',
TO_DATE('1990-11-11', 'YYYY-MM-DD'),
800
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
15,
'Ryan',
TO_DATE('1987-09-05', 'YYYY-MM-DD'),
1500
);
-- 插入数据16-20
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
16,
'Emma',
TO_DATE('1993-06-01', 'YYYY-MM-DD'),
900
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
17,
'Kevin',
TO_DATE('1988-07-31', 'YYYY-MM-DD'),
1800
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
18,
'Olivia',
TO_DATE('1991-12-10', 'YYYY-MM-DD'),
200
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
19,
'William',
TO_DATE('1994-03-22', 'YYYY-MM-DD'),
1000
);
INSERT INTO CUSTOMER (
CUST_ID,
CNAME,
BIRTHDAY,
ACCOUNT
) VALUES (
20,
'Hannah',
TO_DATE('1989-04-17', 'YYYY-MM-DD'),
500
);
-- 1. 构造SQL语句, 列出Customer数据表中每个客户的信息。
-- 如果客户生日未提供，则该列值显示'not available'。
-- 如果没有余额信息，则显示'no account'
SELECT
CUST_ID,
CNAME,
NVL(TO_CHAR(C.BIRTHDAY, 'YYYY-MM-DD'), 'NOT AVAILABLE') BIRTHDAY,
NVL(TO_CHAR(ACCOUNT), 'no account') ACCOUNT
FROM
CUSTOMER C;
-- 2. 构造SQL语句，列出生日在1987年客户的全部信息
SELECT
CUST_ID,
CNAME,
TO_CHAR(BIRTHDAY, 'YYYY-MM-DD') AS BIRTHDAY,
ACCOUNT
FROM
CUSTOMER
WHERE
EXTRACT(YEAR FROM BIRTHDAY) = 1987;
-- 3. 构造SQL语句，列出客户账户的余额总数
SELECT
SUM(ACCOUNT)
FROM
CUSTOMER
WHERE
ACCOUNT IS NOT NULL;
-- 3-1. 按照'2009-04-11 20:35:10'格式显示系统时间
SELECT
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS CURRENT_TIME
FROM
DUAL;
-- 3-2. 构造SQL语句查询员工表中员工编号、姓名，
-- 以及月收入(薪水+奖金)，注意有的员工
-- 可能没有奖金或薪水
SELECT
EMPNO,
ENAME,
NVL(SALARY, 0) + NVL(BONUS, 0) AS MONTHLY_INCOME
FROM
EMP_WHC;
-- 4. 列出每个员工的姓名、薪水、
-- 涨薪后工资(涨幅为8%)，元为单位进行四舍五入
SELECT
ENAME,
SALARY,
ROUND(SALARY * 1.08, 0) AS INCREASED_SALARY
FROM
EMP_WHC;
-- 5. 查询各个职位的员工人数
SELECT
JOB,
COUNT(JOB)
FROM
EMP_WHC
GROUP BY
JOB;
-- 6. 查询员工最高薪水和最低薪水的差距，列名为DIFFERENCE
SELECT
(MAX_SAL - MIN_SAL) AS DIFFERENCE
FROM
(
SELECT
MIN(SALARY) MIN_SAL,
MAX(SALARY) MAX_SAL
FROM
EMP_WHC
WHERE
SALARY IS NOT NULL
);
-- 7. 哪一子句可实现SELECT语句查询员工平均薪水小于5000的部门信息？(A)
/*
A.GROUP BY dept_id WHERE AVG(salary)<5000
B.GROUP BY AVG(salary) HAVING AVG(salary)<5000
C.GROUP BY dept_id HAVING AVG(salary)<5000
D.GROUP BY AVG(salary)<5000
*/
-- 8. Student表的表结构
/*
(
sid_id int
start_date date
end_date date
)
*/
-- 在 start_date 列上使用哪些函数是正确的？(C)
/*
A.sum(start_date)
B.count(start_date)
C.avg(start_date,end_date)
D.min(start_date)
*/
/*
11. 当执行以下查询语句
SELECT empno,ename
from emp_xu
where empno=7782 or empno=7876;
在WHERE语句中，可以实现与OR相同功能的操作符是? (A)
A.IN
B.BETWEEN...AND..
C.LIKE
D.AND
*/
-- ==========================================================================================
-- 1、显示所有员工的姓名、部门号和部门名称
SELECT
E.ENAME,
E.DEPTNO,
D.DNAME
FROM
EMP_WHC E,
DEPT_WHC D;
-- 2、查询在'南京'工作的员工的姓名、职位、部门号、部门名称
SELECT
E.ENAME,
E.JOB,
E.DEPTNO,
D.DNAME
FROM
EMP_WHC E,
DEPT_WHC D
WHERE
D.LOCATION = '南京';
-- 3、查询所有员工的员工号、姓名以及他的领导的员工号和姓名
SELECT
M.EMPNO,
M.ENAME,
E.EMPNO MANAGER,
E.ENAME MANAGER_NAME
FROM
EMP_WHC E
INNER JOIN EMP_WHC M
ON E.EMPNO = M.MGR;
-- 4、查询各部门员工姓名和他们所在位置
-- Select rows from a Table
SELECT
ENAME,
LOCATION
FROM
EMP_WHC,
DEPT_WHC;
-- 5、查询出'张无忌'的领导是谁
SELECT
E.ENAME
FROM
EMP_WHC E
WHERE
E.EMPNO = (
SELECT
MGR
FROM
EMP_WHC
WHERE
ENAME = '张无忌'
);
-- 6、'张三丰'领导谁
SELECT
ENAME
FROM
EMP_WHC
WHERE
MGR IN ( -- 张三丰 ID
SELECT
EMPNO
FROM
EMP_WHC
WHERE
ENAME = '张三丰'
);
-- 7、查询和'张三丰'相同部门的员工姓名和雇佣日期
SELECT
ENAME,
HIREDATE
FROM
EMP_WHC
WHERE
DEPTNO IN ( -- 张三丰的部门
SELECT
DEPTNO
FROM
EMP_WHC
WHERE
ENAME = '张三丰'
)
AND ENAME <> '张三丰';
-- 8、查询薪水比公司平均薪水高的所有员工的员工号、姓名和薪水
WITH T AS (
SELECT
DEPTNO,
AVG(NVL(SALARY, 0)) SALARY
FROM
EMP_WHC
GROUP BY
DEPTNO
)
SELECT
DISTINCT E.EMPNO,
E.ENAME,
E.SALARY
FROM
EMP_WHC E,
T
WHERE
E.SALARY > T.SALARY;
-- 9、查询领导是'张三丰'的员工姓名和薪水
SELECT
ENAME,
SALARY
FROM
EMP_WHC
WHERE
MGR = (
SELECT
EMPNO
FROM
EMP_WHC
WHERE
ENAME = '张三丰'
);
-- 10、显示'研发部'有哪些职位
SELECT
JOB
FROM
EMP_WHC
WHERE
DEPTNO = (
SELECT
DEPTNO
FROM
DEPT_WHC
WHERE
DNAME = '研发部'
);
-- 11、各个部门中薪水大于5000的员工人数
SELECT
DEPTNO,
COUNT(SALARY)
FROM
EMP_WHC
WHERE
SALARY > 5000
GROUP BY
DEPTNO;
-- 13、所在部门平均薪水高于5000的员工名字 [题目有二义性]
SELECT
DEPTNO
FROM
(
SELECT
DEPTNO,
AVG(SALARY) SALARY
FROM
EMP_WHC
GROUP BY
DEPTNO
)
WHERE
SALARY > 5000;
-- 14、列出各个部门中薪水最高的员工的姓名、部门号和薪水
SELECT
ENAME,
DEPTNO,
SALARY
FROM
EMP_WHC
WHERE
(SALARY, DEPTNO) IN (
SELECT
MAX(SALARY) SALARY,
DEPTNO
FROM
EMP_WHC
GROUP BY
DEPTNO
);
-- 15、下列哪个组合查询返回两个查询所选择的所有的行 (B)
/*
A.Union
B.Union all
C.Union only
D.connect by
*/
-- 16. 从员工表的'姓名'字段中找出名字包含'玛丽'的人，下面哪条SELECT语句正确 (D)
/*
A.Select * from 员工 where 姓名='_玛丽_';
B.Select * from 员工 where 姓名='%玛丽_';
C.Select * from 员工 where 姓名 LIKE '_玛丽%';
D.Select * from 员工 where 姓名 LIKE '%玛丽%';
*/
-- 17. 检查下列数据表中的数据
/*
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5500
下面子查询正确的是? (A)
A.select * from emp where salary>
(
select min(salary)
from emp group by department_id
);
B.select * from emp where salary=(
select avg(salary)
from emp group by department_id
);
C.select department_id from emp
where salary>all(select avg(salary)
from emp group by department_id
);
D.select last_name from emp
where salary>any(select max(salary)
from emp group by department_id
);
*/
Reference in New Issue View Git Blame Copy Permalink