COMPANY Database Schema Lab Manual(aau)

Microsoft SQL Server

Topics: SELECT, WHERE, LIKE, TOP, aggregate functions, GROUP BY, HAVING, joins, self joins, subqueries, UNION, INTERSECT,
EXCEPT, UPDATE, ALTER, DELETE, foreign-key constraints, SQL Server views, CREATE VIEW, ALTER VIEW, DROP VIEW, and WITH
CHECK OPTION.

Field Value
Course Fundamentals of Database Systems / Database Systems Lab
DBMS Microsoft SQL Server
Schema Company database schema
Student name
ID number
Section
Instructor

COMPANY Database Lab Manual - Microsoft SQL Server

How to Use This Lab Manual
• Run the SELECT statements first. They are read-only and safe for practice.
• Run UPDATE, ALTER, and DELETE statements only on a lab copy of the database.
• Many examples use sample values such as Ssn = 123456789, Dnumber = 5, and Pnumber = 1. Replace them with values
that exist in your database.
• For destructive commands, use BEGIN TRANSACTION first and ROLLBACK after testing unless your instructor asks you to
COMMIT.
• This document uses SQL Server syntax, including SELECT TOP, INTERSECT, EXCEPT, EXEC sp_rename, and ALTER COLUMN.
Important safety rule: Never execute UPDATE or DELETE without a WHERE clause unless the exercise intentionally requires changing
all rows.

Assumed COMPANY Schema

The examples assume the following classic COMPANY database tables and attributes.
Table Important columns
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
Work_on Essn, Pno, Hours
Dept_Location Dnumber, Dlocation
Dependent Essn, Dependent_name, Sex, Bdate, Relationship
Typical primary keys:
Employee: Ssn
Department: Dnumber
Project: Pnumber
Work_on: (Essn, Pno)
Dept_Location: (Dnumber, Dlocation)
Dependent: (Essn, Dependent_name)

Typical foreign-key relationships:

Child table column Parent table column Meaning
[Link] [Link] An employee works for one department.
Employee.Super_ssn [Link] An employee may have a supervisor.
Department.Mgr_ssn [Link] A department is managed by an employee.
[Link] [Link] A project is controlled by a department.
Work_on.Essn [Link] An employee works on a project.
Work_on.Pno [Link] A project has employee work assignments.
Dept_Location.Dnumber [Link] A department can have multiple locations.
[Link] [Link] An employee can have dependents.
Naming note: Some textbooks use WORKS_ON or DEPT_LOCATIONS. This manual uses the names you provided: Work_on and
Dept_Location. If your database uses different names, edit only the table names in the queries.

COMPANY Database Lab Manual - Microsoft SQL Server

Part 1 - SELECT Statement Practice
This section moves from simple queries to advanced joins, set operations, and nested subqueries.

1. Display all employees

Level: Simple Concept: SELECT *
SELECT *
FROM Employee;

Shows every column and every row in the Employee table.

2. Display selected employee columns

Level: Simple Concept: Projection
SELECT Fname, Lname, Salary
FROM Employee;

Projection means selecting only the columns needed.

3. Display employees with salary greater than 30000

Level: Simple Concept: WHERE condition
SELECT Fname, Lname, Salary
FROM Employee
WHERE Salary > 30000;

4. Display female employees in department 5

Level: Simple to intermediate Concept: WHERE with AND
SELECT Fname, Lname, Sex, Dno
FROM Employee
WHERE Sex = 'F'
AND Dno = 5;

5. Display employees with salaries between 30000 and 50000

Level: Simple Concept: BETWEEN
SELECT Fname, Lname, Salary
FROM Employee
WHERE Salary BETWEEN 30000 AND 50000;

BETWEEN includes both boundary values.

6. Display employees whose first name starts with J

Level: Simple Concept: LIKE pattern matching
SELECT Fname, Lname
FROM Employee
WHERE Fname LIKE 'J%';

The % symbol means zero or more characters.

7. Display employees whose address contains Houston

Level: Simple Concept: LIKE with wildcard on both sides
SELECT Fname, Lname, Address
FROM Employee
WHERE Address LIKE '%Houston%';

8. Display employees whose second letter is a

Level: Intermediate Concept: LIKE with single-character wildcard
SELECT Fname, Lname
FROM Employee
WHERE Fname LIKE '_a%';

The underscore (_) represents exactly one character.

COMPANY Database Lab Manual - Microsoft SQL Server

9. Display employees whose first name starts with J or M
Level: Intermediate Concept: SQL Server bracket pattern
SELECT Fname, Lname
FROM Employee
WHERE Fname LIKE '[JM]%';

In SQL Server, [JM] means the first character can be J or M.

10. Display employees ordered by salary from highest to lowest

Level: Simple Concept: ORDER BY
SELECT Fname, Lname, Salary
FROM Employee
ORDER BY Salary DESC;

11. Display the top 3 highest-paid employees

Level: Simple to intermediate Concept: TOP and ORDER BY
SELECT TOP 3 Fname, Lname, Salary
FROM Employee
ORDER BY Salary DESC;

TOP must normally be combined with ORDER BY when the result should be meaningful.

12. Display the top salaries including ties

Level: Intermediate Concept: TOP WITH TIES
SELECT TOP 3 WITH TIES Fname, Lname, Salary
FROM Employee
ORDER BY Salary DESC;

WITH TIES returns extra rows if more employees share the last selected salary.

13. Display distinct department numbers used by employees

Level: Simple Concept: DISTINCT
SELECT DISTINCT Dno
FROM Employee
ORDER BY Dno;

14. Count the total number of employees

