Disclosure: This post contains affiliate links. I may earn commission from any sales made or actions taken as a result from users clicking the links on this page.



Best SQL Projects to Spark Up Your Resume

Posted in SQL
SQL Projects

A Little Bit About SQL

SQL or Structured Query Language is a popularly used language to communicate with the databases. It has commands through which we can easily select data from the database, insert or update into the database and run multiple queries all at once. We can create an entire SQL schema through SQL like creating tables, creating the relationships between tables through key constraints, modifying table structure, delete data or table, etc.

SQL is also one of the essential languages for data science. Operations such as sorting, grouping, aggregation, finding minimum and maximum value, and many more can be easily done through SQL on huge amounts of data without compromising performance. Learn SQL through tutorials.

Installing SQL

The best way to master the below SQL projects and many more SQL concepts is to practice along. For that, you can install SQL Server or Oracle, which are available free of cost. You can download the latest version of the SQL here.

Top SQL Projects

The best way to test your knowledge of any subject is through projects. Some projects of different levels which you should try are mentioned below. SQL projects are delightful and interesting and give you the chance to learn the language thoroughly. Let us begin:

1. Online Phone Shop Display

To create an online phone shop, we need to store some data and then retrieve it when the user clicks on details. In this project, we will first add and then retrieve the data. This will be two calls to the database. The first page will show all the phones (names) and a link ‘more details,’ and once the user clicks on the link, all the phone details will be shown. This project will not show the UI part but will write corresponding queries to get the correct data. Let’s first create the table and store some data.

create table phone_details (name varchar2(100), description varchar2(255), availability char, price float, in_stock int, rating float);
insert into phone_details values ('Samsung 7x', 'phone with 8mp camera & voice recording', 'y', 3999.0, 3, 4.5)
insert into phone_details values ('sony 98x', 'phone with 32GB memory & Bluetooth', 'y', 6999.0, 5, 4)
insert into phone_details values ('realme 3', 'phone with 8mp camera & wifi calling', 'y', 4999.0, 10, 3.5)
insert into phone_details values ('one plus 4', 'phone with 64GB memory, wifi, Bluetooth, 16mp camera', 'y', 9999.0, 7, 4.5);

For sample purposes, we have shown only four rows here; you can add more for practice. This project intends to show how to insert different types of data. Now coming to the display of data. The first query executed to get the names will be:

select name from phone_details;

which will fetch the rows as:

Example 1

If the user clicks on ‘more details’ on the UI, the query will be:

select description, price, in_stock, rating from phone_details;

Now we will get the data as:

Example 2

Note that these objects will be stored in their corresponding beans and then used for display. Since availability is an internal check, and we already have fetched the ‘name’ column, we are not doing a ‘*’ here.

2. Project to Store Documents, Videos, and Music to SQL Server Database

This is a mini project that will walk you through the storage of media files and documents into the SQL server database. The YouTube video shows the use of Visual Studio and SQL Server to do the same, so make sure you have both these installed on your system. Check the video here and try the same on your system.

3. e-ticket Booking

In this project, we will try to book a railway ticket. Note that once certain seats are booked, they should be blocked so that no other user can book them again. Also, if a user is already booking, another user cannot access the same. Many factors are involved in a ticket reservation – passenger details, train details, route details, station details, booking details, and so on. Here is a link to the complete DBMS project for railway e-ticket booking. You can do similar projects for bus or flight bookings too!

4. Book Store Inventory Management

This is an exciting and easy project where a user can check all the books online. They can also order a book online, in which case the inventory will get updated, and the book count will reduce. Users can also use filters to search for a particular book based on book name, author name, price, etc. They can also search for books from a particular author. Admins can add books or delete books. Here are some important queries that are used for this project.

Creating the table

create table book_details (book_name varchar2(100), author_name varchar2(100), book_description varchar2(255), price float, in_stock int, primary_keyword varchar2(10), secondary_keyword varchar2(10), rating float);

Inserting Data

insert into book_details values ("Let us C", "Yashwant K.", "Learn C programming", 200.25, 3, "C", "software", 4.5);
insert into book_details values ("Let us C++", "Yashwant K.", "Learn C++ programming", 230.25, 5, "C++", "C", 4.5);
insert into book_details values ("Head first Java", "Kathy Sierra", "Learn Java programming", 330, 10, "Java", "JVM", 3.5);
insert into book_details values ("Java: The complete reference", "Mc Graw Hill", "7th edition, learn Java programming", 783, 4, "Java", "Javabeans", 4);
insert into book_details values ("Thinking in Java", "Bruce Eckel", "Java programming basics", 3000, 5, "Java", "JVM", 4.5);

Now, let's select only Java books:

select * from book_details where primary_keyword like '%java%';

Example 3

Let’s select only those Java books that have a rating greater than 4:

select * from book_details where primary_keyword like '%java%' and rating > 4;

Example 4

Now, search for books based on author name:

select * from book_details where author_name like '%Yashwant%';

Example 5

Suppose a user selects the book ‘Let us C’ for purchase. After the purchase, the database has to be updated with the latest count. For this, we have first to get the current stock and then reduce it.

select in_stock, book_name, price from book_details where book_name = 'Let us C';
update book_details set in_stock = in_stock-1 where book_name = 'Let us C';
select * from book_details where book_name = 'Let us C';

Example 6

To delete books from the database that have a rating less than 4, we should write the query as:

delete from book_details where rating < 4;

This will delete all the entries having book ratings less than 4.

5. Customer Order Management

In this project, some customers place orders for various listed products. This project will demonstrate how the primary key becomes important to create a relationship between two tables. In this case, there are two tables: customers and orders. The primary key customer id of the customer's table is used in the orders table. The orders table can have multiple entries with the same customer id. Also, there might be customers who do not have any pending orders – this can be seen by joining the two tables. This challenge from khan academy explains the queries to create and manage a simple customer order management database. You can add more data to test various conditions.

6. Student Registration for an Online Portal

In this simple project, we will get the student details and insert them into the database. A unique student roll number (student_id) will be generated once the registration is complete. If the student is already registered, we will send a message saying the student already exists.

create table student (id integer primary key autoincrement, student_name varchar2(50), dept varchar2(10), major_subject varchar2(10), student_id varchar2(50), email varchar2(50) not null, pwdhash binary(64) not null);

Note that we are using binary(64) to store the password in an encrypted manner. This means we cannot directly insert the user's details but process it before inserting it into the table. Same way, to generate a unique student id, the university decides to combine the fields id and dept, which is dependent on the values that the student enters. To process all the values, we need to write a stored procedure!

For passwords, we use the hashbytes function by specifying the encoding type and password entered by the user: hashbytes('SHA2_512', pwdhash)

For the student_id field, we can use the concat function as student_id as concat(id, ‘_,’ dept)

Here is the basic structure of a stored procedure that you can use for this project:

CREATE PROCEDURE dbo.addStudent
email VARCHAR2(50),
pwdhash binary(64),
student_name VARCHAR2(50),
dept VARCHAR2(10),
major_subject VARCHAR2(10),
student_id VARCHAR2(50) as concat(id,'_',dept),
responseMessage NVARCHAR(250) OUTPUT
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
INSERT INTO student values(student_name, dept, major_subject, student_id, email, pwdhash);
SET @responseMessage=' Success.'
END TRY
BEGIN CATCH
SET @responseMessage=ERROR_MESSAGE()
END CATCH
END

7. Bank Database Display

Yet another interesting project, this project performs all the banking related operations. The project is done using Python and MySQL. There are multiple nested menus, and users can insert, display, update, withdraw, and deposit from the account. This is an interactive project and introduces the reader to the concept of cursors. You can view the project on this YouTube link.

8. Data Summarization and Categorization (Data Science)

SQL is extensively used in data science data analysis, making it a very useful skill for data science projects. This project will learn about filtering, sorting, aggregation, categorization, merge, etc., functions using SQL. Check out this Airbnb data analysis SQL project.

9. Library Management System

This is a mini project for beginners and explains triggers, transaction logic, and updates and deletes on the database. The project uses Visual Studio C# and a local database. Check these YouTube videos for the implementation with an explanation. There are two more videos after this one, which will follow once you understand this video. You can watch the previous videos from this mini project video series of 15 videos if you wish to.

10. Employee & Payroll Management System

This project will look at a database schema and how multiple tables are related to each other. The full project consists of other modules also, but we will focus on the SQL part.

The Entity-Relationship Diagram (ERD) shows relationships between tables like Employee, Company, Department, Grade, Payroll, User, etc. The admin manages the entire system. Here is the link to the complete employee and payroll management project.

Conclusion

SQL projects have a great scope, and you can expand each project as per your imagination. For example, with the student registration portal, you can add more columns to add more data like hobbies, previous study history, languages known, etc. You can also add a mentor/buddy table and relate it to the student table using the primary and foreign keys. Same way, you can build your own airline booking system on the same lines as that of the railway e-ticket booking system. SQL is simple, fast, and a very important aspect of data science and software engineering jobs. Learn SQL from scratch through these amazing courses listed on Hackr.io.

People are also reading:

Ramya Shankar

Ramya Shankar

A cheerful, full of life and vibrant person, I hold a lot of dreams that I want to fulfill on my own. My passion for writing started with small diary entries and travel blogs, after which I have moved on to writing well-researched technical content. I find it fascinating to blend thoughts and research and shape them into something beautiful through my writing. View all posts by the Author

Leave a comment

Your email will not be published
Cancel