Khoá học lập trình SQL [Examination] Quản lý sản phẩm - Lập trình SQL Server

Part 1: Database Creation

• Create a database named ProductManagementSystem.

• Switch the current context to use the ProductManagementSystem database.

Part 2: Table Creation

Create the following four tables with the specified data types and constraints:

1. tblUser (Stores user information)

2. tblOrder (Stores order information)

3. tblProduct (Stores product information)

4. tblOrderDetail (Stores line items for orders)

Part 3: Indexing and Table Modification

• Create Index: Create a Clustered Index named CI_tblUser_UserID on the UserID column of the tblUser table.

• Drop Index: Remove the clustered index CI_tblUser_UserID that you just created.

• Modify Table: Alter the tblUser table to add a new column named BirthDate with the DATETIME datatype.

Part 4: Constraints

Implement the following constraints to ensure data integrity:

• Primary Keys:

  • PK_tblUser: UserID in tblUser

  • PK_tblOrder: OrderID in tblOrder

  • PK_tblProduct: ProductID in tblProduct

  • PK_tblOrderDetail: Composite key of (OrderID, ProductID) in tblOrderDetail

• Foreign Keys:

  • FK_tblOrder_tblUser: UserID in tblOrder referencing UserID in tblUser.

  • FK_tblOrderDetail_tblOrder: OrderID in tblOrderDetail referencing OrderID in tblOrder.

  • FK_tblOrderDetail_tblProduct: ProductID in tblOrderDetail referencing ProductID in tblProduct.

• Default: Set a default value of GETDATE() for the OrderDate column in tblOrder.

• Check: Ensure OrderDate in tblOrder is between '2000-01-01' and the current date.

• Unique: Ensure the UserName in tblUser is unique.

Part 5: Data Insertion

Populate the tables with the records provided in the sample data tables (refer to original images for specific names and dates for Users, Orders, Products, and OrderDetails).

Part 6: Query Operations

• Update: Apply a 10% discount to the Price of all records in tblProduct where the ProductID is 3.

• Select: Display records from all four tables joined together, including: UserID, UserName, OrderID, OrderDate, Quantity, Price, and ProductName.

Part 7: Views

• Create a view named view_Top2Product to display the top 2 best-selling products based on the total quantity sold. The view should show: ProductID, ProductName, Price, and TotalQuantity.

Part 8: Stored Procedures

• Create a procedure named sp_TimSanPham with the following parameters:

  • @GiaMua (MONEY): The maximum price to filter products.

  • @count (INT, OUTPUT): To return the total number of records found.

• The procedure should return a list of products where Price <= @GiaMua and print the count of products found.

• Execute the procedure with @GiaMua = 50.

Part 9: Triggers

• Trigger 1: Create TG_tblProduct_Update on the tblProduct table for UPDATE events. If the updated Price is less than 10, the update should fail, roll back, and print the message: "You don’t update price less than 10!"

• Drop Trigger: Drop the TG_tblProduct_Update trigger.

• Trigger 2: Create TG_tblUser_Update on the tblUser table for UPDATE events. If a user attempts to modify the UserName column, the update should fail and print: "You don’t update this column UserName!"