Homework 5: SQL and B+ Tree

This homework assignment focuses on two key database concepts: SQL queries and B+ tree data structures. The SQL portion involves writing SELECT statements to manipulate data from a 'Products' table, specifically formatting and converting the 'list_price' and 'date_added' columns using functions like FORMAT, CONVERT, CAST, and DATE_FORMAT. The second part requires simulating a B+ tree using a given insertion/deletion sequence. The provided solution demonstrates how to perform these tasks, offering examples of SQL queries and a step-by-step approach to B+ tree simulation. The assignment is designed to enhance understanding of SQL data manipulation and B+ tree operations.

homework 5 solution
Student name:
03 November, 2016

1. Write a SELECT statement that returns these columns from the Products table:
The list_price column
a. A column that uses the FORMAT function to return the list_price column with 1 digit to
the right of the decimal point
Select FORMAT(list_price, 1) from products;
b. A column that uses the CONVERT function to return the list_price column as an integer
Select CONVERT(int, list_price) from products;
c. A column that uses the CAST function to return the list_price column as an integer
Select CAST(list_price as int) from products;
2. Write a SELECT statement that returns these columns from the Products table:
The date_added column
a. A column that uses the CAST function to return the date_added column with its date
only (year, month, and day)
Select CAST(date_added as date) from products;
b. A column that uses the CAST function to return the date_added column with just the
year and the month
Select DATE_FORMAT(CAST(date_added as DATE), '%m/%Y') from products;
c. A column that uses the CAST function to return the date_added column with its full time
only (hour, minutes, and seconds)
Select CAST(date_added as time) from products;
3. Simulate B+ tree with the following insertion sequence:

4. Simulate B+ tree with the following insertion sequence:
5. Simulate B+ tree with the following insertion/deletion sequence: