Learn about Oracle

Here is the Oracle copy from a brother. Thank you!
Java code
1. query constraints in the data dictionary:
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints WHERE table_name = 'ployees ';
// The table names here are all uppercase!
2. Describe the table structure:
Desc Tablename
3. View tables under the user
Select table_name from user_tables;
4. Check if the constraint is created on that column:
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'ployees ';
10. Search for the column names associated with the constraints in a specific table using the variables:
Select constraint_name, column_name from user_cons_columns where table_name = '& tablename'
12. query the data dictionary to view the intermediate elements:
SELECT object_name, object_type
FROM user_objects
WHERE object_name LIKE 'emp' %'
OR object_name LIKE 'dept %'
14 query object type:
Select distinct object_type FROM user_objects;
17 Change Object Name: (Table Name, view, sequence)
Rename emp to emp_newTable
18 Add a table comment:
Comment on table employees IS 'employee information ';
20. view the view structure:
Describe view_name
23 view information in the data dictionary:
Select viewe_name, text from user_views
25. view the sequence in the data dictionary:
Select * from user_sequences
33. Get the names of all time zones:
Select * from v $ timezone_names
34. display the time zone offset of 'us/Eastern '.
Select TZ_OFFSET ('us/Eastern ') from DUAL -- dual'
Display the current date and time in the current session time zone:
Alter session set NLS_DATE_FORMAT = 'dd-MON-YYYY HH24: MI: ss'; -- modify the setting of the display time Method
Alter session set TIME_ZONE = '-'; -- modify the time zone
Select sessiontimezone, CURRENT_DATE from dual; -- a really useful statement!
SELECT CURRENT_TIMESTAMP from dual; -- the returned time is the current date and time, containing the time zone
SELECT CURRENT_TIMESTAMP from dual; -- the returned time is the current date and time, excluding the time zone !!!
35. display the values of the database time zone and session time zone:
Select datimezone, sessiontimezone from dual;
 
