Step-by-step explanation of ORACLE (Lecture 2)

Step-by-step explanation of ORACLE (Lecture 2)
1. Common ORACLE Data Types (not commonly used for other types) (1) character type 1. char fixed length, up to 2000 characters. For example, put the first four characters of char (10) 'xiaoming 'and then add 6 spaces to complete. 2. varchar2 (20) is longer, with a maximum length of 4000 characters. For example, varchar (10) 'xiaoming 'oracle allocates four characters, which can save space. 3. clob (character large objiect) large objects can be up to 4 GB (2) numbers. The value ranges from-10 to 38 to 10, which can represent integers, it can also represent decimal places. 1. number (5) indicates a five-digit integer range-99999 to 99999 2. number (5, 2) indicates that a decimal number has a five-digit effective number and two decimal places, range:-999.99 to 999.99 (3) date type 1. data includes year, month, day, and hour, minute, and second. 2. The difference between timestamp and date is that it can not only save the date. Time. It can also save decimal seconds. (4) image type blob binary data can store images/sounds. Ii. Create a table (1) create a student table and a class table use all types

SQL> create table student(  2  xh number(4),  3  xm varchar2(20),  4  sex char(2),  5  birthday date,  6  sal number(7,2)  7  );Table createdSQL> create table class(  2  classid number(2),  3  cname varchar2(20)  4  );Table created

(2) modify a table. 1. Add fields. Add classid to the student table

SQL> alter table student add (classid number(2));Table altered

2. Modify the length of a field. Modify the xm length of the student table to 30

SQL> alter table student modify (xm varchar2(30));Table altered

3. Modify the field type or name (data is not allowed ). Change the xm type of the student table to char (30)

SQL> alter table student modify(xm char(30));Table altered

SQL> alter table student rename column xm to mc;Table altered

4. delete a field. Delete the sal field of the student table.

SQL> alter table student drop column sal;Table altered

5. Modify the table name. Change student to stu.

SQL> rename student to stu;Table renamed

6. delete a table. Delete the stu table.

SQL> drop table stu;Table dropped

III. Basic Table query (using scott user ). 1. view the table structure.

SQL> desc emp;Name     Type         Nullable Default Comments -------- ------------ -------- ------- -------- EMPNO    NUMBER(4)                              ENAME    VARCHAR2(10) Y                         JOB      VARCHAR2(9)  Y                         MGR      NUMBER(4)    Y                         HIREDATE DATE         Y                         SAL      NUMBER(7,2)  Y                         COMM     NUMBER(7,2)  Y                         DEPTNO   NUMBER(2)    Y    

2. query the annual salary of each employee.

SQL> select ename "name", sal * 12 "annual salary" from emp; name year salary ---------- SMITH 21600 ALLEN 19200 WARD 15000 JONES 35700 MARTIN 15000 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 JAMES 11400 FORD 36000 MILLER 1560014 rows selected

Note: Convert null to 0 using nvl

SQL> select ename "name", sal * 12 + nvl (comm, 0) * 12 "annual salary" from emp; name year salary ---------- SMITH 21600 ALLEN 22800 WARD 21000 JONES 35700 MARTIN 31800 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 JAMES 11400 FORD 36000 MILLER 16014.7214 rows selected

3. How to display the name and salary of all employees whose third character is uppercase O.

SQL> select ename,sal from emp where ename like '__O%';ENAME            SAL---------- ---------SCOTT        3000.00

Note: % represents any 0 to multiple characters, and _ represents any single character.
4. The average and highest salaries of each department are displayed.

SQL> select avg (sal) "average salary", max (sal) "highest salary", deptno "department no." from emp group by deptno; average salary highest salary Department No. ---------- -------- 1566.66666 2850 30 2375 3000 202916.66666 10

5. display the average salary and minimum wage for each position in each department.

SQL> select avg (sal) "average salary", min (sal) "minimum wage", deptno, job from emp group by deptno, job order by deptno; average Wage and Minimum Wage deptno job ---------- ------ --------- 1300 1300 2450 10 CLERK 2450 5000 10 MANAGER 5000 3000 10 PRESIDENT 3000 1450 1100 20 ANALYST 2975 2975 20 CLERK 950 950 20 MANAGER 2850 30 CLERK 2850 30 MANAGER 1400 1250 30 SALESMAN

