2018.5.28 Oracle Database Supplement

SELECT * FROM (select RowNum rn,e2.* from (select e1.* from EMP E1) E2 where rownum<=10) E3 where e3.rn>=6;

SELECT * FROM (select RowNum en,e.* from emp e)

Select E.deptno,e.ename,d.dname,e.mgr,s.grade
From (select AVG (SAL) ea from emp) e1,emp E,salgrade s,dept D
where E.deptno=e.mgr
and D.deptno=e.deptno
and e.sal>e1.ea;

--Copy table data
CREATE TABLE Emps as SELECT * from EMP;

--View Table contents
DESC EMP;
Select table_name from all_tables where Owner=upper (' Legend ');

--Copy table structure
CREATE TABLE Empnew as SELECT * from EMP where 1<>1;

--Example: creating a student table
The---fields are as follows:
--School Number
--Name
--Gender
--Date of birth
--Scholarships
CREATE TABLE Student (
Sno Number (4),
sname varchar (20),
Sex varchar (2) CHECK (Sex in (' Male ', ' female ')),
Birthday date,
Scholarship Number (15)
);

--Example: Creating a Class Table
--Fields are as follows
--Class number
--Class name
CREATE TABLE Class (
Classno Number (3),
ClassName varchar (5)
);

--Expand new Field (column) to increase and revise the search
--Add Class table one field class teacher
ALTER TABLE class Add (Classteacher char (12));

--Modify Fields
ALTER TABLE class Modify (Classteacher varchar2 (10));

--delete field (delete class teacher field)
ALTER TABLE class drop column classteacher;

--Inserting data
Insert into table name (Field 1, Field 2) VALUES (value 1, value 2)

2018.5.28 Oracle Database Supplement