SQL Programming Tutorial for Beginners

SQL Programming Tutorial for Beginners

SQL (Structured Query Language) is a powerful declarative language to manage and manipulate relational databases. It’s a standard for performing operations such as retrieving, inserting, updating, and deleting data. SQL is an essential skill for data analysts, software developers, and database administrators to work with data. It enables you to talk to databases in an efficient manner and derive worthwhile insights.

For in-depth understanding of topics addressed, including basic syntax, joins, subqueries, and database structure, see our comprehensive SQL Programming Course Syllabus.

What is SQL: The Absolute Start

SQL (Structured Query Language) is a standardized programming language for managing relational databases and performing actions on the data in the databases; it’s the programming language of choice for interacting with databases to perform things like fetching data, updating records, and creating new tables. If you think of SQL as your way of communicating with a database to ask for information or tell it to perform a change, then you are heading in the right direction.

Relational Databases and Tables

A relational database is a database that stores and provides access to data points that are related to each other with respect to the data. A relational database is based on the relational model, which organizes data in one or more tables, using rows and columns.

A table is the basic unit of a relational database. A table is a collection of data that is organized in rows and columns.

• Rows (also called records) represent a single, entire instance or entry in the table. For example, one customer’s entry in a customer table.
• Columns (also called fields or attributes) represent a specific category with respect to the data. For example, CustomerID, FirstName, and LastName would be columns in the customer table.

Recommended: Oracle SQL Programming Course Online.

Setting up your Environment for SQL

To write and execute SQL queries, you will need to have a DBMS (database management system). Some popular examples are:

• SQLite: This is a self-contained and serverless database engine. It is also quite easy to get started with.
• MySQL: MySQL is a popular open source relational database. It is good for web applications, and a good learning choice since it is widely used in many professional environments. You will need to install a server and a client to connect to it. 
• PostgreSQL: PostgreSQL is also known as a powerful open-source object-relational database system. Many people consider it to have the most robust set of features of any DBMS. It is good for complex applications including data warehousing.

A beginner can always get started reasonably easily with a tool like SQLite. There are command line tools and also GUI like DB Browser for SQLite.

Code example: Create a Simple Table in SQL

This SQL example will create a table called Students containing “StudentID”, “FirstName” and “LastName” fields.

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

);

• CREATE TABLE Students tells the database to create a new table, called Students.
• StudentID INT PRIMARY KEY defines a column that contains an integer that is unique. PRIMARY KEY makes it so that no two students can have the same ID.
• FirstName VARCHAR(50) and LastName VARCHAR(50) define columns containing text data. VARCHAR(50) means that the columns store text strings ranging up to a length of 50.

The Core Four of SQL

The Core Four—SELECT, FROM, WHERE, and ORDER BY— are the basic building blocks of nearly every SQL query. The Core Four let you retrieve and order specific data from your tables.

SELECT: Pick Columns

The SELECT statement states the columns from which you wish to retrieve information from the table. You can select one or more columns by specifying each column name separated by a comma in the SELECT statement.

To select specific columns:

SELECT column1, column2, column3

FROM table_name;

This query retrieves only column1, column2, column3 from table_name.

To select all columns:

SELECT *

FROM table_name;

The asterisk (*) is a wildcard character selecting every column in the table.

FROM: Identifying the table

The FROM clause specifies which table contains the data you want to query. You use it with the SELECT statement. 

SELECT column1, column2

FROM your_table_name; 

In this example, you are telling the database to find column1 and column2 inside the table your_table_name.

WHERE: Filtering Rows

You can display just entries that satisfy a specific criterion by using the WHERE clause to filter records. When you wish to obtain specific data rather than the complete table, it is crucial. 

Comparison and Logical Operators: Comparison Operators:

• = (Equal to)
• != or <> (Not equal to)
• > (Greater than)
• < (Less than)
• >= (Greater than or equal to)
• <= (Less than or equal to)

Example:

SELECT FirstName, LastName

FROM Students

WHERE Age > 20; 

This example records what First and Last names of all the students who are older than 20 years old.

Logical Operators:

• AND: Displays a record if the first condition is TRUE and the second condition is equally TRUE. 
• OR: If either the first or second condition is true, a record is displayed.
• NOT: If the condition or conditions are NOT TRUE, a record is displayed. 

Example:

SELECT *

FROM Products

WHERE Price > 50 AND Category = ‘Electronics’; 

This example records and returns all products that both have the price greater than 50 and are also in the category of ‘Electronics’. 

IN and BETWEEN

IN: The IN clause allows for multiple possible values for a column. It is a shortcut for multiple OR statements. 

Example:

SELECT *

FROM Employees

WHERE Department IN (‘Sales’, ‘Marketing’, ‘HR’);

This returns all of the employees that are in the Sales, Marketing, or HR department.

BETWEEN: The BETWEEN clause is used for selecting values in a specific range. It is inclusive of both ends of the range. 

Example:

SELECT ProductName

FROM Products

WHERE Price BETWEEN 10 AND 50;

This returns all of the products whose price is between 10 and 50.

ORDER BY: Sort Your Results

The ORDER BY clause will sort the result set in ascending or descending order.

• ASC(Ascending): The default way to sort data. You would use this to sort from smallest to largest (A-Z, 1-10).
  • You may omit asc when you want the results to be sorted in ascending order.
• DESC (Descending): Sort from largest to smallest (Z-A, 10-1).

Example 

SELECT ProductName, Price

FROM Products

ORDER BY Price DESC;

In this statement the query retrieves product names and their prices from the Products table, and returns would be sorted from the most expensive product to the least expensive product.

Related: MySQL Course Online.

Aggregation and Grouping in SQL

Aggregation and grouping are important concepts in SQL as they provide a way to summarize and analyze data. They allow you to execute calculations on a set of rows and display it as a single value.

Aggregate Functions

Aggregate functions work off a set of values and return a single scalar value. Aggregate functions are typically used along with the GROUP BY command. The three most common aggregate functions are:

• COUNT(): It returns the number of rows that satisfy a specific criterion

SELECT COUNT(*) FROM Employees;

• This query returns the total number of employees from the Employees table.
• SUM(): It returns the total sum of a numeric column.

SELECT SUM(Salary) FROM Employees;

• This returns the total sum of salaries.
• AVG(): It returns the average value of a numeric column.

SELECT AVG(Age) FROM Students;

• This query returns the average age of all students.
• MIN(): Returns the smallest value from a column.

SELECT MIN(Price) FROM Products;

• This query returns the lowest price in the Products table.
• MAX(): Returns the largest value from a column.

SELECT MAX(Price) FROM Products;

• This query returns the highest price.

GROUP BY: Grouping Rows

The GROUP BY clause lets you group rows with the same values into summary rows. This process then allows you to calculate each group independently.

Example: Let’s say you have a Sales table with the columns Region and SaleAmount. If you want to get the sales total for each region, you would include GROUP BY.

SELECT Region, SUM(SaleAmount)

FROM Sales

GROUP BY Region; 

This query first groups all sales by Region and then finds the total SaleAmount value for each unique region.

HAVING: Filtering Groups

Groups formed by the GROUP BY clause are filtered by the HAVING clause. Prior to aggregation, the WHERE clause filters individual entries that are part of the group. The HAVING clause filters the results after the aggregation process takes place. 

Difference between WHERE and HAVING:

• WHERE filters rows before aggregation takes place, and it cannot use aggregate functions in the filter criteria,
• HAVING filters groups of rows and must always follow a GROUP BY clause, and it can have aggregate functions in the filter criteria.

Example: To find all regions where total sales greater than $50,000.

SELECT Region, SUM(SaleAmount) AS TotalSales

FROM Sales 

GROUP BY Region

HAVING SUM(SaleAmount) > 50000; 

• FROM Sales: It is starting from the Sales table.
• GROUP BY Region: It is grouping all records into sales records by their Region.
• SELECT Region, SUM(SaleAmount): It calculates the sum of sales on each group’s Region.
• HAVING SUM(SaleAmount) > 50000: It filters the groups, so only the group values are kept with a TotalSales amount greater than 50,000.

Recommended: Oracle SQL/PLSQL Course Online.

Utilizing Multiple Tables – The JOINS Might

