Code icon

The App is Under a Quick Maintenance

We apologize for the inconvenience. Please come back later

Cuantum Technologies logo
Dashboard
Profile
Billing
Full Access
Log out
Menu iconMenu iconPython & SQL Bible
Python & SQL Bible
Arrow Icon
Chapter 15: Advanced SQL
15.1 Subqueries
15.2 Stored Procedures
15.3 Triggers
15.4 Practical Exercises of Chapter 15: Advanced SQL
Buy this book
You've learned this already. ✅
Click here to view the next lesson.

Chapter 15: Advanced SQL

15.4 Practical Exercises of Chapter 15: Advanced SQL

In this section, we'll cover some exercises that will help you solidify your understanding of advanced SQL concepts.

Exercise 1: Working with Subqueries

  1. Write a query that finds the names of all employees whose salary is above the average salary.
SELECT name
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
  1. Write a query to find the customer with the highest total purchase amount. Use a subquery to first calculate the total purchase amount for each customer.
SELECT customer_id, name
FROM Customers
WHERE total_purchase = (SELECT MAX(total_purchase) FROM Customers);

Exercise 2: Creating and Using Stored Procedures

Write a stored procedure to increase the salary of an employee by a certain percentage. The procedure should take the employee id and the percentage as parameters.

DELIMITER //
CREATE PROCEDURE IncreaseSalary(IN emp_id INT, IN percentage DECIMAL)
BEGIN
    UPDATE Employees
    SET salary = salary + salary * percentage/100
    WHERE employee_id = emp_id;
END//
DELIMITER ;

You can then call this procedure with specific parameters like this:

CALL IncreaseSalary(101, 10);

Exercise 3: Triggers

  1. Write a trigger to track changes in the Employees table. The trigger should insert a new row into the EmployeeAudit table whenever an employee's salary is updated. The EmployeeAudit table has fields for employee_id, old_salary, new_salary, and change_date.
DELIMITER //
CREATE TRIGGER SalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
   INSERT INTO EmployeeAudit(employee_id, old_salary, new_salary, change_date)
   VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END;//
DELIMITER ;
  1. Verify your trigger works by updating an employee's salary and then selecting all rows from the EmployeeAudit table.

Remember, the exact SQL syntax might vary slightly depending on your database system.

Chapter 15 Conclusion

This chapter took us through an intensive exploration of advanced SQL features. We started with an understanding of subqueries, which offer the ability to perform multiple layers of data retrieval in a single query, thereby increasing the complexity and depth of the queries we can create. We saw how subqueries can be used to compute averages, find maximums and minimums, and perform other comparisons across different scopes of data.

We then moved on to stored procedures, a powerful SQL feature that allows you to encapsulate and store a series of SQL statements for later use. We examined how stored procedures can reduce network traffic, promote code reuse, and enhance security by restricting direct access to the database tables.

Next, we explored triggers, an advanced SQL feature that allows us to automatically execute a defined set of SQL statements based on certain events or conditions. Triggers enhance data integrity, can automate system maintenance, and provide auditing capabilities.

Through the exercises section, we had a chance to practice creating complex SQL queries, writing stored procedures, and setting up triggers. This hands-on experience solidified our understanding of these advanced SQL concepts, and illustrated how they can be used to solve more complex database tasks.

In conclusion, the power of SQL goes far beyond basic data retrieval. By leveraging advanced SQL features like subqueries, stored procedures, and triggers, we can effectively handle more complex tasks, automate processes, and maintain the integrity of our data. As we move forward, always remember to think about the most efficient and effective ways to utilize these tools in your own SQL programming. This chapter represents a significant step in your journey to becoming an advanced SQL user!

15.4 Practical Exercises of Chapter 15: Advanced SQL

In this section, we'll cover some exercises that will help you solidify your understanding of advanced SQL concepts.

Exercise 1: Working with Subqueries

  1. Write a query that finds the names of all employees whose salary is above the average salary.
SELECT name
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
  1. Write a query to find the customer with the highest total purchase amount. Use a subquery to first calculate the total purchase amount for each customer.
SELECT customer_id, name
FROM Customers
WHERE total_purchase = (SELECT MAX(total_purchase) FROM Customers);

Exercise 2: Creating and Using Stored Procedures

Write a stored procedure to increase the salary of an employee by a certain percentage. The procedure should take the employee id and the percentage as parameters.

