Oracle Walkthrough (second lecture)

I. Oracle Common data types (other types are less common)(1) Character type1, char fixed length, maximum 2000 characters. For example: char (10) ' Xiao Ming ' first four words descriptors ' Xiao Ming ' after adding 6 blanks to complete. 2, VARCHAR2 (20) variable length, maximum 4000 characters. For example: varchar (10) ' Xiaoming ' Oracle allocates four characters, thus saving space. 3, CLOB (character large objiect) character type large object Max 4G(2) Number typenumber Range-10 of the 38-square to 10 38-square, can represent integers, or can represent decimals. 1, Number (5) represents a five-bit integer range-99999 to 999992, Number (5,2) indicates that a decimal number has 5-bit effects, 2 decimal places, a range of 999.99 to 999.99 (3) Date type1. Data contains date and time of day and seconds. 2, timestamp the difference between it and date is not only the date can be saved. Time, and can save fractional seconds. (4) Picture typeblob binary data can store images/sounds. Ii. Establishment of the table(1) Create Student and class tables with all types

Sql> CREATE TABLE Student (  2  xh number (4),  3  xm varchar2,  4  sex char (2),  5  Birthday date,  6  sal number (7,2)  7  ); Table created

Sql> CREATE TABLE Class (  2  classid Number (2),  3  CNAME varchar2 (4)  ); Table created

(2) Table modification.1. Add a field. Add ClassID to student tables

Sql> ALTER TABLE student Add (ClassID number (2)); Table Altered

2. Modify the length of the field. Modify Student Table XM length is 30

sql> ALTER TABLE student Modify (XM VARCHAR2 (30)); Table Altered

3. Modify the type or name of the field (cannot have data). Modify Student table XM type is 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 Student table Sal field.

sql> ALTER TABLE student drop column Sal; Table Altered

5. Modify the name of the table. Modify student to Stu.

Sql> rename student to Stu; Table renamed

6, delete the table. Delete the Stu table.

sql> drop table Stu; Table dropped

Third, table basic query (using Scott users). 1. View the table structure.

sql> desc EMP; Name     Type         Nullable Default Comments-------------------------------------------EMPNO number    (4)                              Ename    VARCHAR2 (Ten) 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. Check the annual salary of each employee.

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

NOTE: Convert null to 0 with NVL

Sql> Select ename "Name", SAL*12+NVL (comm,0) *12 "annual wage" from EMP; name              Annual salary--------------------SMITH           21600ALLEN           22800WARD            21000JONES           35700MARTIN          31800BLAKE           34200CLARK           29400SCOTT           36000KING            60000TURNER          18000ADAMS           13200JAMES           11400FORD            36000MILLER       16014.7214 rows selected

3. How to display the names and wages of all employees whose third character is Capital O.

Sql> Select Ename,sal from emp where ename like ' __o% '; ename            SAL-------------------SCOTT        3000.00

Note:% represents any 0 to more characters, and _ represents any single character.
4. Show the average salary and maximum wage for each department.

Sql> Select AVG (sal) "average wage", Max (SAL) "maximum wage", Deptno "department number" from the EMP Group by DEPTNO; Highest Wage department number----------------------------1566.66666 2850 30 2375 3000 202916.666 66 5000 10

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

Sql> Select AVG (sal) "average wage", min (sal) "minimum wage", deptno,job from EMP Group by deptno,job Order by Deptno;      Wage       minimum wage DEPTNO JOB-----------------------------------      1300       1300     Clerk      2450       2450     Ten MANAGER      ,     President,      1450       1100     20 Clerk      2975       2975     manager       950        950-     clerk      2850       2850      1400       1250     SALESMAN9 rows selected

6. Show the department number of the average salary below 2000 and his average salary.

Sql> Select Deptno,avg (sal) "average wage" from the EMP Group by DEPTNO have avg (SAL) <2000;deptno       Average salary---------------- C31/>30 1566.66666

7. Show 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. The name, position, salary, department number of the employee who has the same job as the 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     208 rows selected

9. The employee's name, salary and department number are displayed with a salary higher than 30 of all employees in the department.

Sql> Select Ename,sal,deptno from emp where Sal>all (select Sal from EMP where deptno=30); ename            SAL DEPTNO-------------------------JONES        2975.00     20SCOTT        3000.00     20KING         5000.00     10FORD         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     20SCOTT        3000.00     20KING         5000.00     10FORD         3000.00     20

10. Query all employees who are exactly the same as the Smith Department and the position

Sql> select ename from emp where (deptno,job) = (select Deptno,job from emp where ename= ' SMITH '); Ename----------Smithadams

11. Query employee information that is higher than the average salary of their department.

Sql> Select Ename,sal,svg "Departmental average salary" from EMP, (select Deptno,avg (SAL) SVG from EMP Group by Deptno) T where sal>svg an D Emp.deptno=t.deptno; ename            SAL       Department average salary-------------------------------ALLEN        1600.00 1566.6666666JONES        2975.00         2375BLAKE        2850.00 1566.6666666SCOTT        3000.00         2375KING         5000.00 2916.6666666FORD         3000.00         23756 rows selected

12. Change employee Scott's position, salary, and benefits as Smith's employees.

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 SA L 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               7844 TURNER     salesman   7698 1981-09-08    1500.00      0.00     7934 MILLER     clerk      7782 1982-01-23    1300.00     34.56     Ten 7499 ALLEN      Salesman   7698 1981-02-20    1600.00    300.00 The     7369 SMITH      clerk      7902 1980-12-17    1800.00               20

14. Create a new table with 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, combined query. (Union, UNION All, intersect, minus)(1) A union of two result sets will automatically remove duplicate rows from 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 two sets of unions 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<  C8/>id NAME---------------7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER28 rows Selected

(3) Intersect 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 to subtract two results.

Sql> Select Ename,sal from emp where sal>2500  2    minus  3    select Ename,sal from emp where job= ' MANAGE R '  4  ; ename            SAL-------------------FORD         3000.00KING         5000.00SCOTT        3000.00

Oracle Walkthrough (second lecture)