Oracle Classroom Essay--day 14th

Collection Operations

Select employee_id, job_id from Employees

UNION ALL does not remove reserved duplicate values

Select employee_id, job_id from Job_history;

Select employee_id, job_id from Employees

Union removes duplicate values

Select employee_id, job_id from Job_history;

Select employee_id, job_id from Employees

Intersect intersect

Select employee_id, job_id from Job_history;

Select employee_id from Employees

Minus minus

Select employee_id from Job_history;

Select employee_id, job_id, salary from employees

UNION ALL

Select employee_id, job_id, null from job_history;

Select employee_id, job_id, To_char (salary) from employees

UNION ALL

Select employee_id, job_id, ' no salary ' from job_history;

Upper and lower corresponding

Collection Sort:

Select employee_id, job_id, salary from employees

UNION ALL

Select employee_id, job_id, null from Job_history

Order by salary;

Select employee_id, job_id, null from Job_history

UNION ALL

Select employee_id, job_id, salary from employees

Order by 3;

DML

Create table T1 as select *from Employees

Drop table tl;

Insert: inserting a string

sql> CREATE TABLE t1 (x int, y char (1), z date);

sql> INSERT into T1 (x, y, Z) VALUES (1, ' a ', sysdate);

table values

sql> INSERT into T1 (x, z, y) VALUES (2, sysdate+1, ' B ');

sql> INSERT into T1 (x, y, Z) VALUES (1, NULL, sysdate);

sql> INSERT into T1 (x, z) VALUES (2, sysdate+1);

sql> INSERT INTO T1 values (1, NULL, sysdate);

all the values have to be written.

Sql> CREATE TABLE My_emp as SELECT * FROM Employees;

Sql> CREATE TABLE My_emp as select Last_Name, salary from employees where department_id=50;

Sql> CREATE TABLE Avg_sal as select department_id, avg (Salary) Avg_sal from Employees group by DEPARTMENT_ID;

Sql> CREATE TABLE My_emp as SELECT * from Employees where 1=0;

sql> INSERT INTO my_emp SELECT * FROM Employees;

Update: Updating columns

sql> update my_emp set salary=salary*1.1;

sql> Update my_emp set salary=salary*1.1 where department_id=50;

sql> Update my_emp set salary=salary*1.1, commission_pct=0.5 where employee_id=197;

Delete

Sql> Delete from my_emp where employee_id=197;

Sql> Delete from my_emp where department_id=50;

Sql> Delete from my_emp;

Sub-query:

Sql> CREATE TABLE My_emp as SELECT * FROM Employees;

Sql> ALTER TABLE my_emp Add (department_name varchar2 (30));

sql> Update my_emp outer set department_name= (select Department_name from departments where department_id= OUTER.DEPARTMENT_ID);

Update (select T1.department_name as Aname,t2.department_name bname from My_emp T1, departments T2 where t1.department_id= T2.DEPARTMENT_ID) set Aname=bname;

Delete a department with no employees in the New_dept table

Sql> CREATE TABLE My_dept as SELECT * from departments;

Delete from my_dept outer

Where NOT EXISTS

(select 1 from my_emp

where department_id=outer.department_id);

Commit End

Delete and truncate:

Delete truncate

Statement type DML DDL

Undo data produces large amounts of undo data without undo data

Space management does not release release

syntax where to delete all data

DDL

String:

sql> CREATE TABLE t1 (x char (ten), Y VARCHAR2 (10));

sql> INSERT INTO T1 values (' x ', ' Y ');

Sql> Select Dump (x), dump (y) from T1;

Numerical:

sql> CREATE TABLE T1 (x number (5,2), y number (5));

sql> INSERT INTO T1 values (123.45, 12345);

sql> INSERT INTO T1 values (12.345, 12345);

sql> INSERT INTO T1 values (12.345, 123.45);

Sql> select * from T1;

sql> INSERT INTO T1 values (12.345, 112345);

Date Time:

sql> CREATE TABLE T1 (a date, b timestamp, c timestamp with time zone, D-Timestamp with local time zone);