6. The average salary is less than 2000 of the Department number and his average salary.

SQL> select deptno, avg (sal) "average salary" from emp group by deptno having avg (sal) <2000; DEPTNO average salary ------ ---------- 30 1566.66666

7. display all employees in the same department as SMITH.

SQL> select ename from emp where deptno=(select deptno from emp where ename='SMITH');ENAME----------SMITHJONESSCOTTADAMSFORD

8. query the names, positions, salaries, and department numbers of employees with the same work as department 10.

SQL> select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);ENAME      JOB             SAL DEPTNO---------- --------- --------- ------CLARK      MANAGER     2450.00     10BLAKE      MANAGER     2850.00     30JONES      MANAGER     2975.00     20KING       PRESIDENT   5000.00     10MILLER     CLERK       1300.00     10JAMES      CLERK        950.00     30ADAMS      CLERK       1100.00     20SMITH      CLERK       1800.00     20

9. display the name, salary, and department Number of the employee whose salary is higher than that of all employees in the department 30.

Ename sal deptno ---------- --------- ------ JONES 2975.00 20 SCOTT 3000.00 20 KING 5000.00 10 FORD 3000.00 20 or SQL> select ename, sal, deptno from emp where sal> (select max (sal) from emp where deptno = 30); ename sal deptno ---------- --------- ------ JONES 2975.00 20 SCOTT 3000.00 20 KING 5000.00 10 FORD 3000.00 20

10. query all employees in the same department and position as SMITH.

SQL> select ename from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');ENAME----------SMITHADAMS

11. query the information of employees who are higher than the average salary of their own departments.

SQL> select ename, sal, svg "average department salary" from emp, (select deptno, avg (sal) svg from emp group by deptno) t where sal> svg and emp. deptno = t. deptno; ename sal Department average salary ---------- ------------------ ALLEN 1600.00 1566.6666666 JONES 2975.00 2375 blke 2850.00 SCOTT 1566.6666666 3000.00 KING 2375 FORD 5000.00 2916.6666666 rows selected

12. Change scott's position, salary, and subsidy, just like smith's.

SQL> update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';1 row updated

13. oracle paging.

SQL> select * from emp where empno in(select empno from (select empno,rownum rn from (select empno from emp order by sal desc) where rownum<=10) t where t.rn>=6);EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10 7844 TURNER     SALESMAN   7698 1981-09-08    1500.00      0.00     30 7934 MILLER     CLERK      7782 1982-01-23    1300.00     34.56     10 7499 ALLEN      SALESMAN   7698 1981-02-20    1600.00    300.00     30 7369 SMITH      CLERK      7902 1980-12-17    1800.00               20

14. Create a new table using the query results

SQL> create table emp1(id,name) as select empno,ename from emp;Table createdExecuted in 0.532 secondsSQL> select * from emp1;   ID NAME----- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER14 rows selected

15. Merge queries. (Union, union all, intersect, minus) (1) union takes the union of two result sets and automatically removes duplicate rows in the result set.

SQL> select empno id,ename name from emp  2  union  3  select id,name from emp1;   ID NAME----- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER14 rows selected

(2) union all takes the union of two result sets and does not remove duplicate rows from the result set.

SQL>  select empno id,ename name from emp  2   union all  3   select id,name from emp1  4  ;   ID NAME----- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE   ID NAME----- ---------- 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER28 rows selected

(3) intersect obtains the intersection of two results.

SQL>  select ename,sal from emp where sal>2500  2    intersect  3    select ename,sal from emp where job='MANAGER';ENAME            SAL---------- ---------BLAKE        2850.00JONES        2975.00

(4) minus subtract two results.

SQL> select ename,sal from emp where sal>2500  2    minus  3    select ename,sal from emp where job='MANAGER'  4  ;ENAME            SAL---------- ---------FORD         3000.00KING         5000.00SCOTT        3000.00