Mastering SQL:

Fundamentals to Advance Concepts

SQL is an essential tool for managing data in today’s technology industry. Familiarity with the fundamentals concepts of SQL is essential for working with databases, and acquiring expertise in advance concepts can take your skills to the next level. With the right training and practice anyone can gain proficiency in SQL and effectively manage and manipulate data .

Introduction

SQL(structured Query Language) is a domain specific programming language for query and manipulating data in relational database. The most used language for implementing relational queries is SQL. It can be used to perform specific actions such as Inserting, Updating, Deleting ,Querying and Retrieving specific information. SQL is widely used in the filed of data analytics and data science.

SQL Data Types

We have a huge volume of data available to us, and it would be complicated to handle this data if we store all the data in a single data type. Data type comes in handy when we need to handle a variety of data available to us. In SQL their is variety of data type to manipulate an handle different kind of data.

Database:

A database is a structured collection of data that is organized and stored in a way that allows for efficient retrieval and manipulation of that data. They are commonly used in a variety of applications, including business operations, scientific research, healthcare, education, and government.

Creating a Database

A creating a database is used to create a new database into SQL management server.

CREATE DATABASE bytewise_DE;

Alter Database

This command use to alter the structure of existing database. This command use to alter the database or modify the existing database.

ALTER DATABASE bytewise_DE
MODIFY NAME IS = DataEngineers_Bytewise;

Drop Database

The Drop Database command is used to delete an existing database. When you drop a database, all the tables, data and other objects in the database are deleted permanently.

DROP DATABASE DataEngineers_Bytewise;

Types of SQL Commands

Data Definition Language(DDL)

Data Definition Language command are used to define the database schema. DML command are used to create ,modify, and delete database structures but not data.

DDL commands:

⦁ CREATE: This command is used to create the database or its objects like table, index, function, views, store procedure, and triggers. The Syntax of CREATE query is follow:

CREATE TABLE employees (
    ID int NOT NULL UNIQUE,
    Name varchar(255),
    Age int,
    department varchar(255),
    city varchar(255)
);

⦁ ALTER: This command is used to alter the structure of the database. It is also used to add and drop various constraints on an existing table. The Syntax of ALTER query is follow:

ALTER TABLE employees
ALTER COLUMN phone VARCHAR(15);

⦁ DROP: This command is used to remove a column from an existing table. The Syntax of DROP query is follow:

ALTER TABLE employees
DROP COLUMN address;

⦁ TRUNCATE: This command is used to remove all records from a table, including all spaces allocated for the records are removed. This command remove the data inside the data but not a table . It is faster than the DELETE command as it removes all rows in one operation rather than deleting each row one by one.

The Syntax of TRUNCATE query is follow:

TRUNCATE TABLE employees

⦁ RENAME: This command is used to used to rename an existing table or column. The Syntax of CREATE query is follow:

ALTER TABLE employees
RENAME COLUMN Name to emp_Name

Data Manipulation Language(DML)

Data Manipulation Language command are used to make changes into database such as CRUD( create, read, update and Delete) operation.

DML commands:

⦁ SELECT: The select is used to select data from a database. The Syntax of SELECT query is follow:

1) 
SELECT * FROM employees
2)
SELECT Name,Age, department
FROM employees

⦁ INSERT: This command is used to insert one or more rows of data into a database. Before inserting data should ensure that table is already created. The Syntax of INSERT query is follow:

INSERT INTO employees(ID, Name, Age, department, city)
VALUES (1,'sadaf',20 ,'SE','ISLAMABAD');

⦁ UPDATE: This command is used to update existing data into one or multiple rows or columns by using UPDATE and WHERE Clause. The Syntax of UPDATE query is follow:

UPDATE employees 
SET Name= 'Sadaf sultan'
WHERE Age= 20;

⦁ DELETE: This command is used to remove single or multiple existing records from the database tables. The Syntax of DELETE query is follow:

DELETE FROM employees
WHERE Name ='sadaf sultan'

⦁ WHERE: It is used to retrieve data from the database to specify a condition while fetching the data from a single table or by joining with multiple tables. The Syntax of WHERE query is follow:

SELECT * FROM employees
WHERE age > 50

SELECT * FROM employees
WHERE Name = 'sadaf'

⦁ORDER BY: This keyword to use sort data into ascending and descending orders. The syntax of this keyword is follow:

SELECT *
FROM employees
ORDER BY age;
---- by default in ascending order

SELECT *
FROM employees
ORDER BY age DESC;
---- the keyword of DESC is used for descending order

⦁ DISTINCT Keyword: This command is used to remove the duplicate values. this command returns only on different values exiting in the column. The Syntax of DISTINCT query is follow:

SELECT DISTINCT ProductName
FROM Products

SELECT DISTINCT ProductName, UnitPrice, UnitsOnOrder,ReorderLevel
FROM Products
WHERE ReorderLevel BETWEEN 1 AND 10
ORDER BY ReorderLevel

Data Controlling Language(DCL)

Data Controlling Language is a query language that allows users to retrieve and edit data held in databases.

DCL Commands

⦁ GRANT: This command gives users access privileges to the database. The Syntax of INVOKE query is follow:

GRANT CREATE TABLE TO testing
/*grant the role to a user.*/

⦁ INVOKE: This command withdraws the user’s access privileges given by using the GRANT command. The Syntax of INVOKE query is follow:

REVOKE CREATE TABLE FROM testing;

Data Query Language(DQL)

Data Query Language is the group of commands responsible for querying data from a database. It is a component of SQL statement that allows getting data from the database and imposing order upon it. When a SELECT is fired against a table or tables the result is compiled into a further temporary table, which is displayed or perhaps received by the program.

DQL includes a variety of commands and clauses, such as SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. These commands are used to specify which data should be retrieved, where to retrieve it from, how to filter it, how to group and aggregate it, and how to sort it.

DQL Commands

⦁ SELECT: It is used to retrieve data from the database. The Syntax of SELECT query is follow:

SELECT * FROM employees

⦁ GROUP BY : It is used to group the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG). Count is used to returns the number of rows that matches a specified criterion. AVG is used to returns the average value of a numeric column. SUM is used to returns the total sum of a numeric column. MAX is used to returns the largest value of the selected column. MIN is used to returns the Smallest value of the selected column.

The Syntax GROUP BY query is follow:

---- COUNT
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

SELECT COUNT(ProductID)
FROM Products;

----- AVERAGE
SELECT AVG(ProductID)
FROM Products;

---- SUM
SELECT SUM(ProductID)
FROM Products;

---- MAXIMUM 
SELECT MAX (ProductID)
FROM Products;

----- MINIMUM
SELECT MIN(ProductID)
FROM Products;

⦁ COLUMN ALIASES: It is often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword. The Syntax ALIASES is follow:

SELECT TerritoryID, TerritoryDescription AS TerritoryDetails 
FROM Territories

⦁ HAVING: This clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

⦁ BETWEEN , AND & OR : used to select values within a given range. The values can be numbers, text, or dates. The AND is used when we want only on the given range or specified range in this case both values must be satisfied the condition. But by using OR operator if one condition or value is exiting the result be will be shown. The Syntax of WHERE query is follow:

SELECT OrderID,CustomerID, ShipName 
FROM Orders
WHERE OrderID BETWEEN 10270 AND 10300

SELECT OrderID,CustomerID, ShipName 
FROM Orders
WHERE ShipName BETWEEN 'EFG' OR 'ABC'

⦁ IN & LIKE: The IN command use to allow multiple values into WHERE clause. The Syntax of IN query is follow:

SELECT * FROM employees
WHERE department IN ('Software', 'Computer Science', 'information Technology');

LIKE operator is used in WHERE clause to search a specified pattern in a Column. The Syntax of LIKE query is follow:

--This query show all the employees whose names started with A
SELECT * FROM employees
WHERE Name LIKE 'a%';

--This query show all the employees whose names ending with A
SELECT * FROM employees
WHERE Name LIKE '%a';

--This query show all the employees whose names contain A in second position
SELECT * FROM employees
WHERE Name LIKE '%_a';

--This query show all the employees whose names started with S but ended at F
SELECT * FROM employees
WHERE Name LIKE 'S%f';

⦁ IS NULL: It is used to find the null values in a specific column. The Syntax IS NULL query is follow:

SELECT * FROM Employees WHERE department IS NULL;

⦁ TOP N: It is used to specify the number of records which want to return. The Syntax IS NULL query is follow:

SELECT TOP 20*
FROM Orders
ORDER BY EmployeeID

SELECT TOP 10 *
FROM EmployeeTerritories
WHERE EmployeeID = 5
ORDER BY TerritoryID DESC

⦁ UNION : It is used to combines the results of two or more SELECT statements into a single result set, without duplicates. The Syntax of UNION query is follow:

SELECT *
FROM Customers
WHERE Country = 'germany' AND Region IS NULL
UNION
SELECT *
FROM Customers
WHERE Country = 'Mexico' AND Region IS NULL

⦁ INTERSECT: It is used to returns only the rows that are common between two SELECT statements, without duplicates. The Syntax of INTERSECT query is follow:

SELECT *
FROM Customers
WHERE Country = 'germany' AND Region IS NULL
INTERSECT
SELECT *
FROM Customers
WHERE City LIKE 'A%'

⦁ EXCEPT: It is used returns only the rows that are present in the first SELECT statement but not in the second SELECT statement, without duplicates. The Syntax of EXCEPT query is follow:

SELECT *
FROM Customers
WHERE Country = 'germany' AND Region IS NULL
EXCEPT
SELECT *
FROM Customers
WHERE Country = 'Mexico' AND Region IS NULL

JOINS

In SQL joins are used to combine rows from two or more tables based on a related column between them. There are several types of joins available in SQL :

Inner join

The inner join will return only the record where a key find a match in both tables.

SELECT * FROM Orders AS o
INNER JOIN [Order Details]  AS OD 
ON o.OrderID = OD.OrderID

SELECT o.OrderID, o.CustomerID, o.OrderDate  FROM Orders AS o
INNER JOIN Customers  AS c ON o.CustomerID = c.CustomerID

Outer join

The OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

SELECT * FROM Orders AS o
FULL OUTER JOIN [Order Details]  AS OD
ON o.OrderID = OD.OrderID

Self join

A self join is a regular join, but the table is joined with itself

SELECT e1.TitleOfCourtesy,e1.FirstName,e1.LastName,e2.City,e2.Address
FROM Employees e1
INNER JOIN Employees e2 ON e1.Title= e2.Title AND e1.EmployeeID = e2.EmployeeID

Cross join

The Cross Join returns all records from both tables.

SELECT P.ProductName, c.companyName, c.ContactTitle 
FROM Products P
CROSS JOIN Customers c

Right join

The Right Join keyword returns all records from the right table, and the matching records from the left table . The result is 0 records from the left side, if there is no match.

SELECT * FROM Orders AS o
RIGHT JOIN [Order Details]  AS OD
ON o.OrderID = OD.OrderID


SELECT o.OrderID, o.CustomerID, o.OrderDate  FROM Orders AS o
RIGHT JOIN Customers  AS c ON o.CustomerID = c.CustomerID

Left join

The Left Join keyword returns all records from the left table (Table1), and the matching records from the right table (Table2). The result is 0 records from the right side, if there is no match.

SELECT * FROM Orders AS o
LEFT JOIN [Order Details]  AS OD
ON o.OrderID = OD.OrderID


SELECT o.OrderID, o.CustomerID, o.OrderDate  FROM Orders AS o
LEFT JOIN Customers  AS c ON o.CustomerID = c.CustomerID

Left anti-join

One of the join kinds available in the Merge dialog box in Power Query is a left anti join, which brings in only rows from the left table that don’t have any matching rows from the right table

SELECT c.ContactName, c.CompanyName,O.ShipCountry
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL

Right anti-join

One of the join kinds available in the Merge dialog box in Power Query is a right anti join, which brings in only rows from the right table that don’t have any matching rows from the left table

SELECT c.ContactName, c.CompanyName,O.ShipCountry
FROM Orders o
RIGHT JOIN Customers c  ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL

SUB QUERIES

In SQL a subquery is a SQL query nested inside a larger query. A sub query may occur with a SELECT, WHERE and FROM clause. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select. The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.

Single Row query

The query which return only single row of data is called single row query.

SELECT* FROM Products 
WHERE UnitPrice > (SELECT min(UnitPrice) AS Minimum_Price FROM Products)


SELECT  TitleOfCourtesy,FirstName,LastName 
FROM Employees
WHERE Extension>=(SELECT MAX(Extension)  FROM Employees )

Multi Row Query

Multi Row query return the multiple rows of data. WE can use the IN, operator in outer query to handle a subquery that returns multiple rows.

SELECT* FROM Orders
SELECT OrderID,OrderDate , ShipName, ShipCountry FROM Orders WHERE CustomerID IN 
(SELECT CustomerID FROM Customers WHERE Country IN 
(SELECT Country FROM Suppliers ))

