In this article, we will see how to manage an SQL Database.
1. Introduction
SQL is a standard language for storing, manipulating, and retrieving data in a database. SQL stands for Structured Query Language. SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987. Although SQL is an ANSI/ISO standard, there are different versions of the SQL language. Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard
2. RDBMS
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and consists of columns and rows.
Let us say we want to store all the employees’ data in a table. We will create a table Employee which will have attributes like Name, Id, Address, Department, etc. These fields will be referred to as columns in database terminology. A column is a vertical entity in a table that contains all information associated with a specific field in a table. A record, also called a row, is each individual entry that exists in a table. A record is a horizontal entity in a table.
3. SQL Database Example
In this section, we will see some working examples of SQL.
We will use SQLite. SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. I am using Mac and SQLite comes to build in. Type SQLite on the terminal to check if it is installed on your machine or not:
~$ sqlite3
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
Let us first create a table where we will store the employee data. Run the below command in the SQLite prompt:
CREATE TABLE employee (
employee_id INTEGER PRIIMARY KEY,
first_name TEXT NOT NULL,
surname TEXT NOT NULL,
email TEXT NOT NULL,
department TEXT
);
To check if the table got created run .tables command. Now let us insert some data into the employee table:
INSERT INTO employee(employee_id, first_name, surname, email, department)
VALUES (1, 'Mike', 'Hertz', 'mike.hertz@jcg.com', 'Operations');
INSERT INTO employee(employee_id, first_name, surname, email, department)
VALUES (2, 'Ahmed', 'Basheer', 'ahmed.basheer@jcg.com', 'HR');
INSERT INTO employee(employee_id, first_name, surname, email, department)
VALUES (3, 'Andree', 'Coper', 'andree.coper@jcg.com', 'Finance');
We will insert three records. To check if the records are added successfully, run the below command:
select * from employee;
This statement is supposed to display all the records in the employee table:
sqlite> select * from employee;
1|Mike|Hertz|mike.hertz@jcg.com|Operations
2|Ahmed|Basheer|ahmed.basheer@jcg.com|HR
3|Andree|Coper|andree.coper@jcg.com|Finance
sqlite>
Let’s say we only want to see the data of the employee who works in the HR department. For this we need to use the WHERE condition in the SELECT statement:
sqlite> select * from employee where department = 'HR';
2|Ahmed|Basheer|ahmed.basheer@jcg.com|HR
sqlite>
We saw that this time only one record was returned. Now let us say we want to add a new column to an existing table – for this, we will make use of the ALTER TABLE command:
ALTER TABLE employee ADD COLUMN salary_band TEXT;
To check if the column was added successfully or not we can make use of table_info:
sqlite> PRAGMA table_info(employee);
0|employee_id|INTEGER PRIIMARY KEY|0||0
1|first_name|TEXT|1||0
2|surname|TEXT|1||0
3|email|TEXT|1||0
4|department|TEXT|0||0
5|salary_band|TEXT|0||0
To delete this column use the DROP command:
sqlite> ALTER TABLE employee DROP COLUMN salary_band;
sqlite> PRAGMA table_info(employee);
0|employee_id|INTEGER PRIIMARY KEY|0||0
1|first_name|TEXT|1||0
2|surname|TEXT|1||0
3|email|TEXT|1||0
4|department|TEXT|0||0
sqlite>
You can use the DROP command to delete the table as well. Find out more in our SQLite Tutorial.
4. Summary
In this article, we learned how we can manage a database using SQL. We looked at the usages of SQL and we discussed the Relational Database Management System (RDBMS). Then we looked at some of the most commonly used SQL statements.
There are lot more SQL commands available. If you want to learn more about SQL you can check our tutorials here.
