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

                DIVING INTO (SQL) USING POSTGRESQL

TABLE OF CONTENTS

1. Introduction to SQL
2. Why SQL is important
3. Database concepts
4. Steps to connect to a database and making a table

5. 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

_Commands in SQL _

_Command that make changes in the table _

Commands that are comparison/Aggregate functions

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; 

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

SELECT AVG(subject_score) as average_score 
FROM performance;

1. 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;

1. 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)
);

1. 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