Easy management!A database is a system that allows data to be easily stored, organized and managed.
Now that we know what a database is, we now can focus on SQL.
It is an abbreviation for Structured Query Language.
As the name suggests, it’s merely a language to query databases.
In a way, SQL is the standard for interfacing with databases.
It goes back to 1970s, when it was initially developed by IBM.
It was originally called SEQUEL (Structured English Query Language) because it was easily readable and understandable.
It has transformed over the years and now remains the most dominant method of interacting with databases.
SQL is the means of communication with a database.
An essential overview of databases is required to grasp how SQL works.
At its simplest, a database is made of rows and columns, similar to a spreadsheet, but it’s far more powerful and has a vast number of features.
Data is categorized and stored in the form of tables.
For example: a company might have a database with a couple of tables, one for employees and another for departments.
Each row in a table is called a record, and column a field that describes the row.
In an employee table, each row is a unique person, and fields like name, address, department describe this particular employee.
Relational databasesA relational database is a structural form of database that stores data in tables, and these tables can be somehow linked to each other.
In the example of our company database, the employee table can be linked to a department table.
The relation here is that the employee belongs to a department.
In a traditional relational database, employee and department data can be presented as the two tables below, where DeptNo relates to the department table.
Employee tableDepartment tableThe obvious visible advantage here is that if the Purchasing department decides to rename itself as Investments, it needs to be renamed in only one place.
Flavors of SQLSQL has been widely accepted as the language for data management, and that’s there are many different implementations of the language and its functionalities by different vendors.
Here is a short list of these implementations:T SQL: developed by Microsoft, this is the proprietary procedural language used in Microsoft SQL Server.
PL/SQL: is the procedural language used by Oracle.
PL/pgSQL: is the procedural language used by PostgreSQL.
These procedural languages are designed to add more functionality to existing capabilities of SQL, but no matter what you choose, to comply with the ANSI standard, the fundamental SQL queries remain the same across all implementations.
SQL QueriesQueries can be categorized as DML (Data Manipulation Language) and DDL (Data Definition Language).
DML queriesSelect: This query is used to display a collection of records.
It is comparable to the ‘print’ statement in programming languages.
Select * from Employee — get all the records from employee tableInsert: Used to insert data into a table.
Insert into Employee (EmpNo,EName,DeptNo)Values (104,'Doug',20)Employee table after adding new employee ‘Doug’Update: A statement to update existing values in a table.
The following update query changes the department of an employee named Doug:Update Employee set DeptNo = 10 where EName = 'Doug'Delete: As you might expect, delete is used to delete records from a table.
Looks like our company is not so fond of Doug:Delete from Employee where EName = 'Doug' – he's fired.
DML queries operate on the level of data.
Just by using the four SQL queries shown above, you can explore databases in great detail.
DDL queriesCreate: Used to create a new table in a particular database.
The query below creates a new table: Interns.
Maybe Doug gets another shot.
Create table Interns (StudentId int ,Name varchar(50)) -creates new table InternsAlter: Alter is a multi-functional statement that can add a column, drop a column, modify a column, rename a column, or rename a table.
Alter table Interns Add (DeptNo int) -adds new column DeptNoDrop: Used to remove table definitions including all the data.
The query below needs no explanation.
Drop table Interns – Oops!!.Sorry Doug.
As opposed to DML, DDL queries operate on the object level and are used to modify, add, or delete definitions of tables and other objects.
Besides these two categories, there are many more, like DCL (Data Control Language) and TCL (Transaction Control Language).
The next article in this series will cover a hands-on exercise using SQLite—keep an eye out for it!.. More details