PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Indexes

Views

SQL> cl scr 
 
SQL> CREATE VIEW Employees 
  2  AS 
  3  SELECT 
  4   Empno "ID Number", 
  5   Ename Name, 
  6   Sal "Basic Salary", 
  7   Job Designation 
  8  FROM Emp; 
 
View created. 
 
SQL> SELECT 
  2     Empno "ID Number", 
  3     Ename Name, 
  4     Sal "Basic Salary", 
  5     Job Designation 
  6  FROM Emp; 
 
 ID Number NAME       Basic Salary DESIGNATI                                     
---------- ---------- ------------ ---------                                     
      7839 KING               5000 PRESIDENT                                     
      7698 BLAKE              2850 MANAGER                                       
      7782 CLARK              2450 MANAGER                                       
      7566 JONES              2975 MANAGER                                       
      7654 MARTIN             1250 SALESMAN                                      
      7499 ALLEN              1600 SALESMAN                                      
      7844 TURNER             1500 SALESMAN                                      
      7900 JAMES               950 CLERK                                         
      7521 WARD               1250 SALESMAN                                      
      7902 FORD               3000 ANALYST                                       
      7369 SMITH               800 CLERK                                         
 
 ID Number NAME       Basic Salary DESIGNATI                                     
---------- ---------- ------------ ---------                                     
      7788 SCOTT              3000 ANALYST                                       
      7876 ADAMS              1100 CLERK                                         
      7934 MILLER             1300 CLERK                                         
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=54           
          6)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =546)                                                                  
                                                                                 
 
 
 
SQL> DESC Employees 
 Name                                      Null?    Type 

 ----------------------------------------- -------- ---------------------------- 
 ID Number                                 NOT NULL NUMBER(4) 
 NAME                                               VARCHAR2(10) 
 Basic Salary                                       NUMBER(7,2) 
 DESIGNATION                                        VARCHAR2(9) 
 
SQL> SELECT * FROM Employees; 
 
 ID Number NAME       Basic Salary DESIGNATI                                     
---------- ---------- ------------ ---------                                     
      7839 KING               5000 PRESIDENT                                     
      7698 BLAKE              2850 MANAGER                                       
      7782 CLARK              2450 MANAGER                                       
      7566 JONES              2975 MANAGER                                       
      7654 MARTIN             1250 SALESMAN                                      
      7499 ALLEN              1600 SALESMAN                                      
      7844 TURNER             1500 SALESMAN                                      
      7900 JAMES               950 CLERK                                         
      7521 WARD               1250 SALESMAN                                      
      7902 FORD               3000 ANALYST                                       
      7369 SMITH               800 CLERK                                         
 
 ID Number NAME       Basic Salary DESIGNATI                                     
---------- ---------- ------------ ---------                                     
      7788 SCOTT              3000 ANALYST                                       
      7876 ADAMS              1100 CLERK                                         
      7934 MILLER             1300 CLERK                                         
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=54           
          6)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =546)                                                                  
                                                                                 
 
 
 
SQL> SELECT Empno, Ename, Sal FROM Employees; 
SELECT Empno, Ename, Sal FROM Employees 
                     * 
ERROR at line 1: 
ORA-00904: "SAL": invalid identifier  
 
 
SQL> SELECT "ID Number", Name, "Basic Salary", HireDate 
  2  FROM Employees; 
SELECT "ID Number", Name, "Basic Salary", HireDate 
                                          * 
ERROR at line 1: 
ORA-00904: "HIREDATE": invalid identifier  
 

 
SQL> SELECT "ID Number", Name, "Basic Salary" 
  2  FROM Employees; 
 
 ID Number NAME       Basic Salary                                               
---------- ---------- ------------                                               
      7839 KING               5000                                               
      7698 BLAKE              2850                                               
      7782 CLARK              2450                                               
      7566 JONES              2975                                               
      7654 MARTIN             1250                                               
      7499 ALLEN              1600                                               
      7844 TURNER             1500                                               
      7900 JAMES               950                                               
      7521 WARD               1250                                               
      7902 FORD               3000                                               
      7369 SMITH               800                                               
 
 ID Number NAME       Basic Salary                                               
