So, what is SQL? SQL (pronounced “sequel” or “es-cue-el”) stands for Structured Query Language and it is the standard language used for accessing databases.
In the 1960s data management was complicated work. It entailed lots of file opening, reading and writing. Software developers back than understood the problematic of the situation and found a way to make the computers do all the dirty work. This was the beginning of database management systems. They made programs that ran on the database server that accepted and processed high level commands instructing the computer which data to return. In a nutshell, the system took care of file management. But even then, database management was a nightmare. It was not until the 1970′s when Edgar Codd made a breakthrough in database management. He transformed the craft into science by proposing a new way for organizing data which reliedon a strong mathematical foundation called the relational model. Most databases nowadays stilluse the relational model and some variation of the SQL language, used to query them, based on his idea. For example, Microsoft SQL Server uses the Transact SQL query language, otherwise referred to as T-SQL and Oracle is using its own variation of SQL named PL/SQL. Therefore, to understand what a database and SQL are we’ll first need to take a look at the relational model.
Imagine that you run a computer store, and you need to inventory the stock you have. In order for the inventory to make any sense, first of all, you would need to define what a computer is. “That’s easy,” you say, “a computer is a motherboard, a CPU, some RAM, a hard drive and a video card”. Congratulations! You’ve just used the relational model to organize data. Namely, in the relational model, a category of objects, called a type, (in this example, a computer) is defined by the relationship between its attributes (in this example, the computer’s components). Now inventorying the stock is a piece of cake – all you need to do is to write down the exact make of every component for each computer and you are done.
Let’s say that you have a lot of computers you need to inventory. Rather than writing Motherboard – make, CPU – make and so on and so forth for every single configuration, you could draw a table, label each column with one component type, and then just write the make of that particular component for each computer in each column.
Then, rather than just filling in the columns randomly, you could intersect the columns with rows and make sure that all the components from the same configuration are written in the same row. This way, when you need to locate a particular computer in your inventory, all you need to do is find the row where its components are located. Guess what? You’ve just created a database!
Basically, a database is just a way of storing, organizing and managing data. In a database, data is stored in a table, just like the one mentioned above, consisting of columns and rows. The same way that the table discussed above represents all the computers you have in stock as a relationship between their respective components, a database table actually represents a relationship (e.g. all computers in stock), a column represents an attribute of that relationship (e.g. a component type) and a row represent a particular instance of the relationship (e.g. a particular configuration)
Now that your computer stock is inventoried, that inventory must be maintained or soon it will become pretty much useless. If you did your inventorying manually you’d either need to maintain it yourself or hire someone to do it for you. However, if you stored your data into a database, a piece of software called Relational Database Management System (RDBMS) will do the maintenance for you including controlling data access, enforcing data integrity, managing concurrency control, recovering the database after failures, as well as maintaining database security. Microsoft SQL Server and Oracle, mentioned earlier, are one of the most popular RDBMS of today. So, how can one communicate with the RDBMS, or store, access or change database data? This is where SQL comes to play.
In a nutshell, SQL is the language used to communicate with the RDBMS and access the database. Even though it changed through history, SQL is a standard of the American National Standards Institute. Unlike most programing languages, which are procedural, SQL is a descriptive language at its core (although it has procedural constructs as well); therefore it’s only logical for its command to be referred to as queries. To illustrate the difference between SQL and procedural languages, let’s say that you are writing a program for a robot that will allow it to take three apples from a basket. In a procedural language, you would write something along the lines of:
take_apple();if number_of_ apples < 3
take_apple();
else
return();
In SQL you can express more naturally:
SELECT TOP 3
FROM basket
The query above contains one of the most commonly used SQL statements – SELECT. The SELECT statement allows you to retrieve (and view) the data stored in the tables. Besides SELECT, the most commonly used statements are Data Manipulation Language (DML) statements and Data Definition Language (DDL) statements. Besides SELECT there are three DML statements – INSERT, UPDATE and DELETE which allow inserting, changing and deleting data for a table. CREATE, ALTER and DROP are DDL statements with similar functionality to their DML counterparts – they are used to crate, change or drop database objects such as tables.
To sum up, today databases are the best way to store, organize and manage your data. Inside databases data is organized into tables. Databases are managed using RDBMS and the data they contain is accessed and manipulated via SQL. So next time when you need to store some data, for instance, if you need to inventory something, let the pen and paper be, and give databases a try. But, wait! What if you don’t just need to inventory your current stock but also keep track of how much you’ve sold and who your suppliers are? This is where databases really shine – but more on that in another article, where I’ll write more about SQL tools for DBAs and developers.
