SQL (Structured Query Language) is a standard language used to communicate with databases. It is used to perform tasks such as querying, updating, and managing data.

Answer: SQL commands are categorized into:

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

A primary key is a unique identifier for a record in a table. It ensures that no duplicate values exist and no value is NULL.

A foreign key is a column (or set of columns) that establishes a relationship between two tables. It points to the primary key in another table.

A unique key ensures all values in a column are unique, but unlike a primary key, it can have a single NULL value.

NULL represents a missing or unknown value in a database. It is different from zero or an empty string.

• WHERE: Filters records before the GROUP BY operation.
• HAVING: Filters records after the GROUP BY operation.

Joins are used to combine records from two or more tables. Types include:

• INNER JOIN: Returns only the matching records.
• LEFT JOIN: Returns all records from the left table, and matched records from the right table.
• RIGHT JOIN: Returns all records from the right table, and matched records from the left table.
• FULL OUTER JOIN: Returns all records when there is a match in either table.
• CROSS JOIN: Produces the Cartesian product of the two tables.

Normalization is the process of organizing data to minimize redundancy:

• 1NF: Each column must contain atomic (indivisible) values, and each column must have unique values.
• 2NF: Meets all the requirements of 1NF, and all non-key attributes are fully dependent on the primary key.
• 3NF: Meets all the requirements of 2NF, and there are no transitive dependencies.
• BCNF: Every determinant is a candidate key.

• DELETE: Removes rows from a table; can have a WHERE clause.
• TRUNCATE: Removes all rows from a table without logging individual row deletions.
• DROP: Completely removes a table and its structure from the database.

A subquery is a query nested inside another query.

• Correlated Subquery: Depends on the outer query for its values.
• Non-correlated Subquery: Executes independently of the outer query.

A view is a virtual table based on the result of a SQL query. Unlike tables, views do not store data physically.

A stored procedure is a precompiled set of SQL statements that can be executed as a program.

A trigger is a set of SQL commands automatically executed when a specific event occurs in a table (like INSERT, UPDATE, or DELETE).

• INNER JOIN: Returns only matching records from both tables.
• OUTER JOIN: Returns matching records plus unmatched records from one (LEFT or RIGHT) or both (FULL OUTER) tables.

• In MySQL/Oracle: SELECT * FROM table LIMIT N;
• In SQL Server: SELECT TOP N * FROM table;

 A composite key is a primary key made up of two or more columns.

• GROUP BY: Groups rows that have the same values in specified columns.
• ORDER BY: Sorts the result set based on specified columns.

• Atomicity: Ensures that all operations within a transaction are completed successfully.
• Consistency: Ensures the database remains in a valid state after a transaction.
• Isolation: Ensures that transactions occur independently of each other.
• Durability: Ensures that once a transaction is committed, it remains in the system.

Transaction management refers to the process of managing multiple operations on a database, ensuring they are executed as a single unit, maintaining data integrity.

A CTE is a temporary result set defined within the execution of a SELECT, INSERT, UPDATE, or DELETE statement.

 Indexes reduce the number of data pages accessed by queries, thereby speeding up the retrieval process.

Steps include:

• Analyzing the execution plan.
• Adding indexes.
• Reducing the number of joins.
• Using appropriate WHERE filters.

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

SELECT product_id, SUM(sales_amount) FROM sales
GROUP BY product_id;

SELECT department_id, MAX(salary) FROM employees
GROUP BY department_id;

• CHAR(size): A fixed-length string. If the length of the inserted value is less than the defined size, it is padded with spaces. For example, CHAR(10) always stores exactly 10 characters.
• VARCHAR(size): A variable-length string. It only uses as much space as the length of the inserted value. For example, VARCHAR(10) will store up to 10 characters, but if you insert a 5-character value, it will only use space for 5 characters.

You can find the nth highest salary using the LIMIT clause in MySQL or a subquery in other databases.

For databases like SQL Server, use:

The COALESCE() function returns the first non-NULL value in a list of expressions. It’s useful for dealing with NULL values.

Example: If you have a table where some columns may contain NULL, you can use COALESCE() to provide a default value.

SELECT COALESCE(column1, 'Default Value') FROM table_name;