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 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

Q7): 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;

Q8) 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 a primary key?

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as a primary key, the following conditions had to be met:

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