Scribd
Upload
618 views

IP mySQL Assignment

The document describes creating a table called "Item" in a database called "Market" with fields for item number, item name, department code, quantity, unit price, and stock date. It then provides SQL queries to insert sample item records, and queries to select records based on various criteria like item name, quantity range, including/excluding certain fields, ordering, and filtering on date.

Uploaded by

Aryan Singh
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
618 views

IP mySQL Assignment

The document describes creating a table called "Item" in a database called "Market" with fields for item number, item name, department code, quantity, unit price, and stock date. It then provides SQL queries to insert sample item records, and queries to select records based on various criteria like item name, quantity range, including/excluding certain fields, ordering, and filtering on date.

Uploaded by

Aryan Singh
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

CLASS XI – IP

MY-SQL – ASSIGNMENT

Q1. Create the following table and add the records


ItemNo Item Dcode Qty UnitPrice StockDate
5005 Ball Pen 0.5 102 100 16 2018-03-10
5003 Ball Pen 0.25 102 150 20 2017-05-17
5002 Gel Pen Premium 101 125 14 2018-04-20
5006 Gel Pen Classic 101 200 22 2018-10-08
5001 Eraser Small 102 210 5 2018-03-11
5004 Eraser Big 102 60 10 2017-11-18
5009 Sharpener Classic NULL 160 8 2017-06-12

COMMANDS FOR CREATING TABLE:-

mysql> create database Market;

Query OK, 1 row affected (0.56 sec)

mysql> use Market;

Database changed

mysql> create table Item(Itemno int(4) Primary key, Item Varchar(20), Dcode int(

3), Qty int(4), Unitprice int(3), StockDate date);

Query OK, 0 rows affected, 4 warnings (1.65 sec)

mysql> insert into Item values(5005, "Ball Pen 0.5", 102, 100, 16, "2018-03-10")

Query OK, 1 row affected (0.45 sec)

mysql> select * from Item;

+--------+--------------+-------+------+-----------+------------+

| Itemno | Item | Dcode | Qty | Unitprice | StockDate |

+--------+--------------+-------+------+-----------+------------+

| 5005 | Ball Pen 0.5 | 102 | 100 | 16 | 2018-03-10 |

+--------+--------------+-------+------+-----------+------------+
1 row in set (0.04 sec)

mysql> insert into Item values(5003, "Ball Pen 0.25", 102, 150, 20, "2017-05-17"

);

Query OK, 1 row affected (0.17 sec)

