Mastering SQL: A Comprehensive Training Program

-- Connect to SQLite (e.g., using 'sqlite3 your_database.db' in terminal or a GUI tool)

-- 1. Create a new database (if using SQLite, this happens when you connect to a new file)
-- For other RDBMS like PostgreSQL/MySQL:
-- CREATE DATABASE MyFirstDB;
-- USE MyFirstDB;  -- (For SQL Server/MySQL)
-- \c MyFirstDB;  -- (For PostgreSQL)

-- 2. Create a simple table named 'Students'
CREATE TABLE Students (
    StudentID INTEGER PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Age INTEGER,
    Major TEXT
);

-- 3. Insert some sample data into the 'Students' table
INSERT INTO Students (StudentID, FirstName, LastName, Age, Major) VALUES
(1, 'Alice', 'Smith', 20, 'Computer Science'),
(2, 'Bob', 'Johnson', 22, 'Engineering'),
(3, 'Charlie', 'Brown', 21, 'History'),
(4, 'Diana', 'Prince', 20, 'Computer Science');

-- 4. Retrieve all data from the 'Students' table
SELECT * FROM Students;

-- 5. Retrieve only FirstName and Major
SELECT FirstName, Major FROM Students;

-- 6. Retrieve students older than 20
SELECT * FROM Students WHERE Age > 20;

-- Conceptual Example: Designing a simple Order Management System (Normalized to 3NF)

-- Customers Table (1NF, 2NF, 3NF)
CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    CustomerName TEXT NOT NULL,
    Email TEXT UNIQUE,
    Phone TEXT
);

-- Products Table (1NF, 2NF, 3NF)
CREATE TABLE Products (
    ProductID INTEGER PRIMARY KEY,
    ProductName TEXT NOT NULL,
    Category TEXT,
    UnitPrice DECIMAL(10, 2) NOT NULL
);

-- Orders Table (linking Customers and containing order details)
CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER NOT NULL,
    OrderDate DATE NOT NULL,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- OrderDetails Table (linking Products to Orders, handling many-to-many relationship)
CREATE TABLE OrderDetails (
    OrderDetailID INTEGER PRIMARY KEY,
    OrderID INTEGER NOT NULL,
    ProductID INTEGER NOT NULL,
    Quantity INTEGER NOT NULL,
    PriceAtOrder DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Example of Denormalization (for reporting, adding CustomerName directly to Orders)
-- ALTER TABLE Orders ADD COLUMN CustomerName TEXT;
-- UPDATE Orders SET CustomerName = (SELECT CustomerName FROM Customers WHERE Customers.CustomerID = Orders.CustomerID);

-- 1. Create a new table 'Employees'
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- 2. Insert data into 'Employees'
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES
(101, 'John', 'Doe', 'Sales', 60000.00),
(102, 'Jane', 'Smith', 'Marketing', 55000.00),
(103, 'Peter', 'Jones', 'Sales', 70000.00),
(104, 'Alice', 'Williams', 'IT', 75000.00),
(105, 'Bob', 'Brown', 'Marketing', 62000.00);

-- 3. Select all employees
SELECT * FROM Employees;

-- 4. Select employees from 'Sales' department
SELECT * FROM Employees WHERE Department = 'Sales';

-- 5. Select employees with salary greater than 65000
SELECT * FROM Employees WHERE Salary > 65000.00;

-- 6. Update Peter's salary
UPDATE Employees SET Salary = 68000.00 WHERE EmployeeID = 102;
SELECT * FROM Employees WHERE EmployeeID = 102;

-- 7. Delete Peter Jones
DELETE FROM Employees WHERE EmployeeID = 103;
SELECT * FROM Employees; -- Verify deletion

-- 8. Add a new column 'HireDate'
ALTER TABLE Employees ADD COLUMN HireDate DATE;

-- 9. Drop the 'Employees' table (use with caution!)
-- DROP TABLE Employees;

-- For SQL Server/PostgreSQL/MySQL syntax might vary slightly

-- 1. Create a Stored Procedure to add a new employee
-- SQL Server Syntax Example:
CREATE PROCEDURE AddNewEmployee
    @p_EmployeeID INT,
    @p_FirstName VARCHAR(50),
    @p_LastName VARCHAR(50),
    @p_Department VARCHAR(50),
    @p_Salary DECIMAL(10, 2)
AS
BEGIN
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
    VALUES (@p_EmployeeID, @p_FirstName, @p_LastName, @p_Department, @p_Salary);
END;
GO -- SQL Server batch separator

-- PostgreSQL/MySQL Syntax Example:
-- DELIMITER //
-- CREATE PROCEDURE AddNewEmployee(
--     IN p_EmployeeID INT,
--     IN p_FirstName VARCHAR(50),
--     IN p_LastName VARCHAR(50),
--     IN p_Department VARCHAR(50),
--     IN p_Salary DECIMAL(10, 2)
-- )
-- BEGIN
--     INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
--     VALUES (p_EmployeeID, p_FirstName, p_LastName, p_Department, p_Salary);
-- END //
-- DELIMITER ;

-- 2. Execute the Stored Procedure
EXEC AddNewEmployee 106, 'Michael', 'Scott', 'Management', 90000.00; -- SQL Server
-- CALL AddNewEmployee(106, 'Michael', 'Scott', 'Management', 90000.00); -- PostgreSQL/MySQL

SELECT * FROM Employees WHERE EmployeeID = 106;

-- 3. Create a User-Defined Function (Scalar Function) to calculate annual bonus
-- SQL Server Syntax Example:
CREATE FUNCTION CalculateBonus (@p_Salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @p_Salary * 0.10; -- 10% bonus
END;
GO

-- PostgreSQL Syntax Example:
-- CREATE FUNCTION CalculateBonus(p_Salary NUMERIC)
-- RETURNS NUMERIC AS $$
-- BEGIN
--     RETURN p_Salary * 0.10;
-- END;
-- $$ LANGUAGE plpgsql;

-- MySQL Syntax Example:
-- DELIMITER //
-- CREATE FUNCTION CalculateBonus(p_Salary DECIMAL(10, 2))
-- RETURNS DECIMAL(10, 2)
-- DETERMINISTIC
-- BEGIN
--     RETURN p_Salary * 0.10;
-- END //
-- DELIMITER ;

-- 4. Use the Function in a SELECT statement
SELECT EmployeeID, FirstName, LastName, Salary, dbo.CalculateBonus(Salary) AS AnnualBonus FROM Employees; -- SQL Server
-- SELECT EmployeeID, FirstName, LastName, Salary, CalculateBonus(Salary) AS AnnualBonus FROM Employees; -- PostgreSQL

-- Create a 'Departments' table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50) NOT NULL,
    Location VARCHAR(50)
);

-- Insert data into 'Departments'
INSERT INTO Departments (DepartmentID, DepartmentName, Location) VALUES
(1, 'Sales', 'New York'),
(2, 'Marketing', 'London'),
(3, 'IT', 'San Francisco'),
(4, 'HR', 'New York');

-- Update 'Employees' to link to 'Departments'
ALTER TABLE Employees ADD COLUMN DepartmentID INT;
UPDATE Employees SET DepartmentID = 1 WHERE Department = 'Sales';
UPDATE Employees SET DepartmentID = 2 WHERE Department = 'Marketing';
UPDATE Employees SET DepartmentID = 3 WHERE Department = 'IT';
UPDATE Employees SET DepartmentID = 4 WHERE Department = 'HR'; -- Assuming HR exists now
UPDATE Employees SET DepartmentID = NULL WHERE Department IS NULL; -- For Michael Scott if no department yet

-- 1. INNER JOIN: Employees with their departments
SELECT
    E.FirstName,
    E.LastName,
    D.DepartmentName,
    D.Location
FROM
    Employees AS E
INNER JOIN
    Departments AS D ON E.DepartmentID = D.DepartmentID;

-- 2. LEFT JOIN: All employees and their departments (if any)
SELECT
    E.FirstName,
    E.LastName,
    D.DepartmentName
FROM
    Employees AS E
LEFT JOIN
    Departments AS D ON E.DepartmentID = D.DepartmentID;

-- 3. COUNT of employees per department
SELECT
    D.DepartmentName,
    COUNT(E.EmployeeID) AS NumberOfEmployees
FROM
    Departments AS D
LEFT JOIN
    Employees AS D ON D.DepartmentID = E.DepartmentID
GROUP BY
    D.DepartmentName;

-- 4. SUM of salaries per department, only for departments with > 1 employee
SELECT
    D.DepartmentName,
    SUM(E.Salary) AS TotalSalary
FROM
    Employees AS E
INNER JOIN
    Departments AS D ON D.DepartmentID = E.DepartmentID
GROUP BY
    D.DepartmentName
HAVING
    COUNT(E.EmployeeID) > 1;

-- 5. Subquery: Employees whose salary is above the average salary of their department
SELECT
    E.FirstName,
    E.LastName,
    E.Salary,
    E.Department
FROM
    Employees AS E
WHERE
    E.Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = E.Department);

-- 6. Window Function: ROW_NUMBER() to rank employees within each department by salary
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RankInDepartment
FROM
    Employees
ORDER BY
    Department, RankInDepartment;

-- Conceptual Example: Importing data from a CSV file
-- This command varies significantly by RDBMS.

-- SQL Server:
-- BULK INSERT NewTable
-- FROM 'C:\path\to\your\file.csv'
-- WITH (
--     ROWTERMINATOR = '\n',
--     FIRSTROW = 2 -- If your CSV has a header row
-- );

-- PostgreSQL:
-- COPY NewTable FROM 'C:\path\to\your\file.csv' DELIMITER ',' CSV HEADER;

-- MySQL:
-- LOAD DATA INFILE 'C:/path/to/your/file.csv'
-- INTO TABLE NewTable
-- FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-- LINES TERMINATED BY '\n'
-- IGNORE 1 ROWS;

-- Conceptual Example: Exporting data to a CSV file
-- Again, this is often done via SQL client tools or specific RDBMS commands.

-- SQL Server (using sqlcmd or SSMS export wizard):
-- bcp "SELECT * FROM Employees" queryout "C:\path\to\employees.csv" -c -t, -S localhost -U user -P password

-- PostgreSQL (using psql):
-- \copy (SELECT * FROM Employees) TO 'C:\path\to\employees.csv' CSV HEADER;

-- MySQL (using mysql client or SELECT ... INTO OUTFILE):
-- SELECT * INTO OUTFILE 'C:/path/to/employees.csv'
-- FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-- LINES TERMINATED BY '\n'
-- FROM Employees;

-- Basic User Management (Conceptual)
-- CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'; -- MySQL
-- GRANT SELECT ON Employees TO new_user; -- PostgreSQL
-- GRANT SELECT ON Employees TO new_user; -- SQL Server

-- String Manipulation
SELECT
    FirstName,
    LastName,
    CONCAT(FirstName, ' ', LastName) AS FullName,
    UPPER(FirstName) AS FirstNameUpper,
    LOWER(LastName) AS LastNameLower,
    LENGTH(FirstName) AS FirstNameLength
FROM Employees;

-- Date Functions (assuming we add HireDate)
-- Update with sample hire dates
UPDATE Employees SET HireDate = '2020-01-15' WHERE EmployeeID = 101;
UPDATE Employees SET HireDate = '2019-06-20' WHERE EmployeeID = 102;
UPDATE Employees SET HireDate = '2021-03-10' WHERE EmployeeID = 104;

SELECT
    FirstName,
    LastName,
    HireDate,
    YEAR(HireDate) AS HireYear,
    MONTH(HireDate) AS HireMonth,
    DATEDIFF(CURDATE(), HireDate) AS DaysSinceHire -- MySQL
FROM Employees
WHERE HireDate IS NOT NULL;

-- Conditional Logic with CASE
SELECT
    FirstName,
    LastName,
    Salary,
    CASE
        WHEN Salary >= 70000 THEN 'High'
        WHEN Salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryBracket
FROM Employees;

-- NULL Handling
SELECT
    FirstName,
    LastName,
    HireDate,
    COALESCE(HireDate, 'Not Available') AS HireDateDisplay
FROM Employees;

-- Create a new table 'Products' with various constraints
CREATE TABLE Products (
    ProductID INT PRIMARY KEY, -- PRIMARY KEY (NOT NULL + UNIQUE)
    ProductName VARCHAR(100) NOT NULL UNIQUE, -- NOT NULL and UNIQUE
    Category VARCHAR(50) DEFAULT 'General', -- DEFAULT value
    Price DECIMAL(10, 2) CHECK (Price > 0), -- CHECK constraint
    StockQuantity INT DEFAULT 0, -- DEFAULT value
    SupplierID INT,
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID) -- FOREIGN KEY (requires Suppliers table)
);

-- Create a 'Suppliers' table for the FOREIGN KEY
CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY,
    SupplierName VARCHAR(100) NOT NULL
);

INSERT INTO Suppliers (SupplierID, SupplierName) VALUES (1, 'TechCorp'), (2, 'OfficeSupplies');

-- Insert valid data into Products
INSERT INTO Products (ProductID, ProductName, Category, Price, StockQuantity, SupplierID) VALUES
(1, 'Laptop', 'Electronics', 1200.00, 50, 1),
(2, 'Desk Chair', 'Furniture', 250.00, 30, 2);

-- Attempt to insert invalid data (violates NOT NULL for ProductName)
-- INSERT INTO Products (ProductID, Category, Price) VALUES (3, NULL, 'Electronics', 50.00);
-- Attempt to insert invalid data (violates UNIQUE for ProductName)
-- INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (3, 'Laptop', 'Electronics', 800.00);
-- Attempt to insert invalid data (violates CHECK for Price)
-- INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (3, 'Mouse', 'Electronics', -10.00);
-- Attempt to insert invalid data (violates FOREIGN KEY for SupplierID)
-- INSERT INTO Products (ProductID, ProductName, Category, Price, SupplierID) VALUES (3, 'Monitor', 'Electronics', 400.00, 99);

-- Transactions Example: Transferring funds (conceptual)
-- This ensures either both updates succeed or both fail.

BEGIN TRANSACTION; -- Start a transaction

-- Assume 'Accounts' table with AccountID, Balance
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- If everything is successful:
-- COMMIT;

-- If an error occurs or you decide to cancel:
-- ROLLBACK;

-- Example of a conditional update based on another table (SQL Server/PostgreSQL/MySQL variations)
-- UPDATE Employees
-- SET Salary = E.Salary * 1.10 -- Increase by 10%
-- FROM Employees E
-- INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID
-- WHERE D.DepartmentName = 'Sales';

-- PostgreSQL/MySQL equivalent (using subquery or JOIN syntax)
-- UPDATE Employees
-- SET Salary = Employees.Salary * 1.10
-- WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');

-- 1. Create a View for Sales Report
-- Views simplify complex queries and can restrict data access.
CREATE VIEW SalesReport AS
SELECT
    O.OrderID,
    C.CustomerName,
    P.ProductName,
    OD.Quantity,
    OD.PriceAtOrder,
    (OD.Quantity * OD.PriceAtOrder) AS LineTotal,
    O.OrderDate
FROM
    Orders AS O
INNER JOIN
    Customers AS C ON O.CustomerID = C.CustomerID
INNER JOIN
    OrderDetails AS OD ON O.OrderID = OD.OrderID
INNER JOIN
    Products AS P ON OD.ProductID = P.ProductID;

-- Query the View
SELECT * FROM SalesReport WHERE OrderDate >= '2023-01-01';

-- 2. Set Operations: UNION (combining results from two similar tables)
-- Create TABLE NewEmployees table for demonstration
CREATE TABLE NewEmployees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

INSERT INTO NewEmployees (EmployeeID, FirstName, LastName, Department) VALUES
(201, 'Sarah', 'Connor', 'IT'),
(202, 'Kyle', 'Reese', 'Sales');

SELECT FirstName, LastName, Department FROM Employees
UNION -- Removes duplicates
SELECT FirstName, LastName, Department FROM NewEmployees;

SELECT FirstName, LastName, Department FROM Employees
UNION ALL -- Includes duplicates
SELECT FirstName, LastName, Department FROM NewEmployees;

-- INTERSECT (find common records - might need specific RDBMS syntax)
-- SELECT FirstName, LastName FROM Employees
-- INTERSECT
-- SELECT FirstName, LastName FROM NewEmployees;

-- EXCEPT (find records in first query not in second - might need specific RDBMS syntax)
-- SELECT FirstName, LastName FROM Employees
-- EXCEPT
-- SELECT FirstName, LastName FROM NewEmployees;

