DatabaseStructuresAndManagementWithMySQL

Welcome to another of my MySQL journeys, I get to interact with the following:

• Filter data with logical operators.
• Use various join types to query relevant data from multiple tables.
• Use MySQL alias to improve the readability of the queries.
• Group data from multiple tables using clauses and operators
• Update, insert and replace data in databases using MySQL REPLACE statement
• Identify and work with constraints in MySQL databases
• Change the structure of tables and copy table data using MySQL ALTER TABLE and COPY TABLE commands
• Use subqueries and complex comparison operators in a MySQL database
• Query data using MySQL CREATE VIEW command to create virtual tables
• Use different types of functions to query data
• Create and deploy stored procedures in a MySQL database

Mission 1: Filtering Data

Lucky Shrub is a medium-sized garden design firm that sells indoor and outdoor plants, making them a one stop shop for clients. These tasks aim to filter data using WHERE clause and logical operators.

Task 1

Write a SQL statement to print all records of orders where the cost is $250 or less.

Task 2

Write a SQL statement to print all records of orders where the cost is between $50 and $750.

Task 3

Write a SQL statement to print all records of orders that have been placed by the client with the id of Cl3 and where the cost of the order is more than $100.

Task 4

Write a SQL statement to print all records of orders that have a product id of p1 or p2 and the order quantity is more than 2.

Mission 2: Using JOINS

Little Lemon is a family-owned Mediterranean restaurant, focused on traditional recipes served with a modern twist. The Little Lemon database contains many tables of data including Customers and Bookings. The two objectives of this activity are Create an INNER JOIN query & Create a LEFT JOIN query.

Task 1: Inner join

Little Lemon want a list of all customers who have made bookings. Write an INNER JOIN SQL statement to combine the full name and the phone number of each customer from the Customers table with the related booking date and booking time from the Bookings table.

Task 2: Left join

Little Lemon want to view information about all existing customers with bookings that have been made so far. This data must include customers who haven’t made any booking yet.

Write a LEFT JOIN SQL statement to view the customer id from Customers table and the related booking id from the Bookings table.

Mission 3: Grouping data

Lucky Shrub is a medium-sized garden design firm that sells indoor and outdoor plants, making them a one stop shop for clients. Help Lucky Shrub group and filter grouped data for their reports using the GROUP BY and HAVING clauses.

The Orders table used contains the following columns: OrderID, Department, OrderDate, OrderQty and OrderTotal

Task 1: Group by

Write a SQL SELECT statement to group all records that have the same order date.

Task 2:

Write a SQL SELECT statement to retrieve the number of orders placed on the same day.

Task 3:

Write a SQL SELECT statement to retrieve the total order quantities placed by each department.

Task 4:

Write a SQL SELECT statement to retrieve the number of orders placed on the same day between the following dates: 1st June 2022 and 30th June 2022.

Mission 4: MySQL REPLACE statement

Task 1: Write a SQL REPLACE statement that inserts two new orders with the following details:

Order 9 data: OrderID = 9, ClientID = "Cl1", ProductID = "P1", Quantity = 10, Cost = 5000

Order 10 data: OrderID = 10, ClientID = "Cl2", ProductID = "P2", Quantity = 5, Cost = 100

Task 2:

Lucky Shrub have noticed that the cost of order number 9 is $5000. This is a mistake. The order must cost $500. You must help them to change it to $500 by writing a relevant REPLACE statement.

Mission 5: Working with constraints

Mangata and Gallo is a jewelry store that specializes in special occasions like engagements, weddings and anniversaries. The company approached you to create three tables for their database called 'Clients', 'Orders' and 'Items' respectively. Each table must have all necessary constraints applied as specified in each task.

The 'Clients' table contains the Client ID, full name and phone number of each client.

The Orders table contains information about each order's Order ID, Client ID, Item ID, Quantity and Cost.

The Items table contains information about the Item ID, Item name, and Item price.

The relationship between the three table is illustrated in the following entity relationship diagram (ER-D).

Task 1: Create the Clients table with the following columns and constraints.

• ClientID: INT, NOT NULL and PRIMARY KEY
• FullName: VARCHAR(100) NOT NULL
• PhoneNumber: INT, NOT NULL and UNIQUE

Task 2: Create the Items table with the following attributes and constraints:

• ItemID: INT, NOT NULL and PRIMARY KEY
• ItemName: VARCHAR(100) and NOT NULL
• Price: Decimal(5,2) and NOT NULL

Task 3: Create the Orders table with the following constraints.

• OrderID: INT, NOT NULL and PRIMARY KEY
• ClientID: INT, NOT NULL and FOREIGN KEY
• ItemID: INT, NOT NULL and FOREIGN KEY
• Quantity: INT, NOT NULL and maximum allowed items in each order 3 only
• COST Decimal(6,2) and NOT NULL

Mission 6: Changing table structure

Task 1

Write a SQL statement that creates the Staff table with the following columns

• StaffID: INT
• FullName: VARCHAR(100)
• PhoneNumber: VARCHAR(10)

Task 2

Write a SQL statement to apply the following constraints to the Staff table:

• StaffID: INT NOT NULL and PRIMARY KEY
• FullName: VARCHAR(100) and NOT NULL
• PhoneNumber: INT NOT NULL

Task 3

Write a SQL statement that adds a new column called 'Role' to the Staff table with the following constraints:

Role: VARCHAR(50) and NOT NULL

Task 4

Write a SQL statement that drops the Phone Number column from the 'Staff' table.

Mission 7: Working with subqueries

• Working with single row, multiple row and correlated subqueries.
• Using the comparison operators and the ALL and NOT EXISTS operators with subqueries.

Task 1: Write a SQL SELECT query to find all bookings that are due after the booking of the guest ‘Vanessa McCarthy’.

Task 2: Write a SQL SELECT query to find the menu items that are more expensive than all the 'Starters' and 'Desserts' menu item types.

Task 3: Write a SQL SELECT query to find the menu items that costs the same as the starter menu items that are Italian cuisine.

Task 4: Write a SQL SELECT query to find the menu items that were not ordered by the guests who placed bookings.

Mission 8: Working with views in MySQL

The Orders table contains information about the Order ID, Client ID, Product ID, Quantity and Cost

The main objectives of this activity are:

• Create a virtual table.
• Update the base table using the virtual table.
• Rename the virtual table.
• Drop the virtual table.

Task 1

Write a SQL statement to create the OrdersView Virtual table based on the Orders table to t include the following columns: Order ID, Quantity and Cost

Task 2

Write a SQL statement that utilizes the ‘OrdersView’ virtual table to Update the base Orders table. In the UPDATE TABLE statement, change the cost to 200 where the order id equals 2.

Task 3

Write a SQL statement that changes the name of the ‘OrdersView’ virtual table to ClientsOrdersView.

Task 4

Write a SQL statement to delete the Orders virtual table.

Mission 9: Working with MySQL functions

Help to make it easier for M&G staff to format and filter data using MySQL string, Math, Date and Comparison functions for their reports.

Use the item and mg_orders tables

Task 1:

Write a SQL SELECT query using appropriate MySQL string functions to list items, quantities and order status in the following format:

• Item name–quantity–order status
• Item name should be in lower case. Order status should be in upper case.

Task 2:

Write a SQL SELECT query using an appropriate date function and a format string to find the name of the weekday on which M&G’s orders are to be delivered.

Task 3:

Write a SQL SELECT query that calculates the cost of handling each order. This should be 5% of the total order cost. Use an appropriate math function to round that value to 2 decimal places.

Task 4:

Review the query that you wrote in the second task. Use an appropriate comparison function to filter out the records that do not have a NULL value in the delivery date column.

Mission 10: Working with procedures

Help Lucky Shrub access relevant data from the Orders table in their database using stored procedures.

Task 1

Write a SQL statement that creates a stored procedure called 'GetOrdersData' which retrieves all data from the Orders table.

Call the “GetOrdersData” to show all orders

Task 2

Write a SQL statement that creates a stored procedure called “GetListOfOrdersInRange” that procedure must contain two parameters that determine the range of retrieved data based on the user input of two cost values “MinimumValue” and “MaximumValue”.

Call the “GetListOfOrdersInRange” to display the data of orders that cost between $150 and $600.

MySQL Database Structures & Management Project

Based in Chicago, Illinois, Little Lemon is a family-owned Mediterranean restaurant, focused on traditional recipes served with a modern twist. The chefs draw inspiration from Italian, Greek, and Turkish culture and have a menu of 12–15 items that they rotate seasonally. The restaurant has a rustic and relaxed atmosphere with moderate prices, making it a popular place for a meal any time of the day.

The objective of these tasks is:

• Provide a recap of all topics introduced in this course.
• Provide experience with developing core database queries.

Task 1: Filter data using the WHERE clause and logical operators.

Create SQL statement to print all records from Bookings table for the following bookings dates using the BETWEEN operator: 2021-11-11, 2021-11-12 and 2021-11-13.

Task 2: Create a JOIN query.

Create a JOIN SQL statement on the Customers and Bookings tables that prints the customers full names and related bookings IDs from the date 2021-11-11.

Task 3: Create a GROUP BY query.

Create a SQL statement to print the bookings dates from Bookings table to show the total number of bookings placed on each of the printed dates using the GROUP BY BookingDate.

Task 4: Create a REPLACE statement.

Create a SQL REPLACE statement that updates the cost of the Kabsa course from $17.00 to $20.00.

To see the new column added

Task 5: Create constraints

Create a new table called "DeliveryAddress" in the Little Lemon database with the following columns and constraints:

• ID: INT PRIMARY KEY
• Address: VARCHAR(255) NOT NULL
• Type: NOT NULL DEFAULT "Private"
• CustomerID: INT NOT NULL FOREIGN KEY referencing CustomerID in the Customers table

Task 6: Alter table structure

Create a SQL statement that adds a new column called 'Ingredients' to the Courses table.

• Ingredients: VARCHAR(255)

Task 7: Create a subquery

Create a SQL statement with a subquery that prints the full names of all customers who made bookings in the restaurant on the following date:2021-11-11.  

Task 8: Create a virtual table

Create the "BookingsView" virtual table to print all bookings IDs, bookings dates and the number of guests for bookings made in the restaurant before 2021-11-13 and where number of guests is larger than 3.

Task 9: Create a stored procedure

Create a stored procedure called 'GetBookingsData'. The procedure must contain one date parameter called "InputDate".

Call the "GetBookingsData" with '2021-11-13' as the input date

Task 10: Use the String function

Create a SQL SELECT query using appropriate MySQL string function to list "Booking Details" including booking ID, booking date and number of guests. The data must be listed in the same format as the following example:

ID: 10, Date: 2021-11-10, Number of guests: 5