Database

Basic Database Concepts

1. What is a Database?

   • A database is a structured collection of data that can be easily accessed, managed, and updated.

2. What is DBMS?

   • DBMS stands for Database Management System, which is software that manages databases and provides functionalities like data storage, retrieval, and manipulation.

3. What is SQL?

   • SQL (Structured Query Language) is a programming language used for managing and querying databases.

SQL Basics

4. What is the difference between DELETE and TRUNCATE?

   • DELETE removes specific rows based on conditions, while TRUNCATE removes all rows from a table.

5. Explain JOIN in SQL.

   • JOIN is used to combine rows from two or more tables based on a related column.

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

4. What are INDEXES?

   • Indexes are used to improve the speed of data retrieval operations on a database table.

5. What is a PRIMARY KEY?

   • A PRIMARY KEY is a unique identifier for a record in a database table.

6. What is a FOREIGN KEY?

   • A FOREIGN KEY is a field in a table that references the PRIMARY KEY of another table.

Normalization

9. What is normalization?

   • Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

10. Explain First Normal Form (1NF).

    • 1NF requires that each column in a table has atomic values and there are no repeating groups or arrays.

Database Design

11. What is ERD?

    • ERD (Entity-Relationship Diagram) is a visual representation of the relationships between entities in a database.

SQL Advanced Queries

12. What is a VIEW?

    • A VIEW is a virtual table based on the result set of

13. 
    What is a STORED PROCEDURE?

    • A STORED PROCEDURE is a prepared SQL code that you can save, so the code can be reused over and over again.

Transaction Management

14. What is a TRANSACTION?

    • A TRANSACTION is a sequence of one or more SQL statements that are executed as a single unit.

15. What is ACID in database transactions?

    • ACID stands for Atomicity, Consistency, Isolation, and Durability, which are properties of database transactions.

Indexing

16. What is CLUSTERED INDEX?

    • A CLUSTERED INDEX reorders the physical order of the table and searches based on the order of the index.

Performance Tuning

17. How do you optimize SQL queries?

    • You can optimize SQL queries by using indexes, avoiding SELECT *, using EXPLAIN to analyze query performance, etc.

Database Security

18. What is SQL Injection?

    • SQL Injection is a technique where malicious users can inject SQL commands into an SQL statement, thereby gaining unauthorized access to the database.

NoSQL Databases

19. What is NoSQL?

    • NoSQL databases are non-relational databases that can store and retrieve data in a flexible and scalable manner.

20. What are the types of NoSQL databases?

    • Types of NoSQL databases include Document-based, Column-based, Key-Value, and Graph databases.

Database Tools

21. What are the popular database management tools?

    • Popular database management tools include MySQL Workbench, SQL Server Management Studio, Oracle SQL Developer, etc.

Data Backup and Recovery

22. What is data backup and why is it important?

    • Data backup is the process of creating copies of data to prevent data loss and ensure data recovery in case of system failures or disasters.

Database Administration

23. What are the responsibilities of a Database Administrator (DBA)?

    • DBA responsibilities include database design, performance tuning, backup and recovery, security management, etc.

SQL Examples

24. Write a SQL query to find the second highest salary.

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

25.  Write a SQL query to find duplicate records in a table.

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

SQL Joins and Subqueries

26. What is a SELF JOIN?

    • A SELF JOIN is a type of SQL join where a table is joined with itself.

SELECT A.column_name, B.column_name

FROM table_name A, table_name B

WHERE A.common_field = B.common_field;

27. What is a LEFT JOIN?

• LEFT JOIN returns all rows from the left table, and the matching rows from the right table. If no match found, NULL values are returned.

SELECT A.column_name, B.column_name

FROM table1 A

LEFT JOIN table2 B ON A.common_field = B.common_field;

28. What is a RIGHT JOIN?

• RIGHT JOIN returns all rows from the right table, and the matching rows from the left table. If no match found, NULL values are returned.

SELECT A.column_name, B.column_name

FROM table1 A

RIGHT JOIN table2 B ON A.common_field = B.common_field;

29. What is a FULL OUTER JOIN?

• FULL OUTER JOIN returns all rows when there is a match in one of the tables. If no match found, NULL values are returned.

SELECT A.column_name, B.column_name

FROM table1 A

FULL OUTER JOIN table2 B ON A.common_field = B.common_field;

30. What is a SUBQUERY?

• A SUBQUERY is a query nested inside another SQL query.

SELECT column_name

FROM table_name

WHERE column_name = (SELECT another_column FROM another_table WHERE condition);

SQL Aggregate Functions

31. What is COUNT() function?

    • COUNT() function returns the number of rows that match a specified condition.

SELECT COUNT(column_name) FROM table_name WHERE condition;

32. What is SUM() function?

• SUM() function returns the total sum of a numeric column.

SELECT SUM(column_name) FROM table_name WHERE condition;

33. What is AVG() function?

• AVG() function returns the average value of a numeric column.

SQL Constraints

35. What are CHECK constraints?

    • CHECK constraints enforce domain integrity by limiting the values that can be placed in a column.