Nested Row Query

A subquery can be nested inside other subqueries. A subquery is a SELECT statement that is nested within another SELECT statement and which return intermediate results. SQL executes innermost subquery first, then next level.

SELECT * FROM Orders WHERE CustomerID IN 
(SELECT CustomerID FROM Customers WHERE Country IN 
(SELECT Country FROM Suppliers WHERE SupplierID IN 
(SELECT SupplierID FROM Products  WHERE Discontinued = 0)))

Co- Related Query

Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement. This query is used for row by row processing.

SELECT ProductID,ProductName,UnitPrice
FROM Products p
WHERE
    UnitsInStock IN (
        SELECT MAX (UnitPrice)
        FROM Products p2
        WHERE p2.ProductID = p.ProductID
        GROUP BY p2.ProductName)
ORDER BY
    UnitsOnOrder,
    ProductName;

VIEWS

A view is a virtual table that is based on the result of a SELECT statement. A view can be used to simplify complex queries or to present a subset of data to users without exposing the underlying tables.

Purpose Of Views

The purpose of views is to provide a customized and simplified view of the data for the end-users, without them needing to know the underlying structure of the data. Views providing a layer of abstraction that protects sensitive data or schema changes. These are also known as virtual tables.

Types of Views

There are two types of view :

⦁ Simple view: A simple view is based on a single table and selects all the columns or a subset of the columns from that table.

CREATE VIEW shippersView
AS
SELECT* FROM Shippers WHERE CompanyName='United Package'

⦁ Complex view: A complex view, is based on multiple tables and includes one or more joins or subqueries in the SELECT statement.

CREATE VIEW orders_View_Detail
AS
SELECT OrderID,OrderDate , ShipName, ShipCountry FROM Orders WHERE CustomerID IN 
(SELECT CustomerID FROM Customers WHERE Country IN 
(SELECT Country FROM Suppliers WHERE Country= 'japan' OR Country='uk' ))

Creating view

CREATE VIEW OrderView 
AS
SELECT *
FROM Orders

Altering view

ALTER VIEW orderView 
AS 
SELECT orderID,OrderDate
FROM Orders

Dropping view

DROP VIEW orderView

INDEXES

a SQL index is a data structure used by a database management system to quickly locate and retrieve data from a database table.

An index works like a book’s table of contents, providing a list of keywords or phrases and their corresponding page numbers. In the same way, a SQL index contains a list of values from one or more columns of a table and their corresponding row locations in the table.

Clustered Index

A clustered index is used to define the order or to sort the table or arrange the data by alphabetical order just like a dictionary. It is faster than non clustered index.

CREATE CLUSTERED INDEX idx_orderID_ShipCity ON Orders(OrderID, ShipCity)

Non-Clustered Index

A non-clustered index collects the data at one place and records at another place. It is slower than the clustered index. It demands more memory to execute the operations.

CREATE NONCLUSTERED INDEX idx_orderID_ShipCity ON Orders(OrderID, ShipCity)

CREATE NONCLUSTERED INDEX idx_regionID_RegionDescription ON 
Region(RegionID, RegionDescription)

Creating a Index

CREATE INDEX idx_EmployeeID ON Employees(EmployeeID)

Altering a Index

ALTER INDEX idx_EmployeeID ON Employees(EmployeeID)  REBUILD

Dropping a Index

DROP INDEX idx_EmployeeID ON Employees(EmployeeID)

Using a Index

execute sp_helpindex Employees

CONDITIONAL STATEMENTS(LOOPS)

In SQL Server, a loop is the technique where a set of SQL statements are executed repeatedly until a condition is met.

WHILE Loop

SQL Server supports the WHILE loop. The execution of the statements can be controlled from within the WHLE block using BREAK and CONTINUE keywords.

DECLARE @i INT = 10;
 
WHILE @i <= 30
 BEGIN
  PRINT (@i);
  SET @i = @i + 10;
 END;

CASE

The CASE statement in SQL allows you to perform conditional logic in a SELECT statement, which can be used to transform data or create calculated fields based on certain conditions. Here is an example of a SQL query that uses the CASE statement:

SELECT employee_id, employee_name, salary, department,
       CASE 
            WHEN salary >= 100000 THEN 'High'
            WHEN salary >= 50000 AND salary < 100000 THEN 'Medium'
            ELSE 'Low'
       END AS salary_range
FROM employees;