Post #1507

• INNER JOIN: Returns rows that have matching values in both tables. SELECT e.name, e.salary, d.department_name FROM employees e INNER JOIN departments d ON e.department = d.department_id; • LEFT JOIN: Returns all rows from the left table and matched rows from the right table. If no match, returns NULL. SELECT e.name, e.salary, d.department_name FROM employees e LEFT JOIN departments d ON e.department = d.department_id; • RIGHT JOIN: Returns all rows from the right table and matched rows from the left table. If no match, returns NULL. SELECT e.name, e.salary, d.department_name FROM employees e RIGHT JOIN departments d ON e.department = d.department_id; • FULL OUTER JOIN: Returns all rows when there is a match in one of the tables. SELECT e.name, e.salary, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department = d.department_id; 6. Subqueries and Nested Queries Subqueries are queries embedded inside other queries. They can be used in the SELECT, FROM, and WHERE clauses. Correlated Subqueries A correlated subquery references columns from the outer query. -- Find employees with salaries above the average salary of their department SELECT name, salary FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department = e2.department); Using Subqueries in SELECT You can also use subqueries in the SELECT statement: SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees; 7. Advanced SQL Window Functions Window functions perform calculations across a set of table rows related to the current row. They do not collapse rows like GROUP BY. -- Rank employees by salary within each department SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees; Common Table Expressions (CTEs) A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. -- Calculate department-wise average salary using a CTE WITH avg_salary_cte AS ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ) SELECT e.name, e.salary, a.avg_salary FROM employees e JOIN avg_salary_cte a ON e.department = a.department; 8. Data Transformation and Cleaning CASE Statements The CASE statement allows you to perform conditional logic within SQL queries. -- Categorize employees based on salary SELECT name, CASE WHEN salary < 50000 THEN 'Low' WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium' ELSE 'High' END AS salary_category FROM employees; String Functions SQL offers several functions to manipulate strings: -- Concatenate first and last names SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; -- Trim extra spaces from a string SELECT TRIM(name) FROM employees; Date and Time Functions SQL allows you to work with date and time values: -- Calculate tenure in days SELECT name, DATEDIFF(CURDATE(), hire_date) AS days_tenure FROM employees; 9. Database Management Indexing Indexes improve query performance by allowing faster retrieval of rows. -- Create an index on the department column for faster lookups CREATE INDEX idx_department ON employees(department); Views A view is a virtual table based on the result of a query. It simplifies complex queries by allowing you to reuse the logic. -- Create a view for high-salary employees CREATE VIEW high_salary_employees AS SELECT name, salary FROM employees WHERE salary > 100000; -- Query the view SELECT * FROM high_salary_employees; Transactions A transaction ensures that a series of SQL operations are completed successfully. If any part fails, the entire transaction can be rolled back to maintain data integrity. -- -- Transaction example START TRANSACTION; UPDATE employees SET salary = salary + 5000 WHERE department = 'HR'; DELETE FROM employees WHERE id = 10; COMMIT; -- Commit the transaction if all Best SQL Interview Resources