Phillip Burton

What is SQL?

hand pointing at computer screen, icons: document, gears, cloud, search

SQL (pronounced either “es-cue-el” or “sequel”), which stands for Structured Query Language, is a versatile, open-source query language. It is used to write queries for managing data in structured, relational databases. Some call SQL the “lingua franca” of database management for its versatility, simplicity, and ability to organize and manage large amounts of data in a standardized, tabular format.

Despite there being many alternatives, SQL remains the world’s most widely used database language. SQL forms the basis for next-generation database managers such as Oracle, MySQL, PostgreSQL and Microsoft T-SQL. For these reasons, SQL skills continue to be in high demand across almost all industries. It can be a key technology for developers, data architects, and data analysts. Even if they use other programs like Excel for data analysis, SQL skills are useful to retrieve the data from databases.

70-461, 761: Querying Microsoft SQL Server with Transact-SQL

Last Updated November 2021

Bestseller
  • 280 lectures
  • Beginner Level
4.5 (8,720)

From Tables and SELECT queries to advanced SQL. SQL Server 2012, 2014, 2016, 2017, 2019, exams 70-461 and 70-761 | By Phillip Burton

Explore Course

SQL: The standard for relational databases

Since the early days of the Internet, SQL has been the world’s standard query language for creating and managing relational databases. These are data management systems built on a tabular format containing rows (or records) and columns (or fields) of individual data entries. 

SQL is a highly versatile programming language for managing databases and creating the schemas (structure) for structured databases. These databases consist of tables containing data, views, stored procedures containing SQL statements, and more. You can also use primary and foreign keys to create relationships among different tables. It can also be very scalable. Azure SQL Database has a hyperscale tier, which allows the data to increase up to 100 terabytes. 

These features make structured relational data models both powerful and versatile. They work across multiple platforms and operating systems, with predictable and reliable operations. This makes it easy for websites to retrieve and display a site and for search engines to recognize and understand the content.

The history of SQL

Structured data models are the most traditional form of data storage. They have been in use continuously since the creation of the earliest versions of database management systems. Since SQL is the query language behind most of those systems, it remains at the core of structured database management even today. 

IBM’s Edgar F. Codd defined the relational database model in 1969. Built on a system of “keys,” or common information among different pieces of data, this model led to IBM’s development of its SQL dialect. IBM tested the earliest version of SQL in 1978. Within a few years, this relatively simple language based on familiar English commands became the basis for a number of SQL-based products designed for relational database management. 

In the mid-1980s, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) recognized SQL as the standard query language for managing any relational database management system, or RDBMS. They established standards for SQL providers to base their dialects on. These two standards have developed over the last few decades.

Different companies have created a variety of proprietary SQL programs. They have not stuck to the SQL standards, but have augmented its functionality. These dialects have similarly evolved over the decades, taking some of the new features in the Standard SQL and introducing additional features. 

The two most popular dialects are both owned by Oracle. Oracle SQL is Oracle’s own version of SQL, which is expanded with the features in PL/SQL such as conditions and loops. MySQL is free and can manage SQL databases for a variety of purposes. It was originally created by Sun Microsystems, which Oracle bought in 2010.

PostgreSQL is an advanced database management system operating on an advanced version of SQL. It has a procedural language of its own, called PL/pgSQL. Like other dialects, it adds its own syntax and a range of user-defined functions and query types.

Transact-SQL (or TSQL) from Microsoft and Sybase includes a proprietary group of programming extensions adding new features to SQL. T SQL functions include not only modifications of standard SQL queries and statements but also a variety of user-defined functions and triggers. For more information, have a look at my Udemy article “SQL vs. T-SQL: How Do They Compare?” 

How do spreadsheets such as Excel compare to SQL?

In a relational model, data is stored in databases in tables that have rows (or records) and columns (fields). In that way, relational database management systems are similar in structure and function to spreadsheet applications like Microsoft Excel, which organize data in workbooks, spreadsheets, rows, and columns. 

Excel can be efficient and easy to use for managing smaller data sets for single users. However, it lacks the scalability and speed for handling larger datasets or allowing multiple users to use it at the same time. Databases can manage far more structured data than standard spreadsheets can. They can scale to handle billions of rows in multiple tables that can connect through sets of common fields. Thousands or millions of users can access this data securely at the same time. For more differences, please see my Udemy article about the differences between Excel and SQL Server.