---------- ---------- ------------                                               
      7788 SCOTT              3000                                               
      7876 ADAMS              1100                                               
      7934 MILLER             1300                                               
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=46           
          2)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =462)                                                                  
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT "ID Number", Name, "Basic Salary" 
  2  FROM Employees 
  3* WHERE "Basic Salary" > 1500 
SQL> / 
 
 ID Number NAME       Basic Salary                                               
---------- ---------- ------------                                               
      7839 KING               5000                                               
      7698 BLAKE              2850                                               
      7782 CLARK              2450                                               
      7566 JONES              2975                                               
      7499 ALLEN              1600                                               
      7902 FORD               3000                                               
      7788 SCOTT              3000                                               
 

7 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=7 Bytes=231           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=7 Bytes=           
          231)                                                                   
                                                                                 
 
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deptno, SUm("Basic Salary") 
  2  FROM Employees 
  3* GROUP BY Deptno 
SQL> / 
GROUP BY Deptno 
         * 
ERROR at line 3: 
ORA-00904: "DEPTNO": invalid identifier  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Deisgnation, SUm("Basic Salary") 
  2  FROM Employees 
  3* GROUP BY Deisgnation 
SQL> / 
GROUP BY Deisgnation 
         * 
ERROR at line 3: 
ORA-00904: "DEISGNATION": invalid identifier  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT Designation, SUm("Basic Salary") 
  2  FROM Employees 
  3* GROUP BY Designation 
SQL> / 
 
DESIGNATI SUM("BASICSALARY")                                                     
--------- ------------------                                                     
ANALYST                 6000                                                     
CLERK                   4150                                                     
MANAGER                 8275                                                     
PRESIDENT               5000                                                     
SALESMAN                5600                                                     
 

 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=26           
          6)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=4 Card=14 Bytes=266)                           
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt           
          es=266)                                                                
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> SELECT 
  2   "ID Number", 
  3   Name, 
  4   "Basic Salary" * 12 
  5  FROM Employees; 
 
 ID Number NAME       "BASICSALARY"*12                                           
---------- ---------- ----------------                                           
      7839 KING                  60000                                           
      7698 BLAKE                 34200                                           
      7782 CLARK                 29400                                           
      7566 JONES                 35700                                           
      7654 MARTIN                15000                                           
      7499 ALLEN                 19200                                           
      7844 TURNER                18000                                           
      7900 JAMES                 11400                                           
      7521 WARD                  15000                                           
      7902 FORD                  36000                                           
      7369 SMITH                  9600                                           
 
 ID Number NAME       "BASICSALARY"*12                                           
---------- ---------- ----------------                                           
      7788 SCOTT                 36000                                           
      7876 ADAMS                 13200                                           
      7934 MILLER                15600                                           
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=46           
          2)                                                                     
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes           
          =462)                                                                  
                                                                                 
 
 
 
SQL> SELECT 

  2   "ID Number", 
  3   Name, 
  4   TO_CHAR("Basic Salary", '99,99,999.99') Monthly, 
  5   "Basic Salary" * 12 Annual 
  6  FROM Employees 
  7  WHERE "Basic Salary" > 2500; 
 
 ID Number NAME       MONTHLY           ANNUAL                                   
---------- ---------- ------------- ----------                                   
      7839 KING            5,000.00      60000                                   
      7698 BLAKE           2,850.00      34200                                   
      7566 JONES           2,975.00      35700                                   
      7902 FORD            3,000.00      36000                                   
      7788 SCOTT           3,000.00      36000                                   
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=5 Bytes=165           
          )                                                                      
                                                                                 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=5 Bytes=           
          165)                                                                   
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> CREATE VIEW EmpInfo 
  2  AS 
  3  SELECT 
  4   E.Empno  EmployeeID, 
  5   E.Ename  Name, 
  6   D.Deptno DepartmentID, 
  7   D.Dname DepartmentName 
  8  FROM Emp E, Dept D 
  9  WHERE D.Deptno = E.Deptno 
 10  ORDER BY D.Deptno; 
 
View created. 
 
