Q1): What is SQL?

Structured Query Language is a database tool that is used to create and access databases to support software applications.

Q2): What is a DBMS?

A database management system (DBMS) is a software system that helps users create, manage, and access data in a database.expand_more It provides an organized way to store, retrieve, and manipulate information.expand_more Imagine a large library filled with bookshelves. A DBMS is like a filing system that keeps all those books organized and easy to find.

Q3): What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS stores the data in a collection of tables, which are related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored in the tables.

Q4): What are the different joins used in SQL?

Q5): What is the difference between the “DELETE” and “TRUNCATE” commands?

Both DELETE and TRUNCATE commands are used to remove data from tables in a database, but they have some key differences

• DELETE: This removes specific rows from a table. You can use a WHERE clause to filter the data and only delete rows that meet the criteria.

• TRUNCATE: This removes all rows from a table. It's like emptying a bucket in one go, as opposed to taking items out one by one

• DELETE is executed using a row lock, and each row in the table is locked for deletion, whereas TRUNCATE is executed using a table lock, and the entire table is locked for the removal of all records.

• DELETE is a DML command, whereas TRUNCATE is a DDL command.

• The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row, whereas TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

• To use Delete, you need DELETE permission on the table, whereas to use Truncate on a table, you need at least ALTER permission on the table.

• Use DELETE for selective deletion with control and rollback options.

• Use TRUNCATE for faster removal of all data from a table, but be aware it's permanent.

DELETE FROM Customers WHERE CustomerName='Mahendra';

TRUNCATE TABLE CUSTOMERS;

Q6): What is the difference between the “WHERE” clause and the “HAVING” clause?

Both WHERE and HAVING clauses are used for filtering data in SQL queries, but they target data at different stages of the query processing:

WHERE Clause:

• Applied before data grouping.

• Filters individual rows based on a specified condition.

• Can be used with various SQL statements like SELECT, UPDATE, and DELETE.

• Works with single-row functions like UPPER, LOWER, etc.

HAVING Clause:

• Applied after data grouping using the GROUP BY clause.

• Filters groups of data based on a condition applied to aggregate functions (e.g., SUM, COUNT, AVG) calculated on those groups.

• Only usable with SELECT statements.

• Works with aggregate functions like SUM, COUNT, AVG, etc.

We can't use aggregate functions in the WHERE clause unless it is in a sub-query contained in a HAVING clause, whereas we can use an aggregate function in the HAVING clause. We can use a column name in the HAVING clause, but the column must be contained in the group by clause.

In the WHERE clause, the data that is fetched from memory depends on a condition, whereas in HAVING, the completed data is first fetched and then separated depending on the condition.

SELECT * FROM Customers. WHERE Country='Mexico';

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

Q6):What is a primary key?

A primary key is a column or combination of columns that uniquely identifies each row in a table. It enforces the entity integrity rule in a relational database.

1. No two rows can have the same primary key value.

2. Every row must have a primary key value

3. The primary key field cannot be null

4. Values in primary key columns can never be modified or updated

Q7):What is a foreign key?

A foreign key is a column or combination of columns that establishes a link between data in two tables. It ensures referential integrity by enforcing relationships between tables.

Q8): What is the difference between “primary key” and “unique key”?

1. We can have only one primary key in a table, whereas we can have more than one unique key in a table.

2. The primary key cannot have a NULL value, whereas a unique key may have only one null value.

3. By default, a primary key is a clustered index, whereas a unique key is a unique non-clustered index.

4. A Primary Key supports an auto-increment value, whereas a Unique Key doesn't support an auto-increment value

Q9):What is the difference between a primary key and a unique key?

A primary key is used to uniquely identify a row in a table and must have a unique value. On the other hand, a unique key ensures that a column or combination of columns has a unique value but does not necessarily identify the row.

Q9): What is a trigger?

A trigger is a code associated with insert, update, or delete operations. The code is executed automatically whenever the associated query is on a table. Triggers can be useful to maintain integrity in a database.

• Events that trigger them: SQL triggers can be fired by various events, such as:

  • Inserting a new row into a table (INSERT statement)

  • Updating an existing row in a table (UPDATE statement)

  • Deleting a row from a table (DELETE statement)

CREATE TRIGGER prevent_negative_stock

BEFORE UPDATE ON Products

FOR EACH ROW

BEGIN

IF NEW.stock_level - OLD.stock_level < 0 THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Update would cause negative stock level.'; END IF;

• END;

Q10): What is a stored procedure?

