Introduction to SQL

This article will teach you basic and advanced concepts of SQL. It concepts, such as what is a Database Management System, what is SQL, SQL Data types, SQL Statements, and more.

Structured Query Language is known as SQL in short. SQL is a standard programming language that is specifically designed for storing, retrieving, managing, or manipulating data. It became an ISO standard in 1987.

SQL is the most widely used and implemented database language concept. It supports the popular relational database system concept,  like MySQL, SQL Server, and Oracle. SQL Tutorial for beginners will consist of the importance and different types of SQL.

Table of Contents

• Introduction
• What is the difference between the conventional way to store data and the database management concept?
• What is a Database Management System?
• Key points for a Database Management System (DBMS)
• DBMS VS File system
• What are the types of DBMS available?
• Advantages of Database management System
• Most used Databases Management systems Software
• What is a Relational Database management System?
• Difference between DBMS And RDBMS
• Types of keys in RDBMS
• What is SQL?
• Data types in MySQL
• Types of SQL Statements
• What is DDL?
• What is a DML command?
• What is a DQL command?
• What is a DCL command?
• What is a TCL command?

Introduction

There is an Old renowned University which was built by a British in 1950. Since then, they were using the conventional method to keep track of their students. Suddenly for an urgent requirement, they need to find records for their 50 years, old students. When authorities decided to find the records they faced the biggest challenge to recover them. As all the past records are mostly in excel or in the pen-paper it becomes impossible for them to recover them. 

So they decided to migrate their database from the conventional method to a proper database management system.

Now a few things to understand and figure out to start with the SQL concept:

What is the difference between the conventional way to store data and the database management concept?

Conventional ways are like using excel or any kind of paper documentation so when we try to process a huge volume of data by using these methods it becomes a mess.

So first, let’s talk about the cons of paper documentation, maintaining the data and retrieving data is almost impossible. It’s very hard to replicate an old analysis on new data because manually all the process needs to be remembered. These steps are very dependent on manual operations.

Now, if we consider an excel tool to manage our data then first cons it becomes very slow to work with a huge amount of data. All these conventional ways are too dependent on manual operations.

So here, the database management system comes into the picture.

SQL Tutorial: What is a Database Management System?

A database is referred to as a collection of data where storing, retrieving, and accessing information becomes easy. 

Key points for a Database Management System (DBMS)

Process Huge volume of data

Typically a database needs to store and manage a huge amount of data for any point in time.

Maintaining sanity check and accuracy of data

A database is meant to be accurate as it has all sorts of built-in constraints, checks to maintain the sanity of the data. So it can be claimed that all the information available in a database is acceptable for most cases

Update data 

In a database, updating any existing data is much easier to work with. It’s required one single query to modify the data.

The most important check is how secure your data is?

N numbers of ways are there in databases to ensure the security of data. Only authorized users are enabled to access any data from the database.

DBMS VS File system

File System	DBMS
The file system is basically a collection of data and any management with the file system has to write the procedures.	DBMS is a collection of data but the user is not required to write the procedures to manage the database.
File systems are not efficient for storing and retrieving data	DBMS is efficient to use as there are large varieties of techniques to store and retrieve the data.
The file system doesn’t have a crash recovery mechanism. Example: While we are entering some data into the file if the System crashes then the content of the file is lost	DBMS has a crash recovery option, it protects users from the effects of system failures.
Protecting a file using a file system is very difficult.	DBMS has its own security process to save the data

What are the types of DBMS available?

• Relational database: A relational database management system (RDBMS) is used to organize data in two-dimensional tables using rows and columns. Relational DBMS are mostly used DBMS models because of their flexibility and easy-to-implement nature. This model is worked on normalizing data in the database. Relational models store the data in fixed structures and manipulate them using SQL.