SQL> DESC EmpInfo 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPLOYEEID                                NOT NULL NUMBER(4) 
 NAME                                               VARCHAR2(10) 
 DEPARTMENTID                              NOT NULL NUMBER(2) 
 DEPARTMENTNAME                                     VARCHAR2(14) 
 
SQL> SELECT * FROM EmpInfo; 
 
EMPLOYEEID NAME       DEPARTMENTID DEPARTMENTNAME                                
---------- ---------- ------------ --------------                                
      7839 KING                 10 ACCOUNTING                                    
      7782 CLARK                10 ACCOUNTING                                    

      7934 MILLER               10 ACCOUNTING                                    
      7566 JONES                20 RESEARCH                                      
      7788 SCOTT                20 RESEARCH                                      
      7876 ADAMS                20 RESEARCH                                      
      7369 SMITH                20 RESEARCH                                      
      7902 FORD                 20 RESEARCH                                      
      7698 BLAKE                30 SALES                                         
      7654 MARTIN               30 SALES                                         
      7499 ALLEN                30 SALES                                         
 
EMPLOYEEID NAME       DEPARTMENTID DEPARTMENTNAME                                
---------- ---------- ------------ --------------                                
      7844 TURNER               30 SALES                                         
      7900 JAMES                30 SALES                                         
      7521 WARD                 30 SALES                                         
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=14 Bytes=58           
          8)                                                                     
                                                                                 
   1    0   VIEW OF 'EMPINFO' (VIEW) (Cost=8 Card=14 Bytes=588)                  
   2    1     SORT (ORDER BY) (Cost=8 Card=14 Bytes=770)                         
   3    2       HASH JOIN (Cost=7 Card=14 Bytes=770)                             
   4    3         TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4           
           Bytes=88)                                                             
                                                                                 
   5    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=462)                                                            
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> CREATE VIEW EmpGrades 
  2  AS 
  3  SELECT 
  4   E.Ename Name, 
  5   E.Sal Basic, 
  6   S.Grade Grade 
  7  FROM Emp E , Salgrade S 
  8  WHERE E.Sal BETWEEN S.LoSal AND S.HiSal 
  9  ORDER BY S.Grade; 
 
View created. 
 
SQL> DESC EmpGrades 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 NAME                                               VARCHAR2(10) 
 BASIC                                              NUMBER(7,2) 
 GRADE                                              NUMBER 

 
SQL> SELECT * FROM EmpGrades; 
 
NAME            BASIC      GRADE                                                 
---------- ---------- ----------                                                 
SMITH             800          1                                                 
JAMES             950          1                                                 
ADAMS            1100          1                                                 
MARTIN           1250          2                                                 
WARD             1250          2                                                 
MILLER           1300          2                                                 
TURNER           1500          3                                                 
ALLEN            1600          3                                                 
CLARK            2450          4                                                 
BLAKE            2850          4                                                 
JONES            2975          4                                                 
 
NAME            BASIC      GRADE                                                 
---------- ---------- ----------                                                 
FORD             3000          4                                                 
SCOTT            3000          4                                                 
KING             5000          5                                                 
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=33)           
   1    0   VIEW OF 'EMPGRADES' (VIEW) (Cost=9 Card=1 Bytes=33)                  
   2    1     SORT (ORDER BY) (Cost=9 Card=1 Bytes=59)                           
   3    2       MERGE JOIN (Cost=8 Card=1 Bytes=59)                              
   4    3         SORT (JOIN) (Cost=4 Card=5 Bytes=195)                          
   5    4           TABLE ACCESS (FULL) OF 'SALGRADE' (TABLE) (Cost=3            
          Card=5 Bytes=195)                                                      
                                                                                 
   6    3         FILTER                                                         
   7    6           SORT (JOIN) (Cost=4 Card=14 Bytes=280)                       
   8    7             TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Car           
          d=14 Bytes=280)                                                        
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> CREATE OR REPLACE VIEW EmpManagers 
  2  AS 
  3  SELECT 
  4   RowNum SerialNo, 
  5   INITCAP(E.Ename)||' Works Under ' 
  6   ||M.Ename  "Employee And Managers" 
  7  FROM Emp E, Emp M 
  8  WHERE E.MGR = M.Empno; 
 