mysql> insert into Item values(5002, "Gel Pen Premium", 101, 125, 14, "2018-04-2

0");

Query OK, 1 row affected (0.07 sec)

mysql> insert into Item values(5006, "Gel Pen Classic", 101, 200, 22, "2018-10-0

8");

Query OK, 1 row affected (0.16 sec)

mysql> insert into Item values(5001, "Eraser Small", 102, 210, 5, "2018-03-11");

Query OK, 1 row affected (0.07 sec)

mysql> insert into Item values(5004, "Eraser Big", 102, 60, 10, "2017-11-18");

Query OK, 1 row affected (0.18 sec)

mysql> insert into Item values(5004, "Sharpener Classic", NULL, 160, 8, "2017-06

-12");

ERROR 1062 (23000): Duplicate entry '5004' for key 'item.PRIMARY'

mysql> insert into Item values(5009, "Sharpener Classic", NULL, 160, 8, "2017-06

-12");

Query OK, 1 row affected (0.07 sec)

Write down the following queries based on the given table:


Q2. Select all record of table.
mysql> select * from Item;

+--------+-------------------+-------+------+-----------+------------+

| Itemno | Item | Dcode | Qty | Unitprice | StockDate |

+--------+-------------------+-------+------+-----------+------------+

| 5001 | Eraser Small | 102 | 210 | 5 | 2018-03-11 |

| 5002 | Gel Pen Premium | 101 | 125 | 14 | 2018-04-20 |

| 5003 | Ball Pen 0.25 | 102 | 150 | 20 | 2017-05-17 |

| 5004 | Eraser Big | 102 | 60 | 10 | 2017-11-18 |

| 5005 | Ball Pen 0.5 | 102 | 100 | 16 | 2018-03-10 |

| 5006 | Gel Pen Classic | 101 | 200 | 22 | 2018-10-08 |

| 5009 | Sharpener Classic | NULL | 160 | 8 | 2017-06-12 |

+--------+-------------------+-------+------+-----------+------------+

7 rows in set (0.00 sec)

Q3. Select ItemNo, name and Unitprice.


mysql> select Itemno,Item,UnitPrice from Item;

+--------+-------------------+-----------+

| Itemno | Item | UnitPrice |

+--------+-------------------+-----------+

| 5001 | Eraser Small | 5|

| 5002 | Gel Pen Premium | 14 |

| 5003 | Ball Pen 0.25 | 20 |

| 5004 | Eraser Big | 10 |

| 5005 | Ball Pen 0.5 | 16 |

| 5006 | Gel Pen Classic | 22 |


| 5009 | Sharpener Classic | 8|

+--------+-------------------+-----------+

7 rows in set (0.00 sec)

Q4. Select all item record where Unitprice is more than 20.

mysql> select * from Item where UnitPrice > 20;

+--------+-----------------+-------+------+-----------+------------+

| Itemno | Item | Dcode | Qty | Unitprice | StockDate |

+--------+-----------------+-------+------+-----------+------------+

| 5006 | Gel Pen Classic | 101 | 200 | 22 | 2018-10-08 |

+--------+-----------------+-------+------+-----------+------------+

1 row in set (0.15 sec)

Q5. Select Item name of those items which are quantity between 100-200.

mysql> select Item from Item where Qty between 100 and 200;

+-------------------+

| Item |

+-------------------+

| Gel Pen Premium |

| Ball Pen 0.25 |

| Ball Pen 0.5 |

| Gel Pen Classic |

| Sharpener Classic |

+-------------------+
5 rows in set (0.00 sec)

Q6. Select all record of Items which contains pen word in it.

mysql> select * from Item where Item like "%pen%";

+--------+-------------------+-------+------+-----------+------------+

| Itemno | Item | Dcode | Qty | Unitprice | StockDate |

+--------+-------------------+-------+------+-----------+------------+

| 5002 | Gel Pen Premium | 101 | 125 | 14 | 2018-04-20 |

| 5003 | Ball Pen 0.25 | 102 | 150 | 20 | 2017-05-17 |

| 5005 | Ball Pen 0.5 | 102 | 100 | 16 | 2018-03-10 |

| 5006 | Gel Pen Classic | 101 | 200 | 22 | 2018-10-08 |

| 5009 | Sharpener Classic | NULL | 160 | 8 | 2017-06-12 |

+--------+-------------------+-------+------+-----------+------------+

5 rows in set (0.01 sec)

Q7. Select unique dcode of all items.

mysql> select DISTINCT Dcode from Item;

+-------+

| Dcode |

+-------+

| 102 |

| 101 |

| NULL |

+-------+
3 rows in set (0.11 sec)

Q8. Display all record in the descending order of UnitPrice.

mysql> select * from Item ORDER BY UnitPrice DESC;

+--------+-------------------+-------+------+-----------+------------+

| Itemno | Item | Dcode | Qty | Unitprice | StockDate |

+--------+-------------------+-------+------+-----------+------------+

| 5006 | Gel Pen Classic | 101 | 200 | 22 | 2018-10-08 |

| 5003 | Ball Pen 0.25 | 102 | 150 | 20 | 2017-05-17 |

| 5005 | Ball Pen 0.5 | 102 | 100 | 16 | 2018-03-10 |

| 5002 | Gel Pen Premium | 101 | 125 | 14 | 2018-04-20 |

| 5004 | Eraser Big | 102 | 60 | 10 | 2017-11-18 |

| 5009 | Sharpener Classic | NULL | 160 | 8 | 2017-06-12 |

| 5001 | Eraser Small | 102 | 210 | 5 | 2018-03-11 |

+--------+-------------------+-------+------+-----------+------------+

7 rows in set (0.04 sec)

Q9. Display all items which are stocked in the month of March.

mysql> select * from Item where StockDate like "______3___"

-> ;

+--------+--------------+-------+------+-----------+------------+

| Itemno | Item | Dcode | Qty | Unitprice | StockDate |

+--------+--------------+-------+------+-----------+------------+

| 5001 | Eraser Small | 102 | 210 | 5 | 2018-03-11 |


| 5005 | Ball Pen 0.5 | 102 | 100 | 16 | 2018-03-10 |

+--------+--------------+-------+------+-----------+------------+

2 rows in set (0.00 sec)

Q10. Display all record whose dcode is not assigned.

mysql> select * from Item where Dcode is NULL;

+--------+-------------------+-------+------+-----------+------------+

| Itemno | Item | Dcode | Qty | Unitprice | StockDate |

+--------+-------------------+-------+------+-----------+------------+

| 5009 | Sharpener Classic | NULL | 160 | 8 | 2017-06-12 |

+--------+-------------------+-------+------+-----------+------------+

1 row in set (0.00 sec)

You might also like