Lab 5: Subqueries and Views
Objectives:
Understand subqueries and their applications.
Learn how to create and use views in SQL Server.
Lab Activities:
1. Writing subqueries to retrieve data for specific conditions.
2. Using subqueries in different clauses (WHERE, SELECT, etc.).
3. Creating and modifying views to simplify complex queries.
LAB 5.1 - WRITING SUBQUERIES TO RETRIEVE DATA FOR SPECIFIC CONDITIONS
Writing subqueries to retrieve data for specific conditions in SQL Server Management Studio (SSMS)
involves using a nested query within the main query to filter or manipulate data based on certain
criteria. Subqueries can be used in various parts of a SQL query, including the SELECT, FROM,
WHERE, and HAVING clauses. Here are steps to write subqueries in SSMS:
1. Launch SSMS: Start by opening SQL Server Management Studio and connecting to your SQL
Server instance if you haven't already.
2. Open a New Query: Click on "New Query" to open a new query window where you can write
and execute SQL queries.
3. Write the Main Query: Begin by writing the main query that you want to use as the outer query.
This will be the query that retrieves data from the primary table(s) you're interested in.
SELECT *
FROM Orders
WHERE CustomerID IN (
SELECT CustomerID
FROM Customers
WHERE LastPurchaseDate >= DATEADD(DAY, -30, GETDATE())
);
In this subquery, we're selecting CustomerID from the Customers table for customers who made a
purchase in the last 30 days. This subquery filters the Orders table based on those CustomerID values.
4. Execute the Query: After writing your query with the subquery, execute it by clicking the
"Execute" button or pressing F5. This will return the results based on the conditions specified in
both the main query and the subquery.
5. Review and Refine: Examine the results to ensure they match your requirements. You can
refine your query and subquery as needed to get the desired output.
6. Save and Optimize: Once you have a working subquery, you can save the query for future use.
Additionally, you can optimize your query for performance by considering indexing and other
database optimization techniques.
Remember that subqueries can be used in various ways to achieve different goals, such as filtering,
aggregating, or selecting data based on specific conditions.
Exercise 1: Retrieve Customer Information
In this exercise, you have two tables: Customers and Orders. Retrieve a list of customers who have
placed orders. Your task is to use a subquery to achieve this.
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
SELECT DISTINCT CustomerID
FROM Orders
);
Exercise 2: Find Products in Stock
You have a database with tables Products and Inventory. Retrieve the names of products that are
currently in stock (inventory > 0).
SELECT ProductName
FROM Products
WHERE ProductID IN (
SELECT ProductID
FROM Inventory
WHERE Quantity > 0
);
Exercise 3: Calculate Average Order Amount
Calculate the average order amount for customers who have placed at least 2 orders.
SELECT AVG(OrderAmount) AS AverageOrderAmount
FROM (
SELECT CustomerID, COUNT(*) AS OrderCount, SUM(OrderAmount) AS OrderAmount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) >= 2
) AS Subquery;
Exercise 4: Find Employees with High Salary
Retrieve the names of employees who have a salary greater than the average salary of all employees.
SELECT FirstName, LastName
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
);
Exercise 5: List Orders with Highest Amount
Find the order(s) with the highest order amount(s) in the database.
SELECT *
FROM Orders
WHERE OrderAmount = (
SELECT MAX(OrderAmount)
FROM Orders
);
Exercise 6: Get Product Sales by Category
Retrieve the total sales amount for each product category.
SELECT CategoryName, (
SELECT SUM(OrderAmount)
FROM Products AS P
JOIN Orders AS O ON P.ProductID = O.ProductID
WHERE P.CategoryID = C.CategoryID
) AS TotalSales
FROM Categories AS C;
These exercises cover a range of scenarios where subqueries can be used to filter, aggregate, and
retrieve data based on specific conditions.
5.2 - USING SUBQUERIES IN DIFFERENT CLAUSES (WHERE, SELECT, ETC.)
Using subqueries in different clauses (WHERE, SELECT, etc.) is a fundamental SQL skill, and SQL
Server Management Studio (SSMS) is a versatile tool for executing these queries. Below are steps to
use subqueries in various clauses using SSMS:
1. Launch SSMS: Open SQL Server Management Studio and connect to your SQL Server database.
2. Create a New Query Window: Click on "New Query" or press Ctrl+N to open a new query window.
3. Writing Subqueries in Different Clauses:
Using Subqueries in WHERE Clause:
Subqueries in the WHERE clause are used to filter rows based on specific conditions.
Example: Retrieve all customers who have placed orders.
SELECT *
FROM Customers
WHERE CustomerID IN (
SELECT CustomerID
FROM Orders
);
Using Subqueries in SELECT Clause:
Subqueries in the SELECT clause are used to calculate values that are displayed in the result
set.
Example: Retrieve the total number of orders for each customer.
SELECT CustomerName, (
SELECT COUNT(*)
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
) AS TotalOrders
FROM Customers;
Using Subqueries in FROM Clause:
Subqueries in the FROM clause can be used to create derived tables or subquery result sets
that can be further queried.
Example: Retrieve all orders along with the customer details.
SELECT *
FROM (
SELECT OrderID, OrderDate, CustomerID
FROM Orders
) AS OrderDetails
JOIN Customers ON OrderDetails.CustomerID = Customers.CustomerID;
Using Subqueries in HAVING Clause:
Subqueries in the HAVING clause are used to filter the results of grouped data.
Example: Retrieve customers who have placed more than 5 orders.
SELECT CustomerName, COUNT(*) AS TotalOrders
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
GROUP BY CustomerName
HAVING COUNT(*) > 5;
4. Execute the Query: After writing the query with subqueries, execute it by clicking the "Execute"
button or pressing F5.
5. Review Results: Review the results in the query output pane to ensure they meet your requirements.
6. Save and Optimize: If the query works correctly, you can save it for future use. Additionally, consider
optimizing your query for performance using appropriate indexes and query optimization techniques.
Using subqueries in different clauses allows you to filter, aggregate, and manipulate data in various
ways, making your SQL queries more powerful and flexible.
Exercise 1: Using Subqueries in WHERE Clause
Retrieve the names of customers who have placed orders in the last 30 days.
SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (
SELECT CustomerID
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -30, GETDATE())
);
Exercise 2: Using Subqueries in SELECT Clause
Retrieve the order with the highest order amount and display it along with the customer's name.
SELECT CustomerName,
(SELECT TOP 1 OrderAmount
FROM Orders AS O
WHERE O.CustomerID = C.CustomerID
ORDER BY OrderAmount DESC) AS HighestOrderAmount
FROM Customers AS C;
Exercise 3: Using Subqueries in FROM Clause
Retrieve the average order amount for each customer and display it along with their names.
SELECT C.CustomerName, Subquery.AvgOrderAmount
FROM Customers AS C
JOIN (
SELECT CustomerID, AVG(OrderAmount) AS AvgOrderAmount
FROM Orders
GROUP BY CustomerID
) AS Subquery ON C.CustomerID = Subquery.CustomerID;
Exercise 4: Using Subqueries in HAVING Clause
Find the categories that have more than 3 products and list those categories along with the number of
products they contain.
SELECT CategoryName, COUNT(*) AS ProductCount
FROM Products
GROUP BY CategoryName
HAVING COUNT(*) > 3;
Exercise 5: Using Subqueries in WHERE Clause with Aggregation
Retrieve the names of customers who have placed orders with a total amount greater than the average
order amount.
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderAmount) > (SELECT AVG(OrderAmount) FROM Orders)
);
Exercise 6: Using Subqueries to Filter JOINed Data
Retrieve a list of products and their categories where the product price is higher than the average price
within the category.
SELECT P.ProductName, C.CategoryName
FROM Products AS P
JOIN Categories AS C ON P.CategoryID = C.CategoryID
WHERE P.ProductPrice > (
SELECT AVG(ProductPrice)
FROM Products
WHERE CategoryID = P.CategoryID
);
5.3 - CREATING AND MODIFYING VIEWS TO SIMPLIFY COMPLEX QUERIES
Creating and modifying views in SQL Server Management Studio (SSMS) is a useful way to simplify
complex queries by encapsulating them into a reusable virtual table. Here are the steps to create and
modify views using SSMS:
Creating a View:
1. Open SQL Server Management Studio: Launch SSMS and connect to your SQL Server
instance.
2. Open a New Query Window: Click on "New Query" to open a new query window.
3. Write the SQL Query: Write the SQL query that represents the complex logic you want to
encapsulate into a view. For example:
CREATE VIEW MyView AS
SELECT Column1, Column2
FROM MyTable
WHERE Column3 = 'SomeValue';
Replace MyView with your desired view name and modify the query as needed.
4. Execute the Query: Select the query you've written and execute it by pressing F5 or clicking
the "Execute" button.
5. Verify the View: You can verify that the view was created by expanding the "Views" folder
under the appropriate database in the Object Explorer.
Modifying an Existing View:
1. Open SSMS: Launch SSMS and connect to your SQL Server instance.
2. Open Object Explorer: In SSMS, navigate to the "Object Explorer" window.
3. Locate the View: Expand the database containing the view you want to modify. Go to the
"Views" folder and find the view you want to modify.
4. Right-click the View: Right-click on the view you want to modify, and select "Design" to open the
view in the design editor.
5. Modify the View: In the design editor, you can make changes to the view's structure, including
adding or removing columns, changing column names, or modifying the query that defines the
view.
6. Save the Changes: After making your modifications, save the changes by clicking the "Save"
button or pressing Ctrl + S.
7. Confirm the Changes: SSMS may prompt you to confirm the changes you've made. Review
them and click "Yes" to apply the changes.
8. Close the View Designer: Once you've saved the changes, close the view designer.
Remember that views can simplify complex queries, but they are essentially saved SQL queries that
are executed whenever you query the view. So, any changes you make to the view will affect the
results returned when you select data from the view.
Creating and modifying views in SSMS can help you encapsulate complex logic, improve query
readability, and provide a level of abstraction for your database interactions.
Lab Exercise 1: Creating a Simple View
1. Open SSMS, connect to your SQL Server instance, and choose a database to work with.
2. Create a new view called "EmployeeInfo" that retrieves the employee's name, job title, and
salary from the "Employees" table.
CREATE VIEW EmployeeInfo AS
SELECT EmployeeName, JobTitle, Salary
FROM Employees;
3. Execute the query to create the view.
4. Verify that the "EmployeeInfo" view has been created in the Object Explorer.
5. Test the view by running a SELECT statement against it:
SELECT * FROM EmployeeInfo;
Lab Exercise 2: Modifying an Existing View
1. Open SSMS, connect to your SQL Server instance, and choose the same database where the
"EmployeeInfo" view was created.
2. Locate and open the "EmployeeInfo" view using the Object Explorer.
3. In the view designer, add a new column to the view to include the employee's department. Save
the changes.
4. Test the modified view by running a SELECT statement against it:
SELECT * FROM EmployeeInfo;
Lab Exercise 3: Creating a Complex View
1. Create a new view called "HighPaidEmployees" that retrieves the names and salaries of
employees earning more than $50,000 from the "Employees" table.
CREATE VIEW HighPaidEmployees AS
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > 50000;
2. Execute the query to create the view.
3. Test the view by running a SELECT statement against it:
SELECT * FROM HighPaidEmployees;
Lab Exercise 4: Modifying a Complex View
1. Open SSMS, connect to your SQL Server instance, and choose the same database where the
"HighPaidEmployees" view was created.
2. Locate and open the "HighPaidEmployees" view using the Object Explorer.
3. In the view designer, modify the view to also include the employee's department. Save the
changes.
4. Test the modified view by running a SELECT statement against it:
SELECT * FROM HighPaidEmployees;