Level: Simple Concept: COUNT aggregate
SELECT COUNT(*) AS TotalEmployees
FROM Employee;

15. Find average, minimum, maximum, and total salary

Level: Simple to intermediate Concept: AVG, MIN, MAX, SUM
SELECT
AVG(Salary) AS AverageSalary,
MIN(Salary) AS MinimumSalary,
MAX(Salary) AS MaximumSalary,
SUM(Salary) AS TotalSalary
FROM Employee;

16. Find total salary by department

Level: Intermediate Concept: GROUP BY and SUM
SELECT
Dno,
SUM(Salary) AS TotalDepartmentSalary
FROM Employee
GROUP BY Dno
ORDER BY Dno;

17. Find average salary by department

COMPANY Database Lab Manual - Microsoft SQL Server

Level: Intermediate Concept: GROUP BY and AVG
SELECT
Dno,
AVG(Salary) AS AverageDepartmentSalary
FROM Employee
GROUP BY Dno
ORDER BY Dno;

18. Display departments whose average salary is greater than 30000

Level: Intermediate Concept: GROUP BY and HAVING
SELECT
Dno,
AVG(Salary) AS AverageSalary
FROM Employee
GROUP BY Dno
HAVING AVG(Salary) > 30000
ORDER BY AverageSalary DESC;

WHERE filters rows before grouping; HAVING filters groups after grouping.

19. Display employee names with department names

Level: Intermediate Concept: INNER JOIN
SELECT
[Link],
[Link],
[Link]
FROM Employee AS E
INNER JOIN Department AS D
ON [Link] = [Link]
ORDER BY [Link], [Link];

20. Display department managers

Level: Intermediate Concept: JOIN between Department and Employee
SELECT
[Link],
[Link] AS ManagerFirstName,
[Link] AS ManagerLastName,
D.Mgr_start_date
FROM Department AS D
INNER JOIN Employee AS E
ON D.Mgr_ssn = [Link]
ORDER BY [Link];

21. Display employees and their supervisors

Level: Intermediate Concept: SELF JOIN and LEFT JOIN
SELECT
[Link] AS EmployeeFirstName,
[Link] AS EmployeeLastName,
[Link] AS SupervisorFirstName,
[Link] AS SupervisorLastName
FROM Employee AS E
LEFT JOIN Employee AS S
ON E.Super_ssn = [Link]
ORDER BY [Link];

LEFT JOIN is used because some employees may not have supervisors.

22. Display department names and locations

Level: Simple to intermediate Concept: JOIN with multivalued location table
SELECT
[Link],
[Link]
FROM Department AS D
INNER JOIN Dept_Location AS DL

COMPANY Database Lab Manual - Microsoft SQL Server

 ON [Link] = [Link]
ORDER BY [Link], [Link];

23. Display employee, project, and hours worked

Level: Intermediate Concept: Three-table JOIN
SELECT
[Link],
[Link],
[Link],
[Link]
FROM Employee AS E
INNER JOIN Work_on AS W
ON [Link] = [Link]
INNER JOIN Project AS P
ON [Link] = [Link]
ORDER BY [Link], [Link];

24. Find total hours worked by each employee

Level: Intermediate Concept: JOIN, GROUP BY, SUM
SELECT
[Link],
[Link],
[Link],
SUM([Link]) AS TotalHours
FROM Employee AS E
INNER JOIN Work_on AS W
ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY TotalHours DESC;

25. Display employees who worked more than 40 total hours

Level: Intermediate Concept: HAVING with SUM
SELECT
[Link],
[Link],
[Link],
SUM([Link]) AS TotalHours
FROM Employee AS E
INNER JOIN Work_on AS W
ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
HAVING SUM([Link]) > 40
ORDER BY TotalHours DESC;

26. Display projects with total assigned hours

Level: Intermediate Concept: LEFT JOIN and aggregate
SELECT
[Link],
[Link],
COALESCE(SUM([Link]), 0) AS TotalProjectHours
FROM Project AS P
LEFT JOIN Work_on AS W
ON [Link] = [Link]
GROUP BY [Link], [Link]
ORDER BY TotalProjectHours DESC;

LEFT JOIN includes projects even when no employee is assigned.

27. Display projects with total hours greater than 50

Level: Intermediate Concept: JOIN, GROUP BY, HAVING
SELECT
[Link],
[Link],
SUM([Link]) AS TotalProjectHours
FROM Project AS P
INNER JOIN Work_on AS W

COMPANY Database Lab Manual - Microsoft SQL Server

 ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link]) > 50
ORDER BY TotalProjectHours DESC;

28. Display employees who have dependents

Level: Simple to intermediate Concept: JOIN and DISTINCT
SELECT DISTINCT
[Link],
[Link]
FROM Employee AS E
INNER JOIN Dependent AS DP
ON [Link] = [Link]
ORDER BY [Link];

DISTINCT avoids repeating an employee who has more than one dependent.

29. Display employees who do not have dependents

Level: Intermediate Concept: NOT EXISTS subquery
SELECT
[Link],
[Link]
FROM Employee AS E
WHERE NOT EXISTS (
SELECT 1
FROM Dependent AS DP
WHERE [Link] = [Link]
)
ORDER BY [Link];

30. Display projects controlled by the Research department

Level: Intermediate Concept: JOIN with WHERE
SELECT
[Link],
[Link],
[Link]
FROM Project AS P
INNER JOIN Department AS D
ON [Link] = [Link]
WHERE [Link] = 'Research'
ORDER BY [Link];

31. Display employees whose salary is greater than the company average
Level: Intermediate Concept: Scalar subquery
SELECT
Fname,
Lname,
Salary
FROM Employee
WHERE Salary > (
SELECT AVG(Salary)
FROM Employee
)
ORDER BY Salary DESC;

32. Display employees earning above their department average

Level: Advanced Concept: Correlated subquery
SELECT
[Link],
[Link],
[Link],
[Link]
FROM Employee AS E
WHERE [Link] > (
SELECT AVG([Link])
FROM Employee AS E2

COMPANY Database Lab Manual - Microsoft SQL Server

 WHERE [Link] = [Link]
)
ORDER BY [Link], [Link] DESC;

The inner query depends on the department of the current employee row.

33. Display departments with more than 3 employees

Level: Intermediate Concept: JOIN, GROUP BY, HAVING
SELECT
[Link],
COUNT([Link]) AS NumberOfEmployees
FROM Department AS D
INNER JOIN Employee AS E
ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT([Link]) > 3
ORDER BY NumberOfEmployees DESC;

34. Display the department with the highest average salary

Level: Advanced Concept: TOP with grouped result
SELECT TOP 1
[Link],
AVG([Link]) AS AverageSalary
FROM Department AS D
INNER JOIN Employee AS E
ON [Link] = [Link]
GROUP BY [Link]
ORDER BY AVG([Link]) DESC;

35. Display employees who work on project 1 or project 2

Level: Intermediate Concept: UNION
SELECT Essn
FROM Work_on
WHERE Pno = 1

UNION

SELECT Essn
FROM Work_on
WHERE Pno = 2;

UNION removes duplicate rows. Use UNION ALL if duplicates should be kept.

36. Display names of employees who work on project 1 or project 2

Level: Intermediate Concept: UNION inside IN
SELECT [Link], [Link]
FROM Employee AS E
WHERE [Link] IN (
SELECT Essn
FROM Work_on
WHERE Pno = 1

UNION

SELECT Essn
FROM Work_on
WHERE Pno = 2
)
ORDER BY [Link];

37. Display employees who work on both project 1 and project 2

Level: Advanced Concept: INTERSECT
SELECT [Link], [Link]
FROM Employee AS E
WHERE [Link] IN (
SELECT Essn

COMPANY Database Lab Manual - Microsoft SQL Server

 FROM Work_on
WHERE Pno = 1

INTERSECT

SELECT Essn
FROM Work_on
WHERE Pno = 2
)
ORDER BY [Link];

INTERSECT returns rows common to both SELECT results.

38. Display employees who work on project 1 but not project 2

Level: Advanced Concept: EXCEPT
SELECT [Link], [Link]
FROM Employee AS E
WHERE [Link] IN (
SELECT Essn
FROM Work_on
WHERE Pno = 1

EXCEPT

SELECT Essn
FROM Work_on
WHERE Pno = 2
)
ORDER BY [Link];

EXCEPT returns rows from the first query that are not in the second query.

39. Display all person names from employees and dependents

Level: Intermediate Concept: UNION across different tables
SELECT Fname AS PersonName
FROM Employee

UNION

SELECT Dependent_name AS PersonName

FROM Dependent
ORDER BY PersonName;

Both SELECT statements in a UNION must return the same number of columns and compatible data types.

40. Display employees who work on at least two projects

Level: Advanced Concept: GROUP BY and COUNT
SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS NumberOfProjects
FROM Employee AS E
INNER JOIN Work_on AS W
ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
HAVING COUNT([Link]) >= 2
ORDER BY NumberOfProjects DESC, [Link];

41. Display employees working on projects controlled by their own department

Level: Advanced Concept: Multi-table join with comparison
SELECT DISTINCT
[Link],
[Link],
[Link],
[Link]
FROM Employee AS E
INNER JOIN Department AS D
COMPANY Database Lab Manual - Microsoft SQL Server
 ON [Link] = [Link]
INNER JOIN Project AS P
ON [Link] = [Link]
INNER JOIN Work_on AS W
ON [Link] = [Link]
AND [Link] = [Link]
ORDER BY [Link], [Link], [Link];

42. Display department summary without double counting

Level: Advanced Concept: CTE and LEFT JOIN
WITH EmployeeCount AS (
SELECT Dno, COUNT(*) AS NumberOfEmployees
FROM Employee
GROUP BY Dno
),
ProjectCount AS (
SELECT Dnum, COUNT(*) AS NumberOfProjects
FROM Project
GROUP BY Dnum
)
SELECT
[Link],
[Link],
ISNULL([Link], 0) AS NumberOfEmployees,
ISNULL([Link], 0) AS NumberOfProjects
FROM Department AS D
LEFT JOIN EmployeeCount AS EC
ON [Link] = [Link]
LEFT JOIN ProjectCount AS PC
ON [Link] = [Link]
ORDER BY [Link];

The CTEs prevent multiplication of rows that can happen when joining employees and projects directly.

43. Display employees who work on all projects

Level: Very advanced Concept: Relational division using NOT EXISTS
SELECT
[Link],
[Link]
FROM Employee AS E
WHERE NOT EXISTS (
SELECT [Link]
FROM Project AS P
WHERE NOT EXISTS (
SELECT 1
FROM Work_on AS W
WHERE [Link] = [Link]
AND [Link] = [Link]
)
)
ORDER BY [Link];

Meaning: find employees for whom there is no project that they do not work on.

COMPANY Database Lab Manual - Microsoft SQL Server

Part 2 - UPDATE, MODIFY, RENAME, and ALTER Practice
SQL Server transaction pattern: For lab practice, test data-changing commands inside a transaction. Use ROLLBACK to undo the test
or COMMIT only when the change is required.
BEGIN TRANSACTION;

