Basic SQL operations in Oracle (2)

---------------------------- Create a table and insert data
-- Create a job table
Create table position (
POSITIONID VARCHAR2 (2 ),
POSITIONNAME VARCHAR2 (20 ),
CONSTRAINTS POS_ID_PK primary key (POSITIONID)
);
-- Create a payroll table
Create table emplevel (
LEVELNO VARCHAR2 (2 ),
Lowsalary number (7, 2 ),
Highsalary number (7,2 ),
CONSTRAINTS EMP_NO_PK primary key (LEVELNO)
);
-- Create a degree table
Create table quafication (
Qualid char (1 ),
QUALNAME VARCHAR2 (10 ),
CONSTRAINTS QUA_ID_PK primary key (QUALID)
);

-- Create a department table
Create table department (
DEPTID VARCHAR2 (2 ),
DEPTNAME VARCHAR2 (30 ),
LOCATION VARCHAR2 (30 ),
MANAGERID VARCHAR2 (4 ),
CONSTRAINTS DEP_ID_PK primary key (DEPTID)
);

-- Create employee table
Create table employee (
EMPLOYEEID VARCHAR2 (4 ),
EMPLOYEENAME VARCHAR2 (40) not null,
Hiredate date,
Salary number (7, 2 ),
Commission number (7,2 ),
MANAGERID VARCHAR2 (4 ),
DEPTID VARCHAR2 (2 ),
POSITIONID VARCHAR2 (2 ),
Qualid char (1 ),
CONSTRAINTS EMP_ID_PK primary key (EMPLOYEEID ),
CONSTRAINTS EMP_DEPTID_FK foreign key (DEPTID) references department (DEPTID ),
CONSTRAINTS EMP_PID_FK foreign key (POSITIONID) references position (POSITIONID ),
CONSTRAINTS EMP_QUALID_FK foreign key (QUALID) references quafication (QUALID)
);
--------- Add the EMPLOYEE table foreign key, refer to the original table
ALTER TABLE EMPLOYEE
Add constraints EMP_MID_FK foreign key (MANAGERID) references employee (EMPLOYEEID );

--------- Add the DEPARTMENT Foreign key. For details, refer to the EMPLOYEE table.
ALTER TABLE DEPARTMENT
Add constraints DEP_MAN_FK foreign key (MANAGERID) references employee (EMPLOYEEID );
------ INSERT POSITION
INSERT into POSITION (POSITIONID, POSITIONNAME) values ('01', 'Leader ');
INSERT into position values ('02', 'manager ');
INSERT into position values ('03', 'directory ');
INSERT into position values ('04 ', 'ceo ');
--- Select * from POSITION;
----- INSERT EMPLEVEL
Insert into emplevel values ('0', 1000,2000 );
Insert into emplevel values ('1', 2000,3000 );
---- Select * from EMPLEVEL;
--- INSERT QUAFICATION
Insert into quafication values ('1', 'bachelor ');
Insert into quafication values ('2', 'shid ');
------ Select * from QUAFICATION;
----- INSERT DEPARTMENT
Insert into department values ('01', 'commercialization DEPARTMENT ', 'Team lead office', NULL );
Insert into department values ('02 ', 'management authorization', 'manager office', NULL );
---- Select * from DEPARTMENT;
---- INSERT EMPLOYEE
Insert into employee values (01, 'small Zhang ', TO_DATE ('2017-09-01', 'yyyy-MM-DD '), 2009, NULL, NULL );
---- Same as the following record, EMPLOYEEID, SALARY
Insert into employee values (02, 'Lee ', TO_DATE ('2017-09-02', 'yyyy-MM-DD '), 2009, NULL );
----- Same as the above record
Insert into employee values (03, 'Lee ', TO_DATE ('2017-09-02', 'yyyy-MM-DD '), 2009, NULL );
Insert into employee values (04, 'dayong ', TO_DATE ('2017-09-03', 'yyyy-MM-DD '), 2009, NULL );
Insert into employee values (05, 'dayong ', TO_DATE ('2017-09-03', 'yyyy-MM-DD '), null, 2009, NULL );
Insert into employee values (06, 'xiaogang ', null, 2100,500, NULL );
Insert into employee values (07, 'hua ', null, 2100,500, NULL );
--- Select * from EMPLOYEE;
-- Update EMPLOYEE
Update employee set commission = 300;
Update employee set commission = 200 where employeeid = 1;
Update employee set commission = 400 where employeeid = 3;
Update employee set commission = 500 where employeeid = 4;
Update employee set commission = 200 where employeeid = 2;
Update employee set employeename = 'chubby 'where employeeid = 4;
Update employee set employeename = 'zhang Xiaohua 'where employeeid = 1;
Update employee set employeename = 'Li Xiaohua 'where employeeid = 2;
Update employee set employeename = 'size Huahua 'where employeeid = 3;
Update employee set commission = 1000 where employeeid = 6;
--- DELETE POSITION
Delete position;
Delete position where positionname = 'Leader ';
Delete position where positionid = '02 ';
--- Select * from POSITION;
-- SELECT EMPLOYEE
SELECT * from employee;
Select emp. EMPLOYEEID, EMP. employeename from employee emp;
Select emp. employeeid id, EMP. EMPLOYEENAME name from employee emp;
Select emp. employeeid id, EMP. EMPLOYEENAME name from employee emp where emp. SALARY = 1500;

--- DISTINCT
Select distinct emp. EMPLOYEENAME, EMP. salary from employee emp;

---- | Series
Select emp. EMPLOYEENAME | 'monthly SALARY is' | EMP. salary info from employee emp;
--- Arithmetic expression
Select emp. EMPLOYEEID, EMP. SALARY * 12 annual salary from employee emp;
 
--- Select * from EMPLOYEE;
--- Where clause>
Select emp. EMPLOYEEID, EMP. salary from employee emp where emp. SALARY> 1600;
--- BETWTEEIN AND
Select emp. EMPLOYEEID, EMP. salary from employee emp where emp. salary between 1500 AND 1660;
--- IN
Select emp. EMPLOYEENAME, EMP. salary from employee emp where emp. salary in (1399,1600 );
--- NOT IN
Select emp. EMPLOYEENAME, EMP. salary from employee emp where salary not in );
--- IS NULL
Select emp. EMPLOYEENAME, EMP. salary from employee emp where emp. salary is null;
----- IS NOT NULL
Select emp. EMPLOYEENAME, EMP. hiredate from employee emp where emp. hiredate is not null;
--- LIKE
Select emp. employeename from employee emp where emp. employeename like 'small % ';
Select emp. employeename from employee emp where emp. employeename like '% ';
Select emp. employeename from employee emp where emp. employeename like '_ small % ';
Select emp. employeename from employee emp where emp. employeename like '_ small _';
--- NOT LIKE
Select emp. employeename from employee emp where emp. employeename not like 'Big % ';
--- AND
Select emp. EMPLOYEENAME, EMP. salary from employee emp where emp. SALARY> 1600 and emp. COMMISSION> 900;
--- ORDER
Select emp. EMPLOYEEID, EMP. EMPLOYEENAME, EMP. COMMISSION FROM EMPLOYEE EMP
Order by emp. COMMISSION;
Select emp. EMPLOYEEID, EMP. EMPLOYEENAME, EMP. SALARY, EMP. COMMISSION FROM EMPLOYEE EMP
Order by emp. SALARY, EMP. commission desc;
 
This article is from the "Enthusiasm 10 years" blog