Database for a Cosmetics Company

Transaction
TransactionID TransactionDateTime CustomerID Subtotal Tax Discount TotalBill
22000001 2022-04-08 12:35:29.123 1001 159.00 15.90 31.80 143.10
22000002 2022-04-08 02:32:21.173 1002 20.00 2.00 1.00 21.00
22000003 2022-04-08 16:31:19.829 1003 89.99 9.00 9.00 89.99
22000004 2022-04-09 03:15:10.321 1002 355.00 35.50 17.75 372.75
22000005 2022-04-09 07:53:23.100 1002 75.00 7.50 3.75 78.75
22000006 2022-04-10 13:13:22.543 1004 269.97 27.00 13.50 283.47
22000007 2022-04-15 12:59:41.539 1001 89.99 9.00 18.00 80.99
22000008 2022-04-28 19:12:09.103 1005 42.00 4.20 8.40 37.80

Customer
CustomerID CustomerName City MemberLevel MemberDiscount
1001 NULL Manila 3 0.2
1002 BTS Seoul 1 0.05
1003 BollyWood New Delhi 2 0.1
1004 James Brown Seoul 1 0.05
1005 Wingroup Manila 3 0.2

TransactionDetails
TransactionID ProductID Quantity ProductSubtotal
22000001 AC1001 1 75
22000001 AC1002 2 84
22000002 CZ1001 1 20
22000003 BE1002 1 89.99
22000004 AC1001 1 75
22000004 BE1001 2 240
22000004 CZ1001 2 40
22000005 AC1001 1 75
22000006 BE1002 3 269.97
22000007 BE1002 1 89.99
22000008 AC1002 1 42

Product
ProductID ProductName ItemCategory BoxPrice
AC1001 Smoothy Lotion 75.00
AC1002 Silky Lotion 42.00
BE1001 Smelly Perfume 120.00
BE1002 Fragrant Perfume 89.99
CZ1001 Clean Soap 20.00
SET 1 (CRUD)

1. Give the CREATE script for the table Transaction (no keys).
a. Use Identity
2. Give the CREATE script for the table Customer (no keys).
a. Use Identity
b. Set Default City to Hanoi, and Default MemberLevel to 0.
3. Give the CREATE script for the table TransactionDetails (no keys).
a. Set Quantity to NOT NULL
4. Give the CREATE script for the table Product (no keys).
5. Give the ALTER TABLE script to add 1 primary key and a foreign key (to Customer) in
Transaction.
a. Choose CASCADE for update and SET DEFAULT for delete
6. Give the ALTER TABLE script to add 1 primary key in Customer.
7. Give the ALTER TABLE script to add the primary key and 2 foreign keys (to Product and to
Transaction) in TransactionDetails.
a. Choose CASCADE for update and SET DEFAULT for delete
8. Give the ALTER TABLE script to add 1 primary key in Product.
9. Give the INSERT INTO script for the 1st line in Transaction.
10. Give the INSERT INTO script for the 1st line in Customer.
11. Give the INSERT INTO script for the 1st line in TransactionDetails.
12. Give the INSERT INTO script for the 1st line in Product.
13. Give the DELETE script to delete all transactions from CustomerID 1001.
14. Give the DELETE script to delete the all customers with Member Level 3.
15. Give the DELETE script to delete all transactions.
16. Give the DELETE script to delete products whose Boxprice is less than 50.
17. Update the Customer Name of 1001 to “Son Goku”.
18. Update the Quantity of TransactionID 22000001 and Product ID AC1001 to 5.
19. Update the Product Name of CZ1001 to “Cleaner”.
20. Update the MemberDiscount rate of all Member Levels = 3 to 0.25.
21. Update all cities from Manila to “Metro Manila”.
22. Update the tax column to be equal to 15% of the Subtotal (instead of 10%)
23. Update all Perfumes to increase their price +10.
24. Update all Transaction Total bills to get 20 (USD) discount if the total bill is more than 100.

SET 2 (SELECT)

25. Give a query that shows only the transaction ID and the total Bill of all transactions.
26. Give a query that shows only the 1st three rows of Transaction.
27. Give a query to show all transactions after April 8.
28. Give a query that shows the transaction ID and Customer ID arranged by decreasing TotalBill.
29. Give a query showing all transactions who have a discount more than 10 (USD).
30. Give a query showing all distinct CustomerIDs who ordered in April 2022.
31. Give a query showing all CustomerIDs who live in Manila OR have MemberLevel=2.
 32. Give a query showing all distinct CustomerIDs who made a transaction with tax less than 10
