A COMPREHENSIVE LOOK INTO SQL : - What a Beginner can learn

Tiffany Wambui — Wed, 21 May 2025 13:12:04 +0000

                DIVING INTO (SQL) USING POSTGRESQL

TABLE OF CONTENTS

Introduction to SQL
Why SQL is important
Database concepts
Steps to connect to a database and making a table
Commands on a table
```
              INTRODUCTION TO SQL 
```
ABBREVIATIONS
SQL - Search Query Language
RDBMS - Relational Database management systems
NON-RDBMS - Non-relational database management systems

WHY IS SQL IMPORTANT
Structured Query Language (SQL) is a commonly used language for database administration that facilitates tasks like retrieving and manipulating data contained in databases. It is able to help data analysts derive data and make trends. It is also a very powerful tool used to manage databases and execute queries to assist in the deriving of data trends.

This article is meant to look into the introduction of SQL (Search Query Language) and better beginners on how SQL can be used and interacted with. There are key things we need to look into: -

KEYWORDS

Data Analytics - It refers to the process of examining, transforming, & modeling data in order to establish patterns and patterns to make data-driven decisions.
Data Science —Interdisciplinary field that utilizes scientific methods to derive data trends.
Database - This is an organized collection of data; it contains data from 2 types of databases: relational databases that contain structured data (the main type used) and non-relational databases that contains unstructured data.
Database Management Systems —software used to manage the databases that we use; we have (RDBMS) relational database management systems & (Non-RDBMS) non-relational database management systems. A type of RDBMS is PostgreSQL or MySQL an example of Non-RDBMS is Mongodb

                   DATABASE CONCEPTS

These database concepts are meant to familiarize you with the understanding of what a beginner student will encounter. This knowledge will help them better associate with data

Concept	Meaning
Schema	A collection of database objects (tables, views, etc.) associated with a database
Search path	is a list of schema names that PostgreSQL searches in order when you refer to a table without qualifying the schema name.
Table	A spreadsheet-like structured collection of data with rows and columns
Rows	A record in a table that contains data related to one item
Column	A particular field or attribute that contains a single sort of data, like a name, email address, or enrollment date

_Commands in SQL _

Command	Meaning
Select	Select data from database
From	Specify table we're pulling from
Where	Used to filter query to match a specified condition
As	Used in renaming column/table with alias
Joins	Is used in the combination of 2 or more rows from tables
And	Combines query conditions that, all conditions must be met
Or	Combines query conditions where at least one condition must be met
Limit	It is used to show only specific number of rows to be shown
In	Used to specify multiple values when using (WHERE) - Subqueries
Like	Looks to identify patterns in a column

_Command that make changes in the table _

Command	Meaning
Alter Table	Add/Remove columns from table
Update	Update table data
Create	Used in inputting TABLES, DATABASE, INDEX
Insert	Used to add a single row to a table
Drop	Used in the removal or delete from TABLE, DATABASE.

Commands that are comparison/Aggregate functions

Comparison	Meaning
Group by	Combines data in aggregate functions
Order	Set order of result, (ASC/DESC) ascending/descending order
Having	Similar to the function WHERE but it filters groups.
Count	It counts the number of rows in a table.
Sum	Return the sum total of a column
Avg	Returns the average of a column
Min/Max	Returns the minimum and maximum value of a column

Data Types in SQL
What is a data type? It refers to classifications of data; it tells Postgres/MySql how to interpret the data that has been inputted
E.g.

Text/string - It would be denoted by CHAR (), VARCHAR().
Numeric - It would be denoted by INT(), DECIMAL(), BOOLEAN().
Date & Time - It would be denoted by DATE(),TIME(), TIMESTAMP(). Primary Key - Enables records in a table to be uniquely identified Foreign Key -Uses the main key of another table to establish a relationship between them.

With that being concluded, we continue with the practical part of the SQL

             HOW TO CREATE/CONNECT TO A DATABASE

To best illustrate this, we will need to use a project. The project will be on teams of football and their scores. Project Name: - Student Awards

Step 1: - Create a database

The first order of business will be to create a database. the database will be called

create database: student_award;

This database will contain data from students, performance and the awards

Step 2 - Create a schema

The second step will be to create a schema; the name of the schema will be

create schema: Daystar02;

This to help use organize our data in line with a schema

Step 3 - Create the Students Table

This will be the first table we will use

Create Table students (
student_id = SERIAL primary key,
student_name = VARCHAR (50) is not null,
student_age = INT unique not null,
student_email = VARCHAR(100);

This table contains the following: student id, student name, student age, and student email.

Step 4 - Create the Performance Table

This table will be the second one

Create Table performance (
Subject_code = SERIAL primary key,
Subject_name = VARCHAR (100),
Subject_score = DECIMAL (10, 2),
Subject_lec = VARCHAR (100);

This table contains the following: subject code, subject name, subject score, and subject lecturer.

Step 5 Create the Award Table

This table will be the third one

Create Table awards (
Awards_code = SERIAL primary key, 
Subject_code = INT,
Student_id = INT,
Year = Date;

Step 6 Insert into Student Table

This we will be inputting data required for the database

INSERT INTO daystar01.student (student_id, student_name, student_age, student_email) VALUES
(1, 'Dorothy', 34, 'Dorothy1@gmail.com'),
(2, 'Wambui', 25, 'Wambui2@gmail.com'),
(3, 'Nicole', 23, 'Nicole3@gmail.com'),
(4, 'Mike', 27, 'Mike4@gmail.com'),
(5, 'Rachel', 29, 'Rachel5@gmail.com'), 
(6, 'Lewis', 20, 'Lewis6@gmail.com);

Step 7 Insert into Performance Table

This will be used to load the information into the performance table

INSERT INTO daystar01. Performance ( subject_code, subject_name, subject_score, subject_lec)
(21, 'Kiswahili', 67.6, 'Dan'),
(22, 'English', 74.5, 'Ted'),
(34, 'Math', 70.3, 'Vanessa'),
(43, 'History', 54.8, 'Peter');

Step 8 Insert into Awards Table

This will be used to load the information into the awards table

INSERT INTO daystar01. awards (award_code, subject_code, student_id, year)
(7.5, 21, 1, 2023), --- Dorothy took Kiswahili ----
(3.4, 43, 2, 2024), --- Wambui took History ----
(5.2, 34, 3, 2022), --- Nicole took Math ----
(2.3, 43, 4, 2025), --- Mike took History ---
(1.4, 21, 5, 2024), --- Rachel took Kiswahili ---
(5.6, 22, 6. 2023); --- Lewis took English ---

Step 9 Retrieving data from the tables

We will now use Select to get all the information from the tables

Select * from student;
Select * from performance;
Select * from awards;

                  ## Commands on a table

In order for this to be illustrated, we will need some questions
Questions
1 Retrieve all student names and their ages from the students table (Select Queries)

Select student_names, student_age
        From students;

Find the average score of all subjects in the performance table. (Aggregate Functions)

SELECT AVG(subject_score) as average_score 
FROM performance;

Find all subjects with scores greater than 70. (Conditional Queries)

SELECT subject_name 
FROM performance 
WHERE subject_score > 70;

4 Retrieve the list of subjects along with their average scores, but only for those subjects that have an average score greater than 60. Order the results by average score in descending order.

SELECT subject_name, AVG(subject_score) as average_score 
FROM performance 
GROUP BY subject_name 
HAVING AVG(subject_score) > 60 
ORDER BY average_score DESC;

Retrieve the names of students who have a score in any subject that is above the average score of all subjects. (Subqueries)

SELECT s.student_name 
FROM students s 
WHERE s.student_id IN (
    SELECT a.student_id 
    FROM awards a
    JOIN performance p ON a.subject_code = p.subject_code 
    WHERE p.subject_score > (SELECT AVG(subject_score) FROM performance)
);

Get the names of students who have received awards, along with the subjects they received awards for. (JOINS)

SELECT s.student_name, p.subject_name 
FROM awards a
INNER JOIN students s ON a.student_id = s.student_id
INNER JOIN performance p ON a.subject_code = p.subject_code;

These are short examples of what a beginner needs to know in SQL. Good luck

DEV Community: Tiffany Wambui