SQL practice note

Important Concepts

Nested Queries
- Nested Query Results Expressions
  - ALL: Must satisfy expression for all rows in subquery
  - ANY: Must satisfy expression for at least one row in subquery
  - IN: Equivalent to "=ANY()"
  - EXISTS: At lease one row is returned
Window Functions
- Perform a calculation across a set of tuples that related to a single row
- SELECT ... FUNC-NAME(...) OVER(...) FROM TableName
- Functions:
  - Aggregate Functions
  - Special Windown Functions:
    - ROW_NUMBER()
    - RANK()
- Grouping: The OVER clause specifies how to group together tuples when computing the window function Use PARTITION BY to specify group
Common Table Expressions (CTE)
- Think of it like a temp table for one query
- Alternative to nested queries and views
- Bind output columns to names before the AS keyword
- A single query can contain multiple CTE declarations

Practice

Leetcode

184. Department Highest Salary
185. Department Top Three Salaries
262. Trips and Users
595. Big Countries
569. Median Employee Salary
- without built-in SQL functions?
601. Human Traffic of Stadium

Common Interview Questions

What are the different types of SQL commands? Explain the DDL/DCL commands in SQL?
What is the difference between Cluster and Non-Cluster Index?
the use of UNION and UNION ALL in SQL?
What is the difference between DELETE and TRUNCATE?
What is a join? types of Join?
How to test for NULL values?
What is Normalization? What are all the different Normalization?

Resources