Within the scope of this project, you are expected to get used to the use of PostgreSQL, open source object-relational database system. In addition, it is expected that you will be able to query all possible SQL commands and create your own queries according to the desired output.
In the project, Task A part is given for you to install and get used to the programs (You do not need to put a screenshot in the document.).The main part you need to do is Task B, Task C and Task D. For Task B, there should be full screenshots of each query in the document. Projects of those who write the query on paper or cut the image will not be evaluated. The query and the output of the query should be clearly visible inside the image. For Task C, the queries should be handwritten on a piece of paper and the photograph should be attached to the document. And lastly, for Task D you must upload the updated Project_1.py as StudentNumber_Project_1.py and add the output images to the document.
You must submit the project as a single file in the “StudentNumber.pdf” format and StudentNumber_Project_1.py file. The project is individual and each student is expected to do it himself. In projects that are taken from each other or that are understood to be duplicates, all projects sent by the student will not be evaluated and the student’s overall lab grade will be considered “0”.
Task A: Getting Set Up
Before starting, you need to download PostgreSQL program from the “https://www.postgresql.org/download/” and install it on your computer. For more detailed information, the documents in the “https://www.postgresql.org/docs/” file can be examined.
After installing, you can access the program from the command prompt as shown in Figure 1.
Important Note: If you have not added the path of the PostgreSQL program to the system paths, you must work in the file location of the “bin” folder the program when running it at the command prompt.
For connection, you need to type the following command. psql –U postgres –h localhost
After running that command, the password you specified during installation will be asked. After this process, your database connection is made and you can see that you are currently running “postgres=#”. Now it’s time to create the database you will be working on.
create database mydb;
The “\l” command shows the existing databases, the “\c” command shows which user is connected to the database, the “\d” command shows the properties of the table. The “\q” command logs out of the system.
You can also use the pgAdmin 4 program that comes by default in the installation instead of the command prompt. If it was not installed by default during installation, you can download it from the “https://www.pgadmin.org/download/” page. Of course, the pgAdmin program will also ask you for the password you set during installation for the connection. As you can see in Figure 2, all database operations on the command prompt screen are automatically detected by the pgadmin program. To run a query in the pgAdmin program, you must press the “Query Tool” button marked in blue in Figure 2.
Figure 1. PostgreSQL database connection example with command promt
Task B: Querying – SQL!
Figure 2. Example of PgAdmin 4
Now, after getting a little familiar with the program, we come to the part where you have to do it!!! In this part, you need to import the attached northwind.sql. You can perform the queries on the command screen or in pgAdmin. This choice is up to you!
You can see the database structure in Figure 3. You must examine the recorded data in each table before performing the queries. The given database is widely used. You can search the internet for your questions about the database.
Attention!!! Each student should make the database name they create as their student number. Also, each student should create a schema named StudentNumber_Project1 and import the given .sql file in there.
Figure 3. Nortwind Database
Write standard SQL queries to answer the following questions:
1. By finding the total price (unit_price*quantity) and discounted total prices (unit_price*quantity*(1-discount)) of the products, search for the top 10 when you sort them from high to low based on the discounted price calculation.
Attention!!! In order to get full marks, you need to obtain the same query outputs (pay attention to the column names and order). You are requested to specify the tables that you should use for queries. Therefore, it is expected that each student’s query will be unique.
Figure 4. Question-1 Output
2. Query the total price of the products whose shipped_date value is between 1997-12-30 and 1998-1-5, and the data whose shipped_date value is null and the total price is greater than 4000.
Figure 5. Question-2 Output
3. Query the data whose discontinued value is “0” and whose reorder_level value is higher than 20 and units_on_order value is 0.
Figure 6. Question-3 Output
4. In the specified columns, query the data that ends with “y” for Ship_country, starts with “M” for customer_id, and whose freight value is greater than 70.
Figure 7. Question-4 Output
5. Write a query that calculates the total discounted price of all products by years (if there is no product, the value “0” will be written) and shows them by creating columns according to the years, with product_id less than 5 and customer_id starting with “E”.
Figure 8. Question-5 Output
6. Combine the Customers and Suppliers tables and query the data containing the letter “w” in the contact_name in the costumer table and the letter “g” in the suppliers contact_name.
Figure 9. Question-6 Output
- Show the first 5 products with the highest unit_price value and the first 5 products with the lowest, in order by unit_price.Figure 10. Question-7 Output
- Query to sum of the sales made since June-1997 on a yearly basis according to the category_name. (Set to write two digits after the dot.)
Figure 11. Question-8 Output
Task C: Relational Algebra – SQL!
Now, you are expected to rewrite the SQL queries you wrote in Task B using relational algebra. As mentioned before, you are expected to write the solutions manually on a piece of paper and attach the photo to the document. Queries should be clearly visible.
Task D: Connect the Database in Python
In this part, you are expected to connect to the database you have created using python and execute the queries you have written. To connect to the database, you must first have python installed on your system. Then, you should then run the following command.
python -m pip install psycopg2-binary
After the installation is complete, you need to run the Project_1.py file given to you by filling in the required places and add the screen outputs you received as in the image to your document (Figure 12). You need to save the updated Project_1.py file as StudentNo_Project_1.py and upload it to the system.
Figure 12. Question-1 Output with Python
