Getting Started with Oracle

--Create User
Create user QJT identified by 1234

--Permissions
Grant Connect to QJT

Grant resource to QJT

--Build a table
CREATE TABLE UserInfo
(ID number primary key NOT NULL)

--Query with several tables
SELECT * from All_tables where owner= ' QJT '

--Pseudo-column
Select Id,rowid,rownum from UserInfo

--The third record of querying natural sort
SELECT * FROM
(
Select Ename,rownum rn from EMP
) Temp
where rn=3

--Query teachers ' information on the fifth salary ranking in the Teacher's table
SELECT * FROM
(
Select RowNum rn,ta.* from
(
Select Ename,sal,rownum rn from emp ORDER BY sal Desc
) Ta where rownum<6
) Where rn>=5

--pagination
--Method One
SELECT * FROM
(
Select Temp.*,rownum rn from
(
Select emp.* from emp
ORDER BY Sal Desc
) Temp
where rownum<=9
)
where rn>=5
--Method Two
SELECT * FROM
(
SELECT Temp.*,rownum RN from
(
SELECT * from emp e ORDER by E.sal DESC
) Temp
) Temp2
WHERE Temp2.rn between 5 and 9

--Authorization
Grant SELECT on HAPPYY2165. STUDENT to Scott

--Unlock User
Alter user HR account unlock

--Revoke permissions
Revoke select on QJT from SCOTT

--Through role control
--Custom roles
CREATE role role_testy2165

--Bind the query table's permissions and roles
Grant SELECT on Y2165. " Student "to role_testy2165

--Assigning roles to users
GRANT role_testy2165 to SCOTT

--distinct querying for data that is not duplicated
Select DISTINCT "Stuname", "stuage" from STUDENT;

--Create a backup table of student tables
CREATE TABLE Studentbak
As
SELECT * FROM STUDENT

--Delete the same column, preserving the highest ID
DELETE from Student
WHERE "Stuno" Not In (select "MAX" ("Stuno") from student GROUP by "Stuname", "Stuage")

SELECT from dual

SELECT ' You ' | | ' Good ' from dual

SELECT ' operation result ' | | ' 5 ' from dual

--Intersection
SELECT Deptno from Dept
INTERSECT
SELECT DISTINCT Deptno from emp

--Reduced set
SELECT Deptno from Dept
Minus
SELECT DISTINCT Deptno from emp

Getting Started with Oracle