SQL Fundamentals

1. What is SQL?

   • SQL (Structured Query Language) is a standard language for managing relational databases.
   • It's used to interact with databases, such as MySQL, PostgreSQL, and SQL Server.

2. What are the different types of SQL commands?

   • DDL (Data Definition Language): Used to define the structure of the database (e.g., CREATE, ALTER, DROP).
   • DML (Data Manipulation Language): Used to manipulate data within the database (e.g., INSERT, UPDATE, DELETE, SELECT).
   • DCL (Data Control Language): Used to control access to the database (e.g., GRANT, REVOKE).
   • TCL (Transaction Control Language): Used to manage transactions (e.g., COMMIT, ROLLBACK).

3. What is a relational database?

   • A database that stores data in tables, where rows represent records and columns represent attributes.
   • Relationships between data are defined using keys (primary keys, foreign keys).

4. What is a primary key?

   • A unique identifier for each row in a table.
   • Cannot contain null values.

5. What is a foreign key?

   • A column in one table that references the primary key of another table.
   • Establishes a relationship between two tables.

SELECT Statement

6. Write a basic SELECT statement to retrieve all columns from a table.

   • SELECT * FROM table_name;

7. Write a SELECT statement to retrieve specific columns from a table.

   • SELECT column1, column2 FROM table_name;

8. How do you filter rows using the WHERE clause?

   • SELECT * FROM table_name WHERE condition;
   • Example: SELECT * FROM customers WHERE city = 'New York';

9. How do you sort the results using the ORDER BY clause?

   • SELECT * FROM table_name ORDER BY column_name ASC; (Ascending order)
   • SELECT * FROM table_name ORDER BY column_name DESC; (Descending order)  

10. How do you limit the number of rows returned using the LIMIT clause?

    • SELECT * FROM table_name LIMIT 10; (Returns the first 10 rows)

JOIN Clauses

11. What is an INNER JOIN?

    • Returns rows where there is a match between the columns of two tables.

12. What is a LEFT JOIN?

    • Returns all rows from the left table and the matching rows from the right table.
    • If there is no match in the right table, it returns NULL values for the right table's columns.  

13. What is a RIGHT JOIN?

    • Returns all rows from the right table and the matching rows from the left table.
    • If there is no match in the left table, it returns NULL values for the left table's columns.  

14. What is a FULL OUTER JOIN?

    • Returns all rows from both tables, including rows where there is no match in either table.

Aggregate Functions

15. What is the AVG() function?

    • Calculates the average value of a column.

16. What is the SUM() function?

    • Calculates the sum of values in a column.

17. What is the COUNT() function?

    • Counts the number of rows in a table or the number of non-NULL values in a column.

18. What is the MAX() function?

    • Returns the maximum value in a column.

19. What is the MIN() function?

    • Returns the minimum value in a column.

GROUP BY Clause

20. How does the GROUP BY clause work?
    • Groups rows that have the same values for specified columns.
    • Often used with aggregate functions.

HAVING Clause21. How does the HAVING clause work?- Filters the results of a GROUP BY clause.

Subqueries

22. What are subqueries?
    • Nested SELECT statements within another SELECT statement.

UNION Operator

23. What does the UNION operator do?
    • Combines the result sets of two or more SELECT statements.
    • Removes duplicate rows by default.

Data Manipulation Language (DML)

24. How do you insert a new row into a table?

    • INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

25. How do you update existing rows in a table?

    • UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

26. How do you delete rows from a table?

    • DELETE FROM table_name WHERE condition;

Data Definition Language (DDL)

27. How do you create a new table?

    • CREATE TABLE table_name (column1 data_type, column2 data_type, ...);

28. How do you alter the structure of a table?

    • ALTER TABLE table_name ADD column_name data_type;
    • ALTER TABLE table_name DROP column_name;
    • ALTER TABLE table_name MODIFY column_name data_type;  

29. How do you delete a table?

    • DROP TABLE table_name;

Indexes

30. What is an index?

    • A data structure that improves the speed of data retrieval.
    • Creates a sorted order for a specific column or set of columns.

31. What are the different types of indexes?

    • B-tree index: Suitable for most situations.
    • Hash index: Fast for equality searches.

Views

32. What is a view?
    • A virtual table that is created from the result set of a SELECT statement.

Stored Procedures

33. What is a stored procedure?
    • A set of SQL statements that are stored in the database and can be executed by name.

Triggers

34. What is a trigger?
    • A special type of stored procedure that is automatically executed when a specific event occurs (e.g., INSERT, UPDATE, DELETE).

Transactions

35. What is a transaction?

    • A logical unit of work that consists of one or more SQL statements.
    • Transactions ensure data integrity by either committing all changes or rolling back all changes.

36. What are the ACID properties of transactions?

    • Atomicity: All operations within a transaction are treated as a single unit.
    • Consistency: A transaction must leave the database in a consistent state.
    • Isolation: Concurrent transactions do not interfere with each other.
    • Durability: Once a transaction is committed, the changes are permanent and will not be lost.

Constraints

37. What are constraints?

    • Rules that enforce data integrity in a database.

38. What is a NOT NULL constraint?

    • Ensures that a column cannot contain NULL values.

39. What is a UNIQUE constraint?

    • Ensures that all values in a column are unique.

40. What is a CHECK constraint?

    • Ensures that the values in a column meet a specific condition.

Database Administration

41. What is database backup and recovery?

    • The process of creating copies of the database to prevent data loss.

42. What is database tuning?

    • The process of optimizing database performance.

43. What is database security?

    • The process of protecting the database from unauthorized access and attacks.

Advanced Topics

44. What is window functions?

    • Perform calculations across a set of rows related to the current row.
    • Examples: RANK(), ROW_NUMBER(), LAG(), LEAD().

45. What is recursive SQL?

    • Used to query hierarchical data (e.g., tree structures).

46. What is a materialized view?

    • A view that is pre-calculated and stored in the database.

Interview Questions

47. Write a SQL query to find the names of all customers from a specific city.
48. Write a SQL query to find the total sales for each product.
49. Write a SQL query to find the employees who have a salary greater than the average salary.
50. Write a SQL query to find the top 5 customers who have made the most purchases.

Note: These are just some of the many SQL concepts and questions that can be covered in an interview. The specific questions will vary depending on the job role and the company.

I hope these questions are helpful for your SQL interview preparation!