Structured Query Language (SQL)
SQL is a domain-specific language that is standardized for managing relational databases. Relational databases store data in a table where columns, also known as attributes, contain details about the data such as Name, Address, City, etc., and rows contain the actual data. Data from each row corresponds to a particular person or thing. Generally, each table has a primary key that uniquely identifies a specific row from the table. A typical representation of the table is shown in the following.
In the above customer table, the first row consists of columns/attributes that represent a particular category of information. In addition, multiple tables can be related as a form of parent and child relationship: where the parent table’s primary key appears in the child table as a foreign key so that a parent’s table can have additional information that can be referred to by using the child table’s foreign key. This way redundancy can be removed significantly since foreign keys may contain duplicate values and the primary key appears only one time. For instance, in the above table Country attribute can be a foreign key, and if the table has rows of more than 10000 then the values of the Country attribute will be repeated. Therefore, if we add extra information about the country, such as the president, prime minister, etc., to the same table those values are also repeated. Instead, we can have another table named Country where the Country attribute is a primary key, therefore it will be unique, and all the information related to the Country can be shifted to the Country table.
Data Manipulation Language (DML)
Get Jeyaganesh Moorthiyamma’s stories in your inbox
Join Medium for free to get updates from this writer.
Data Manipulation Language (DML) is a subset of SQL, and it is used to maintain the data stored in relational databases using its command. This allows the user to handle the data very effectively. The following are the key DML functions.
- Querying data: Retrieving specific information from a particular table or a group of tables residing in the relational database.
- Updating data: It modifies the existing data and makes the database up-to-date.
- Inserting data: It populates the database with new values.
- Deleting data: Removing the existing data.
- Creating and managing database structures: Defining and altering the structure of databases and tables.
The SQL query function can be used to fetch specific data from large databases by providing some parameters. Here, the parameter is nothing but the attribute values so that it can fetch the specific row or set of rows by using those attribute values as a filter. This could save a lot of time. For example, let’s assume an IPL database that has ball-by-ball information about all the IPL matches happened so far, which can easily contain hundreds of thousands of rows. Let’s say that you want to find out how many sixes Virat Kohli has hit so far, this can be easily fetched by using just a few lines of SQL Query as shown in the following.
SELECT COUNT(*) FROM deliveries
WHERE batsman=’Virat Kohli’ & batsman_runs = 6;
Here a COUNT(*) is an aggregate function, which is one of the features of SQL, that takes a set of rows as input and returns just one row containing the integer value that represents the total number of rows this aggregate function took as an input. WHERE clause has been used to filter the rows by taking specific attribute’s value. Similarly, we can filter while updating as well as deleting data from the database. SQL query always returns a row or set of rows as an output but we can select particular attribute(s) by specifying its name after the SELECT clause. And FROM clause is used to select the table(s) from where the data has been fetched.
