DB2 SQL View

Top of Page

-- some examples of views
-- SIMPLE VIEW, WITH WHERE CLAUSE                               
CREATE VIEW EMPV                                                
AS                                                              
SELECT EMPNO, LASTNAME, SALARY, COMM                            
FROM DSN8610.EMP                                                
WHERE SALARY > 30000                                            
;                                                               
                                                                
SELECT * FROM EMPV;                                             
                                                                
                                                                
-- VIEW CHANGING COLUMN NAME                                    
CREATE VIEW EMPV2                                               
AS                                                              
SELECT EMPNO, LASTNAME, SALARY, COMM  AS COMMISH                
FROM DSN8610.EMP                                                
WHERE SALARY > 30000                                            
;                                                               
     

                                                                  
SELECT COMMISH FROM EMPV2;                                        
--SELECT COMM    FROM EMPV2; -- THIS WON'T WORK                   
                                                                  
                                                                  
-- VIEW THAT CHANGES ALL COLUMN NAMES, ANOTHER WAY                
CREATE VIEW EMPV3                                                 
      (EM,    LA,       SA,     CO)                               
AS                                                                
SELECT EMPNO, LASTNAME, SALARY, COMM                              
FROM DSN8610.EMP                                                  
WHERE SALARY > 30000                                              
;                                                                 
                                                                  
SELECT CO      FROM EMPV3;                                        
                                  


-- EXAMPLE OF AN INLINE VIEW                                        
SELECT D.DEPTNO, D.DEPTNAME, D.MGRNO, E.LASTNAME                    
FROM DEPT D                                                         
   INNER JOIN                                                       
       (SELECT WORKDEPT, LASTNAME FROM EMP) AS E                    
   ON D.DEPTNO = E.WORKDEPT                                         
;                                                                   
                                                                    
                                                                    
-- EXAMPLE OF AN INLINE VIEW                                        
SELECT D.DEPTNO, D.DEPTNAME, D.MGRNO, E.LA                          
FROM DEPT D                                                         
   INNER JOIN                                                       
       (SELECT WORKDEPT, LASTNAME AS LA FROM EMP) AS E              
   ON D.DEPTNO = E.WORKDEPT                                         
;
Top of Page
Your email and other personal information will not be given to anyone and will be used only to communicate with you about your order. [Books Computer]