-- Put your UPDATE, ALTER, or DELETE statement here.

ROLLBACK;
-- Use COMMIT instead of ROLLBACK only when you intentionally want to save the change.

1. Increase every employee salary by 10 percent

Level: Basic UPDATE Concept: UPDATE without WHERE changes all rows
BEGIN TRANSACTION;

UPDATE Employee
SET Salary = Salary * 1.10;

SELECT Fname, Lname, Salary

FROM Employee;

ROLLBACK;

This intentionally updates every employee. In a real system, confirm this is required before using COMMIT.

2. Increase salary of employees in department 5 only

Level: UPDATE with WHERE Concept: Selective update
BEGIN TRANSACTION;

UPDATE Employee
SET Salary = Salary * 1.10
WHERE Dno = 5;

SELECT Fname, Lname, Dno, Salary

FROM Employee
WHERE Dno = 5;

ROLLBACK;

3. Move one employee to another department

Level: UPDATE foreign key column Concept: Foreign key validation
BEGIN TRANSACTION;

UPDATE Employee
SET Dno = 4
WHERE Ssn = '123456789';

SELECT Fname, Lname, Dno

FROM Employee
WHERE Ssn = '123456789';

ROLLBACK;

This succeeds only if [Link] = 4 exists.

4. Change hours for one employee on one project

Level: UPDATE composite-key row Concept: Composite key in Work_on
BEGIN TRANSACTION;

UPDATE Work_on
SET Hours = 20
WHERE Essn = '123456789'
AND Pno = 1;

SELECT *

COMPANY Database Lab Manual - Microsoft SQL Server

 FROM Work_on
WHERE Essn = '123456789'
AND Pno = 1;

ROLLBACK;

5. Give different salary increases by department

Level: Conditional UPDATE Concept: CASE expression
BEGIN TRANSACTION;

UPDATE Employee
SET Salary =
CASE
WHEN Dno = 1 THEN Salary * 1.15
WHEN Dno = 4 THEN Salary * 1.10
WHEN Dno = 5 THEN Salary * 1.05
ELSE Salary
END;

SELECT Fname, Lname, Dno, Salary

FROM Employee;

ROLLBACK;

6. Increase salary for employees in the Research department

Level: UPDATE with JOIN Concept: SQL Server UPDATE FROM syntax
BEGIN TRANSACTION;

UPDATE E
SET [Link] = [Link] * 1.10
FROM Employee AS E
INNER JOIN Department AS D
ON [Link] = [Link]
WHERE [Link] = 'Research';

SELECT [Link], [Link], [Link], [Link]

FROM Employee AS E
INNER JOIN Department AS D
ON [Link] = [Link]
WHERE [Link] = 'Research';

ROLLBACK;

7. Change the manager of a department

Level: UPDATE parent relationship Concept: Department.Mgr_ssn references [Link]
BEGIN TRANSACTION;

UPDATE Department
SET Mgr_ssn = '987654321',
Mgr_start_date = GETDATE()
WHERE Dnumber = 5;

SELECT Dname, Dnumber, Mgr_ssn, Mgr_start_date

FROM Department
WHERE Dnumber = 5;

ROLLBACK;

This succeeds only if [Link] = 987654321 exists.

8. Add an Email column to Employee

Level: ALTER TABLE ADD Concept: Schema change
ALTER TABLE Employee
ADD Email VARCHAR(100);

Run this only once. Running it again fails because the column already exists.

COMPANY Database Lab Manual - Microsoft SQL Server

9. Add a Phone column with a named default constraint
Level: ALTER TABLE ADD with DEFAULT Concept: Schema change and default value
ALTER TABLE Employee
ADD Phone VARCHAR(20)
CONSTRAINT DF_Employee_Phone DEFAULT ('Not Provided');

A named constraint is easier to drop later.

10. Modify the Salary column type

Level: ALTER COLUMN Concept: SQL Server modify-column syntax
ALTER TABLE Employee
ALTER COLUMN Salary DECIMAL(10, 2);

In SQL Server, MODIFY is not used; use ALTER COLUMN.

11. Rename the Address column to HomeAddress

Level: RENAME column Concept: EXEC sp_rename
EXEC sp_rename '[Link]', 'HomeAddress', 'COLUMN';

After this change, queries must use HomeAddress instead of Address.

12. Rename Dept_Location table to Department_Location

Level: RENAME table Concept: EXEC sp_rename
EXEC sp_rename 'Dept_Location', 'Department_Location';

Renaming a table may break existing queries, views, or stored procedures.

13. Add a salary check constraint

Level: ALTER TABLE ADD CONSTRAINT Concept: CHECK constraint
ALTER TABLE Employee
ADD CONSTRAINT CHK_Employee_Salary_NonNegative
CHECK (Salary >= 0);

This prevents negative salary values.

14. Add a unique constraint to department name

Level: ALTER TABLE ADD CONSTRAINT Concept: UNIQUE constraint
ALTER TABLE Department
ADD CONSTRAINT UQ_Department_Dname
UNIQUE (Dname);

This prevents duplicate department names.

15. Drop the Phone column

Level: ALTER TABLE DROP COLUMN Concept: Schema change
ALTER TABLE Employee
DROP COLUMN Phone;

If a default constraint exists on Phone, drop the default constraint first, then drop the column.

16. Drop a named default constraint

Level: ALTER TABLE DROP CONSTRAINT Concept: Constraint management
ALTER TABLE Employee
DROP CONSTRAINT DF_Employee_Phone;

Use this before dropping Phone if DF_Employee_Phone exists.

