Database Orientation

Database: A database can be defined as an organized collection of data and information in one place.

Types of structures: There are five types of databases namely, Hierarchical Database, Network Database, Object Oriented Database, Relational Database, and No SQL.

Hierarchical databases:   Hierarchical database is one in which data are stored or organized into a tree-like structure.

 

Hierarchical database (Image Source: Internet)

Network databases: A network database is similar to a hierarchical database with one difference being that child records have the freedom to be associated with multiple parent nodes.

Network database (Image Source: Internet)

Object-oriented database: In the Object-oriented database, the information stored in a database is capable of being represented as an object which responds as an instance of the database model. Therefore, the object can be referenced and called without any difficulty.

Object oriented database (Image source: Internet)

Relational databases:

A relational database can be defined as a database that stores the data in a tabular format that is related to one another.

NoSQL database: A NoSQL refers to a non-SQL or non-relational database that provides a mechanism for storing and retrieval of data. 
Relational Database (Image source: Internet)

DBMS: A DataBase Management System (or DBMS) is essentially nothing more than a computerized data-keeping system.

RDBMS: Relational DataBase Management Systems

Relational Database Language: A language to deal with RDBMS (Ex: SQL).

Introduction to SQL 

Structured Query Language (SQL) is a standard programming language used to communicate with the database. SQL is used to create and modify/manipulate the database. 

Features of SQL

  1. High-performance: A large amount of data is retrieved quickly and efficiently. In addition, simple operations like inserting, deleting, and manipulating data can also be accomplished in a short amount of time.
  2. Scalability: SQL database is vertically scalable, which means that you can increase the load on a single server by adding more RAM, SSDs, or CPUs.
  3. DQL provides  Security and proper authentication.
  4. SQL is easy to understand as it has English-Like Structure.
  5. SQL provides Data Definition Language(DDL) which is used in deciding the structure of the database.
  6. SQL provides Data Manipulation Language(DML) which is used to manipulate the database.
  7. SQL provides Transaction Control Language which provides Commit command, Rollback command, Savepoint command.

Data Definition Language(DDL): Dr CAT

Data Definition Language (DDL) is a computer language that helps in creating the structure and modifying the structure of the database. Some of the basic commands available in DDL are DROP, RENAME, CREATE, ALTER, TRUNCATE (Dr CAT).

DROP: This command will delete the table.

Syntax: DROP   TABLE   table_name;

Example: DROP TABLE Employee;

RENAME: This command is used to rename the database.

Syntax: RENAME DATABASE old_database_name TO new_database_name;

 

CREATE: This command is used to create a table in the database.

Syntax: CREATE TABLE table_name (column_name data_type,  column_name data_type);

Example: CREATE TABLE EMPLOYEE (empId INTEGER PRIMARY KEY,   name TEXT NOT NULL,   dept TEXT NOT NULL);

ALTER:  The ALTER command is used to alter or modify the table structure.

Syntax: ALTER TABLE table_name add new_column_name data_type(length);

Example: ALTER TABLE employee add address varchar(100);

TRUNCATE: This command will delete all the data from the table.

Example: TRUNCATE TABLE EMPLOYEE;

DATA MANIPULATION LANGUAGE(DML): UDIS

Data Manipulation Language (DML) consists of SQL commands which could be used to update, delete, insert, and select (UDIS) the contents of the database.

UPDATE: The UPDATE command is used to update the content of the table. We can use the UPDATE command based on a certain condition also.

Syntax: UPDATE table_name SET column_name=new_value WHERE condition;

Example: UPDATE EMPLOYEE SET name=’admin’ WHERE empid=123;

DELETE : This command will delete all the data from the table.

Syntax: DELETE table_name;

Example: DELETE EMPLOYEE;

INSERT: The INSERT command is used to insert data into the table.

Example: INSERT INTO employee (empid, name, dept, address) values (123, ‘dr cat’, ‘BBA’,  ‘Bangalore’);

SELECT: The SELECT command is used to select the data from the table. If we the symbol * then all the columns will be selected otherwise only the column name mentioned will be selected.

In Example 1, all the columns will be displayed as the output and in Example 2, only the empid and name from the table employee will be displayed as the output.

Example 1: SELECT * FROM Employee;

Example 2: SELECT empid, name FROM employee;