View created. 

 
SQL> DESC EmpManagers 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 SERIALNO                                           NUMBER 
 Employee And Managers                              VARCHAR2(33) 
 
SQL> SELECT * FROM EmpManagers; 
 
  SERIALNO Employee And Managers                                                 
---------- ---------------------------------                                     
         1 Jones Works Under KING                                                
         2 Clark Works Under KING                                                
         3 Blake Works Under KING                                                
         4 Ward Works Under BLAKE                                                
         5 James Works Under BLAKE                                               
         6 Turner Works Under BLAKE                                              
         7 Allen Works Under BLAKE                                               
         8 Martin Works Under BLAKE                                              
         9 Miller Works Under CLARK                                              
        10 Scott Works Under JONES                                               
        11 Ford Works Under JONES                                                
 
  SERIALNO Employee And Managers                                                 
---------- ---------------------------------                                     
        12 Smith Works Under FORD                                                
        13 Adams Works Under SCOTT                                               
 
13 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=13 Bytes=40           
          3)                                                                     
                                                                                 
   1    0   VIEW OF 'EMPMANAGERS' (VIEW) (Cost=7 Card=13 Bytes=403)              
   2    1     COUNT                                                              
   3    2       HASH JOIN (Cost=7 Card=13 Bytes=520)                             
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=280)                                                            
                                                                                 
   5    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=280)                                                            
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> CREATE OR REPLACE VIEW EmpAccounts 
  2  AS 
  3  SELECT 
  4   Ename, 
  5   Deptno, 
  6   Sal Monthly, 

  7   Sal * 12 Annual 
  8  FROM  Emp 
  9  WHERE Deptno = (SELECT 
 10       Deptno 
 11      FROM Dept 
 12      WHERE Dname = 'ACCOUNTING') 
 13  ORDER BY Annual; 
 
View created. 
 
SQL> DESC EmpAccounts 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 ENAME                                              VARCHAR2(10) 
 DEPTNO                                    NOT NULL NUMBER(2) 
 MONTHLY                                            NUMBER(7,2) 
 ANNUAL                                             NUMBER 
 
SQL> SELECT * FROM EmpAccounts; 
 
ENAME          DEPTNO    MONTHLY     ANNUAL                                      
---------- ---------- ---------- ----------                                      
MILLER             10       1300      15600                                      
CLARK              10       2450      29400                                      
KING               10       5000      60000                                      
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=46)           
   1    0   VIEW OF 'EMPACCOUNTS' (VIEW) (Cost=7 Card=1 Bytes=46)                
   2    1     SORT (ORDER BY) (Cost=7 Card=1 Bytes=33)                           
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 By           
          tes=33)                                                                
                                                                                 
   4    3         TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1           
           Bytes=22)                                                             
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> CREATE OR REPLACE VIEW CumSum 
  2  AS 
  3  SELECT 
  4   B.Sal, 
  5   SUM(A.Sal) Cum_Sal 
  6  FROM Emp A, Emp B 
  7  WHERE A.RowID <= B.RowID 
  8  GROUP BY B.RowID, B.Sal; 
 
View created. 
 
SQL> DESC CumSum 
 Name                                      Null?    Type 

 ----------------------------------------- -------- ---------------------------- 
 SAL                                                NUMBER(7,2) 
 CUM_SAL                                            NUMBER 
 
SQL> SELECT * FROM CumSum; 
 
       SAL    CUM_SAL                                                            
---------- ----------                                                            
      5000       5000                                                            
      2850       7850                                                            
      2450      10300                                                            
      2975      13275                                                            
      1250      14525                                                            
      1600      16125                                                            
      1500      17625                                                            
       950      18575                                                            
      1250      19825                                                            
      3000      22825                                                            
       800      23625                                                            
 
       SAL    CUM_SAL                                                            
---------- ----------                                                            
      3000      26625                                                            
      1100      27725                                                            
      1300      29025                                                            
 
14 rows selected. 
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=10 Bytes=40           
          0)                                                                     
                                                                                 
   1    0   SORT (GROUP BY) (Cost=9 Card=10 Bytes=400)                           
   2    1     MERGE JOIN (Cost=8 Card=10 Bytes=400)                              
   3    2       SORT (JOIN) (Cost=4 Card=14 Bytes=280)                           
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=280)                                                            
                                                                                 
   5    2       SORT (JOIN) (Cost=4 Card=14 Bytes=280)                           
   6    5         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14           
           Bytes=280)                                                            
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> CREATE OR REPLACE VIEW OrgDesignations 
  2  AS 
  3  SELECT 
  4   Job 
  5  FROM Emp 
  6  WHERE Deptno = 10 
  7  UNION 

  8  SELECT 
  9   Job 
 10  FROM Emp 
 11  WHERE Deptno IN(20, 30); 
 
View created. 
 
SQL> SELECT * FROM OrgDesignations; 
 
JOB                                                                              
---------                                                                        
ANALYST                                                                          
CLERK                                                                            
MANAGER                                                                          
PRESIDENT                                                                        
SALESMAN                                                                         
 
 
Execution Plan 
----------------------------------------------------------                       
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=14 Bytes=84           
          )                                                                      
                                                                                 
   1    0   VIEW OF 'ORGDESIGNATIONS' (VIEW) (Cost=8 Card=14 Bytes=84)           
   2    1     SORT (UNIQUE) (Cost=8 Card=14 Bytes=266)                           
   3    2       UNION-ALL                                                        
   4    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3            
          Bytes=57)                                                              
                                                                                 
   5    3         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=11           
           Bytes=209)                                                            
                                                                                 
 
 
 
SQL> cl scr 
 
SQL> cl scr 
 
SQL> DESC USER_VIEWS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 VIEW_NAME                                 NOT NULL VARCHAR2(30) 
 TEXT_LENGTH                                        NUMBER 
 TEXT                                               LONG 
 TYPE_TEXT_LENGTH                                   NUMBER 
 TYPE_TEXT                                          VARCHAR2(4000) 
 OID_TEXT_LENGTH                                    NUMBER 
 OID_TEXT                                           VARCHAR2(4000) 
 VIEW_TYPE_OWNER                                    VARCHAR2(30) 
 VIEW_TYPE                                          VARCHAR2(30) 
 SUPERVIEW_NAME                                     VARCHAR2(30) 
 
SQL> COLUMN VIEW_NAME FORMAT A20 
SQL> COLUMN TEXT FORMAT A40 
SQL> SELECT VIEW_NAME, TEXT 

  2  FROM USER_VIEWS 
  3  WHERE VIEW_NAME = 'EMPACCOUNTS'; 
 
VIEW_NAME            TEXT                                                        
-------------------- ----------------------------------------                    
EMPACCOUNTS          SELECT                                                      
                      Ename,                                                     
                      Deptno,                                                    
                      Sal Monthly,                                               
                      Sal * 12 Annual                                            
                     FROM  Emp                                                   
                     WHERE Deptno =                                              
                                                                                 
 
 
Execution Plan 
----------------------------------------------------------                       
ERROR: 
ORA-01039: insufficient privileges on underlying objects of the view  
 
 
SP2-0612: Error generating AUTOTRACE EXPLAIN report 
SQL> SET LONG 300 
SQL> SELECT VIEW_NAME, TEXT 
  2  FROM USER_VIEWS 
  3  WHERE VIEW_NAME = 'EMPACCOUNTS'; 
 
VIEW_NAME            TEXT                                                        
-------------------- ----------------------------------------                    
EMPACCOUNTS          SELECT                                                      
                      Ename,                                                     
                      Deptno,                                                    
                      Sal Monthly,                                               
                      Sal * 12 Annual                                            
                     FROM  Emp                                                   
                     WHERE Deptno = (SELECT                                      
                          Deptno                                                
                         FROM Dept                                              
                         WHERE Dname = 'ACCOUNTING')                            
                     ORDER BY Annual                                             
 
VIEW_NAME            TEXT                                                        
-------------------- ----------------------------------------                    
                                                                                 
 
 
Execution Plan 
----------------------------------------------------------                       
ERROR: 
ORA-01039: insufficient privileges on underlying objects of the view  
 
 
SP2-0612: Error generating AUTOTRACE EXPLAIN report 
SQL> cl scr 
 
