-- Creating table with various data types CREATE TABLE Employees ( employee_id INT PRIMARY KEY, -- 4 bytes, -2^31 to 2^31-1 first_name VARCHAR(50) NOT NULL, -- Variable length string last_name VARCHAR(50) NOT NULL, salary DECIMAL(10,2), -- Precision 10, Scale 2 hire_date DATE, -- Date only (no time) birth_date DATETIME, -- Date and time is_active BIT DEFAULT 1, -- Boolean (0/1) resume TEXT, -- Large text data profile_pic VARBINARY(MAX), -- Binary data skills JSON, -- JSON data (PostgreSQL/MySQL 5.7+) created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Type casting examples SELECT CAST(123.45 AS INT) AS integer_value, -- 123 CONVERT(VARCHAR, GETDATE(), 103) AS uk_date, -- dd/mm/yyyy 'Price: $' + CAST(99.99 AS VARCHAR(10)) AS formatted_price;

-- CREATE DATABASE CREATE DATABASE CompanyDB ON PRIMARY ( NAME = 'CompanyDB_Data', FILENAME = 'C:\Data\CompanyDB.mdf', SIZE = 100MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ); -- CREATE TABLE with constraints CREATE TABLE Departments ( dept_id INT IDENTITY(1,1) PRIMARY KEY, dept_name VARCHAR(100) NOT NULL UNIQUE, manager_id INT, budget DECIMAL(15,2) CHECK (budget >= 0), created_date DATETIME DEFAULT GETDATE(), CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES Employees(employee_id) ON DELETE SET NULL ); -- ALTER TABLE examples ALTER TABLE Employees ADD email VARCHAR(100) UNIQUE, phone VARCHAR(20); ALTER TABLE Employees ALTER COLUMN salary DECIMAL(12,2); ALTER TABLE Employees DROP COLUMN old_column_name; -- DROP TABLE (use with caution!) DROP TABLE IF EXISTS TemporaryTable;

-- INSERT examples INSERT INTO Employees (first_name, last_name, salary, hire_date) VALUES ('John', 'Doe', 75000.00, '2024-01-15'); -- Insert multiple rows INSERT INTO Employees (first_name, last_name, salary) VALUES ('Jane', 'Smith', 82000.00), ('Bob', 'Johnson', 68000.00), ('Alice', 'Williams', 91000.00); -- INSERT from SELECT INSERT INTO Managers (emp_id, dept_id, start_date) SELECT employee_id, department_id, GETDATE() FROM Employees WHERE salary > 80000; -- UPDATE examples UPDATE Employees SET salary = salary * 1.10, -- 10% raise last_raise_date = GETDATE() WHERE hire_date < '2023-01-01'; -- UPDATE with JOIN (SQL Server) UPDATE e SET e.salary = e.salary * 1.05 FROM Employees e JOIN Departments d ON e.dept_id = d.dept_id WHERE d.dept_name = 'Engineering'; -- DELETE examples DELETE FROM Employees WHERE hire_date < '2020-01-01' AND active_status = 0; -- MERGE/UPSERT example MER INTO EmployeeTarget AS target USING EmployeeSource AS source ON target.employee_id = source.employee_id WHEN MATCHED THEN UPDATE SET target.salary = source.salary WHEN NOT MATCHED THEN INSERT (employee_id, first_name, salary) VALUES (source.employee_id, source.first_name, source.salary);

-- Basic SELECT SELECT * FROM Employees; -- Returns all columns -- Specific columns with aliases SELECT employee_id AS "ID", first_name + ' ' + last_name AS "Full Name", salary AS "Annual Salary", salary / 12 AS "Monthly Salary" FROM Employees; -- SELECT with expressions SELECT product_name, unit_price, quantity_in_stock, unit_price * 0.9 AS "Discounted Price", CASE WHEN quantity_in_stock < 10 THEN 'Low Stock ⚠️' WHEN quantity_in_stock < 50 THEN 'Medium Stock' ELSE 'In Stock ✅' END AS stock_status FROM Products; -- SELECT DISTINCT SELECT DISTINCT department_id, job_title FROM Employees; -- TOP/LIMIT examples SELECT TOP 10 * FROM Employees ORDER BY salary DESC; -- SQL Server -- MySQL: SELECT * FROM Employees LIMIT 10; -- PostgreSQL: SELECT * FROM Employees LIMIT 10 OFFSET 20; -- Computed columns SELECT order_id, order_date, YEAR(order_date) AS order_year, DATENAME(month, order_date) AS order_month, DATEDIFF(day, order_date, GETDATE()) AS days_since_order FROM Orders;

-- Basic WHERE examples SELECT * FROM Employees WHERE salary > 50000 AND department_id = 10; -- Multiple conditions with parentheses SELECT * FROM Orders WHERE (status = 'Shipped' OR status = 'Processing') AND order_date >= '2024-01-01' AND total_amount > 1000; -- LIKE pattern matching SELECT * FROM Customers WHERE email LIKE '%@gmail.com' AND phone LIKE '+1%'; -- More complex patterns SELECT * FROM Products WHERE product_name LIKE 'Pro%' -- Starts with Pro OR product_name LIKE '%2024%' -- Contains 2024 OR product_name LIKE '___-___' -- Exactly 3 chars, dash, 3 chars OR product_name REGEXP '^[A-Z]'; -- Starts with uppercase letter -- BETWEEN (inclusive) SELECT * FROM Orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' AND total_amount BETWEEN 100 AND 1000; -- IN operator SELECT * FROM Employees WHERE department_id IN (10, 20, 30) AND job_title IN ('Manager', 'Director', 'VP'); -- NULL handling SELECT * FROM Employees WHERE manager_id IS NULL -- Correct OR manager_id IS NOT NULL; -- NOT with other operators SELECT * FROM Products WHERE NOT (price > 100 OR discontinued = 1) AND category_id NOT IN (5, 10, 15); -- Complex conditional logic SELECT employee_id, first_name, last_name, salary, CASE WHEN salary >= 100000 THEN 'Executive 💼' WHEN salary >= 70000 THEN 'Senior 👔' WHEN salary >= 50000 THEN 'Mid-Level 📊' ELSE 'Junior 📝' END AS salary_band FROM Employees WHERE (department_id = 10 AND salary > 60000) OR (department_id = 20 AND hire_date > '2023-01-01') ORDER BY salary DESC;

-- INNER JOIN (default) - Only matching rows SELECT e.employee_id, e.first_name, e.last_name, d.dept_name, e.salary FROM Employees e INNER JOIN Departments d ON e.department_id = d.dept_id WHERE e.salary > 50000; -- LEFT JOIN - All employees + department if exists SELECT e.employee_id, e.first_name, e.last_name, COALESCE(d.dept_name, 'No Department') AS department, m.first_name + ' ' + m.last_name AS manager_name FROM Employees e LEFT JOIN Departments d ON e.department_id = d.dept_id LEFT JOIN Employees m ON e.manager_id = m.employee_id; -- RIGHT JOIN - All departments + employees if exist SELECT d.dept_name, COUNT(e.employee_id) AS employee_count, AVG(e.salary) AS avg_salary FROM Employees e RIGHT JOIN Departments d ON e.department_id = d.dept_id GROUP BY d.dept_name; -- FULL OUTER JOIN - All from both sides SELECT COALESCE(e.first_name, 'No Employee') AS employee, COALESCE(d.dept_name, 'No Department') AS department FROM Employees e FULL OUTER JOIN Departments d ON e.department_id = d.dept_id; -- SELF JOIN - Employee hierarchy SELECT emp.first_name + ' ' + emp.last_name AS employee, mgr.first_name + ' ' + mgr.last_name AS manager FROM Employees emp LEFT JOIN Employees mgr ON emp.manager_id = mgr.employee_id ORDER BY manager, employee; -- CROSS JOIN - Cartesian product SELECT p.product_name, s.store_name, p.price FROM Products p CROSS JOIN Stores s; -- Every product with every store -- Multiple table join SELECT o.order_id, c.customer_name, p.product_name, od.quantity, od.unit_price, o.order_date FROM Orders o JOIN Customers c ON o.customer_id = c.customer_id JOIN Order_Details od ON o.order_id = od.order_id JOIN Products p ON od.product_id = p.product_id WHERE o.order_date >= '2024-01-01' ORDER BY o.order_date DESC;

-- Basic aggregate functions SELECT COUNT(*) AS total_employees, COUNT(DISTINCT department_id) AS unique_departments, SUM(salary) AS total_salary_budget, AVG(salary) AS average_salary, MIN(salary) AS minimum_salary, MAX(salary) AS maximum_salary, MAX(hire_date) AS most_recent_hire, STDEV(salary) AS salary_std_dev FROM Employees; -- Aggregate with expressions SELECT COUNT(*) AS total_orders, SUM(quantity * unit_price) AS total_revenue, AVG(quantity * unit_price) AS avg_order_value, MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM Order_Details od JOIN Orders o ON od.order_id = o.order_id; -- COUNT variations SELECT COUNT(*) AS total_rows, -- All rows COUNT(employee_id) AS non_null_ids, -- Ignores NULLs in column COUNT(DISTINCT department_id) AS dept_count, -- Unique departments COUNT(CASE WHEN salary > 50000 THEN 1 END) AS high_earners FROM Employees; -- STRING_AGG / GROUP_CONCAT (string concatenation) SELECT department_id, STRING_AGG(first_name + ' ' + last_name, ', ') AS employee_list, COUNT(*) AS employee_count FROM Employees GROUP BY department_id; -- MySQL: GROUP_CONCAT(first_name, ' ', last_name SEPARATOR ', ') -- Filtered aggregates using CASE SELECT department_id, COUNT(*) AS total_employees, SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count, SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count, AVG(CASE WHEN salary > 50000 THEN salary END) AS avg_high_salary FROM Employees GROUP BY department_id; -- Statistical aggregates SELECT AVG(salary) AS mean_salary, MIN(salary) AS min_salary, MAX(salary) AS max_salary, STDEV(salary) AS std_dev_salary, VAR(salary) AS variance_salary, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary FROM Employees;

-- Basic GROUP BY SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary, SUM(salary) AS total_salary FROM Employees WHERE hire_date > '2020-01-01' -- Filter BEFORE grouping GROUP BY department_id HAVING COUNT(*) > 5 -- Filter AFTER grouping ORDER BY avg_salary DESC; -- GROUP BY multiple columns SELECT YEAR(order_date) AS order_year, MONTH(order_date) AS order_month, customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent FROM Orders GROUP BY YEAR(order_date), MONTH(order_date), customer_id HAVING COUNT(*) >= 3 ORDER BY order_year, order_month, total_spent DESC; -- GROUP BY with expressions SELECT LEFT(product_name, 3) AS product_prefix, COUNT(*) AS product_count, AVG(price) AS avg_price FROM Products GROUP BY LEFT(product_name, 3) ORDER BY product_count DESC; -- Using HAVING with aggregate functions SELECT department_id, job_title, COUNT(*) AS emp_count, AVG(salary) AS avg_salary FROM Employees GROUP BY department_id, job_title HAVING AVG(salary) > 50000 AND COUNT(*) > 2 ORDER BY department_id, avg_salary DESC; -- GROUPING SETS (multiple groupings in one query) SELECT COALESCE(CAST(department_id AS VARCHAR), 'All Departments') AS dept, COALESCE(job_title, 'All Jobs') AS job, COUNT(*) AS emp_count, AVG(salary) AS avg_salary FROM Employees GROUP BY GROUPING SETS ( (department_id, job_title), -- Detailed level (department_id), -- Department summary (job_title), -- Job title summary () -- Grand total ) ORDER BY GROUPING(department_id), GROUPING(job_title), department_id, job_title; -- CUBE (all possible combinations) SELECT department_id, job_title, gender, COUNT(*) AS emp_count FROM Employees GROUP BY CUBE (department_id, job_title, gender); -- ROLLUP (hierarchical aggregation) SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, DAY(order_date) AS day, COUNT(*) AS order_count, SUM(total_amount) AS daily_total FROM Orders GROUP BY ROLLUP (YEAR(order_date), MONTH(order_date), DAY(order_date)) ORDER BY year, month, day;

-- Scalar subquery in SELECT SELECT employee_id, first_name, salary, (SELECT AVG(salary) FROM Employees) AS company_avg, salary - (SELECT AVG(salary) FROM Employees) AS diff_from_avg FROM Employees; -- Subquery in WHERE (non-correlated) SELECT * FROM Employees WHERE department_id IN ( SELECT dept_id FROM Departments WHERE location = 'New York' ); -- Correlated subquery SELECT e.first_name, e.last_name, e.salary, e.department_id, (SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id) AS dept_avg_salary FROM Employees e WHERE e.salary > ( SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id ); -- EXISTS vs IN -- EXISTS is often faster for correlated queries SELECT * FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id AND o.total_amount > 1000 ); -- Same query with IN (might be slower) SELECT * FROM Customers WHERE customer_id IN ( SELECT customer_id FROM Orders WHERE total_amount > 1000 ); -- Subquery in FROM clause (derived table) SELECT dept_stats.dept_name, dept_stats.emp_count, dept_stats.avg_salary FROM ( SELECT d.dept_name, COUNT(e.employee_id) AS emp_count, AVG(e.salary) AS avg_salary FROM Departments d LEFT JOIN Employees e ON d.dept_id = e.department_id GROUP BY d.dept_name ) AS dept_stats WHERE dept_stats.emp_count > 10 ORDER BY dept_stats.avg_salary DESC; -- Subquery with ALL/SOME/ANY operators SELECT * FROM Products WHERE price > ALL ( SELECT price FROM Products WHERE category_id = 5 ); SELECT * FROM Employees WHERE salary > ANY ( SELECT salary FROM Employees WHERE department_id = 10 ); -- Nested subqueries (use CTEs instead for readability) SELECT * FROM Orders WHERE customer_id IN ( SELECT customer_id FROM Customers WHERE country IN ( SELECT country FROM Countries WHERE region = 'Europe' ) ); -- Better approach with CTE: WITH EuropeanCustomers AS ( SELECT customer_id FROM Customers c JOIN Countries co ON c.country = co.country_name WHERE co.region = 'Europe' ) SELECT * FROM Orders WHERE customer_id IN (SELECT customer_id FROM EuropeanCustomers);

-- UNION (removes duplicates) SELECT product_name, price FROM Current_Products WHERE price > 100 UNION SELECT product_name, price FROM Archived_Products WHERE price > 100 ORDER BY price DESC; -- UNION ALL (keeps duplicates, faster) SELECT employee_id, first_name, 'Active' AS status FROM Employees WHERE active = 1 UNION ALL SELECT employee_id, first_name, 'Inactive' FROM Employees WHERE active = 0; -- INTERSECT (common rows) -- Customers who bought both Product A and Product B SELECT customer_id FROM Orders WHERE product_id = 'A' INTERSECT SELECT customer_id FROM Orders WHERE product_id = 'B'; -- EXCEPT (MINUS in Oracle) -- Products in inventory but never ordered SELECT product_id FROM Products EXCEPT SELECT product_id FROM Order_Details; -- Complex set operations with parentheses (SELECT customer_id FROM Orders WHERE YEAR(order_date) = 2023) EXCEPT ( (SELECT customer_id FROM Orders WHERE YEAR(order_date) = 2022) INTERSECT (SELECT customer_id FROM Orders WHERE YEAR(order_date) = 2021) ) ORDER BY customer_id; -- Set operations with different numbers of columns (use NULL) SELECT first_name, last_name, hire_date FROM Current_Employees UNION ALL SELECT first_name, last_name, NULL FROM Archived_Employees; -- Practical example: Monthly active users report WITH MonthlyUsers AS ( -- January active users SELECT user_id, 'January' AS month FROM Logins WHERE login_date BETWEEN '2024-01-01' AND '2024-01-31' UNION -- February active users SELECT user_id, 'February' AS month FROM Logins WHERE login_date BETWEEN '2024-02-01' AND '2024-02-28' UNION -- March active users SELECT user_id, 'March' AS month FROM Logins WHERE login_date BETWEEN '2024-03-01' AND '2024-03-31' ) SELECT month, COUNT(DISTINCT user_id) AS active_users FROM MonthlyUsers GROUP BY month ORDER BY CASE month WHEN 'January' THEN 1 WHEN 'February' THEN 2 WHEN 'March' THEN 3 END; -- Set operations for data validation -- Find orphaned records (in child table but not in parent) SELECT customer_id FROM Orders WHERE customer_id NOT IN (SELECT customer_id FROM Customers) -- Alternative using EXCEPT SELECT customer_id FROM Orders EXCEPT SELECT customer_id FROM Customers;

-- Creating a simple view CREATE VIEW EmployeeDirectory AS SELECT e.employee_id, e.first_name, e.last_name, e.email, d.dept_name AS department, e.hire_date, e.salary FROM Employees e JOIN Departments d ON e.department_id = d.dept_id WHERE e.active = 1; -- Using the view SELECT * FROM EmployeeDirectory WHERE department = 'Engineering' ORDER BY last_name; -- Complex view with aggregates CREATE VIEW DepartmentSummary AS SELECT d.dept_id, d.dept_name, COUNT(e.employee_id) AS employee_count, AVG(e.salary) AS avg_salary, SUM(e.salary) AS total_budget, MIN(e.hire_date) AS oldest_hire, MAX(e.hire_date) AS newest_hire FROM Departments d LEFT JOIN Employees e ON d.dept_id = e.department_id GROUP BY d.dept_id, d.dept_name; -- View with check option (enforces constraints) CREATE VIEW HighPaidEmployees AS SELECT employee_id, first_name, last_name, salary FROM Employees WHERE salary > 80000 WITH CHECK OPTION; -- Prevents inserts/updates that would violate WHERE -- Trying to update through view (will fail due to CHECK OPTION) UPDATE HighPaidEmployees SET salary = 70000 WHERE employee_id = 101; -- Error: violates CHECK OPTION -- Materialized view (PostgreSQL/Oracle syntax) CREATE MATERIALIZED VIEW MonthlySales AS SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) AS order_count, SUM(total_amount) AS total_sales, AVG(total_amount) AS avg_order_value FROM Orders GROUP BY DATE_TRUNC('month', order_date); -- Refresh materialized view REFRESH MATERIALIZED VIEW MonthlySales; -- View with encryption (hides definition) CREATE VIEW EncryptedView WITH ENCRYPTION AS SELECT * FROM SensitiveData; -- System catalog views SELECT * FROM INFORMATION_SCHEMA.TABLES; SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employees'; -- Dynamic management views (SQL Server) SELECT * FROM sys.dm_exec_connections; SELECT * FROM sys.dm_os_performance_counters; -- Updatable view example CREATE VIEW UpdatableEmployeeView AS SELECT employee_id, first_name, last_name, email, department_id FROM Employees WHERE department_id IN (10, 20, 30); -- This update will work (view is updatable) UPDATE UpdatableEmployeeView SET email = 'new.email@company.com' WHERE employee_id = 1001; -- View with computed columns CREATE VIEW EmployeeBenefits AS SELECT employee_id, first_name, last_name, salary, salary * 0.05 AS retirement_contribution, salary * 0.03 AS health_insurance, salary * 0.01 AS training_budget, salary * 0.91 AS net_compensation FROM Employees; -- Dropping a view DROP VIEW IF EXISTS OldEmployeeView;