COMPANY Database Lab Manual - Microsoft SQL Server

Part 3 - DELETE Statements and Foreign-Key Relationships
Foreign keys control whether a row can be deleted. In SQL Server, the default behavior is usually NO ACTION, meaning the parent row
cannot be deleted while child rows still reference it.
Delete target Possible child rows that may block deletion
Employee [Link], Work_on.Essn, Department.Mgr_ssn, Employee.Super_ssn
Department [Link], [Link], Dept_Location.Dnumber
Project Work_on.Pno
Dept_Location Usually a child table; deleting one location is normally allowed.
Dependent Usually a child table; deleting one dependent is normally allowed.
Work_on Usually a child table; deleting one assignment is normally allowed.
Delete rule: Delete child rows first, then delete the parent row. Alternatively, reassign child rows or define ON DELETE CASCADE / ON
DELETE SET NULL where appropriate.

1. Delete one dependent of an employee

Level: Usually safe Concept: Delete from child table
BEGIN TRANSACTION;

DELETE FROM Dependent

WHERE Essn = '123456789'
AND Dependent_name = 'Alice';

SELECT *
FROM Dependent
WHERE Essn = '123456789';

ROLLBACK;

Dependent is normally a child table of Employee.

2. Delete one employee-project assignment

Level: Usually safe Concept: Delete from relationship table
BEGIN TRANSACTION;

DELETE FROM Work_on

WHERE Essn = '123456789'
AND Pno = 1;

SELECT *
FROM Work_on
WHERE Essn = '123456789';

ROLLBACK;

This removes the assignment only; it does not delete the employee or the project.

3. Try to delete an employee who is referenced by other rows

Level: Expected to fail if child rows exist Concept: Foreign-key conflict
BEGIN TRY
BEGIN TRANSACTION;

DELETE FROM Employee

WHERE Ssn = '123456789';

COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

COMPANY Database Lab Manual - Microsoft SQL Server

SQL Server may return an error such as: The DELETE statement conflicted with the REFERENCE constraint.

4. Correctly delete an employee after removing related child rows

Level: Advanced Concept: Delete order with multiple relationships
BEGIN TRANSACTION;

-- 1. Remove dependent rows.

DELETE FROM Dependent
WHERE Essn = '123456789';

-- 2. Remove project assignments.

DELETE FROM Work_on
WHERE Essn = '123456789';

-- 3. If this employee supervises others, reassign or remove supervisor reference.

UPDATE Employee
SET Super_ssn = NULL
WHERE Super_ssn = '123456789';

-- 4. If this employee manages a department, assign another manager or allow NULL.

UPDATE Department
SET Mgr_ssn = NULL
WHERE Mgr_ssn = '123456789';

-- 5. Now delete the employee.

DELETE FROM Employee
WHERE Ssn = '123456789';

ROLLBACK;

This works only if Super_ssn and Mgr_ssn allow NULL. Otherwise, assign another valid employee SSN instead of NULL.

5. Delete an employee by assigning another manager and supervisor first

Level: Advanced Concept: Reassignment instead of NULL
BEGIN TRANSACTION;

UPDATE Employee
SET Super_ssn = '987654321'
WHERE Super_ssn = '123456789';

UPDATE Department
SET Mgr_ssn = '987654321',
Mgr_start_date = GETDATE()
WHERE Mgr_ssn = '123456789';

DELETE FROM Dependent

WHERE Essn = '123456789';

DELETE FROM Work_on

WHERE Essn = '123456789';

DELETE FROM Employee

WHERE Ssn = '123456789';

ROLLBACK;

Use this version when supervisor or manager columns are NOT NULL.

6. Try to delete a project that has work assignments

Level: Expected to fail if child rows exist Concept: Project parent row referenced by Work_on
BEGIN TRY
BEGIN TRANSACTION;

DELETE FROM Project

WHERE Pnumber = 1;

COMMIT;
END TRY
BEGIN CATCH
COMPANY Database Lab Manual - Microsoft SQL Server
 IF @@TRANCOUNT > 0
ROLLBACK;

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

This fails when rows in Work_on reference the project.

7. Correctly delete a project and its assignments

Level: Intermediate Concept: Delete child rows first
BEGIN TRANSACTION;

DELETE FROM Work_on

WHERE Pno = 1;

DELETE FROM Project

WHERE Pnumber = 1;

ROLLBACK;

Delete Work_on rows before deleting the Project row.

8. Delete one department location

Level: Usually safe Concept: Delete from child table
BEGIN TRANSACTION;

DELETE FROM Dept_Location

WHERE Dnumber = 5
AND Dlocation = 'Houston';

SELECT *
FROM Dept_Location
WHERE Dnumber = 5;

ROLLBACK;

This removes only one location for the department.

9. Try to delete a department that still has employees, projects, or locations

Level: Expected to fail if child rows exist Concept: Department parent row referenced by other tables
BEGIN TRY
BEGIN TRANSACTION;

DELETE FROM Department

WHERE Dnumber = 5;

COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

This may fail because [Link], [Link], or Dept_Location.Dnumber references [Link].

10. Delete a department by transferring employees first

Level: Advanced Concept: Reassign child rows, then delete
BEGIN TRANSACTION;

-- Move employees from department 5 to department 4.

UPDATE Employee
SET Dno = 4
COMPANY Database Lab Manual - Microsoft SQL Server
 WHERE Dno = 5;

-- Remove assignments for projects controlled by department 5.

DELETE FROM Work_on
WHERE Pno IN (
SELECT Pnumber
FROM Project
WHERE Dnum = 5
);

