Question

Consider the given table GARMENT:

+--------+--------------+------+--------+-------+
| GCODE  | GNAME        | SIZE | COLOUR | PRICE |
+--------+--------------+------+--------+-------+
| 111    | TShirt       | XL   | Red    | 1400  |
| 112    | Jeans        | L    | Blue   | 1600  |
| 113    | Skirt        | M    | Black  | 1100  |
| 114    | Ladies Jacket| XL   | Blue   | 4000  |
| 115    | Trousers     | L    | Brown  | 1500  |
| 116    | Ladies Top   | L    | Pink   | 1200  |
+--------+--------------+------+--------+-------+

Write SQL commands for the following: (a) To display names of those garments that are available in ‘L’ size.
(b) To change the colour of garments with code as 115 to “Orange”.
(c) To add a new row with the following data:
GCODE = 117, GNAME = Kurta, SIZE = M, COLOUR = White, PRICE = 1000
(d) To remove the column ‘COLOUR’ from the table ‘GARMENT’.

Hint:

Use SELECT with WHERE to filter specific rows.
UPDATE modifies existing records based on conditions.
INSERT INTO adds new data into a table.
ALTER TABLE with DROP COLUMN removes an entire column from the table.

Answer 1

(a) To fetch garments of size 'L', we use the SELECT statement with a WHERE clause.
It ensures only those records are fetched where SIZE is equal to 'L'.

SELECT GNAME FROM GARMENT WHERE SIZE = 'L';

This command retrieves the names of garments available in L size: Jeans, Trousers, and Ladies Top.
(b) To update the COLOUR of garment with GCODE 115, use the UPDATE statement.
It targets the exact row using the GCODE field.

UPDATE GARMENT SET COLOUR = 'Orange' WHERE GCODE = 115;

This statement changes the COLOUR of Trousers (GCODE 115) to Orange.
(c) The INSERT INTO statement adds a new row to the GARMENT table.
We provide values in the same order as the columns in the table.

INSERT INTO GARMENT (GCODE, GNAME, SIZE, COLOUR, PRICE)  
VALUES (117, 'Kurta', 'M', 'White', 1000);

This command adds a new garment record named Kurta to the table.
(d) To remove a column from a table in SQL, we use the ALTER TABLE statement with DROP COLUMN.

ALTER TABLE GARMENT DROP COLUMN COLOUR;

This command removes the COLOUR column and all its associated data from the GARMENT table.