Concepts of SQL

SQL and Benefits of SQL

SQL (Structured Query Language) is a powerful language used for managing and manipulating relational databases. It is the standard language for interacting with database management systems (DBMS) like MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and many others. SQL allows users to perform various operations on the data stored in relational databases, including querying, updating, inserting, and deleting data.

Key Features of SQL

1. Data Retrieval: SQL enables users to retrieve data from one or more tables using complex queries with conditions, sorting, grouping, and joining multiple tables.
2. Data Manipulation: SQL allows for the insertion, updating, and deletion of data within tables.
3. Data Definition: SQL provides commands to define and modify database schema, including creating, altering, and dropping tables and other database objects.
4. Data Control: SQL includes commands to control access to data and database objects, ensuring data security through user permissions and roles.
5. Transaction Control: SQL supports transactions, allowing multiple operations to be executed as a single unit, ensuring data integrity and consistency.

Benefits of SQL

1. Easy to Learn and Use:
   • SQL is relatively easy to learn due to its English-like syntax.
   • Even beginners can quickly write queries to interact with databases.
2. Standardized Language:
   • SQL is a standardized language, which means that it is supported by most major database systems.
   • This makes it easier to switch between different databases without learning a new language.
3. Efficient Query Processing:
   • SQL allows users to retrieve large amounts of data quickly and efficiently.
   • Database engines are optimized to handle complex queries and operations.
4. Powerful Data Manipulation:
   • SQL provides powerful capabilities to filter, aggregate, sort, and join data from multiple tables.
   • This allows for complex data analysis and reporting.
5. Scalability:
   • SQL databases are highly scalable, making them suitable for small applications and large enterprise systems.
   • They can handle a vast amount of data and concurrent users.
6. Data Integrity and Security:
   • SQL supports constraints, triggers, and transactions to maintain data integrity.
   • It provides robust security features to control access and permissions at various levels.
7. Integration with Programming Languages:
   • SQL can be easily integrated with various programming languages (like Java, Python, C#) to build applications.
   • Libraries and APIs are available to interact with databases using SQL from within code.
8. Support for Complex Queries:
   • SQL allows users to write complex queries involving multiple nested subqueries, joins, and aggregations, enabling sophisticated data analysis.
9. Community and Support:
   • SQL has a large and active community, which means extensive resources, documentation, and support are available.
   • Users can find solutions to common problems quickly through forums and online communities.

Components of SQL

SQL (Structured Query Language) is composed of several key components, each serving a specific purpose in managing and manipulating data within a relational database. These components include the various types of SQL statements and their functionalities. Here’s a detailed overview of the main components of SQL:

1. Data Query Language (DQL)

DQL is primarily concerned with querying and retrieving data from the database. It includes the SELECT statement, which is used to fetch data from one or more tables.

• SELECT: Retrieves data from the database.
  Syntax:
  SELECT column1, column2 FROM table_name WHERE condition;

Example:
Bhanu wants to retrieve a list of all customers from a database who have purchased a specific book:

SELECT CustomerName, PurchaseDate
FROM Customers
WHERE BookID = 101;

2. Data Definition Language (DDL)

DDL statements are used to define and manage database schema and structure. They include commands to create, modify, and delete database objects like tables, indexes, and views.

• CREATE: Creates a new table, view, or other database objects.sqlCopy codeCREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(50), Position VARCHAR(50), Salary DECIMAL(10, 2) );
• ALTER: Modifies an existing database object.
  Syntax:
  ALTER TABLE Employees ADD COLUMN Department VARCHAR(50);
• DROP: Deletes a database object.
  DROP TABLE Employees;
• TRUNCATE: Removes all records from a table but keeps the structure.
  Syntax:
  TRUNCATE TABLE Employees;

Example:
Akhil needs to create a new table to store information about authors:

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100),
    Nationality VARCHAR(50)
);

3. Data Manipulation Language (DML)

DML is used for data manipulation and includes commands that allow users to insert, update, and delete data within the database tables.

• INSERT: Adds new rows of data to a table.
  Syntax:
  INSERT INTO Employees (EmployeeID, Name, Position, Salary) VALUES (1, 'Yaswanth', 'Software Engineer', 75000.00);
• UPDATE: Modifies existing data in a table.
  Syntax:
  UPDATE Employees SET Salary = 80000.00 WHERE EmployeeID = 1;
• DELETE: Removes data from a table.
  Syntax:
  DELETE FROM Employees WHERE EmployeeID = 1;

Example:
Yaswanth wants to update the salary of an employee in the database:

UPDATE Employees
SET Salary = 85000.00
WHERE Name = 'Bhanu';

4. Data Control Language (DCL)

DCL is used to control access to data within the database. It includes commands to grant or revoke permissions to users or roles.

• GRANT: Provides specific privileges to users.sqlCopy codeGRANT SELECT, INSERT ON Employees TO User1;
• REVOKE: Removes previously granted privileges.
  Syntax:
  REVOKE INSERT ON Employees FROM User1;

Example:
Bhanu wants to grant read and write permissions to Akhil for the Books table:

GRANT SELECT, INSERT, UPDATE ON Books TO Akhil;

5. Transaction Control Language (TCL)

TCL manages transactions within a database, allowing for commit and rollback operations to maintain data integrity.

• COMMIT: Saves all changes made in the current transaction.
  syntax:
  COMMIT;
• ROLLBACK: Reverts changes made in the current transaction.
  Syntax:
  ROLLBACK;
• SAVEPOINT: Sets a point within a transaction to which you can later roll back.
  Syntax:
  SAVEPOINT SavePoint1;

Example:
During a transaction, Akhil realizes a mistake and wants to undo the changes:

BEGIN TRANSACTION;

UPDATE Books
SET Price = 18.99
WHERE BookID = 202;

ROLLBACK;  -- Reverts the above update

COMMIT;  -- Finalizes the transaction

Literals, Integers and Data types

In SQL, literals, integers, and data types are foundational concepts that define how data is represented, stored, and manipulated within a database. Understanding these concepts is crucial for effective database design and query writing. Let’s explore each of these in detail.

Literals

Literals are fixed values that appear directly in SQL statements. They represent constant data and can be of various types, including numeric, string, date, and more.

1. Numeric Literals:
   • Numeric literals represent numbers and can be integers or floating-point numbers.
   • Examples:
     SELECT * FROM Products WHERE Price = 100;
SELECT * FROM Orders WHERE Quantity > 10;
2. String Literals:
   • String literals represent text and are enclosed in single quotes.
   • Examples:
     SELECT * FROM Customers WHERE Name = 'Bhanu';
SELECT * FROM Books WHERE Title = 'SQL Fundamentals';
3. Date and Time Literals:
   • Date and time literals represent date and time values. The format may vary depending on the database system.
   • Examples:
     SELECT * FROM Orders WHERE OrderDate = '2024-08-07';
SELECT * FROM Events WHERE EventTime = '2024-08-07 10:30:00';
4. Boolean Literals:
   • Boolean literals represent truth values, typically TRUE and FALSE.
   • Examples:
     SELECT * FROM Users WHERE IsActive = TRUE;
SELECT * FROM Products WHERE IsAvailable = FALSE;

Integers

Integers in SQL are a type of numeric literal used to represent whole numbers. They are used in various operations, such as calculations, conditions, and data storage.

1. Integer Literals:
   • Integers are written as a sequence of digits without decimal points.
   • Examples:
     SELECT * FROM Inventory WHERE StockCount > 50;
SELECT * FROM Employees WHERE EmployeeID = 123;
2. Integer Operations:
   • SQL supports arithmetic operations with integers, such as addition, subtraction, multiplication, and division.
   • Examples:
     SELECT Price * 2 AS DoublePrice FROM Products;
SELECT Salary + 5000 AS NewSalary FROM Employees WHERE EmployeeID = 1;
3. Integer Data Types:
   • SQL provides specific data types to store integers, such as INT, SMALLINT, BIGINT, etc.
   • Examples:
     REATE TABLE Orders ( OrderID INT PRIMARY KEY, Quantity SMALLINT, TotalAmount BIGINT );

Summary

• Literals: Represent constant values in SQL statements.
• Integers: Numeric literals representing whole numbers, often used in calculations and conditions.
• Data Types: Define the nature of data stored in columns, ensuring data integrity and optimizing storage.

NULL and Pseudo Columns

In SQL, NULL and pseudo columns are important concepts that allow for more flexible and powerful database management. Understanding how to work with these can significantly improve the way you interact with relational databases. Here’s a detailed look at each:

NULL

NULL is a special marker used in SQL to indicate that a data value does not exist in the database. It represents an unknown or missing value and is different from zero or an empty string.

Key Characteristics of NULL

1. Absence of Value:
   • NULL represents the absence of a value in a column. It is used when the actual value is unknown or not applicable.
2. Data Type Compatibility:
   • NULL can be used with any data type, including integers, strings, dates, etc.
3. Comparisons with NULL:
   • NULL is not equal to anything, including itself. To check for NULL values, SQL provides the IS NULL and IS NOT NULL operators.
   SELECT * FROM Employees WHERE ManagerID IS NULL; -- Finds employees without a manager SELECT * FROM Orders WHERE Discount IS NOT NULL; -- Finds orders with a discount
4. Impact on Expressions:
   • Any arithmetic or string operation involving NULL results in NULL. For example, NULL + 10 or CONCAT(NULL, 'text') results in NULL.

Pseudo Columns

Pseudo columns are special columns provided by SQL databases that do not physically exist in the table but can be used in SQL queries as if they were actual columns. They are often used to retrieve metadata or perform special operations.

Common Pseudo Columns

1. ROWNUM/ROW_NUMBER():
   • ROWNUM is a pseudo column in Oracle used to limit the number of rows returned by a query. Other databases like SQL Server and PostgreSQL use the ROW_NUMBER() function.
   SELECT * FROM Employees WHERE ROWNUM <= 10; -- Oracle: Limits to first 10 rows -- Using ROW_NUMBER() with OVER() SELECT *, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber FROM Employees;

Summary

• NULL: Represents the absence of a value and requires special handling in SQL queries.
• Pseudo Columns: Special columns that provide metadata or support special operations, enhancing the functionality of SQL queries.

Embedded SQL and Lexical Conventions

Embedded SQL and lexical conventions are important concepts in SQL, especially when integrating SQL with programming languages and understanding how SQL syntax is structured. Let’s explore each of these concepts in detail.

Embedded SQL

Embedded SQL refers to the technique of embedding SQL statements directly within a host programming language, such as C, C++, Java, or COBOL. This allows developers to interact with a database directly from their application code, making it easier to perform database operations as part of the application logic.

Key Features of Embedded SQL

1. Integration with Host Language:
   • Embedded SQL allows SQL commands to be executed within a host language program. This integration helps in handling database operations within the application code.
2. Preprocessor:
   • A preprocessor translates embedded SQL statements into host language code, making the SQL commands executable within the program.
3. Data Exchange:
   • Host variables are used to exchange data between the SQL environment and the host programming environment.
4. Transaction Management:
   • Embedded SQL provides support for managing database transactions, including BEGIN, COMMIT, and ROLLBACK.
5. Error Handling:
   • SQL provides mechanisms to handle errors within embedded SQL, such as using SQLCODE and SQLSTATE to capture error information.

Lexical Conventions

Lexical conventions in SQL define the basic syntax rules and structure of SQL statements. They determine how SQL statements are written and interpreted by the SQL engine. Understanding these conventions is crucial for writing syntactically correct SQL queries.

Key Lexical Conventions

1. Identifiers:
   • Identifiers are names used to define database objects such as tables, columns, views, etc.
   • They can include letters, digits, and underscores, but must start with a letter. Identifiers can be case-sensitive or case-insensitive, depending on the database system.
   • Examples:
     CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(50) );
SELECT EmployeeID, Name FROM Employees;
2. Keywords:
   • Keywords are reserved words in SQL that have special meanings. They are used to perform operations and define SQL statements.
   • Examples of keywords include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, WHERE, FROM, etc.
   • Keywords are typically case-insensitive in SQL.
3. Literals:
   • Literals represent constant values in SQL statements. They can be numeric, string, date, or boolean literals.
   • Examples:
     SELECT * FROM Products WHERE Price = 100;
SELECT * FROM Customers WHERE Name = 'Bhanu';
SELECT * FROM Orders WHERE OrderDate = '2024-08-07';
4. Comments:
   • Comments are used to include explanatory notes within SQL code. They are ignored by the SQL engine.
   • Single-line comments:
     -- This is a single-line comment SELECT * FROM Employees;
   • Multi-line comments:
     /* This is a multi-line comment */ SELECT * FROM Employees;
5. Operators:
   • Operators are used to perform operations on data, such as arithmetic operations, comparisons, and logical operations.
   • Examples:
     SELECT Salary * 1.1 FROM Employees;
WHERE Salary > 50000;
SELECT * FROM Employees WHERE Salary > 50000 AND Department = 'HR';
6. Delimiters:
   • Delimiters are characters that separate SQL statements and elements within statements. Common delimiters include commas, parentheses, and semicolons.
   • Examples:
     SELECT Name, Salary FROM Employees;
(Name, Salary) VALUES ('Akhil', 70000);
7. Whitespace:
   • Whitespace includes spaces, tabs, and newlines. SQL statements are generally insensitive to whitespace, allowing for flexible formatting.
   • Example:
     SELECT EmployeeID, Name FROM Employees WHERE Salary > 50000;

Summary

• Embedded SQL: Allows SQL statements to be embedded within a host programming language, facilitating database operations directly from the application code.
• Lexical Conventions: Define the basic syntax rules and structure of SQL statements, including identifiers, keywords, literals, comments, operators, delimiters, and whitespace.

Naming in SQL

Naming in SQL is a critical aspect of database design and management. Proper naming conventions help ensure clarity, consistency, and maintainability of database schemas. This involves naming tables, columns, indexes, constraints, and other database objects in a way that accurately reflects their purpose and makes them easily understandable to anyone who interacts with the database.

1. General Naming Rules

1. Length:
   • Keep names concise but descriptive. Most databases have a limit on the length of identifiers (e.g., 30 characters for SQL Server, 64 characters for MySQL).
2. Case Sensitivity:
   • SQL is generally case-insensitive for identifiers, but some databases (e.g., PostgreSQL) are case-sensitive if quotes are used. Stick to a consistent case style (e.g., all uppercase or all lowercase).
3. Special Characters:
   • Avoid using special characters (e.g., spaces, hyphens) in names. Use underscores or camelCase for multi-word names.
4. Reserved Words:
   • Do not use SQL reserved keywords (e.g., SELECT, TABLE, DATE) as names. If necessary, use quotes to delimit identifiers, but this is not recommended.
5. Uniqueness:
   • Ensure that names are unique within their scope. For example, table names must be unique within a database schema.

Naming of the Objects and Parts and How to Refer them

in SQL, the term “objects” refers to various components within a database that are used to store, manage, and manipulate data. Each object type serves a different purpose and is essential for organizing and interacting with data in a relational database management system (RDBMS). Here’s an overview of the primary SQL objects and their functions:

1. Tables

Definition: Tables are the fundamental building blocks of a database. They store data in rows and columns, where each row represents a record and each column represents a field of that record.

• Example:
  CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(100) );

2. Columns

Definition: Columns are the vertical divisions of a table, where each column holds a specific type of data. Columns are defined when a table is created.

• Example:
  ALTER TABLE Customer ADD DateOfBirth DATE;

3. Rows

Definition: Rows represent individual records in a table. Each row contains data for all columns defined in the table.

• Example:
  INSERT INTO Customer (CustomerID, FirstName, LastName, Email) VALUES (1, 'John', 'Doe', 'john.doe@example.com');

4. Indexes

Definition: Indexes are used to speed up the retrieval of rows from a table. They are created on columns that are frequently used in queries to improve performance.

• Example:
  CREATE INDEX IDX_Customer_Email ON Customer (Email);

5. Views

Definition: Views are virtual tables created by querying one or more tables. They provide a way to simplify complex queries and present data in a specific format without altering the actual tables.

• Example:
  CREATE VIEW View_CustomerEmail AS SELECT FirstName, LastName, Email FROM Customer;

6. Sequences

Definition: Sequences are objects used to generate unique numeric values, often used for primary key columns.
Example:
CREATE SEQUENCE CustomerID_Seq START WITH 1 INCREMENT BY 1;

Summary

• Tables: Store data in rows and columns.
• Columns: Define the types of data stored in a table.
• Rows: Represent individual records.
• Indexes: Improve query performance by indexing columns.
• Views: Provide virtual tables for complex queries.
• Stored Procedures: Encapsulate reusable SQL logic.
• Functions: Return values and are used in expressions.
• Triggers: Automatically execute in response to changes in a table.
• Constraints: Enforce data integrity rules.
• Sequences: Generate unique values, often for primary keys.
• Schemas: Organize and manage database objects.
• User Defined Types (UDTs): Define custom data types.

Understanding these SQL objects and how they work together helps in designing and managing a relational database effectively. If you need more details or examples on any specific SQL object, let me know!

Literals Text Integer Number

In SQL, literals are constant values that are directly embedded in the SQL statements. They represent fixed data values and can be of various types, including text, integer, and number literals. Here’s an overview of each:

1. Text Literals

Text literals are sequences of characters enclosed in single quotes ('). They are used to represent textual data.

Key Points:

• Enclose text literals in single quotes.
• Use two single quotes to include an apostrophe within a text literal.

Examples:

1. Basic Text Literal:
   SELECT 'Hello, Bhanu!' AS Greeting;
2. Inserting Text into a Table:
   INSERT INTO Employees (FirstName, LastName) VALUES ('Bhanu', 'Singh');
3. Text Literal with Apostrophe:
   SELECT 'Bhanu''s Project' AS ProjectName;
4. Using Text Literals in WHERE Clause:
   SELECT * FROM Customers WHERE FirstName = 'Bhanu';

2. Integer Literals

Integer literals are whole numbers without a fractional component. They can be positive or negative.

Key Points:

• Integer literals are used for operations that require whole numbers.
• They do not have decimal points.

Examples:

1. Basic Integer Literal:sqlCopy codeSELECT 42 AS AnswerToLife;
2. Inserting Integers into a Table:
   INSERT INTO Products (ProductID, ProductName, Quantity) VALUES (1, 'Laptop', 10);
3. Using Integer Literals in Calculations:
   SELECT EmployeeID, Salary, Salary + 500 AS UpdatedSalary FROM Employees;
4. Using Integer Literals in WHERE Clause:
   SELECT * FROM Orders WHERE Quantity = 5;

3. Numeric Literals

Numeric literals include integers, decimals, or floating-point numbers, representing any numeric value, including those with fractional parts.

Key Points:

• Numeric literals can have decimal points.
• They can represent both whole numbers and fractional numbers.

Examples:

1. Basic Numeric Literal:
   SELECT 3.14159 AS Pi;
2. Inserting Numeric Values into a Table:
   INSERT INTO Orders (OrderID, Amount) VALUES (1001, 299.99);
3. Using Numeric Literals in Calculations:sqlCopy codeSELECT ProductName, Price, Price * 1.2 AS PriceWithTax FROM Products;
4. Using Numeric Literals in WHERE Clause:
   SELECT * FROM Products WHERE Price > 100.00;

Data Types

In SQL, data types are used to define the nature of the data that can be stored in a table’s columns. Each column in a database table must have a data type. Here’s a comprehensive overview of commonly used SQL data types:

1. String Data Types

These data types are used to store character data.

• CHAR(size): Fixed-length string. The size parameter defines the number of characters. E.g., CHAR(10) reserves 10 characters.
• VARCHAR(size): Variable-length string. The size parameter defines the maximum number of characters. E.g., VARCHAR(255).
• TEXT: Variable-length string with a maximum length of 65,535 characters.

Examples:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    Password CHAR(32),
    Biography TEXT
);

2. Numeric Data Types

These data types are used to store numeric values.

• INT: Integer value. Typically ranges from -2,147,483,648 to 2,147,483,647.
• SMALLINT: Smaller range integer value. Typically ranges from -32,768 to 32,767.
• BIGINT: Larger range integer value. Typically ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• DECIMAL(p, s) or NUMERIC(p, s): Fixed-point number. p is the total number of digits, and s is the number of digits after the decimal point.
• FLOAT: Floating-point number. Precision can vary.
• DOUBLE: Double-precision floating-point number.

Examples:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10, 2),
    StockCount INT,
    Weight FLOAT
);

3. Date and Time Data Types

These data types are used to store date and time values.

• DATE: Stores date values in the format YYYY-MM-DD.
• TIME: Stores time values in the format HH:MI:SS.
• DATETIME: Stores both date and time values in the format YYYY-MM-DD HH:MI:SS.
• TIMESTAMP: Stores a timestamp, typically in the format YYYY-MM-DD HH:MI:SS. Often used to track changes in a record.
• YEAR: Stores a year value in either 2-digit or 4-digit format.

Examples:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    OrderTime TIME,
    OrderTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Difference between Char and Varchar2

Aspect	CHAR	VARCHAR2
Storage Type	Fixed-length	Variable-length
Padding	Pads with spaces to match specified length	No padding
Length Specification	Always uses defined length	Uses only the necessary length up to the max specified length
Usage Scenario	Best for fixed-length data (e.g., codes)	Best for variable-length data (e.g., names, addresses)
Storage Efficiency	Can be inefficient for variable-length data due to padding	More efficient for variable-length data
Performance	Can be faster for fixed-length data due to consistent size	Slightly slower for variable-length data due to length calculation
Example Declaration	CHAR(10)	VARCHAR2(50)
Example Storage	'Bhanu ' (for CHAR(10))	'Bhanu' (for VARCHAR2(50)

Comments in SQL

n SQL, comments are used to annotate or explain sections of SQL code, making it easier to understand and maintain. SQL supports both single-line and multi-line comments. Here’s how you can use comments in SQL:

1. Single-Line Comments

Single-line comments are used to add a comment on a single line. They begin with -- (double hyphen).

Syntax:

-- This is a single-line comment
SELECT * FROM Employees;

Example:

-- Retrieve all employees
SELECT * FROM Employees;

2. Multi-Line Comments

Multi-line comments can span multiple lines and are enclosed between /* and */.

Syntax:

/* This is a multi-line comment
   It can span multiple lines */
SELECT * FROM Employees;

Example:

/*
   The following query retrieves all
   employee records from the Employees table
*/
SELECT * FROM Employees;

SQL Operators

SQL operators are symbols or keywords used to perform operations on data in SQL queries. They are used in expressions to manipulate data, compare values, and control query results. Here’s a detailed overview of the most commonly used SQL operators:

1. Arithmetic Operators

Used for mathematical operations.

Operator	Description	Example
+	Addition	SELECT 5 + 3;
-	Subtraction	SELECT 10 - 2;
*	Multiplication	SELECT 4 * 5;
/	Division	SELECT 20 / 4;
%	Modulus (remainder)	SELECT 10 % 3;

Example:

-- Example using all arithmetic operators
SELECT
10 + 5 AS AdditionResult, -- Addition
15 - 5 AS SubtractionResult, -- Subtraction
5 * 3 AS MultiplicationResult, -- Multiplication
20 / 4 AS DivisionResult, -- Division
10 % 3 AS ModulusResult -- Modulus
;

2. Comparison Operators

Used to compare values and return boolean results.

Operator	Description	Example
=	Equal to	SELECT * FROM Employees WHERE Age = 30;
!= or <>	Not equal to	SELECT * FROM Employees WHERE Age != 30;
>	Greater than	SELECT * FROM Products WHERE Price > 100;
<	Less than	SELECT * FROM Products WHERE Price < 100;
>=	Greater than or equal to	SELECT * FROM Products WHERE Price >= 100;
<=	Less than or equal to	SELECT * FROM Products WHERE Price <= 100;

Example:

-- Example using all comparison operators
SELECT
*
FROM
Employees
WHERE
Age = 30 -- Equal to
AND Salary != 50000 -- Not equal to
AND Age > 25 -- Greater than
AND Salary < 100000 -- Less than AND Age >= 18 -- Greater than or equal to
AND Salary <= 75000; -- Less than or equal to

3. Logical Operators

Used to combine multiple conditions in a WHERE clause.

Operator	Description	Example
AND	Both conditions must be true	SELECT * FROM Employees WHERE Age > 30 AND Salary > 50000;
OR	At least one condition must be true	SELECT * FROM Employees WHERE Age > 30 OR Salary > 50000;
NOT	Negates a condition	SELECT * FROM Employees WHERE NOT (Age > 30);

Example:

-- Example using all logical operators
SELECT 
    * 
FROM 
    Employees
WHERE 
    Age > 30 AND Salary > 50000  -- AND
    OR Department = 'Sales'      -- OR
    NOT (Position = 'Intern');   -- NOT

4. String Operators

Used for manipulating and comparing text strings.

Operator	Description	Example
`		or+`
LIKE	Pattern matching	SELECT * FROM Employees WHERE LastName LIKE 'S%';
ILIKE	Case-insensitive pattern matching (PostgreSQL)	SELECT * FROM Employees WHERE LastName ILIKE 's%';
NOT LIKE	Opposite of LIKE	SELECT * FROM Employees WHERE LastName NOT LIKE 'S%';

example:

-- Example using string operators
SELECT 
    FirstName || ' ' || LastName AS FullName,  -- Concatenation
    FirstName LIKE 'B%' AS StartsWithB,         -- LIKE
    LastName NOT LIKE 'S%' AS NotStartsWithS    -- NOT LIKE
FROM 
    Employees;

5. NULL Operators

Used for checking NULL values in SQL.

Operator	Description	Example
IS NULL	Checks if a value is NULL	SELECT * FROM Employees WHERE MiddleName IS NULL;
IS NOT NULL	Checks if a value is not NULL	SELECT * FROM Employees WHERE MiddleName IS NOT NULL;

Example:

-- Example using NULL operators
SELECT 
    * 
FROM 
    Employees
WHERE 
    MiddleName IS NULL         -- IS NULL
    OR MiddleName IS NOT NULL; -- IS NOT NULL

6. Set Operators

Used to combine the results of two or more queries.

Operator	Description	Example
UNION	Combines results from two queries, removing duplicates	SELECT Name FROM Employees UNION SELECT Name FROM Managers;
UNION ALL	Combines results from two queries, including duplicates	SELECT Name FROM Employees UNION ALL SELECT Name FROM Managers;
INTERSECT	Returns only rows that are in both queries	SELECT Name FROM Employees INTERSECT SELECT Name FROM Managers;
EXCEPT or MINUS	Returns rows from the first query that are not in the second query	SELECT Name FROM Employees EXCEPT SELECT Name FROM Managers;

Example:

-- Example using set operators
-- Assume we have two tables: Employees and Managers

-- UNION
SELECT Name FROM Employees
UNION
SELECT Name FROM Managers;

-- UNION ALL
SELECT Name FROM Employees
UNION ALL
SELECT Name FROM Managers;

-- INTERSECT
SELECT Name FROM Employees
INTERSECT
SELECT Name FROM Managers;

-- EXCEPT (or MINUS)
SELECT Name FROM Employees
EXCEPT
SELECT Name FROM Managers;

7. Conditional Operators

Used in SQL queries to perform conditional logic.

Operator	Description	Example
CASE	Provides conditional logic within queries	SELECT Name, CASE WHEN Salary > 50000 THEN 'High' ELSE 'Low' END AS SalaryLevel FROM Employees;

Example:

-- Example using CASE
SELECT 
    Name,
    CASE 
        WHEN Salary > 50000 THEN 'High'
        WHEN Salary BETWEEN 30000 AND 50000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryLevel
FROM 
    Employees;

Summary

SQL operators allow you to perform a wide range of operations on your data, including mathematical calculations, data comparisons, logical evaluations, and string manipulations. Using these operators effectively helps you write powerful and flexible SQL queries.

Types of Set Operation:

1. UNION

Purpose: Combines the results of two or more SELECT queries into a single result set and removes duplicate rows.

Requirements:

• The number of columns in each SELECT query must be the same.
• The data types of the columns must be compatible (i.e., the same or convertible).

Example:

-- Combining names from both Employees and Contractors
SELECT Name FROM Employees
UNION
SELECT Name FROM Contractors;

Explanation:

• SELECT Name FROM Employees: Retrieves the Name column from the Employees table.
• UNION: Combines the result with the subsequent SELECT query.
• SELECT Name FROM Contractors: Retrieves the Name column from the Contractors table.
• Result: The final result set includes all unique names from both tables. Any duplicate names across the tables are removed.

Detailed Result: If Employees has names Alice, Bob, Carol and Contractors has names Bob, David, the result would be:

Name
Alice
Bob
Carol
David

2. UNION ALL

Purpose: Combines the results of two or more SELECT queries into a single result set, including all duplicate rows.

Requirements:

• The number of columns in each SELECT query must be the same.
• The data types of the columns must be compatible.

Example:

-- Combining names from both Employees and Contractors, including duplicates
SELECT Name FROM Employees
UNION ALL
SELECT Name FROM Contractors;

Explanation:

• SELECT Name FROM Employees: Retrieves the Name column from the Employees table.
• UNION ALL: Combines the result with the subsequent SELECT query and includes duplicates.
• SELECT Name FROM Contractors: Retrieves the Name column from the Contractors table.
• Result: The final result set includes all names from both tables, including duplicates.

Detailed Result: If Employees has names Alice, Bob, Carol and Contractors has names Bob, David, the result would be:

Name
Alice
Bob
Carol
Bob
David

3. INTERSECT

Purpose: Returns only the rows that are present in both result sets.

Requirements:

• The number of columns in each SELECT query must be the same.
• The data types of the columns must be compatible.

Example:

-- Finding common names in both Employees and Contractors
SELECT Name FROM Employees
INTERSECT
SELECT Name FROM Contractors;

Explanation:

• SELECT Name FROM Employees: Retrieves the Name column from the Employees table.
• INTERSECT: Returns only the names that are present in both Employees and Contractors.
• SELECT Name FROM Contractors: Retrieves the Name column from the Contractors table.
• Result: The final result set includes only those names that exist in both tables.

Detailed Result: If Employees has names Alice, Bob, Carol and Contractors has names Bob, David, the result would be:

Name
Bob

4. EXCEPT (or MINUS in some SQL dialects)

Purpose: Returns rows from the first result set that are not present in the second result set.

Requirements:

• The number of columns in each SELECT query must be the same.
• The data types of the columns must be compatible.

Example:

-- Finding names that are in Employees but not in Contractors
SELECT Name FROM Employees
EXCEPT
SELECT Name FROM Contractors;

Explanation:

• SELECT Name FROM Employees: Retrieves the Name column from the Employees table.
• EXCEPT: Returns rows from the first SELECT that do not appear in the second SELECT.
• SELECT Name FROM Contractors: Retrieves the Name column from the Contractors table.
• Result: The final result set includes names that are present in the Employees table but not in the Contractors table.

Detailed Result: If Employees has names Alice, Bob, Carol and Contractors has names Bob, David, the result would be:

Name
Alice
Carol

Additional Details

Column Requirements:

• For all set operations (UNION, UNION ALL, INTERSECT, EXCEPT), the number of columns and their data types must be the same in each SELECT statement.

Ordering Results:

• You can apply ORDER BY only to the final result of a set operation.

Example with ORDER BY:

-- Combining names from Employees and Contractors, sorting the final result
SELECT Name FROM Employees
UNION
SELECT Name FROM Contractors
ORDER BY Name;

Explanation:

• ORDER BY Name: Sorts the final combined result set by the Name column.

Detailed Result: If Employees has names Alice, Bob and Contractors has names Bob, David, after combining and removing duplicates, the result would be:

Name
Alice
Bob
David

Summary

• UNION: Combines results and removes duplicates.
• UNION ALL: Combines results and includes duplicates.
• INTERSECT: Returns common rows from both result sets.
• EXCEPT (or MINUS): Returns rows from the first result set not present in the second.
• Column Matching: Number and types of columns must match.
• ORDER BY: Can be used to sort the final result set.

These set operations are powerful tools for combining and comparing data from multiple queries.

Data Definition Language (DDL) Commands

Data Definition Language (DDL) commands in SQL are used to define, modify, and manage the structure of database objects such as tables, indexes, and schemas. DDL commands are crucial for creating and maintaining the database schema. Here are the primary DDL commands with detailed explanations and examples:

1. CREATE

The CREATE command is used to define new database objects such as tables, indexes, and views.

Create Table

Purpose: To create a new table within the database.

Example:

-- Creating a table named 'Employees'
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,         -- Unique identifier for each employee
    FirstName VARCHAR(50) NOT NULL,    -- Employee's first name, cannot be NULL
    LastName VARCHAR(50),              -- Employee's last name
    HireDate DATE                      -- Date when the employee was hired
);

Explanation:

• EmployeeID INT PRIMARY KEY: Defines EmployeeID as an integer that uniquely identifies each employee.
• FirstName VARCHAR(50) NOT NULL: Defines FirstName as a variable character field with a maximum length of 50 characters and ensures it cannot be NULL.
• LastName VARCHAR(50): Defines LastName as a variable character field with a maximum length of 50 characters.
• HireDate DATE: Defines HireDate as a date field.

Create Index

Purpose: To improve the performance of queries by creating an index on a table column.

Example:

-- Creating an index on the 'LastName' column of the 'Employees' table
CREATE INDEX idx_lastname ON Employees (LastName);

Explanation:

• CREATE INDEX idx_lastname: Creates an index named idx_lastname.
• ON Employees (LastName): Specifies that the index is created on the LastName column of the Employees table.

Create View

Purpose: To create a virtual table (view) that can simplify complex queries or provide a simplified data interface.

Example:

-- Creating a view that shows employees with salaries above 50000
CREATE VIEW HighSalaryEmployees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Explanation:

• CREATE VIEW HighSalaryEmployees AS: Defines a view named HighSalaryEmployees.
• SELECT EmployeeID, FirstName, LastName, Salary: Specifies the columns to include in the view.
• FROM Employees: Indicates the source table.
• WHERE Salary > 50000: Filters the rows to include only those with a salary greater than 50,000.

2. ALTER

The ALTER command is used to modify the structure of an existing database object.

Add Column

Purpose: To add a new column to an existing table.

Example:

-- Adding a new column 'Email' to the 'Employees' table
ALTER TABLE Employees
ADD Email VARCHAR(100);

Explanation:

• ALTER TABLE Employees: Specifies the table to modify.
• ADD Email VARCHAR(100): Adds a new column Email with a maximum length of 100 characters.

Modify Column

Purpose: To change the definition of an existing column in a table.

Example:

-- Modifying the 'LastName' column to increase its size
ALTER TABLE Employees
MODIFY LastName VARCHAR(100);

Explanation:

• MODIFY LastName VARCHAR(100): Changes the LastName column to have a maximum length of 100 characters.

Drop Column

Purpose: To remove an existing column from a table.

Example:

-- Dropping the 'Email' column from the 'Employees' table
ALTER TABLE Employees
DROP COLUMN Email;

Explanation:

• DROP COLUMN Email: Removes the Email column from the Employees table.

Add Constraint

Purpose: To add a new constraint (e.g., foreign key, unique key) to an existing table.

Example:

-- Adding a foreign key constraint to the 'Employees' table
ALTER TABLE Employees
ADD CONSTRAINT fk_department
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

Explanation:

• ADD CONSTRAINT fk_department: Adds a new constraint named fk_department.
• FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID): Specifies that DepartmentID in the Employees table references DepartmentID in the Departments table.

Drop Constraint

Purpose: To remove an existing constraint from a table.

Example:

-- Dropping a foreign key constraint from the 'Employees' table
ALTER TABLE Employees
DROP CONSTRAINT fk_department;

Explanation:

• DROP CONSTRAINT fk_department: Removes the fk_department constraint from the Employees table.

3. DROP

The DROP command is used to delete existing database objects permanently.

Drop Table

Purpose: To remove an entire table and all its data.

Example:

-- Dropping the 'Employees' table
DROP TABLE Employees;

Explanation:

• DROP TABLE Employees: Permanently deletes the Employees table and all its data.

Drop Index

Purpose: To remove an index from a table.

Example:

- Dropping the index 'idx_lastname'
DROP INDEX idx_lastname;

Explanation:

• DROP INDEX idx_lastname: Deletes the idx_lastname index.

Drop View

Purpose: To remove a view from the database.

Example:

-- Dropping the view 'HighSalaryEmployees'
DROP VIEW HighSalaryEmployees;

Explanation:

• DROP VIEW HighSalaryEmployees: Deletes the HighSalaryEmployees view.

4. TRUNCATE

The TRUNCATE command is used to delete all rows from a table, but the table structure remains intact. It is faster than DELETE and is often used when you want to clear out a table quickly.

Example:

-- Removing all rows from the 'Employees' table
TRUNCATE TABLE Employees;

Explanation:

• TRUNCATE TABLE Employees: Removes all rows from the Employees table but keeps the table structure and schema.

5. RENAME

The RENAME command is used to change the name of an existing database object.

Example:

-- Renaming the 'Employees' table to 'Staff'
RENAME TABLE Employees TO Staff;

Explanation:

• RENAME TABLE Employees TO Staff: Changes the name of the Employees table to Staff.

Summary

• CREATE: Used to define new tables, indexes, views, and other database objects.
• ALTER: Used to modify the structure of existing tables and other objects (e.g., adding, modifying, or dropping columns and constraints).
• DROP: Used to permanently delete tables, indexes, views, and other objects.
• TRUNCATE: Used to remove all rows from a table but retain its structure.
• RENAME: Used to change the name of a database object.

These DDL commands are fundamental for creating and managing the schema of a database, allowing you to define and adjust the structure according to your needs.

Data Manipulation Commands (DML) Commands

Data Manipulation Language (DML) commands are used to manage and manipulate data within existing database objects. DML commands allow you to perform operations such as querying, inserting, updating, and deleting data in a database. Here’s a detailed explanation of the primary DML commands:

1. SELECT

The SELECT command is used to retrieve data from one or more tables. It allows you to specify which columns you want to retrieve and filter the results based on conditions.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT number;

Examples:

Select All Columns:

-- Retrieve all columns from the 'Employees' table
SELECT * FROM Employees;

Select Specific Columns:

-- Retrieve only 'FirstName' and 'LastName' columns
SELECT FirstName, LastName FROM Employees;

With Conditions:

-- Retrieve employees with a salary greater than 50000
SELECT * FROM Employees
WHERE Salary > 50000;

With Sorting:

-- Retrieve employees sorted by 'LastName' in ascending order
SELECT * FROM Employees
ORDER BY LastName ASC;

With Limiting Results:

-- Retrieve the first 10 employees
SELECT * FROM Employees
LIMIT 10;

2. INSERT

The INSERT command is used to add new rows to a table. You can specify the columns and values for the new rows.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Examples:

Insert Single Row:

-- Add a new employee to the 'Employees' table
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, Salary)
VALUES (1, 'John', 'Doe', '2024-08-01', 60000);

Insert Multiple Rows:

-- Add multiple employees to the 'Employees' table
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, Salary)
VALUES 
    (2, 'akhil', 'yaswnath', '2024-08-02', 65000),
    (3, 'bhanu', 'prakash', '2024-08-03', 70000);

3. UPDATE

The UPDATE command is used to modify existing rows in a table. You can update one or more columns and specify conditions to determine which rows should be updated.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Examples:

Update Single Row:

-- Update the salary of employee with EmployeeID 1
UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 1;

Update Multiple Rows:

-- Increase the salary of all employees in the 'Sales' department by 10%
UPDATE Employees
SET Salary = Salary * 1.10
WHERE Department = 'Sales';

4. DELETE

The DELETE command is used to remove one or more rows from a table based on a specified condition.

Syntax:

DELETE FROM table_name
WHERE condition;

Examples:

Delete Single Row:

-- Remove employee with EmployeeID 1
DELETE FROM Employees
WHERE EmployeeID = 1;

Delete Multiple Rows:

-- Remove all employees from the 'Intern' position
DELETE FROM Employees
WHERE Position = 'Intern';

Delete All Rows:

-- Remove all rows from the 'Employees' table
DELETE FROM Employees;

5. MERGE (also known as UPSERT)

The MERGE command is used to insert, update, or delete rows in a target table based on matching conditions with a source table. It’s useful for synchronizing tables.

Syntax:

MERGE INTO target_table AS target
USING source_table AS source
ON (target.key = source.key)
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (source.column1, source.column2, ...);

Example:

-- Merge data from 'NewEmployees' into 'Employees'
MERGE INTO Employees AS e
USING NewEmployees AS ne
ON (e.EmployeeID = ne.EmployeeID)
WHEN MATCHED THEN
    UPDATE SET e.Salary = ne.Salary
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, FirstName, LastName, HireDate, Salary)
    VALUES (ne.EmployeeID, ne.FirstName, ne.LastName, ne.HireDate, ne.Salary);

Summary

• SELECT: Retrieves data from one or more tables. Can filter, sort, and limit results.
• INSERT: Adds new rows to a table.
• UPDATE: Modifies existing rows in a table.
• DELETE: Removes rows from a table.
• MERGE: Performs insert, update, or delete operations based on conditions, useful for synchronizing tables.

These DML commands are essential for managing and manipulating data within a relational database.

Order By Keyword

The ORDER BY keyword in SQL is used to sort the result set of a query based on one or more columns. It allows you to organize the data in ascending or descending order, making it easier to analyze and present the results.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• column1, column2, …: The columns by which you want to sort the results.
• ASC: Sorts the results in ascending order (default).
• DESC: Sorts the results in descending order.

Examples

1. Sort by a Single Column

Ascending Order (Default)

-- Retrieve employees sorted by last name in ascending order
SELECT * FROM Employees
ORDER BY LastName;

Descending Order

-- Retrieve employees sorted by last name in descending order
SELECT * FROM Employees
ORDER BY LastName DESC;

2. Sort by Multiple Columns

Primary and Secondary Sorting

-- Retrieve employees sorted by department first (ascending) and then by salary within each department (descending)
SELECT * FROM Employees
ORDER BY Department ASC, Salary DESC;

3. Sorting with Expressions

Sorting by Calculated Column

-- Retrieve employees sorted by their annual salary (assuming 'Salary' is monthly)
SELECT EmployeeID, FirstName, LastName, Salary, Salary * 12 AS AnnualSalary
FROM Employees
ORDER BY AnnualSalary DESC;

4. Sorting with NULL Values

Sorting with NULLS LAST or NULLS FIRST

-- Retrieve employees sorted by salary, with NULL salaries appearing last
SELECT * FROM Employees
ORDER BY Salary DESC NULLS LAST;

-- Retrieve employees sorted by salary, with NULL salaries appearing first
SELECT * FROM Employees
ORDER BY Salary ASC NULLS FIRST;

Group By

The GROUP BY keyword in SQL is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions to perform calculations on each group of rows.

Syntax

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

• column1, column2, ...: The columns by which you want to group the results.
• aggregate_function(column3): Aggregate functions like COUNT(), SUM(), AVG(), MAX(), MIN(), etc., applied to the grouped data.

1. Count Employees by Department

Objective: Find out how many employees work in each department.

SQL Query:

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

Explanation:

• SELECT Department, COUNT(*): This selects the department and counts the number of employees in each department.
• FROM Employees: Specifies the table from which to retrieve the data.
• GROUP BY Department: Groups the rows by the Department column so that COUNT(*) is calculated for each department.

Result:

Department	EmployeeCount
Sales	10
HR	5
IT	7

2. Sum Salaries by Department

Objective: Calculate the total salary expense for each department.

SQL Query:

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

Explanation:

• SELECT Department, SUM(Salary): This selects the department and calculates the total salary for each department.
• SUM(Salary): Aggregates the Salary column to find the total sum for each group.
• GROUP BY Department: Groups the rows by the Department column.

Result:

Department	TotalSalary
Sales	600000
HR	250000
IT	400000

3. Average Salary by Department

Objective: Find the average salary within each department.

SQL Query:

SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

Explanation:

• SELECT Department, AVG(Salary): This selects the department and calculates the average salary.
• AVG(Salary): Computes the average of the Salary column for each group.
• GROUP BY Department: Groups the rows by the Department column.

Result:

Department	AverageSalary
Sales	60000
HR	50000
IT	57143

4. Maximum Salary by Department

Objective: Determine the highest salary in each department.

SQL Query:

SELECT Department, MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department;

Explanation:

• SELECT Department, MAX(Salary): This selects the department and finds the highest salary in each.
• MAX(Salary): Gets the maximum value of the Salary column for each group.
• GROUP BY Department: Groups the rows by the Department column.

Result:

Department	HighestSalary
Sales	90000
HR	60000
IT	75000

5. Filter Groups with Having Clause

Objective: Retrieve departments where the total salary expense is greater than 100000.

SQL Query:

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 100000;

Explanation:

• SELECT Department, SUM(Salary): Selects the department and total salary.
• HAVING SUM(Salary) > 100000: Filters the results to include only those departments where the total salary is greater than 100000.
• GROUP BY Department: Groups the rows by the Department column.

Result:

Department	TotalSalary
Sales	600000
IT	400000

6. Group by Multiple Columns

Objective: Find out the number of employees in each job title within each department.

SQL Query:

SELECT Department, JobTitle, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department, JobTitle;

Explanation:

• SELECT Department, JobTitle, COUNT(*): This selects the department and job title, and counts the number of employees.
• GROUP BY Department, JobTitle: Groups the rows by both Department and JobTitle.

Result:

Department	JobTitle	EmployeeCount
Sales	Manager	2
Sales	Sales Rep	8
IT	Developer	5
HR	Recruiter	3

7. Sorting Grouped Results

Objective: Retrieve departments and their total salary expenses, ordered by total salary in descending order.

SQL Query:

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
ORDER BY TotalSalary DESC;

Explanation:

• SELECT Department, SUM(Salary): Selects the department and total salary.
• ORDER BY TotalSalary DESC: Orders the result by TotalSalary in descending order.
• GROUP BY Department: Groups the rows by the Department column.

Result:

Department	TotalSalary
Sales	600000
IT	400000
HR	250000

Summary

• GROUP BY: Used to group rows sharing a property so that aggregate functions can be applied.
• Aggregate Functions: Provide summarized information, such as counts, sums, averages, etc.
• HAVING: Filters groups after aggregation.
• Multiple Columns: Grouping by more than one column can provide more detailed breakdowns.

These examples illustrate how the GROUP BY clause is used to aggregate and summarize data, allowing for more insightful data analysis.

Having Clauses

The HAVING clause in SQL is used to filter the results of a GROUP BY query based on aggregate functions. It is similar to the WHERE clause, but HAVING is applied after the data has been grouped and aggregated.
Purpose: To filter groups of rows created by the GROUP BY clause based on conditions involving aggregate functions (e.g., SUM, COUNT, AVG).

Requirements:

• It is used with the GROUP BY clause.
• It is typically used with aggregate functions.

Syntax:

SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1
HAVING aggregate_function(column2) condition;

Detailed Examples

1. Basic Example

Scenario: You have a table named Sales with sales data, and you want to find salespersons whose total sales amount exceeds 1000.

Table: Sales

Salesperson	SaleAmount
Bhanu	500
Bhanu2	600
Bhanu	600
Bhanu2	300

SQL Query:

-- Finding salespersons with total sales amount greater than 1000
SELECT Salesperson, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Salesperson
HAVING SUM(SaleAmount) > 1000;

Explanation:

• GROUP BY Salesperson: Groups the data by each salesperson. This means that we aggregate data for each unique salesperson.
• SUM(SaleAmount) AS TotalSales: Calculates the total sales amount for each salesperson.
• HAVING SUM(SaleAmount) > 1000: Filters the groups to include only those where the total sales amount is greater than 1000.

Detailed Result:

• For Bhanu, total sales = 500 + 600 = 1100.
• For Bhanu2, total sales = 600 + 300 = 900.

The query will return:

Salesperson	TotalSales
Bhanu	1100

Only Bhanu has total sales greater than 1000, so only Bhanu is included in the result.

2. Using Multiple Aggregates

Scenario: You have a table Employees with salary data and want to find departments where the average salary is more than 50000 and there are more than 10 employees.

Table: Employees

Department	Salary
HR	55000
Sales	45000
HR	60000
Sales	47000
HR	58000
Sales	48000
HR	62000
HR	59000
HR	60000
HR	61000
HR	63000

SQL Query:

-- Finding departments with average salary > 50000 and employee count > 10
SELECT Department, AVG(Salary) AS AvgSalary, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000 AND COUNT(*) > 10;

Explanation:

• GROUP BY Department: Groups employees by department.
• AVG(Salary) AS AvgSalary: Computes the average salary for each department.
• COUNT(*) AS EmployeeCount: Counts the number of employees in each department.
• HAVING AVG(Salary) > 50000 AND COUNT(*) > 10: Filters departments to include only those with an average salary greater than 50000 and more than 10 employees.

Detailed Result:

• For HR, the average salary is 59000, and the total count is 11 (greater than 10).
• For Sales, the average salary is 47000, and the total count is less than 10.

The query will return:

Department	AvgSalary	EmployeeCount
HR	59000	11

Only the HR department meets both criteria, so it is included in the result.

Comparison with WHERE Clause

Scenario: Compare filtering with WHERE versus HAVING.

Incorrect Use of WHERE:

-- Incorrect use of WHERE for aggregate filtering
SELECT Salesperson, SUM(SaleAmount) AS TotalSales
FROM Sales
WHERE SUM(SaleAmount) > 1000  -- This will produce an error
GROUP BY Salesperson;

Explanation:

• The WHERE clause cannot be used with aggregate functions like SUM because it filters rows before grouping.

Correct Use of HAVING:

-- Correct use of HAVING for aggregate filtering
SELECT Salesperson, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY Salesperson
HAVING SUM(SaleAmount) > 1000;

Explanation:

• The HAVING clause is used to filter the results after the aggregation has been applied.

Summary

• HAVING Clause: Used to filter groups of rows based on aggregate function results after grouping.
• WHERE Clause: Used to filter rows before any grouping or aggregation takes place.

The HAVING clause is essential for refining grouped data according to aggregate conditions, enabling you to focus on groups that satisfy specific criteria, such as those involving Bhanu or other names in your dataset.

Functions Single Row Functions (Numeric Functions Character Functions , data functions , conversion functions )

SQL functions

1.Numeric functions

Numeric functions in SQL are used to perform mathematical operations and calculations on numeric data. These functions can be applied to columns in your tables to perform tasks such as rounding, finding minimum and maximum values, and performing mathematical operations. Here’s a detailed explanation of common numeric functions in SQL, along with examples.

Numeric Functions in SQL

1. ABC()

Purpose: Returns the absolute value of a number.

Syntax:

ABC(number);

Example:

-- Finding the absolute value of -150
SELECT ABC(-150) AS AbsoluteValue;

Result:

AbsoluteValue
150

Explanation: The function ABS(-150) returns 150 because it removes the negative sign.

2. CEILING()

Purpose: Returns the smallest integer greater than or equal to a number.

Syntax:

CEILING(number);

Example:

-- Finding the ceiling value of 4.3
SELECT CEILING(4.3) AS CeilingValue;

Result:

CeilingValue
5

Explanation: The function CEILING(4.3) returns 5 because it rounds up to the nearest whole number.

3. FLOOR()

Purpose: Returns the largest integer less than or equal to a number.

Syntax:

FLOOR(number);

Example:

-- Finding the floor value of 4.7
SELECT FLOOR(4.7) AS FloorValue;

Result:

FloorValue
4

Explanation: The function FLOOR(4.7) returns 4 because it rounds down to the nearest whole number.

4. ROUND()

Purpose: Rounds a number to a specified number of decimal places.

Syntax:

ROUND(number, decimal_places);

Example:

-- Rounding 123.4567 to 2 decimal places
SELECT ROUND(123.4567, 2) AS RoundedValue;

Result:

RoundedValue
123.46

Explanation: The function ROUND(123.4567, 2) returns 123.46, rounding the number to two decimal places.

5. POWER()

Purpose: Returns the result of raising a number to a specified power.

Syntax:

POWER(base, exponent);

Example:

-- Calculating 2 raised to the power of 3
SELECT POWER(2, 3) AS PowerResult;

Result:

PowerResult
8

Explanation: The function POWER(2, 3) returns 8, which is 2 raised to the power of 3.

6. SQRT()

Purpose: Returns the square root of a number.

Syntax:

SQRT(number);

Example:

-- Finding the square root of 16
SELECT SQRT(16) AS SquareRoot;

Result:

SquareRoot
4

Explanation: The function SQRT(16) returns 4, the square root of 16.

7. RAND()

Purpose: Returns a random floating-point number between 0 and 1.

Syntax:

RAND();

Example:

-- Generating a random number
SELECT RAND() AS RandomNumber;

Result:

RandomNumber
0.7432

Explanation: The function RAND() generates a random number between 0 and 1. The actual value will vary each time you run the query.

8. MOD()

Purpose: Returns the remainder of a division operation.

Syntax:

MOD(dividend, divisor);

Example:

-- Finding the remainder of 10 divided by 3
SELECT MOD(10, 3) AS Remainder;

Result:

Remainder
1

Explanation: The function MOD(10, 3) returns 1, which is the remainder when 10 is divided by 3.

Usage Scenarios

• Financial Calculations: Numeric functions can be used to perform rounding and precise calculations for financial data.
• Statistical Analysis: Functions like ROUND, CEILING, and FLOOR are useful in statistical analyses and reporting.
• Data Validation: Functions like ABS and MOD help in validating and cleaning data.

Summary

• ABC(): Returns the absolute value of a number.
• CEILING(): Returns the smallest integer greater than or equal to a number.
• FLOOR(): Returns the largest integer less than or equal to a number.
• ROUND(): Rounds a number to a specified number of decimal places.
• POWER(): Raises a number to a specified power.
• SQRT(): Returns the square root of a number.
• RAND(): Generates a random number between 0 and 1.
• MOD(): Returns the remainder of a division operation.

2.Aggregate Functions

An aggregate function is a function that performs a calculation on a set of values, and returns a single value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group.

Aggregate Functions in SQL

1. COUNT()

Purpose: Counts the number of rows or non-NULL values in a column.

Syntax:

COUNT(column_name);
COUNT(*);

Examples:

Count Rows:

-- Counting the number of employees in the Employees table
SELECT COUNT(*) AS NumberOfEmployees
FROM Employees;

Explanation: The function COUNT(*) returns the total number of rows in the Employees table

Result:

NumberOfEmployees
50

Count Non-NULL Values:

-- Counting the number of non-NULL values in the Salary column
SELECT COUNT(Salary) AS NumberOfSalaries
FROM Employees;

Explanation: The function COUNT(Salary) returns the number of non-NULL values in the Salary column.

Result:

NumberOfSalaries
48

2. SUM()

Purpose: Calculates the total sum of a numeric column.

Syntax:

SUM(column_name);

Example:

-- Calculating the total salary of all employees
SELECT SUM(Salary) AS TotalSalary
FROM Employees;

Explanation: The function SUM(Salary) returns the total sum of all values in the Salary column.

Result:

TotalSalary
2500000

3. AVG()

Purpose: Calculates the average value of a numeric column.

Syntax:

AVG(column_name);

Example:

-- Calculating the average salary of employees
SELECT AVG(Salary) AS AverageSalary
FROM Employees;

Explanation: The function AVG(Salary) returns the average value of the Salary column.

Result:

AverageSalary
50000

4. MIN()

Purpose: Returns the minimum value of a numeric column or date column.

Syntax:

MIN(column_name);

Example:

-- Finding the lowest salary in the Employees table
SELECT MIN(Salary) AS MinimumSalary
FROM Employees;

Explanation: The function MIN(Salary) returns the smallest value in the Salary column.

Result:

MinimumSalary
30000

5. MAX()

Purpose: Returns the maximum value of a numeric column or date column.

Syntax:

MAX(column_name);

Example:

-- Finding the highest salary in the Employees table
SELECT MAX(Salary) AS MaximumSalary
FROM Employees;

Explanation: The function MAX(Salary) returns the largest value in the Salary column.

Result:

MaximumSalary
120000

6. GROUP_CONCAT() (MySQL specific)

Purpose: Concatenates values from multiple rows into a single string, separated by a specified delimiter.

Syntax:

GROUP_CONCAT(column_name SEPARATOR 'separator');

Example:

-- Concatenating names of all employees into a single string
SELECT GROUP_CONCAT(FirstName SEPARATOR ', ') AS EmployeeNames
FROM Employees;

Explanation: The function GROUP_CONCAT(FirstName SEPARATOR ', ') concatenates all employee first names into a single string, separated by commas.

Result:

EmployeeNames
Bhanu, Bhanu2, …

7. VARIANCE() (SQL Server and PostgreSQL)

Purpose: Calculates the variance of a numeric column, measuring the spread of data.

Syntax:

sqlCopy codeVARIANCE(column_name);

Example:

-- Calculating the variance of salaries
SELECT VARIANCE(Salary) AS SalaryVariance
FROM Employees;

Explanation: The function VARIANCE(Salary) returns the variance of the values in the Salary column.

Result:

SalaryVariance
1000000

8. STDDEV() (SQL Server and PostgreSQL)