-- Creating a clustered index (primary key usually creates one) CREATE CLUSTERED INDEX IX_Employees_EmployeeID ON Employees(employee_id); -- Non-clustered index CREATE INDEX IX_Employees_LastName ON Employees(last_name); -- Composite index (multiple columns) CREATE INDEX IX_Employees_Dept_HireDate ON Employees(department_id, hire_date DESC); -- Unique index CREATE UNIQUE INDEX UQ_Employees_Email ON Employees(email); -- Filtered index (SQL Server) - index subset of rows CREATE INDEX IX_ActiveEmployees_Dept ON Employees(department_id) WHERE active = 1; -- Include additional columns (covering index) CREATE INDEX IX_Employees_Search ON Employees(last_name, first_name) INCLUDE (email, phone, department_id); -- Full-text index (for text search) CREATE FULLTEXT CATALOG FTCatalog AS DEFAULT; CREATE FULLTEXT INDEX ON Products(product_description) KEY INDEX PK_Products ON FTCatalog; -- Spatial index (for geography/geometry) CREATE SPATIAL INDEX IX_Customers_Location ON Customers(location); -- Columnstore index (for data warehousing) CREATE COLUMNSTORE INDEX CSI_Orders ON OrderHistory(order_id, product_id, quantity, price, order_date); -- Viewing index information SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Employees'); EXEC sp_helpindex 'Employees'; -- SQL Server SHOW INDEX FROM Employees; -- MySQL -- Index maintenance -- Rebuild (creates new index) ALTER INDEX IX_Employees_LastName ON Employees REBUILD; -- Reorganize (defragments) ALTER INDEX IX_Employees_LastName ON Employees REORGANIZE; -- Disable/Enable index ALTER INDEX IX_Employees_LastName ON Employees DISABLE; ALTER INDEX IX_Employees_LastName ON Employees REBUILD; -- Re-enables -- Dropping index DROP INDEX IX_Employees_LastName ON Employees; -- Best practices example -- Good for WHERE clause: WHERE department_id = 10 AND hire_date > '2023-01-01' CREATE INDEX IX_Employees_DeptID_HireDate ON Employees(department_id, hire_date); -- Good for ORDER BY: ORDER BY last_name, first_name CREATE INDEX IX_Employees_Name ON Employees(last_name, first_name); -- Good for JOIN: ON e.department_id = d.dept_id CREATE INDEX IX_Employees_DeptID ON Employees(department_id); -- Monitoring index usage SELECT object_name(object_id) AS table_name, name AS index_name, user_seeks, user_scans, user_lookups, user_updates FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID() ORDER BY user_seeks + user_scans DESC;

