SQL for Beginners – Part 2: SELECT Commands with Examples

1 Comment
Intermediate SQL for Beginners
1 0
Read Time10 Minute, 37 Second

In the first article, SQL for Beginners, we covered the basics of SQL, including RDBMS concepts, classification of SQL commands, basic data types, and essential commands. In this follow-up article, we’ll explore intermediate SQL commands and techniques that will deepen your understanding and enable you to write more powerful and efficient queries. Let’s continue your SQL journey!

All code samples in this article can be accessed here: https://github.com/The-Developer-Space/CodeSamples/blob/main/SQL_for_Beginners/SQL_for_Beginners_Part_2.sql

Setting up the Sample Database

We’ll continue to use the same database that we created in Part 1 of the article series. We’ll modify the database as needed to try out the examples in this article.

Let’s add additional columns that we’ll use in the examples.

--Drop the table and recreate it with additional columns
DROP TABLE Users;

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Email VARCHAR(100) UNIQUE,
    City VARCHAR(50),
    State VARCHAR(5),
    PhoneNumber VARCHAR(20),
    SignUpDate DATE,
    IsActive BOOLEAN,
    AccountBalance DECIMAL(10,2),
    MembershipLevel VARCHAR(20)
);

Let’s add a few sample rows to try out the examples in this article.

--Delete all rows from the table
DELETE FROM Users;

--Insert new values
INSERT INTO Users 
(FirstName, LastName, Age, Email, City, State, PhoneNumber, SignUpDate, IsActive, AccountBalance, MembershipLevel, CreatedAt) 
VALUES
('John', 'Doe', 30, 'john.doe@example.com', 'New York', 'NY', '+1888412374', '2024-12-15', TRUE, 150.75, 'Gold', CURRENT_TIMESTAMP),
('Jane', 'Smith', 25, 'jane.smith@example.com', 'Los Angeles', 'CA', '+1222111432', '2024-11-20', TRUE, 200.50, 'Silver', CURRENT_TIMESTAMP),
('Alice', 'Johnson', 28, 'alice.johnson@example.com', 'Dallas', 'TX', '+1333098345', '2025-01-10', TRUE, 89.00, 'Bronze', CURRENT_TIMESTAMP),
('Bob', 'Brown', 35, 'bob.brown@example.com', 'Dallas', 'TX', '+1999810923', '2023-09-05', FALSE, 0.00, 'Free', CURRENT_TIMESTAMP),
('Emily', 'Davis', 22, 'emily.davis@example.com', 'Chicago', 'IL', '+1444555123', '2025-02-18', TRUE, 305.99, 'Gold', CURRENT_TIMESTAMP),
('Michael', 'Miller', 40, 'michael.miller@example.com', 'Seattle', 'WA', '+1555666789', '2023-12-01', FALSE, 120.00, 'Silver', CURRENT_TIMESTAMP),
('Sarah', 'Wilson', 31, 'sarah.wilson@example.com', 'Boston', 'MA', '+1666777432', '2024-10-10', TRUE, 75.50, 'Bronze', CURRENT_TIMESTAMP),
('David', 'Moore', 29, 'david.moore@example.com', 'Austin', 'TX', '+1777888999', '2025-03-01', TRUE, 650.00, 'Platinum', CURRENT_TIMESTAMP),
('Laura', 'Taylor', 27, 'laura.taylor@example.com', 'San Francisco', 'CA', '+1888999001', '2023-11-25', FALSE, 0.00, 'Free', CURRENT_TIMESTAMP),
('Daniel', 'Anderson', 33, 'daniel.anderson@example.com', 'Miami', 'FL', '+1999000111', '2024-05-14', TRUE, 480.00, 'Gold', CURRENT_TIMESTAMP),
('Olivia', 'Martin', 26, 'olivia.martin@example.com', 'Houston', 'TX', '+1234567890', '2025-01-05', TRUE, 350.25, 'Gold', CURRENT_TIMESTAMP),
('Ethan', 'Clark', 38, 'ethan.clark@example.com', 'Denver', 'CO', '+1987654321', '2023-06-22', FALSE, 45.00, 'Bronze', CURRENT_TIMESTAMP),
('Chloe', 'Lewis', 24, 'chloe.lewis@example.com', 'Atlanta', 'GA', '+1321654987', '2024-07-19', TRUE, 95.50, 'Silver', CURRENT_TIMESTAMP),
('Liam', 'Walker', 32, 'liam.walker@example.com', 'Portland', 'OR', '+1415098765', '2025-02-03', TRUE, 540.90, 'Platinum', CURRENT_TIMESTAMP),
('Sophia', 'Hall', 30, 'sophia.hall@example.com', 'San Diego', 'CA', '+1765432987', '2024-09-08', TRUE, 230.75, 'Gold', CURRENT_TIMESTAMP),
('Noah', 'Allen', 36, 'noah.allen@example.com', 'Orlando', 'FL', '+1212121212', '2023-08-15', FALSE, 0.00, 'Free', CURRENT_TIMESTAMP),
('Ava', 'Young', 21, 'ava.young@example.com', 'Phoenix', 'AZ', '+1432543321', '2024-05-01', TRUE, 150.00, 'Silver', CURRENT_TIMESTAMP),
('James', 'King', 45, 'james.king@example.com', 'Philadelphia', 'PA', '+1345983210', '2024-03-22', TRUE, 500.00, 'Gold', CURRENT_TIMESTAMP),
('Isabella', 'Wright', 27, 'isabella.wright@example.com', 'Austin', 'TX', '+1789456321', '2025-01-28', TRUE, 625.00, 'Platinum', CURRENT_TIMESTAMP),
('Lucas', 'Scott', 29, 'lucas.scott@example.com', 'Dallas', 'TX', '+1231231234', '2024-10-30', TRUE, 275.00, 'Silver', CURRENT_TIMESTAMP),
('Mia', 'Green', 23, 'mia.green@example.com', 'Charlotte', 'NC', '+1456743210', '2023-12-12', TRUE, 80.00, 'Bronze', CURRENT_TIMESTAMP),
('Alexander', 'Baker', 34, 'alex.baker@example.com', 'Nashville', 'TN', '+1999888777', '2024-08-18', TRUE, 130.00, 'Silver', CURRENT_TIMESTAMP),
('Amelia', 'Gonzalez', 39, 'amelia.gonzalez@example.com', 'Columbus', 'OH', '+1888000999', '2023-07-01', FALSE, 0.00, 'Free', CURRENT_TIMESTAMP),
('Henry', 'Nelson', 31, 'henry.nelson@example.com', 'Salt Lake City', 'UT', '+1222333444', '2025-03-11', TRUE, 410.25, 'Gold', CURRENT_TIMESTAMP),
('Grace', 'Carter', 28, 'grace.carter@example.com', 'Indianapolis', 'IN', '+1555444333', '2024-11-03', TRUE, 215.40, 'Silver', CURRENT_TIMESTAMP);

Filtering Data

The = operator does a literal comparison of values.

Example: Finding Users from Texas

SELECT FirstName, LastName 
FROM Users 
WHERE State = 'TX';

This query retrieves users whose State is TX.

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It’s particularly useful when you want to filter data based on partial matches.

  • % (percent symbol) represents zero or more characters.
  • _ (underscore) represents a single character.

Example: Finding Users with Names Starting with “J”

SELECT FirstName, LastName 
FROM Users 
WHERE FirstName LIKE 'J%';

Example: Finding Users with specific Email domain

SELECT * FROM Users
WHERE Email LIKE '%@example.com';

Sorting Data

The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order based on one or more columns. If the order is not specified, ascending order is applied by default. You can sort on multiple columns by separating them with a comma in the ORDER BY clause. Each column can have its own ASC or DESC clause.

Example: Sorting Users by Age in Ascending Order

SELECT FirstName, LastName, Age 
FROM Users 
ORDER BY Age ASC;

