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:
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:
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%';
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;
Now, search for books based on author name:
select * from book_details where author_name like '%Yashwant%';
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';
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.