🗄️ EXPERIMENT-10

Creating SQL Views on COMPANY Database

Instructor: Dr. Mohsin Dar

Position: Assistant Professor, Cloud & Software Operations Cluster (SOCS)

Institution: UPES

Course: Database Systems - MTech First Semester

📚 Introduction to SQL Views

What is a View?

A view is a virtual table based on the result-set of an SQL query. It contains rows and columns just like a real table, but the fields in a view are fields from one or more real tables in the database.

💡 Key Characteristics of Views

  • Virtual Table: Views don't store data physically
  • Dynamic: Data is retrieved from base tables when queried
  • Security: Can hide sensitive columns from users
  • Simplification: Simplify complex queries
  • Consistency: Provide consistent interface to data

COMPANY Database Schema (Experiment 2)

Core Tables:

  • EMPLOYEE: Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno
  • DEPARTMENT: Dname, Dnumber, Mgr_ssn, Mgr_start_date
  • PROJECT: Pname, Pnumber, Plocation, Dnum
  • WORKS_ON: Essn, Pno, Hours
  • DEPENDENT: Essn, Dependent_name, Sex, Bdate, Relationship
  • DEPT_LOCATIONS: Dnumber, Dlocation

Objectives of This Experiment

In this lab, we will create four different views that demonstrate:

  1. Joining tables to create meaningful departmental information
  2. Filtering data based on specific criteria
  3. Using aggregate functions with GROUP BY
  4. Applying HAVING clause for filtered aggregation
Slide 1 of 7

🏢 View 1: Department Management Information

Requirement

Create a view that displays the department name, manager name, and manager salary for every department.

📋 Tables Involved

  • DEPARTMENT: Contains department name and manager SSN
  • EMPLOYEE: Contains employee details including salary

SQL Solution

View 1: DEPT_MANAGER_INFO 
CREATE VIEW DEPT_MANAGER_INFO AS
SELECT 
    D.Dname AS Department_Name,
    CONCAT(E.Fname, ' ', E.Minit, ' ', E.Lname) AS Manager_Name,
    E.Salary AS Manager_Salary
FROM 
    DEPARTMENT D
INNER JOIN 
    EMPLOYEE E ON D.Mgr_ssn = E.Ssn;

Step-by-Step Explanation

  1. CREATE VIEW: Defines a new view named DEPT_MANAGER_INFO
  2. SELECT Clause: Specifies the columns to include in the view
    • D.Dname: Department name from DEPARTMENT table
    • CONCAT function: Combines manager's first, middle, and last names
    • E.Salary: Manager's salary from EMPLOYEE table
  3. FROM & JOIN: Joins DEPARTMENT with EMPLOYEE tables
  4. ON Clause: Matches manager SSN (Mgr_ssn) with employee SSN (Ssn)

Query the View

Querying the View 
SELECT * FROM DEPT_MANAGER_INFO;

Expected Output Sample

Department_Name Manager_Name Manager_Salary
Research Franklin T Wong 40000
Administration Jennifer S Wallace 43000
Headquarters James E Borg 55000
Slide 2 of 7

🔬 View 2: Research Department Employees

Requirement

Create a view that displays the employee name, supervisor name, and employee salary for each employee who works in the 'Research' department.

⚠️ Important Considerations

  • Need to join EMPLOYEE table with itself for supervisor information (self-join)
  • Filter results to only include Research department employees
  • Handle employees who may not have supervisors (NULL values)

SQL Solution

View 2: RESEARCH_DEPT_EMP 
CREATE VIEW RESEARCH_DEPT_EMP AS
SELECT 
    CONCAT(E.Fname, ' ', E.Minit, ' ', E.Lname) AS Employee_Name,
    CONCAT(S.Fname, ' ', S.Minit, ' ', S.Lname) AS Supervisor_Name,
    E.Salary AS Employee_Salary
FROM 
    EMPLOYEE E
LEFT JOIN 
    EMPLOYEE S ON E.Super_ssn = S.Ssn
INNER JOIN 
    DEPARTMENT D ON E.Dno = D.Dnumber
WHERE 
    D.Dname = 'Research';

Detailed Breakdown

1. Self-Join Technique

The EMPLOYEE table is joined with itself using table aliases (E for employee, S for supervisor):

  • E: Represents the employee record
  • S: Represents the supervisor record
  • LEFT JOIN: Ensures employees without supervisors are included

2. Department Filter

The WHERE clause filters for only Research department employees:

  • Joins with DEPARTMENT table using Dno (department number)
  • Checks Dname = 'Research'

Alternative Syntax (Using Subquery)