A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks. It acts like a subroutine that you can call and reuse multiple times to perform a particular task.

• Reusability: Once you create a stored procedure, you can call it by its name whenever you need to execute the same set of SQL statements. This saves you time and effort compared to rewriting the statements each time.

• Modularity: Stored procedures break down complex tasks into smaller, manageable units. This improves code organization and maintainability.

• Input Parameters: Stored procedures can accept input parameters, allowing you to customize their behaviour based on the provided values. This makes them more flexible than static SQL statements.

• Security: You can control access to stored procedures using database permissions. This allows you to grant users the ability to execute specific procedures without giving them direct access to the underlying tables.

• Performance: In some cases, stored procedures can improve performance by reducing network traffic. The database server can compile the stored procedure code once and then reuse it for subsequent executions.

  CREATE PROCEDURE GetCustomerById (@CustomerID INT) AS

  BEGIN

  -- Select customer details from the Customers table SELECT * FROM Customers WHERE CustomerID = @CustomerID; END;

Q9): What is the difference between triggers and stored procedures?

Unlike stored procedures, triggers cannot be called directly. They can only be associated with queries.

• Trigger: A trigger fires automatically whenever a specific event occurs in the database, such as inserting, updating, or deleting data in a table. It acts like a reactive measure.

• Stored Procedure: A stored procedure is called explicitly by an application or user using a command EXEC followed by the procedure name. It's invoked intentionally to perform a task.

  | Feature | Trigger | Stored Procedure | | --- | --- | --- | | Invocation | Automatic (based on database events) | Explicit (called by user or application) | | Typical Use Cases | Enforce data integrity and automate actions | Perform calculations, data manipulation, and logic | | Input Parameters | No | Yes | | Return Values | No | Can return value |

  Q10): What are indexes?

  A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on disk. To support faster access according to different values, a faster search, like a binary search for different values, is desired. For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.

  CREATE INDEX idx_city ON Customers(City);

Q11): What are clustered and non-clustered indexes?

Clustered indexes are the indexes according to which data is physically stored on disk. Therefore, only one clustered index can be created on a given database table. Non-clustered indexes don’t define the physical ordering of data; they define logical ordering. Typically, a tree is created whose leaf points to disk records. B-Tree or B+ trees are used for this purpose.

Q12): What is normalization?

Normalization is the process of organizing data in a database to minimize redundancy and dependency. It involves breaking down a table into smaller tables and establishing relationships between them.

Q13):What are the different types of normalization?

1-The different types of normalization are: First Normal Form (1NF)

2-Second Normal Form (2NF) Third Normal Form (3NF)

3-Boyce-Codd Normal Form (BCNF) Fourth Normal Form (4NF)

4-Fifth Normal Form (5NF) or Project-Join Normal Form (PJNF)

Q14): What is a transaction in SQL?

A transaction is a sequence of SQL statements that are executed as a single logical unit of work. It ensures data consistency and integrity by either committing all changes or rolling them back if an error occurs.

Q15):What is ACID in the context of database transactions?

ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that guarantee reliable processing of database transactions.

Atomicity ensures that a transaction is treated as a single unit of work, either all or none of the changes are applied.

Consistency ensures that a transaction brings the database from one valid state to another.

Isolation ensures that concurrent transactions do not interfere with each other.

Durability ensures that once a transaction is committed, its changes are permanent and survive system failures.

Q16):What is a deadlock?

A deadlock occurs when two or more transactions are waiting for each other to release resources, resulting in a circular dependency. As a result, none of the transactions can proceed, and the system may become unresponsive.

Q17):What is the difference between a database and a schema?

A database is a container that holds multiple objects, such as tables, views, indexes, and procedures. It represents a logical grouping of related data. A schema, on the other hand, is a container within a database that holds objects and defines their ownership. It provides a way to organize and manage database objects.
Q18):What is the difference between a temporary table and a table variable?

A temporary table is a table that is created and exists only for the duration of a session or a transaction. It can be explicitly dropped or is automatically dropped when the session or transaction ends.

A table variable is a variable that can store a table- like structure in memory. It has a limited scope within a batch, stored procedure, or function. It is automatically deallocated when the scope ends.

Q19):What is the purpose of the GROUP BY clause?

The GROUP BY clause is used to group rows based on one or more columns in a table. It is typically used in conjunction with aggregate functions, such as SUM, AVG, COUNT, etc., to perform calculations on grouped data.

Q20):What is a view?

A view is a virtual table based on the result of an SQL statement. It allows users to retrieve and manipulate data as if