Oracle knowledge point Summary 3 (subquery)

Oracle knowledge point Summary 3 (subquery) 1. Use of subquery 1. insert use of subquery when inserting data in insert, be sure not to add the Vaules keyword.

SQL> create  table copy_dep  as  select *  from    departments;Table created.SQL> truncate table copy_dep;Table truncated.SQL> insert into copy_dep    2  select * from  departments;27 rows created.

 

2. When create creates a table using a subquery, only not null in the original table will be passed to the new table, and other constraints will not be passed. When creating tables and views using subqueries, adding aliases to the expressions is required;

SQL> create table dept   2  as   3  select employee_id,last_name,salary*12,hire_date  4  from employees where department_id=80;select employee_id,last_name,salary*12,hire_date

 

* ERROR at line 3: ORA-00998: must name this expression with a column alias modification with alias

SQL> create table dept(emp_id,name,salary,hire_date)  2  as  select employee_id,last_name,salary*12,hire_date  3  from employees where department_id=80;Table created.

 

Or

SQL> create table dept  2  as  3   select employee_id,last_name,salary*12 ANNSAL,hire_date  4  from employees where department_id=80;Table created.

 

3. When update is queried using a subquery, data can be obtained from other tables.

SQL> select *  from  copy_dep;DEPARTMENT_ID     DEPARTMENT_NAME       MANAGER_ID   LOCATION_ID-----------------------      ------------------------------       -----------------     ------------------           10               Administration                          200         1700           20               Marketing                                201          1800           30              Purchasing                               114          1700           40          Human Resources                       203          2400           50                    Shipping                              121          1500           60                       IT                                    103         1400SQL> update copy_dep  set  department_name=(select department_name  from  departments  where department_id=10),  2  location_id=(select location_id from departments where department_id=10)  3  where   department_id=50;1 row updated.SQL> select *  from  copy_dep;DEPARTMENT_ID       DEPARTMENT_NAME    MANAGER_ID     LOCATION_ID-------------                  ------------------------------             ----------           -----------           10                      Administration                     200                 1700           20                     Marketing                             201        1800           30                     Purchasing                            114        1700           40                   Human Resources                       203        2400           50                  Administration                        121        1700           60                      IT                                    103        1400

 

4. Changing the read-only table to read-only can prevent table addition, deletion, and modification, but cannot be prevented from deleting the table.

SQL> alter table dept read only;Table altered.SQL> delete  from  dept where employee_id=170;delete  from  dept where employee_id=170              *ERROR at line 1:ORA-12081: update operation not allowed on table "HR"."DEPT"SQL> alter table dept  read write;Table altered.SQL> delete  from  dept where employee_id=170;1 row deleted.SQL> alter table dept read only;Table altered.SQL> drop table dept;Table dropped.

 

5. View simple View: Only one table does not use functions. complex View: one or more tables use functions. Generally, you can execute DML on a simple view, but this is NOT always the case. If the view does NOT include a forced column with not null, the insert operation on the view will NOT succeed, and the complex view cannot execute DML. Create views

SQL> create view empvu   2  as  select employee_id,last_name,salary from employees where department_id=80;View created.SQL> desc empvu Name                                           Null?               Type -----------------------------------------   --------       ---------------------------- EMPLOYEE_ID                          NOT NULL   NUMBER(6) LAST_NAME                             NOT NULL     VARCHAR2(25) SALARY                                                            NUMBER(8,2)SQL> select *  from empvu;EMPLOYEE_ID          LAST_NAME         SALARY----------- -------          ------------------          ----------        167                   Banda                    6200        168                    Ozer                     11500        169                    Bloom                   10000        170                     Fox                      9600        171                    Smith                     7400        172                    Bates                     7300

 

Change last_name = Bloom in the view with 'employee_id = 169 'to last_name = 'blooms '.

SQL> update empvu set last_name='Blooms'  2  where employee_id=169;1 row updated.SQL> commit;Commit complete.

 

Check whether the base table has been modified,

SQL> select employee_id,last_name,salary from employees where employee_id=169;EMPLOYEE_ID           LAST_NAME         SALARY----------- ---------          ----------------           ----------        169               Blooms               10000

 

The base table is also modified. The modification to the view is also a modification to the base table. Do not add with check option when creating a view.

SQL> create table emp as select * from employees;Table created.SQL> create or replace view empvu1   2  as select * from emp where department_id=20;View created.SQL> select  employee_id,last_name,department_id from  empvu1;EMPLOYEE_ID         LAST_NAME                 DEPARTMENT_ID----------- ------------       -------------                         -------------        201            Hartstein                                 20        202            Fay                                         20SQL> update empvu1 set department_id=10  where employee_id=202;1 row updated.SQL> select employee_id,last_name,department_id from empvu1;EMPLOYEE_ID         LAST_NAME                DEPARTMENT_ID-----------               -------------------------           -------------     201               Hartstein                             20

 

You can change department_id = 20 in the where clause to department_id = 10, leading to the disappearance of this line. Using with check option to create a view can prevent DML operations that cause rows to disappear from the view.

SQL> create or replace view empvu1   2  as select * from emp where department_id=20 with check option;View created.SQL> select employee_id,last_name,department_id from empvu1;EMPLOYEE_ID        LAST_NAME           DEPARTMENT_ID     ----------- -       ----------------------           -- -------------        201           Hartstein                       20SQL> update empvu1 set department_id=10 where employee_id=201;update empvu1 set department_id=10 where employee_id=201       *ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violation

 

Other Columns cannot be modified.

SQL> update empvu1 set last_name='sun' where employee_id=201;1 row updated.SQL> select employee_id,last_name,department_id from empvu1;EMPLOYEE_ID     LAST_NAME                 DEPARTMENT_ID      -----------       -------------------------            -------------        201          sun                                20SQL> drop view empvu1;View dropped.

 

Deleting a View does not affect the base table, because the View does not store data but query statements.