Basic SQL Queries and Operators | One Shot SQL

How to Use the SELECT Statement in SQL Queries?

The SELECT statement is the heart of data retrieval from a database. You can pick specific columns or dump entire tables depending on your analytical needs. These actions produce outputs called result sets. The result sets are stored as temporary tables.

As you write these commands, remember that SQL is not case sensitive. Commands can be entered in upper case or lower case and will still execute. But using different styling for commands makes your scripts more readable.

SQL

-- Standard syntax for selecting specific columns

SELECT column_name1, column_name2

FROM table_name; 

-- Standard syntax for selecting all columns

SELECT * FROM table_name; 

To fetch specific data from a system, you specify the required fields separated by commas. If you need to view every single column within a dataset without applying structural changes, use an asterisk (*).

How the WHERE Clause Works in SQL Queries?

Pulling complete datasets becomes inefficient when working with thousands of rows. The WHERE clause acts as a row-level filter, ensuring that the database only displays records that meet specific conditions.

This clause looks for valid matches across individual rows and filters out non-matching records. It is placed directly after specifying the source table. 

SQL

-- Syntax structure for filtering records

SELECT column_name

FROM table_name

WHERE condition;

When filtering text or string values, always enclose the targeted value inside single or double quotes. Numeric values do not require quotes.

• Numeric Filters: Filtering items where a price or count falls below a specific value.

• Text Filters: Retrieving details for a specific brand or category by matching the exact text string.

How to Use ORDER BY to Organize Data in SQL Queries?

The order of data in a raw database table is often random or based on the entry sequence. To make the output meaningful, you can sort the result set using the ORDER BY clause.

By default, data sorts in ascending order. If you want to sort records from highest to lowest, add the DESC keyword at the very end of your expression.

SQL

-- Sorting by a single column in descending order

SELECT * FROM car_details

ORDER BY sealing_price DESC;

-- Sorting using multiple columns with mixed priorities

SELECT * FROM car_details

ORDER BY sealing_price ASC, manufacturing_year DESC; 

You can also use column positions instead of typing out full column names. For instance, ordering by the number 2 sorts the data using the second column listed in your statement.

SQL

-- Sorting by the second column in the SELECT statement

SELECT name, sealing_price

FROM car_details

ORDER BY 2 DESC;

How the LIMIT Clause Works in SQL Queries?

When testing code or dealing with large datasets, displaying thousands of rows can slow down performance. The LIMIT clause restricts the maximum number of rows returned in the final output.

This clause is placed at the end of the query statement, following any sorting rules.

SQL

-- Restricting the result set to the top 10 records

SELECT name, sealing_price

FROM car_details

ORDER BY sealing_price DESC

LIMIT 10;

Using this command is helpful for finding extreme values, such as the top 15 cheapest or most expensive items in a dataset. It keeps your workspace clean and improves query response times.

How to Use Aliases in SQL Queries?

Database tables often contain complex, technical column names that are difficult for business stakeholders to understand. You can use aliases to assign temporary names to columns or tables for better readability.

An alias is created using the AS keyword. It only changes how the column heading appears in the current output, without altering the actual database structure.

SQL

-- Assigning a clear alias to a column

SELECT manufacturing_year AS car_age

FROM car_details;

-- Creating an alias without using the AS keyword

SELECT manufacturing_year car_age

FROM car_details;

Using the AS keyword is optional. Separating the original column name and the new alias with a space delivers the same result.

How to Learn SQL Querries Processing Order?

The order in which you write a query is different from how the database engine executes it internally. Understanding this sequence helps you avoid errors and optimize performance.

Scripting Order vs. Execution Order

The database engine first identifies the source table (FROM) and filters the rows (WHERE). Next, it selects the specified columns (SELECT), sorts the filtered records (ORDER BY), and finally limits the output rows (LIMIT). Because filtering happens before selection, you cannot use a column alias inside a WHERE clause.

How to Remove Duplicate Rows in SQL Queries?

Real-world datasets often contain redundant entries and missing information. Clean datasets are essential for accurate analytics and reporting.

Removing Duplicates with DISTINCT

The DISTINCT keyword eliminates duplicate rows and returns only unique values. It is placed directly after the SELECT command.

Checking for Missing Information

A NULL value represents missing or unknown data. It is not equivalent to a zero or an empty text string. To find these missing records, use the IS NULL or IS NOT NULL operators.

Using a standard mathematical sign, such as WHERE name = NULL or WHERE name != NULL, will result in an error or return an empty dataset.

What are Data Filtering Techniques in SQL Queries?

To perform complex mathematical calculations and detailed data filtering, you need to use specific operators. These are divided into three main categories: arithmetic, comparison, and logical operators.

1. Arithmetic Operators

Arithmetic operators perform mathematical calculations on numeric columns within your datasets.

• Addition (+): Adds values together, such as increasing mileage records.

• Subtraction (-): Deducts values, useful for calculating discounts on prices.

• Multiplication (*): Multiplies values, such as doubling prices for projections.

• Division (/): Divides numbers to find averages or percentages.

• Modulus (%): Returns the remainder of a division, often used to identify even or odd values.

2. Comparison Operators

Comparison operators match column values against specific criteria. They return a Boolean value of true or false.

• Equal To (=): Matches an exact value.

• Not Equal To (!= or <>): Filters out specific values.

• Greater Than (>) / Less Than (<): Checks if values are higher or lower than a threshold.

• Greater/Less Than or Equal To (>=, <=): Includes the boundary value in the comparison.

3. Logical Operators

Logical operators combine multiple conditions to create advanced filters.

• AND: Returns rows only when all conditions are true.

• OR: Returns rows if at least one condition is true.

• NOT: Inverts the result of a condition.

• BETWEEN: Filters values within a specific range, including the start and end values.

• IN: Checks if a value matches any item in a specified list.

• LIKE: Searches for specific text patterns using wildcards.

4. Pattern Matching with Wildcards

The LIKE operator uses two wildcard characters to find text patterns:

1. Percentage (%): Represents zero, one, or multiple characters.

2. Underscore (_): Represents a single character.