Alternative Approach 
CREATE VIEW RESEARCH_DEPT_EMP_V2 AS
SELECT 
    CONCAT(E.Fname, ' ', E.Minit, ' ', E.Lname) AS Employee_Name,
    CONCAT(S.Fname, ' ', S.Minit, ' ', S.Lname) AS Supervisor_Name,
    E.Salary AS Employee_Salary
FROM 
    EMPLOYEE E
LEFT JOIN 
    EMPLOYEE S ON E.Super_ssn = S.Ssn
WHERE 
    E.Dno = (SELECT Dnumber FROM DEPARTMENT WHERE Dname = 'Research');

Expected Output Sample

Employee_Name Supervisor_Name Employee_Salary
John B Smith Franklin T Wong 30000
Franklin T Wong James E Borg 40000
Ramesh K Narayan Franklin T Wong 38000
Joyce A English Franklin T Wong 25000
Slide 3 of 7

📊 View 3: Project Statistics (All Projects)

Requirement

Create a view showing project name, controlling department name, number of employees, and total hours worked per week on the project for each project.

📈 Aggregate Functions Required

  • COUNT(): Count number of employees per project
  • SUM(): Calculate total hours worked on each project
  • GROUP BY: Group results by project

SQL Solution

View 3: PROJECT_STATS 
CREATE VIEW PROJECT_STATS AS
SELECT 
    P.Pname AS Project_Name,
    D.Dname AS Controlling_Department,
    COUNT(W.Essn) AS Number_Of_Employees,
    SUM(W.Hours) AS Total_Hours_Per_Week
FROM 
    PROJECT P
INNER JOIN 
    DEPARTMENT D ON P.Dnum = D.Dnumber
LEFT JOIN 
    WORKS_ON W ON P.Pnumber = W.Pno
GROUP BY 
    P.Pnumber, P.Pname, D.Dname;

Comprehensive Explanation

1. Table Relationships

  • PROJECT (P): Main table containing project details
  • DEPARTMENT (D): Joined to get controlling department name
  • WORKS_ON (W): Contains employee-project assignments and hours

2. Why LEFT JOIN for WORKS_ON?

Using LEFT JOIN ensures that projects with no employees assigned are still displayed:

  • Projects with employees: Shows actual count and hours
  • Projects without employees: Shows 0 count and NULL (or 0) hours

3. GROUP BY Clause

Groups results by project to allow aggregate functions:

  • P.Pnumber: Primary grouping identifier
  • P.Pname & D.Dname: Included to display in SELECT

4. Aggregate Functions

  • COUNT(W.Essn): Counts distinct employees (SSNs)
  • SUM(W.Hours): Sums all hours worked on the project

Enhanced Version (With COALESCE)

Handling NULL Values 
CREATE VIEW PROJECT_STATS_ENHANCED AS
SELECT 
    P.Pname AS Project_Name,
    D.Dname AS Controlling_Department,
    COUNT(W.Essn) AS Number_Of_Employees,
    COALESCE(SUM(W.Hours), 0) AS Total_Hours_Per_Week
FROM 
    PROJECT P
INNER JOIN 
    DEPARTMENT D ON P.Dnum = D.Dnumber
LEFT JOIN 
    WORKS_ON W ON P.Pnumber = W.Pno
GROUP BY 
    P.Pnumber, P.Pname, D.Dname;

Expected Output Sample

Project_Name Controlling_Department Number_Of_Employees Total_Hours_Per_Week
ProductX Research 3 52.5
ProductY Research 2 37.5
ProductZ Research 2 50.0
Computerization Headquarters 1 10.0
Reorganization Headquarters 1 10.0
Slide 4 of 7

👥 View 4: Multi-Employee Project Statistics

Requirement

Create a view showing project name, controlling department name, number of employees, and total hours worked per week on the project for projects with more than one employee working on it.

🔍 Key Difference from View 3

This view adds a HAVING clause to filter only projects with multiple employees (> 1 employee).

SQL Solution

View 4: MULTI_EMP_PROJECT_STATS 
CREATE VIEW MULTI_EMP_PROJECT_STATS AS
SELECT 
    P.Pname AS Project_Name,
    D.Dname AS Controlling_Department,
    COUNT(W.Essn) AS Number_Of_Employees,
    SUM(W.Hours) AS Total_Hours_Per_Week
FROM 
    PROJECT P
INNER JOIN 
    DEPARTMENT D ON P.Dnum = D.Dnumber
INNER JOIN 
    WORKS_ON W ON P.Pnumber = W.Pno
GROUP BY 
    P.Pnumber, P.Pname, D.Dname
HAVING 
    COUNT(W.Essn) > 1;

