SQL Practice

VIEW

A virtual table in SQL that is based on the result of a SELECT query. It does not store data itself but provides a way to save and reuse complex queries. A view displays data from one or more tables and can be queried like a regular table. Views simplify querying and can help secure data by restricting access to specific rows or columns without exposing the full underlying table.

SUM

An SQL aggregate function that calculates the total sum of a numeric column. It adds up all the values in a specified column for the rows that meet the query's conditions. SELECT SUM(TotalAmount) FROM Orders;

COUNT

An SQL aggregate function that returns the number of rows in a table or the number of non-NULL values in a specific column. SELECT COUNT(Column1) AS CountColumn1, COUNT(Column2) AS CountColumn2 FROM YourTable;

RANK()

1. RANK() Purpose: Assigns a rank to each row within a result set, with the same rank given to rows with identical values (ties). However, it skips ranks after ties. Usage: Ideal when you want to account for ties but still need the next rank to reflect the position as if there were no ties. SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

ROW_NUMBER()

3. ROW_NUMBER() Purpose: Assigns a unique, sequential row number to each row, without considering ties. Even if two rows have the same values, they will receive different row numbers. Usage: Use when you need to assign a unique identifier to each row and don't care about ties. Often used for pagination or filtering out duplicates.

PRIMARY KEY

A Primary Key is a column or set of columns that uniquely identifies each row in a table. It must contain unique values and cannot have NULLs. Each table can have only one primary key.

SQL Procedure

A stored program in a database that contains a set of SQL statements to be executed. It can accept input parameters, perform operations like INSERT, UPDATE, DELETE, or SELECT, and return values. Procedures improve performance by reusing code and can be called multiple times.

TABLE

A structured object in a database that stores data in rows and columns. Each column represents a specific field (or attribute), and each row represents a record (or entry). Tables hold actual data and are a fundamental building block in relational databases. CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, Name VARCHAR(100), Age INT, City VARCHAR(50) );

Temp Table

A temporary table in SQL used to store intermediate results or data for a session. It exists only for the duration of the session or query and is automatically deleted when the session ends. Temp tables are useful for breaking down complex queries or storing data that needs to be reused within a query. Denoted by a # symbol at the beginning of the table name (ex. #temp_table)

LEFT JOIN

A type of SQL join that returns all records from the left table (the first table), and the matched records from the right table (the second table). If no match is found in the right table, the result will include NULL values for columns from the right table.

RIGHT JOIN

A type of SQL join that returns all records from the right table (the second table) and the matched records from the left table (the first table). If no match is found in the left table, the result will include NULL values for columns from the left table.

OUTER JOIN

A type of SQL join that returns all rows from one or both tables, including those that do not have a match in the other table. Unmatched rows will show NULL values for columns where no match exists.

INNER JOIN

A type of SQL join that returns only the rows where there is a match in both tables. If there is no matching record in either the left or right table, the row is not included in the result.

_ (UNDERSCORE)

A wildcard character used in SQL with the LIKE operator to represent a single character. It is useful when you want to match strings of a specific length or pattern. SELECT * FROM Customers WHERE Name LIKE 'J_n'; This query will return all customer names where the name starts with 'J' and ends with 'n', with exactly one character in between (e.g., "Jon", "Jen"). SELECT * FROM Customers WHERE Name LIKE '_a_'; This query will return all customer names where the second letter is 'a' and there are exactly two more characters (e.g., "Sam", "Max"). Summary: _ represents exactly one character. You can combine multiple underscores to specify a pattern with multiple characters at specific positions. The underscore is especially helpful when you want to enforce exact character positions while still allowing some variation.

%

A wildcard character used in SQL with the LIKE operator to represent zero or more characters. It is used when searching for patterns in strings. SELECT * FROM Customers WHERE Name LIKE 'S%'; **This query will return all customer names that start with 'S', regardless of what comes after SELECT * FROM Customers WHERE Name LIKE '%y'; This query will return all customer names that end with 'y' SELECT * FROM Customers WHERE Name LIKE '%ar%'; This query will return all customer names that contain 'ar' anywhere in the string The % symbol allows for flexible matching of any number of characters before, after, or between specified characters.

AVG

An SQL aggregate function that calculates the average value of a numeric column. It sums up all the values in the specified column and divides by the number of rows that meet the query's conditions. SELECT AVG(TotalAmount) FROM Orders;

GROUP BY

An SQL clause used to group rows that have the same values in specified columns. It is commonly used with aggregate functions like SUM(), COUNT(), AVG(), etc., to perform calculations on each group of rows. SELECT City, Age, COUNT(*) AS NumCustomers FROM Customers GROUP BY City, Age;

LIKE

An SQL operator used to search for a specified pattern in a column, often with wildcard characters. The most commonly used wildcards are: %: Represents zero or more characters. _: Represents a single character.

TRUNCATE vs. DELETE

DELETE: Row-by-row deletion: Removes rows one at a time based on a condition (or all rows if no condition is given). Can use WHERE clause: You can selectively delete specific rows by adding a condition. Logging: Every row deletion is logged, making it slower, but allowing for rollback in transactions and ensuring traceability. Triggers: DELETE operations can fire triggers (such as actions for auditing, logging, or archiving data). Performance: Slower for large datasets due to row-by-row logging and trigger execution. TRUNCATE: Fast deletion: Removes all rows in a table without logging individual row deletions. No WHERE clause: It deletes all rows, so you cannot specify conditions. No triggers: It bypasses triggers, making it faster but potentially less safe for certain workflows. Performance: Significantly faster, especially on large tables, because it deallocates the data pages instead of deleting rows one by one. Not always transaction-safe: While in some systems TRUNCATE can be rolled back (e.g., in SQL Server), in others it may not be.

DENSE_RANK()

Purpose: Like RANK(), but without skipping ranks after ties. Rows with identical values (ties) get the same rank, and the next rank continues sequentially. Usage: Use this when you need to rank tied values and still maintain sequential ranking without gaps. SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank FROMemployees;

RANK() vs. DENSE_RANK() VERSUS ROW_NUMBER()

RANK(): Use when you want to account for ties and need to reflect skipped ranks. DENSE_RANK(): Use when you want to account for ties but maintain continuous ranks. ROW_NUMBER(): Use when you want unique identifiers for each row and don't care about ties.

CASE

The CASE command in SQL allows you to create conditional logic within a query. It works like an IF-THEN statement and can be used to return different values based on specified conditions. SELECT employee_name, salary, CASE WHEN salary > 50000 THEN 'High Salary' WHEN salary BETWEEN 30000AND 50000 THEN 'Medium Salary' ELSE 'Low Salary' END AS salary_level FROM employees;

HAVING vs. WHERE

The HAVING clause is tested after the GROUP BY. You can test the aggregated values with a HAVING clause. Show the total population of those continents with a total population of at least half a billion.

PARTITION BY

The PARTITION BY clause is used in SQL to divide the result set into partitions, or subsets, of data. It is typically used with window functions like ROW_NUMBER(), RANK(), and SUM(). Each window function operates independently within each partition, much like how GROUP BY works but without reducing the number of rows.