Database in SQL​ 

Introduction to Database in SQL

As we all know that data is basically the facts, information about a particular object or an entity. For example, if we talk about the data of an Employee it contains Employee Information which can be both personal and Corporate Information. Personal Information includes name, address, phno, Spouse, Dependent, etc whereas Corporate Information includes Employee department, salary, projects, manager, etc. In this topic, we are going to learn about the Database in SQL. It is easy to note down and manage the data of a single entity in a notebook but a company has thousands of employees. To access and manage huge amount of data, we need a software or a system that can help in its easy and efficient access, handling and updating of data. For this reason, the database came into the picture. By definition, a database is a systematic collection of data that supports the storage and manipulation of data.

Databases also provide indexes for easy access to data. Databases can be relational and non-relational depending on the type of information the user wants to store. Relational databases are those in which data is stored in the form of rows and columns in a table whereas non-relational databases do not use tabular format to store the data instead the data is stored either in graphical, document, columnar or key-value form. SQL is a relational database and hence stores the values in rows and columns format. Every column in SQL is called a field that is designed to store specific information about a record. Information/data of a single entity is stored in a record/ row having valid values of all columns. Columns have a vertical structure in SQL and rows have horizontal.

How to create a Database in SQL?

Before storing and accessing any information in a database, the first step is to create a database. The database in SQL is created by ​the CREATE DATABASE​ statement.

Syntax

CREATE DATABASE​ db_name;

In the above syntax, db_name is the name of the database that the user wants to give to the new database. It is necessary that the user must have admin privileges to create a new database.

Example

CREATE DATABASE employeeDB;

It will create a new database in SQL with the name employees. We can also check the above-created DB using the command given below:

SHOW DATABASES;

It will show the list of all the databases which are present in the SQL server till then.

How to Drop a Database in SQL?

Dropping a database means deleting an existing database from SQL Server. It is a good practice to delete the database that is not in use as it saves memory. DROP is a DDL command. In SQL ​DROP DATABASE​ statement is used to drop all the table that exists inside the particular database and delete that database.

Syntax

DROP DATABASE​ db_name;

In the above syntax db_name is the name of the database which we want to delete. One must be very careful while using the DROP command as the operations of the DROP command cannot be rolled back. Users must have admin privileges to drop the database.

Example

DROP DATABASE employeeDB;

If we try to delete a database that does not exist, SQL will show an error for the missing database which we are trying to delete. After dropping any database, we can check using the SHOW DATABASES; command and the deleted database will not be present in the list of available databases.

How to Fetch the Data from the Database?

After storing/ inserting the data into the database tables, one of the important tasks is to fetch the data and display the results to the user in a particular format. In SQL, the SELECT statement is used to retrieve the data from the database. The SELECT statement can be used in different forms according to the requirements of the user of what data it wants to fetch.

• To fetch all the data of database * is used.
• To fetch specific columns from the database, the column name is written after the SELECT keyword.
• In order to retrieve data according to some conditions WHERE clause is used along with the SELECT statement.

Syntax 1: To fetch all the data from the table.

SELECT * FROM tb1;

In the above syntax, tb1 is the name of the table from which we want to fetch the data. (*) is used to retrieve the data of all the fields/columns present in the database.

Example 1

Consider a table name ‘Emp_details’ having the various columns and data mentioned below:

Emp_code	Emp_name	Emp_city	Emp_phno
101	Rahul	Noida	7894561236
102	Ankit	Delhi	9236547896
103	Sonam	Agra	9256347895

Example 2

SELECT * FROM Emp_details;

Output:

Number of Records: 3

Emp_code	Emp_name	Emp_city	Emp_phno
101	Rahul	Noida	7894561236
102	Ankit	Delhi	9236547896
103	Sonam	Agra	9256347895

Syntax 2: To fetch specific columns from the table.

SELECT column 1, column 2, column... from tb1;

Example

SELECT Emp_code, Emp_name, Emp_address from Emp_details;

Output:

Number of Records: 3

Emp_code	Emp_name	Emp_city
101	Rahul	Noida
102	Ankit	Delhi
103	Sonam	Agra

Syntax 3: To fetch data according to the given condition.

SELECT * from tb1 WHERE Emp_code =103;
Output: Number of Records: 3
Emp_code	Emp_name	Emp_city	Emp_phno
103	Sonam	Agra	9256347895

How to Update the Database in SQL?

It is not necessary that the values once stored in the table remain the same in the future. For example, in an employee table, employee address, phno can be changed and needs to be altered in the database once reported. To update the values of the table in the database, ​the UPDATE​ statement is used in SQL. UPDATE modifies the values in the database of one or more records at a time according to the specific condition given by the user.

Syntax

UPDATE tb1 SET col 1= val 1, col 2= val 2 WHERE condition;

In the above syntax, ‘tb1’ is the name of the table on which data we want to make updation and ‘col 1’, ‘col 2’ are the name of columns of table whose values we want to modify and ‘condition’ specifies the condition of selection of rows on which basis updation needs to be done.

Example

UPDATE Emp_details SET Emp_phno = ‘8746456789’ WHERE Emp_code = 101;

 Output:

 Rows affected: 1

 We can check the updation of Emp_phno of Employee having Emp_code 101 by fetching the table data:

SELECT Emp_code, Emp_phno from Emp_details WHERE Emp_code=101;
Output: Number of Records: 1
Emp_code	Emp_phno
101	8746456789

Conclusion

There are various operations that can be done in the SQL Database and the queries can be written in many forms in order to access the data according to the requirement. It is very important and considers a good practice to write optimized queries while doing any operation and retrieve only those records which are required at that particular time as this increases the execution speed and saves memory.

Recommended Articles

This is a guide to Database in SQL​ . Here we have discussed How to Update the Database in SQL and How to Fetch the Data from the Database. You may also look at the following article to learn more –

1. SQL Server Constraints
2. What is SQL Injection?
3. How to Connect Database to MySQL?
4. PostgreSQL String Functions