<< Back to Database Engineering Portfolio

Assignment 4

Chapter 1

1.3:

Question: Read the description of the search process on the Pearson Web site. Using your own computer, find another retailer Web site (other than any of those discussed or mentioned in this chapter), and search for something of interest to you. Write up a description (with screen shots if possible) of your search.

Answer: On the home page you can see a searchbar in the blue bar on the top of the page.

1.4:

Question: What is the purpose of a database?

Answer: The purpose of a database is to store, access and modify data.

1.5:

Question: Why is a relational database the most commonly used type of database?

Answer: A relational database is the most commonly used type of database because it allows for efficient data management.

1.6:

Question: Give an example of two related tables other than the example used in this book. Use the STUDENT and GRADE tables in Figure 1-5 as an example pattern for your tables. Name the tables and columns using the conventions in this book.

Answer:

Employees
EmployeeID	FirstName	LastName
3427	John	Doe
8734	Jane	Doe

Salaries
SalaryID	EmployeeID	Amount
1	3427	€2600
2	8734	€2600

1.7:

Question: For the tables you created in Review Question 1.6, what are the primary keys of each table? Do you think that any of these primary keys could be surrogate keys? Are any of these primary keys composite keys?

Answer: In the table EMPLOYEES, the EmployeeID serves as the primary key and can be a surrogate key while in the table SALARIES, the EmployeeID serves as an foreign key. In the SALARIES table the SalaryID serves as the primary key.

1.8:

Question: Explain how the two tables you provided in Review Question 1.6 are related. Which table contains the foreign key, and what is the foreign key?

Answer: The tables are related to each other by the EmployeeID. In the SALARIES table the EmployeeID is the foreign key.

1.9:

Question: Show your two tables from Review Question 1.6 without the columns that represent the relationships. Explain how the value of your two tables is diminished without the relationships

Answer:

Employees
FirstName	LastName
John	Doe
Jane	Doe

Salaries
SalaryID	Amount
1	€2600
2	€2600

The value of the two tables are gone because there is no relationships between the two tables. You have a SalaryID and an amount but you don't know who the amount is assigned to.

1.10:

Question: How do databases create information?

Answer: Databases create information because the data inside the database are recorded as facts and figures.

1.11:

Question: Give an example of information that could be determined using the two tables you provided in your answer to Review Question 1.6.

Answer: Using the two tables, you can determine the salary of each employee. For example, John Doe has a salary of €2600, and Jane Doe also has a salary of €2600.

1.12:

Question: Give examples of a single-user database application and a multiuser database application other than the ones shown in Figure 1-7.

Answer:

Departments
DepartmentID	DepartmentName	Manager	Employees	Location
1	Human Resources	John Smith	35	Building A
2	Finance	Jane Doe	25	Building B
3	IT	Michael Johnson	45	Building C
4	Marketing	Emily Brown	30	Building D
5	Sales	David White	40	Building E
6	Research and Development	Olivia Green	60	Building F

Chapter 2

Use only the INVENTORY table to answer Review Questions 2.17 through 2.39:

Inventory
WarehouseID	SKU	SKU_Description	QuantityOnHand	QuantityOnOrder
100	100100	Std. Scuba Tank, Yellow	250	0
200	100100	Std. Scuba Tank, Yellow	100	50
300	100100	Std. Scuba Tank, Yellow	100	0
400	100100	Std. Scuba Tank, Yellow	200	0
100	100200	Std. Scuba Tank, Magenta	200	30
200	100200	Std. Scuba Tank, Magenta	75	75
300	100200	Std. Scuba Tank, Magenta	100	100
400	100200	Std. Scuba Tank, Magenta	250	0
100	101100	Dive Mask, Small Clear	0	500
200	101100	Dive Mask, Small Clear	0	500
300	101100	Dive Mask, Small Clear	300	200
400	101100	Dive Mask, Small Clear	450	0
100	101200	Dive Mask, Med Clear	100	500
200	101200	Dive Mask, Med Clear	50	500
300	101200	Dive Mask, Med Clear	475	0
400	101200	Dive Mask, Med Clear	250	250
100	201000	Half-dome Tent	2	100
200	201000	Half-dome Tent	10	250
300	201000	Half-dome Tent	250	0
400	201000	Half-dome Tent	0	250
100	202000	Half-dome Tent Vestibule	10	250
200	202000	Half-dome Tent Vestibule	1	250
300	202000	Half-dome Tent Vestibule	100	0
400	202000	Half-dome Tent Vestibule	0	200
100	301000	Light Fly Climbing Harness	300	250
200	301000	Light Fly Climbing Harness	250	250
300	301000	Light Fly Climbing Harness	0	250
400	301000	Light Fly Climbing Harness	0	250
100	302000	Locking Carabiner, Oval	1000	0
200	302000	Locking Carabiner, Oval	1250	0
300	302000	Locking Carabiner, Oval	500	500
400	302000	Locking Carabiner, Oval	0	1000

2.17:

Question: Write an SQL statement to display SKU and SKU_Description.

Answer: SELECT SKU, SKU_Description FROM INVENTORY;

2.18:

Question: Write an SQL statement to display SKU_Description and SKU.

Answer: SELECT SKU_Description, SKU FROM INVENTORY;

2.19:

Question: Write an SQL statement to display WarehouseID.

Answer: SELECT WarehouseID FROM INVENTORY;

2.20:

Question: Write an SQL statement to display unique WarehouseIDs.

Answer: SELECT DISTINCT WarehouseID FROM INVENTORY;

2.21:

Question: Write an SQL statement to display all of the columns without using the SQL asterisk (*) wildcard character

Answer: SELECT WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder FROM Inventory;

2.22:

Question: Write an SQL statement to display all of the columns using the SQL asterisk (*) wildcard character.

Answer: SELECT * FROM INVENTORY;

2.23:

Question: Write an SQL statement to display all data on products having a QuantityOnHand greater than 0

Answer: SELECT * FROM INVENTORY WHERE QuantityOnHand > 0;

2.24:

Question: Write an SQL statement to display the SKU and SKU_Description for products having QuantityOnHand equal to 0.

Answer: SELECT SKU, SKU_Description FROM INVENTORY WHERE QuantityOnHand = 0;

2.25:

Question: Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for products that have a QuantityOnHand equal to 0. Sort the results in ascending order by WarehouseID

Answer: SELECT SKU, SKU_Description, WarehouseID FROM Inventory WHERE QuantityOnHand = 0 ORDER BY WarehouseID ASC;

2.26:

Question: Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for products that have a QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.

Answer: SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE QuantityOnHand > 0 ORDER BY WarehouseID DESC, SKU ASC;

2.27:

Question: Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all products that have a QuantityOnHand equal to 0 and a QuantityOnOrder greater than 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.

Answer: SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE QuantityOnHand = 0 AND QuantityOnOrder > 0 ORDER BY WarehouseID DESC, SKU ASC;

2.28:

Question: Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all products that have a QuantityOnHand equal to 0 or a QuantityOnOrder equal to 0. Sort the results in descending order by WarehouseID and in ascending order by SKU.

Answer: SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY WHERE QuantityOnHand = 0 OR QuantityOnOrder = 0 ORDER BY WarehouseID DESC, SKU ASC;

2.29:

Question: Write an SQL statement to display the SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Do not use the BETWEEN keyword.

Answer: SELECT SKU, SKU_Description, WarehouseID, QuantityOnHand FROM INVENTORY WHERE QuantityOnHand > 1 AND QuantityOnHand < 10;

2.30:

Answer: SELECT SKU, SKU_Description, WarehouseID, QuantityOnHand FROM INVENTORY WHERE QuantityOnHand BETWEEN 1 AND 10;

2.31:

Question: Write an SQL statement to show a unique SKU and SKU_Description for all products having an SKU description starting with %Half-Dome%.

Answer: SELECT DISTINCT SKU, SKU_Description FROM INVENTORY WHERE SKU_Description LIKE '%Half-Dome%';

2.32:

Question: Write an SQL statement to show a unique SKU and SKU_Description for all products having a description that includes the word %Climb%.

Answer: SELECT DISTINCT SKU, SKU_Description FROM INVENTORY WHERE SKU_Description LIKE '%Climb%';

2.33:

Question: Write an SQL statement to show a unique SKU and SKU_Description for all products having a %d% in the third position from the left in SKU_Description.

Answer: SELECT DISTINCT SKU, SKU_Description FROM INVENTORY WHERE SUBSTRING(SKU_Description, 3, 1) = 'd';

2.34:

Question: Write an SQL statement that uses all of the SQL built-in functions on the QuantityOnHand column. Include meaningful column names in the result.

Answer: SELECT SUM(QuantityOnHand) AS Total_QuantityOnHand, AVG(QuantityOnHand) AS Average_QuantityOnHand, MAX(QuantityOnHand) AS Max_QuantityOnHand, MIN(QuantityOnHand) AS Min_QuantityOnHand, COUNT(QuantityOnHand) AS Count_Of_QuantityOnHand FROM INVENTORY;

2.35:

Question: Explain the difference between the SQL built-in functions COUNT and SUM.

Answer: The SQL built-in function COUNT returns the number of rows that match a specified condition, while the SUM function calculates the total amount of the values in a column.

2.36:

Question: Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Name the sum TotalItemsOnHand and display the results in descending order of TotalItemsOnHand.

Answer: SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHand FROM INVENTORY GROUP BY WarehouseID ORDER BY TotalItemsOnHand DESC;

2.37:

Question: Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum TotalItemsOnHandLT3 and display the results in descending order of TotalItemsOnHandLT3.

Answer: SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHandLT3 FROM INVENTORY WHERE QuantityOnHand < 3 GROUP BY WarehouseID ORDER BY TotalItemsOnHandLT3 DESC;

2.38:

Question: Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand grouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum TotalItemsOnHandLT3. Show the WarehouseID only for warehouses having fewer than 2 SKUs in their TotalItemsOnHandLT3. Display the results in descending order of TotalItemsOnHandLT3.

Answer:

2.39:

Question: In your answer to Review Question 2.38, was the WHERE clause or the HAVING clause applied first? Why?

Answer: