SQL Queries for Practice

SQL Queries

SQL queries stand out in data management as they allow smooth database interaction. Whether you're a beginner or an experienced developer, grasping the principles of SQL queries is like opening the door to a world of efficient data manipulation. Let us begin on a trip to decipher the world of SQL queries, investigating their meaning and unraveling the grammar that underpins them.

SQL, or Structured Query Language, is a domain-specific language used to manage and manipulate relational databases. SQL is fundamentally made up of a series of commands called as queries, which serve as messengers between you and the database.

In basic terms, SQL queries are the methods used to obtain, insert, update, or delete data from a database. Consider them the bridge that connects your intentions to massive data sources, allowing you to easily interact with information.

Simple Queries

1. Retrieving Data with SELECT:

   The SELECT statement is the foundation of SQL queries. To retrieve all the columns from a table, use the asterisk (*). For instance:

   This query fetches all columns from the "employees" table. A specific column can also be chosen by listing them after the SELECT keyword:

2. Filtering Data with WHERE:

   The WHERE keyword allows you to filter results depending on particular criteria.

   Consider the following example:

   This query obtains the product name and price from the "products" table where the category is Electronics. Customize the condition based on your requirements.

3. Sorting Data with ORDER BY:

   To organize your results in a certain order, use the ORDER BY clause. For instance:

   This query collects product names and prices from the "products" database and sorts them in decreasing price order. Change DESC to ASC for ascending order.

4. Combining Conditions with AND and OR:

   Enhance your searches by combining criteria using AND and OR. For example:

   This query retrieves customer names and order dates from the "orders" table when the order status is 'Shipped' and the total amount is more than 1000.

5. Counting Rows with COUNT:

   Use the COUNT function to count the number of rows present in a table. For example:

   This query returns the total number of rows in the "orders" table.

6. Calculating Average with AVG:

   The AVG function helps you to calculate the average of a numerical column. Let`s find the average price of products:

   This query retrieves the average price from the "products" table.

7. Filtering with LIKE:

   The LIKE operator is handy for pattern matching. Suppose you want to find the products with the names containing laptop:

   The % symbols function as wildcards, allowing any characters before and after laptop.

8. Limiting Results with LIMIT:

   The LIMIT clause restricts the number of rows returned. If you only want the first three products:

   This query fetches the top three rows from the "products" table.

9. Updating Data with UPDATE:

   Use the UPDATE statement to modify the existing data. Let's say you want to increase the price of all electronics by 10%:

   This query updates the price for all electronic products.

10. Deleting Data with DELETE:

The DELETE statement removes the specific rows from a table. For instance, to delete a product by name:

This query removes the row containing the outdated product.

Let us now look at some complex, nested queries.

Nested Queries

At its core, a nested SQL query is a query embedded within another query. This approach enables the execution of one query inside the context of another, resulting in more complicated and dynamic data retrieval. Consider it a query inception, in which one query nests within another, resulting in a hierarchy of instructions.

Benefits of Nested SQL Queries:

• Modularity:

  Nested queries improve code modularity by dividing complicated issues into smaller, more manageable chunks.

• Dynamic Filtering:

  These queries support dynamic filtering, which allows you to customise your results based on the outcome of a subquery.

• Enhanced Readability:

  Because nested queries organise your SQL logic hierarchically, they increase code readability and understanding.

• Reduced Round-Trips:

  Combining numerous queries into a layered structure can frequently eliminate the need for repeated round-trips to the database, resulting in improved speed.

Let's look into the basic syntax before going into examples. The structure of a nested SQL query typically follows this pattern:

In this structure, the inner query (wrapped in brackets) is run first, and the results are then utilised in the outer query to generate the final result set.

Let us now look at examples for more clarity.

Example 1: Retrieving Data Based on a Subquery

Consider the following scenario: you wish to collect information on employees who make more than the average income for their department. Here's how to accomplish this using nested queries:

This query uses the inner query to determine the average pay for each department, and the outer query to select workers whose salaries surpass the department's average.

Example 2: Finding Maximum Value within a Group

Consider a situation where you need to find the employee with the highest salary in each department. Nested queries can simplify this task:

The inner query determines the maximum salary within each department, while the outer query chooses the relevant personnel data.

Example 3: Subquery in the FROM Clause - Calculating Aggregated Data

Assume you need to show the entire sales for each product category, including the average sales value. Nested queries in the FROM clause might be useful for this circumstance.

In this example, we utilise two nested subqueries in the FROM clause to determine total and average sales for each product category. The primary query then combines the results to form a complete perspective.

Example 4: Correlated Subqueries - Finding the Nth Highest Salary

Correlated subqueries are ones in which the inner query refers to a column from the outer query. Let's utilise a linked subquery to identify the Nth highest wage in the workers table.

In this case, the inner query counts the number of different salaries that exceed or equal the pay of the current employee in the outer query. The primary query then picks employees whose count meets the desired Nth greatest salary.

Example 5: Subquery in the HAVING Clause for Filtering Aggregated Data

Assume you wish to locate departments where the average income exceeds a specific level. Nested queries in the HAVING clause can help:

The inner query determines the overall average pay, whereas the outer query organises the data by department and only selects departments with average salaries higher than the general average.

Example 6- Multiple Levels of Nesting: Identifying Employees with Maximum Sales

Consider a scenario in which you wish to locate workers who have the highest sales in a particular quarter:

This example contains two layers of nesting. The inner query computes each employee's total sales for the third quarter, while the outer query identifies the employee(s) with the highest quarterly sales.

Let us now look at some further applications of nested queries.

Updating Records Based on Subquery Results

Nested queries are not limited to SELECT statements; they can also be used with UPDATE and DELETE statements. Consider a scenario where you want to increase the salary of all employees in a department by a certain percentage:

In this example, the subquery fetches the department_id for the IT department, while the main query adjusts employee wages inside that department.

Existence Check with NOT EXISTS

Sometimes you may need to check for the absence of certain records. For instance, finding employees who do not have any dependents:

Here, the subquery determines if there is a record in the dependents database for each employee in the outer query. The primary query then picks employees with no dependents.

Delete Records based on Subquery Results

You may find situations in which you need to remove records depending on certain criteria. Assume you wish to eliminate all workers with no sales.

This query deletes employees from the employees table for whom there are no corresponding records in the sales_data table.

Conditional Updates with CASE Statements

Combine the nested queries with the CASE statements to perform conditional updates. Suppose you want to increase the salary for employees who have achieved high sales:

Here, the subquery detects employees whose total sales are above a certain level, and the main query adjusts their salaries.

Nested SQL queries are a great tool for managing complicated data circumstances systematically and effectively. Exploring these additional examples and use cases will help you broaden your SQL toolset and meet a variety of database administration difficulties. As you explore and implement these strategies, you'll get more confident in using nested queries for a variety of tasks.