SQL Basics

1. What is SQL?

SQL (Structured Query Language) is used to manage and manipulate relational databases. It allows users to create, retrieve, update, and delete data.

2. What are the different types of SQL commands?

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE
• DQL (Data Query Language): SELECT
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
• DCL (Data Control Language): GRANT, REVOKE

3. What is the difference between DELETE, TRUNCATE, and DROP?

• DELETE: Removes specific records using a WHERE clause. (Can be rolled back)
• TRUNCATE: Deletes all rows but retains the table structure. (Cannot be rolled back)
• DROP: Completely removes the table structure and data.

4. What are Primary Key and Foreign Key?

• Primary Key: Uniquely identifies each record in a table. Cannot have NULL values.
• Foreign Key: References a Primary Key in another table to establish a relationship.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

5. What are Joins in SQL?

Joins are used to combine data from multiple tables.

• INNER JOIN: Returns matching records from both tables.
• LEFT JOIN: Returns all records from the left table and matching records from the right.
• RIGHT JOIN: Returns all records from the right table and matching records from the left.
• FULL JOIN: Returns all records when there is a match in either table.

6. What is the difference between WHERE and HAVING?

• WHERE is used to filter rows before aggregation.
• HAVING is used to filter aggregated results after GROUP BY.