-- Basic transaction BEGIN TRANSACTION; BEGIN TRY -- Transfer money between accounts UPDATE Accounts SET balance = balance - 100 WHERE account_id = 101; UPDATE Accounts SET balance = balance + 100 WHERE account_id = 202; -- Log the transaction INSERT INTO TransactionLog (from_account, to_account, amount, timestamp) VALUES (101, 202, 100, GETDATE()); COMMIT TRANSACTION; PRINT 'Transaction completed successfully ✅'; END TRY BEGIN CATCH ROLLBACK TRANSACTION; PRINT 'Transaction failed: ' + ERROR_MESSAGE() + ' ❌'; END CATCH; -- Transaction with savepoints BEGIN TRANSACTION; INSERT INTO Orders (customer_id, order_date) VALUES (1001, GETDATE()); SAVE TRANSACTION OrderCreated; INSERT INTO OrderDetails (order_id, product_id, quantity) VALUES (SCOPE_IDENTITY(), 500, 2); -- Something went wrong with details, rollback to savepoint ROLLBACK TRANSACTION OrderCreated; -- Continue with corrected details INSERT INTO OrderDetails (order_id, product_id, quantity) VALUES (SCOPE_IDENTITY(), 500, 1); COMMIT TRANSACTION; -- Setting isolation level SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; SELECT * FROM Orders WHERE order_date > '2024-01-01'; -- Other transactions cannot modify these rows until commit COMMIT TRANSACTION; -- Nested transactions (SQL Server) BEGIN TRANSACTION OuterTran; PRINT 'Transaction count: ' + CAST(@@TRANCOUNT AS VARCHAR); BEGIN TRANSACTION InnerTran; PRINT 'Transaction count: ' + CAST(@@TRANCOUNT AS VARCHAR); -- Only COMMIT OuterTran actually commits COMMIT TRANSACTION InnerTran; PRINT 'Transaction count after inner commit: ' + CAST(@@TRANCOUNT AS VARCHAR); COMMIT TRANSACTION OuterTran; -- Deadlock handling example SET DEADLOCK_PRIORITY LOW; -- This transaction will be the deadlock victim BEGIN TRANSACTION; UPDATE Accounts SET balance = balance - 50 WHERE account_id = 101; WAITFOR DELAY '00:00:05'; -- Simulate delay UPDATE Accounts SET balance = balance + 50 WHERE account_id = 202; COMMIT TRANSACTION; -- Implicit vs explicit transactions SET IMPLICIT_TRANSACTIONS ON; -- Statements automatically begin transaction INSERT INTO TestTable VALUES (1); COMMIT; -- Must explicitly commit SET IMPLICIT_TRANSACTIONS OFF; -- Default INSERT INTO TestTable VALUES (2); -- Auto-committed -- Transaction with error handling using XACT_STATE BEGIN TRANSACTION; BEGIN TRY -- Operations here COMMIT TRANSACTION; END TRY BEGIN CATCH IF XACT_STATE() = -1 -- Uncommittable transaction ROLLBACK TRANSACTION; ELSE IF XACT_STATE() = 1 -- Committable transaction COMMIT TRANSACTION; -- Or ROLLBACK based on business logic END CATCH; -- Distributed transactions (two-phase commit) BEGIN DISTRIBUTED TRANSACTION; -- Update local database UPDATE LocalDB..Accounts SET balance = balance - 100 WHERE account_id = 101; -- Update remote database UPDATE RemoteServer.RemoteDB.dbo.Accounts SET balance = balance + 100 WHERE account_id = 202; COMMIT TRANSACTION;