-- Delete projects controlled by department 5.

DELETE FROM Project
WHERE Dnum = 5;

-- Delete department locations.

DELETE FROM Dept_Location
WHERE Dnumber = 5;

-- Delete the department.

DELETE FROM Department
WHERE Dnumber = 5;

ROLLBACK;

Department 4 must already exist. This version keeps employees by moving them to another department.

11. Delete a department by setting employee department to NULL

Level: Advanced Concept: Use only if [Link] allows NULL
BEGIN TRANSACTION;

UPDATE Employee
SET Dno = NULL
WHERE Dno = 5;

DELETE FROM Work_on

WHERE Pno IN (
SELECT Pnumber
FROM Project
WHERE Dnum = 5
);

DELETE FROM Project

WHERE Dnum = 5;

DELETE FROM Dept_Location

WHERE Dnumber = 5;

DELETE FROM Department

WHERE Dnumber = 5;

ROLLBACK;

This works only when [Link] is nullable.

12. Delete employees who have no project assignments and no blocking references
Level: Advanced Concept: DELETE with NOT EXISTS safety checks
BEGIN TRANSACTION;

DELETE FROM Employee

WHERE NOT EXISTS (
SELECT 1
FROM Work_on AS W
WHERE [Link] = [Link]
)
AND NOT EXISTS (
SELECT 1
FROM Dependent AS DP
WHERE [Link] = [Link]
)
AND NOT EXISTS (
SELECT 1
COMPANY Database Lab Manual - Microsoft SQL Server
 FROM Department AS D
WHERE D.Mgr_ssn = [Link]
)
AND NOT EXISTS (
SELECT 1
FROM Employee AS Subordinate
WHERE Subordinate.Super_ssn = [Link]
);

ROLLBACK;

This query avoids deleting employees who are still referenced by common child relationships.

13. Example: define cascade delete from Project to Work_on

Level: DDL example Concept: ON DELETE CASCADE
-- Use this only when creating or redesigning the schema.
-- Do not add it if an equivalent foreign key already exists.
ALTER TABLE Work_on
ADD CONSTRAINT FK_WorkOn_Project_Cascade
FOREIGN KEY (Pno)
REFERENCES Project(Pnumber)
ON DELETE CASCADE;

With this constraint, deleting a project automatically deletes matching Work_on rows.

14. Example: define set-null behavior for supervisor references

Level: DDL example Concept: ON DELETE SET NULL
-- Super_ssn must allow NULL for this to work.
ALTER TABLE Employee
ADD CONSTRAINT FK_Employee_Supervisor_SetNull
FOREIGN KEY (Super_ssn)
REFERENCES Employee(Ssn)
ON DELETE SET NULL;

With this constraint, deleting a supervisor sets subordinates Super_ssn to NULL.

COMPANY Database Lab Manual - Microsoft SQL Server

Part 4 - SQL Server Views
A view is a saved SELECT query that can be used like a virtual table. In this lab, views are used to simplify joins, hide unnecessary
columns, summarize data, and practice SQL Server DDL statements.
SQL Server note: CREATE VIEW and ALTER VIEW should be the first statement in a batch. In SQL Server Management Studio, use GO
before and after each CREATE VIEW or ALTER VIEW example.
Assumption: The examples use dbo as the schema name. If your tables were created under another schema, replace dbo with your
schema name.
View example Main purpose
vEmployeeBasic Create a simple view from one table.
vEmployeeFullName Use a computed column inside a view.
vEmployeeDepartment Create a join view between Employee and Department.
vEmployeeSupervisor Create a self-join view for supervisor names.
vEmployeeProjectHours Create a multi-table join view.
vDepartmentSalarySummary Use aggregate functions in a view.
vProjectWorkSummary Summarize project work hours.
vEmployeesWithDependents Use DISTINCT with related child rows.
vEmployeesWithoutDependents Use NOT EXISTS inside a view.
vTop5HighestPaidEmployees Use TOP with ORDER BY inside a SQL Server view.
vDepartment5Employees Use WITH CHECK OPTION for safer updates through a view.
[Link] queries Inspect, alter, rename, and drop views.
1. Create a simple employee view
Level: Basic Concept: CREATE VIEW from one table
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

AS
SELECT
Ssn,
Fname,
Lname,
Sex,
Salary,
Dno
FROM [Link];
GO

SELECT *
FROM [Link];
GO

This view hides columns such as Address, Bdate, Super_ssn, and Minit, and exposes only the columns needed for many simple
reports.

2. Query a view using WHERE and ORDER BY

Level: Basic Concept: Treat a view like a table in SELECT statements
SELECT Fname, Lname, Salary, Dno
FROM [Link]
WHERE Salary > 30000
ORDER BY Salary DESC;

After a view is created, students can filter, sort, join, and group it just like a table. The ORDER BY belongs in the outer SELECT.

3. Create a view with a computed full-name column

Level: Basic to intermediate Concept: Column aliases and expressions
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
COMPANY Database Lab Manual - Microsoft SQL Server
 GO

CREATE VIEW [Link]

AS
SELECT
Ssn,
CONCAT(Fname, ' ', Lname) AS EmployeeName,
Salary,
Dno
FROM [Link];
GO

SELECT EmployeeName, Salary

FROM [Link]
ORDER BY EmployeeName;
GO

A view can contain expressions such as CONCAT. Every expression should be given a clear column alias.

4. Create a view that joins employees with departments

Level: Intermediate Concept: INNER JOIN inside a view
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

AS
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] AS E
INNER JOIN [Link] AS D
ON [Link] = [Link];
GO