13 common table creation statements:
Create table dept
(Deptno NUMBER (2 ),
Dname VARCHAR2 (14 ),
Loc VARCHAR2 (13 ));
15 create a table using a subquery:
Create table dept80
As select employee_id, last_name,
Salary * 12 ANNSAL,
Hire_date FROM employees WHERE department_id = 80;
6. add a column: // alter table EMP add column (dept_id number (7); error !!
Alter table EMP add (dept_id number (7 ));
7. delete a column:
Alter table emp drop column dept_id;
8. Add both column names and constraints:
Alter table EMP add (dept_id number (7)
Constraint my_emp_dept_id_fk references dept (ID ));
9. Change the column: // note that the constraint cannot be modified !!
Alter table dept80 modify (last_name varchar2 (30); // here we use modify instead of alter!
24 add a line:
Insert into table_name values ();
 
5. Add a primary key:
Alter Table EMP add constraint my_emp_id_pk primary key (ID );
11 Add a new column with check constraints:
Alter table EMP
Add (COMMISSION number (2) constraint emp_commission_ck check (commission> 0 ))
16. delete a table:
Drop table emp;
19 create a view:
Create view empvu80
As select employee_id, last_name, salary
FROM employees WHERE department_id = 80;
21. Delete A View:
Drop view view_name
22. Find the top 5 employees. (Top-n Analysis) (In-row view)
Select rownum, employee_id from (select employee_id, salary from
Employees order by salary desc)
Where rownum <5;
26 create Synonyms:
Create synonym name for original name
Or create public synonym name for the original name
27 create a sequence: (Note that the sequence in the table is not shown here !!)
Create sequence dept_deptid_seq
Increment by 10
Start with 120
Max value 9999
NOCACHE
NOCYCLE
28 use sequence:
Insert into dept (ID, NAME) values (DEPT_ID_SEQ.nextval, 'admin ');
29 create an index: // by default, the index is a nonunique index, unless the Keyword: unique is used.
Create index emp_last_name_idx ON employees (last_name );
30. Create a user: (errors may occur. For more information, see help)
Create user username (user name)
Identified by oracle (password)
Default tablespace data01 (the tablespace name // exists in the system tablespace by default)
Quota 10 M (set size, maximum: unlimited) on tablespace name // a quota must be allocated!
31. create ROLE manager
Grant role permissions: grant create table, create view to manage
Grant user roles: grant manager to DENHAAN and KOCHHAR (two Users)
32. Assign permissions:
GRANT update (department_name, location_id)
ON orders ments
TO scott, manager;
Revoke permissions
REVOKE select, insert
ON orders ments
FROM scott;
36 extract year, month, and day from time: Use the function extract
Select extract (year from sysdate) year, extract (month from sysdate ),
Extract (day from sysdate) from dual;
37. Use the function to get the date several months later: to_yminterval ('01-02 ') indicates that the date of January 1, February cannot be reached !!
Select hire_date, hire_date + to_yminterval ('01-02 ') as hire_date_new from employees where department_id = 20
How many days do you get the date: Add a number to the date!
Select hire_date + 3 from employees where department_id = 20
38 General time functions:
MONTHS_BETWEEN ('01-SEP-95 ', '11-JAN-94') -- the number of months between two dates, returns a floating point number
ADD_MONTHS ('11-JAN-94 ', 6) -- add the number of months
NEXT_DAY ('01-SEP-95 ', 'Friday') -- Date of the next FRIDAY
LAST_DAY ('01-FEB-95 ') -- the last day of the month!
ROUND (SYSDATE, 'month') -- rounding a MONTH
ROUND (SYSDATE, 'Year') -- rounding YEAR
TRUNC (SYSDATE, 'month') -- phase MONTH
TRUNC (SYSDATE, 'Year') -- truncation YEAR
39 group statement: and advanced application statement:
SELECT department_id, job_id, SUM (salary), COUNT (employee_id) FROM employees
Group by department_id, job_id;
Use having for constraints:
1. group by rollup: n + is obtained by combining n columns.
SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id <60 group by rollup (department_id, job_id );
2. group by cube: returns the N power of 2.
SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id <60 group by cube (department_id, job_id );
3. When grouping is used to obtain the columns in a row, only 1 and 0 are returned. If it is null, 1 is returned. Otherwise, 0 is returned. (do not reverse it !)
SELECT department_id DEPTID, job_id JOB, SUM (salary), GROUPING (department_id) GRP_DEPT, GROUPING (job_id) GRP_JOB
FROM employees WHERE department_id <50 group by rollup (department_id, job_id );
4. grouping sets: gets the combination as needed
SELECT department_id, job_id, manager_id, avg (salary) FROM employees group by grouping sets (department_id, job_id), (job_id, manager_id ));
Use subquery in 40from: return information about the average salary and employees of each department larger than that of the Department to be changed.
SELECT a. last_name, a. salary, a. department_id, B. salavg FROM employees a, -- the following is a subquery, mainly returning a set of data!
(SELECT department_id, AVG (salary) salavg FROM employees group by department_id) B
WHERE a. department_id = B. department_id
AND a. salary> B. salavg;
41exists statement usage:
SELECT employee_id, last_name, job_id, department_id
FROM employees outer -- select FROM exists can be any character or number.
Where exists (SELECT 'x' FROM employees WHERE manager_id = outer. employee_id );
42 powerful with statements:
WITH
Dept_costs AS (-- defines a temporary table
SELECT d. department_name, SUM (e. salary) AS dept_total -- a temporary column dept_total is defined.
FROM employees e, departments d
WHERE e. department_id = d. department_id
Group by d. department_name),/* Note that there is a comma */
Avg_cost (
Select sum (dept_total)/COUNT (*) AS dept_avg
FROM dept_costs) -- the second temporary table here references the column between the temporary table and the previously defined temporary table!
SELECT * FROM dept_costs WHERE dept_total> (SELECT dept_avg FROM avg_cost) order by department_name; --- the preceding temporary table is used in the final query statement.
43. Traverse tree:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
Start with employee_id = 101
Connect by prior manager_id = employee_id; -- bottom-up traversal tree.
 
44. Update statement
UPDATE employees SET
Job_id = 'sa _ MAN ', salary = salary + 1000, department_id = 120
WHERE first_name | ''| last_name = 'Douglas Grant ';
 
Update table (SELECT projs
FROM dept d WHERE d. dno = 123) p
SET p. budgets = p. budgets + 1
WHERE p. pno IN (123,456 );

Author: "jsczxy2"