Databases

Structured Query Language (SQL)

SQL has remained a consistently popular choice for database users over the years primarily due to its ease of use and the highly effective manner in which it queries, manipulates, aggregates data and performs a wide range of other functions to turn massive collections of structured data into usable information.

SQL lets you access and manipulate databases. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

For this reason, it has been incorporated into numerous commercial database products, such as MySQL, Oracle, Sybase, SQL Server, Postgres and others. In fact, many non-relational databases like MongoDB and DynamoBD are called NoSQL products due to their lack of SQL programming.

The five critical differences of SQL vs NoSQL:

1. SQL databases are relational, NoSQL are non-relational.
2. SQL databases use structured query language and have a predefined schema. NoSQL databases have dynamic schemas for unstructured data.
3. SQL databases are vertically scalable, NoSQL databases are horizontally scalable.
4. SQL databases are table based, while NoSQL databases are document, key-value, graph or wide-column stores.
5. SQL databases are better for multi-row transactions, NoSQL are better for unstructured data like documents or JSON.

Databases and tables

Database, also called electronic database, any collection of data, or information, that is specially organized for rapid search and retrieval by a computer. Databases are structured to facilitate the storage, retrieval, modification, and deletion of data in conjunction with various data-processing operations

A table is an object inside a database. A database has tables of data, views, indexes and programs. A database can have 10 or thousands of tables.

database is a collection of several components like tables, indexes, stored procedures and so on. A table is a two dimensional structure that contains several columns and rows. It is contains all the data in form of several records.

Total 5 types of tables-

1. MyISAM

2. Heap

3. Merge

4. INNO DB

5. ISAM

Database Query

A query is a way of requesting information from the database. A database query can be either a select query or an action query. A select query is a query for retrieving data, while an action query requests additional actions to be performed on the data, like deletion, insertion, and updating.

Database indexes

Bitmap indexes

A bitmap index is a special kind of indexing that stores the bulk of its data as bit arrays (bitmaps) and answers most queries by performing bitwise logical operations on these bitmaps. The most commonly used indexes, such as B+ trees, are most efficient if the values they index do not repeat or repeat a small number of times. In contrast, the bitmap index is designed for cases where the values of a variable repeat very frequently. For example, the sex field in a customer database usually contains at most three distinct values: male, female or unknown (not recorded). For such variables, the bitmap index can have a significant performance advantage over the commonly used trees.

A Dense Index

A dense index in databases is a file with pairs of keys and pointers for every record in the data file. Every key in this file is associated with a particular pointer to a record in the sorted data file. In clustered indices with duplicate keys, the dense index points to the first record with that key

Sparse Index

A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the block in the sorted data file. In clustered indices with duplicate keys, the sparse index points to the lowest search key in each block.

Reverse-key Index

A reverse-key index reverses the key value before entering it in the index. E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.

Primary Index

The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

Secondary Index

It is used to index fields that are neither ordering fields nor key fields (there is no assurance that the file is organized on key field or primary key field). One index entry for every tuple in the data file (dense index) contains the value of the indexed attribute and pointer to the block/record.

SQL commands and functions

SQL commands include the following :

A. Data defining Language (DDL)

• DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
• All the command of DDL are auto-committed that means it permanently save all the changes in the database.

Here are some commands that come under DDL

• CREATE
• ALTER
• DROP
• TRUNCATE

CREATE is used to create a new table in the database

ALTER is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute

DROP is used to delete both the structure and record stored in the table.

TRUNCATE is used to delete all the rows from the table and free the space containing the table.

B. Data Manipulation Language (DML)

• DML commands are used to modify the database. It is responsible for all form of changes in the database.
• The command of DML is not auto-committed that means it can’t permanently save all the changes in the database. They can be rollback.

Commands that come under DML

• INSERT
• UPDATE
• DELETE

INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

UPDATE: This command is used to update or modify the value of a column in the table.

DELETE: It is used to remove one or more row from a table.

C. Data Control Language

DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL

• Grant
• Revoke

Grant: It is used to give user access privileges to a database.

Revoke: It is used to take back permissions from the user.

D. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

These operations are automatically committed in the database that’s why they cannot be used while creating tables or dropping them.

Commands under TCL:

• COMMIT
• ROLLBACK
• SAVE-POINT

Commit: Commit command is used to save all the transactions to the database.

Rollback: Rollback command is used to undo transactions that have not already been saved to the database

SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

E. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

• SELECT

SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.

SQL Data Types

SQL data types can be broadly divided into following categories.

1. Numeric data types such as int, tinyint, bigint, float, real etc.
2. Date and Time data types such as Date, Time, Datetime etc.
3. Character and String data types such as char, varchar, text etc.
4. Unicode character string data types, for example nchar, nvarchar, ntext etc.
5. Binary data types such as binary, varbinary etc.
6. Miscellaneous data types — clob, blob, xml, cursor, table etc.