SELECT Fname, Lname, Dname

FROM [Link]
ORDER BY Dname, Lname;
GO

This view simplifies a common join. Instead of rewriting the join each time, users can query the view.

5. Create a self-join view for employee supervisors

Level: Intermediate Concept: Self join in a view
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

AS
SELECT
[Link] AS EmployeeSsn,
[Link] AS EmployeeFirstName,
[Link] AS EmployeeLastName,
[Link] AS SupervisorSsn,
[Link] AS SupervisorFirstName,
[Link] AS SupervisorLastName
FROM [Link] AS E
LEFT JOIN [Link] AS S
ON E.Super_ssn = [Link];
GO

SELECT *
FROM [Link]
ORDER BY EmployeeLastName;
GO

COMPANY Database Lab Manual - Microsoft SQL Server

A LEFT JOIN is used because a top-level employee may not have a supervisor.

6. Create a multi-table view for employee project hours

Level: Intermediate Concept: Join Employee, Work_on, and Project
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

AS
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] AS E
INNER JOIN dbo.Work_on AS W
ON [Link] = [Link]
INNER JOIN [Link] AS P
ON [Link] = [Link];
GO

SELECT Fname, Lname, Pname, Hours

FROM [Link]
ORDER BY Lname, Pname;
GO

This view is useful for reports that need employee names, project names, and assigned work hours.

7. Create an aggregate view for salary summary by department

Level: Intermediate to advanced Concept: GROUP BY and aggregate functions in a view
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

AS
SELECT
[Link],
[Link],
COUNT([Link]) AS NumberOfEmployees,
SUM([Link]) AS TotalSalary,
AVG([Link]) AS AverageSalary,
MIN([Link]) AS MinimumSalary,
MAX([Link]) AS MaximumSalary
FROM [Link] AS D
LEFT JOIN [Link] AS E
ON [Link] = [Link]
GROUP BY [Link], [Link];
GO

SELECT *
FROM [Link]
ORDER BY AverageSalary DESC;
GO

Aggregate views are helpful for summary reports. They are usually read-only because they use GROUP BY.

8. Create an aggregate view for total project work hours

Level: Intermediate to advanced Concept: LEFT JOIN, SUM, and COUNT
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

AS
COMPANY Database Lab Manual - Microsoft SQL Server
 SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS NumberOfAssignments,
SUM(COALESCE([Link], 0)) AS TotalHours
FROM [Link] AS P
LEFT JOIN dbo.Work_on AS W
ON [Link] = [Link]
GROUP BY [Link], [Link], [Link];
GO

SELECT *
FROM [Link]
WHERE TotalHours > 40
ORDER BY TotalHours DESC;
GO

COALESCE changes NULL to 0 for projects that currently have no work assignments.

9. Create a view for employees who have dependents

Level: Intermediate Concept: DISTINCT with a child table
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

AS
SELECT DISTINCT
[Link],
[Link],
[Link]
FROM [Link] AS E
INNER JOIN [Link] AS DP
ON [Link] = [Link];
GO

SELECT *
FROM [Link]
ORDER BY Lname;
GO

DISTINCT is used because one employee may have more than one dependent.

10. Create a view for employees who do not have dependents

Level: Intermediate Concept: NOT EXISTS in a view
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

AS
SELECT
[Link],
[Link],
[Link]
FROM [Link] AS E
WHERE NOT EXISTS (
SELECT 1
FROM [Link] AS DP
WHERE [Link] = [Link]
);
GO

SELECT *
FROM [Link]
ORDER BY Lname;
GO

This view is a reusable version of a common NOT EXISTS query.

COMPANY Database Lab Manual - Microsoft SQL Server

11. Create a TOP view for highest-paid employees
Level: Intermediate Concept: TOP with ORDER BY inside a SQL Server view
IF OBJECT_ID('dbo.vTop5HighestPaidEmployees', 'V') IS NOT NULL
DROP VIEW dbo.vTop5HighestPaidEmployees;
GO

CREATE VIEW dbo.vTop5HighestPaidEmployees

AS
SELECT TOP (5)
Ssn,
Fname,
Lname,
Salary,
Dno
FROM [Link]
ORDER BY Salary DESC;
GO

SELECT *
FROM dbo.vTop5HighestPaidEmployees
ORDER BY Salary DESC;
GO

SQL Server does not allow a plain ORDER BY inside a view unless TOP, OFFSET, or FOR XML is also used. To guarantee display order,
still use ORDER BY in the outer SELECT.

12. Create a view with WITH CHECK OPTION

Level: Advanced Concept: Safer updates through a filtered view
IF OBJECT_ID('dbo.vDepartment5Employees', 'V') IS NOT NULL
DROP VIEW dbo.vDepartment5Employees;
GO

CREATE VIEW dbo.vDepartment5Employees

AS
SELECT
Ssn,
Fname,
Lname,
Salary,
Dno
FROM [Link]
WHERE Dno = 5
WITH CHECK OPTION;
GO

BEGIN TRANSACTION;

UPDATE dbo.vDepartment5Employees
SET Salary = Salary * 1.05
WHERE Ssn = '123456789';

-- This update should fail if uncommented, because it violates WITH CHECK OPTION.
-- UPDATE dbo.vDepartment5Employees
-- SET Dno = 4
-- WHERE Ssn = '123456789';

ROLLBACK;
GO

WITH CHECK OPTION prevents updates through the view that would make the row disappear from the view.

13. Alter an existing view

Level: Intermediate Concept: ALTER VIEW
ALTER VIEW [Link]
AS
SELECT
[Link],
[Link],
[Link],
COMPANY Database Lab Manual - Microsoft SQL Server
 [Link],