(USD).
33. Give a query showing all CustomerIDs whose subtotal is less than 100 and made a transaction
before April 15, 2022.
34. Give all Customer Names that starts with B.
35. Give all Customer Names that ends with a consonant (A,E,I,O,U are vowels, not consonants).
36. Give all Customer Names that has the letter O in its name.
37. Give all Customer Names who lives in a city that has the letter i.
38. Give all products whose Boxprice is between 50 and 100 USD.
39. Arrange all the products by decreasing BoxPrice.
40. Arrange all transactions by increasing Discounts
41. Arrange all customers by City (alphabetically), then by Customer Name (alphabetically).
42. Arrange all customers by City (alphabetically), then by decreasing MemberLevel.
43. Arrange all customers by decreasing MemberLevel, then by city (alphabetically).
44. Arrange all transactions by CustomerID (increasing), then by TotalBill (decreasing).

SET 3 (SELECT, more complex)

45. Give the 3 transactions with the lowest total bill.

46. Give the 4 transactions with the biggest tax paid.
47. Give the 3 transactions before April 15 with the biggest discount.
48. Give the 3 transaction IDs with the biggest ProductSubtotals.
49. Give all transactions with Customer ID<1003 and Discount <15 (USD), arranged by increasing
Customer ID and decreasing discount.
50. Give all transactions which ordered AC1001, arranged by number decreasing Quantity.
51. Give all distinct transactions which ordered a product ID starting with A.
52. Give all distinct transactions with a product subtotal greater than 50, arranged by Product ID.

SET 4 (SELECT with aggregate functions MIN,MAX,SUM,COUNT,AVG and GROUP BY, 2 points)

53. Give the largest Total Bill.

54. Give the smallest tax paid.
55. Give the average subtotal.
56. Give the total discount given in all transactions.
57. Give the number of transactions made by CustomerID = 1002.
58. Give the total tax paid of CustomerID = 1001 in all transactions.
59. What is the average TotalBill of all transactions who enjoyed discounts >10 (USD)?
60. What is the minimum subtotal of all transactions done after April 10, 2022.
61. What is the average Total bill of the 3 largest transactions?
62. How many distinct customers had a transaction whose tax <10 (USD)?
63. What is the average BoxPrice of all Perfumes?
64. What is the difference of the most expensive Lotion and the cheapest Lotion?
65. How many customers are of MemberLevel 3?
66. How many AC1001 were sold in this database instance?
67. Give the average boxprice for each ItemCategory, arranged by decreasing average.
68. Give the average TotalBill of each customer, arranged by decreasing average.
 69. Give the total discount enjoyed by each customer, arranged by CustomerID.
70. Give the total number of items per transactionID.
71. Give the total number of distinct productIDs per transactionID.
72. (HAVING 2+1 pt) Give the sum of all TotalBill of all Customers who has done more than 1
transaction, grouped by customer ID, arranged by decreasing sum.

SET 5 (from multiple tables and/or JOIN, 2 points)

73. Join Transaction and Customer.

74. Join Transaction and TransactionDetails
75. Join TransactionDetails and Product.
76. Join Transaction, Customer, and TransactionDetails.
77. Join Transaction, Customer, and TransactionDetails and Product.
78. Give a table showing all transaction IDS next to the name of the Customer.
79. Give a table showing all transaction IDs and the product IDs for each transaction, arrange by
Product ID alphabetically and increasing TransactionID.
80. Give a table like the transaction Detailsbut replacing Product ID with the Product Name.
81. Give a table showing the transactions of all customers whose MemberLevel=3.
82. Give a table showing the transaction ID and Total Bill of all transactions which bought AC1001.

SET 6 (with any/all keywords, 4 points)

83. Give a table showing the transaction ID and Total Bill of all transactions which bought lotion.
84. Give the 2 transactions with the largest total bill and performed by a customer with Member
level 2.
85. Give the 3 transactions with the smallest tax and which involves more than 1 item (Hint: Sum of
Quantity for each transactionID).
86. Give the total Tax of all transactions per city, arranged by decreasing Tax.
87. Give the average TotalBill of all transactions done by a customer with Member Level 3, arranged
by increasing CustomerID.
88. Give the average tax of all transactions for each Member Level, arranged by decreasing Member
Level.
89. (HAVING 4+1pt) Give the average Tax paid of all transactions which has more than 1 item in the
transaction, grouped by the number of items.
90. (HAVING 4+1pt) Give the sum of all tax paid in all cities which has TotalBills summing up greater
than 100 (USD), grouped by City, arranged by decreasing sum.

Others (3 points)
91. This database would be better of to have a 5th table (by breaking down one of these tables into
2 smaller tables). What columns will this 5th table have?
 ERD practice (all groups can answer these)

1. Make an ER diagram for a hospital. (5 points)

o Each patient is identified by a patient ID and entry date, and has attributes name and
date of birth. A patient may be treated by several doctors.
o A doctor is identified by a Doctor ID and has attributes name, specialties, and telephone
number. He may have several specialties (heart, brain, surgery, eyes, etc.). A doctor
treats several patients, but is associated to only one department.
o A department is identified by its name, and has attributes location, telephone number,
and its code. A department has several doctors.

2. Construct the schema (table names + attributes only) for the following ERD and then draw lines
that will signify the foreign keys from one table to another. (3 points)