SQL> CREATE VIEW DeptSalSummary 

  2  ( 
  3   DepartmentName, 
  4   MinimumSalary, 
  5   MaxSalary, 
  6   AverageSalary, 
  7   SalarySum 
  8  ) 
  9  AS 
 10  SELECT 
 11   D.Dname, 
 12   MIN(E.Sal), 
 13   MAX(E.Sal), 
 14   AVG(E.Sal), 
 15   SUM(E.Sal) 
 16  FROM Emp E, Dept D 
 17  WHERE E.Deptno = D.Deptno 
 18  GROUP BY D.Dname; 
 
View created. 
 
SQL> DESC DeptSalSummary 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 DEPARTMENTNAME                                     VARCHAR2(14) 
 MINIMUMSALARY                                      NUMBER 
 MAXSALARY                                          NUMBER 
 AVERAGESALARY                                      NUMBER 
 SALARYSUM                                          NUMBER 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE OR REPLACE VIEW DeptSalSummary 
  2  AS 
  3  SELECT 
  4   D.Dname, 
  5   MIN(E.Sal), 
  6   MAX(E.Sal), 
  7   AVG(E.Sal), 
  8   SUM(E.Sal) 
  9  FROM Emp E, Dept D 
 10  WHERE E.Deptno = D.Deptno 
 11* GROUP BY D.Dname 
SQL> / 
 MIN(E.Sal), 
 * 
ERROR at line 5: 
ORA-00998: must name this expression with a column alias  
 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> CREATE VIEW InsertDept 
  2  ( 
  3    DeptID, 

  4    DeptName, 
  5    Place 
  6  ) 
  7   AS 
  8   SELECT 
  9    Deptno, 
 10    Dname, 
 11    Loc 
 12   FROM Dept; 
 
View created. 
 
SQL> DESC InsertDept 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 DEPTID                                    NOT NULL NUMBER(2) 
 DEPTNAME                                           VARCHAR2(14) 
 PLACE                                              VARCHAR2(13) 
 
SQL> SELECT * FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
 
SQL> SELECT * FROM InsertDept; 
 
    DEPTID DEPTNAME       PLACE                                                  
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
 
SQL> INSERT INTO InsertDept 
  2  VALUES(50, 'SHIPPING', 'CHENNAI'); 
 
1 row created. 
 
SQL> SELECT * FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
        50 SHIPPING       CHENNAI                                                
 
SQL> UPDATE InsertDept 
  2  SET Dname = 'CARGO' 
  3  WHERE Deptno = 50; 
WHERE Deptno = 50 

      * 
ERROR at line 3: 
ORA-00904: "DEPTNO": invalid identifier  
 
 
SQL> UPDATE InsertDept 
  2  SET DEPTNAME = 'CARGO' 
  3  WHERE DEPTID = 50; 
 
1 row updated. 
 
SQL>  SELECT * FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
        50 CARGO          CHENNAI                                                
 
SQL> DELETE FROM InsertDept 
  2  WHERE DeptID = 50; 
 
1 row deleted. 
 
SQL> SELECT * FROM Dept; 
 
    DEPTNO DNAME          LOC                                                    
---------- -------------- -------------                                          
        10 ACCOUNTING     NEW YORK                                               
        20 RESEARCH       DALLAS                                                 
        30 SALES          CHICAGO                                                
        40 OPERATIONS     BOSTON                                                 
 
SQL> cl scr 
 
SQL> COLUMN Empno FORMAT 9999 
SQL> COLUMN Deptno FORMAT 99 
SQL> COLUMN Sal FORMAT 9999 
SQL> COLUMN Comm FORMAT 9999 
SQL> cl scr 
 
SQL> ROLLBACK; 
 
Rollback complete. 
 
SQL> cl scr 
 
SQL> SELECT * FROM Emp; 
 