DELIMITER //
CREATE PROCEDURE IncreaseSalary(IN emp_id INT, IN percentage DECIMAL)
BEGIN
    UPDATE Employees
    SET salary = salary + salary * percentage/100
    WHERE employee_id = emp_id;
END//
DELIMITER ;

You can then call this procedure with specific parameters like this:

CALL IncreaseSalary(101, 10);

Exercise 3: Triggers

  1. Write a trigger to track changes in the Employees table. The trigger should insert a new row into the EmployeeAudit table whenever an employee's salary is updated. The EmployeeAudit table has fields for employee_id, old_salary, new_salary, and change_date.
DELIMITER //
CREATE TRIGGER SalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
   INSERT INTO EmployeeAudit(employee_id, old_salary, new_salary, change_date)
   VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END;//
DELIMITER ;
  1. Verify your trigger works by updating an employee's salary and then selecting all rows from the EmployeeAudit table.

Remember, the exact SQL syntax might vary slightly depending on your database system.

Chapter 15 Conclusion

This chapter took us through an intensive exploration of advanced SQL features. We started with an understanding of subqueries, which offer the ability to perform multiple layers of data retrieval in a single query, thereby increasing the complexity and depth of the queries we can create. We saw how subqueries can be used to compute averages, find maximums and minimums, and perform other comparisons across different scopes of data.

We then moved on to stored procedures, a powerful SQL feature that allows you to encapsulate and store a series of SQL statements for later use. We examined how stored procedures can reduce network traffic, promote code reuse, and enhance security by restricting direct access to the database tables.

Next, we explored triggers, an advanced SQL feature that allows us to automatically execute a defined set of SQL statements based on certain events or conditions. Triggers enhance data integrity, can automate system maintenance, and provide auditing capabilities.

Through the exercises section, we had a chance to practice creating complex SQL queries, writing stored procedures, and setting up triggers. This hands-on experience solidified our understanding of these advanced SQL concepts, and illustrated how they can be used to solve more complex database tasks.

In conclusion, the power of SQL goes far beyond basic data retrieval. By leveraging advanced SQL features like subqueries, stored procedures, and triggers, we can effectively handle more complex tasks, automate processes, and maintain the integrity of our data. As we move forward, always remember to think about the most efficient and effective ways to utilize these tools in your own SQL programming. This chapter represents a significant step in your journey to becoming an advanced SQL user!

15.4 Practical Exercises of Chapter 15: Advanced SQL

In this section, we'll cover some exercises that will help you solidify your understanding of advanced SQL concepts.

Exercise 1: Working with Subqueries

  1. Write a query that finds the names of all employees whose salary is above the average salary.
SELECT name
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
  1. Write a query to find the customer with the highest total purchase amount. Use a subquery to first calculate the total purchase amount for each customer.
SELECT customer_id, name
FROM Customers
WHERE total_purchase = (SELECT MAX(total_purchase) FROM Customers);

Exercise 2: Creating and Using Stored Procedures

Write a stored procedure to increase the salary of an employee by a certain percentage. The procedure should take the employee id and the percentage as parameters.

DELIMITER //
CREATE PROCEDURE IncreaseSalary(IN emp_id INT, IN percentage DECIMAL)
BEGIN
    UPDATE Employees
    SET salary = salary + salary * percentage/100
    WHERE employee_id = emp_id;
END//
DELIMITER ;

You can then call this procedure with specific parameters like this:

CALL IncreaseSalary(101, 10);

Exercise 3: Triggers

  1. Write a trigger to track changes in the Employees table. The trigger should insert a new row into the EmployeeAudit table whenever an employee's salary is updated. The EmployeeAudit table has fields for employee_id, old_salary, new_salary, and change_date.
DELIMITER //
CREATE TRIGGER SalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
   INSERT INTO EmployeeAudit(employee_id, old_salary, new_salary, change_date)
   VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END;//
DELIMITER ;
  1. Verify your trigger works by updating an employee's salary and then selecting all rows from the EmployeeAudit table.

Remember, the exact SQL syntax might vary slightly depending on your database system.

Chapter 15 Conclusion

This chapter took us through an intensive exploration of advanced SQL features. We started with an understanding of subqueries, which offer the ability to perform multiple layers of data retrieval in a single query, thereby increasing the complexity and depth of the queries we can create. We saw how subqueries can be used to compute averages, find maximums and minimums, and perform other comparisons across different scopes of data.

We then moved on to stored procedures, a powerful SQL feature that allows you to encapsulate and store a series of SQL statements for later use. We examined how stored procedures can reduce network traffic, promote code reuse, and enhance security by restricting direct access to the database tables.

Next, we explored triggers, an advanced SQL feature that allows us to automatically execute a defined set of SQL statements based on certain events or conditions. Triggers enhance data integrity, can automate system maintenance, and provide auditing capabilities.

Through the exercises section, we had a chance to practice creating complex SQL queries, writing stored procedures, and setting up triggers. This hands-on experience solidified our understanding of these advanced SQL concepts, and illustrated how they can be used to solve more complex database tasks.

In conclusion, the power of SQL goes far beyond basic data retrieval. By leveraging advanced SQL features like subqueries, stored procedures, and triggers, we can effectively handle more complex tasks, automate processes, and maintain the integrity of our data. As we move forward, always remember to think about the most efficient and effective ways to utilize these tools in your own SQL programming. This chapter represents a significant step in your journey to becoming an advanced SQL user!

15.4 Practical Exercises of Chapter 15: Advanced SQL

In this section, we'll cover some exercises that will help you solidify your understanding of advanced SQL concepts.

Exercise 1: Working with Subqueries

  1. Write a query that finds the names of all employees whose salary is above the average salary.
SELECT name
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
  1. Write a query to find the customer with the highest total purchase amount. Use a subquery to first calculate the total purchase amount for each customer.
SELECT customer_id, name
FROM Customers
WHERE total_purchase = (SELECT MAX(total_purchase) FROM Customers);

Exercise 2: Creating and Using Stored Procedures

Write a stored procedure to increase the salary of an employee by a certain percentage. The procedure should take the employee id and the percentage as parameters.

DELIMITER //
CREATE PROCEDURE IncreaseSalary(IN emp_id INT, IN percentage DECIMAL)
BEGIN
    UPDATE Employees
    SET salary = salary + salary * percentage/100
    WHERE employee_id = emp_id;
END//
DELIMITER ;

You can then call this procedure with specific parameters like this:

CALL IncreaseSalary(101, 10);

Exercise 3: Triggers

  1. Write a trigger to track changes in the Employees table. The trigger should insert a new row into the EmployeeAudit table whenever an employee's salary is updated. The EmployeeAudit table has fields for employee_id, old_salary, new_salary, and change_date.
DELIMITER //
CREATE TRIGGER SalaryChange
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
   INSERT INTO EmployeeAudit(employee_id, old_salary, new_salary, change_date)
   VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END;//
DELIMITER ;
  1. Verify your trigger works by updating an employee's salary and then selecting all rows from the EmployeeAudit table.

Remember, the exact SQL syntax might vary slightly depending on your database system.

Chapter 15 Conclusion

This chapter took us through an intensive exploration of advanced SQL features. We started with an understanding of subqueries, which offer the ability to perform multiple layers of data retrieval in a single query, thereby increasing the complexity and depth of the queries we can create. We saw how subqueries can be used to compute averages, find maximums and minimums, and perform other comparisons across different scopes of data.

We then moved on to stored procedures, a powerful SQL feature that allows you to encapsulate and store a series of SQL statements for later use. We examined how stored procedures can reduce network traffic, promote code reuse, and enhance security by restricting direct access to the database tables.

Next, we explored triggers, an advanced SQL feature that allows us to automatically execute a defined set of SQL statements based on certain events or conditions. Triggers enhance data integrity, can automate system maintenance, and provide auditing capabilities.

Through the exercises section, we had a chance to practice creating complex SQL queries, writing stored procedures, and setting up triggers. This hands-on experience solidified our understanding of these advanced SQL concepts, and illustrated how they can be used to solve more complex database tasks.

In conclusion, the power of SQL goes far beyond basic data retrieval. By leveraging advanced SQL features like subqueries, stored procedures, and triggers, we can effectively handle more complex tasks, automate processes, and maintain the integrity of our data. As we move forward, always remember to think about the most efficient and effective ways to utilize these tools in your own SQL programming. This chapter represents a significant step in your journey to becoming an advanced SQL user!

Purchase this book