INTODUCTION
Creating Greenwood academy database is essential for managing the students, subject and exam results efficiently. PostgreSQL, a powerful open-source relational database system, offers the perfect foundation for such a project.
The main areas areas in SQL covered in this projects are :
1. DDL (Data Definition Language)
DDL commands define, modify, and change the physical structure of database objects like tables and schemas.
The first step is to create a greenwood academy schema using the create command.
create schema greenwood_academy;
set search_path to greenwood_academy;
Next is to crete tables in the schema;
The schema has 3 tables students,subject and exam results.
create table greenwood_academy.students(
student_id INT PRIMARY key,
first_name VARCHAR(50) NOT null,
last_name VARCHAR(50) NOT null,
gender VARCHAR(1),
date_of_birth DATE,
class VARCHAR(10),
city VARCHAR(50)
);
create table greenwood_academy.subject(
subject_id INT PRIMARY key,
subject_name VARCHAR(100) NOT null unique,
department VARCHAR(50),
teacher_name VARCHAR(100),
credits INT
);
create table greenwood_academy.exam_results(
result_id INT PRIMARY key,
student_id INT NOT null,
subject_id INT NOT null,
marks INT NOT null,
exam_date DATE,
grade VARCHAR(2)
);
ALTER- This command changes the structure of tables in a database.
Core Actions You Can Perform
Add columns: Insert a new column and its data type into a table.
The school realised that the nthey forgot to add phone numbers in the students table. The following command is used to add the data
alter table greenwood_academy.students
add column phone_number VARCHAR(20);
Rename colums: Change the name of a table or a column.
The column credit has to be changed to credit hours
alter table greenwood_academy.subject
rename column credits to credit_hours;
Drop columns: Delete an unwanted column from a table.
Later the school relised that the phone number column is nolonger needed.
alter table greenwood_academy.students
drop column phone_number;
Once all the columns names in the tables are perfect, the next step is to add data in the 3 tables using the INSERT command.
insert into greenwood_academy.students(student_id,first_name,last_name,gender,date_of_birth,class,city)
values
(1, 'Amina',' Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
(2, 'Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4',' Mombasa' ),
(3, 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
(4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
(5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2',' Nakuru'),
(6, 'Felix', 'Otieno','M', '2009-09-14', 'Form 2',' Eldoret'),
(7, 'Grace', 'Mwangi', 'F', '2008-01-22', 'Form 3', 'Nairobi'),
(8, 'Hassan', 'Abdi', 'M', '2007-04-09', 'Form 4', 'Mombasa'),
(9, 'Ivy','Chebet', 'F', '2009-12-01', 'Form 2', 'Nakuru'),
(10, 'James', 'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi');
insert into greenwood_academy.subject(subject_id,subject_name,department,teacher_name,credit_hours)
values
(1,'Mathematics','Sciences','Mr. Njoroge',4),
(2,'English','Languages','Ms. Adhiambo',3),
(3, 'Biology','Sciences','Ms. Otieno',4 ),
(4,'History', 'Humanities', 'Mr. Waweru',3),
(5,'Kiswahili', 'Languages', 'Ms. Nduta', 3),
(6,'Physics','Sciences', 'Mr. Kamande',4),
(7 ,'Geography', 'Humanities','Ms. Chebet',3),
(8, 'Chemistry','Sciences','Ms. Muthoni',4),
(9,'Computer Studies','Sciences','Mr. Oduya',3),
(10,'Business Studies', 'Humanities', 'Ms. Wangari',3);
insert into greenwood_academy.exam_results(result_id, student_id, subject_id, marks, exam_date, grade)
values
(1, 1, 1, 78, '2024-03-15', 'B'),
(2, 1, 2, 85, '2024-03-16', 'A'),
(3, 2, 1, 92, '2024-03-15', 'A'),
(4, 2, 3, 55, '2024-03-17', 'C'),
(5, 3, 2, 49, '2024-03-16', 'D'),
(6, 3, 4, 71, '2024-03-18', 'B'),
(7, 4, 1, 88, '2024-03-15', 'A'),
(8, 4, 6, 63, '2024-03-19', 'C'),
(9, 5, 5, 39, '2024-03-20', 'F'),
(10, 6, 9, 95, '2024-03-21', 'A');
Checking if the data has been inserted in the tables using the select statement
select* from greenwood_academy.students;
select* from greenwood_academy.subject;
select* from greenwood_academy.exam_results;
2. DML - Data Manipulation Language
DML commands are used to manipulate the data stored in database tables. With DML, you can insert new records, update existing ones, delete unwanted data or retrieve information
One student, Esther Akinyi has moved from Nakuru to Nairobi.*UPDATE * statement is used to change the city of the student.
update greenwood_academy.students
set city= 'Nairobi'
where student_id =5;
changing the marks of a student
update greenwood_academy.exam_results
set marks = 59
where student_id =5;
Deleting result id 9
delete from greenwood_academy.exam_results
where result_id =9;
*3. DQL (Data Query Language) *
DQL is used exclusively for fetching or reading data from the database without altering it.
To retrieve data from the tables, the SELECT statement is used.
Note: DQL has only one command, SELECT. Other terms like FROM, WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT and LIMIT are clauses of SELECT, not separate commands.
--All students in Form 4
select *
from greenwood_academy.students
where class = 'Form 4';
-- Subjects in the science department
select subject_name
from greenwood_academy.subject
where department = 'Sciences';
--Exam results greater that 70
select *
from greenwood_academy.exam_results
where marks >=70
order by marks;
-- Female students in the school
select first_name,last_name
from greenwood_academy.students
where gender = 'F';
Logical operators in SQL
AND- It connects multiple conditions in a clause (such as WHERE or HAVING) and evaluates them to return records only when all individual conditions are true.
-- Students that are in Form 3 and live in Nairobi
select first_name,last_name
from greenwood_academy.students
where class = 'Form 3' and city = 'Nairobi';
OR- It returns a row if at least one of the specified conditions evaluates to true.
-- students that are either in Form 2 of Form 4
select first_name,last_name,class
from greenwood_academy.students
where class = 'Form 2' or class ='Form 4';
BETWEEN - Filter data within a specified range.
--Marks between 50 and 80
select *
from greenwood_academy.exam_results
where marks between 50 and 80
order by marks;
-- all exams that took place between 15th March 2024 and 18th March 2024
select *
from greenwood_academy.exam_results
where exam_date between '2024-03-15' and '2024-03-18'
order by exam_date;
IN - Checks if a specific column's value matches any value within a literal list or a subquery result.
-- All students who live in Nairobi, Mombasa, or Kisumu
select first_name,last_name,city
from greenwood_academy.students
where city in ('Nairobi','Mombasa','Kisumu');
NOT IN- Exclude a specific list of values from your query results.
--All students who are NOT in Form 2 or Form 3.
select first_name,last_name,class
from greenwood_academy.students
where class not in ('Form 2','Form 3') ;
LIKE-Operator searches for partial text patterns using wildcards, while the IN operator checks for exact matches against a list of values.
--All students whose first name starts with the letter 'A' or 'E'
select first_name,last_name
from greenwood_academy.students
where first_name like 'A%' or first_name like 'E%';
Count - Allows you to count how many records match a specific list of values.
--Number of students in Form 3
select count(student_id)as total_students, class
from greenwood_academy.students
where class = 'Form 3'
group by class;
CASE- Adds conditional IF-THEN-ELSE logic directly inside your database queries.
--Label each exam result with a grade description:
select result_id,
marks,
case
when marks >= 80 then 'Distinction'
when marks >= 60 then 'Merit'
when marks >= 40 then 'Pass'
else 'Fail'
end
as performance
from greenwood_academy.exam_results
order by marks;
CONCLUSION
By walking through these SQL commands, we’ve built a complete Greenwood Academy Database in PostgreSQL — from creating tables and inserting records to querying relationships and managing updates.
PostgreSQL’s flexibility ensures that Greenwood Academy’s data remains structured, scalable, and ready for analysis, making it a powerful tool for turning raw records into actionable insights.
Link to github: https://github.com/Libuko95/sql-week2-assignment-Jedidah-Ondiso
Top comments (0)