Purpose: Calculates the standard deviation of a numeric column, measuring the amount of variation or dispersion.

Syntax:

STDDEV(column_name);

Example:

-- Calculating the standard deviation of salaries
SELECT STDDEV(Salary) AS SalaryStdDev
FROM Employees;

Explanation: The function STDDEV(Salary) returns the standard deviation of the values in the Salary column.

Result:

SalaryStdDev
1000

Usage Scenarios

• Data Analysis: Aggregate functions are used to perform statistical and summary analysis on large datasets.
• Reporting: They are commonly used in reports to provide summarized data, such as totals and averages.
• Data Validation: Helps in validating data by checking ranges, totals, and other metrics.

Summary

• COUNT(): Counts rows or non-NULL values.
• SUM(): Calculates the total sum of a numeric column.
• AVG(): Calculates the average value of a numeric column.
• MIN(): Returns the smallest value in a numeric or date column.
• MAX(): Returns the largest value in a numeric or date column.
• GROUP_CONCAT(): Concatenates multiple values into a single string (MySQL specific).
• VARIANCE(): Measures data spread by calculating variance (SQL Server and PostgreSQL).
• STDDEV(): Measures data dispersion by calculating standard deviation (SQL Server and PostgreSQL).

These aggregate functions are essential for summarizing, analyzing, and reporting on data in SQL databases.

1. Date Functions

Date functions are used to manipulate and retrieve information from date and time values.

1.1 GETDATE() / CURRENT_TIMESTAMP

Purpose: Returns the current date and time.

Syntax:

GETDATE();
-- or
CURRENT_TIMESTAMP;

Example:

-- Get the current date and time
SELECT GETDATE() AS CurrentDateTime;

Explanation: This function returns the current date and time based on the server’s system clock.

Result:

CurrentDateTime
2024-08-07 14:30:00

1.2 DATEADD()

Purpose: Adds a specified time interval to a date.

Syntax:

DATEADD(datepart, number, date);

Example:

-- Add 10 days to the current date
SELECT DATEADD(DAY, 10, GETDATE()) AS NewDate;

Explanation: This function adds 10 days to the current date and time.

Result:

NewDate
2024-08-17 14:30:00

1.3 DATEDIFF()

Purpose: Calculates the difference between two dates.

Syntax:

DATEDIFF(datepart, startdate, enddate);

Example:

-- Calculate the number of days between two dates
SELECT DATEDIFF(DAY, '2024-01-01', GETDATE()) AS DaysDifference;

Explanation: This function calculates the number of days between January 1, 2024, and the current date.

Result:

DaysDifference
219

1.4 DATEPART()

Purpose: Returns a specific part of a date, such as year, month, or day.

Syntax:

DATEPART(datepart, date);

Example:

-- Extract the year from the current date
SELECT DATEPART(YEAR, GETDATE()) AS CurrentYear;

Explanation: This function extracts the year part from the current date.

Result:

CurrentYear
2024

1.5 FORMAT() (SQL Server)

Purpose: Formats a date according to a specified format.

Syntax:

FORMAT(date, format);

Example:

-- Format the current date as 'dd/MM/yyyy'
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS FormattedDate;

Explanation: This function formats the current date as day/month/year.

Result:

FormattedDate
07/08/2024

4. String Functions

String functions are used to manipulate and query string data. They perform operations like trimming, concatenating, and searching for substrings.

2.1 CONCAT()

Purpose: Concatenates two or more strings into one.

Syntax:

CONCAT(string1, string2, ...);

Example:

-- Concatenate first name and last name with a space
SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

Explanation: This function concatenates the FirstName and LastName columns, separated by a space.

Result:

FullName
Bhanu Kumar
Ria Sharma

2.2 SUBSTRING()

Purpose: Extracts a portion of a string starting from a specified position.

Syntax:

SUBSTRING(string, start, length);

Example:

-- Extract the first 5 characters from a string
SELECT SUBSTRING('HelloWorld', 1, 5) AS SubstringResult;

Explanation: This function extracts the first 5 characters from the string HelloWorld.

Result:

SubstringResult
Hello

2.3 LEN() / LENGTH()

Purpose: Returns the length of a string.

Syntax:

LEN(string);
-- or
LENGTH(string);

Example:

-- Get the length of the string 'HelloWorld'
SELECT LEN('HelloWorld') AS StringLength;

Explanation: This function returns the number of characters in the string HelloWorld.

Result:

StringLength
10

2.4 UPPER()

Purpose: Converts all characters in a string to uppercase.

Syntax:

UPPER(string);

Example:

-- Convert a string to uppercase
SELECT UPPER('HelloWorld') AS UpperCaseString;

Explanation: This function converts HelloWorld to uppercase.

Result:

UpperCaseString
HELLOWORLD

2.5 LOWER()

Purpose: Converts all characters in a string to lowercase.

Syntax:

LOWER(string);

Example:

-- Convert a string to lowercase
SELECT LOWER('HelloWorld') AS LowerCaseString;

Explanation: This function converts HelloWorld to lowercase.

Result:

LowerCaseString
helloworld

2.6 TRIM() / LTRIM() / RTRIM()

Purpose: Removes leading and/or trailing spaces from a string.

Syntax:

TRIM(string);
-- or
LTRIM(string); -- Removes leading spaces
-- or
RTRIM(string); -- Removes trailing spaces

Example:

-- Remove leading and trailing spaces from a string
SELECT TRIM('   HelloWorld   ') AS TrimmedString;

Explanation: This function removes all leading and trailing spaces from HelloWorld .

Result:

TrimmedString
HelloWorld

Summary

• Date Functions:
  • GETDATE() / CURRENT_TIMESTAMP: Returns the current date and time.
  • DATEADD(): Adds a time interval to a date.
  • DATEDIFF(): Calculates the difference between two dates.
  • DATEPART(): Returns a specific part of a date.
  • FORMAT(): Formats a date according to a specified format.
• String Functions:
  • CONCAT(): Concatenates multiple strings into one.
  • SUBSTRING(): Extracts a portion of a string.
  • LEN() / LENGTH(): Returns the length of a string.
  • UPPER(): Converts a string to uppercase.
  • LOWER(): Converts a string to lowercase.
  • TRIM() / LTRIM() / RTRIM(): Removes leading and/or trailing spaces from a string.

These scalar functions are fundamental for data manipulation and transformation, helping you format, analyze, and clean data effectively in SQL.

Transaction Control Commands

Transaction control commands are crucial for managing transactions in SQL databases. They ensure the integrity and consistency of the database by allowing you to define how transactions should be handled. Here’s a detailed explanation of the primary transaction control commands:

Transaction Control Commands

1. BEGIN TRANSACTION

Purpose: Marks the beginning of a transaction. This command signals the start of a set of operations that should be treated as a single unit.

Syntax:

BEGIN TRANSACTION;

Example:

-- Start a new transaction
BEGIN TRANSACTION;

-- Insert a record into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '2024-08-07');

Explanation: The BEGIN TRANSACTION command starts a new transaction. Any operations performed after this command are part of the transaction.

2. COMMIT

Purpose: Saves all changes made during the current transaction to the database. This command finalizes the transaction, making all changes permanent.

Syntax:

COMMIT;

Example:

-- Start a new transaction
BEGIN TRANSACTION;

-- Insert a record into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Jane', 'Smith', '2024-08-07');

-- Save the changes
COMMIT;

Explanation: The COMMIT command is issued to save all changes made in the transaction. After this command, the inserted record is permanently added to the Employees table.

3. ROLLBACK

Purpose: Undoes all changes made during the current transaction. This command is used to revert the database to its previous state before the transaction began.

Syntax:

sqlCopy codeROLLBACK;

Example:

-- Start a new transaction
BEGIN TRANSACTION;

-- Insert a record into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (3, 'Alice', 'Johnson', '2024-08-07');

-- Undo the changes
ROLLBACK;

Explanation: The ROLLBACK command is issued to discard all changes made in the transaction. The inserted record is not added to the Employees table, and the database remains unchanged from before the transaction started.

4. SAVEPOINT

Purpose: Sets a point within a transaction to which you can later roll back. It allows partial rollback to a specific point without undoing the entire transaction.

Syntax:

SAVEPOINT savepoint_name;

Example:

-- Start a new transaction
BEGIN TRANSACTION;

-- Insert a record into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (4, 'Bob', 'Brown', '2024-08-07');

-- Set a savepoint
SAVEPOINT BeforeAdditionalInsert;

-- Insert another record
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (5, 'Carol', 'Taylor', '2024-08-07');

-- Roll back to the savepoint
ROLLBACK TO SAVEPOINT BeforeAdditionalInsert;

-- Save the changes
COMMIT;

Explanation: The SAVEPOINT command sets a point within the transaction. If an issue arises after the savepoint, you can use ROLLBACK TO SAVEPOINT to revert to that specific point, preserving any changes made before the savepoint.

5. SET TRANSACTION

Purpose: Configures the transaction properties, such as isolation level.

Syntax:

SET TRANSACTION ISOLATION LEVEL isolation_level;

Example:

-- Set the transaction isolation level to READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Start a new transaction
BEGIN TRANSACTION;

-- Perform database operations
-- ...

-- Save the changes
COMMIT;

Explanation: The SET TRANSACTION command is used to define the isolation level of the transaction. The isolation level determines how transaction changes are visible to other transactions and controls concurrency and consistency.

1. ROLLBACK

Purpose: The ROLLBACK command is used to undo changes made during a transaction. It reverts the database to the state it was in before the BEGIN TRANSACTION command was issued.

When to Use: You use ROLLBACK when you encounter an error or need to discard changes made during a transaction.

Syntax:

ROLLBACK;

Example:

-- Start a new transaction
BEGIN TRANSACTION;

