This is the project to create a database using data in a spreadsheet in order to practice SQL queries.

The SQL skill for using a Relational Database Management System (RDMA) is required for many data-related positions these days. In the social media forums like Quora or Reddit, there are many people who search for a public database for practicing their SQL querying skills.

However, although there are many public data sets in a single spreadsheet, there are not many public databases online. Even if you found a data set for the topic you have interest in, the format of the data is usually just one spreadsheet, not a database for most cases. Therefore, it will be very useful to know how to convert a data set in one spreadsheet to a database with multiple tables fitting a relational database format. Knowing the process of this conversion can give us many chances to practice SQL querying skills with a variety of databases.

The goal of this project is to show how to convert a data in one spreadsheet to a relational database for SQL. After the database is created, I will perform several SQL queries using the database.

The original data set is Sample Sales Data (sample_sales_data.csv) from the Kaggle Datasets. It has 25 columns and 2824 rows. The explanation for each feature is as follows:

• ORDERNUMBER: the identification number for each order
• QUANTITYORDERED: the quantity ordered
• PRICEEACH: the actual price paid for the transaction in terms of percentage of the MSRP (variable across transactions)
• ORDERLINENUMBER: the number of the order line
• SALES: the amount of sales
• ORDERDATE: the order date
• STATUS: the shipping status (Shipped, Resolved, Cancelled, On Hold, Disputed, and In Progress)
• QTR_ID: the quarter of the order date
• MONTH_ID: the month of the order date
• YEAR_ID: the year of the order date
• PRODUCTLINE: the category of products
• MSRP: the manufacture’s suggested retail price (constant across transactions)
• PRODUCTCODE: the identification code for each product
• CUSTOMERNAME: the names of customers
• PHONE: the phone numbers of customers
• ADDRESSLINE1: addressline 1 for customers
• ADDRESSLINE2: address line 2 for customers
• CITY: city names for customers
• STATE: state names for customers (only for customers located in the US)
• POSTALCODE: postal codes for customers
• COUNTRY: countries for customers
• TERRITORY: the regional names of each country (NA, EMEA, Japan, and APAC)
• CONTACTLASTNAME and CONTACTFIRSTNAME: the last and first names of customers
• DEALSIZE: the deal sizes of orders

The explanation of how to create the Entity Relationship Diagram for this data set is in the following link: https://medium.com/swlh/creating-a-database-converting-a-spreadsheet-to-a-relational-database-part-1-2a9a228bf77a

The final version of the ERD for this data is as follows:

How to create tables and insert records in MySQL is explained in detail here: https://medium.com/@yjeong5126/creating-a-database-converting-a-spreadsheet-to-a-relational-database-part-2-faf4fc83bae5

• The SQL query questions that I solved for this database are in sql_queries_questions.txt.
• The solutions for this questions are in sql_queries_solutions.sql.