Database

A checkpoint is like a snapshot of the DBMS state. Using checkpoints, the DBMS can reduce the amount of work to be done during a restart in the event of subsequent crashes. Checkpoints are used for the recovery of the database after the system crash. Checkpoints are used in the log-based recovery system. When due to a system crash we need to restart the system then at that point we use checkpoints. So that, we don't have to perform the transactions from the very starting.

The sequence of operations performed which changes the consistent state of the database to another is known as the database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.

Online Transaction Processing (OLTP) manages transaction-based applications which can be used for data entry, data retrieval, and data processing. OLTP makes data management simple and efficient. Unlike OLAP systems goal of OLTP systems is to serve real-time transactions.

SQL stands for Structured Query Language, and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updating, insertion, and deletion of data from a database.

Advantages of SQL are:

• Simple SQL queries can be used to retrieve a large amount of data from the database very quickly and efficiently.
• SQL is easy to learn and almost every DBMS supports SQL.
• It is easier to manage the database using SQL as no large amount of coding is required.

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle, or IBM DB2, that is used to manage SQL databases.

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:

• NOT NULL - Restricts NULL value from being inserted into a column.
• CHECK - Verifies that all values in a field satisfy a condition.
• DEFAULT - Automatically assigns a default value if no value has been specified for the field.
• UNIQUE - Ensures unique values to be inserted into the field.
• INDEX - Indexes a field providing faster retrieval of records.
• PRIMARY KEY - Uniquely identifies each record in a table.
• FOREIGN KEY - Ensures referential integrity for a record in another table.

SQL statements are basically divided into three categories, DDL, DML, and DCL.

They can be defined as:

• Data Definition Language (DDL) commands are used to define the structure that holds the data. These commands are auto-committed i.e. changes done by the DDL commands on the database are saved permanently.
• Data Manipulation Language (DML) commands are used to manipulate the data of the database. These commands are not auto-committed and can be rolled back.
• Data Control Language (DCL) commands are used to control the visibility of the data in the database like revoke access permission for using data in the database.

Some common SQL clauses used in conjunction with a SELECT query are as follows:

• WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
• ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).

SELECT *
FROM myDB.students
WHERE graduation_year = 2019
ORDER BY studentID DESC;

• GROUP BY clause in SQL is used to group records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.
• HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.

SELECT COUNT(studentId), country
FROM myDB.students
WHERE country != "INDIA"
GROUP BY country
HAVING COUNT(studentID) > 5;

Data Definition Language (DDL) commands:

• CREATE to create a new table or database.
• ALTER for alteration.
• TRUNCATE to delete data from the table.
• DROP to drop a table.
• RENAME to rename a table.

Data Manipulation Language (DML) commands:

• INSERT to insert a new row.
• UPDATE to update an existing row.
• DELETE to delete a row.
• MERGE for merging two rows or two tables.

Data Control Language (DCL) commands:

• COMMIT to permanently save.
• ROLLBACK to undo the change.
• SAVEPOINT to save temporarily.