-- 3. Create an Index to improve query performance
-- This index will speed up queries that filter or Join on the LastName column of Employees table.
CREATE INDEX IX_Employees_LastName ON Employees (LastName);

-- Example of a query that would benefit from the index:
SELECT * FROM Employees WHERE LastName = 'Smith';

-- ABS function: Returns the absolute (positive) value of a number.
SELECT ABS(-0.08) AS AbsoluteValue;

-- ROUND function: Rounds a number to a specified number of decimal places.
SELECT
    ROUND(10.09) AS RoundToNearestIntPos,
    ROUND(-10.09) AS RoundToNearestIntNeg,
    ROUND(-10.44) AS RoundToNearestIntNeg2,
    ROUND(10.4) AS RoundToNearestIntPos2,
    ROUND(10000.55) AS RoundExample,
    ROUND(20000.56, 1) AS RoundToOneDecimal
FROM DUAL;

-- CEIL function: Returns the smallest integer greater than or equal to the given number.
SELECT
    CEIL(10.09) AS CeilPos,
    CEIL(-10.09) AS CeilNeg
FROM DUAL;

-- FLOOR function: Returns the largest integer less than or equal to the given number.
SELECT
    FLOOR(10.09) AS FloorPos,
    FLOOR(-10.09) AS FloorNeg,
    FLOOR(20000.56) AS FloorExample
FROM DUAL;

-- SUBSTRING (or SUBSTR): Extracts a substring from a string.
-- Syntax: SUBSTR(value, start_position, length)
-- Note: SQL indexing typically starts from 1.

-- Example with positive start position:
SELECT
    SUBSTR('abcdefghijklmnopqrstuvwxyz', 1, 4) AS FIRST4,
    SUBSTR('abcdefghijklmnopqrstuvwxyz', 2, 10) AS TEN_FROM_2,
    SUBSTR('abcdefghijklmnopqrstuvwxyz', 3, 5) AS ALL_FROM_3
FROM DUAL;

-- Example with negative start position (counts from end):
SELECT
    SUBSTR('abcdefghijklmnopqrstuvwxyz', -1) AS LastChar,
    SUBSTR('abcdefghijklmnopqrstuvwxyz', -10) AS LastTenChars,
    SUBSTR('abcdefghijklmnopqrstuvwxyz', -20, 10) AS TenCharsFromTwentiethLast
FROM DUAL;

-- CHARINDEX (or INSTR/LOCATE): Returns the starting position of a substring within a string.
SELECT CHARINDEX('@', 'Rajkumar@example.com') AS AtPosition;

-- LENGTH (or LEN): Returns the length of a string.
SELECT LENGTH('Hello World 123') AS StringLength;

-- UPPER and LOWER: Converts a string to uppercase or lowercase.
SELECT
    UPPER('HelloWorld') AS UpperCase,
    LOWER('HelloWorld') AS LowerCase
FROM DUAL;

-- REPLACE: Replaces all occurrences of a substring with another substring.
SELECT REPLACE('Mississippi', 'iss', 'X') AS ReplacedString;

-- CONCAT: Concatenates two or more strings.
SELECT CONCAT('First', 'Last') AS FullName;

-- CONCAT with multiple parts (SQL Server syntax, conceptual for compiler)
SELECT CONCAT(name, ' is from ', location) AS result FROM salesman;

-- SYSDATE (or GETDATE()): Returns the current system date and time.
-- SYSTIMESTAMP: Returns the current system date and time with fractional seconds and timezone.
SELECT
    SYSDATE AS CurrentDate,
    SYSTIMESTAMP AS CurrentTimestamp
FROM DUAL;

-- ADD_MONTHS: Adds a specified number of months to a date.
SELECT
    RecordDate,
    ADD_MONTHS(RecordDate, 1) AS NextMonthDate
FROM Rise_Temperature WHERE ID = 1;

-- MONTHS_BETWEEN: Returns the number of months between two dates.
SELECT
    MONTHS_BETWEEN('2014-02-01', '2014-01-01') AS MonthDiff1,
    MONTHS_BETWEEN('2016-01-10', '2014-01-01') AS MonthDiff2,
    ABS(MONTHS_BETWEEN(ADD_MONTHS('2014-02-09', 3), '2015-03-09')) AS MonthsBetweenExample
FROM DUAL;

-- TO_CHAR (Number to String): Converts a number to a string with optional formatting.
SELECT
    TO_CHAR(1000) AS ORIG_NOFORMAT,
    TO_CHAR(1000.98) AS CONV_NOFORMAT,
    TO_CHAR(1000.98, '9999.99') AS FIXED_DIGITS,
    TO_CHAR(1000.98, '9,999.99') AS WITH_COMMA
FROM DUAL;

-- TO_CHAR (Date to String): Converts a date to a string with optional formatting.
SELECT
    RecordDate AS RECORDDATE,
    TO_CHAR(RecordDate, 'MON') AS MONTH_ABBR,
    TO_CHAR(RecordDate, 'Month') AS FULL_MONTH,
    TO_CHAR(RecordDate, 'Dy') AS DAY_ABBR,
    TO_CHAR(RecordDate, 'Day') AS FULL_DAY,
    TO_CHAR(RecordDate, 'DD/MM/YYYY') AS INDIAN_FORMAT,
    TO_CHAR(RecordDate, 'MM/DD/YYYY') AS AMERICAN_FORMAT
FROM Rise_Temperature WHERE ID = 1;

-- TO_DATE: Converts a string to a date with a specified format.
SELECT
    '01-Jan-2014' AS DATE_STRING,
    TO_DATE('01-Jan-2014', 'DD-Mon-YYYY') AS ConvertedDate
FROM DUAL;

-- TO_NUMBER (String to Number): Converts a string to a number with optional formatting.
SELECT TO_NUMBER('2,50,000.00', '999,999.99') AS ConvertedAmount;

-- NVL (Oracle) / IFNULL (MySQL) / COALESCE (Standard SQL): Replaces NULL with a specified value.
SELECT
    Salary,
    IFNULL(Salary, 0.0) AS SalaryWithDefault,
    Bonus,
    IFNULL(Bonus, 0.0) AS BonusWithDefault
FROM Employees WHERE EmployeeID IN (1, 2);

-- SQL Server Specific: Concatenate Employee Names into a single string
-- SELECT EmployeeNames =
--     STUFF((SELECT ', ' + FirstName
--            FROM Employees
--            FOR XML PATH('')), 1, 2, '')

-- Function 1: Count number of books in each book category (conceptual)
-- ALTER FUNCTION ufn_NumberOfBooks_BookCategory() RETURNS TABLE
-- AS RETURN
-- (
--     SELECT ISNULL(CAST(BookCategory AS VARCHAR(20)), 'Total Books') AS BookCategory, COUNT(BookCategory) AS NumberOfBooks
--     FROM Books
--     GROUP BY ROLLUP(BookCategory)
-- );
-- Usage: SELECT * FROM ufn_NumberOfBooks_BookCategory();

-- Function 2: Retrieve books purchased using 'Online' payment mode (conceptual)
-- CREATE FUNCTION ufn_BooksPurchased_MediumOnline() RETURNS TABLE
-- AS RETURN
-- (
--     SELECT ISBN, BookName, AuthorName, Edition, BookCategory, Price
--     FROM Books
--     WHERE ISBN IN (SELECT DISTINCT ISBN FROM OnlineBooking WHERE PaymentMode = 'Online' GROUP BY ISBN)
-- );
-- Usage: SELECT * FROM ufn_BooksPurchased_MediumOnline();

-- Function 3: Find the quickest book delivery (conceptual)
-- CREATE FUNCTION ufn_QuickDelivery() RETURNS TABLE
-- AS RETURN
-- (
--     SELECT BookingId, UserId, ISBN, NumberOfBooksPurchased, BookingDate, DeliveryDate, DeliveryCharges, PaymentMode
--     FROM OnlineBooking
--     WHERE DATEDIFF(DATEYFEAR, BookingDate, DeliveryDate) = (SELECT MIN(DATEDIFF(DATEYEAR, BookingDate, DeliveryDate)) FROM OnlineBooking)
-- );
-- Usage: SELECT * FROM ufn_QuickDelivery();

-- Function 4: Find books that have not been purchased online (conceptual)
-- CREATE FUNCTION ufn_Books_NotPurchased() RETURNS TABLE
-- AS RETURN
-- (
--     SELECT ISBN, BookName, AuthorName, Edition, BookCategory, Price
--     FROM Books
--     WHERE ISBN NOT IN (SELECT DISTINCT ISBN FROM OnlineBooking)
-- );
-- Usage: SELECT * FROM ufn_Books_NotPurchased();

-- Function 5: Find books delivered in a specific month (conceptual)
-- CREATE FUNCTION ufn_BooksDelivered(@MonthName VARCHAR(20)) RETURNS TABLE
-- AS RETURN
-- (
--     SELECT BookingId, UserId, ISBN, NumberOfBooksPurchased, BookingDate, DeliveryDate, DeliveryCharges, PaymentMode
--     FROM OnlineBooking
--     WHERE DATENAME(MONTH, DeliveryDate) = @MonthName
-- );
-- Usage: SELECT * FROM ufn_BooksDelivered('February');

-- Function 6: Rank books based on ISBN (conceptual)
-- CREATE FUNCTION ufn_RankBooks() RETURNS TABLE
-- AS RETURN
-- (
--     SELECT RANK() OVER (PARTITION BY ISBN ORDER BY ISBN DESC) AS [Rank], BookName, AuthorName, Edition, BookCategory, Price
--     FROM Books
-- );
-- Usage: SELECT * FROM ufn_RankBooks();

-- Function 7: Validate a user based on their mobile number (conceptual)
-- CREATE FUNCTION ufn_ValidateUser(@MobileNumber BIGINT) RETURNS VARCHAR(20)
-- AS
-- BEGIN
--     DECLARE @UserName VARCHAR(20)
--     IF EXISTS (SELECT UserName FROM [User] WHERE MobileNumber = @MobileNumber)
--         BEGIN
--             SET @UserName = (SELECT UserName FROM [User] WHERE MobileNumber = @MobileNumber)
--         END
--     RETURN @UserName
-- END
-- Usage: SELECT ufn_ValidateUser(1234567890) AS UserName;

-- Function 8: Validate a user and password combination (conceptual)
-- CREATE FUNCTION ufn_ValidateUserandPassword(@UserName VARCHAR(20), @Password VARCHAR(20)) RETURNS INT
-- AS
-- BEGIN
--     IF EXISTS (SELECT UserName FROM [User] WHERE UserName = @UserName)
--         BEGIN
--             IF EXISTS (SELECT UserName FROM [User] WHERE UserName = @UserName AND [Password] = @Password)
--                 BEGIN
--                     RETURN 1
--                 END
--             ELSE
--                 RETURN 0
--         END
--     ELSE
--         RETURN -1
-- END
-- Usage: SELECT ufn_ValidateUserandPassword('TestUser', 'TestPass123') AS ValidUser;

-- Stored Procedure: Insert online booking information (conceptual)
-- DROP PROCEDURE IF EXISTS InsertOnlineBooking;
-- CREATE PROCEDURE InsertOnlineBooking
-- (
--     @UserId INT,
--     @BookName VARCHAR(50),
--     @NumberOfBooksPurchased INT,
--     @PaymentMode VARCHAR(20),
--     @BookingId INT OUTPUT
-- )
-- AS
-- BEGIN
--     BEGIN TRY
--         IF NOT EXISTS (SELECT UserId FROM [User] WHERE UserId = @UserId)
--             BEGIN
--                 RETURN -1
--             END
--         ELSE
--             BEGIN
--                 IF NOT EXISTS (SELECT BookName FROM Books WHERE BookName = @BookName)
--                     BEGIN
--                         RETURN -2
--                     END
--                 ELSE
--                     BEGIN
--                         IF (@NumberOfBooksPurchased >= 1)
--                             BEGIN
--                                 RETURN -3
--                             END
--                         ELSE
--                             BEGIN
--                                 IF NOT (@PaymentMode IN ('Online', 'Cash'))
--                                     BEGIN
--                                         RETURN -4
--                                     END
--                                 ELSE
--                                     BEGIN
--                                         DECLARE @BookingDate DATE, @DeliveryDate DATE, @DeliveryCharges FLOAT, @BillAmount FLOAT, @Price FLOAT, @ISBN INT
--                                         
--                                         SET @BookingId = 0
--                                         SET @ISBN = (SELECT ISBN FROM Books WHERE BookName = @BookName)
--                                         SET @BookingDate = CAST(GETDATE() AS DATE)
--                                         SET @DeliveryDate = CAST(GETDATE() + 3 AS DATE)
--                                         
--                                         IF ((SELECT BookCategory FROM Books WHERE BookName = @BookName) = 'Cook Books')
--                                             BEGIN
--                                                 SET @DeliveryCharges = 40
--                                             END
--                                         ELSE
--                                             BEGIN
--                                                 SET @DeliveryCharges = 0
--                                             END
--                                         
--                                         SET @Price = (SELECT Price FROM Books WHERE ISBN = (SELECT ISBN FROM Books WHERE BookName = @BookName))
--                                         
--                                         IF (@PaymentMode = 'Online')
--                                             BEGIN
--                                                 SET @Discount = 0.10
--                                             END
--                                         ELSE
--                                             BEGIN
--                                                 SET @Discount = 0
--                                             END
--                                         
--                                         SET @BillAmount = (SELECT @NumberOfBooksPurchased * @Price - @NumberOfBooksPurchased * @Price * @Discount + @DeliveryCharges)
--                                         
--                                         INSERT INTO OnlineBooking (UserId, ISBN, NumberOfBooksPurchased, BookingDate, BookingDate, DeliveryDate, DeliveryCharges, PaymentMode, @BillAmount)
--                                         VALUES (@UserId, @ISBN, @NumberOfBooksPurchased, @BookingDate, @DeliveryDate, @DeliveryCharges, @PaymentMode, @BillAmount)
--                                         
--                                         SELECT @BookingId = MAX(BookingId) FROM OnlineBooking
--                                         
--                                         RETURN 1
--                                     END
--                             END
--                     END
--             END
--     END TRY
--     BEGIN CATCH
--         SELECT ERROR_LINE() AS [Error Line], ERROR_MESSAGE() AS [Error Message], ERROR_NUMBER() AS [Error Number]
--         RETURN -5
--     END CATCH
-- END
-- Usage:
-- DECLARE @RET_INT, @Ld INT
-- EXEC @RET = InsertOnlineBooking 1002, 'Clean Slate', 2, 'Online', @Ld OUT
-- SELECT @RET, @Ld
-- SELECT * FROM OnlineBooking;

-- Example: Retrieve the 2nd employee (skipping the first)
SELECT EmployeeName, Salary FROM Employees
ORDER BY EmployeeName
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

-- Example: Using HAVING without an aggregate (behaves like WHERE)
SELECT EmployeeName, Department
FROM Employees
HAVING Department = 'IT';

-- Conceptual query. Assumes a 'student' table with 'name' column.
-- SELECT name
-- FROM student
-- WHERE LENGTH(name) = 10 AND LOWER(name) LIKE 'kk%';

-- Setup for DUPLICATE_CHECK (already in in-memory DB)
-- CREATE TABLE DUPLICATE_CHECK(
-- ID INT,
-- NAME NVARCHAR(30),
-- AGE INT);
-- INSERT INTO DUPLICATE_CHECK VALUES (1,'KAPIL',20);
-- INSERT INTO DUPLICATE_CHECK VALUES (1,'KAPIL',21);
-- INSERT INTO DUPLICATE_CHECK VALUES (2,'ABC',22);
-- INSERT INTO DUPLICATE_CHECK VALUES (3,'DEF',24);
-- INSERT INTO DUPLICATE_CHECK VALUES (4,'GHI',29);
SELECT * FROM DUPLICATE_CHECK;

SELECT ID, NAME, COUNT(*) AS Frequency
FROM DUPLICATE_CHECK
GROUP BY ID, NAME
HAVING COUNT(*) > 1;

-- Example using Employees table with some NULLs in Bonus
SELECT
    COUNT(*) AS TotalEmployees,
    COUNT(Bonus) AS EmployeesWithBonus
FROM Employees;

-- Conceptual DDL: This will create a new table 'EmployeeManager'
-- CREATE TABLE EmployeeManager AS
-- SELECT
--     e.EmployeeName AS Employee,
--     m.EmployeeName AS Manager
-- FROM
--     Employees e
-- LEFT JOIN
--     Employees m ON e.ManagerID = m.EmployeeID;

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
ORDER BY AvgSalary DESC;

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

SELECT Department, MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department
HAVING MIN(Salary) < 5000 AND MAX(Salary) > 15000;