An overview of SQL
The Structured Query Language (SQL) comprises one of the fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms. At first glance, the language may seem intimidating and complex but it’s really not too difficult to learn on our SQL Training sessions. The information in this blog post should give you the fundamental knowledge you need to go and experiment with SQL in your database projects, even within Microsoft Access, Microsoft Excel Power Query or PowerBI.
Here we will provide an essential overview of SQL — not a mastery of the individual commands – come on one of our highly rated SQL training courses in Glasgow or Edinburgh to learn in depth! In this post you will find a few examples for illustrative purposes and an explanation of the theory behind them, but don’t feel frustrated if you can’t write your own SQL commands after this short introduction. If and when you want to delve more deeply into SQL there are plenty of books and short courses to help you.
To start with, the correct pronunciation of SQL is a contentious issue within the database community. In their SQL standard, the American National Standards Institute declared that the official pronunciation is “es queue el.” However, many database professionals have taken to the slang pronunciation “sequel.” The choice is yours.
SQL comes in many flavours. Oracle databases utilize their proprietary PL/SQL. Microsoft SQL Server makes use of Transact-SQL. However, all of these variations are based upon the industry standard ANSI SQL. In our tutorial series, we’ll stick to ANSI-compliant SQL commands that will work on any modern relational database system.
SQL commands can be divided into two main sublanguages. The Data Definition Language (DDL) contains the commands used to create and destroy databases and database objects. After the database structure is defined with DDL, database administrators and users can utilize the Data Manipulation Language to insert, retrieve and modify the data contained within it. In the next two sections, we’ll explore DDL and DML in further detail.
Data Definition Language
The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let’s take a look at the structure and usage of a basic DDL command:
SELECT
The SELECT command is the most commonly used command in SQL. It allows database users to retrieve the specific information they desire from an operational database. Let’s take a look at a few examples, again using the personal_info table from our employees database.
The command shown below retrieves all of the information contained within the personal_info table. Note that the asterisk is used as a wildcard in SQL. This literally means “Select everything from the personal_info table.”
SELECT *
FROM personal_info
Alternatively, users may want to limit the attributes that are retrieved from the database. For example, the Human Resources department may require a list of the last names of all employees in the company. The following SQL command would retrieve only that information:
SELECT last_name
FROM personal_info
Finally, the WHERE clause can be used to limit the records that are retrieved to those that meet specified criteria. The CEO might be interested in reviewing the personnel records of all highly paid employees. The following command retrieves all of the data contained within personal_info for records that have a salary value greater than £50,000:
SELECT *
FROM personal_info
WHERE salary > £50000