Database in SQL​

Introduction to Database in SQL

A data structure that stores data in memory in an organized form, facilitating functions such as data manipulation and effective management of data, has a particular structure containing various tables. With each table being unique and storing data of a separate concept in the form of records represented by rows, and columns in the table representing fields, with the tables being linked to other tables through fields known as keys, is referred to as the database in SQL.

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 a 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 CREATE DATABASE statement creates the database in SQL.

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. The SQL ​DROP DATABASE​ statement is used to drop all the tables 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 the 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, the 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, the employee address of the phone 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 the 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

Various operations can be done in the SQL Database, and the queries can be written in many forms to access the data according to the requirement. Therefore, it is very important and considered a good practice to write optimized queries while doing any operation and retrieve only those required records at that particular time. This increases the execution speed and saves memory.

Recommended Articles

This is a guide to Database in SQL​. Here we discuss the introduction, how to create, drop, fetch and update a Database in SQL. You may also look at the following articles to learn more –

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