Practice One:
"Construct implementation environment----execute under any user"
DROP TABLE DEPT;
CREATE TABLE DEPT (DEPTNO number (2) CONSTRAINT pk_dept PRIMARY KEY, Dname VARCHAR2, LOC VARCHAR2 (13)); DROP TABLE EMP;
CREATE TABLE EMP (EMPNO number (4) CONSTRAINT pk_emp PRIMARY key, ename VARCHAR2 (Ten), job VARCHAR2 (9), mgr Number (4), hiredate date, sal number (7,2), comm number (7,2), deptno number (2) CONSTRAINT Fk_ DEPTNO REFERENCES dept ); insert into DEPT values (' ACCOUNTING ', ' NEW YORK '); INSERT into DEPT values (' RES Earch ', ' DALLAS '); insert into DEPT values ("SALES", ' CHICAGO '), insert into DEPT values (+, ' OPERATIONS ', ' BOSTON '); Nsert into EMP VALUES (7369, ' SMITH ', ' Clerk ', 7902,to_date (' 17-12-1980 ', ' dd-mm-yyyy '), 800,null,20); INSERT into EMP VALUES (7499, ' ALLEN ', ' salesman ', 7698,to_date (' 20-2-1981 ', ' dd-mm-yyyy '), 1600,300,30); INSERT into EMP VALUES (7521, ' WARD ', ' salesman ', 7698,to_date (' 22-2-1981 ', ' dd-mm-yyyy '), 1250,500,30); INSERT into EMP VALUES (7566, ' JONES ', ' MANAGER ', 7839,to_date (' 2-4-1981 ', ' dd-mm-yyyy '), 2975,null,20); INSERT into EMP VALUES (7654, ' MARTIN ', ' salesman ', 7698,to_ Date (' 28-9-1981 ', ' dd-mm-yyyy '), 1250,1400,30), INSERT into EMP VALUES (7698, ' BLAKE ', ' MANager ', 7839,to_date (' 1-5-1981 ', ' dd-mm-yyyy '), 2850,null,30); INSERT into EMP VALUES (7782, ' CLARK ', ' MANAGER ', 7839,to_ Date (' 9-6-1981 ', ' dd-mm-yyyy '), 2450,null,10); INSERT into EMP VALUES (7788, ' SCOTT ', ' ANALYST ', 7566,to_date (' 13-jul-87 ') -85,3000,null,20); INSERT into EMP VALUES (7839, ' KING ', ' president ', null,to_date (' 17-11-1981 ', ' dd-mm-yyyy '), 5000, null,10); insert into EMP VALUES (7844, ' TURNER ', ' salesman ', 7698,to_date (' 8-9-1981 ', ' dd-mm-yyyy '), 1500,0,30); insert into EMP values (7876, ' ADAMS ', ' Clerk ', 7788,to_date (' 13-jul-87 ') -51,1100,null,20); INSERT into EMP values (7900, ' JAMES ' , ' Clerk ', 7698,to_date (' 3-12-1981 ', ' dd-mm-yyyy '), 950,null,30); INSERT into EMP VALUES (7902, ' FORD ', ' ANALYST ', 7566,to _date (' 3-12-1981 ', ' dd-mm-yyyy '), 3000,null,20); INSERT into EMP VALUES (7934, ' MILLER ', ' Clerk ', 7782,to_date (' 23-1-1982 ', ' dd-mm-yyyy '), 1300,null,10);
DROP TABLE BONUS;
CREATE TABLE BONUS (ename VARCHAR2 (Ten), JOB VARCHAR2 (9), SAL number, COMM number); DROP TABLE Salgrade;
CREATE TABLE Salgrade (GRADE number, losal number, hisal number); INSERT into Salgrade values (1,700,1200), insert into Salgrade values (2,1201,1400), insert into salgrade values (3,1401,200 0) insert into Salgrade values (4,2001,3000), insert into Salgrade values (5,3001,9999); COMMIT;
"Scott User Content Word notes" The EMP Employee table field reads as follows: EMPNO employee number ename employee Name Job Job Mgr Superior number hiredate Employment Date Sal Salary Comm Commission DEPTNO Department number
Dept Department table field contents are as follows: Deptno Department number ename department name Loc Place
The Bonus Bonus table field reads as follows: ENAME employee name Job Job name Sal Salary Comm Commission
"Scott user Action"
--1. Select all employees in Department 30. SELECT * from EMP where deptno=30;
--2. Lists the name, number, and department number of all clerks (Clerk). Select Ename,empno,deptno from emp where job= ' clerk '
--3. Find employees with higher commissions than salaries. Select Ename,empno,comm,sal from emp where comm>sal;
--4. Find 60% of employees with commissions above their salaries. Select Empno,ename,comm,sal from emp where comm>sal*0.6;
--5. Find out the details of all the managers in department 10 and all the Clerks (clerk) in department 20. SELECT * from emp where deptno=10 and job= ' manager ' or (DEPTNO=20 and job = ' clerk ');
--6. Find out the details of all employees who are neither a manager nor a clerk but whose salary is greater than or equal to 2000. SELECT * from emp where job isn't in (' MANAGER ', ' Clerk ') and sal>=2000; --7. Find out the different work of the employee who receives the commission. SELECT DISTINCT JOB from EMP WHERE COMM are not NULL;
--8. Find employees who do not charge a commission or receive a commission less than 100. SELECT * from EMP WHERE comm<100 OR COMM is NULL;
--9. Find all employees employed on the 3rd day of the month. SELECT * from EMP WHERE hiredate =last_day (HireDate)-2;
--10. Identify employees who were employed before 12.
SELECT ename from EMP WHERE hiredate <add_months (sysdate,-12*12);
SELECT ename from EMP WHERE add_months (sysdate,12*12) > sysdate;
--11. Displays the names of all employees in uppercase letters. Select INITCAP (ename) from EMP;
--12. Displays the name of the employee who is exactly 5 characters. Select ename from emp where ename like ' _____ '; Wildcard characters% 0 or more wildcard characters _ 1 wildcards [1-9] represent 1-9 of the number [^1-9] with a number of 1-9 starts with a SELECT ename from EMP WHERE ename like ' _____ ';
SELECT ename from EMP WHERE LENGTH (ename) = 5;
--13. Displays the names of employees who do not have "R". Select ename from emp where ename is not a like '%r% ';
--14. Displays the first three characters of all employee names. SELECT SUBSTR (ename,1,3) from EMP;
--15. Displays the names of all employees, replacing all "a" select Replace with a (ename, ' a ', ' a ') from EMP;
--16. Displays the name and date of employment of the employee who has been in service for 10 years (add_months system function). SELECT ename,hiredate from EMP WHERE hiredate < add_months (sysdate,-10*12);
--17. Show employee details, sorted by name ASC default, Ascending | Desc Descending "
SELECT * from EMP ORDER by ename;
--18. Displays the employee's name and the date of employment, according to their service life, the oldest employees at the front. SELECT ename,hiredate from EMP ORDER by HireDate ASC;
--19. Displays the names, jobs, and salaries of all employees, sorted by work in descending order, and by salary if the work is the same. SELECT * from EMP ORDER by JOB desc,sal DESC;
--20. Displays the names of all employees, the year and month in which the company was joined, sorted by the month of the hire date, and, if the month is the same, the employee of the earliest year is ranked first. SELECT Ename,to_char (hiredate, ' YYYY '), To_char (hiredate, ' mm ') from the EMP ORDER by To_char (hiredate, ' mm ') Asc,to_char ( HireDate, ' YYYY ') ASC;
--21. Shows the daily salary of all employees at one months for 30 days, ignoring the remainder. SELECT Floor (SAL/30) from EMP; SELECT TRUNC (SAL/30) from EMP;
--22. Identify all employees employed in February (in any year). SELECT * from EMP WHERE to_char (hiredate, ' MM ') = ' 02 '; --23. For each employee, the number of days that it was added to the company (Trunc, not rounded, full). SELECT ' days to join the company: ' | | Trunc (sysdate-hiredate) from EMP;
--24. Displays the names of all employees that contain "A" anywhere in the Name field. SELECT ename from EMP WHERE ename like '%a% ';
--25. For each employee, how many years has it shown that he has joined the company?? How many months??? SELECT ' number of years to join the company: ' | | Trunc ((sysdate-hiredate)/365), ' Number of months to join the company: ' | | Trunc ((sysdate-hiredate)/30) from EMP;
(i), 1, common year Otsuki 31 days SELECT ..... 2, common year Xiao Yue 30 days SELECT ..... 2, Common year 28 days SELECT ..... 1, leap year Otsuki 31 days SELECT .... 2, Leap year Xiao Yue 30 days SELECT ..... 2, Leap year February 28 days SELECT .....
(b), SELECT ' number of years to join the company: ' | | Trunc ((sysdate-hiredate)/365), ' Number of months to join the company: ' | | Trunc ((sysdate-hiredate)/30) from EMP; "I wrote the common year, and prescribed 30 days a month, a leap year situation to consider, just did not write"--------------------------------------------------------------------------- ----------------------------------------------------------------------------Exercise Two:
"Function Exercise"
--1, Example: 2-4-bit select substr (ename,2,3) that extracts the name of Employee No. No. 7654, ename from EMP where empno=7654;
Use of the--2,round function "rounding" select ROUND (2.3), ROUND (2.6) from DUAL;
--3,to_char--The SAL column select To_char (sal, ' $99,999,999 ') in currency form, To_char (Sal, ' l99,999,999 '), To_char (Sal, ' l00,000,000 ') From EMP;
--Display current system time select To_char (sysdate, ' yyyy-mm-dd HH:MI:SS '), To_char (sysdate, ' YYYY ' "MM" month "DD" Day ") from DUAL;
--4,to_date function--Find employees after 1981-4-01: SELECT * from EMP WHERE hiredate > To_date (' 1981-4-01 ', ' yyyy-mm-dd ');
--5,NVL function Select NVL (comm,1111), COMM from EMP;
--8. Identify employees who were employed before 27. SELECT * from EMP WHERE floor (Floor (Months_between (sysdate,hiredate))/12) >27; SELECT ename,hiredate from EMP WHERE hiredate < add_months (sysdate,-27*12);
--9. Displays the names of all employees in an uppercase letter. SELECT Initcap (ename) from EMP;
--10. Displays the name of the employee who is exactly 5 characters. SELECT ename from EMP WHERE LENGTH (ename) = 5;
--11. Displays the names of employees without "T". SELECT ename from EMP WHERE ename does like '%t% ';
--12. Displays the names of all employees, replacing all "a" select Replace with a (ename, ' a ', ' a ') from EMP;
--13. Displays the name and date of employment of the employee who has been in service for 10 years. SELECT * from EMP WHERE floor (Floor (Months_between (sysdate,hiredate))/12) >10;
--14. Displays the names of all employees, the year and month of incorporation, select To_char (hiredate, ' yyyy-mm ') from EMP;
--15. For each employee, show the number of days that they joined the company. SELECT TRUNC (sysdate-hiredate) from EMP;
--16 decode () function: Displays employee number, name, monthly salary and salary level: Requires 800= beginner 1600 = Intermediate 3000= premium remaining monthly salary show other SELECT decode (sal,800, ' beginner ', ' other ') from Emp;--select DECODE (sal,800, ' Beginner ', DECODE (Sal, 1600, ' intermediate ', DECODE (SAL, 3000, ' advanced ', ' Other '))) from EMP;
SELECT DECODE (sal,800, ' beginner ', 1600, ' intermediate ', 3000, ' advanced ', ' other ') from EMP;
--17sign function: Show employee number, name, monthly salary and salary level: 1----999 Junior----2999 Intermediate----3999 Advanced 4000----Super
Action: SELECT sign (+), sign ( -1000), sign (0) from dual; Result: sign (+ -1000) sign (0)------------------------------- 1-1 0
SELECT DECODE (sign (SAL-1000),-1, ' Beginner ', DECODE (sign (SAL-3000),-1, ' intermediate ', DECODE (sign (SAL-4000),-1, ' Advanced ', ' super '))) from EMP ;
"Aggregate functions, grouping, string connections"
(1) | | : String Connection Select ' Number: ' | | empno| | ', Name: ' | | ename condition from EMP;
(2) Count: count (not NULL) a. Number of statistical recordsSelect COUNT (*) from EMP; B. Statistics comm The number of records for which the field is not emptySelect COUNT (comm) from EMP;
(3) Group by ... having .... Grouping filter
Once the group by IS involved, remember two points:1,select can only be followed by grouping columns and aggregate functions2,where before group BY, the order can not change, where the filter is a record, having the filter is grouped after the record.Student table (name, score, age, gender) create table student (name VARCHAR2, score number,age number,sex varchar2); INSERT INTO student VALUES (' Zhangsan ', 100,20, ' nan '), insert into student values (' Lisi ', 10,30, ' nan '), insert into student values (' Wangwu ', 0,40, ' NV '); INSERT into student values (' Xiaoming ', 90,10, ' Nan '); INSERT into student values (' Laowang ', 100,50, ' Nan '); INSERT into student values (' Xiaohong ', 100,60, ' NV '), insert into student values (' Shuangshuang ', 100,80, ' NV '), insert INTO Student values (' Ribenren ', 100,80, ' Weizhi '); commit;
--Example: Group of students who pass the class by sex, find the average of 80 or more groups Select Sex,count (*) from the student where score>=60 group by sex have avg (score) >8 0; To count the maximum wage for each department select Deptno,max (SAL) from the EMP group by DEPTNO; Statistics the maximum wage for each department, showing the department with the highest wage greater than 2900 select Max (SAL), Deptno from EMP Group by DEPTNO have Max (SAL) >2900;
The highest wage in the Department of all employees with a salary of more than $2900, showing the department with the highest wage greater than 4000 select Deptno,max (SAL) from EMP where sal>2900 GROUP by DEPTNO have Max (SAL) >4000;
Displays the highest wage in all employees select Max (SAL) from EMP; Displays the maximum wage for different jobs select Max (SAL), job from EMP Group by job; Displays the average wage for different departments and retains two decimal places select Deptno,round (avg (SAL), 2) from the EMP group by DEPTNO;
Show how many jobs are in total, remove duplicate values select distinct job from EMP;
Exercise Three:
--Question: When do I use a connection query? --Use a connection query when the data that needs to be displayed is from more than one table.
1. Check the name of the employee and the Department (2 ways)
--equivalent connection select Emp.ename,dept.dname from EMP, dept where Emp.deptno=dept.deptno;
--INNER JOIN inner join on select A.ename,b.dname from EMP a INNER JOIN dept B on A.deptno=b.deptno; 2, the name of the employee's name and department is queried, the department number is 30 (2) Select A.ename,b.dname from emp a INNER JOIN dept B on A.deptno=b.deptno where a.deptno=30; Select A.ename,b.dname from emp a,dept b where A.deptno=b.deptno and a.deptno=30;
3, query the name of the employee and its direct superiors, ask the staff without the manager to inquire (3 kinds of wording)
The EMP Employee table field reads as follows:Empno Employee NumberENAME Employee NameJob JobsMgr Superior numberHireDate Date of employmentSal SalaryComm CommissionDEPTNO Department Number
For example: "SMITH" is superior to "ORD" empno:7369 ename:smith mgr:7902empno:7902 Ename:ford
Select ' Employees ' | | a.ename| | ' The superior is ' | | B.ename from EMP A to outer join EMP B on a.mgr=b.empno;select ' Employees ' | | a.ename| | ' The superior is ' | | B.ename from emp a,emp b where a.mgr=b.empno (+) Select ' Employees ' | | a.ename| | ' The superior is ' | | B.ename from emp a,emp b where a.mgr=b.empno;
--1, query all employees who pay more than SMITH pay select * from emp where sal> (select Sal from emp where Ename= ' Smith ');
--2, query all employees who pay more than the average company's salary select * from emp where sal> (select AVG (SAL) from EMP);
--Additional questions-->= < <= =! = <> ^= followed by only one value,
--3, query all employees with a salary higher than the average wage for this department select * from EMP a WHERE a.sal> (select AVG (SAL) from EMP b where b.deptno=a.deptno);
--4, check the employees of the department's highest salary (2 methods) method 1, using nested subqueries (non-associative subqueries) SELECT * from EMP a WHERE (a.deptno,a.sal) in (select Deptno,max (Sal) FR Om EMP Group by DEPTNO); Method 2, using the associated subquery, select * from the EMP A where a.sal= (select Max (SAL) from EMP where Deptno=a.deptno);
--5, count the information and number of each department select A.*, (select COUNT (*) from EMP where Deptno=a.deptno) The number of people from dept A;
Select A.deptno,b.dname,b.loc,count (*) number from emp a,dept b where A.deptno=b.deptno group by A.deptno,b.dname,b.loc; --6, statistics on the number of salaries per department in (500-1000), (1000-3500), (3500-7000) Select A.*, (select COUNT (*) from EMP b where b.deptno=a.dept No and sal>500 and sal<=1000) "Wage range: 500-1000 people", (select COUNT (*) from EMP b where B.deptno=a.deptno and sal>100 0 and sal<=3500) "Wage range: 1000-3500 people", (select COUNT (*) from EMP b where B.deptno=a.deptno and sal>3500 and sal<=7 000) "Salary range: 3500-7000 people" from dept A;
From for notes (Wiz)
Day03_scott User Practice