WHERE vs HAVING: Critical Distinction

Understanding the Difference

Aspect WHERE Clause HAVING Clause
When Applied Before grouping After grouping
Filters Individual rows Grouped results
Can Use Aggregates No Yes
Example WHERE Salary > 30000 HAVING COUNT(*) > 1

Why INNER JOIN Instead of LEFT JOIN?

In this view, we use INNER JOIN for WORKS_ON because:

  • We only want projects that have employees assigned
  • Projects with zero employees cannot have "more than one employee"
  • INNER JOIN automatically excludes projects without assignments

Step-by-Step Execution Flow

Query Processing Order

  1. FROM & JOIN: Combine PROJECT, DEPARTMENT, and WORKS_ON tables
  2. GROUP BY: Group results by project
  3. Aggregate Functions: Calculate COUNT and SUM for each group
  4. HAVING: Filter groups to only those with COUNT(Essn) > 1
  5. SELECT: Display the final columns

Comparison with View 3

Side-by-Side Comparison 
-- View 3: All Projects (uses LEFT JOIN)
LEFT JOIN WORKS_ON W ON P.Pnumber = W.Pno
-- No HAVING clause

-- View 4: Multi-Employee Projects (uses INNER JOIN)
INNER JOIN WORKS_ON W ON P.Pnumber = W.Pno
HAVING COUNT(W.Essn) > 1

Expected Output Sample

Project_Name Controlling_Department Number_Of_Employees Total_Hours_Per_Week
ProductX Research 3 52.5
ProductY Research 2 37.5
ProductZ Research 2 50.0

✅ Notice the Difference

Projects like "Computerization" and "Reorganization" (which have only 1 employee each) are excluded from this view, whereas they appeared in View 3.

Slide 5 of 7

📝 Summary & Best Practices

Complete View Statements

All Four Views - Complete Script 
-- View 1: Department Manager Information
CREATE VIEW DEPT_MANAGER_INFO AS
SELECT 
    D.Dname AS Department_Name,
    CONCAT(E.Fname, ' ', E.Minit, ' ', E.Lname) AS Manager_Name,
    E.Salary AS Manager_Salary
FROM DEPARTMENT D
INNER JOIN EMPLOYEE E ON D.Mgr_ssn = E.Ssn;

-- View 2: Research Department Employees
CREATE VIEW RESEARCH_DEPT_EMP AS
SELECT 
    CONCAT(E.Fname, ' ', E.Minit, ' ', E.Lname) AS Employee_Name,
    CONCAT(S.Fname, ' ', S.Minit, ' ', S.Lname) AS Supervisor_Name,
    E.Salary AS Employee_Salary
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE S ON E.Super_ssn = S.Ssn
INNER JOIN DEPARTMENT D ON E.Dno = D.Dnumber
WHERE D.Dname = 'Research';

-- View 3: All Project Statistics
CREATE VIEW PROJECT_STATS AS
SELECT 
    P.Pname AS Project_Name,
    D.Dname AS Controlling_Department,
    COUNT(W.Essn) AS Number_Of_Employees,
    SUM(W.Hours) AS Total_Hours_Per_Week
FROM PROJECT P
INNER JOIN DEPARTMENT D ON P.Dnum = D.Dnumber
LEFT JOIN WORKS_ON W ON P.Pnumber = W.Pno
GROUP BY P.Pnumber, P.Pname, D.Dname;

-- View 4: Multi-Employee Project Statistics
CREATE VIEW MULTI_EMP_PROJECT_STATS AS
SELECT 
    P.Pname AS Project_Name,
    D.Dname AS Controlling_Department,
    COUNT(W.Essn) AS Number_Of_Employees,
    SUM(W.Hours) AS Total_Hours_Per_Week
FROM PROJECT P
INNER JOIN DEPARTMENT D ON P.Dnum = D.Dnumber
INNER JOIN WORKS_ON W ON P.Pnumber = W.Pno
GROUP BY P.Pnumber, P.Pname, D.Dname
HAVING COUNT(W.Essn) > 1;

Key Concepts Covered

Concept View(s) Description
INNER JOIN 1, 2, 4 Retrieves matching rows from both tables
LEFT JOIN 2, 3 Includes all rows from left table, even without matches
Self-Join 2 Joining a table with itself (for hierarchical data)
GROUP BY 3, 4 Groups rows for aggregate calculations
HAVING 4 Filters grouped results using aggregate conditions
Aggregate Functions 3, 4 COUNT(), SUM() for calculations
String Functions 1, 2 CONCAT() for name formatting

View Management Commands

Useful View Operations 
-- Show all views in database
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Describe view structure
DESCRIBE DEPT_MANAGER_INFO;

