In this article, I am going to create garment table in MySQL and insert data into it and i will also implement various mysql command on garment table .

Also read : How to insert multiple rows at a time in MySQL

Consider the GARMENT database and answer the following SQL queries based on it

1. To create table garment using given data( refer table 1)
2. To insert rows into garment table
3. To display contents of garment table
4. To display names of those garments that are available in “L” Size.
5. To display codes and names of those garment that have their names starting with “Ladies”.
6. To display garment names,codes and prices of those garment that have price in range 1000.00 to 1500.00 (both 1000.00 and 1500.00 included)
7. To change the colour of garment with code as 116 to “Orange”.
8. To display garment code ,names and prices of all garments in descending order of prices.
9. Increase the price of all garments in the given table by 15%.
10. Remove tuples from garment tables that have price less than 1300.00
11. To change the existing column namely colour of garment table to color

Solutions:

1. To create table garment using given data( refer table 1)

mysql> CREATE TABLE GARMENT
    -> (GCODE INT,
    -> GNAME VARCHAR(30),
    -> SIZE CHAR(3),
    -> COLOUR CHAR(10),
    -> PRICE DECIMAL(8,2));
Query OK, 0 rows affected (1.81 sec)

2.To insert rows into garment table

mysql> INSERT INTO GARMENT
    -> (GCODE,GNAME,SIZE,COLOUR,PRICE)
    -> VALUES
    -> (111,"TSHIRT","XL","RED",1400.00),
    -> (112,"JEANS","L","BLUE",1600.00),
    -> (113,"SKIRT","M","BLACK",1100.00),
    -> (114,"LADIES JACKET","XL","BLUE",4000.00),
    -> (115,"TROUSERS","L","BROWN",1500.00),
    -> (116,"LADIES TOP","L","PINK",1200.00)
    -> ;
Query OK, 6 rows affected (0.33 sec)
Records: 6  Duplicates: 0  Warnings: 0

3.To display contents of garment table

mysql> SELECT * FROM GARMENT;
+-------+---------------+------+--------+---------+
| GCODE | GNAME         | SIZE | COLOUR | PRICE   |
+-------+---------------+------+--------+---------+
|   111 | TSHIRT        | XL   | RED    | 1400.00 |
|   112 | JEANS         | L    | BLUE   | 1600.00 |
|   113 | SKIRT         | M    | BLACK  | 1100.00 |
|   114 | LADIES JACKET | XL   | BLUE   | 4000.00 |
|   115 | TROUSERS      | L    | BROWN  | 1500.00 |
|   116 | LADIES TOP    | L    | PINK   | 1200.00 |
+-------+---------------+------+--------+---------+
6 rows in set (0.01 sec)

4. To display names of those garments that are available in “L” Size.

mysql> SELECT GNAME
    -> FROM GARMENT
    -> WHERE SIZE = "L";
+------------+
| GNAME      |
+------------+
| JEANS      |
| TROUSERS   |
| LADIES TOP |
+------------+
3 rows in set (0.61 sec)

5. To display codes and names of those garment that have their names starting with “Ladies”.

mysql> SELECT GCODE,GNAME
    -> FROM GARMENT
    -> WHERE GNAME LIKE "LADIES%";
+-------+---------------+
| GCODE | GNAME         |
+-------+---------------+
|   114 | LADIES JACKET |
|   116 | LADIES TOP    |
+-------+---------------+
2 rows in set (0.06 sec)

6. To display garment names,codes and prices of those garment that have price in range 1000.00 to 1500.00 (both 1000.00 and 1500.00 included)

mysql> SELECT GNAME,GCODE,PRICE
    -> FROM GARMENT
    -> WHERE PRICE BETWEEN 1000.00 AND 1500.00;
+------------+-------+---------+
| GNAME      | GCODE | PRICE   |
+------------+-------+---------+
| TSHIRT     |   111 | 1400.00 |
| SKIRT      |   113 | 1100.00 |
| TROUSERS   |   115 | 1500.00 |
| LADIES TOP |   116 | 1200.00 |
+------------+-------+---------+
4 rows in set (0.11 sec)

7. To change the colour of garment with code as 116 to “Orange”.

mysql> UPDATE GARMENT
    -> SET COLOUR = "ORANGE"
    -> WHERE GCODE = 116;
Query OK, 1 row affected (0.22 sec)
Rows matched: 1  Changed: 1  Warnings: 0

After changing colour of garment with code as 116, table will look like

mysql> SELECT * FROM GARMENT;
+-------+---------------+------+--------+---------+
| GCODE | GNAME         | SIZE | COLOUR | PRICE   |
+-------+---------------+------+--------+---------+
|   111 | TSHIRT        | XL   | RED    | 1400.00 |
|   112 | JEANS         | L    | BLUE   | 1600.00 |
|   113 | SKIRT         | M    | BLACK  | 1100.00 |
|   114 | LADIES JACKET | XL   | BLUE   | 4000.00 |
|   115 | TROUSERS      | L    | BROWN  | 1500.00 |
|   116 | LADIES TOP    | L    | ORANGE | 1200.00 |
+-------+---------------+------+--------+---------+
6 rows in set (0.02 sec)

8. To display garment code ,names and prices of all garments in descending order of prices.

mysql> SELECT GCODE,GNAME,PRICE
    -> FROM GARMENT
    -> ORDER BY PRICE DESC;
+-------+---------------+---------+
| GCODE | GNAME         | PRICE   |
+-------+---------------+---------+
|   114 | LADIES JACKET | 4000.00 |
|   112 | JEANS         | 1600.00 |
|   115 | TROUSERS      | 1500.00 |
|   111 | TSHIRT        | 1400.00 |
|   116 | LADIES TOP    | 1200.00 |
|   113 | SKIRT         | 1100.00 |
+-------+---------------+---------+
6 rows in set (0.06 sec)

9. Increase the price of all garments in the given table by 15%.

mysql> UPDATE GARMENT
    -> SET PRICE = PRICE + 0.15 * PRICE;
Query OK, 6 rows affected (0.17 sec)
Rows matched: 6  Changed: 6  Warnings: 0


After increasing prices of all graments by 15%, table will look like

mysql> SELECT * FROM GARMENT;
+-------+---------------+------+--------+---------+
| GCODE | GNAME         | SIZE | COLOUR | PRICE   |
+-------+---------------+------+--------+---------+
|   111 | TSHIRT        | XL   | RED    | 1610.00 |
|   112 | JEANS         | L    | BLUE   | 1840.00 |
|   113 | SKIRT         | M    | BLACK  | 1265.00 |
|   114 | LADIES JACKET | XL   | BLUE   | 4600.00 |
|   115 | TROUSERS      | L    | BROWN  | 1725.00 |
|   116 | LADIES TOP    | L    | ORANGE | 1380.00 |
+-------+---------------+------+--------+---------+
6 rows in set (0.07 sec)

10. Remove tuples from garment tables that have price less than 1300.00

Mysql> DELETE FROM GARMENT
    -> WHERE PRICE < 1300.00;
Query OK, 1 row affected (0.11 sec)


After deleting tuples from table, table will contains following data

mysql> SELECT * FROM GARMENT;
+-------+---------------+------+--------+---------+
| GCODE | GNAME         | SIZE | COLOUR | PRICE   |
+-------+---------------+------+--------+---------+
|   111 | TSHIRT        | XL   | RED    | 1610.00 |
|   112 | JEANS         | L    | BLUE   | 1840.00 |
|   114 | LADIES JACKET | XL   | BLUE   | 4600.00 |
|   115 | TROUSERS      | L    | BROWN  | 1725.00 |
|   116 | LADIES TOP    | L    | ORANGE | 1380.00 |
+-------+---------------+------+--------+---------+
5 rows in set (0.01 sec)

11. To change the existing column namely colour of garment table to color

mysql> ALTER TABLE GARMENT
    -> CHANGE COLOUR COLOR CHAR(10);
Query OK, 0 rows affected (3.05 sec)
Records: 0  Duplicates: 0  Warnings: 0



After changing the column name, table will look like

mysql> SELECT * FROM GARMENT;
+-------+---------------+------+--------+---------+
| GCODE | GNAME         | SIZE | COLOR  | PRICE   |
+-------+---------------+------+--------+---------+
|   111 | TSHIRT        | XL   | RED    | 1610.00 |
|   112 | JEANS         | L    | BLUE   | 1840.00 |
|   114 | LADIES JACKET | XL   | BLUE   | 4600.00 |
|   115 | TROUSERS      | L    | BROWN  | 1725.00 |
|   116 | LADIES TOP    | L    | ORANGE | 1380.00 |
+-------+---------------+------+--------+---------+
5 rows in set (0.09 sec)

Related MYSQL Queries and Python programs :

Create garment table and insert data into it.

JOINING OF TWO TABLES IN MYSQL

Integrate MySQL with python by importing suitable modules

Write a python program to implement a stack using list

Python program for Binary Search in linear list