In this Blog i am posting the tutorial for SQL Concept in RDBMS – Relation Database Management System. SQL is basically a scripting language used in Relational Database Management System(RDBMS).
Relational Database Management System -RDBMS
As the name suggests, RDBMS is Database Management System where Data is managed in a relational model. Relational model means data is stored in tables have relationship with one another. RDBMS is designed to store, organize, and retrieve data in a structured manner. Database Management System can be various platforms like MYSQL, Oracle, SQL Server, Postgres, EXASOL etc. where data will be stored in Table Structures and as a developer we require to manipulate and work on these data.
Data in RDBMS is stored basically in Table Structure format. Table consists of Rows and Columns as we see in Excel sheets. Each column represents a specific attribute or field of the data. Each row represents a record or a tuple.
In the above screenshot, we can see a simple example where data of a Company’s Employee is shown. The vertical bars displayed in Blue color is Columns – Employee ID, Employee Name, Employee Department, Salary and Designation. We can say that the columns are attribute of Employees. Attribute means parameters which gives details about any specific Object- in our case the object is Employee and we get various details of an Employee Working in the Company.
The rows in the figure which are also called tuples gives the actual data of all the employees working in the company. Here we can see there are 5 employee details shown for a company.
So now we understand below terms:-
- RDBMS – Relational Database Management System.
- Relational Model.
- Tables – Rows and columns concept.
- Various RDBMS platforms.
Structured Query Language – SQL
SQL is programming language used by RDBMS to manage, store and manipulate Databases. Understanding the actual concept of the technical terms like manipulation, storage and managing we can take a very simple basic example – Some 10 years back if suppose a person had a small shop and manages a register to store details of his products that he sells and the money that he earns. There can be lot of parameters that he should mark in his register in columns format for eg. product name, price of the product, quantity sold, profit earned etc.
But with passing time interval the Business of person grows and hence his data also grows in more number. Hence it becomes difficult to manage the growing large number of data in registers as the numbers of registers will also grow and hence difficult to handle. Hence he buys a computer and uses a Database Management system to store these data in form of Tables. The new data in the DBMS system will look like this
So when used a Database System, we can manage data in form of tables and store our useful data. We can also query this data whenever required in any specific format, we can do some calculations on it and get some aggregated yearly on monthly results in for of net profit/month or net profit/year etc. Hence we can retrieve and manipulate data based on our needs using SQL in the DBMS system. So now we can se how easy the life of the businessman in our example has been when used DBMS and SQL.
Now when we have understood the very easy concept of DBMS with SQL , now its time to understand various technical aspects and properties of SQL.
1. SQL Data Types:
Data types of in SQL relates to the concept that what type of data we are storing in Tables. Is it a String type, Integer type, Decimal type, Date type etc. For eg in our above screenshots, Employee ID or Employee Name has values stored in string format. Hence they are String type, columns that store some numeric Values like Net Profit, Quantity etc are numeric type. Hence below are the Datatypes in SQL:-
a) Numeric Datatype-
- Integers – It has the values without decimals eg- 101, 102, 13 etc.
- floating-point numbers – They have values with Decimals for eg 10.08, 103.24 etc.
b) Character Datatype-
- String- This datatype when defined for a column can be used to store any value in String format. eg- “ANUJ KUMAR”, “CONTAINEMENT ZONE” etc.
- Text – These datatypes are used to store string value of large length to store paragraphs or sentences etc.
c) Date Datatype-
- Date- This datatype when defined for a column can be used to store any value in date(‘YYYY-MM-DD’) format. eg- “2023-05-21”, “1999-09-23” etc.
- Timestamp – These datatypes are used to store string value of date shown along with timestamp(‘YYYY-MM-DD HH24:MM:SS’) format. . Timestamp is required when we want to know that any incident happening on a particular date and on what time exactly.eg- “2023-05-21 12:34:45”, “1999-09-23 23:35:55” etc.
Now when the above Datatypes we have understood, we can create the actual Table structure having Columns in it. This structure creation will be done by DDL statements.
2. SQL Data Definition Language (DDL):
DDL is used to create the structure of the Table. Tables can be created, Altered and Dropped by DDL statements.
a) CREATE TABLE statements can be written as
CREATE TABLE Company(
EmployeeID INT,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255),
SALARY DECIMAL(2,3),
Department varchar(255)
);
We can see we have created a Table called Company and defined various columns for it with Datatypes. This will create a Table in Database. We can also define Constraints for the Table. Below are the constraints for a Table:-
- PRIMARY KEY: Identify a unique record in a table.
- NOT NULL: Specify that a column must have a value.
- UNIQUE: Ensure that each value in a column is unique.
- FOREIGN KEY: Establish a relationship between tables.
- CHECK: Define specific conditions for column values.
b) ALTER TABLE statements is used to change any particular attribute of a Table. Hence structural changes can be done by ALTER statements. Suppose we have to update a Columns Datatype or add one new column in existing table, update a primary key, update the name or drop a column. Hence all these updates in the structure is done by ALTER commands.
- ALTER TABLE TABLE_NAME ADD COLUMN COLUMN_NAME
- ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME
- ALTER TABLE TABLE_NAME ADD PRIMARY KEY CONSTRAINT_NAME
- ALTER TABLE TABLE_NAME DROP COLUMN COLUMN NAME etc.
c) DROP TABLE: Removes a table from the database.
DROP TABLE TABLE_NAME.
3. SQL Data Manipulation Language (DML):
Now that we have created the Table Structure, hence we are ready to Load or Insert Data into it. Insertion of Data, then operation on this data by doing some calculations, updating some data or deleting the data comes under DML Operation. Below are the process in details for DML:-
- INSERT INTO: Insert new records into a table. Syntax – INSERT INTO Company(EmployeeID, LastName, FirstName, Address, City, Salary, Department)
VALUES (‘E101’, ‘Prabhakar’, ‘Anuj’, ‘ECITY PHASE-1’, ‘MUMBAI’, 200000.00,’D101′); - UPDATE: Modify existing records in a table. Syntax- UPDATE Company set SALARY = 2500000 where EmployeeID=’E1023′
- DELETE: Remove records from a table. Delete removes some or all data based on if filter is provided or not. If no filter is used, it deletes hole record from a table. If used with a filter it will delete specific records. Syntax – DELETE FROM Company where EmployeeID=’E101′ , DELETE FROM Company;
Hence in this Article we have understood below concepts:-
- RDBMS – Relational Database Management System.
- SQL – Structured Query Language
- Relational Model.
- Datatypes in SQL.
- SQL – Data Definition Language (DDL)
- SQL – Data Manipulation Language (DML)