-- Show view definition
SHOW CREATE VIEW DEPT_MANAGER_INFO;

-- Drop a view
DROP VIEW IF EXISTS DEPT_MANAGER_INFO;

-- Modify an existing view
CREATE OR REPLACE VIEW DEPT_MANAGER_INFO AS
SELECT /* new definition */;

Best Practices

✨ View Design Guidelines

  1. Meaningful Names: Use descriptive names that indicate content
  2. Column Aliases: Always use clear AS aliases for readability
  3. Documentation: Add comments explaining complex views
  4. Performance: Avoid overly complex views with many joins
  5. Security: Use views to hide sensitive columns
  6. Maintenance: Keep view definitions simple and focused
  7. Testing: Test views with various data scenarios
  8. Dependencies: Be aware of view dependencies on base tables

Common Pitfalls to Avoid

⚠️ Watch Out For

  • Circular Dependencies: Don't create views that reference each other
  • SELECT *: Avoid using SELECT * in view definitions
  • Missing GROUP BY: Include all non-aggregated columns in GROUP BY
  • NULL Handling: Consider using COALESCE for NULL values
  • WHERE vs HAVING: Use WHERE for row filtering, HAVING for group filtering
  • Performance: Complex views can slow down queries significantly
Slide 6 of 7

🎯 Knowledge Check Quiz

Question 1

What is the main difference between View 3 and View 4?

A) View 3 uses INNER JOIN while View 4 uses LEFT JOIN
B) View 3 doesn't use GROUP BY while View 4 does
C) View 4 includes a HAVING clause to filter projects with more than one employee
D) View 3 shows employee names while View 4 shows project names

Question 2

Why do we use LEFT JOIN when joining EMPLOYEE with itself in View 2?

A) To improve query performance
B) To include employees who don't have supervisors
C) To avoid duplicate rows
D) LEFT JOIN is not used in View 2

Question 3

Which clause is used to filter grouped results in SQL?

A) WHERE
B) HAVING
C) FILTER
D) GROUP FILTER

Question 4

What does COUNT(W.Essn) do in the project statistics views?

A) Counts the number of employees working on each project
B) Counts the total hours worked on the project
C) Counts the number of projects each employee works on
D) Counts the number of departments in the company

Question 5

What is a view in SQL?

A) A physical table that stores data permanently
B) A virtual table based on a SELECT query that doesn't store data
C) A backup copy of a table
D) A special type of index for faster queries
Slide 7 of 7

📚 Additional Resources & Practice

Practice Exercises

Exercise 1: Create Your Own View

Create a view that shows employee names and their total hours worked across all projects.

Exercise 2: Modify Existing View

Modify View 1 to also include the manager's start date and the number of employees in each department.

Exercise 3: Complex View

Create a view that shows department name, average employee salary, and number of projects controlled by that department.

Exercise 4: View with Subquery

Create a view showing employees who work more total hours than the company average.

Real-World Applications

💼 When to Use Views

  • Reporting: Create standardized reports that multiple users can access
  • Security: Limit access to sensitive data by exposing only necessary columns
  • Simplification: Hide complex joins from end users
  • Data Abstraction: Provide a consistent interface even if underlying schema changes
  • Calculated Fields: Pre-compute derived values for easier querying
  • Legacy Support: Maintain compatibility when restructuring databases

Performance Considerations

⚡ Optimization Tips

  • Indexing: Ensure base tables have appropriate indexes
  • Materialized Views: Consider materialized views for expensive computations
  • Avoid Nesting: Don't create views based on other views excessively
  • Limit Joins: Too many joins in a view can slow performance
  • Monitor Usage: Track which views are actually used

Further Reading

Recommended Topics 
1. Materialized Views
   - When and how to use them
   - Refresh strategies

2. Updatable Views
   - Conditions for updating data through views
   - WITH CHECK OPTION

3. Indexed Views
   - Creating indexes on views
   - Performance benefits

4. View Security
   - GRANT permissions on views
   - Role-based access control

5. View Dependencies
   - Managing dependencies
   - Impact of table schema changes

Lab Submission Checklist

✅ Before Submitting

  1. All four views created successfully
  2. Test each view with SELECT * statements
  3. Verify output matches expected results
  4. Document any assumptions made
  5. Include screenshots or query results
  6. Comment your code appropriately
  7. Test edge cases (NULL values, empty results)
  8. Verify view definitions with SHOW CREATE VIEW

Contact Information

For Questions or Clarifications:

Dr. Mohsin Dar

Assistant Professor, SOCS | UPES

Office Hours: [To be announced]

Slide 8 of 8 (Bonus)