What are the main types of SQL commands?

SQL commands are grouped into DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language).

What's the difference between DELETE and TRUNCATE?

DELETE removes specific rows and can be rolled back. TRUNCATE removes all rows, resets the identity counter, is faster, and can't be rolled back.

Explain PRIMARY KEY and FOREIGN KEY constraints.

PRIMARY KEY uniquely identifies each record in a table. FOREIGN KEY establishes relationships between tables by referencing a PRIMARY KEY in another table.

What is the difference between INNER and LEFT JOIN?

INNER JOIN returns matching records from both tables. LEFT JOIN returns all records from the left table and matching records from the right table.

What are SQL aggregate functions?

Typical aggregate functions include COUNT, SUM, AVG, MAX, and MIN. They perform calculations on multiple rows and return a single value.

Explain the WHERE vs HAVING clause.

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY clause. HAVING works with aggregate functions, WHERE doesn't.

What are SQL indexes and their importance?

Indexes improve query performance by providing faster data retrieval. They create data structure that speeds up searching and sorting operations.

How to remove duplicates in SQL?

Use DISTINCT keyword in SELECT statement or GROUP BY clause. For removing duplicate rows, use the DELETE with ROW_NUMBER() function.

What is SQL normalization?

Normalization organizes database tables to minimize redundancy. Standard forms are 1NF, 2NF, and 3NF, each addressing specific data integrity issues.

Explain SQL subqueries.

Subqueries (nested queries) are queries within another query. They can be used in SELECT, FROM, and WHERE clauses to perform complex operations.

What's the difference between UNION and UNION ALL?

UNION combines results and removes duplicates. UNION ALL combines results, including duplicates. UNION ALL performs better as it skips duplicate removal.