INTRODUCTION
For this week's SQL assignment, I was given a practical scenario involving Greenwood Academy, a secondary school in Nairobi. The task was to take on the role of a new database administrator and build a school database from scratch, populate it with information, and then use SQL queries to retrieve and work with the data.
At first, the assignment looked like a lot because there were several different SQL concepts involved. However, as I worked through it step by step, I realized that each section was connected to the next. I started by creating the database structure, added the required information, and finally used queries to make sense of the data.
WHAT THE PROJECT IS ABOUT:
We are required to build the data the database from scratch and start querying it, let's go step by step on how we are going to do that.We will start by
1.creating a schema called greenwoods_academy;
the schema will act as a blue print to ensure our data is stored, exchanged and validated consistently
2.Creating tables
Tables store information that is going to be used in the query. Tables usually have keys that are important in building databases. Keys help identify records and where relationships are defined, connect information between different tables examples of the keys are primary and foreign key. In our database we have 3 tables that we are going to create;
(a)create the students table
This table store the students information. The primary key here is the students_id
(b)create the subjects table
Here the subjects information is going to be stored. The primary key is subject_id
(c)create the exam_results table
It contains the exam information. The primary key is result_id
3.Altering the tables
Databases can be changed to suit the users requirements after they have been created, this is very suitable because real world databases might need some adjustments. The school forgot to add a phone_number column, it also needed to rename a column to credit_hours and delete the phone number column. we will use the ALTER function to make these changes.
4.filling the database
We will now insert data to the tables we created. Here we use the Data manipulation language. I mainly worked with insert, update and delete. You have to be keen when inserting the data to get the right thing
Esther Akinyi moved from Nakuru to Nairobi, we will use update to adjust the query
In the result_id column we entered 59 instead of 49
Result for result_id 9 was cancelled, we use delete to adjust the query
It is very important we have these operations since data in the real work keeps changing
5.Querying the data
Here we retrieve information from the query and we use the where clause. We will first find all students who are in form 4 and all subjects in the science department.
Now we find all exam results whose marks are greater than or equal to 70 and all females only
! Image description
Using and, or
they are used to combine different conditions. For and all conditions must have been satisfied while or either of the two must have been satisfied.
Queries allow you to easily search for data rather than doing it manually.
6.Working with operators
The operators we are going to use here are between, in, not in and like Between returns values within a range with the boundaries included example 50 and 80 will be included
In matches any value in a list, not in excludes values in a list while like matches a text pattern
7.Using count
We use count to get the number of records, that matches your query. In our database we will find the number of students who are in form 3 and those that have marks 70 or above
8.Using case when
We use case when to assign labels or description to numbers or values
what I learnt
The biggest thing I learned from this assignment is that SQL is not just about writing individual commands. There is a process involved.
First, I have to create the structure of the database. Then I insert the data, make corrections when necessary, and finally query the information I need.
I also got more comfortable with commands such as CREATE, ALTER, INSERT, UPDATE, DELETE, SELECT, WHERE, BETWEEN, IN, LIKE, COUNT(), and CASE WHEN.
Another important lesson was the importance of being precise. A small mistake in a table name, column name, condition, or value can cause a query to fail or return the wrong information. Working through the assignment helped me become more careful with my SQL syntax.
final thoughts
Overall, this assignment gave me a more practical understanding of how databases work. Instead of only learning SQL commands individually, I got to use them in a situation that resembles something a real school might need.
Starting with an empty database and gradually building it into something that could store student, subject, and examination information made the different SQL concepts easier to connect.
The assignment also helped me see how SQL can be used to organize information, correct mistakes, search through large amounts of data, and turn raw data into useful information. Going forward, I feel more confident about using SQL for larger and more realistic database projects.
Top comments (0)