Intermediate Flashcards
What are GRANT and REVOKE commands used for?
These are data control language statements that are used by DBAs and users with proper priviledges to give or remove access to actions such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALL on database objects like tables, views and stored procedures.
GRANT SELECT, INSERT ON EMPLOYEES TO USER100
GRANT SELECT ON EMPLOYEES TO USER100 WITH GRANT OPTION
REVOKE INSERT ON EMPLOYEES FROM USER100
Best Practices:
* Use least privilege principle — only give users what they need.
* Grant users access to roles, over individuals for scalability. Ex. GRANT db_datareader to User100, where the role db_datareader is assigned priviledges
What is a Transaction in SQL?
A transaction is a set of SQL statements that are executed as a single unit of work. Either all suceed or none.
- BEGIN TRANSACTION –Starts a new explicit transaction.
- COMMIT TRANSACTION – Permanently applies all the changes made since BEGIN TRANSACTION.
- ROLLBACK TRANSACTION - Reverts all changes made in the transaction
- SAVE TRANSACTION savepoint1 - Allows partial rollback upto the save point. If there are multiple steps and code needs finer control, use SAVEPOINTs to roll back to a known good state.
Tips:
Wrap critical operations in TRY…CATCH blocks to handle errors gracefully.
Use @@TRANSCOUNT to keep track of nested transactions.
What is the difference between inner join, left outer join and full join?
- An INNER JOIN returns only the matching rows from both tables.
- A LEFT JOIN returns all rows from the left table, even if there’s no match on the right.
- A FULL JOIN returns all rows from both tables — showing NULLs where there’s no match.
Pick the type depending on whether I want to preserve unmatched data from one or both sides.
Can you give an example of inner join?
Customers
CustomerID Name
1 Alice
2 Bob
3 Charlie
🧾 Orders
OrderID CustomerID Product
101 1 Laptop
102 2 Keyboard
103 4 Mouse
1️⃣ INNER JOIN
Returns only matching rows from both tables.
🔸 Query:
sql
Copy
Edit
SELECT c.Name, o.Product
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
✅ Output:
Name Product
Alice Laptop
Bob Keyboard
❗ Charlie has no order → excluded
❗ Order with CustomerID = 4 → excluded
Can you give an example of left outer join?
Customers
CustomerID Name
1 Alice
2 Bob
3 Charlie
🧾 Orders
OrderID CustomerID Product
101 1 Laptop
102 2 Keyboard
103 4 Mouse
Returns all rows from the left table (Customers), with matches from right table if any; fills NULL if no match.
🔸 Query:
sql
Copy
Edit
SELECT c.Name, o.Product
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
✅ Output:
Name Product
Alice Laptop
Bob Keyboard
Charlie NULL
❗ Charlie is included even without a matching order
Can you give an example of full outer join?
Customers
CustomerID Name
1 Alice
2 Bob
3 Charlie
🧾 Orders
OrderID CustomerID Product
101 1 Laptop
102 2 Keyboard
103 4 Mouse
Returns all rows from both tables. Where no match exists, fills NULL.
🔸 Query:
sql
Copy
Edit
SELECT c.Name, o.Product
FROM Customers c
FULL OUTER JOIN Orders o ON c.CustomerID = o.CustomerID;
✅ Output:
Name Product
Alice Laptop
Bob Keyboard
Charlie NULL
NULL Mouse
❗ Charlie has no order
❗ The order with CustomerID = 4 has no customer
What are other types of joins?
Besides the common INNER, LEFT, and FULL joins, there is the RIGHT JOIN, though one often uses LEFT JOIN by flipping table order. CROSS JOINs are used for generating combinations, SELF JOINs for comparing data within the same table. Each join type is useful depending on the scenario.
- INNER JOIN Returns only matching rows from both tables
- LEFT JOIN Returns all rows from the left table, and matches from the right (if any)
- RIGHT JOIN Returns all rows from the right table, and matches from the left (if any)
- FULL JOIN Returns all rows from both tables, with NULLs where no match exists
- CROSS JOIN Returns the Cartesian product — every row from table A paired with every row from table B.
SELECT c.Name, p.Product
FROM Customers c
CROSS JOIN Products p;
If Customers has 3 rows and Products has 4, result = 3 × 4 = 12 rows.
Used in cases like generating all possible combinations (e.g. sizes × colors).
* SELF JOIN A table joined with itself. Useful for hierarchical data or comparing rows. Helps answer questions like “Who reports to whom?” or “Find duplicate rows.”
Can you explain self joins with an example?
A self join is when a table is joined to itself to compare rows within the same table. Use aliases to distinguish two copies of the same table.
It helps answer questions like “Who reports to whom?” or “Find duplicate rows.”
Example Table: Employees
EmployeeID Name ManagerID
1 Alice NULL
2 Bob 1
3 Charlie 1
4 Divya 2
If the goal is to get each employee and their manager’s name.
SELECT
E.Name AS Employee,
M.Name AS Manager
FROM
Employees E
LEFT JOIN
Employees M ON E.ManagerID = M.EmployeeID;
OUTPUT:
Employee Manager
Alice NULL
Bob Alice
Charlie Alice
Divya Bob
When to Use SELF JOIN:
1. Org charts (employees → managers)
2. Finding duplicate records in a table
3. Comparing rows within the same table (e.g., customers in same city)
Can you explain cross join with an example?
A cross join combines every row from Table A with every row from Table B. It does not require a join condition.
Example Tables
👕 Shirts
ShirtColor
Red
Blue
📏 Sizes
Size
S
M
L
If the goal is to generate every color-size combination.
SELECT
s.ShirtColor, sz.Size
FROM
Shirts s
CROSS JOIN
Sizes sz;
Output:
ShirtColor Size
Red S
Red M
Red L
Blue S
Blue M
Blue L
2 × 3 = 6 rows
When to Use CROSS JOIN:
* Generating all possible combinations (colors × sizes, months × regions)
* Testing or simulation: CROSS JOIN is for creating every combo — useful when I want to simulate or test how something behaves in different what-if conditions.
I want to test how a product performs under different conditions — so I create all combinations of the product and those conditions.”
✅ Example: Product Performance Testing
Say you have:
💻 Devices
DeviceName
Laptop
Tablet
🔋 BatteryLevels
BatteryLevel
100%
50%
10%
You want to simulate or test how each device performs at every battery level.
CROSS JOIN:
sql
Copy
Edit
SELECT
d.DeviceName, b.BatteryLevel
FROM
Devices d
CROSS JOIN
BatteryLevels b;
🧾 Output (All test cases generated):
DeviceName BatteryLevel
Laptop 100%
Laptop 50%
Laptop 10%
Tablet 100%
Tablet 50%
Tablet 10%
What are sub-queries and what are the different types of sub-queries?
A subquery is a query inside another query.
There are three different types of sub-queries:
1. Scalar subquery: Returns a single value (one row, one column). Often used in SELECT or WHERE. Ex. Finds employees who earn above average salary.
SELECT Name
FROM Employees
WHERE Salary > (
SELECT AVG(Salary) FROM Employees
);
2. Row / Column subquery: Returns multiple values — used with IN, EXISTS, etc. Ex. Finds employees in Seattle-based departments.
SELECT Name
FROM Employees
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Departments
WHERE Location = ‘Seattle’
);
3. Correlated subquery: Subquery depends on the outer query — runs for each row in the outer query. Ex. For each employee, compares their salary to the average salary of their own department.
SELECT e.Name
FROM Employees e
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = e.DepartmentID
);
Tips:
* Use subqueries when you need to filter or compare using results from another query.
- Scalar subqueries return a single value, and correlated subqueries are run row-by-row with the outer query. If performance matters, consider replacing complex subqueries with joins or CTEs for better optimization and readability.
- Convert subqueries to joins when you want better performance or readability — especially if the subquery returns many values or needs to be matched row-by-row.
When to use subqueries vs joins?
Use subqueries when you need to filter records based on a single calculated value, and when you need to check for existence or compare values.
Use a JOIN when matching records between related tables, when you need results from both tables and when you need better performance on large datasets.
When to use EXISTS vs IN commands?
Use EXISTS when you have a correlated subquery or expect a large result set.
* EXISTS is a boolean check—it just needs to find a match and returns TRUE or FALSE, and it doesn’t care about NULLs.
* EXISTS is often used with correlated subqueries (referring to the outer query), allowing filtering based on complex conditions that are not easily expressed using IN.
Use IN when you have a small static list or know the subquery won’t return NULLs.
* IN checks for equality, and if the subquery returns a NULL, it can lead to unexpected results or eliminate rows you’d expect to match.
Can inline views or derived tables be used in the FROM clause?
Yes, when you need results from a “virtual table.”
Ex. SELECT d.DepartmentID, d.TotalSalary
FROM (
SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID
) d
WHERE d.TotalSalary > 500000;
OLAP vs OLTP. What’s the difference?
- They are each two different types of database systems, optimized for different use cases - OLTP (Transactional databases) for real time transactions and OLAP (Analytical databases) for complex analytical queries, aggregations and reporting.
- OLTP is optimized for day to day operations, whereas OLAP is optimized for long-term insights.
- Examples of OLAP databases: Synapse, Snowflake, Redshift
Examples of OLTP databases: SQL Server, PostGreSQL
