Section Assignment - The Novice Sales Adventures at a Furniture Store

type

status

date

slug

summary

Pre-Assignment

Access the Simulation Data:

Open the simulation data link provided.

Load Data into pg-sql:

Go to pg-sql.com.

Import the simulation data into the platform to have it ready for queries and practice.

Proceed to Answer the Questions:

Once the data is successfully imported, start working on the assignment tasks by running queries and exploring the data as per the prompts in the presentation.

Narrative Voiceover

Xiao Mei, who just started her job as a sales consultant at "Dream Home Furnishings,"

unexpectedly encountered a major system crash on her first day.

To make things worse, this long-established furniture store only has a POS system and a database,

with no website available for easy access.

Today also happens to be the launch of the store’s anniversary sale, and the supervisor is overseeing things on-site.

With business booming, Xiao Mei has no choice but to bravely seek help from the store’s engineer...

Dialogue

(In the store)

Customer: I’d like to see that 貓抓皮沙發. Do you have any in stock?

Xiao Mei: (nervously) Uh… 貓抓皮沙發...

(in a rush, messaging Xiao Ming on LINE)

Xiao Mei: Help, senior! How do I check for this sofa?

Xiao Ming: Just use an equal sign:

Xiao Mei: Oh! So it’s like

Is that right?

Xiao Ming: Correct! You’re getting the hang of it quickly!

Xiao Mei: (typing quickly)

Wow! I found it! Only one in stock...

Customer: (impatiently) Miss? Do you have it or not?

Xiao Mei: This 貓抓皮L型沙發 is currently the last one in stock at a special price of 52,900. Would you like to reserve it?

Customer: Oh? The last one, huh... well, go ahead and reserve it for me!

Xiao Ming: !!! You picked that up so fast? You’re a database prodigy == b

Xiao Ming: Well, let me test you with a few more questions. If you master these, I might as well retire, haha.

Xiao Mei: .....

Questions

Basic Comparison Operators：

Scenario 1: Single Item Query

Customer: "How much is this 北歐風雙人沙發?”

Xiao Mei wants to check: Find the price and stock of this sofa.

Scenario 2: Price Comparison

Customer: "Can you list the 櫃子 under 5,000?”

Xiao Mei wants to check: Find items in the 櫃子 category with prices below 5,000.

Scenario 3: Stock Check

Customer: "Is the 日式雙人床架 still in stock?"

Xiao Mei wants to check: Confirm the stock status of the 日式雙人床架.

Logical Operator AND:

Scenario 4: Products Within Budget

Customer: "I’m looking for a 沙發 under 40,000 that’s also in stock."

Xiao Mei wants to check: Find items that meet all of the following conditions: it’s a 沙發, priced under 40,000, and is in stock.

Scenario 5: On Sale and In Stock

Customer: "Which 沙發 are on sale and in stock?"

Xiao Mei wants to check: Find items in the 沙發 category that are on sale (original price is greater than the discounted price) and still in stock.

Logical Operator OR：

Scenario 6: Multi-Category Query

Customer: "I’m looking for 櫃子 or 桌子.”

Xiao Mei wants to check: Find items that are either in the 櫃子 category or the 桌子 category.

Scenario 7: Specific Item

Customer: "Is the 北歐風雙人沙發 or the 貓抓皮L型沙發 still in stock?"

Xiao Mei wants to check: Find the stock status of these two specific sofas.

IN Operator：

Scenario 8: Multi-Category Query

Customer: "What furniture do you have for the living room? I want to see 沙發, 櫃子, and 桌子."

Xiao Mei wants to check: Find items in the 沙發, 櫃子, and 桌子 categories.

Scenario 9: Specific Products

Customer: "What are the prices of the 電腦辦公椅 and the 餐椅四入組?"

Xiao Mei wants to check: Find the prices of these two specific chairs.

BETWEEN：

Scenario 10: Price Range

Customer: "I’m looking for items priced between 10,000 and 20,000."

Xiao Mei wants to check: List all items within this price range.

Scenario 11: Stock Range

Supervisor: "Please list items with stock levels between 5 and 15."

Xiao Mei wants to check: List items with stock quantities within this range.

NOT IN：

Scenario 12: Exclude Products

Supervisor: "List all items except for 沙發 and 床架."

Xiao Mei wants to check: Find items that are not 沙發 or 床架.

Update and Delete：

Scenario 13: Adjust Price

Supervisor: "The price of the 北歐風雙人沙發 needs to be reduced by 2,000."

Xiao Mei wants to check: How to update the price of this sofa.

Scenario 14: Update Stock

Supervisor: "Five 電腦辦公椅 have arrived."

Xiao Mei wants to check: How to increase the stock quantity for this chair.

Scenario 15: Delete Data

Supervisor: "We need to remove the records for the 兒童床架 and the 電競書桌."

Xiao Mei wants to check: How to delete these two items from the database.

Question from a Classmate

Apply a 50% Discount on the Highest-Stock Item in the 椅子 Category

To apply a 50% discount on the item with the highest stock in the "椅子" category, you can use the following SQL statement:

However, PostgreSQL does not allow the use of ORDER BY and LIMIT in an UPDATE statement.

Therefore, we can use a subquery to find the name of the "椅子" category item with the highest stock, and then proceed with the update:

In this way, the code will first identify the name of the "椅子" item with the highest stock, then set the discount price for that item to 50% of the original price.