From the time you have known computers, you are also familiar with memory. You understand that computers need to store data to function smoothly. Now, in a computer system, it is very important to have a system that lets you search, select, and store information easily. This is where a database comes into the picture. As Oracle mentions, a database is an organized collection of structured information that is typically stored electronically in a computer system. The data is generally stored in the form of tables with rows and columns in a database so that its processing becomes easier.
You must have already come across such rows and columns in tables while working in Microsoft Excel. Well, spreadsheets are a convenient way to organize data when a single user is working on it, and there is no complex data manipulation involved. On the other hand, databases are capable of holding much larger sets of organized information, and multiple users can simultaneously work on accessing and querying the data. Now, you can control a database using a database management system (DBMS). Using a DBMS, a database can be easily accessed, managed, modified, updated, organized, and controlled. To perform all the operations on a database, a programming language called SQL (Structured Query Language) is used.
Let us give you an introduction to SQL and what purpose it serve when working on a database.
SQL Explained
Dated back to the 1970s, SQL or Structured Query Language is still the most popular programming language when it comes to relational databases.
By relational databases, we mean those in which the data is stored in a tabular format. For example, here is an image showing a simple dataset:
Some of the commonly used relational database management systems are MySQL, Oracle, PostgreSQL, and MSSQL.
SQL is used to modify database tables and index structures, add, delete, or modify rows or columns of data, and even retrieve subsets of information from within relational database management systems. To do so, SQL queries take the form of commands that are written as statements as a part of the program.
SQL commands are divided into a number of categories; some of the popular ones are mentioned here:
- Data Definition Language (DDL) commands
- Data Manipulation Language (DML) commands
- Data Query Language
- Data Control Langugae
- Transaction Control Language
Some of the widely used SQL commands are:
- CREATE
- DELETE
- INSERT
- UPDATE
- ALTER
- SELECT
- DROP
- WHERE
Taking the above table ‘customers’, for example, let us see how these commands work.
SELECT – This command is used to select any particular part of a table, be it a set of rows or columns or others. Here is how the syntax goes:
SELECT first_name FROM customers;
This statement has been used to select one entire column from the table. In other words, the data of the column ‘first_name’ is extracted from the ‘customers’ table using the SELECT command.
INSERT – This command can be used to add new rows in any table of a database. Let us add one more row to the Customers table using the following syntax:
INSERT INTO Customers(customer_id, first_name, last_name, age, country)
VALUES
(5, ‘Joe’, ‘Peralta’, 35, ‘UK’);
When this statement is executed, it will add a new row to the table with the values mentioned.
UPDATE – This statement is used to modify the existing data of rows in any database table. There are many situations where you need to change the data in a specific row or column without modifying the entire table. You can use the UPDATE statement as shown below:
UPDATE Customers
SET first_name = ‘Amy’, last_name = ‘Diaz’
WHERE customer_id = 2;
For this statement, we have mentioned the name of the table, what row data needs to be changed to new values, and from where. Here, the statement says that in the Customers table, the data needs to be changed where customer_id is 2; meaning ‘Robert’ will be replaced by ‘Amy’ and ‘Luna’ will be replaced by ‘Diaz’. Similarly, you can modify the other rows of a table as well.
Using these commands and others, you can use SQL in databases and perform a range of operations on it. You can control the information stored in databases or retrieve the specific piece of information as and when you need it. Knowledge of SQL is useful for many IT job roles like back-end developers, database administrators, system administrators, full-stack developers, and system engineers. In fact, with the onset of data-related job roles, SQL has become a must-know skill for roles like data engineers, data scientists, data analysts, data architects, and so on.
Overall, SQL is an ANSI and ISO standard and remains the de facto standard database query language. Apart from relational databases, there are some non-relational databases that are designed to handle unstructured data and do not use SQL for manipulating data. Such databases are also called NoSQL databases, and some of the popular ones in this category are MongoDB, DynamoDB, Apache Cassandra, and Apache HBase.
If you haven’t learned SQL yet, it is high time that you start looking for an online course and gain expertise in the language to establish your career in the IT landscape.