EMPNO ENAME      JOB              MGR HIREDATE    SAL  COMM DEPTNO               
----- ---------- --------- ---------- --------- ----- ----- ------               
 7839 KING       PRESIDENT            17-NOV-81  5000           10               
 7698 BLAKE      MANAGER         7839 01-MAY-81  2850           30               
 7782 CLARK      MANAGER         7839 09-JUN-81  2450           10               

 7566 JONES      MANAGER         7839 02-APR-81  2975           20               
 7654 MARTIN     SALESMAN        7698 28-SEP-81  1250  1400     30               
 7499 ALLEN      SALESMAN        7698 20-FEB-81  1600   300     30               
 7844 TURNER     SALESMAN        7698 08-SEP-81  1500     0     30               
 7900 JAMES      CLERK           7698 03-DEC-81   950           30               
 7521 WARD       SALESMAN        7698 22-FEB-81  1250   500     30               
 7902 FORD       ANALYST         7566 03-DEC-81  3000           20               
 7369 SMITH      CLERK           7902 17-DEC-80   800           20               
 
EMPNO ENAME      JOB              MGR HIREDATE    SAL  COMM DEPTNO               
----- ---------- --------- ---------- --------- ----- ----- ------               
 7788 SCOTT      ANALYST         7566 09-DEC-82  3000           20               
 7876 ADAMS      CLERK           7788 12-JAN-83  1100           20               
 7934 MILLER     CLERK           7782 23-JAN-82  1300           10               
 
14 rows selected. 
 
SQL> CREATE OR REPLACE VIEW EDept30 
  2  AS 
  3  SELECT * 
  4  FROM Emp 
  5  WHERE Deptno = 30; 
 
View created. 
 
SQL> DESC EDept30 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPNO                                     NOT NULL NUMBER(4) 
 ENAME                                              VARCHAR2(10) 
 JOB                                                VARCHAR2(9) 
 MGR                                                NUMBER(4) 
 HIREDATE                                           DATE 
 SAL                                                NUMBER(7,2) 
 COMM                                               NUMBER(7,2) 
 DEPTNO                                    NOT NULL NUMBER(2) 
 
SQL> SELECT * FROM EDept30; 
 
EMPNO ENAME      JOB              MGR HIREDATE    SAL  COMM DEPTNO               
----- ---------- --------- ---------- --------- ----- ----- ------               
 7698 BLAKE      MANAGER         7839 01-MAY-81  2850           30               
 7654 MARTIN     SALESMAN        7698 28-SEP-81  1250  1400     30               
 7499 ALLEN      SALESMAN        7698 20-FEB-81  1600   300     30               
 7844 TURNER     SALESMAN        7698 08-SEP-81  1500     0     30               
 7900 JAMES      CLERK           7698 03-DEC-81   950           30               
 7521 WARD       SALESMAN        7698 22-FEB-81  1250   500     30               
 
6 rows selected. 
 
SQL> SELECT * FROM EDept30 
  2  WHERE Ename = 'JONES'; 
 
no rows selected 
 
SQL> SELECT * FROM EDept30 

  2  WHERE Job = 'MANAGER'; 
 
EMPNO ENAME      JOB              MGR HIREDATE    SAL  COMM DEPTNO               
----- ---------- --------- ---------- --------- ----- ----- ------               
 7698 BLAKE      MANAGER         7839 01-MAY-81  2850           30               
 
SQL> cl scr 
 
SQL> DESC EDept30 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPNO                                     NOT NULL NUMBER(4) 
 ENAME                                              VARCHAR2(10) 
 JOB                                                VARCHAR2(9) 
 MGR                                                NUMBER(4) 
 HIREDATE                                           DATE 
 SAL                                                NUMBER(7,2) 
 COMM                                               NUMBER(7,2) 
 DEPTNO                                    NOT NULL NUMBER(2) 
 
SQL> UPDATE EDept30 
  2  SET Sal = Sal + 1000 
  3  WHERE Empno = 7566; 
 
0 rows updated. 
 
SQL> UPDATE EDept30 
  2  SET Sal = Sal + 1000 
  3  WHERE Empno = 7654; 
 
1 row updated. 
 
SQL> DELETE FROM EDept30 
  2  WHERE Ename = 'JONES'; 
 
0 rows deleted. 
 
SQL> DELETE FROM EDept30 
  2  WHERE Ename = 'TURNER'; 
 
1 row deleted. 
 
SQL> INSERT INTO EDept30 
  2  (Empno, Ename, Sal, Job, Deptno) 
  3  VALUES(1234, 'SAMPLE01', 2500, 'CLERK', 30); 
 
