A Common Table Expression (CTE) in SQL is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.It is defined using the WITH ... AS syntax. Think of a CTE like a temporary view that exists only for the duration of the query. ✅ Basic Syntax WITH…
🔹 Stored Procedure A Stored Procedure is a collection of SQL statements that you save in the database and can execute multiple times.They can include logic (IF, loops, etc.), accept input/output parameters, and perform actions like inserting, updating, or deleting data. ✅ Syntax: CREATE PROCEDURE procedure_name (parameters) AS BEGIN -- SQL statements END; CREATE…
🔹 Triggers in SQL A trigger is a stored procedure that automatically executes (fires) in response to certain events on a table (like INSERT, UPDATE, or DELETE). Example: Trigger for Audit Logging Suppose you have a table employees and you want to log changes whenever an employee’s salary is updated. -- Create employees table…
In SQL, an index is a database object that improves the speed of data retrieval (SELECT queries) from a table. Think of an index like the index of a book: instead of reading the whole book to find a topic, you can go directly to the indexed page. Similarly, indexes help the database quickly…
🔹 What is a CASE Statement? A CASE statement lets you apply conditional logic in SQL queries — kind of like an if-else in programming languages. Basic Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultN END CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultN END…
SQL supports combining results from two or more queries (with the same number of columns and compatible data types) using set operators. The main ones are: 1. UNION Combines the results of two queries. Removes duplicates by default. Columns in both queries must have the same number and compatible data types. SELECT city FROM…
🔹 String Functions in SQL These are used to manipulate text values (CHAR, VARCHAR, etc.). 1. UPPER() – Convert to uppercase SELECT UPPER('hello world') AS UpperCase; -- Output: HELLO WORLD SELECT UPPER('hello world') AS UpperCase; -- Output: HELLO WORLD 2. LOWER() – Convert to lowercase SELECT LOWER('HELLO SQL') AS LowerCase; -- Output: hello sql…
🔑 Primary Key A Primary Key is a column (or set of columns) that uniquely identifies each row in a table. Rules: Must be unique. Cannot be NULL. A table can have only one primary key (but it can consist of multiple columns = composite key). Example: CREATE TABLE Students ( StudentID INT PRIMARY…
🔹 What is a Subquery? A subquery is a query inside another SQL query.It is often used in the WHERE, FROM, or SELECT clause to provide intermediate results. 🔹 Types of Subqueries Single-row subquery → returns only one value Multi-row subquery → returns multiple values Nested subquery in SELECT or FROM → used as…
We’ll use two simple tables as reference: Table: Employees EmpIDNameDeptID1Alice102Bob203Charlie304DavidNULL Table: Departments DeptIDDeptName10HR20IT30Finance40Marketing 1. INNER JOIN Returns only the matching rows between two tables. SELECT e.Name, d.DeptName FROM Employees e INNER JOIN Departments d ON e.DeptID = d.DeptID; SELECT e.Name, d.DeptName FROM Employees e INNER JOIN Departments d ON e.DeptID = d.DeptID; Result: NameDeptNameAliceHRBobITCharlieFinance…