Question

(i) Write an SQL statement to create a table named PRODUCTS with the following specifications:

Column Name	Data Type	Key
PID	Integer	Primary Key
PName	Varchar of size 30
PCat	Char of size 15
Price	Decimal Number

(ii) Write an SQL query to increase the price of all items by 100, whose product category is Grocery, in the table PRODUCTS.

Hint:

Use CREATE TABLE with INT PRIMARY KEY, VARCHAR(30), CHAR(15) and DECIMAL. Then use UPDATE with SET Price = Price + 100 and WHERE PCat = 'Grocery'.

Answer 1

Step 1: Understand the two parts:
Part (i) is a DDL task: create the table PRODUCTS with four columns and a primary key. Part (ii) is a DML task: raise the price by 100 for one category.

Step 2: Part (i), choose the data types:
PID is an integer, so it gets INT, and it is the primary key.
PName is a Varchar of size 30, so it is VARCHAR(30).
PCat is a Char of size 15, so it is CHAR(15).
Price is a decimal number, so it gets DECIMAL(8,2). Here 8 is the total digits and 2 is the digits after the point.

Step 3: Part (i), write the statement:

CREATE TABLE PRODUCTS (
    PID INT PRIMARY KEY,
    PName VARCHAR(30),
    PCat CHAR(15),
    Price DECIMAL(8,2)
);

Step 4: Part (ii), pick the clauses:
UPDATE changes values in existing rows.
SET Price = Price + 100 adds 100 to the old price.
WHERE PCat = 'Grocery' limits the change to Grocery items only. Without WHERE every row would change.

UPDATE PRODUCTS
SET Price = Price + 100
WHERE PCat = 'Grocery';

A test run on a small table gave a Grocery price of 500 as 600, and a Stationery price of 10 stayed 10.

Final Answer:
(i) CREATE TABLE PRODUCTS (PID INT PRIMARY KEY, PName VARCHAR(30), PCat CHAR(15), Price DECIMAL(8,2));
(ii) UPDATE PRODUCTS SET Price = Price + 100 WHERE PCat = 'Grocery';