-- Insert a record into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '2024-08-07');

-- Insert another record
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Jane', 'Smith', '2024-08-07');

-- Assume something went wrong or an error occurred
-- Rollback the transaction
ROLLBACK;

Explanation: The ROLLBACK command undoes all changes made during the transaction. In this example, both insertions are discarded, and the Employees table remains unchanged from its state before the BEGIN TRANSACTION.

2. GRANT

Purpose: The GRANT command is used to give specific privileges or permissions to users or roles. It controls what actions a user can perform on database objects.

When to Use: Use GRANT to allow users to access or manipulate database objects like tables, views, or procedures.

Syntax:

GRANT privilege_type ON object_name TO user_or_role;

Example:

-- Grant SELECT and INSERT permissions on the Employees table to user 'bhanu'
GRANT SELECT, INSERT ON Employees TO bhanu;

Explanation: In this example, the GRANT command provides the user bhanu with the ability to perform SELECT and INSERT operations on the Employees table.

Common Privileges:

• SELECT: Allows reading data from a table.
• INSERT: Allows inserting new data into a table.
• UPDATE: Allows modifying existing data in a table.
• DELETE: Allows deleting data from a table.
• EXECUTE: Allows executing stored procedures or functions.

3. REVOKE

Purpose: The REVOKE command removes specific privileges or permissions from users or roles. It effectively takes back the access rights granted earlier.

When to Use: Use REVOKE to withdraw access or permissions when a user no longer needs them or if their access level needs to be restricted.

Syntax:

REVOKE privilege_type ON object_name FROM user_or_role;

Example:

-- Revoke SELECT permission on the Employees table from user 'bhanu'
REVOKE SELECT ON Employees FROM bhanu;

Explanation: In this example, the REVOKE command removes the SELECT permission on the Employees table from the user bhanu. The user bhanu will no longer be able to read data from this table.

Summary

• ROLLBACK: Undoes all changes made during the current transaction, reverting the database to its state before the transaction began. Useful for discarding changes due to errors or issues.
• GRANT: Provides specific privileges or permissions to users or roles, allowing them to perform certain actions on database objects.
• REVOKE: Removes specific privileges or permissions from users or roles, restricting their access to database objects.

• BEGIN TRANSACTION: Starts a new transaction. All operations performed after this command are part of the transaction.
• COMMIT: Saves all changes made during the transaction and makes them permanent.
• ROLLBACK: Undoes all changes made during the transaction, reverting the database to its state before the transaction began.
• SAVEPOINT: Sets a specific point within a transaction that you can roll back to, allowing partial undoing of changes.
• SET TRANSACTION: Configures transaction properties such as isolation level to control transaction behavior and concurrency.

These transaction control commands are essential for maintaining data integrity, consistency, and reliability in SQL databases. They help manage and coordinate changes made to the database, ensuring that operations are performed correctly and that data remains accurate.

Sub Queries

Subqueries in SQL

A subquery is a query nested inside another SQL query. It is often used to retrieve or manipulate data based on the results of another query. Subqueries can be included in various SQL clauses, such as WHERE, HAVING, and FROM, and can be used with SELECT, UPDATE, INSERT, and DELETE statements.

Rules for Subqueries

1. Placement: Subqueries can be placed in WHERE, HAVING, or FROM clauses.
2. Execution Order: Subqueries typically execute first if independent. For correlated subqueries, the SQL engine determines the execution order dynamically.
3. Syntax: Subqueries must be enclosed in parentheses.
4. Operators: Use single-row operators (e.g., =, <, >) with single-row subqueries and multiple-row operators (e.g., IN, ANY, ALL) with multiple-row subqueries.

Syntax Example

SELECT column_name
FROM table_name
WHERE column_name [operator]
    (SELECT column_name FROM table_name WHERE ...);

New Sample Tables

EMPLOYEES

EMP_ID	NAME	DEPARTMENT	SALARY
1	Alice	HR	50000
2	Bob	IT	60000
3	Carol	IT	70000
4	David	Finance	75000
5	Eva	HR	52000

PROJECTS

PROJECT_ID	EMP_ID	PROJECT_NAME	BUDGET
101	1	Recruitment	20000
102	2	App Dev	30000
103	3	Network	35000
104	4	Budgeting	40000
105	5	Training	15000

Example Queries

Display Employees with Salaries Greater Than the Average Salary of the IT Department

SELECT NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT = 'IT');

Explanation: The subquery SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT = 'IT' calculates the average salary of employees in the IT department. The outer query then retrieves the names and salaries of employees whose salaries exceed this average.

Output:

NAME	SALARY
David	75000
Eva	52000

Insert New Projects into the PROJECTS Table

New Projects Table:

PROJECT_ID	EMP_ID	PROJECT_NAME	BUDGET
106	1	Hiring	25000
107	2	Security	35000

INSERT INTO PROJECTS (PROJECT_ID, EMP_ID, PROJECT_NAME, BUDGET)
VALUES (106, 1, 'Hiring', 25000),
       (107, 2, 'Security', 35000);

Explanation: This query inserts new project records into the PROJECTS table with specified PROJECT_ID, EMP_ID, PROJECT_NAME, and BUDGET

Delete Projects with a Budget Less Than 20000

DELETE FROM PROJECTS
WHERE BUDGET < 20000;

Explanation: This query deletes records from the PROJECTS table where the BUDGET is less than 20000. There is no subquery needed here, as it performs a straightforward deletion based on the BUDGET condition.

Resulting PROJECTS Table:

PROJECT_ID	EMP_ID	PROJECT_NAME	BUDGET
102	2	App Dev	30000
103	3	Network	35000
104	4	Budgeting	40000
106	1	Hiring	25000
107	2	Security	35000

Update Budget of Projects to 30000 for Projects Managed by Employees with Salary Greater Than 60000

UPDATE PROJECTS
SET BUDGET = 30000
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEES WHERE SALARY > 60000);

Explanation: This query updates the BUDGET to 30000 for projects managed by employees whose salary is greater than 60000. The subquery retrieves EMP_ID of such employees, and the outer query uses these IDs to update the corresponding projects.

Resulting PROJECTS Table:

PROJECT_ID	EMP_ID	PROJECT_NAME	BUDGET
102	2	App Dev	30000
103	3	Network	30000
104	4	Budgeting	40000
106	1	Hiring	30000
107	2	Security	30000

JOINS

SQL Joins are used to combine rows from two or more tables based on a related column between them. Understanding joins is crucial because they allow you to retrieve data that is spread across multiple tables in a relational database. Let’s dive into the different types of joins in SQL, with detailed explanations and examples.

1. INNER JOIN

Definition: An INNER JOIN returns only the rows that have matching values in both tables.

Example Scenario: Consider two tables:

Employees

EmployeeID	Name	DepartmentID
1	Bhanu	101
2	Akhil	102
3	Yaswanth	103
4	Alice	104

• DepartmentsDepartmentIDDepartmentName101HR102IT103Finance

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
Bhanu	HR
Akhil	IT
Yaswanth	Finance

Explanation: The INNER JOIN only returns rows where there is a match in both the Employees and Departments tables. Here, since Alice does not have a matching department in the Departments table, she is not included in the result.

2. LEFT JOIN (or LEFT OUTER JOIN)

Definition: A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

Example Scenario: Using the same tables as above.

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
Bhanu	HR
Akhil	IT
Yaswanth	Finance
Alice	NULL

Explanation: The LEFT JOIN returns all records from the Employees table, along with matching records from the Departments table. Since Alice does not have a corresponding department, NULL is returned for the DepartmentName.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Definition: A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Example Scenario: Let’s modify the Departments table to add a department without any employees.

• DepartmentsDepartmentIDDepartmentName101HR102IT103Finance105Marketing

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
Bhanu	HR
Akhil	IT
Yaswanth	Finance
NULL	Marketing

Explanation: The RIGHT JOIN returns all records from the Departments table, along with matching records from the Employees table. The Marketing department has no employees, so NULL is returned for the Name.

4. FULL JOIN (or FULL OUTER JOIN)

Definition: A FULL JOIN returns all rows when there is a match in either left or right table. If there is no match, NULL values are returned for columns where there is no match.

Example Scenario: Using the modified Departments table and the Employees table from the previous examples.

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
Bhanu	HR
Akhil	IT
Yaswanth	Finance
Alice	NULL
NULL	Marketing

Explanation: The FULL JOIN returns all records from both tables, matching rows where possible. Rows with no match in one of the tables return NULL for that table’s columns.

5. CROSS JOIN

Definition: A CROSS JOIN returns the Cartesian product of the two tables, i.e., it combines each row of the first table with every row of the second table.

Example Scenario: Using simplified tables.

Employees

EmployeeID	Name
1	Bhanu
2	Akhil

Departments

DepartmentID	DepartmentName
101	HR
102	IT

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;

Result:

Name	DepartmentName
Bhanu	HR
Bhanu	IT
Akhil	HR
Akhil	IT

Explanation: The CROSS JOIN results in every possible combination of rows from the Employees and Departments tables. Here, each employee is paired with each department.

6. SELF JOIN

Definition: A SELF JOIN is a regular join, but the table is joined with itself.

Example Scenario: Consider a scenario where employees have managers, and both are listed in the same table.

• EmployeesEmployeeIDNameManagerID1Bhanu22Akhil33YaswanthNULL

Query:

SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees e1
LEFT JOIN Employees e2
ON e1.ManagerID = e2.EmployeeID;

Result:

Employee	Manager
Bhanu	Akhil
Akhil	Yaswanth
Yaswanth	NULL

Explanation: The SELF JOIN pairs each employee with their manager. The LEFT JOIN ensures that even employees without managers (like Yaswanth) are included in the results.