"SQL query Statement 4"

Collection operations

Remove duplicate values:Union

Do not remove duplicate values:Union all

The number and type of columns in the top and bottom two query statements are consistent, not enough to be substituted with null values or set default values

Select employee_id, job_id from Employees

UNION ALL

Select employee_id, job_id from Job_history;

Example 2:

Select employee_id, job_id from Employees

Union

Select employee_id, job_id from Job_history;

Query intersection: Intersect

Select employee_id, job_id from Employees

Intersect

Select employee_id, job_id from Job_history;

Query difference set: minus

Select employee_id from Employees

Minus

Select employee_id from Job_history;

The number and type of columns in the top and bottom two query statements are consistent, not enough to be substituted with null values or set default values

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;

Set sort:// Sort statements can only be written at the end , sorted by the columns in table 1

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 (including additions and deletions)Select Insert Update Delete)

Insert: Define column name, values: Assignment

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

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

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);// for columns with null values, you can omit this column

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

Write the contents of the employee table to my_emp

Sql> CREATE TABLE My_emp as SELECT * FROM Employees; Sub-query

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

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 (modifying) 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: Remove

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;

Practice:

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);

Delete and truncate:

DML(including additions and deletions): not automatically submitted

DDL: A carriage return, which is automatically submitted directly, and the preceding DML is also committed

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 (create Alter drop)

Char: Fixed length

Vachar: variable length

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; B

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

Instead OF Trigger view triggers

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;

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 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

"SQL query Statement 4"