Exploring Advanced SQL Concepts: Part 2
Welcome to the fifth day of my SQL learning journey! As I continue my journey to master SQL from the ground up, today, we’re delving deeper into some advanced SQL concepts. Specifically, we’ll be exploring two powerful tools in SQL: Stored Procedures and Sub queries.
Stored Procedures:
Stored procedures are a set of carefully crafted SQL instructions tucked or stored away in a database. What makes them special is their ability to accept input parameters, this makes them versatile and adaptable for various tasks. Stored procedures are like reusable code snippets that not only reduce the load on the network but also automatically update when modified, ensuring you always have the latest version.
Creating a stored procedure involves a straightforward syntax:
CREATE PROCEDURE Name_of_Procedure
AS
BEGIN
— Your SQL Statements Here
END
To run a stored procedure, all you need is:
EXEC Name_of_Procedure
For instance, imagine you’re working with a “temp employee” table:
CREATE PROCEDURE Calculate_Salary
AS
BEGIN
SELECT *
FROM temp_employee
WHERE salary > 50000;
END
Altering Stored Procedures: Keeping Up with Changes
If you ever need to make adjustments to a stored procedure, it’s as simple as using the ALTER statement. This keeps your procedures up-to-date, ensuring they align with your evolving database needs.
Subqueries: Your Secret Query within a Query
Subqueries, also known as inner queries, are your way of embedding one query inside another. These are your problem-solving tools, seamlessly integrated into SELECT, FROM, WHERE INSERT, and DELETE statements. Subqueries enable you to filter, group, or manipulate data within a larger query.
While there’s no set syntax for sub-queries, they typically appear within WHERE, SELECT, or FROM clauses in SQL statements. They are your go-to solution for handling intricate data tasks.
Here are some examples to illustrate how versatile subqueries can be:
- Using Sub queries with FROM Statements:
Suppose you want to find employees in departments with an average salary over $50,000:
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE AVG(salary) > 50000);
2. Using Subqueries with WHERE Statements:
Imagine you need to identify orders with product prices higher than the average product price:
SELECT order_id, product_id
FROM orders
WHERE product_price > (SELECT AVG(price) FROM products);
3. Using Subqueries with INSERT Statements:
Let’s say you want to copy employees who joined the company in the last year to a new table:
INSERT INTO new_employees
SELECT * FROM employees
WHERE join_date >= DATEADD(YEAR, -1, GETDATE());
4. Using Subqueries with DELETE Statements:
Consider a scenario where you want to delete orders made by customers who haven’t purchased anything in the last two years:
DELETE FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE last_purchase_date <= DATEADD(YEAR, -2, GETDATE()));
These examples showcase how sub-queries seamlessly integrate with various SQL statements to filter, insert, or delete data based on conditions from other tables. Thank you so much for reading and please stay tuned for more discoveries in the world of SQL!
