Useful and Most Frequently asked Questions & Answers of Database (SQL, NoSQL, etc..)

» SQL commands

→ DDL (Data Definition Language) : create, alter, drop.

→ DML (Data Manipulation Language) : select, insert, update and delete

→ DCL (Data Control Language) : GRANT- Gives a privilege to user, REVOKE - Takes back privileges granted from user

» List MySQL Engines

→ PERFORMANCE_SCHEMA

→ InnoDB

→ MRG_MYISAM

→ BLACKHOLE

→ MyISAM

» What are MySQL triggers and how to use them?

See Answer

» MySQL Joins

- INNER JOIN: Returns all rows when there is at least one match in BOTH tables

- LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

- FULL JOIN: Return all rows when there is a match in ONE of the tables

» Find 3rd or nth maximum salary from table

Consider table "employee" having 3 columns:

1. emp_id, 2.emp_name, 3.emp_salary


- Select emp_salary
from (SELECT DISTINCT emp_salary from employee ORDER BY emp_salary DESC LIMIT 0,3) a ORDER BY emp_salary ASC LIMIT 1;


OR
- Select TOP 1 emp_salary from (SELECT DISTINCT TOP 3 emp_salary from employee ORDER BY emp_salary DESC) a ORDER BY emp_salary ASC;

See more answers

» What is Primary Key

Primary Key uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key.

» What is Unique Key

It is same as Primary Key i.e. it enforces the Uniqueness of the values (i.e. avoids duplicate values) on the column[s] on which it is defined. But unlike primary key it allows Null value. Also we can have more than one Unique key columns.

» What is Foreign Key

A foreign key is a key used to link two tables together. This is sometimes called a referencing key. Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

» What is Indexing in sql

Indexing is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table. An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries.

» What are the advantages of stored procedures, triggers, indexes?

Stored Procedures:

Advantages:

• Improved Performance: Stored procedures can reduce network traffic by executing multiple SQL statements in a single call to the database server.
• Code Reusability: Procedures can be reused across multiple applications, reducing redundant code and improving maintainability.
• Enhanced Security: Access to data can be controlled through stored procedures, reducing the risk of SQL injection attacks.

Example:

CREATE PROCEDURE GetEmployeeDetails
        AS
        BEGIN
            SELECT * FROM Employees;
        END;

This stored procedure retrieves all employee details from the Employees table.

Triggers:

Advantages:

• Data Integrity: Triggers enforce business rules and data integrity constraints automatically whenever specified events occur, ensuring data consistency.
• Audit Trail: Triggers can be used to track changes to database records, providing an audit trail for compliance and troubleshooting purposes.
• Cascade Actions: Triggers can perform cascade actions such as updating related records or sending notifications based on specific events.

Example:

 CREATE TRIGGER AfterEmployeeInsert
        AFTER INSERT ON Employees
        FOR EACH ROW
        BEGIN
            INSERT INTO AuditTrail (TableName, Action, Timestamp)
            VALUES ('Employees', 'Insert', NOW());
        END;

This trigger inserts a record into the AuditTrail table after each new employee record is inserted into the Employees table.

Indexes:

Advantages:

• Faster Query Performance: Indexes speed up data retrieval operations by allowing the database engine to quickly locate relevant rows based on indexed columns.
• Optimized Sorting and Filtering: Indexes improve the efficiency of sorting and filtering operations, particularly for large datasets.
• Unique Constraints: Unique indexes enforce uniqueness constraints on columns, preventing duplicate values and ensuring data integrity.

Example:

        CREATE INDEX idx_lastname ON Employees (LastName);

This index on the LastName column of the Employees table speeds up queries that involve filtering or sorting by last name.

In summary, stored procedures, triggers, and indexes are valuable database features that offer performance improvements, data integrity enforcement, and code reusability benefits. By leveraging these features effectively, developers can optimize database operations and enhance the reliability and scalability of their applications.

» How can we know the number of days between two given dates using MySQL?

Example:

SELECT DATEDIFF(‘2007-03-07′,’2005-01-01′);