Databases always seem to be multi-table and for good reason. This process of normalization helps to eliminate redundancy and store data in a more efficient manner. JOINS allow you to combine rows from two or more tables based on a column that exists in both tables. The JOIN process can help you obtain a complete set of information that may be spread over several tables in the database. 

Why Use Joins?

You have an Order table that contains OrderID and CustomerID. You also have a Customers table that has CustomerID and CustomerName. If you want to know which customer placed a particular order, you will need to join the two tables together using the common column, CustomerID. This is essentially what a join does.

INNER JOIN

An INNER JOIN will return only the records with matching values in both tables. This is the most common type of join and is used to fetch data that exists in a one-to-one relationship between tables or if it has records in a one-to-many relationship between tables. 

Code Example: Let us find the names of customers whom have made an order.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

INNER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID; 

This query is joining Customers and Orders table on the condition that the column CustomerID matches between both tables, which will return only rows where a customer had an order. 

LEFT JOIN

A LEFT JOIN (or LEFT OUTER JOIN) will return all rows from the left table and matched rows from the right table. If there is no match for that row in the right table, the result will have NULL in the columns of the left table. 

Code Example: Selecting all customers including customers whom have made no orders will use a LEFT JOIN.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

LEFT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

This returns all customer names from the Customers table. If a customer does not have any orders, then the OrderID column will be NULL.

RIGHT JOIN

A RIGHT JOIN or RIGHT OUTER JOIN can be thought of as the mirror image of a LEFT JOIN. It returns all rows from the right table and any matched rows from the left table. Rows from the left table without matches will return NULL values.

Code Example: To list all of the orders and show who the customers that placed orders are even if some of the orders did not have a customer (and possibly indicate a data error) you would use a RIGHT JOIN.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

RIGHT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

This returns all of the rows from the Orders table. If an OrderID does not have a matching CustomerID in the Customers table then CustomerName will be NULL.

FULL OUTER JOIN

A FULL OUTER JOIN returns all the rows when there is a match in either the left table or the right table. Combining the results of a LEFT JOIN and a RIGHT JOIN with all the unmatched rows in both tables returning NULL values.

Code Example: To see all the customers and all of the orders posting which customers made orders, even if there were no matches in one of the other tables, you would use a FULL OUTER JOIN.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

FULL OUTER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID

WHERE Customers.CustomerID IS NULL OR Orders.CustomerID IS NULL;

This query returns all customers and all orders. The WHERE clause is tacked on here to intentionally filter for records that do not have a match, which can be helpful when performing data cleanup and finding incorrect records. If there was no WHERE clause it would simply return all records from both tables, with NULLs where there is not a match. 

Advanced SQL Features

Once you are comfortable with the basics, you can leverage more advanced SQL features, such as subqueries, CASE statements, window functions, and Common Table Expressions (CTE) to perform more complex data manipulation. 

Subqueries: Queries within Queries

A subquery (or inner query) is a query that appears inside another SQL query. Subqueries are used quite often to return data that will be used in the primary query to restrict the data to be returned. Essentially, they are a restriction in a SELECT, FROM, or WHERE clause. 

Example: Find employees that earn a salary that is greater than the average salary of all employees.

SELECT EmployeeName, Salary

FROM Employees

WHERE Salary >(SELECT AVG(Salary) FROM Employees);

The inner query (SELECT AVG(Salary) FROM Employees) is executed first in order to find the average salary. The outer query uses the result of the inner query to restrict the employees.

CASE statements: Conditional Logic

The CASE statement is a way to implement IF/THEN/ELSE logic directly in SQL queries. It evaluates a set of conditions one-by-one and returns the first condition that is found to be true.

Example: Classifying product prices.

SELECT ProductName, Price,

    CASE

        WHEN Price > 100 THEN ‘Expensive’

        WHEN Price > 50 THEN ‘Moderate’

        ELSE ‘Cheap’

    END AS PriceCategory

FROM Products;

This query looks at the Price for each product and assigns a text value (PriceCategory) based on that value.

Window Functions

Window functions calculate over a set of rows related to the current row.  Window functions do not decrease the number of rows returned and are appropriate for calculating things like ranking, or moving averages, etc. 

• ROW_NUMBER(): It assigns a unique sequential integer to rows. 
• RANK(): It duplicates values receive the same rank, and the next rank is skipped. 
• DENSE_RANK(): It receives the same value as RANK, but does not skip ranks.

Example with RANK(): To rank products within each category based on the prices.

SELECT ProductName, Category, Price,

RANK() OVER (PARTITOIN BY Category ORDER BY Price DESC) AS PriceRank FROM Produts;

By using the PARTITION BY Category clause, the data is segmented into groups (or “windows”) by category. The ORDER BY Price DESC sorts the products in each category by price from high to low.

Common Table Expressions (CTEs)

Common Table Expressions (CTEs) are a temporary, named result set that you can refer to within the context of a SELECT, INSERT, UPDATE, or DELETE statements. CTEs are defined and referenced within the WITH clause, and they assist in breaking complex constructs down into simpler and more comprehensible formats.

Example: Finding the top 5 products with the highest prices and their average prices.

WITH ExpensiveProducts AS (

    SELECT ProductName, Price

    FROM Products

    WHERE Price > 100

)

SELECT *

FROM ExpensiveProducts

ORDER BY Price DESC

LIMIT 5;

The ExpensiveProducts CTE creates a temporary result set of all products with prices greater than 100. In this instance, the main SELECT statement selects from our temporary CTE, making the overall structure of our query clearer.

Modifying and Deleting Data (DML) 

Data Manipulation Language (DML) is a type of SQL that is concerned with adding, changing, and deleting data. The commands we will learn are vital for appropriately managing and maintaining an up to date and correct database. 

INSERT INTO: When You Want to Send New Data into Your Table

The INSERT INTO statement can be used to send new rows of data into a table. 

Syntax for inserting data with all the columns: 

When you are inserting values into a table, you can list the values in the same order they are listed in the schema. This is the least complex way to do it.

INSERT INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

Example: Adding a new employee to the Employees table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)

VALUES (101, ‘Jane’, ‘Doe’, ‘Marketing’);

Syntax for inserting data with specified columns:

You can insert data while using specific columns only. This is useful when you’re not able to provide data for every column, or in some instances, a usage of default values are present.

INSERT INTO Employees (EmployeeID, FirstName, LastName)

VALUES (102, ‘John’, ‘Smith’);

Because we did not provide a value for the employee department, the department column for the John Smith record will be NULL, or the default value. 

UPDATE: Updating Existing Data 

The syntax of UPDATE is used to update existing records in a table. The UPDATE statement is used with a WHERE clause to specify what rows to update, and without a WHERE clause, the complete table will be modified. This can be catastrophic! 

UPDATE table_name

SET column1 = new_value1, column2 = new_value2, …

WHERE some_column = some_value; 

Example: Updating the department for EmployeeID 101 from ‘Marketing’ to ‘Sales’.

UPDATE Employees

SET Department = ‘Sales’

WHERE EmployeeID = 101;

DELETE FROM: Deleting Data 

The DELETE FROM statement is used to delete one or more rows from a table. The DELETE FROM statement should almost always be used with a WHERE clause to specify what rows to delete, and without a WHERE clause, the complete table will be deleted. 

DELETE FROM table_name

WHERE some_column = some_value; 

Example: Deleting the employee with EmployeeID 102. 

DELETE FROM Employees

WHERE EmployeeID = 102;

Before executing an UPDATE or DELETE statement, run a SELECT query with the WHERE clause you use in the UPDATE or DELETE statement to ensure the rows you are targeting to update or delete are correct. This quick approach will help you determine your target and help prevent from losing data unintentionally.

Explore: All Software Training Courses.

Conclusion

We have covered fundamentals in this SQL Programming Tutorial. We started out learning the fundamentals, what SQL is, and how it interacts with relational databases. Next, we developed a base with the basic SELECT, FROM, WHERE, and ORDER BY syntax, which is the basis of almost every query that you will ever create. 

Are you ready to level-up your SQL skills? Enroll in our best SQL programming course in Chennai to get practiced with industry projects, expert guidance, and a community to help you become a database expert.