Oracle operation statement Learning

Oracle operation statement Learning 1. oracle operation 1. connect to oracle: 1) sqlplus 2) scott 3) tiger] 2. sqlplus scott/tiger; 3. 4. sqlplus scott tiger 5. interface Logon: 5. exit quit Exit 2. query 1. select empno, ENAME, JOB, sal from emp where sal <2500; 2. select epno, ENAME, JOB, sal from emp where sal <2500; 3. CHANGE/EPNO/EMPNO Run 4. add a row 5 to delete a row 6. SAVE the SAVE command and run the command file. buffer clearance 8. DESCRIBE: list the table structure 2. oracle query 1. show all Department numbers, employee names, and administrator numbers in the EMP table. 1. arithmetic Operators in SQL Use select ename, SAL + 250*12 from emp; 2. the use of the hyphen connects the employee id with the EMPLOYEE name, as follows: select empno | ename employee from emp; connects the employee id with '-' in the middle of the EMPLOYEE name, and output 'Works in department ', as follows: select empno |'-'| ename employee, 'Works in department', deptno from emp; 3. prohibit duplication if you list all Department numbers in the EMP table: Select deptno from emp; from the table above, we can see that there are many identical Department numbers, and you can use the DISTINCT clause to eliminate duplication. Select distinct deptno from emp; 4. sort BY a single field, such as by ename, select ename, JOB, SAL * 12, deptno from emp order by ename; 5. sort BY multiple fields: for example, select deptno, JOB, ename from emp order by deptno, sal desc in descending ORDER of Department numbers, and sort BY multiple fields: for example, select deptno, JOB, ename from emp order by deptno, sal desc; 6. query with conditions 1) query the names of all employees of CLERK, employee number and department number select ename, EMPNO, JOB, deptno from emp where job = 'cler'; 2) from DE In the PT table, select dname and deptno from dept where deptno> 20 for the department with a department number greater than 20 are queried. 3) in the composite condition query, the query job is MANAGER and the salary is greater than 1500, OR the employee information of the JOB is SALESMAN: select empno, ENAME, JOB, SAL, deptno from emp where sal> 1500 and job = 'manager' or job = 'salesman'; 5. 1) The BETWEEN application queries the employee name and salary information BETWEEN 1000 and 2000. Select ename, sal from emp where sal between 1000 AND 2000; 2) All employees of one of the three MGR numbers IN the IN query: select empno, ENAME, SAL, mgr from emp where mgr in (7902,7566, 7788); 3) LIKE wildcard % represents any 0 or multiple characters. Wildcard _ represents any single character. Query all employees whose names start with "S": select ename from emp where ename like's % '; query all employees whose names are only 4 characters long: select ename from emp where ename like '_'; 4) is null queries all employees without managers: select ename, mgr from emp where mgr is null; 6. single & # Replace variable 1) numeric variable input: select empno, ENAME, sal from emp where deptno = & DEPARTMENT_NUMBER; Enter value for department_number: 10 2) string variable input: select empno, ENAME, SAL * 12 from emp where job = '& JOB_TITLE'; Enter value for job_title: MANAGER 7. character Function Application TO_CHAR numeric data conversion to string SQL> select to_char (8897) from dual; 8. the use of the numeric function TO_NUMBER string data is converted to a numeric SQL> select to_number ('000000') from dual; 9. data type conversion TO_DATE string data conversion to date data SQL> select to_date ('03-January 1, December-81 ') from dual; 10. grouping Function Application 1) Grouping function see textbook P60 2) Grouping Function Application average value select avg (SAL) from emp; minimum value select min (SAL) from emp where job = 'cler'; returns the number select count (*) from emp where deptno = 20; 3) the group by clause calculates the average salary of each department: select job, AVG (SAL) from emp group by job; 4) The HAVING clause queries the average salary of a department with more than three persons: select deptno, AVG (SAL) from emp group by deptno having count (*)> 3; 11. the connection queries the employee name, JOB and department name from emp and DEPT: select ename, JOB, dname from emp, dept where emp. DEPTNO = DEPT. DEPTNO; 12. the subquery application queries the employees with the lowest wage from emp: select ename, JOB, sal from emp where sal = (select min (SAL) from emp ); find the employees with the lowest salaries IN each department from emp: select ename, SAL, deptno from emp where sal in (select min (SAL) from emp group by deptno );