arpita-deb/sweet-symphony-dessert-shop-sql-analysis

Database design project: Building a business database from scratch using DDL, DML and DQL.

Sweet Symphony Dessert Shop Analysis in SQL

Database Design Project: Building a database from scratch

Introduction:

Sweet Symphony Desserts Shop is a fictional family owned business known for their variety of desserts and confectionaries. In this project I designed a business database to help the business owner keep track of their products, product categories, employee details, customers and order details.

Tool used:

This project is done in Microsoft SQL Server and SQL Server Management Studio(SSMS).

Project Overview:

In this project a database with 5 tables of a fictional dessert shop has been created using SQL CRUD Statements and different Data Manipulation Languages (DML). CRUD stands for CREATE, READ, UPDATE and DELETE that refers to major operations on databases. Each letter in the acronym can be mapped to SQL operational clauses.

CRUD statements	SQL Clauses
create	CREATE
read	SELECT
update	UPDATE
delete	DROP

The project is divided into 3 parts-

1. Creating the Tables using CREATE, ALTER and DROP Clauses
2. Inserting Values and updating them with INSERT INTO, SELECT, UPDATE, ALTER, DELETE FROM Clauses
3. Using Data Query Language (DQL) to answer some basic questions about the data

Analysis

Part 1: Creating the Tables

In this part, a Database named Sweet Symphony Desserts Shop and 5 tables have been created. The following Data Definition Language (DDL) commands were used-

• CREATE - to create the database and the tables
• DROP - to delete/remove the database objects from the SQL database
• ALTER - to change or modify the existing structure of the database

Here only 2 tables have been shown created.

Tables Created

• Categories table - individual products are grouped into 8 categories.

  

• Customers table - customers who have ordered products from Sweet Symphony.

  

• EmployeeDetails table - employees who work for Sweet Symphony.

  

• Products table - The details of products which Sweet Symphony sold in their first 6 months of opening.

  

• Orders table - Order details from the first 2 months of opening.

As can be seen from the database design diagram only Orders and Customers tables are joined together with the help of a Foreign key (CustomerID). Each table has individual Primary key and datatypes of the columns defined.

Part 2: Inserting and updating values in the tables

Data manipulation language (DML) is a family of computer languages that permits users to manipulate data in a database by inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. The following DML commands are used-

• SELECT - to show the records of the specified table
• INSERT - to insert data in database tables.
• UPDATE - to update or modify the existing data in database tables
• DELETE - to remove single or multiple existing records from the database tables.

Here values are inserted in the EmployeeDetails table.

Part 3: Answer some basic questions about the data using SQL

The following clauses and functions were used to obtain various information from the tables.

• WHERE - to filter data of interest

• GROUP BY - to group rows that have the same values into summary rows

• ORDER BY - to sort the data in ascending or descending order

• CONCAT - to add two or more strings together

• DATEDIFF - to return the difference between two date values

• DATEADD - to add a date to another date, then return the new date

• JOIN - to combine rows from two or more tables, based on a related column between them

• BETWEEN - to filter values in a range

• MAX - to obtain the maximum value of a numeric column

• MIN - to obtain the minimum value of a numeric column

• COUNT - to return the number of rows that matches a specific criterion

• SUM - to return the total sum of a numeric column.

• AVG - to get average value of a numeric column.

• LIKE - to search for a specified pattern in a column.

• IN - to specify multiple values in a WHERE clause
  
  
  

• How many categories of product did they sell?

  

  

• Show the total price of products sold by categories.

  

  

• What is the average price of the products sold?

  

  

• What are Sweet Symphony's Top 5 profitable products?

  

  

• What are Sweet Symphony's 5 least profitable products?

  

  

• What is their total revenue?

  

  

• Which product is most expensive?

  

  

• Which product is the cheapest?

  

  

• Show the full name of the employees.

  

  

• Show the employee names whose last name consists of 'er'.

  

  

• Show the orders from Portland in the month of July.

  

  

• The customer from Eugene, Oregon ordered some desserts from Sweet Symphony which they want a week later. Update the shippeddate to a week after and add a required date column.

  

  

• Calculate the difference of days between OrderDate and ShippedDate.

  

  

• Show the customer details who ordered products from Sweet Symphony.

  

  

Conclusion:

• A Database named Sweet Symphony Desserts Shop is created in Microsoft SQL Server.
• With the help of CREATE, ALTER, DROP clauses 5 tables have been created and altered.
• Values have been inserted into the tables using INSERT INTO, SELECT, UPDATE, ALTER and DELETE FROM Clauses
• Various information has been obtained using SELECT statement.
• Use of WHERE, GROUP BY, ORDER BY clauses have been shown.
• Different aggregate functions have been used to obtain the sum, average, maximum and minimum of numerical data points.

Limitations:

• The major limitation of this project is that it is not normalized.
• The dataset is very small to perform complex analysis.

List of References:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Query Language (DQL)
• Crud Operations in SQL
• Primary Key
• Foreign Key
• CONCAT Function in SQL
• DATEDIFF Function in SQL
• DATEADD Function in SQL
• SQL Wildcard Characters
• Types of SQL Joins