Advanced SQL:

Stored Procedure:

• A Stored Procedure is nothing but already written SQL queries that are stored in the database.
• If you are using same Queries over and over then put those queries into the Stored Procedure.
• After put the SQL Statements in the Stored Procedure then you can run the Stored Procedure from the database commands Environment.

• Stored procedure can be defined as set of logical group of SQL statements or pre compiled one or more SQL Statements to perform a specific task.

Advantages of Stored Procedure:

• The main Benefit of Stored Procedure, it saves the time since stored procedure are executed on the server side and perform set of actions before returning the results to client side.

• By using Stored Procedure method we can reduce the network traffic because it allows set of actions  to be executed(if we send SQL Query which is executing again and again  to the server through network ,the network might be disconnected ,therefore the expecting result does not return to client side.
• It improves the performance of the database (since set of actions executed at once)

• SQL Statements needs to be compiled every time whenever it sent for execution whereas Stored Procedure required only one compilation when stored procedure is created.

                 How to create Stored Procedure:

Firstly we need to create a table called emp_details

CREATE TABLE emp_details

(

[emp_id] [int] Identity (1,1) NOT NULL,

[FirstName][nvchar](300) NOT NULL,

[LastName] [nvchar](300)  NULL,

[Emali] [nvchar](100) NULL

)

Here i am going to insert the data for the above structure of table:

insert into emp_details(FirstName,LastName,Email)values(‘lucky’,’raju’,’lucky&xyz.com’)

insert into emp_details(FirstName,LastName,Email)values(‘akhil’,’rao’,’akki&xyz.com’)

insert into emp_details(FirstName,LastName,Email)values(‘naga’,’raju’,’nag&xyz.com’)

insert into emp_details(FirstName,LastName,Email)values(‘krishna’,’raju’,’krish&xyz.com’)

Now we are doing in the first step creating Stored Procedure

Syntax:

Create Procedure procedure-name

(

Input parameters,

Output Parameters(if need)

)

As

Begin

Sql statement is used in Stored Procedure

End

Here if you need to create Stored Procedure which you want return empname whose emp_id is given as Input Parameter to the stored Procedure then it will look like as below

Example 1:

Create Procedure GetEmpname //GetEmpname is name of the Stored Procedure

{

@emp_id int // input parameter emp_id of the employee details table

)

As

Begin

Select firstName +’   ‘+LastName from emp_details WHERE Emp_id=@Emp_id;

End

Example 2:

Stored Procedure can be used to insert, update or delete a SQL Statement. Suppose you want insert a value into the Emp_details table.

create procedure InsertEmprecord

(

@Empfirstname varchar (100),

@EmpLastname varchar (100),

@EmpEmail varchar (50)

As

Begin

Insert into emp_details(FirstName,LastName,Email)values(‘@Empfirstname’,@EmpLastname,@EmpEmail)

End

Finally we will see how to execute Stored Procedure in SQL Server:

To Execute Stored Procedure in SQL Server we use “Exec” (or) “Execute” Keyword.

Suppose if you want execute a Stored Procedure name as ” GetEmpname “then

we have to write command as below

Exec GetEmpname

OR

Execute GetEmpname

Conclusion:

If same SQL Query is required to execute again and again so it takes lot of network traffic to send information to the server. If you create Stored procedure it reduce the network traffic by reducing the amount of information sent over the network because all pre compiled one or more SQL Statements put into the Stored Procedure..

Views in SQL:

• Views are virtual tables that don’t store data themselves but provide a customized view of data from one or more underlying base tables.
• They act as a lens, focusing on specific data or simplifying complex queries.
• Think of them as saved SQL queries that can be treated like regular tables.

Key Benefits of Using Views:

Data Abstraction:

Hide underlying table complexities and present a tailored view to users.

Security:

Control data access by granting permissions to views instead of base tables.

Reusability:

Encapsulate frequently used queries for convenience and consistency.

Query Simplification:

 Break down complex queries into smaller, more manageable views.

Data Isolation:

Protect sensitive data from direct access.

Creating Views:

Syntax:

SQL

CREATE VIEW view_name AS

SELECT column1, column2, …

FROM base_table

WHERE condition(s);

Example:

SQL

CREATE VIEW ActiveCustomers AS

SELECT CustomerID, FirstName, LastName

FROM Customers

WHERE IsActive = 1;

Using Views:

Query them like regular tables:

SQL

SELECT * FROM ActiveCustomers;

Example 2:

The following view displays only active Employees:

CREATE VIEW ActiveEmployees AS

SELECT EmployeeID, FirstName, LastName, Department

FROM Employees

WHERE Status = ‘Active’;

Example 3: (View with Where clause)

The following view filters orders from last 30 days and making it easier to focus on recent activity.

CREATE VIEW RecentOrders AS

SELECT * FROM Orders

WHERE OrderDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

 View with Aggregate Functions:

This view summarizes sales data by month and year, providing valuable insights for reporting and analysis.

CREATE VIEW MonthlySalesTotals AS

SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month,

       SUM(TotalAmount) AS TotalSales

FROM Orders

GROUP BY YEAR(OrderDate), MONTH(OrderDate);

Modifying Views:

Use CREATE OR REPLACE VIEW to update the defining query.

Dropping Views:

SQL

DROP VIEW view_name;

Important Considerations:

• Views don’t store data, but reflect changes in base tables.
• Updating data through views might have limitations depending on their definition.
• Not all database systems support all view capabilities.

INDEX:

• Indexes are special data structures that optimize the speed of data retrieval in SQL databases.
• They act like the index at the back of a book, helping you quickly find specific information without scanning every page.
• Indexes don’t change the underlying data, but they create a separate sorted structure for faster lookups.

Why Use Indexes?

Performance:

Significantly improve query performance, especially for large tables and frequent searches.

Filtering and Sorting:

Accelerate operations that filter or sort data based on indexed columns.

Uniqueness Enforcement:

Enforce unique constraints on columns (e.g., primary keys), ensuring data integrity.

Types of Indexes:

Clustered Index: Organizes the physical order of data in the table based on the index key. Each table can have only one clustered index.

Non-Clustered Index: Separate structure from the table data, containing a copy of the indexed columns and pointers to the actual data rows. Each table can have multiple non-clustered indexes.

Unique Index: Ensures that each value in the indexed column(s) is unique.

Composite Index: Indexes multiple columns together, useful for queries that filter or sort on multiple criteria.

Creating Indexes:

Syntax:

CREATE INDEX index_name

ON table_name (column_name);

Use code with caution. Learn more

Example:

CREATE INDEX idx_CustomerName

ON Customers (LastName, FirstName);  // Composite index

Key Considerations:

• Indexes improve query performance, but they come with overhead for creation and maintenance.
• Choose wisely which columns to index, considering query patterns and data updates.
• Over-indexing can slow down data modifications (INSERT, UPDATE, DELETE).
• Monitor index usage and performance to ensure they’re beneficial.

Remember:

• Indexes are powerful tools for optimizing database performance.
• Use them strategically to enhance query speed and overall database efficiency.
• Understand their benefits and trade-offs to make informed decisions about index usage.

Here are some more examples of indexes in SQL to solidify your understanding:

1. Filtering with an Index:

Let’s say you have a large table of Orders with columns for OrderID, CustomerID, OrderDate, and Product. You frequently need to find orders placed by a specific customer in the last month.

First we will understand Without an index on CustomerID and OrderDate:

SQL

SELECT * FROM Orders

WHERE CustomerID = 123

AND OrderDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

This query needs to scan all rows in the table to find matching records, potentially impacting performance.

Now we will understand using an index on CustomerID and OrderDate:

SQL

SELECT * FROM Orders

WHERE CustomerID = 123

AND OrderDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Now, the database can leverage the index to quickly locate relevant rows instead of scanning the entire table, significantly improving query speed.

2. Sorting with an Index:

Imagine you have a table of Employees with columns for EmployeeID, LastName, and Department. You often need to display a list of employees sorted by last name within each department.

First we will know the problem Without an index on LastName and Department:

SQL

SELECT * FROM Employees

ORDER BY Department, LastName;

This query requires sorting the entire table based on department and then last name, potentially taking longer for large datasets.

Now we solve the problem With an index on LastName and Department (composite index):

SQL

SELECT * FROM Employees

ORDER BY Department, LastName;

Now, the database utilizes the sorted index structure to efficiently retrieve data in the desired order without sorting the entire table, leading to faster results.

3. 3. Unique Indexes for Data Integrity:

Consider a User’s table with columns for UserID (primary key), Username, and Email. You want to ensure that usernames and emails are unique across all users.

Creating unique indexes:

SQL

CREATE UNIQUE INDEX idx_Username ON Users (Username);

CREATE UNIQUE INDEX idx_Email ON Users (Email);

These unique indexes prevent duplicate entries for username and email, enforcing data integrity and preventing invalid data insertions.

By understanding these examples, you can clearly see how indexes can play a crucial role in optimizing the performance and integrity of your SQL databases.

Subqueries:

What are Subqueries?

• Subqueries are queries embedded within other queries.
• They act as temporary helper queries, providing intermediate results to the main query.
• Think of them as mini-queries that enhance the capabilities of your main SQL statements.

Why Use Subqueries?

Complex Data Retrieval: Perform intricate data filtering, comparison, or calculations within a single query.

Conditional Logic: Build queries that depend on results of other queries, enabling dynamic decision-making.

Aggregation and Analysis: Calculate summary values or derive information from multiple tables within a single query.

Types of Subqueries:

Single-Row Subqueries:

Return one value

Ex: a maximum value, a specific customer ID

Multi-Row Subqueries:

 Return multiple values

Ex: a list of product IDs, a set of customer names

Correlated Subqueries:

It Dependent on values from the outer query, making them more dynamic.

Where to Use Subqueries:

SELECT Clause:

Introduce new columns or filter results based on subquery output.

WHERE Clause:

Filter rows based on conditions derived from subquery results.

FROM Clause:

Treat subquery results as a virtual table, enabling complex joins.

HAVING Clause:

 Apply subquery conditions to groups of data.

Example: Finding Products with Above-Average Prices

SQL

SELECT ProductName, Price

FROM Products

WHERE Price > (SELECT AVG(Price) FROM Products);

More Subquery Examples to Deepen Your Understanding:

1. Using Subqueries in the SELECT Clause:

This example finds customers who placed orders after a specific date:

SQL

SELECT Customers.Name, Orders.OrderID

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

WHERE Orders.OrderDate > (SELECT MIN(OrderDate) FROM Orders);

Here, the subquery finds the minimum order date in the Orders table, and the main query selects customers whose orders are placed after that date.

2. Filtering with a Correlated Subquery:

Let’s say you have a table of Employees with columns for EmployeeID, Department, and Salary. You want to find employees in each department earning more than the average salary for that department.

SQL

SELECT *

FROM Employees

WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = Employees.Department);

The subquery calculates the average salary for each department based on the current employee’s department (obtained through the Employees.Department reference). The main query then filters employees whose salary exceeds the department average.