Advanced SQL

Introduction:

Building on our SQL basics and intermediate knowledge, this tutorial, is the second part of SQL 101, here we will delve deeper into advanced SQL techniques, which can help in managing and manipulating complex relational databases.

Part IV: SQL Constraints

4.1 Understanding SQL Constraints

SQL Constraints are used to specify rules for the data in a table. Constraints ensure the reliability and accuracy of the data in the table.

4.2 Primary Key

A primary key is used to uniquely identify each record in a table.

CREATE TABLE Employees (
    ID int PRIMARY KEY,
    Name text,
    Age int,
    Salary real
);

The PRIMARY KEY constraint uniquely identifies each record in the Employees table. This means that the ID field is unique for each record and cannot be NULL.

4.3 Foreign Key

A foreign key is used to link two tables together.

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderNumber int,
    EmployeeID int,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

Here, EmployeeID in the Orders table is a foreign key that refers to ID in the Employees table.

4.4 Unique

The UNIQUE constraint ensures that all values in a column are different.

CREATE TABLE Employees (
    ID int PRIMARY KEY,
    Name text UNIQUE,
    Age int,
    Salary real
);

This ensures that the Name is unique for each record.

---

Part V: Advanced SQL Queries

5.1 Using Alias

SQL aliases are used to give a table or a column a temporary name. They make column names more readable.

SELECT e.Name AS Employee, e.Salary AS Income 
FROM Employees AS e;

This command selects all Name and Salary from the Employees table but results will be displayed as Employee and Income respectively.

5.2 Conditional Selection

The CASE statement goes through conditions and returns a value when the first condition is met.

SELECT Name, Salary,
CASE 
    WHEN Salary > 50000 THEN 'Above Average'
    ELSE 'Below Average' 
END AS SalaryStatus
FROM Employees;

This command adds a new column SalaryStatus that categorizes employees based on their salary.

5.3 IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

SELECT * FROM Employees
WHERE Name IN ('John Doe', 'Jane Doe');

This command selects all fields from Employees where the Name is either ‘John Doe’ or ‘Jane Doe’.

---

Part VI: Stored Procedures

6.1 What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save so the code can be reused over and over again.

6.2 Creating a Stored Procedure

Let’s assume we frequently need to fetch all employees with a salary above a certain value. Instead of writing this query every time, we can create a stored procedure.

CREATE PROCEDURE SelectHighlyPaid @Salary real
AS
SELECT * FROM Employees WHERE Salary > @Salary;
GO;

6.3 Calling a Stored Procedure

Now that we have our stored procedure, we can execute it.

EXEC SelectHighlyPaid 50000;

This command calls the SelectHighlyPaid stored procedure and retrieves employees earning more than 50000.

---

Conclusion

From basic commands to advanced procedures, SQL is a versatile tool that can handle complex queries with ease. By mastering these advanced concepts and continually practicing, you’re well on your way to becoming an SQL expert!