A beginner’s guide to SQL and its queries

The technology world is using data for almost everything. However, only data has no value alone. Here is SQL, that’s Structured Query Language. SQL makes it possible for a person to work with all the data involved in organizations by efficiently managing and manipulating vast datasets. In this beginner’s guide to SQL by a online web development course instructor in Delhi, we will explore the various important aspects of SQL as a language from queries to why mastering it is important for any aspiring database developer.

What is SQL?

SQL stands for Structured Query Language; it can be defined as specialized programming language used to communicate with databases and manipulate their contents. The word “Structured” signifies that SQL employs an orderly methodology on how people interrelate with databases and request information.

SQL uses CRUD operations; create, read, update and delete on a database. It is basic for managing databases and other related tasks such as data manipulation among others. Unlike other general purpose programming languages like Java or C++, SQL is specifically designed for DMBSs (Data Base Management Systems) making it a fourth generation language (4GL).

Sometimes called sequel or ess-que-el. It has particular role like no other. Each time you come across words such as loops, logic directives, variables they are useful for developers who use this programming language for writing statements that can be applied when building websites or any software using front-end or back-end languages. SQL is still serving as a query language focused on storing/retrieving/manipulating data in databases itself.

SQL Language Elements

SQL works at the level of a structure through the series of statements that begin with keywords or commands such as CREATE and end with a semicolon. These statements are responsible for the core SQL operations. Here are some key SQL language elements:

• Keywords: used to perform different functions within a database. Examples include ADD, JOIN, and VIEW.
• Identifiers: these would be object names existing in the database like tables, views, indexes, columns.
• Expressions: strings of symbols/symbols which do mathematical operations on data.
• Search Conditions: criterion for selecting just part of rows in a table or creating IF statement checking condition in row itself.
• Data Types: when creating a table you need these to specify what type of data is stored in each column. The most common ones are number, string and date.
• Nulls: represent unknown values or missing information in databases.
• Comments: utilized to explain sections of sql statements for better understandability and maintainability.

Important SQL Queries for Beginners

These are some of the most important SQL queries for beginners.

First let us create one table and then use it for our queries guide

CREATE TABLE ads (
    ads-id INT PRIMARY KEY,
    ad-cname VARCHAR(50),
    cost INT
);

Lets insert one row of data

INSERT INTO ads (ads-id, ad-cname, cost)
VALUES
(1, 'video', 30),
(2, 'audio', 20),
(3, 'photo', 5);

Retrieving Data From All Columns

Retrieving data from all columns of a table is a fundamental SQL operation. The query that does this is simple. Instruct SQL to fetch any column by using the asterisk (*) after the SELECT statement without listing them out individually. After the FROM clause, do not forget to specify the name of the table. For example, if we want to retrieve data from “ads” table, we will use the following query:

SELECT *
FROM ads;

Retrieving Data From Certain Columns

This is one of the simplest SQL operations which involves getting data from certain tables’ columns. It’s rather easy to execute this type of query, though. A plain SQL query will be able to select certain columns. List them down separately by inserting an asterisk (*) after SELECT keyword – a special character that tells an oracle server to bring back everything in this particular row. Just remember also (afterwards) include your table name on FROM clause like this one;

SELECT ad-id, ad-cname
FROM ads;

It will give only ads-ID coloum and ad-cname coloum data

Filtering Data Using the WHERE Clause

SQL filtering is important as it helps you extract specific records based on given conditions only through WHERE clause which narrows down your search results making it faster and better targeted than before. By using where clause you can filter data according to certain standards. For instance, given a query below would return only those records where age is 2 or more:

SELECT ads-id, ad-cname, cost
FROM ads
WHERE cost >= 20;

Filtering Data Using Conditions Joined by AND Operator

The AND operator is crucial when filtering data using multiple conditions since it allows combining conditions where each condition has its own criteria; thus fulfilling all of them at once. Now consider a situation where you are interested in retrieving rows that have value greater than or equal 2 for age column and whose names contain dog in it;

SELECT ads-id, ad-cname, cost
FROM ads
WHERE cost >= 10 AND name = 'audio';

Using DISTINCT to Retrieve Non-Repeated Records

In SQL, the DISTINCT keyword is crucial for retrieving unique records from a database. The DISTINCT after SELECT ensures that only one of each kind of record will be returned so as to avoid redundancy. For example, consider a query to retrieve unique combinations of cost from a ads table. If multiple records have similar names with same cost, the query will only need to show one record among them. Using DISTINCT:

SELECT DISTINCT cost
FROM ads;

Retrieving Data Without NULL in a Certain Column

If you want to get data without including rows where certain columns contain NULL inside it then use IS NOT NULL operator which results into any column values that are not null in the given row being retrieved effectively excluding all those with nulls like this:

SELECT ads-id, cost
FROM ads
WHERE ad-cname IS NOT NULL;

Sorting Data According to One Column

Sorting in SQL is an elementary operation for easier querying and orderly arrangement of results displayed on the screen sorted by specific column (s). This implies that after ORDER BY, you mention your desired column’s name and data is sorted accordingly. By default, SQL orders data in ascending order which is alphabetic for text columns. In case you need rows arranged in descending order include DESC after the column’s name;

To illustrate, should you wish to sort the data using the name column of id and name columns, use the following query:

SELECT ad-cname
FROM ads
ORDER BY name;

Sorting Data According to More Than One Column

In SQL, sorting data by multiple columns gives a more precise ordering. For example, first sort records by the name column in descending order and then by the id column in ascending order. This is so that records with same names (for instance all records having ‘dog’ in their name) are further sorted according to their ids which should be in ascending order. The SQL query below illustrates this method:

SELECT ads-id, ad-cname
FROM ads
ORDER BY ad-cname DESC, ads-id;

Searching for Values Matching a Certain Pattern

In SQL, the LIKE operator allows you to retrieve data that matches a specific pattern. This is particularly useful for searching within text columns. For instance, to find records in the ads table where the name column contains the character “i” you can use the following query:

SELECT ad-cname
FROM ads
WHERE name LIKE '%i%';

Joining Values from Text Columns into One String

In SQL, the CONCAT function is used to join strings from multiple columns into a single string. This function is particularly useful when you need to combine text data from different columns for more readable or meaningful output. For instance, if you have two columns, category and name, you can use CONCAT to merge these values with a space in between. Here’s a basic example:

SELECT CONCAT(ads-id, ' ', ad-cname)
FROM ads;

Using Mathematical Operators

In SQL, you can perform calculations directly within your queries using mathematical operators such as “+”, “-“, “*”, and “/”. These operators allow you to manipulate data effectively and derive new values. For example here are various operations, of how you can do it:

SELECT SUM(COST) AS TotalCost FROM Ads;
SELECT AVG(COST) AS AverageCost FROM Ads;
SELECT MAX(COST) AS MaxCost FROM Ads;
SELECT ADS_ID, AD_CNAME, COST, (COST * 1.10) AS IncreasedCost FROM Ads;
SELECT ADS_ID, AD_CNAME, COST, (COST - 5) AS DiscountedCost FROM Ads;

Lets have another table called Campaign

CREATE TABLE campaign (
    campid INT PRIMARY KEY,
    campname VARCHAR(50),
    budget INT
);
INSERT INTO campaign (campid,campname, budget)
VALUES
(1, 'HD Videos', 220),
(2, 'MP3-audio', 233 ),
(3, 'HD-photo', 52);

Adding Data From Different Tables

To combine data from different tables, you can use the UNION ALL operator, ensuring the records share the same data type. For instance, to retrieve all ids from both the ads and campaign tables, use UNION ALL. This operation includes all occurrences of last names, even if they are duplicated across tables. Alternatively, to fetch unique last names only once, employ UNION instead of UNION ALL.

SELECT ads-id FROM ads
UNION ALL
SELECT campid FROM campaign;

Using Set Operations in SQL

In SQL, the INTERSECT operator retrieves the common elements between two datasets. For instance, it can be used to fetch names present in both the ads and campaign tables. To find the differences between datasets, operators like MINUS or EXCEPT are utilized. These operations are essential for comparing and analyzing datasets effectively within SQL queries. For example:

SELECT ads-cname FROM ads
INTERSECT
SELECT campname FROM campaign;

Joining Data From Different Tables

In SQL, you can merge data from multiple tables using various types of JOIN operations: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN. For instance, to combine data from tables “ads” and “campaign” use INNER JOIN. Specify the first table after FROM, followed by INNER JOIN and the second table. Use ON to match records based on a specified condition, such as matching “ads-id” with “campaign.campid.” This operation retrieves records where corresponding values in both tables align, allowing comprehensive data analysis and integration across related datasets.

SELECT ads.ad-cname, campaign.campname
FROM ads
INNER JOIN campaign
ON ads.ads-id = campaign.campid;

Using Aliases of Tables and Columns

While joining tables in SQL, it is better to use aliases for table and column names because it enhances clarity and efficiency. For example, readability can be improved by aliasing tables as ‘a’ and ‘c’ meaning ads and campaign respectively. These aliases are defined using ‘AS’ in the FROM or JOIN clauses. The query also simplifies when specific columns such as name are renamed as adname or city. For instance:

SELECT a.ads-cname AS adname, c.campnamename AS campaign
FROM ads AS a
INNER JOIN campaign AS c
ON a.ads-id = c.campid;

Counting the Number of Rows in a Table

SQL provides the COUNT function that returns the total number of rows available in a table. This function counts entries within a specified column like id in ads table. For example:

SELECT COUNT(ads-id)
FROM ads;

Calculating the Average of Values in a Column

In SQL, you can take advantage of the AVG function to find average value of values present within a column. This means that if all products are stored in the ‘campaign’; then, the following query calculates an average budget:

SELECT AVG(budget)
FROM campaign;

Calculating Aggregate Values for Groups of Records

In SQL, GROUP BY allows rows to be grouped together to calculate aggregate values. Thus using COUNT on each group (category) determines how many rows there are (products). Columns selected must appear in GROUP BY also – similar applies for MAX, MIN, AVG and SUM.

For example, to retrieve the count of products in each category from a ‘product’ table:

SELECT category, COUNT(id)
FROM product
GROUP BY category;

Updating a Column by Filtering Records

When you want to update specific values in a column without affecting all records, you may use the WHERE clause with condition in SQL. It shows which records will be affected and how through specified criteria. For instance, if only those records where the id is 2 are changed and ‘campname’ becomes ‘music’, then, you would execute this SQL query:

UPDATE campaign
SET campname = 'music'
WHERE id = 2;

These queries are useful for both beginners and professionals as well. This beginners guide by a trainer from web development institute in Delhi to database management; for these SQL queries is a great cheatsheet for basic queries.

A guide on basics of stack is also available. Learn python stack here.