SQL statement Common DDL/DML/DCL operation part of the question (i)

Create three empty table Emp1,emp2,emp3, structure reference EMP table

CREATE TABLE EMP1 as SELECT * from EMP WHERE 1=2;

CREATE TABLE EMP2 as SELECT * from EMP WHERE 1=2;

CREATE TABLE EMP3 as SELECT * from EMP WHERE 1=2;

Inserts into the emp1,20 into the emp2,30 insert into the Emp3 with an INSERT statement inserting the department Number 10 into the EMP table

INSERT All

When deptno=10 Then

Into EMP1

When Deptno=20 Then

Into EMP2

When Deptno=30 Then

Into EMP3

SELECT * from EMP;

Create a table t35,x column as any timestamp type, insert the current time, and query

CREATE TABLE T35 (X TIMESTAMP with LOCAL time ZONE);

INSERT into T35 VALUES (sysdate);

SELECT * from T35;

Query the EMP table for all employee information, whose salary exceeds the average wage of their department

SELECT Ename,sal,deptno from EMP outer_table

where Sal> (SELECT AVG (SAL) from EMP inner_table WHERE inner_table. Deptno=outer_table. DEPTNO);

Query for several layers of employee information in EMP that are not leaders

SELECT * from emp outer_table where not EXISTS (select ' X ' from emp inner_table where inner_table. MGR = outer_table. EMPNO);

Find departments with no employees in the Dept table

Select Deptno,dname from DEPT where is not EXISTS (select ' X ' from EMP where EMP. Deptno=dept. DEPTNO);

In HR user authorization Scott can query the permissions of the Employees,departments table

GRANT SELECT on departments to SCOTT;

GRANT SELECT on EMPLOYEES to SCOTT;

The Scott user creates a employees,departments table with the same name and copies the data

CREATE TABLE EMPLOYEES as SELECT * from HR. EMPLOYEES;

CREATE TABLE Departments as SELECT * from HR. Departments;

In the Scott user adds a column to the Employees table, the department name

ALTER TABLE EMPLOYEES ADD (dname VARCHAR2 (20));

The department name of the Employees table is updated by the Scott user based on the department name of the departments table

UPDATE EMPLOYEES SET dname= (SELECT department_name from departments WHERE departments. Department_id=employees. DEPARTMENT_ID);

SELECT * from departments;

SELECT * from EMPLOYEES;

Delete Employee information in location_id 1700 area in Scott user employees

DELETE from EMPLOYEES where department_id in (SELECT department_id from departments where location_id=1700);

SQL statement Common DDL/DML/DCL operation part of the question (i)