# SQL_For_Views

ViewSQL:
SELECT     dbo.tblDept.DeptID AS Dept_DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblEmp.DeptId AS Emp_DeptID
FROM     dbo.tblDept CROSS JOIN
         dbo.tblEmp


ViewSQL:
SELECT     dbo.tblDept.DeptID AS Dept_DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblEmp.DeptId AS Emp_DeptID
FROM         dbo.tblDept INNER JOIN
             dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId


ViewSQL:
SELECT     dbo.tblProject.ProjID, dbo.tblProject.ManagerID, dbo.tblEmp.EmpName AS ManagerName
FROM       dbo.tblProject INNER JOIN
           dbo.tblEmp ON dbo.tblProject.ManagerID = dbo.tblEmp.EmpID


ViewSQL:
SELECT     dbo.tblDept.DeptID AS Dept_DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName
FROM       dbo.tblDept INNER JOIN
           dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId INNER JOIN
           dbo.tblEmpProj ON dbo.tblEmp.EmpID = dbo.tblEmpProj.EmpID


ViewSQL:
SELECT     TOP 100 PERCENT dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblProject.ProjID, 
                      dbo.tblProject.ManagerID
FROM         dbo.tblDept INNER JOIN
             dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId INNER JOIN
             dbo.tblEmpProj ON dbo.tblEmp.EmpID = dbo.tblEmpProj.EmpID INNER JOIN
             dbo.tblProject ON dbo.tblEmpProj.ProjID = dbo.tblProject.ProjID
ORDER BY dbo.tblDept.DeptID, dbo.tblEmp.EmpID


ViewSQL:
SELECT     dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblEmp.DeptId AS Expr1
FROM         dbo.tblDept LEFT OUTER JOIN
             dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId


ViewSQL:
SELECT     dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblEmp.DeptId AS Expr1
FROM         dbo.tblDept RIGHT OUTER JOIN
             dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId


ViewSQL:
SELECT     dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblEmp.DeptId AS Expr1
FROM        dbo.tblEmp LEFT OUTER JOIN
            dbo.tblDept ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId


ViewSQL:
SELECT     dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName
FROM       dbo.tblDept FULL OUTER JOIN
           dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId


ViewSQL:
SELECT     dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblProject.ProjID
FROM        dbo.tblDept FULL OUTER JOIN
            dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId FULL OUTER JOIN
            dbo.tblEmpProj ON dbo.tblEmp.EmpID = dbo.tblEmpProj.EmpID FULL OUTER JOIN
            dbo.tblProject ON dbo.tblEmpProj.ProjID = dbo.tblProject.ProjID


ViewSQL:
SELECT     dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblEmpProj.ProjID
FROM         dbo.tblDept LEFT OUTER JOIN
             dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId LEFT OUTER JOIN
             dbo.tblEmpProj ON dbo.tblEmp.EmpID = dbo.tblEmpProj.EmpID


ViewSQL:
SELECT     dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblEmpProj.ProjID
FROM         dbo.tblDept LEFT OUTER JOIN
             dbo.tblEmp ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId INNER JOIN
             dbo.tblEmpProj ON dbo.tblEmp.EmpID = dbo.tblEmpProj.EmpID


ViewSQL:
SELECT     dbo.tblDept.DeptID, dbo.tblDept.DeptName, dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblEmpProj.ProjID
FROM         dbo.tblDept LEFT OUTER JOIN
             dbo.tblEmp INNER JOIN
             dbo.tblEmpProj ON dbo.tblEmp.EmpID = dbo.tblEmpProj.EmpID ON dbo.tblDept.DeptID = dbo.tblEmp.DeptId


ViewSQL:
SELECT     dbo.tblEmp.EmpID, dbo.tblEmp.EmpName, dbo.tblDept.DeptID
FROM       dbo.tblEmp LEFT OUTER JOIN
           dbo.tblDept ON dbo.tblEmp.DeptId = dbo.tblDept.DeptID
WHERE     (dbo.tblDept.DeptID IS NULL)


ViewSQL:
SELECT     EmpID, EmpName AS ManagerName
FROM       dbo.tblEmp
WHERE     (EmpID IN
           (SELECT   ManagerID
            FROM     tblProject))


ViewSQL:
SELECT    EmpID
FROM      dbo.tblEmpProj
WHERE     (ProjID IN
              (SELECT   ProjID
               FROM     tblEmpProj
               WHERE    EmpID = 20))


ViewSQL:
SELECT     EmpID, EmpName
FROM       dbo.tblEmp
WHERE     (EmpID IN
          (SELECT   EmpID
           FROM     tblEmpProj
           WHERE    ProjID IN
                   (SELECT   ProjID
                    FROM     tblProject
                    WHERE    ManagerID = 40)))


ViewSQL:
SELECT     EmpID, EmpName
FROM       dbo.tblEmp
WHERE     (EmpID NOT IN
          (SELECT    EmpID
           FROM      tblEmpProj))