If you are an Excel expert, then the good news is that you do not have to choose between Excel or SQL Server — you can use them with each other. For more about how they can work together, please see my Udemy article “How to Use SQL in Excel.”

person sitting in office environment, with headphones, looking at computer screen

What are the alternatives to traditional database systems?

For years, structured data was the standard form for data storage, and SQL the standard querying language. With the advent of Web 2.0 and the rise of the Internet of Things, unstructured data from a multitude of internet apps and billions of constantly connected devices began to challenge the structured data model.

Unstructured data or semi-structured data is a broad term for information that can’t be organized according to a predefined data model. Unstructured data includes documents, audio, image, and video files. Semi-structured data includes data that has a more defined data structure, but not necessarily one which is constant. This includes formats such as XML, JSON, and data that can be stored in Azure Cosmos DB, MongoDB, Cassandra, and graph format.

For more about different types of database structures, have a look at my course “DP-900: Microsoft Azure Data Fundamentals.” In addition to relational data, this course looks at non-relational data on Azure, including Cosmos DB APIs, Table, Blob and File storage, together with analytics workloads and data visualization in Microsoft Power BI.

Why should you learn SQL?

Even with the proliferation of unstructured or semi-structured data, SQL remains the standard for querying databases. Even if a company uses another type of database management system, it will likely involve SQL syntax or one of its derivatives in some way.

That means demand remains high for professionals with a knowledge of SQL, and salaries for positions involving SQL skills can be higher than the average. A solid understanding of SQL can be valuable for a variety of positions, including:

Adding SQL knowledge to your resume or CV, in addition to other data-related skills and languages, can enhance your job opportunities across all kinds of industries needing database management or querying data in their daily operations.

Is it easy to learn SQL?

SQL’s straightforward and relatively simple syntax makes it easier to learn than some other programming languages. Mastering SQL involves learning its syntax and structure and practical exercises on writing queries and creating model databases.

SQL has an English-based query structure, so you will encounter familiar words like SELECT, INSERT, and DELETE. It is possible for beginners with little experience to learn the basics of SQL relatively quickly. You may need more time to learn more complex features and how to integrate SQL with other tools and software.

How can you use SQL?

SQL can execute a range of essential database management and querying functions, including:

Executing queries and retrieving data from the database

This uses the six principal clauses of the SELECT statement: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.

A valid SELECT statement could therefore be:

SELECT substring(name,1,1), count(*) as NumberOfRows
FROM sys.objects
WHERE name LIKE '%f%'
GROUP BY substring(name,1,1)
HAVING count(*)>1
ORDER BY NumberOfRows ASC, substring(name,1,1) DESC;

It is not necessary to use all six principal clauses in the same statement. All SQL dialects allow valid SELECT statements using just the SELECT and FROM clauses. It is possible in SQL Server for valid SELECT statements to use only the SELECT clause.

Combining tables together

There are two ways to do this: either add new rows or new columns to an existing data source. 

You can combine or contrast rows from datasets by using UNION, UNION ALL, INTERSECT and EXCEPT (known as MINUS in some dialects).

Here is an example of a UNION:

SELECT *
FROM tblEmployeePart1
UNION
SELECT *
FROM tblEmployeePart2;

This combines the two tables tblEmployeePart1 and tblEmployeeNumber2. They will need to have the same number of columns and compatible data types.

However, the main way to combine tables is usually by adding data from one table into additional columns in another table. You can do this using JOINs, which are part of the FROM clause.

There are several types of JOINs:

This is an example of a LEFT JOIN:

SELECT T.*
FROM tblTransaction AS T
LEFT JOIN tblEmployee AS E
ON T.EmployeeNumber = E.EmployeeNumber
WHERE E.EmployeeNumber IS NULL;

The above shows all Transactions for all Employees who do not have a matching entry in the Employee table.

Managing data within the database, such as inserting, deleting, or updating individual records

You can do this with the keywords INSERT, DELETE, UPDATE, and MERGE.

INSERT adds additional records or rows, either using the VALUES keyword to add hard-coded rows or, by using a SELECT statement, incorporating them from another table. The following inserts an Employee’s data into the Employee table:

INSERT INTO tblEmployee (EmployeeNumber, EmployeeFirstName, EmployeeMiddleName, EmployeeLastName)
VALUES (2, 'Jane', NULL, 'Zwilling');

DELETE can delete all of the records or rows in a table. It is usually used with WHERE to limit the deletion to rows that fulfill particular criteria. The following deletes EmployeeNumber 5, if it exists, from the tblEmployee table.

DELETE FROM tblEmployee
WHERE EmployeeNumber = 5;

UPDATE modifies existing rows or records in a table. It could change the status of a set of records from “On leave” to “Active,” for example.

UPDATE tblEmployee
SET Status = 'Active'
WHERE EmployeeNumber = 5;

MERGE combines one table with another table and can take different actions depending on whether key parts of the row are already included in the table or not. The following updates the Amount column for existing transactions:

MERGE INTO tblTransaction as T
USING tblTransactionNew as S
ON T.EmployeeNumber = S.EmployeeNumber AND T.DateOfTransaction = S.DateOfTransaction
WHEN MATCHED THEN
UPDATE SET Amount = T.Amount + S.Amount;

Creating new tables and other objects within a structured database

The keywords CREATE, ALTER, and DROP (which deletes the object) do this.

When defining tables using CREATE TABLE, you also define the columns, the data type, and any constraints (restrictions) that should be imposed. Examples of these constraints include:

An example of a new table is:

CREATE TABLE Lockers
(LockerNumber INT PRIMARY KEY,
Location varchar(20) NULL,
NumberOfTimesUsed INT CHECK (NumberOfTimesUsed>=0),
DateWhenInserted datetime DEFAULT (GETDATE()));

This creates a table called Lockers with four columns. The column “LockerNumber” is the Primary Key, and other tables may reference it. The column “Location” might be NULL (blank). The column “NumberOfTimesUsed” cannot be negative. And the column DateWhenInserted will default to the current date and time unless I specify another value.

How long does it take to learn it?

The good news is that you can learn the basics of SQL very quickly. In fact, you can learn the six principal clauses of the SELECT statement in an hour. This will allow you to retrieve data from databases such as Microsoft SQL Server. If this appeals to you, then please have a look at my course SQL Server Essentials in an hour: The SELECT statement.

If you would like a more in-depth look at SQL, then why not have a look at my eight-hour “Database Fundamentals” course? We’ll look at the SELECT statement, normalisation, constraints, and inserting, updating and deleting data. Additionally, we’ll investigate views, procedures, functions, triggers, indexes, security, and backing up data. With 15 quizzes and a Practice Test, you can be sure that you are learning.

However, if you want more, then have a look at my 29-hour “Querying Microsoft SQL Server with Transact-SQL.” In this, we have a real in-depth look at various aspects of querying data, such as those mentioned above, plus data types, combining datasets, ranking functions, subqueries, XML, JSON, transactions, and Dynamic Management Views.

For more than half a century, SQL has been the industry standard for relational database management. Today, it still tops most other popular programming languages as a desired skill for any data analyst, engineer, or architect. Although the advent of Big Data and the Internet of Things has boosted the rise of other kinds of data management systems, SQL remains an essential tool for data professionals in all sorts of organizations. 
I hope you will expand your toolkit with learning SQL commands, including the SELECT statement. I’ll see you in one of my courses!

Page Last Updated: November 2021

Top courses in SQL

SQL and PostgreSQL: The Complete Developer's Guide
Stephen Grider
4.8 (2,701)
Highest Rated
SQL - The Complete Developer's Guide (MySQL, PostgreSQL)
Academind by Maximilian Schwarzmüller, Maximilian Schwarzmüller, Manuel Lorenz
4.8 (110)
Hot & New
The Complete SQL Bootcamp 2022: Go from Zero to Hero
Jose Portilla
4.7 (115,017)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (61,457)
Bestseller
SQL & PostgreSQL for Beginners: Become an SQL Expert
Jon Avis - SQL Instructor
4.6 (3,956)
Bestseller
Learn SQL +Security(pen) testing from Scratch
Rahul Shetty
4.4 (1,890)
Bestseller
SQL Programming Basics
Global Academy
4.5 (844)
Master SQL For Data Science
Imtiaz Ahmad
4.6 (8,859)

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo