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

SQLZOO

Mode Analytics