You have learned SQL, are proficient in using it, and trying to land a job that requires strong SQL knowledge. Now, you have to prove your knowledge and skills, and you are searching for some good projects that will boost your resume by showing what you can actually do with SQL and relational databases.

Typically, the type of projects you should have on your portfolio are ones that can clearly show a few things:

• You know relational database theory, and how to create good database schemas
• You know how to connect your entities in the best possible way

• You can write complex queries to get the required data

  Bonus:

• You can create stored procedures, views, and aggregators

• You can create complex database schemas

The same goes if you are only trying to learn, train yourself, and get the most out of your practices to gain new knowledge and become more proficient in SQL.

The best way to test and show your knowledge of any computer science subject is through projects. Some projects involve different levels of SQL complexity and expertise, can be very interesting, and give you the chance to understand and practice the language thoroughly.

Let us begin! We created a few examples and ideas for your next projects, that will help you prove your SQL skills, help you gain new knowledge by creating them, and show the employers you can do anything with SQL. Along with these ideas, we are going to illustrate their respective ER diagrams, so you can have a better overview of the idea behind the examples.

College or school database

By making a project such as college or school database, you can do a variety of operations. The main objective of this project will be to handle all the details of students’ and proffessors' data, as well as their courses. The main data properties that this database should have are the following:

• Student: name, phone number, email, address, year of admission, courses applicable, etc.
• Other important things such as student's attendance, their grade sheets, scholarships, and tests
• Data for the teachers, same as for the students (name, contact details, courses, salary, hours)
• Course: name, description, duration..

Query examples:

1. Get all the teachers that have given D to the students with year of admission 2020.

2. Get all the different courses that the students born in 1995 have taken in the past 3 years.

In the following ER diagram we will show a simplified version of the school database:

Bicycle rental database

This database can be implemented around the entity Bicycle and its properties. Usually, there should be detailed info about the bicycle as well as a table for the rental properties.

A user should be able to rent the bike from a certain location, and maybe return it to a different location in the city. The user should pay for the time spent with the rented bike, at the end of the rental.

Given this, the data in this database should be the following:

• Bike details: year, model, tires, gears, etc
• User details: name, surname, ID number, phone number...
• Rental details: date, price, location from, location to ...

Query examples:

1. Get the bikes from the brand XYZ that have been rented in the past 3 days.

2. Get all the bikes that have been rented from location X and returned to location Y.

The data should be structured like in the following example ER diagram:

Hospital management database

This type of project is a more complex database and it can allow you to deal with the working of a hospital or any other medical setup. It should keep records of patients, doctors, and rooms, which can then be queried and used. If this database was to expand, there could be separate modules for hospital admission, patients’ summary, duties of nurses, medical stores, etc.

We will have the following data in this database:

• Patients information: name, surname, date of birth, SSN, insurance number, age, etc.
• We can have records for the patient's chronic diseases
• Doctor's information: name, surname, date of birth, employment date, specialty, salary, etc.
• Patient's treatments and doctor consultations - date, patient Id, doctor Id, notes from the consultation, diagnosis
• Rooms in the hospital can be managed by keeping data about roomNumber, category (operation hall, doctor's office, etc.), availability, etc...

A bonus for this project is if you go a step further and do actual hospital departments, with specialist doctors, and all available treatments for patients.

Query examples: 1. Find all the doctors employed for more than 5 years, that have treated patients with diagnosis X in the past 10 days.

2. Find all the patients younger than 45 that have not come to the hospital in the past year.

For the purpose of this tutorial, we will show you a shortened example of the hospital database ER diagram, that according to the needs can be extended into a larger database.

Comic books database

This project could be something like a personal collection database, for people that collect comic books, or any other kind of books. This is a simple database, but can be a fun project for a student to make, and even maybe build an application on top of it.

The data in this database will be around the entity Comic and its details. The properties can include the following:

• Comic name, year, category, character Id, date of purchase, gifted by (if the comic was a gift)
• And we can have separate tables for main characters and the categories (genres) so we can connect them to the 'Comic' table.

Query examples:

1. Find all the Superman comics released in 1998.

2. Find all the Batman comics gifted by Anne.

The data should be structured like in the following example ER diagram:

Cellphone shop database

This would be a kind of an online-shop database, specialized for cellphones and accessories.

The products sold will be different models of cellphones, earphones, phone cases, chargers, and any other type of cellphone accessories. The user can purchase a product, and therefore will receive an invoice for the purchase. The users should be registered in the system, thus should have a separate table for them.

The database should have data like the following:

• User data: name, surname, date of birth, email, address, phone number
• Products: category, model, color, name, year, brand, items available, price
• Product specs: CPU, screen, battery, RAM memory, internal storage, slots, etc..
• Invoices: date of purchase, userId, productId, price, payment type, shipping address, etc. Query examples:

1. Find all the Samsung phones, cheaper than $799 with RAM memory of 4GB.

2. Find all the phones with a 6-inch screen that have been released after 2019.

The data should be structured like in the following simplified ER diagram:

One Pager Cheat Sheet

• You should create projects that demonstrate your ability to use SQL and create complex database schemas in order to prove your knowledge and skills and get the most out of your practice.
• This project creates a database of college or school data, capturing pertinent info for Students and Teachers, as well as course data, and enables querying of this data to access useful information.
• This database can be used to store details about bicycles, users and rentals, and to query for example about bikes rented in the past 3 days or bikes rented from one location and returned to another.
• This hospital management database project can allow you to query records and data of patients, doctors, and rooms, as well as have modules and data for hospital departments, specialist doctors, treatments, etc.
• This project is a simple database that holds information related to Comic entities, including Comic name, year, category, character Id, date of purchase, and gifted by properties, and can be used to answer queries such as Find all the Superman comics released in 1998 and Find all the Batman comics gifted by Anne.
• A database should be created to record user data and product data such as specs, name, color, brand, and price to allow queries to be made for products such as Samsung phones under $799 and phones with 6-inch screens released after 2019, as illustrated in the ER diagram.