BCS360 Final Project submission Instructions Prof. Amani
Please Read carefully
On SQL server account, you have to upload the tables to your account on SQL server.
Your database has to have at least 5 tables. The number of rows is 20 rows. You can add more if
you want.
The tables have to be filled with data in order to run SQL queries.
You can use the following website https:
mockaroo.com/
Remember, this website fills out the data randomly. Therefore, to build the relationship
etween foreign key and primary key, you have to have same values in both columns in
different tables. Your queries won’t work if there is not same values of foreign key and primary
key even if you build referential integrity constrain. Therefore, in this case, you have to fill out
this part manually.
The referential integrity constraint is a rule specifying that every value of a foreign key matches
a value of the primary key.
On Blackboard, Deadline: 12/02/2019
You have to upload the followings:
First a documented project: PDF file
Name the a documented project as following
FirstName_LastName_FinalProject
For example: Amani_Ayad_FinalProject
The document should have the followings:
Page1: The cover page.
Page 2: Introduction of your system, the scope, answering the followings:
What does your system do?
What is the important entities in your system?
What is the activities and transactions can occur in your system
Next Page: Full ER of your system. Remember, foreign keys are not shown in ER.
Next Page: fill out the table. For example:
https:
mockaroo.com
BCS360 Final Project submission Instructions Prof. Amani
Next Page: List all tables in term of relations. Show the foreign key. Primary key is underlined.
For example:
TEXTBOOK (ISBN, Title, Publisher, Copyright)
XXXXXXXXXXPUBLISHER (PublisherName, Street, City, State, Zip)
Publisher in TEXTBOOK is a foreign key that references the primary key PublisherName
in PUBLISHER.
Next Page: show the script that has all create statements for your tables.
Next Page: select two questions from each chapter and apply them on your system. The total is
10 queries. Feel free to run more queries. Try with complicated ones. The more complicated,
the better evaluation and understanding of your system.
You have to provide the question, query and the output.
Chapters are 3,4,5,6,9,13
The questions are provided at the end.
Next page:
Summary: try to answer these questions: How much do you like your system? What is the
difficult part? What did you learn?
That’s the end of the documented project.
Second: SQL queries:
RELATIONSHIP CARDINALITY
PARENT CHILD TYPE MAX MIN
DRIVER CORRECTION_NOTICE Strong 1:N M-O
CORRECTION_NOTICE VIOLATION Weak_ID-
Dependent
1:N M-O
BCS360 Final Project submission Instructions Prof. Amani
Name the script that creates your database table as following
Create_tables.sql
Add this script to the following queries.
After you select two SQL from each chapter, SQL file has to be named as the following pattern
ch3_1, ch3_2, ch4_1,……
Add all your SQL queries in a folder and name it as SQL_QUERIES with create_tables.sql
Then add SQL_QUERIES folder and a documented project in one zipped folder and upload it to
the blackboard.
Name the zip folder as following
FirstName_LastName_FinalProject360
For example:
Amani_Ayad_FinalProject360
BCS360 Final Project submission Instructions Prof. Amani
Chapter 3
How to retrieve data from a single table
Exercises
Enter and run your own SELECT statements
In these exercises, you’ll enter and run your own SELECT statements.
1. Write a SELECT statement that returns four columns from the Products table: ProductCode,
ProductName, ListPrice, and DiscountPercent. Then, run this statement to make sure it works
co
ectly.
Add an ORDER BY clause to this statement that sorts the result set by list price in descending
sequence. Then, run this statement again to make sure it works co
ectly. This is a good way to build
and test a statement, one clause at a time.
2. Write a SELECT statement that returns one column from the Customers table named FullName that
joins the LastName and FirstName columns.
Format this column with the last name, a comma, a space, and the first name like this:
Doe, John
Sort the result set by last name in ascending sequence.
Return only the contacts whose last name begins with a letter from M to Z.
3. Write a SELECT statement that returns these column names and data from the Products table:
ProductName The ProductName column
ListPrice The ListPrice column
DateAdded The DateAdded column
Return only the rows with a list price that’s greater than 500 and less than 2000.
Sort the result set in descending sequence by the DateAdded column.
4. Write a SELECT statement that returns these column names and data from the Products table:
ProductName The ProductName column
ListPrice The ListPrice column
DiscountPercent The DiscountPercent column
DiscountAmount A column that’s calculated from the previous two columns
DiscountPrice A column that’s calculated from the previous three columns
Sort the result set by discount price in descending sequence.
BCS360 Final Project submission Instructions Prof. Amani
5. Write a SELECT statement that returns these column names and data from the OrderItems table:
ItemID The ItemID column
ItemPrice The ItemPrice column
DiscountAmount The DiscountAmount column
Quantity The Quantity column
PriceTotal A column that’s calculated by multiplying the item price by the
quantity
DiscountTotal A column that’s calculated by multiplying the discount amount
y the quantity
ItemTotal A column that’s calculated by subtracting the discount amount
from the item price and then multiplying by the quantity
Only return rows where the ItemTotal is greater than 500.
Sort the result set by item total in descending sequence.
Work with nulls and test expressions
6. Write a SELECT statement that returns these columns from the Orders table:
OrderID The OrderID column
OrderDate The OrderDate column
ShipDate The ShipDate column
Return only the rows where the ShipDate column contains a null value.
7. Write a SELECT statement without a FROM clause that creates a row with these columns:
Price 100 (dollars)
TaxRate XXXXXXXXXXpercent)
TaxAmount The price multiplied by the tax rate
Total The price plus tax
To calculate the fourth column, add the expressions you used for the first and third columns.
BCS360 Final Project submission Instructions Prof. Amani
Chapter 4
How to retrieve data
from two or more tables
Exercises
1. Write a SELECT statement that joins the Categories table to the Products table and returns these
columns: CategoryName, ProductName, ListPrice.
Sort the result set by CategoryName and then by ProductName in ascending order.
2. Write a SELECT statement that joins the Customers table to the Addresses table and returns these
columns: FirstName, LastName, Line1, City, State, ZipCode.
Return one row for each address for the customer with an email address of
XXXXXXXXXX.
3. Write a SELECT statement that joins the Customers table to the Addresses table and returns these
columns: FirstName, LastName, Line1, City, State, ZipCode.
Return one row for each customer, but only return addresses that are the shipping address for a
customer.
4. Write a SELECT statement that joins the Customers, Orders, OrderItems, and Products tables. This
statement should return these columns: LastName, FirstName, OrderDate, ProductName, ItemPrice,
DiscountAmount, and Quantity.
Use aliases for the tables.
Sort the final result set by LastName, OrderDate, and ProductName.
5. Write a SELECT statement that returns the ProductName and ListPrice columns from the Products
table.
Return one row for each product that has the same list price as another product. (Hint: Use a self-join
to check that the ProductID columns aren’t equal but the ListPrice column is equal.)
Sort the result set by ProductName.
6. Write a SELECT statement that returns these two columns:
CategoryName The CategoryName column from the Categories table
ProductID The ProductID column from the Products table
Return one row for each category that has never been used. (Hint: Use an outer join and only return
ows where the ProductID column contains a null value.)
BCS360 Final Project submission Instructions Prof. Amani
7. Use the UNION operator to generate a result set consisting of three columns from the Orders table:
ShipStatus A calculated column that contains a value of SHIPPED or NOT
SHIPPED
OrderID The OrderID column
OrderDate The OrderDate column
If the order has a value in the ShipDate column, the ShipStatus column should contain a value of
SHIPPED. Otherwise, it should contain a value of NOT SHIPPED.
Sort the final result set by OrderDate.
BCS360 Final Project submission Instructions Prof. Amani
Chapter 5
How to code summary queries
Exercises
1. Write a SELECT statement that returns these columns:
The count of the number of orders in the Orders table
The sum of the TaxAmount columns in the Orders table
2. Write a SELECT statement that returns one row for each category that has products with these
columns:
The CategoryName column from the Categories table
The count of the products in the Products table
The list price of the most expensive product in the Products table
Sort the result set so the category with the most products appears first.
3. Write a SELECT statement that returns one row for each customer that has orders with these
columns:
The EmailAddress column from the Customers table
The sum of the item price in the OrderItems table multiplied by the quantiy in the
OrderItems table
The sum of the discount amount column in the OrderItems table multiplied by the quantiy
in the OrderItems table
Sort the result set in descending sequence by the item price total for each customer.
4. Write a SELECT statement that returns one row for each customer that has orders with these
columns:
The EmailAddress column from the