Example: Sorting Users by Last Name in Descending Order

SELECT FirstName, LastName 
FROM Users 
ORDER BY LastName DESC;

Example: Sorting Users by State in Ascending order and then by City in Descending Order

SELECT FirstName, LastName, City, State
FROM Users 
ORDER BY State ASC, City DESC;

This query retrieves users by their State in ascending order and then by City in descending order.

Using Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include:

  • COUNT(): Counts the number of rows.
  • SUM(): Calculates the sum of values.
  • AVG(): Calculates the average of values.
  • MIN(): Finds the minimum value.
  • MAX(): Finds the maximum value.

Example: Calculating the Average, Minimum and Maximum Age of Users

SELECT AVG(Age) AS AverageAge, MIN(Age) AS MinimumAge, MAX(Age) AS MaximumAge 
FROM Users;

This query calculates the average age of all users and the minimum and maximum ages. Aggregate functions without a GROUP BY clause returns exactly one row no matter how many rows the table contains.

Grouping Data

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It’s often used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

Example: Counting the number of users in each age group

SELECT Age, COUNT(*) AS UserCount 
FROM Users 
GROUP BY Age;

Example: Finding the average age in each state

SELECT State, AVG(Age) AS AverageAge
FROM Users
GROUP BY State;

Example: Finding the number of users in each membership level

SELECT MembershipLevel, COUNT(*) AS UserCount
FROM Users
GROUP BY MembershipLevel;

Filtering Groups

The HAVING clause is used to filter groups created by GROUP BY. It’s similar to WHERE, but it operates on groups rather than individual rows.

Example: Finding membership levels with more than 5 users 

SELECT MembershipLevel, COUNT(*) AS UserCount
FROM Users
GROUP BY MembershipLevel
HAVING COUNT(*) > 5;

Combining Multiple Conditions

You can combine multiple conditions in a WHERE clause using AND, OR, and NOT.

Example: Finding users aged between 25 and 30

SELECT FirstName, LastName, Age 
FROM Users 
WHERE Age >= 25 AND Age <= 30;

This can also be rewritten using the BETWEEN clause. BETWEEN works with numeric and date types and is inclusive of both values.

SELECT FirstName, LastName, Age 
FROM Users 
WHERE Age BETWEEN 25 AND 30;

Example: Finding users named “John” or “Jane”

SELECT FirstName, LastName 
FROM Users 
WHERE FirstName = 'John' OR FirstName = 'Jane';

The order of precedence can be controlled using brackets for complex combination of conditions. Here is an example.

SELECT FirstName, LastName, Age
FROM Users 
WHERE (FirstName = 'John' OR FirstName = 'Jane')
    AND NOT (LastName = 'Adams')
    AND (State = 'TX' OR State = 'NY')
    AND (Age BETWEEN 20 AND 30) ;

Limiting Results

The LIMIT clause is used to restrict the number of rows returned by a query. It’s often used for pagination.

Example: Retrieving the first 5 users

SELECT FirstName, LastName 
FROM Users 
LIMIT 5;

While most databases support the LIMIT keyword, Microsoft SQL Server has a different syntax for selecting top N rows. It uses the TOP clause in the SELECT clause instead of LIMIT clause at the end of the query.

SELECT TOP 5 FirstName, LastName 
FROM Users;

What Next?

As you continue your SQL journey, practice these commands in a real database environment. Experiment with different queries, explore advanced topics like subqueries and window functions, and don’t hesitate to refer to documentation or online resources when needed. Happy querying!

Conclusion

These intermediate SQL techniques build on your foundational skills and prepare you for more complex queries and database operations. Keep practicing with real data and try combining techniques for more advanced results. In the next part of this series, we’ll dive into stored procedures, triggers, transactions, and performance tuning.

Have questions or want more tutorials? Let me know in the comments or connect with me on LinkedIn!

Related Articles

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

One thought on “SQL for Beginners – Part 2: SELECT Commands with Examples”

Leave a Reply

Your email address will not be published. Required fields are marked *