1、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';
2、要求将视图emp_v1修改为查询电话号码以‘011’开头的并且邮箱中包含e字符的员工姓名和邮箱、电话号码
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,email,phone_number
FROM employees
WHERE phone_number LIKE '011%' AND email LIKE '%e%';
SELECT * FROM employees;
3、向emp_v1插入一条记录，是否可以？
可以
INSERT INTO emp_v1 VALUES('john','sdfe','0112345');
4、修改刚才记录中的电话号码为‘119’
UPDATE emp_v1 SET phone_number ='119' WHERE last_name='john';
5、删除刚才记录
DELETE FROM emp_v1 WHERE last_name='';
6、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
SELECT *
FROM departments
WHERE department_id IN(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000
)
CREATE OR REPLACE VIEW emp_v2
AS
SELECT *
FROM departments
WHERE department_id IN(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000
)
SELECT * FROM emp_v2;
7、向emp_v2中插入一条记录，是否可以？
INSERT INTO emp_v2 VALUES(999,'dd',201,1700);
8、删除刚才的emp_v2和emp_v1
DROP VIEW emp_v2,emp_v1;