1 row created. 
 
SQL> (Empno, Ename, Sal, Job, Deptno) 
  2   
SQL> INSERT INTO EDept30 
  2  (Empno, Ename, Sal, Job, Deptno) 
  3  VALUES(1235, 'SAMPLE02', 2500, 'CLERK', 20); 
 
1 row created. 
 

SQL> CREATE OR REPLACE VIEW EDept30 
  2  AS 
  3  SELECT * 
  4  FROM Emp 
  5  WHERE Deptno = 30 
  6  WITH CHECK OPTION CONSTRAINT EDept30ChkView; 
 
View created. 
 
SQL> INSERT INTO EDept30 
  2  (Empno, Ename, Sal, Job, Deptno) 
  3  VALUES(1236, 'SAMPLE03', 2500, 'CLERK', 20); 
INSERT INTO EDept30 
            * 
ERROR at line 1: 
ORA-01402: view WITH CHECK OPTION where-clause violation  
 
 
SQL> cl scr 
 
SQL> CREATE OR REPLACE VIEW EDept30 
  2  AS 
  3  SELECT * 
  4  FROM Emp 
  5  WHERE Deptno = 30 
  6  WITH READ ONLY; 
 
View created. 
 
SQL> INSERT INTO EDept30 
  2  (Empno, Ename, Sal, Job, Deptno) 
  3  VALUES(1236, 'SAMPLE03', 2500, 'CLERK', 20); 
(Empno, Ename, Sal, Job, Deptno) 
 * 
ERROR at line 2: 
ORA-01733: virtual column not allowed here  
 
 
SQL> DELETE FROM EDept30 
  2  WHERE Ename = 'TURNER'; 
DELETE FROM EDept30 
            * 
ERROR at line 1: 
ORA-01752: cannot delete from view without exactly one key-preserved table  
 
 
SQL> UPDATE EDept30 
  2  SET Sal = Sal + 1000 
  3  WHERE Ename = 'TURNER'; 
SET Sal = Sal + 1000 
    * 
ERROR at line 2: 
ORA-01733: virtual column not allowed here  
 
 
SQL> cl scr 

 
SQL> CREATE TABLE MyMasterDF 
  2  ( 
  3   MastID            NUMBER(2) 
  4   CONSTRAINT MastIDDFPK PRIMARY KEY 
  5   INITIALLY DEFERRED DEFERRABLE, 
  6   MastName  VARCHAR2(10) 
  7   CONSTRAINT MastNameDFCHK 
  8   CHECK(MastName = UPPER(MastName)) 
  9   INITIALLY DEFERRED DEFERRABLE, 
 10   MastDate  DATE 
 11   CONSTRAINT MastDateDFNN NOT NULL 
 12   INITIALLY DEFERRED DEFERRABLE 
 13  ); 
 
Table created. 
 
SQL> INSERT INTO 
  2  MyMasterDF(MastID, MastName, MastDate) 
  3  VALUES(10, 'MASTER10', '10-OCT-07'); 
 
1 row created. 
 
SQL> INSERT INTO 
  2  MyMasterDF(MastID, MastName, MastDate) 
  3  VALUES(11, 'MASTER11', '11-OCT-07'); 
 
1 row created. 
 
SQL> INSERT INTO 
  2  MyMasterDF(MastID, MastName, MastDate) 
  3  VALUES(12, 'MASTER12', '12-OCT-07'); 
 
1 row created. 
 
SQL> INSERT INTO 
  2  MyMasterDF(MastID, MastName, MastDate) 
  3  VALUES(10, 'MASTER10', '10-OCT-07'); 
 
1 row created. 
 
SQL> INSERT INTO 
  2  MyMasterDF(MastID, MastName, MastDate) 
  3  VALUES(12, 'MASTER12', '12-OCT-07'); 
 
1 row created. 
 
SQL> COMMIT; 
COMMIT 
* 
ERROR at line 1: 
ORA-02091: transaction rolled back  
ORA-00001: unique constraint (SCOTT.MASTIDDFPK) violated  
 
SQL> SPOOL OFF 

Go Back