• Object-oriented database: Data is represented in the form of objects and this is the combination of relational database concepts and object-oriented principles. It maintains a structure which is known as classes and displays the data within that. It describes the database as a collection of objects and stores data members’ values and operations.
• Hierarchical database: The data matrices have one to many relationships (1: N). It is organized in a tree-like structure which is the same as a folder structure of any computer system. Data are stored in hierarchical (top-down or bottom-up) order and also they are represented by the parent-child relationship.
• In Hierarchical DBMS → parents can have many children, but the children can only have one parent.
• Network database: The data matrices have one to one relationship (1: 1) or many to many relationships (N: N). The concept of the network database model allows all the children to have multiple parents and also it helps to solve the more complex models like the orders/parts many-to-many relationship. Entities are organized in a graph manner which has an option to be accessed through several paths

Advantages of Database management System

• DBMS has lots of techniques to store, manipulate, and retrieve data
• DBMS is considered the most efficient handler to balance the data
• A DBMS uses lots of powerful functions to store, manipulate and retrieve data efficiently.
• Data Integrity and Security is one of the strong parts of DBMS
• The DBMS uses data integrity to protect data and maintains the privacy
• Helps to reduce Application Development Time

Most used Databases Management systems Software

• MySQL
• Oracle
• PostgreSQL
• SQLite
• Maria DB

Now, we will move ahead in this SQL tutorial and we will focus on relational database management system:

What is a Relational Database management System?

Difference between DBMS And RDBMS

DBMS	RDBMS
DBMS stores data as a file.	RDBMS stores data in a tabular form.
In DBMS, data is stored in two ways: hierarchical form navigational form	In RDBMS: the tables have a classifier known as the primary key and the data values are stored in the tabular format.
Normalization is not needed in DBMS.	Normalization is needed in RDBMS.
DBMS does not have any security for data manipulation.	RDBMS has the integrity constraint for the purpose of ACID (Atomicity, Consistency, Isolation, and Durability) property.
In DBMS  there is no relation between the tables.	in RDBMS there should be a relationship between the data values.
DBMS is used for small organizations and always deals with small data. it also supports a single user.	RDBMS is used for large data and also supports multiple users.
Examples: file systems, XML, etc.	Example:  mysql, postgre, SQL server, oracle, etc.

Types of keys in RDBMS

The next topic that we will cover in this SQL Tutorial is what is SQL.

SQL Tutorial: What is SQL?

To deal with RDBMS we need a  standard language, which is known as SQL. This language can be used to insert, search, update, and delete database records, and simply, it is easier to manipulate data with SQL. 

What is the Full form of SQL?

SQL stands for “Structured Query language”.

Why do we use SQL?

• It is easier to access data in the RDBMS system.
•  Adding describe of the data becomes easy.
• Defining the particular database for the data and also manipulating them is one of the main key tasks for SQL
• Securing the data becomes much easier with SQL

Data types in MySQL

A data type is an attribute that specifies the type of data that the object can hold:

• integer data,

• character data,

• monetary data,

• date and time data,

• binary strings, and so on

The next topic that we will cover in this SQL tutorial is types of SQL statements.

Types of SQL Statements

Here we have attached five types of widely used SQL queries.

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Transaction Control Language (TCL)
• Data Control Language (DCL)
• Data Query Language (DQL)

What is DDL?

• DDL–> Data definition language
• Helps to define the database schema
• Deals with a description of the database
• Also, have the power to deal with creating and modifying the structure of database object

Types of DDL commands

• CREATE – This command is used to create the database. It also has some objects like table, index, function, views, store procedure, and triggers
• DROP – DROP is mainly used to delete objects from the database
• ALTER –ALTER is used to alter the structure of the database
• TRUNCATE – TRUNCATE command is used to remove all records from a table which includes all memory allocated for the records are removed.
• COMMENT – It is used to add comments to the data dictionary
•  RENAME– This is used to rename an object existing in the database.

What is a DML command?

DML–> Data Manipulation Language

This DML command handles all the data manipulation part

It includes the most important parts of the SQL 

Types of DML commands

• INSERT – INSERT is used to insert the data in the table
• UPDATE – Helps to update existing data within a table
• DELETE – To delete records from a database table

What is a DQL command and give an example of DQL?

DQL–> Data Query Language

DQL is used to make queries on the data within schema objects

The main focus of the DQL Command is to get some schema relation based on the query passed into it

SELECT–>This command is used to retrieve all the data from the table

What is a DCL command and give an example of DCL?

DCL–> Data Control Language

Deals with the rights and permission of the database

Works for the controlling part of the data

GRANT–> provide user’s access privileges to the database.

REVOKE–>Helps to withdraw the user’s access privileges given by using the GRANT command.

What is a TCL command and give an example of TCL?

TCL–> Transaction Control Language

COMMIT– commits a Transaction

ROLLBACK – rollbacks a transaction for any error that occurs

SAVEPOINT– use a savepoint within a transaction

SET TRANSACTION–specify the characteristics for the transaction

As we have learned about the basic concept of DBMS and SQL. Now, in this SQL tutorial, let’s focus on how to create a student database management system:

First, create a database where we can store our data:

Command:

CREATE DATABASE student_database;

After creating the database we need one more command to activate that:

USE student_database;

Now create a table format where we can store our student data:

CREATE TABLE student
   (
       student_id VARCHAR(6),
       fname VARCHAR(30),
       mname VARCHAR(30),
       ltname VARCHAR(30),
       city VARCHAR(15),
       mobileno VARCHAR(10),
       branch VARCHAR(10),
       dob DATE,
      CONSTRAINT studentspk PRIMARY KEY(student_id)   
   );   

Now let’s insert a few dummy data to understand how to use the INSERT command:

INSERT INTO student VALUES('C00001','Ramesh','Chandra','Sharma','Delhi','9543198345','Service','1976-12-06');
 
INSERT INTO student VALUES('C00002','Avinash','Sunder','Minha','Delhi','9876532109','Service','1974-10-16');
 
INSERT INTO student VALUES('C00003','Rahul',NULL,'Rastogi','Delhi','9765178901','Student','1981-09-26');
 
INSERT INTO student VALUES('C00004','Parul',NULL,'Gandhi','Delhi','9876532109','Housewife','1976-11-03');
 
INSERT INTO student VALUES('C00005','Naveen','Chandra','Aedekar','Mumbai','8976523190','Service','1976-09-19');
 
INSERT INTO student VALUES('C00006','Chitresh',NULL,'Barwe','Mumbai','7651298321','Student','1992-11-06');
 
INSERT INTO student VALUES('C00007','Amit','Kumar','Borkar','Mumbai','9875189761','Student','1981-09-06');
 
INSERT INTO student VALUES('C00008','Nisha',NULL,'Damle','Mumbai','7954198761','Service','1975-12-03');
 
INSERT INTO student VALUES('C00009','Abhishek',NULL,'Dutta','Kolkata','9856198761','Service','1973-05-22');
 
INSERT INTO student VALUES('C00010','Shankar',NULL,'Nair','Chennai','8765489076','Service','1976-07-12');

How to check the table structure?

DESC TABLE_NAME;

How to check all the tables which are existing in the database?

SHOW TABLES;

How to update a new change in the existing table?

Update syntax:

UPDATE TABLE_NAME
SET column1 = value1, column2 = value2, ...
WHERE condition;

Now use the same to update a particular record for the student database management:

UPDATE student
SET fname = 'Hansa', City= 'Chennai'
WHERE student_id =c00010 ;

How to DELETE particular records from the current database?

DELETE statement is used to delete existing records from the table.

DELETE FROM TABLE_NAME WHERE condition;

Now let’s try to delete records for “Hansa”

DELETE FROM student WHERE fname=‘Hansa’ AND student_id =c00010

 Also Explore:

• 100+ SQL Interview Questions and Answers
• Understanding SQL Server Data Types

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.