sql> INSERT INTO T1 values (sysdate, Systimestamp, Systimestamp, Systimestamp);

Sql> alter session set time_zone= ' +9:00 ';

Sql> select * from T1;

To modify a table structure:

Sql> ALTER TABLE T1 Add (e char (10));

sql> ALTER TABLE T1 drop (e);

sql> ALTER TABLE T1 modify (d not null);

Constraint conditions:

Field (column): NOT NULL, check (salary>0)

Rows and rows: Primary key, unique

Between table and table: foreign key

CREATE TABLE Dept (

DEPTNO int constraint DEPT_DEPTNO_PK primary key,

Dname varchar2 () constraint dept_dname_nn not NULL);

CREATE TABLE EMP (

empno int constraint EMP_EMPNO_PK primary key,

ename varchar2 (constraint) emp_ename_nn NOT NULL,

email varchar2 (constraint emp_email_uq unique),

Salary int constraint emp_salary_ck check (salary>0),

DEPTNO int constraint EMP_DEPTNO_FK references dept (DEPTNO))

Sql> Select Constraint_name, constraint_type from user_constraints where table_name in (' DEPT ', ' EMP ');

sql> INSERT into EMP values (+, ' abc ', ' [email protected] ', 10000, 10);

INSERT into EMP values (+, ' abc ', ' [email protected] ', 10000, 10)

*

ERROR at line 1:

Ora-02291:integrity constraint (HR. EMP_DEPTNO_FK) violated-parent Key not

Found

sql> INSERT INTO Dept values (' Sales ');

1 row created.

sql> INSERT INTO Dept values ("Market");

INSERT INTO Dept VALUES ("Market")

*

ERROR at line 1:

Ora-00001:unique constraint (HR. DEPT_DEPTNO_PK) violated

sql> INSERT INTO Dept values ("Market");

1 row created.

Sql> commit;

Commit complete.

sql> INSERT INTO EMP values (101, ' Def ', ' [email protected] ', 10000, 20);

CREATE TABLE EMP (

empno int constraint EMP_EMPNO_PK primary key,

ename varchar2 (constraint) emp_ename_nn NOT NULL,

email varchar2 (constraint emp_email_uq unique),

Salary int constraint emp_salary_ck check (salary>0),

DEPTNO int constraint EMP_DEPTNO_FK references dept (deptno) on delete set null) or ON DELETE cascade

Create table TL (x int constraint tl_x_pk primary key);

Alter table tl (add y int);

Alter table tl Add (z int, a int);

Alter table tl Frop (z, a);

Alter table tl Modify (y char (1));

Alter table tl Modify (y default ' a ')

Alter table tl Disable novalidate constraint tl_x_pk;

Insert into TL values (1, ' a ');

Insert into TL values (1, ' B ');

Alter table tl enable validate constraint tl_x_pk;

Delete tl;

Alter table tl enable validate constraint tl_x_pk;

Drop table tl;

Instead OF trigger view trigger substitution trigger

Sequence:

sql> Create sequence Test_seq increment by 1 start with 1 MaxValue + nocycle cache 20;

sql> CREATE TABLE t1 (x int primary key, y int);

sql> INSERT INTO T1 values (Test_seq.nextval, 11); Repeated execution

Sql> select * from T1;

Select Test_seq.currva from dual one time per sequence growth

Index:

Primary KEY and uniqueness constraints automatically create indexes:

Sql> Select Constraint_name, Constraint_type from user_constraints where table_name= ' EMPLOYEES ';

Sql> Select Index_name, Index_type uniqueness; From user_indexes where table_name= ' EMPLOYEES ';

Sql> set Autot on

Sql> Select last_name from Employees where employee_id=100; Walk index

Sql> select email from employees; Walk index

Sql> Select last_name from Employees where salary=2100; Full table Scan

Sql> CREATE index Emp_salary_ix on employees (salary);

Sql> Select last_name from Employees where salary=2100; Walk index

Sql> set Autot off

Oracle Classroom Essay--day 14th