-- Window functions SELECT employee_id, first_name, last_name, salary, department_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank, RANK() OVER (ORDER BY salary DESC) AS company_rank, LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary, LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary, SUM(salary) OVER (PARTITION BY department_id) AS dept_total, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_dept_avg FROM Employees; -- Common Table Expressions (CTEs) WITH RegionalSales AS ( SELECT r.region_name, YEAR(o.order_date) AS order_year, SUM(o.total_amount) AS total_sales FROM Orders o JOIN Customers c ON o.customer_id = c.customer_id JOIN Regions r ON c.region_id = r.region_id GROUP BY r.region_name, YEAR(o.order_date) ), YearlySummary AS ( SELECT order_year, SUM(total_sales) AS yearly_total, AVG(total_sales) AS yearly_avg FROM RegionalSales GROUP BY order_year ) SELECT rs.region_name, rs.order_year, rs.total_sales, ys.yearly_total, ROUND((rs.total_sales * 100.0 / ys.yearly_total), 2) AS percent_of_total FROM RegionalSales rs JOIN YearlySummary ys ON rs.order_year = ys.order_year ORDER BY rs.order_year DESC, rs.total_sales DESC; -- Recursive CTE (organizational hierarchy) WITH EmployeeHierarchy AS ( -- Anchor member (top-level managers) SELECT employee_id, first_name, last_name, manager_id, 1 AS level, CAST(first_name + ' ' + last_name AS VARCHAR(500)) AS hierarchy_path FROM Employees WHERE manager_id IS NULL UNION ALL -- Recursive member SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.level + 1, CAST(eh.hierarchy_path + ' -> ' + e.first_name + ' ' + e.last_name AS VARCHAR(500)) FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id ) SELECT employee_id, first_name, last_name, level, hierarchy_path FROM EmployeeHierarchy ORDER BY hierarchy_path; -- PIVOT (rows to columns) SELECT * FROM ( SELECT YEAR(order_date) AS order_year, DATENAME(month, order_date) AS order_month, total_amount FROM Orders ) AS SourceTable PIVOT ( SUM(total_amount) FOR order_month IN ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December]) ) AS PivotTable ORDER BY order_year; -- JSON functions (modern SQL) SELECT customer_id, customer_name, JSON_QUERY('{"email": "' + email + '", "phone": "' + phone + '"}') AS contact_info, JSON_VALUE(preferences, '$.newsletter') AS wants_newsletter, JSON_MODIFY(preferences, '$.theme', 'dark') AS updated_prefs FROM Customers WHERE ISJSON(preferences) = 1; -- Temporal tables (point-in-time querying) -- Creating a temporal table CREATE TABLE EmployeeHistory ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), salary DECIMAL(10,2), valid_from DATETIME2 GENERATED ALWAYS AS ROW START, valid_to DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (valid_from, valid_to) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistoryArchive)); -- Querying historical data SELECT * FROM EmployeeHistory FOR SYSTEM_TIME AS OF '2024-06-01 10:00:00' WHERE employee_id = 1001; -- Dynamic SQL DECLARE @TableName NVARCHAR(128) = 'Employees'; DECLARE @ColumnName NVARCHAR(128) = 'salary'; DECLARE @Threshold DECIMAL(10,2) = 50000; DECLARE @SQL NVARCHAR(MAX); SET @SQL = N' SELECT COUNT(*) AS high_earners FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' > @Thresh'; EXEC sp_executesql @SQL, N'@Thresh DECIMAL(10,2)', @Thresh = @Threshold; -- Full-text search example SELECT product_id, product_name, product_description FROM Products WHERE CONTAINS(product_description, '"data analysis" NEAR "machine learning"'); -- Geospatial queries SELECT store_id, store_name, location.STDistance(@userLocation) AS distance_meters FROM Stores WHERE location.STDistance(@userLocation) < 5000 -- Within 5km ORDER BY distance_meters; -- MERGE statement (UPSERT) MERGE INTO Customers AS target USING (VALUES (1001, 'John', 'Doe', 'john@email.com'), (1002, 'Jane', 'Smith', 'jane@email.com') ) AS source (customer_id, first_name, last_name, email) ON target.customer_id = source.customer_id WHEN MATCHED THEN UPDATE SET target.first_name = source.first_name, target.last_name = source.last_name, target.email = source.email, target.updated_at = GETDATE() WHEN NOT MATCHED THEN INSERT (customer_id, first_name, last_name, email, created_at) VALUES (source.customer_id, source.first_name, source.last_name, source.email, GETDATE());