[Link],
[Link],
[Link],
D.Mgr_ssn
FROM [Link] AS E
INNER JOIN [Link] AS D
ON [Link] = [Link];
GO

SELECT *
FROM [Link];
GO

Use ALTER VIEW when the view already exists and you want to change its SELECT statement.

14. Rename a view

Level: DDL Concept: EXEC sp_rename
EXEC sp_rename '[Link]', 'vEmployeeDepartmentInfo';
GO

-- Optional: rename it back to the original name for the rest of the lab.
EXEC sp_rename '[Link]', 'vEmployeeDepartment';
GO

sp_rename can rename a view, but renaming database objects can confuse existing queries, stored procedures, and documentation.
Use it carefully.

15. List all views in the current database

Level: Intermediate Concept: SQL Server catalog views
SELECT
[Link] AS SchemaName,
[Link] AS ViewName,
V.create_date,
V.modify_date
FROM [Link] AS V
INNER JOIN [Link] AS S
ON V.schema_id = S.schema_id
ORDER BY [Link], [Link];

SQL Server stores metadata about views in system catalog views such as [Link] and [Link].

16. Display the SQL definition of a view

Level: Intermediate Concept: OBJECT_DEFINITION and OBJECT_ID
SELECT OBJECT_DEFINITION(OBJECT_ID('[Link]')) AS ViewDefinition;

This displays the stored SELECT statement behind the view.

17. Create a schema-bound view

Level: Advanced Concept: WITH SCHEMABINDING
IF OBJECT_ID('[Link]', 'V') IS NOT NULL
DROP VIEW [Link];
GO

CREATE VIEW [Link]

WITH SCHEMABINDING
AS
SELECT
[Link],
COUNT_BIG(*) AS EmployeeCount
FROM [Link] AS E
GROUP BY [Link];
GO

SELECT *
FROM [Link];
GO

COMPANY Database Lab Manual - Microsoft SQL Server

SCHEMABINDING ties the view to the underlying table structure. SQL Server will not allow certain changes to the base table while the
schema-bound view depends on it.

18. Drop views after the lab

Level: DDL cleanup Concept: DROP VIEW IF EXISTS
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS [Link];
DROP VIEW IF EXISTS dbo.vTop5HighestPaidEmployees;
DROP VIEW IF EXISTS dbo.vDepartment5Employees;
DROP VIEW IF EXISTS [Link];
GO

Use cleanup statements at the end of the lab if students need to recreate the views from the beginning.

Important Rules About SQL Server Views

• A view stores a query definition, not a separate copy of data, unless special indexed-view features are used.
• Avoid SELECT * in view definitions. List the required columns clearly.
• Plain ORDER BY is not allowed inside a view. Use ORDER BY in the final SELECT that queries the view.
• Simple single-table views may be updateable. Join views and aggregate views are usually read-only or have strict update
limitations.
• Use WITH CHECK OPTION when a filtered view should prevent updates that violate its WHERE condition.
• Use ALTER VIEW to change a view definition and DROP VIEW to remove a view.

Lab Questions for Students

1. Write a query to list employees whose salary is below the average salary of their own department.
2. Write a query to display every department with the number of employees and number of projects it controls.
3. Write a query using INTERSECT to find employees who work on two chosen projects.
4. Write a query using EXCEPT to find employees who work on one chosen project but not another.
5. Write an UPDATE statement that increases salary by 5 percent only for employees who work more than 30 total hours.
6. Explain why deleting a department may fail when foreign keys are enforced.
7. Write the correct delete order for removing a project that has rows in Work_on.
8. Explain the difference between ON DELETE CASCADE and deleting child rows manually.
9. Write a query against [Link] to list all views created in the database.
10. Create a filtered view for employees in department 5 and test WITH CHECK OPTION.
11. Explain why an aggregate view is usually not directly updateable.
12. Create a view that lists employees who have no dependents.
13. Create an aggregate view that shows total hours worked on each project.
14. Create a view that shows employee full name, department name, and salary.

COMPANY Database Lab Manual - Microsoft SQL Server

Quick Reference: SQL Server Keywords Used
Keyword or feature Purpose
TOP Limits the number of returned rows.
LIKE Matches text patterns using wildcards such as % and _.
GROUP BY Groups rows before applying aggregate functions.
HAVING Filters grouped results.
INNER JOIN Returns matching rows from both tables.
LEFT JOIN Returns all rows from the left table and matching rows from the right table.
UNION Combines results and removes duplicates.
INTERSECT Returns rows common to both result sets.
EXCEPT Returns rows from the first result set that are not in the second.
UPDATE FROM SQL Server syntax for updating one table using a join.
EXEC sp_rename SQL Server procedure used to rename columns or tables.
ALTER COLUMN SQL Server syntax for modifying an existing column.
TRY...CATCH Handles errors such as foreign-key delete conflicts.
CREATE VIEW Creates a saved SELECT query that can be queried like a virtual table.
ALTER VIEW Changes the SELECT statement of an existing view.
DROP VIEW Removes a view definition from the database.
WITH CHECK OPTION Prevents updates through a filtered view that would violate the view
condition.
SCHEMABINDING Binds a view to the schema of the referenced base table or tables.
[Link] SQL Server catalog view used to list views in the current database.
OBJECT_DEFINITION Displays the stored SQL definition of a view or other programmable
object.
GO SSMS and sqlcmd batch separator; useful before and after CREATE
VIEW or ALTER VIEW.

End of Lab Manual

COMPANY Database Lab Manual - Microsoft SQL Server