coding-interview-university
A complete computer science study plan to become a software engineer.
- 365k
- CC-BY-SA-4.0
- 2 wk ago
๐ฃ SQL interview questions and answers to help you prepare for your next machine learning and data science interview in 2026.
Repo Devinterview-io/sql-interview-questions, read from GitHub 1 min ago. Devinterview-io has not joined Take My Repo.
Devinterview-io/sql-interview-questions is a public repo on GitHub by Devinterview-io. ๐ฃ SQL interview questions and answers to help you prepare for your next machine learning and data science interview in 2026. It declares no license on GitHub. Public does not mean open source: without a license, nobody is granted the right to reuse it. It has 436 stars and last changed on 2026-03-13. Nobody has reviewed it on Take My Repo yet, which says nothing about its quality. Read from GitHub on 2026-11-22.
In the maintainer's words. Shown from GitHub; Take My Repo does not keep a copy.
SQL is the standardized domain-specific, declarative language for managing and querying Relational Database Management Systems (RDBMS). Beyond legacy relational models, modern SQL dialects (e.g., PostgreSQL 18, DuckDB 1.2) now incorporate support for semi-structured data (JSONB) and vector embeddings for RAG (Retrieval-Augmented Generation) workflows.
CREATE, ALTER, DROP).INSERT, UPDATE, DELETE, MERGE).GRANT, REVOKE).COMMIT, ROLLBACK, SAVEPOINT).SELECT).pgvector) utilize SQL extensions to perform $k$-nearest neighbor ($k$-NN) searches on high-dimensional vectors, enabling AI-driven semantic similarity queries.OVER, PARTITION BY).FROM clause, critical for complex analytical transformations.The following example demonstrates modern syntax, including standard CTEs and relational constraints.
-- Schema Definition CREATE TABLE Department ( dept_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, dept_name VARCHAR(100) NOT NULL ); CREATE TABLE Employee ( emp_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, emp_name VARCHAR(255) NOT NULL, dept_id INT REFERENCES Department(dept_id) ); -- Recursive/Complex Query using CTE WITH DepartmentStats AS ( SELECT dept_id, COUNT(*) as emp_count FROM Employee GROUP BY dept_id ) SELECT e.emp_name, d.dept_name, ds.emp_count FROM Employee e JOIN Department d ON e.dept_id = d.dept_id JOIN DepartmentStats ds ON d.dept_id = ds.dept_id WHERE ds.emp_count > 0;
EXPLAIN ANALYZE to inspect the query execution tree and identify high-cost nodes.The dichotomy between Relational Database Management Systems (RDBMS/SQL) and Non-Relational (NoSQL) systems has matured into a hybrid landscape. While fundamental constraints remain, modern distributed systems have blurred the lines via multi-model support.
| Feature | SQL (Modernized) | NoSQL |
|---|---|---|
| Schema | Schema-on-write | Schema-on-read |
| Integrity | Enforcement via constraints/FKs | Application-layer validation |
| Joins | Efficient via relational algebra | Often discouraged/denormalized |
| Scaling | Vertical/Distributed SQL (Horizontal) | Native Horizontal (Sharding) |
| Querying | Declarative (SQL Standard) | Proprietary API / GraphQL / SQL-like |
Select SQL when data integrity, complex transactional reporting, and strict schema enforcement are paramount. Select NoSQL for high-velocity streaming data, sparse datasets (e.g., IoT logs), or when the schema must evolve iteratively without database-level downtime. Consider Distributed SQL if the project requires the familiarity of SQL with the elastic scalability of NoSQL.
Modern SQL classification includes a fifth critical category: Transaction Control Language (TCL), which handles atomic state transitions. DQL is now standardly recognized as a subset of DML in formal SQL:2023/2025 specifications, though categorized separately for pedagogical clarity.
Focused on data retrieval. Modern analytical engines prioritize set-based operations and window functions.
OVER(), PARTITION BY) perform calculations across related rows.Manages schema evolution and metadata persistence.
ADD COLUMN, RENAME TO, SET DATA TYPE).Handles row-level operations. 2026 standards emphasize "Upsert" patterns and atomic modifications.
ON CONFLICT (PostgreSQL) or MERGE (SQL Standard) for idempotent data ingestion.Governs the state of transactions to ensure ACID properties.
Governs security and governance via RBAC (Role-Based Access Control).
SELECT, INSERT, EXECUTE) to users/roles.The SELECT statement is the primary Data Query Language (DQL) construct in SQL, designed for retrieving, projecting, and transforming data from relational structures. In modern architectures, it serves as the interface between storage engines and application layers, supporting complex analytical processing and vector-relational hybrid queries.
The SELECT statement is evaluated by the database engine in a specific logical sequence, distinct from the written syntax order:
Modern SQL engines (e.g., PostgreSQL 18, DuckDB 1.2) extend traditional SELECT capabilities to support JSONB traversal, Vector embeddings (via extensions like pgvector), and Window Functions.
<=> for Cosine distance) to query high-dimensional embeddings:
SELECT content FROM docs ORDER BY embedding <=> '[...]' LIMIT 5;
WITH clause is now standard for improving query readability and recursion over subqueries.OVER (PARTITION BY ...) for performant ranking and running totals at $O(n \log n)$ complexity.CASE statements or COALESCE for business logic injection.JSON_AGG) directly for REST or GraphQL API consumers.Using modern syntax, including standard JOIN formatting and a CTE for improved readability:
WITH OrderSummary AS ( SELECT o.OrderID, o.OrderDate, c.CustomerName, e.LastName AS SalesRep, SUM(od.UnitPrice * od.Quantity) AS TotalValue FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID JOIN Employees e ON o.EmployeeID = e.EmployeeID JOIN OrderDetails od ON o.OrderID = od.OrderID GROUP BY 1, 2, 3, 4 ) SELECT * FROM OrderSummary WHERE TotalValue > 1000 ORDER BY OrderDate DESC LIMIT 100;
The execution cost of a SELECT query typically scales $O(n)$ with full table scans or $O(\log n)$ when utilizing B-Tree or GIN indices. In 2026, developers are expected to analyze query plans using EXPLAIN ANALYZE to ensure that join strategies (Nested Loop vs. Hash Join) are optimized for the data distribution.
The fundamental difference lies in the logical order of operations within the SQL execution pipeline. While both clauses filter rows, they function at distinct stages of the query lifecycle.
The WHERE clause acts as a pre-aggregation filter. It evaluates conditions against individual rows before any GROUP BY operation occurs.
FROM/JOIN phase.WHERE clause allows for $O(\log n)$ lookup performance via B-tree index traversal.SUM(), AVG()) because the aggregation state has not yet been computed.The HAVING clause serves as a post-aggregation filter. It evaluates conditions against the resulting sets generated by a GROUP BY clause.
GROUP BY phase and before the SELECT projection.HAVING COUNT(*) > 100).The logical processing order in modern RDBMS (PostgreSQL 17+, SQL Server 2026) follows this sequence:
FROM / JOINWHERE (Row Filtering)GROUP BY (Aggregation)HAVING (Group Filtering)SELECT (Projection)ORDER BY (Sorting)Note: In modern analytical query optimization, if a HAVING clause contains no aggregates, most optimizers will rewrite it to a WHERE clause internally to benefit from indexing, though writing explicit WHERE clauses remains the standard for query readability and intent.
A JOIN clause in SQL is a relational operator used to combine rows from two or more tables based on a join predicate. Conceptually, JOINs implement the relational algebra Join operation. While foreign keys define referential integrity, joins operate on any expression satisfying the join predicate.
Given two tables $T_1$ and $T_2$ with $n$ and $m$ rows, the time complexity for a standard Nested Loop Join is $O(n \times m)$. Modern query optimizers prefer Hash Joins or Sort-Merge Joins, which operate in $O(n+m)$ average time, depending on index availability and memory allocation for hash buffers.
TRUE.NULL.The predicate evaluates equality or inequality. Use INNER keyword explicitly for semantic clarity.
SELECT T1.A, T1.B, T2.C FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.B = T2.B;
When using FULL JOIN, use COALESCE() or IFNULL() to normalize result sets if the join keys differ.
SELECT COALESCE(T1.B, T2.B) AS B, T1.A, T2.C FROM Table1 AS T1 FULL JOIN Table2 AS T2 ON T1.B = T2.B;
Self joins are frequently used in Recursive Common Table Expressions (CTEs) for recursive hierarchy traversals (e.g., organizational charts).
Modern Pattern (Recursive CTE):
WITH RECURSIVE Hierarchy AS ( SELECT EmpID, Name, ManagerID FROM Employee WHERE ManagerID IS NULL UNION ALL SELECT E.EmpID, E.Name, E.ManagerID FROM Employee E INNER JOIN Hierarchy H ON E.ManagerID = H.EmpID ) SELECT * FROM Hierarchy;
SELECT * in joins. Explicitly list columns to reduce I/O overhead and minimize the risk of schema drift.SELECT E.Name AS Employee, M.Name AS Manager FROM Employee E LEFT JOIN Employee M ON E.ManagerID = M.EmpID;
ManagerID column of the primary alias and the EmpID of the secondary alias.A Primary Key is a constraint that uniquely identifies each tuple (record) within a relation (table). It serves as the immutable anchor for Entity Integrity.
UNIQUE constraint, ensuring no two rows share the same key value.NOT NULL. Absence of value is logically incompatible with identification.BIGINT or UUIDv7) minimizes index fragmentation and I/O latency.student_email may change, breaking references. Use synthetic identifiers (e.g., BIGINT IDENTITY or UUIDv7).UUIDv4 to UUIDv7. The monotonic timestamp prefix ensures index-friendly insertion patterns, mitigating the $O(\log n)$ rebalancing costs of random inserts.Using BIGINT with an identity sequence for high-performance single-node databases, or UUID for distributed environments.
-- Standard sequence-based PK (High Performance) CREATE TABLE Students ( student_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, student_uuid UUID DEFAULT gen_random_uuid(), -- For distributed systems grade_level INT NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); -- Optimization: Adding a covering index for lookup on the UUID CREATE UNIQUE INDEX idx_students_uuid ON Students(student_uuid);
A Foreign Key (FK) is a database constraint that enforces referential integrity between two tables. It identifies a column (or set of columns) in a referencing table (the child) that links to the primary or unique key of a referenced table (the parent).
NULL (unless defined as NOT NULL).JOIN operations.ON DELETE and ON UPDATE triggers (e.g., CASCADE, SET NULL, RESTRICT).NULL by default. If the business logic mandates a mandatory relationship, the FK column must be constrained with NOT NULL.JOIN operations and ON DELETE CASCADE checks, explicit B-tree indexes are required on FK columns where query frequency is high.
ON DELETE RESTRICT to prevent the deletion of a parent record if child records exist, ensuring business-critical data is not accidentally purged.-- Parent Table CREATE TABLE departments ( department_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL ); -- Child Table with explicit constraint naming for better error handling CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, department_id INT, CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE RESTRICT ON UPDATE CASCADE ); -- Optimization: Explicit index on the FK column to optimize JOIN performance CREATE INDEX idx_employees_department_id ON employees(department_id);
The original text incorrectly stated that the foreign key itself must be unique. Correction: The foreign key column in the child table does not need to be unique (as in a 'One-to-Many' relationship, many children reference one parent). It is the referenced key in the parent table that must be unique.
SQL Injection (SQLi) occurs when an application improperly neutralizes user-supplied input, allowing an attacker to interfere with query logic. Defense relies on the principle of separation of data from code.
Description: The industry-standard primary defense. The SQL query structure is sent to the database engine first, and user inputs are sent later as bound parameters. The database driver ensures inputs are treated strictly as data, never as executable code, reducing the risk to $O(1)$ complexity regarding query logic manipulation.
psycopg3) Example:# Using modern Pythonic ORM practices from sqlalchemy import text stmt = text("SELECT * FROM users WHERE username = :u AND password = :p") result = session.execute(stmt, {"u": username, "p": password})
Description: Stored procedures isolate data access logic. However, they only prevent SQLi if the procedure body itself uses parameterized logic. Constructing strings dynamically inside a procedure remains vulnerable.
Description: Validate input against a defined schema. In 2026, utilize Pydantic (Python) or Zod (TypeScript/Node.js) for runtime type checking and constraint enforcement.
from pydantic import BaseModel, StringConstraints from typing import Annotated class UserLogin(BaseModel): # Enforce strict regex at the boundary username: Annotated[str, StringConstraints(pattern=r'^[a-zA-Z0-9_]{3,20}$')]
Description: A critical 2026 infrastructure requirement. The database user account assigned to the web application should possess only the minimum permissions necessary.
db_owner or superuser.audit_logs or sys_users).DROP, TRUNCATE, or GRANT operations.Deprecated/Caution: Manual Code Filtering (sanitization/escaping via regex) is now considered an anti-pattern. It is error-prone, difficult to maintain, and often bypassable via encoding tricks (e.g., Unicode normalization).
Normalization is a database design process that minimizes data redundancy and eliminates insertion, update, and deletion anomalies by organizing columns and tables according to functional dependencies.
Storing data in a single flat structure forces denormalization, leading to $O(N \cdot M)$ redundancy where $N$ is customers and $M$ is line items.
| ID | Name | Invoice No. | Item No. | Description | Price |
|---|
Enforces atomicity and uniqueness. We identify the composite key (InvoiceNo, ItemNo).
(ID [PK], Name)(InvoiceNo [PK], Customer_ID [FK], Date)(InvoiceNo [FK], ItemNo [PK], Description, Price)If we stored ItemDescription in the Invoice_Items table, it would depend only on ItemNo, not the full PK (InvoiceNo, ItemNo). To reach 2NF, we move Description to a Products catalog table.
We ensure no non-prime attribute depends on another. If Invoice table included Customer_Address, that would be a transitive dependency (InvoiceNo -> Customer_ID -> Customer_Address). We move address data exclusively to the Customers table.
Using standardized constraints to enforce integrity:
-- Core Entity: Products (Normalization prevents update anomalies) CREATE TABLE Products ( ProductID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, Description TEXT NOT NULL, UnitPrice DECIMAL(12, 2) CHECK (UnitPrice >= 0) ); -- Normalized Relationship Tables CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(255) NOT NULL ); CREATE TABLE Invoices ( InvoiceNo INT PRIMARY KEY, CustomerID INT NOT NULL REFERENCES Customers(CustomerID), InvoiceDate TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE Invoice_Items ( InvoiceNo INT REFERENCES Invoices(InvoiceNo), ProductID INT REFERENCES Products(ProductID), Quantity INT CHECK (Quantity > 0), PRIMARY KEY (InvoiceNo, ProductID) );
Audit Note: The implementation above utilizes TIMESTAMPTZ and GENERATED ALWAYS AS IDENTITY, aligning with modern RDBMS capabilities (PostgreSQL 17+, SQL Server 2025) for superior concurrency and type safety.
Denormalization is the intentional introduction of redundancy into a schema to optimize Read-Latency and reduce computational overhead for complex join operations. In 2026, this remains a critical architectural pattern, particularly when balancing the $O(n \log n)$ cost of joins against the $O(1)$ cost of direct column access.
SUM() or COUNT() calculations to precomputed columns or Materialized Views. Modern RDBMS (e.g., PostgreSQL 18+, SQL Server 2026) support automated concurrent refresh, mitigating traditional staleness issues.Customer_Region) directly in the fact table. This creates a "Star Schema" optimized for OLAP (Online Analytical Processing) workloads.JOIN operations.UPDATE statements across denormalized replicas. Engineers must calculate the Write Amplification Factor (WAF):
$$WAF = \frac{\text{Total writes to storage}}{\text{App-level writes}}$$
If $WAF > \text{threshold}$, normalization should be re-evaluated.Denormalization is a read-optimization strategy, not a design default. In 2026, favor Normalization for transactional integrity, and utilize Materialized Views or Read Replicas for denormalized query performance. Reserve physical denormalization only when profiling indicates that query execution plans are constrained by join depth rather than I/O throughput.
Indexes are auxiliary data structures that decouple physical data storage from logical retrieval patterns. By maintaining a pointer-based reference system, they reduce the search space from $O(n)$ (full table scan) to $O(\log n)$ (tree-based traversal) or $O(1)$ (hash-based lookup).
INCLUDE clause), the engine avoids the "bookmark lookup" penalty, keeping the entire query lifecycle within the index structure.INSERT, UPDATE, or DELETE requires updating the index structure. An excessive index count increases the Write Penalty ($O(k \cdot \log n)$ where $k$ is the number of indexes).REINDEX or VACUUM operations to maintain optimal fill factors.EXPLAIN ANALYZE or EXPLAIN (FORMAT JSON) to audit cost-to-performance ratios.CREATE INDEX ... WHERE active = true) to significantly reduce index size and maintenance overhead.(a, b), the index is only usable for filters on a or (a, b), but not b alone.INCLUDE columns to store non-indexed data in leaf nodes, satisfying common queries entirely within the index.ANALYZE daemon (autovacuum/auto-stats) is configured to prevent the optimizer from making stale decisions based on outdated distribution statistics.GROUP BY ClauseThe GROUP BY clause in SQL is a fundamental relational operation used to partition a result set into summary rows based on the values of one or more columns. It acts as a mandatory precursor for vector-based aggregate functions.
Region, Product).HAVING clause, distinct from row-level filtering (WHERE).Consider a Sales table with columns: Product, Region, and Amount.
The engine collapses non-aggregated columns into the grouping set.
-- Standard aggregation for regional metrics SELECT Region, SUM(Amount) AS TotalSales FROM Sales GROUP BY Region;
The WHERE clause filters data before aggregation, whereas the HAVING clause filters result sets after aggregation.
-- Filtering records > 100 before grouping, then excluding regions with low volume SELECT Region, COUNT(*) AS HighValueTransactions FROM Sales WHERE Amount > 100 GROUP BY Region HAVING COUNT(*) > 50;
For relative contributions, 2026 industry standards favor Window Functions over subqueries for performance efficiency ($O(n \log n)$ vs $O(n^2)$ complexity).
-- Optimized relative contribution using Analytic Window Functions SELECT Region, Product, SUM(Amount) / SUM(SUM(Amount)) OVER(PARTITION BY Region) AS RelativeContribution FROM Sales GROUP BY Region, Product;
GROUP BY operations typically trigger internal sort or hash-aggregate operations. Indexing the grouping columns significantly reduces the cost of the Sort phase.Nested Loop instead of Hash Aggregate).GROUP BY operation is generally $O(N \log N)$ due to the sorting required for grouping, or $O(N)$ if using hash-based aggregation, where $N$ is the number of rows processed.ONLY_FULL_GROUP_BY is enabled in your SQL dialect (e.g., MySQL/MariaDB) to prevent non-deterministic query results where non-aggregated, non-grouped columns are selected.A Subquery (or nested query) is an inner SELECT statement embedded within an outer query (e.g., SELECT, INSERT, UPDATE, or DELETE). In modern RDBMS architectures, subqueries are classified based on their interaction with the outer scope: Non-Correlated (independent, executed once) and Correlated (dependent on the outer query, executed once per row).
Returns exactly one row and one column.
Returns one or more rows/columns.
IN, EXISTS, or ANY/ALL operators.JOIN operations) to improve execution plan efficiency.While subqueries provide logical encapsulation, they are increasingly superseded by Common Table Expressions (CTEs) for readability and recursive operations.
WITH clause): Preferred over subqueries for complex logic as they improve maintainability and allow for recursive tree traversal.LATERAL Joins (PostgreSQL/Modern SQL): Replaces correlated subqueries in FROM clauses, allowing columns from the outer query to be referenced within a subquery or table-valued function.EXPLAIN ANALYZE outputs to identify unnecessary row-by-row scanning.-- 1. Scalar Subquery: Retrieving records above average using CTE for clarity WITH AvgValue AS ( SELECT AVG(salary) AS global_avg FROM employees ) SELECT emp_name, salary FROM employees, AvgValue WHERE salary > AvgValue.global_avg; -- 2. Correlated Subquery/Lateral Join: -- Fetching the most recent order for each customer SELECT c.customer_name, o.order_date FROM customers c CROSS JOIN LATERAL ( SELECT order_date FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 1 ) o;
LATERAL joins as the replacement for correlated subquery patterns in modern analytical workloads.The ORDER BY clause specifies the sorting order of a query's result set. In relational theory, rows in a table are inherently unordered; ORDER BY provides a deterministic sequence for the output.
Sorting is a blocking operation. The performance complexity is $O(N \log N)$ where $N$ is the number of rows processed.
work_mem in PostgreSQL) trigger External Merge Sorts, involving disk I/O, which significantly degrades latency.The use of Column Position (e.g., ORDER BY 1) is discouraged in 2026. This practice is considered technical debt; it breaks if the SELECT list is modified and hinders static analysis and query plan stability. Use explicit column names or aliases.
For "Top-N" queries, modern standards prefer Window Functions over LIMIT for complex partitioning:
-- Standard Top-N pattern using Window Functions SELECT product_name, units_sold FROM ( SELECT product_name, units_sold, RANK() OVER (ORDER BY units_sold DESC) as rnk FROM sales WHERE sale_date = '2026-05-20' ) t WHERE rnk <= 3;
The RAND() function is engine-specific and non-deterministic. For production-grade randomization, use cryptographically secure seeds if available, or the standard RANDOM() function (PostgreSQL) or RAND() (MySQL/MariaDB). Note that ORDER BY RANDOM() performs a full table scan and is computationally expensive ($O(N \log N)$).
-- Explicit aliasing and NULLS handling SELECT product_name, sale_date, units_sold FROM sales WHERE sale_date = '2026-05-20' ORDER BY units_sold DESC NULLS LAST, product_name ASC LIMIT 3;
NULLS FIRST/LAST syntax, which is critical for deterministic results in 2026.ORDER BY RAND() for large datasets due to $O(N \log N)$ disk-heavy overhead.LIMIT clauses.Devinterview-io/sql-interview-questions is a public repo on GitHub by Devinterview-io. ๐ฃ SQL interview questions and answers to help you prepare for your next machine learning and data science interview in 2026.
It declares no license on GitHub. Public does not mean open source: without a license, nobody is granted the right to reuse it.
Its maker has not tagged a system, so it is unknown here. Its README says what it needs.
Open "Take it" on its page: Take My Repo writes an install plan for your machine and hands it to your AI agent (Claude Code, Codex or Cursor), which asks you before each step. Take My Repo has not tested it on any machine.
A complete computer science study plan to become a software engineer.
Its last change on GitHub was on 2026-03-13. GitHub counts 1 open issue and pull requests. Take My Repo does not judge from that whether it is maintained.
Nobody has reviewed it on Take My Repo yet, which says nothing about its quality.
12 weeks, 26 lessons, 52 quizzes, classic Machine Learning for all
Bring data to life with SVG, Canvas and HTML. :bar_chart::chart_with_upwards_trend::tada:
Curated coding interview preparation materials for busy software engineers

๐ฃ LLMs interview questions and answers to help you prepare for your next machine learning and data science interview in 2026.

๐ฃ Python interview questions and answers to help you prepare for your next technical interview in 2026.

๐ฃ Pytorch interview questions and answers to help you prepare for your next machine learning and data science interview in 2026.

๐ฃ React interview questions and answers to help you prepare for your next technical interview in 2026.

๐ฃ API Design interview questions and answers to help you prepare for your next software architecture and design patterns interview in 2026.

๐ฃ Databases interview questions and answers to help you prepare for your next software architecture and design patterns interview in 2026.
This page is also available as structured data, with the same sources and dates.
/api/v1/resources/gh/Devinterview-io/sql-interview-questionsTake My Repo has not tested this on your machine. Requirements come from the project's own documentation.