Image- Umang Bhalla

Database Management System with SQL

In this article, I will be taking you, readers, on a tour of getting started with Database Management Systems (DBMS). And at the end, I will teach you how to make a simple database using SQL queries.

Why DBMS?

Since the beginning of the digital era, there has been the need for data and well storing the data. Now we can store this data on our laptop as files and folders but when working with large amounts of data that isn't possible.

So we need DBMS to store the data in a data warehouse or a server and be able to retrieve it whenever need be without having to connect to the physical storage device.

We use DBMS for:

• Creation of a database.
• Retrieval of information from the database.
• Updating the database.
• Managing a database.

This work on a beginner level is done using languages like SQL. Before using SQL we must know the types of commands present in SQL.

DDL vs DQL vs DML

These SQL commands are mainly categorized into these categories:

1. DDL — Data Definition Language
2. DQL— Data Query Language
3. DML — Data Manipulation Language

DDL commands are commands used to well define a database.

In SQL these are :

1. CREATE: This is used to “create” new databases or objects ( like tables or views).
2. DROP: This is the delete command of SQL and is used to delete previously created databases or objects.
3. ALTER: This is used to change the structure of the database
4. TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the records are removed.
5. RENAME: This is used to rename the objects present in a database or even the database itself.

DQL commands are used to get the data from the database and show it to the user.

The most used DQL command is SELECT which is used to retrieve data from the database.

DML commands are used to make changes to the data present in the database.

DML SQL commands are:

1. Insert: Put data into a table
2. UPDATE: To update existing values
3. DELETE: Delete records inside the database.

Notice the similarity between DELETE and TRUNCATE, so basically TRUNCATE means delete everything.

Keys

No, here I do not mean your car keys or house keys.

The keys used in DBMS are to uniquely identify a record in the database and also create a relationship between tables inside a database.

Types of Keys:

1. Primary Key
2. Candidate Key
3. Alternate Key
4. Foreign Key

Primary Key:

Used to identify one part of the database table uniquely.

This comes with a few conditions.

1. Primary Key can never be NULL
2. All values of the Primary key must be different.

There can never be more than one primary key but we can use more than one column as a Primary key.

For example, I’ll take my college’s database.

Let’s assume I take the First name of the student as the primary key. That would be a big problem because I have 5 students named Gaurav in my batch alone.

Now we can take 2 columns First and Last name. This partially solves the problem but there is an exceptional case to this. There are 2 students named Nitin Gupta in my batch and both are of CSE branch.

So instead of using any of these values as the Primary key, we can just take the Roll Number as it is made in such a way that it is unique and each student has been given one.

Candidate Key

All columns ( attributes) that are not Primary keys are candidate keys.

So in the college database, if roll number is primary key values like first name, last name, branch etc become candidate keys.

Alternate Key

Keys that are unique and satisfy the criteria of Primary Key but are not used as Primary key.

Since there is only one primary key the others that fit into the description become Alternate Keys.

Again in the college database if Roll Number is the Primary key then stuff like Phone Number or email becomes alternate key.

Time to Code some SQL:

For this let’s take the database I used in the examples above.

So in my college database we will need a table of my batch i.e. batch24 with various values.

So first things first we need to make sure a college database doesn’t already exist and will use the command to delete the pre-existing one ( if any)

DROP DATABASE IF EXISTS college;

Next up we create our database using:

CREATE DATABASE college;

Now we need to enter into the college database for which we use:

USE college;

Now it’s time to create a table:

CREATE TABLE batch24 (roll_number VARCHAR(8) PRIMARY KEY , first_name VARCHAR(100) , last_name VARCHAR(100) , phone_number INT , branch VARCHAR(3) );

This has created a table named batch24 with columns roll_number, first_name, last_name, phone_number and branch.

VARCHAR and INT specify the datatype of the given values.

Now let’s put some values in the table:

INSERT INTO batch24 (roll_number, first_name,last_name,phone_number,branch) VALUES (‘20U03001’, ‘Gaurav’, ‘Gupta’,999999999,’IT’),(‘20U02003’,’Nitin’ , ‘Gupta’, 212344356,’CSE’),(‘20U02023’,’Nitin’ , ‘Gupta’, 21344356,’CSE’),(‘20U01003’,’Gaurav’ , ‘Kuch bhi ‘, 455234435,’ECE’),(‘20U03043’,’Umang’ , ‘Bhalla’, 712344356,’IT’),(‘20U03013’,’Tanav’ , ‘Bajaj’, 212343456,’IT’);

This has inserted values in the batch24 table. Here the first () shows the order in which the values will be inserted and the rest brackets are the values in the predecided order.

The database with one table and a few values is ready.

If you tried these commands along with me your command line may be pretty empty

Here we can use the select statement to see what all we have in the database.

SELECT * FROM batch24;

“*” here means all. So the above statement can be read as Select all the values from batch24 table.

We can also use the WHERE command to filter it out.

SELECT * FROM batch24 WHERE branch=’IT’;

This shows all students that belong to the IT branch.

Remember I told you the case where 2 students had the same name and were separated by the primary key ( roll_number) and alternate key ( phone_number)

SELECT * FROM batch24 WHERE first_name=’Nitin’ AND last_name=’Gupta’;

We can use this command to see such cases.

Stay Tuned for more such Articles

Mlearning.ai Submission Suggestions

🟠 Become a Writer at MLearning.ai