Introduction to SQL

Author(s): Amrut Prabhu

Reviewers: Ronak Lakhotia, Rahul Rajesh

What is SQL?

A Relational Database Management System (RDBMS) is a popular database solution used by software applications to manage their data.

Structured Query Language (SQL) is a programming language that is specifically designed for interacting with an RDBMS. Unlike other languages, SQL doesn't come as a standalone installation. Rather, RDBMSs like MySQL, Oracle, SQL Server and PostgreSQL come with an implementation of SQL.

How Does SQL Work?

SQL uses queries to retrieve data. Here is an example of how an SQL query is used.

Suppose we have the data table Students shown here:

We can use this SQL query to retrieve information from this table:

SELECT Course, COUNT(*) num
FROM Students
WHERE Faculty = 'CS'
GROUP BY Course
HAVING COUNT(*) > 1
ORDER BY num;

This query first filters the entries in the Students table such that only entries that have CS as the faculty are considered. After that, it groups those entries into the 3 courses: CS101, CS202 and CS404. Then, it removes courses that do not have more than 1 student, i.e., CS404 is removed from consideration. Finally, it returns a list of courses with a count of the number of students, ordered in increasing order. So, the output of the query is:

You can see how this simple query can prove to be extremely useful for getting this information when the table has a large number of rows. Queries can also be used to retrieve data that span across multiple tables. Apart from retrieving information, SQL can be used for creating, deleting and manipulating data with commands like INSERT, DELETE and UPDATE for entries, in addition to CREATE, DROP and ALTER for tables as a whole.

For example, you can set up the structure of the table shown in Table 1 by executing the CREATE TABLE query shown here. It creates a new data table Students, with 4 fields (ID, Name, Course and Faculty) and specifies their data types.

CREATE TABLE Students(ID int, Name varchar(255), Course varchar(255), Faculty varchar(255));

We can also remove rows containing the MA faculty from Table 1 with this DELETE query.

DELETE
FROM Students
WHERE Faculty='MA';

Figure 1. Using DB Fiddle to run queries in MySQL

Online editors like DB Fiddle can be used while learning SQL. However, to use SQL in application development, you need to consider other alternatives for running SQL queries:

• RDBMS software: RDBMS installations usually come with a Graphical User InterfaceGUI (see Figure 2), but can also be accessed from the command line (see Figure 3).

Figure 2. MySQL (an RDBMS) can be used in the MySQL Workbench GUI application (source)

Figure 3. Logging in to the MySQL RDBMS and viewing tables from the command line (source)

• APIs: You can use APIs in programming languages or independent ones like SQL API (shown in Figure 4) to interact with databases. For example, the SQLite3 library can be used to interact with a database through Python.

  

  Figure 4. An example of how to use CARTO's SQL API, which uses a PostgreSQL database (source)

Why Learn SQL?

The main reason why you should learn SQL is because of its widespread adoption. As SQL is not limited to any particular operating system or programming language, RDBMSs (which use SQL) are widely used by a lot of software applications (web, mobile etc.). Hence, in order to utilize almost any relational database or RDBMS available today, you need to know the SQL language.

Disadvantage

The biggest issue in SQL is with regards to the syntax and features. Although SQL databases use established American National Standard InstitutesANSI & International Organization for StandardizationISO standards, some RDBMSs (like PostgreSQL, for example) add proprietary extensions to standard SQL. Due to this, the available feature set can vary according to what you're using. Furthermore, there may be case-sensitivity, date and time format, functions available out-of-the-box, etc.minor changes in the syntaxes across RDBMSs. These factors can make SQL confusing and frustrating to use when switching across RDBMSs, since you would have to change your SQL queries.

How to Get Started With SQL?

As you saw in the example in the earlier section, SQL is really not that complex. It is easy to learn, even for beginners who do not have any prior experience with databases.

To get started with SQL, you can either just use an online SQL playground or install an RDBMS. There are many free RDBMSs available like PostgreSQL and MySQL. At the early stages of learning SQL, you can even use online editors like DB Fiddle (supports MySQL and SQLite) or SqliteOnline (supports SQLite) since they allow you to start learning without the hassle of setting up anything.

Here are some recommended steps for learning SQL:

1. Understand RDBMS: Before jumping into the SQL language, refer to this book's DataBase Management SystemDBMS chapter to understand the basic database concepts. More specifically, understand the basic concepts of RDBMS by going through Tutorials Point's SQL- RDBMS Concepts page. This is not essential, but will give you a better high-level understanding before diving into programming.

2. Learn SQL Syntax: These are some recommended resources that you can use to learn the SQL language.

   • The Guru99 SQL Tutorial for Beginners is a good course for beginners. It is well organized and provides comprehensive information, which will ensure that you know the basics well.

   • The W3Schools SQL Tutorial is a useful, interactive written tutorial in which you can run and modify SQL queries to see the examples in action.

   • In order to keep track of and remember the syntaxes that you're learning, you can refer to the W3Schools SQL Quick Reference Guide. This is particularly useful when you want to look up specific information quickly.

3. Use SQL in practice: Look into different RDBMSs and decide which one you want to work with. You can start by comparing the 3 mentioned in this Oracle vs MySQL vs SQL Server post and work from there. Then, set up the corresponding RDBMS and integrate it with an application. This will give you good experience and exposure to how databases are used in practice.

   In the case of MySQL, a good resource for getting started with this is MySQLTutorial.org. The Interfaces section of the website contains multiple tutorials explaining how to integrate and work with MySQL from Node.js, Java, Python and more.