Scribd
Upload
51 views12 pages

SQL Database Management Guide

The document shows the creation of a MySQL database called online_food and a table called order_food to store food delivery orders. Various columns like OrderID, OrderDate, Rest_Name, etc. are added to the table. Sample order data is inserted and updated. The Qty column is added and altered multiple times to store quantity of items in each order.

Uploaded by

nkneeraj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
51 views12 pages

SQL Database Management Guide

The document shows the creation of a MySQL database called online_food and a table called order_food to store food delivery orders. Various columns like OrderID, OrderDate, Rest_Name, etc. are added to the table. Sample order data is inserted and updated. The Qty column is added and altered multiple times to store quantity of items in each order.

Uploaded by

nkneeraj
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| customerdata |
| customers |
| details1 |
| employees |
| food_db |
| gdb041 |
| information_schema |
| invoicing |
| masterdata |
| movies |
| moviesdb |
| my_movies |
| mydb |
| mysql |
| online_food |
| onlinestore |
| performance_schema |
| random_tables |
| sakila |
| sql_hr |
| sql_intro |
| sql_inventory |
| sql_store |
| sys |
| world |
+--------------------+

use online_food;
Database changed

mysql> create table order_food(OrderID int, OrderDate date, Rest_Name varchar(30),


Del_Name varchar(30), Menu_Name varchar(30), Price int, primary key(OrderID));

mysql> show tables;


+-----------------------+
| Tables_in_online_food |
+-----------------------+
| order_food |
+-----------------------+

mysql> desc order_food;


+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| OrderID | int | NO | PRI | NULL | |
| OrderDate | date | YES | | NULL | |
| Rest_Name | varchar(30) | YES | | NULL | |
| Del_Name | varchar(30) | YES | | NULL | |
| Menu_Name | varchar(30) | YES | | NULL | |
| Price | int | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> insert into order_food (OrderID, OrderDate, Rest_Name, Del_Name, Menu_Name,
Price)
-> values(1001, "2022-01-01", "Ganga Baba", "Neeraj", "Biryani", 350);

mysql> select * from order_food;


+---------+------------+------------+----------+-----------+-------+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price |
+---------+------------+------------+----------+-----------+-------+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 |
+---------+------------+------------+----------+-----------+-------+
1 row in set (0.00 sec)

mysql> insert into order_food (OrderID, OrderDate, Rest_Name, Del_Name, Menu_Name,


Price)
-> value(1002, "2022-01-02", "Shivam Rest", "Shyam", "Thali", 280)
-> ;

mysql> insert into order_food (OrderID, OrderDate, Rest_Name, Del_Name, Menu_Name,


Price)
-> values(1003, "2022-01-03", "Shivam Rest", "Nikhil", "Masala Dosa", 70),
-> (1004, "2022-01-04", "Ganga Baba", "Runa", "Chicken Curry", 450),
-> (1005, "2022-01-05", "Shivam Rest", "Preeti", "Paneera", 350);

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price |
+---------+------------+-------------+----------+---------------+-------+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 |
+---------+------------+-------------+----------+---------------+-------+

mysql> alter table order_rest


-> add Qty int;

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+------+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+------+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | NULL |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | NULL |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | NULL |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | NULL |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | NULL |
+---------+------------+-------------+----------+---------------+-------+------+

mysql> update order_food


-> set Qty = 2
-> where Rest_Name = "Ganga Baba";

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+------+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+------+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | NULL |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | NULL |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 2 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | NULL |
+---------+------------+-------------+----------+---------------+-------+------+

mysql> alter table order_food


-> drop column Qty;

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price |
+---------+------------+-------------+----------+---------------+-------+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 |
+---------+------------+-------------+----------+---------------+-------+

mysql> alter table order_food


-> add Qty int not null;

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 0 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | 0 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | 0 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | 0 |
+---------+------------+-------------+----------+---------------+-------+-----+

mysql> update order_food


-> set Qty= 2
-> where OrderID in (1001,1005);

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | 0 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | 0 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | 2 |
+---------+------------+-------------+----------+---------------+-------+-----+

mysql> update order_food


-> set Qty = 1
-> where Rest_Name = "Shivam Rest";

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | 1 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | 1 |
+---------+------------+-------------+----------+---------------+-------+-----+

mysql> insert into order_food (OrderID, OrderDate, Price, Qty)


-> values(1006, "2022-01-06", 90, 2);

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | 1 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | 1 |
| 1006 | 2022-01-06 | NULL | NULL | NULL | 90 | 2 |
+---------+------------+-------------+----------+---------------+-------+-----+

mysql> insert into order_food (OrderID, OrderDate, Rest_Name, Price, Qty)


-> values(1007, "2022-01-07", "Radha Rest", null,0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | 1 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | 1 |
| 1006 | 2022-01-06 | NULL | NULL | NULL | 90 | 2 |
| 1007 | 2022-01-07 | Radha Rest | NULL | NULL | NULL | 0 |
+---------+------------+-------------+----------+---------------+-------+-----+
7 rows in set (0.00 sec)

mysql> insert into order_food (OrderID, OrderDate, Rest_Name, Del_Name, Menu_Name,


Price, Qty)
-> values(1008, "2022-01-08", "Radha Rest", "Preeti", "Biryani", 425, 2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into order_food (OrderID, OrderDate, Rest_Name, Del_Name, Menu_Name,


Price, Qty)
-> values(1009, "2022-01-09", "Ganga Baba", "Neeraj", "Biryani", 450, 3),
-> (1010, "2022-01-10", "Ganga Baba", "Shyam", "Paneera", 390, 2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | 1 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | 1 |
| 1006 | 2022-01-06 | NULL | NULL | NULL | 90 | 2 |
| 1007 | 2022-01-07 | Radha Rest | NULL | NULL | NULL | 0 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | Biryani | 425 | 2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | Biryani | 450 | 3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | Paneera | 390 | 2 |
+---------+------------+-------------+----------+---------------+-------+-----+
10 rows in set (0.00 sec)

mysql> update order_food


-> set del_name = "Nikhil"
-> where OrderID = 1006;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | 1 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | 1 |
| 1006 | 2022-01-06 | Raja Rest | Nikhil | NULL | 90 | 2 |
| 1007 | 2022-01-07 | Radha Rest | NULL | NULL | NULL | 0 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | Biryani | 425 | 2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | Biryani | 450 | 3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | Paneera | 390 | 2 |
+---------+------------+-------------+----------+---------------+-------+-----+

mysql> update order_food


-> set del_name = "Mona", Menu_Name = "Thali", Price = 250, Qty = 2
-> where OrderID = 1007;

mysql> select * from order_food;


+---------+------------+-------------+----------+---------------+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Menu_Name | Price | Qty |
+---------+------------+-------------+----------+---------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | Biryani | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | Thali | 280 | 1 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | Masala Dosa | 70 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | Chicken Curry | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | Paneera | 350 | 1 |
| 1006 | 2022-01-06 | Raja Rest | Nikhil | NULL | 90 | 2 |
| 1007 | 2022-01-07 | Radha Rest | Mona | Thali | 250 | 2 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | Biryani | 425 | 2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | Biryani | 450 | 3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | Paneera | 390 | 2 |
+---------+------------+-------------+----------+---------------+-------+-----+

mysql> alter table order_food


-> add column Local_Zone varchar(4) not null after Del_Name;
mysql> select * from order_food;
+---------+------------+-------------+----------+------------+---------------
+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name |
Price | Qty |
+---------+------------+-------------+----------+------------+---------------
+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | | Biryani |
350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | | Thali |
280 | 1 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | | Masala Dosa |
70 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | | Chicken Curry |
450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | | Paneera |
350 | 1 |
| 1006 | 2022-01-06 | Raja Rest | Nikhil | | NULL |
90 | 2 |
| 1007 | 2022-01-07 | Radha Rest | Mona | | Thali |
250 | 2 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | | Biryani |
425 | 2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | | Biryani |
450 | 3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | | Paneera |
390 | 2 |
+---------+------------+-------------+----------+------------+---------------
+-------+-----+
10 rows in set (0.00 sec)

mysql> update order_food


-> set Local_Zone = "N"
-> where Del_Name = "Neeraj";

mysql> update order_food


-> set Local_Zone = "S"
-> where del_name = "Preeti";

mysql> update order_food


-> set local_zone = "E"
-> where del_name = "Shyam";

mysql> update order_food


-> set local_zone = "W"
-> where del_name in ("Runa", "Mona");

mysql> select * from order_food;


+---------+------------+-------------+----------+------------+---------------
+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name |
Price | Qty |
+---------+------------+-------------+----------+------------+---------------
+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | N | Biryani |
350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | E | Thali |
280 | 1 |
| 1003 | 2022-01-03 | Shivam Rest | Nikhil | | Masala Dosa |
70 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | W | Chicken Curry |
450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | S | Paneera |
350 | 1 |
| 1006 | 2022-01-06 | Raja Rest | Nikhil | | NULL |
90 | 2 |
| 1007 | 2022-01-07 | Radha Rest | Mona | W | Thali |
250 | 2 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | S | Biryani |
425 | 2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | N | Biryani |
450 | 3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | E | Paneera |
390 | 2 |
+---------+------------+-------------+----------+------------+---------------
+-------+-----+
10 rows in set (0.00 sec)

mysql> delete from order_food


-> where del_name = "Nikhil";

mysql> select * from order_food;


+---------+------------+-------------+----------+------------+---------------
+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name |
Price | Qty |
+---------+------------+-------------+----------+------------+---------------
+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | N | Biryani |
350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | E | Thali |
280 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | W | Chicken Curry |
450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | S | Paneera |
350 | 1 |
| 1007 | 2022-01-07 | Radha Rest | Mona | W | Thali |
250 | 2 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | S | Biryani |
425 | 2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | N | Biryani |
450 | 3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | E | Paneera |
390 | 2 |
+---------+------------+-------------+----------+------------+---------------
+-------+-----+

mysql> select distinct rest_name from order_food;


+-------------+
| rest_name |
+-------------+
| Ganga Baba |
| Shivam Rest |
| Radha Rest |
+-------------+

mysql> select count(*) as count_total from order_food;


+-------------+
| count_total |
+-------------+
| 8 |
+-------------+

mysql> select * from order_food


-> where Rest_Name = "Ganga Baba"
-> order by price desc;
+---------+------------+------------+----------+------------+---------------
+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name | Price
| Qty |
+---------+------------+------------+----------+------------+---------------
+-------+-----+
| 1004 | 2022-01-04 | Ganga Baba | Runa | W | Chicken Curry | 450
| 0 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | N | Biryani | 450
| 3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | E | Paneera | 390
| 2 |
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | N | Biryani | 350
| 2 |
+---------+------------+------------+----------+------------+---------------
+-------+-----+

mysql> select * from order_food


-> where qty >=3;
+---------+------------+------------+----------+------------+-----------+-------
+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name | Price |
Qty |
+---------+------------+------------+----------+------------+-----------+-------
+-----+
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | N | Biryani | 450 |
3 |
+---------+------------+------------+----------+------------+-----------+-------
+-----+
1 row in set (0.00 sec)

mysql> select orderid, orderdate, rest_name from order_food


-> where Local_Zone ="N" and Menu_Name="Biryani";
+---------+------------+------------+
| orderid | orderdate | rest_name |
+---------+------------+------------+
| 1001 | 2022-01-01 | Ganga Baba |
| 1009 | 2022-01-09 | Ganga Baba |
+---------+------------+------------+
2 rows in set (0.00 sec)

mysql> select orderid, orderdate, rest_name, menu_name, price from order_food


-> where Rest_Name = "Shivam Rest" or Del_name= "Preeti";
+---------+------------+-------------+-----------+-------+
| orderid | orderdate | rest_name | menu_name | price |
+---------+------------+-------------+-----------+-------+
| 1002 | 2022-01-02 | Shivam Rest | Thali | 280 |
| 1005 | 2022-01-05 | Shivam Rest | Paneera | 350 |
| 1008 | 2022-01-08 | Radha Rest | Biryani | 425 |
+---------+------------+-------------+-----------+-------+
3 rows in set (0.00 sec)

mysql> select orderid, orderdate, rest_name, price, qty from order_food


-> where orderdate between "2022-01-01" and "2022-01-05";
+---------+------------+-------------+-------+-----+
| orderid | orderdate | rest_name | price | qty |
+---------+------------+-------------+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | 350 | 2 |
| 1002 | 2022-01-02 | Shivam Rest | 280 | 1 |
| 1004 | 2022-01-04 | Ganga Baba | 450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | 350 | 1 |
+---------+------------+-------------+-------+-----+
4 rows in set (0.00 sec)

mysql> select * from order_food


-> where qty != 0;
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name | Price |
Qty |
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | N | Biryani | 350 |
2 |
| 1002 | 2022-01-02 | Shivam Rest | Shyam | E | Thali | 280 |
1 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | S | Paneera | 350 |
1 |
| 1007 | 2022-01-07 | Radha Rest | Mona | W | Thali | 250 |
2 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | S | Biryani | 425 |
2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | N | Biryani | 450 |
3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | E | Paneera | 390 |
2 |
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
7 rows in set (0.00 sec)

mysql> select * from order_food


-> where Del_Name like "%ee%";
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name | Price |
Qty |
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | N | Biryani | 350 |
2 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | S | Paneera | 350 |
1 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | S | Biryani | 425 |
2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | N | Biryani | 450 |
3 |
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
4 rows in set (0.00 sec)

mysql> select menu_name, price from order_food


-> where price <300;
+-----------+-------+
| menu_name | price |
+-----------+-------+
| Thali | 280 |
| Thali | 250 |
+-----------+-------+
2 rows in set (0.00 sec)

mysql> select * from order_food


-> where local_zone in ("N","S");
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name | Price |
Qty |
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | N | Biryani | 350 |
2 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | S | Paneera | 350 |
1 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | S | Biryani | 425 |
2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | N | Biryani | 450 |
3 |
+---------+------------+-------------+----------+------------+-----------+-------
+-----+
4 rows in set (0.00 sec)

mysql> select * from order_food


-> where menu_name in ("Biryani","Paneera") or rest_name = "Ganga Baba";
+---------+------------+-------------+----------+------------+---------------
+-------+-----+
| OrderID | OrderDate | Rest_Name | Del_Name | Local_Zone | Menu_Name |
Price | Qty |
+---------+------------+-------------+----------+------------+---------------
+-------+-----+
| 1001 | 2022-01-01 | Ganga Baba | Neeraj | N | Biryani |
350 | 2 |
| 1004 | 2022-01-04 | Ganga Baba | Runa | W | Chicken Curry |
450 | 0 |
| 1005 | 2022-01-05 | Shivam Rest | Preeti | S | Paneera |
350 | 1 |
| 1008 | 2022-01-08 | Radha Rest | Preeti | S | Biryani |
425 | 2 |
| 1009 | 2022-01-09 | Ganga Baba | Neeraj | N | Biryani |
450 | 3 |
| 1010 | 2022-01-10 | Ganga Baba | Shyam | E | Paneera |
390 | 2 |
+---------+------------+-------------+----------+------------+---------------
+-------+-----+

mysql> select rest_name, menu_name, price from order_food


-> where rest_name != "Ganga Baba";
+-------------+-----------+-------+
| rest_name | menu_name | price |
+-------------+-----------+-------+
| Shivam Rest | Thali | 280 |
| Shivam Rest | Paneera | 350 |
| Radha Rest | Thali | 250 |
| Radha Rest | Biryani | 425 |
+-------------+-----------+-------+

mysql> select sum(price) as grand_total from order_food;


+-------------+
| grand_total |
+-------------+
| 2945 |
+-------------+

mysql> select count(distinct rest_name) from order_food;


+---------------------------+
| count(distinct rest_name) |
+---------------------------+
| 3 |
+---------------------------+

mysql> select sum(price) as total, avg(price) as avg_price, max(price)


highest_price, min(price) lowest_price from order_food;
+-------+-----------+---------------+--------------+
| total | avg_price | highest_price | lowest_price |
+-------+-----------+---------------+--------------+
| 2945 | 368.1250 | 450 | 250 |
+-------+-----------+---------------+--------------+

mysql> select rest_name, sum(price) as price_total from order_food


-> group by rest_name;
+-------------+-------------+
| rest_name | price_total |
+-------------+-------------+
| Ganga Baba | 1640 |
| Shivam Rest | 630 |
| Radha Rest | 675 |
+-------------+-------------+

mysql> select rest_name, sum(price) as price_total, sum(qty)


qty_total,count(rest_name) as rest_num from order_food
-> group by rest_name;
+-------------+-------------+-----------+----------+
| rest_name | price_total | qty_total | rest_num |
+-------------+-------------+-----------+----------+
| Ganga Baba | 1640 | 7 | 4 |
| Shivam Rest | 630 | 2 | 2 |
| Radha Rest | 675 | 4 | 2 |
+-------------+-------------+-----------+----------+
mysql> select orderid, rest_name, menu_name, price from order_food
-> order by price desc
-> limit 3;
+---------+------------+---------------+-------+
| orderid | rest_name | menu_name | price |
+---------+------------+---------------+-------+
| 1004 | Ganga Baba | Chicken Curry | 450 |
| 1009 | Ganga Baba | Biryani | 450 |
| 1008 | Radha Rest | Biryani | 425 |
+---------+------------+---------------+-------+

mysql> select menu_name, price, qty, price*qty as amt_total from order_food;


+---------------+-------+-----+-----------+
| menu_name | price | qty | amt_total |
+---------------+-------+-----+-----------+
| Biryani | 350 | 2 | 700 |
| Thali | 280 | 1 | 280 |
| Chicken Curry | 450 | 0 | 0 |
| Paneera | 350 | 1 | 350 |
| Thali | 250 | 2 | 500 |
| Biryani | 425 | 2 | 850 |
| Biryani | 450 | 3 | 1350 |
| Paneera | 390 | 2 | 780 |
+---------------+-------+-----+-----------+

You might also like