Unraveling the World of SQL: A Hands-On Guide with Example Tables

Structured Query Language (SQL) is the language of databases, providing a powerful and standardized way to interact with and manage data. In this article, we’ll explore essential SQL queries using an example table, “Students,” to demonstrate real-world scenarios.

Example Table: Students

Let’s start by creating the “Students” table, representing a fictional dataset of students and their academic information.

-- Create Students Table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Grade FLOAT,
    CourseID INT
);

-- Insert Data into Students Table
INSERT INTO Students VALUES
    (1, 'Alice', 'Smith', 22, 85.5, 101),
    (2, 'Bob', 'Johnson', 20, 78.0, 102),
    (3, 'Charlie', 'Davis', 21, 92.3, 101),
    (4, 'Diana', 'Miller', 23, 76.8, 103),
    (5, 'Evan', 'Wilson', 22, 88.9, 102),
    (6, 'Fiona', 'Brown', 21, 95.2, 103),
    (7, 'George', 'Taylor', 20, 81.7, 101);

Now, let’s dive into various SQL queries with examples and their corresponding outputs.

SELECT Statement:

Retrieve all columns from the “Students” table.

-- Select all columns from Students table
SELECT * FROM Students;

Output:

StudentID     FirstName   LastName      Age      Grade      CourseID
1             Alice       Smith         22       85.5       101
2             Bob         Johnson       20       78.0       102
3             Charlie     Davis         21       92.3       101
4             Diana       Miller        23       76.8       103
5             Evan        Wilson        22       88.9       102
6             Fiona       Brown         21       95.2       103
7             George      Taylor        20       81.7       101

WHERE and ORDER BY Clauses:

Retrieve students older than 21, ordered by their grades in descending order.

-- Select students older than 21, ordered by Grade in descending order
SELECT * FROM Students WHERE Age > 21 ORDER BY Grade DESC;

Output:

StudentID     FirstName     LastName       Age      Grade      CourseID
4             Diana         Miller         23       76.8       103
1             Alice         Smith          22       85.5       101
5             Evan          Wilson         22       88.9       102

GROUP BY and Aggregate Functions:

Count the number of students in each course.

-- Count the number of students in each course
SELECT CourseID, COUNT(StudentID) AS StudentCount FROM Students GROUP BY CourseID;

Output:

CourseID        StudentCount
101             3
102             2
103             2

JOIN Statements:

Retrieve the names of students along with their corresponding course names.

-- Retrieve student names and course names using INNER JOIN
SELECT FirstName, LastName, CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

Output:

FirstName         LastName          CourseName
Alice             Smith             Mathematics
Bob               Johnson           Physics
Charlie           Davis             Mathematics
Diana             Miller            History
Evan              Wilson            Physics
Fiona             Brown             History
George            Taylor            Mathematics

By exploring these SQL queries with the “Students” example table, we’ve gained a practical understanding of how SQL can be used to retrieve, filter, and analyze data from databases. This knowledge is invaluable for QA Automation Engineers working with databases to ensure the efficiency and accuracy of their testing processes.