Scribd
Upload
3 views27 pages

Structured Query Language SQL Practical

The document outlines practical exercises for creating and managing a database using SQL, specifically for a store's product and sales information. It includes commands for creating a database, creating tables, inserting records, and querying data with various SQL commands such as SELECT, WHERE, and ORDER BY. The examples demonstrate how to manipulate and retrieve data from the database effectively.

Uploaded by

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

Structured Query Language SQL Practical

The document outlines practical exercises for creating and managing a database using SQL, specifically for a store's product and sales information. It includes commands for creating a database, creating tables, inserting records, and querying data with various SQL commands such as SELECT, WHERE, and ORDER BY. The examples demonstrate how to manipulate and retrieve data from the database effectively.

Uploaded by

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

STRUCTURED QUERY LANGUAGE (SQL)

Class XII Computer Science Practical File

1
Creation of Database
mysql> CREATE DATABASE store;
Query OK, 1 row affected (0.01 sec)

Using Database
mysql> USE store;
Database changed

Creation of Product Table


mysql> CREATE TABLE product(
-> pid INT PRIMARY KEY,
-> pname VARCHAR(30),
-> category VARCHAR(20),
-> price INT,
-> stock INT
-> );
Query OK, 0 rows affected (0.05 sec)

Creation of Sales Table


mysql> CREATE TABLE sales(
-> sid INT PRIMARY KEY,
-> pid INT,
-> qty INT,
-> city VARCHAR(20)
-> );
Query OK, 0 rows affected (0.05 sec)

2
Inserting Records
mysql> INSERT INTO product VALUES
-> (101, 'Laptop', 'Electronics', 55000, 15),
-> (102, 'Mouse', 'Electronics', 700, 40),
-> (103, 'Keyboard', 'Electronics', 1200, 30),
-> (104, 'Monitor', 'Electronics', 9500, 20),
-> (105, 'Printer', 'Electronics', 8500, 10),
-> (106, 'Bag', 'Accessories', 1500, 35),
-> (107, 'Bottle', 'Accessories', 350, 50),
-> (108, 'Notebook', 'Stationery', 80, 100),
-> (109, 'Pen', 'Stationery', 20, 300),
-> (110, 'Calculator', 'Stationery', 650, 25),
-> (111, 'Chair', 'Furniture', 2500, 12),
-> (112, 'Table', 'Furniture', 4500, 8),
-> (113, 'Fan', 'Appliances', 2800, 18),
-> (114, 'LED Bulb', 'Appliances', 250, 75),
-> (115, 'Water Cooler', 'Appliances', 9500, 5);
Query OK, 15 rows affected (0.01 sec)
Records: 15 Duplicates: 0 Warnings: 0

Inserting Records
mysql> INSERT INTO sales VALUES
-> (1, 101, 2, 'Lucknow'),
-> (2, 102, 5, 'Kanpur'),
-> (3, 103, 3, 'Prayagraj'),
-> (4, 104, 1, 'Lucknow'),
-> (5, 105, 2, 'Agra'),
-> (6, 106, 4, 'Raebareli'),
-> (7, 107, 8, 'Kanpur'),
-> (8, 108, 15, 'Lucknow'),
-> (9, 109, 25, 'Ayodhya'),
-> (10, 110, 3, 'Noida'),
-> (11, 111, 2, 'Lucknow'),
-> (12, 112, 1, 'Meerut'),
-> (13, 113, 4, 'Kanpur'),
-> (14, 114, 20, 'Agra'),
-> (15, 115, 1, 'Varanasi'),
-> (16, 101, 1, 'Lucknow'),
-> (17, 102, 6, 'Prayagraj'),
-> (18, 103, 2, 'Noida'),
-> (19, 104, 2, 'Kanpur'),
-> (20, 105, 1, 'Lucknow'),
-> (21, 106, 3, 'Ayodhya'),
-> (22, 107, 5, 'Meerut'),
-> (23, 108, 12, 'Lucknow'),
-> (24, 109, 30, 'Raebareli'),
-> (25, 114, 10, 'Kanpur');
Query OK, 25 rows affected (0.01 sec)
Records: 25 Duplicates: 0 Warnings: 0

3
SELECT
mysql> SELECT * FROM product;
+-----+--------------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+--------------+-------------+-------+-------+
| 101 | Laptop | Electronics | 55000 | 15 |
| 102 | Mouse | Electronics | 700 | 40 |
| 103 | Keyboard | Electronics | 1200 | 30 |
| 104 | Monitor | Electronics | 9500 | 20 |
| 105 | Printer | Electronics | 8500 | 10 |
| 106 | Bag | Accessories | 1500 | 35 |
| 107 | Bottle | Accessories | 350 | 50 |
| 108 | Notebook | Stationery | 80 | 100 |
| 109 | Pen | Stationery | 20 | 300 |
| 110 | Calculator | Stationery | 650 | 25 |
| 111 | Chair | Furniture | 2500 | 12 |
| 112 | Table | Furniture | 4500 | 8 |
| 113 | Fan | Appliances | 2800 | 18 |
| 114 | LED Bulb | Appliances | 250 | 75 |
| 115 | Water Cooler | Appliances | 9500 | 5 |
+-----+--------------+-------------+-------+-------+
15 rows in set (0.00 sec)

4
SELECT
mysql> SELECT * FROM sales;
+-----+-----+-----+-----------+
| sid | pid | qty | city |
+-----+-----+-----+-----------+
| 1 | 101 | 2 | Lucknow |
| 2 | 102 | 5 | Kanpur |
| 3 | 103 | 3 | Prayagraj |
| 4 | 104 | 1 | Lucknow |
| 5 | 105 | 2 | Agra |
| 6 | 106 | 4 | Raebareli |
| 7 | 107 | 8 | Kanpur |
| 8 | 108 | 15 | Lucknow |
| 9 | 109 | 25 | Ayodhya |
| 10 | 110 | 3 | Noida |
| 11 | 111 | 2 | Lucknow |
| 12 | 112 | 1 | Meerut |
| 13 | 113 | 4 | Kanpur |
| 14 | 114 | 20 | Agra |
| 15 | 115 | 1 | Varanasi |
| 16 | 101 | 1 | Lucknow |
| 17 | 102 | 6 | Prayagraj |
| 18 | 103 | 2 | Noida |
| 19 | 104 | 2 | Kanpur |
| 20 | 105 | 1 | Lucknow |
| 21 | 106 | 3 | Ayodhya |
| 22 | 107 | 5 | Meerut |
| 23 | 108 | 12 | Lucknow |
| 24 | 109 | 30 | Raebareli |
| 25 | 114 | 10 | Kanpur |
+-----+-----+-----+-----------+
25 rows in set (0.00 sec)

5
SELECT
mysql> SELECT pname, price FROM product;
+--------------+-------+
| pname | price |
+--------------+-------+
| Laptop | 55000 |
| Mouse | 700 |
| Keyboard | 1200 |
| Monitor | 9500 |
| Printer | 8500 |
| Bag | 1500 |
| Bottle | 350 |
| Notebook | 80 |
| Pen | 20 |
| Calculator | 650 |
| Chair | 2500 |
| Table | 4500 |
| Fan | 2800 |
| LED Bulb | 250 |
| Water Cooler | 9500 |
+--------------+-------+
15 rows in set (0.00 sec)

SELECT
mysql> SELECT pname, stock FROM product;
+--------------+-------+
| pname | stock |
+--------------+-------+
| Laptop | 15 |
| Mouse | 40 |
| Keyboard | 30 |
| Monitor | 20 |
| Printer | 10 |
| Bag | 35 |
| Bottle | 50 |
| Notebook | 100 |
| Pen | 300 |
| Calculator | 25 |
| Chair | 12 |
| Table | 8 |
| Fan | 18 |
| LED Bulb | 75 |
| Water Cooler | 5 |
+--------------+-------+
15 rows in set (0.00 sec)

6
SELECT
mysql> SELECT pname, category, price FROM product;
+--------------+-------------+-------+
| pname | category | price |
+--------------+-------------+-------+
| Laptop | Electronics | 55000 |
| Mouse | Electronics | 700 |
| Keyboard | Electronics | 1200 |
| Monitor | Electronics | 9500 |
| Printer | Electronics | 8500 |
| Bag | Accessories | 1500 |
| Bottle | Accessories | 350 |
| Notebook | Stationery | 80 |
| Pen | Stationery | 20 |
| Calculator | Stationery | 650 |
| Chair | Furniture | 2500 |
| Table | Furniture | 4500 |
| Fan | Appliances | 2800 |
| LED Bulb | Appliances | 250 |
| Water Cooler | Appliances | 9500 |
+--------------+-------------+-------+
15 rows in set (0.00 sec)

WHERE
mysql> SELECT * FROM product
-> WHERE price>5000;
+-----+--------------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+--------------+-------------+-------+-------+
| 101 | Laptop | Electronics | 55000 | 15 |
| 104 | Monitor | Electronics | 9500 | 20 |
| 105 | Printer | Electronics | 8500 | 10 |
| 115 | Water Cooler | Appliances | 9500 | 5 |
+-----+--------------+-------------+-------+-------+
4 rows in set (0.00 sec)

WHERE
mysql> SELECT * FROM product
-> WHERE category='Electronics';
+-----+----------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+----------+-------------+-------+-------+
| 101 | Laptop | Electronics | 55000 | 15 |
| 102 | Mouse | Electronics | 700 | 40 |
| 103 | Keyboard | Electronics | 1200 | 30 |
| 104 | Monitor | Electronics | 9500 | 20 |
| 105 | Printer | Electronics | 8500 | 10 |
+-----+----------+-------------+-------+-------+

7
5 rows in set (0.00 sec)

8
WHERE
mysql> SELECT * FROM product
-> WHERE stock<20;
+-----+--------------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+--------------+-------------+-------+-------+
| 101 | Laptop | Electronics | 55000 | 15 |
| 105 | Printer | Electronics | 8500 | 10 |
| 111 | Chair | Furniture | 2500 | 12 |
| 112 | Table | Furniture | 4500 | 8 |
| 113 | Fan | Appliances | 2800 | 18 |
| 115 | Water Cooler | Appliances | 9500 | 5 |
+-----+--------------+-------------+-------+-------+
6 rows in set (0.00 sec)

WHERE
mysql> SELECT * FROM sales
-> WHERE city='Lucknow';
+-----+-----+-----+---------+
| sid | pid | qty | city |
+-----+-----+-----+---------+
| 1 | 101 | 2 | Lucknow |
| 4 | 104 | 1 | Lucknow |
| 8 | 108 | 15 | Lucknow |
| 11 | 111 | 2 | Lucknow |
| 16 | 101 | 1 | Lucknow |
| 20 | 105 | 1 | Lucknow |
| 23 | 108 | 12 | Lucknow |
+-----+-----+-----+---------+
7 rows in set (0.00 sec)

9
WHERE
mysql> SELECT * FROM sales
-> WHERE qty>=10;
+-----+-----+-----+-----------+
| sid | pid | qty | city |
+-----+-----+-----+-----------+
| 8 | 108 | 15 | Lucknow |
| 9 | 109 | 25 | Ayodhya |
| 14 | 114 | 20 | Agra |
| 23 | 108 | 12 | Lucknow |
| 24 | 109 | 30 | Raebareli |
| 25 | 114 | 10 | Kanpur |
+-----+-----+-----+-----------+
6 rows in set (0.00 sec)

DISTINCT
mysql> SELECT DISTINCT category
-> FROM product;
+-------------+
| category |
+-------------+
| Electronics |
| Accessories |
| Stationery |
| Furniture |
| Appliances |
+-------------+
5 rows in set (0.00 sec)

DISTINCT
mysql> SELECT DISTINCT city
-> FROM sales;
+-----------+
| city |
+-----------+
| Lucknow |
| Kanpur |
| Prayagraj |
| Agra |
| Raebareli |
| Ayodhya |
| Noida |
| Meerut |
| Varanasi |
+-----------+
9 rows in set (0.00 sec)

10
ORDER BY
mysql> SELECT * FROM product
-> ORDER BY price;
+-----+--------------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+--------------+-------------+-------+-------+
| 109 | Pen | Stationery | 20 | 300 |
| 108 | Notebook | Stationery | 80 | 100 |
| 114 | LED Bulb | Appliances | 250 | 75 |
| 107 | Bottle | Accessories | 350 | 50 |
| 110 | Calculator | Stationery | 650 | 25 |
| 102 | Mouse | Electronics | 700 | 40 |
| 103 | Keyboard | Electronics | 1200 | 30 |
| 106 | Bag | Accessories | 1500 | 35 |
| 111 | Chair | Furniture | 2500 | 12 |
| 113 | Fan | Appliances | 2800 | 18 |
| 112 | Table | Furniture | 4500 | 8 |
| 105 | Printer | Electronics | 8500 | 10 |
| 104 | Monitor | Electronics | 9500 | 20 |
| 115 | Water Cooler | Appliances | 9500 | 5 |
| 101 | Laptop | Electronics | 55000 | 15 |
+-----+--------------+-------------+-------+-------+
15 rows in set (0.00 sec)

11
ORDER BY
mysql> SELECT * FROM product
-> ORDER BY price DESC;
+-----+--------------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+--------------+-------------+-------+-------+
| 101 | Laptop | Electronics | 55000 | 15 |
| 104 | Monitor | Electronics | 9500 | 20 |
| 115 | Water Cooler | Appliances | 9500 | 5 |
| 105 | Printer | Electronics | 8500 | 10 |
| 112 | Table | Furniture | 4500 | 8 |
| 113 | Fan | Appliances | 2800 | 18 |
| 111 | Chair | Furniture | 2500 | 12 |
| 106 | Bag | Accessories | 1500 | 35 |
| 103 | Keyboard | Electronics | 1200 | 30 |
| 102 | Mouse | Electronics | 700 | 40 |
| 110 | Calculator | Stationery | 650 | 25 |
| 107 | Bottle | Accessories | 350 | 50 |
| 114 | LED Bulb | Appliances | 250 | 75 |
| 108 | Notebook | Stationery | 80 | 100 |
| 109 | Pen | Stationery | 20 | 300 |
+-----+--------------+-------------+-------+-------+
15 rows in set (0.00 sec)

ORDER BY
mysql> SELECT * FROM product
-> ORDER BY stock DESC;
+-----+--------------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+--------------+-------------+-------+-------+
| 109 | Pen | Stationery | 20 | 300 |
| 108 | Notebook | Stationery | 80 | 100 |
| 114 | LED Bulb | Appliances | 250 | 75 |
| 107 | Bottle | Accessories | 350 | 50 |
| 102 | Mouse | Electronics | 700 | 40 |
| 106 | Bag | Accessories | 1500 | 35 |
| 103 | Keyboard | Electronics | 1200 | 30 |
| 110 | Calculator | Stationery | 650 | 25 |
| 104 | Monitor | Electronics | 9500 | 20 |
| 113 | Fan | Appliances | 2800 | 18 |
| 101 | Laptop | Electronics | 55000 | 15 |
| 111 | Chair | Furniture | 2500 | 12 |
| 105 | Printer | Electronics | 8500 | 10 |
| 112 | Table | Furniture | 4500 | 8 |
| 115 | Water Cooler | Appliances | 9500 | 5 |
+-----+--------------+-------------+-------+-------+
15 rows in set (0.00 sec)

12
ORDER BY
mysql> SELECT * FROM sales
-> ORDER BY qty;
+-----+-----+-----+-----------+
| sid | pid | qty | city |
+-----+-----+-----+-----------+
| 4 | 104 | 1 | Lucknow |
| 12 | 112 | 1 | Meerut |
| 15 | 115 | 1 | Varanasi |
| 16 | 101 | 1 | Lucknow |
| 20 | 105 | 1 | Lucknow |
| 1 | 101 | 2 | Lucknow |
| 5 | 105 | 2 | Agra |
| 11 | 111 | 2 | Lucknow |
| 18 | 103 | 2 | Noida |
| 19 | 104 | 2 | Kanpur |
| 3 | 103 | 3 | Prayagraj |
| 10 | 110 | 3 | Noida |
| 21 | 106 | 3 | Ayodhya |
| 6 | 106 | 4 | Raebareli |
| 13 | 113 | 4 | Kanpur |
| 2 | 102 | 5 | Kanpur |
| 22 | 107 | 5 | Meerut |
| 17 | 102 | 6 | Prayagraj |
| 7 | 107 | 8 | Kanpur |
| 25 | 114 | 10 | Kanpur |
| 23 | 108 | 12 | Lucknow |
| 8 | 108 | 15 | Lucknow |
| 14 | 114 | 20 | Agra |
| 9 | 109 | 25 | Ayodhya |
| 24 | 109 | 30 | Raebareli |
+-----+-----+-----+-----------+
25 rows in set (0.00 sec)

13
UPDATE
mysql> UPDATE product
-> SET price=60000
-> WHERE pid=101;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM product;


+-----+--------------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+--------------+-------------+-------+-------+
| 101 | Laptop | Electronics | 60000 | 15 |
| 102 | Mouse | Electronics | 700 | 40 |
| 103 | Keyboard | Electronics | 1200 | 30 |
| 104 | Monitor | Electronics | 9500 | 20 |
| 105 | Printer | Electronics | 8500 | 10 |
| 106 | Bag | Accessories | 1500 | 35 |
| 107 | Bottle | Accessories | 350 | 50 |
| 108 | Notebook | Stationery | 80 | 100 |
| 109 | Pen | Stationery | 20 | 300 |
| 110 | Calculator | Stationery | 650 | 25 |
| 111 | Chair | Furniture | 2500 | 12 |
| 112 | Table | Furniture | 4500 | 8 |
| 113 | Fan | Appliances | 2800 | 18 |
| 114 | LED Bulb | Appliances | 250 | 75 |
| 115 | Water Cooler | Appliances | 9500 | 5 |
+-----+--------------+-------------+-------+-------+
15 rows in set (0.00 sec)

14
UPDATE
mysql> UPDATE product
-> SET stock=50
-> WHERE pid=105;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM product;


+-----+--------------+-------------+-------+-------+
| pid | pname | category | price | stock |
+-----+--------------+-------------+-------+-------+
| 101 | Laptop | Electronics | 60000 | 15 |
| 102 | Mouse | Electronics | 700 | 40 |
| 103 | Keyboard | Electronics | 1200 | 30 |
| 104 | Monitor | Electronics | 9500 | 20 |
| 105 | Printer | Electronics | 8500 | 50 |
| 106 | Bag | Accessories | 1500 | 35 |
| 107 | Bottle | Accessories | 350 | 50 |
| 108 | Notebook | Stationery | 80 | 100 |
| 109 | Pen | Stationery | 20 | 300 |
| 110 | Calculator | Stationery | 650 | 25 |
| 111 | Chair | Furniture | 2500 | 12 |
| 112 | Table | Furniture | 4500 | 8 |
| 113 | Fan | Appliances | 2800 | 18 |
| 114 | LED Bulb | Appliances | 250 | 75 |
| 115 | Water Cooler | Appliances | 9500 | 5 |
+-----+--------------+-------------+-------+-------+
15 rows in set (0.00 sec)

15
DELETE
mysql> DELETE FROM sales
-> WHERE sid=25;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM sales;


+-----+-----+-----+-----------+
| sid | pid | qty | city |
+-----+-----+-----+-----------+
| 1 | 101 | 2 | Lucknow |
| 2 | 102 | 5 | Kanpur |
| 3 | 103 | 3 | Prayagraj |
| 4 | 104 | 1 | Lucknow |
| 5 | 105 | 2 | Agra |
| 6 | 106 | 4 | Raebareli |
| 7 | 107 | 8 | Kanpur |
| 8 | 108 | 15 | Lucknow |
| 9 | 109 | 25 | Ayodhya |
| 10 | 110 | 3 | Noida |
| 11 | 111 | 2 | Lucknow |
| 12 | 112 | 1 | Meerut |
| 13 | 113 | 4 | Kanpur |
| 14 | 114 | 20 | Agra |
| 15 | 115 | 1 | Varanasi |
| 16 | 101 | 1 | Lucknow |
| 17 | 102 | 6 | Prayagraj |
| 18 | 103 | 2 | Noida |
| 19 | 104 | 2 | Kanpur |
| 20 | 105 | 1 | Lucknow |
| 21 | 106 | 3 | Ayodhya |
| 22 | 107 | 5 | Meerut |
| 23 | 108 | 12 | Lucknow |
| 24 | 109 | 30 | Raebareli |
+-----+-----+-----+-----------+
24 rows in set (0.00 sec)

ALTER TABLE
mysql> ALTER TABLE product
-> ADD company VARCHAR(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESCRIBE product;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| pname | varchar(30) | YES | | NULL | |
| category | varchar(20) | YES | | NULL | |
| price | int | YES | | NULL | |
| stock | int | YES | | NULL | |
| company | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

16
17
UPDATE
mysql> UPDATE product
-> SET company='HP'
-> WHERE pid=101;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> DESCRIBE product;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| pname | varchar(30) | YES | | NULL | |
| category | varchar(20) | YES | | NULL | |
| price | int | YES | | NULL | |
| stock | int | YES | | NULL | |
| company | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

UPDATE
mysql> UPDATE product
-> SET company='Logitech'
-> WHERE pid=102;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM product;


+-----+--------------+-------------+-------+-------+----------+
| pid | pname | category | price | stock | company |
+-----+--------------+-------------+-------+-------+----------+
| 101 | Laptop | Electronics | 60000 | 15 | HP |
| 102 | Mouse | Electronics | 700 | 40 | Logitech |
| 103 | Keyboard | Electronics | 1200 | 30 | NULL |
| 104 | Monitor | Electronics | 9500 | 20 | NULL |
| 105 | Printer | Electronics | 8500 | 50 | NULL |
| 106 | Bag | Accessories | 1500 | 35 | NULL |
| 107 | Bottle | Accessories | 350 | 50 | NULL |
| 108 | Notebook | Stationery | 80 | 100 | NULL |
| 109 | Pen | Stationery | 20 | 300 | NULL |
| 110 | Calculator | Stationery | 650 | 25 | NULL |
| 111 | Chair | Furniture | 2500 | 12 | NULL |
| 112 | Table | Furniture | 4500 | 8 | NULL |
| 113 | Fan | Appliances | 2800 | 18 | NULL |
| 114 | LED Bulb | Appliances | 250 | 75 | NULL |
| 115 | Water Cooler | Appliances | 9500 | 5 | NULL |
+-----+--------------+-------------+-------+-------+----------+
15 rows in set (0.00 sec)

18
UPDATE
mysql> UPDATE product
-> SET company='Dell'
-> WHERE pid BETWEEN 103 AND 111;
Query OK, 9 rows affected (0.01 sec)
Rows matched: 9 Changed: 9 Warnings: 0

mysql> SELECT * FROM product;


+-----+--------------+-------------+-------+-------+----------+
| pid | pname | category | price | stock | company |
+-----+--------------+-------------+-------+-------+----------+
| 101 | Laptop | Electronics | 60000 | 15 | HP |
| 102 | Mouse | Electronics | 700 | 40 | Logitech |
| 103 | Keyboard | Electronics | 1200 | 30 | Dell |
| 104 | Monitor | Electronics | 9500 | 20 | Dell |
| 105 | Printer | Electronics | 8500 | 50 | Dell |
| 106 | Bag | Accessories | 1500 | 35 | Dell |
| 107 | Bottle | Accessories | 350 | 50 | Dell |
| 108 | Notebook | Stationery | 80 | 100 | Dell |
| 109 | Pen | Stationery | 20 | 300 | Dell |
| 110 | Calculator | Stationery | 650 | 25 | Dell |
| 111 | Chair | Furniture | 2500 | 12 | Dell |
| 112 | Table | Furniture | 4500 | 8 | NULL |
| 113 | Fan | Appliances | 2800 | 18 | NULL |
| 114 | LED Bulb | Appliances | 250 | 75 | NULL |
| 115 | Water Cooler | Appliances | 9500 | 5 | NULL |
+-----+--------------+-------------+-------+-------+----------+
15 rows in set (0.00 sec)

Creation of Table
mysql> CREATE TABLE demo(
-> id INT,
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW TABLES;


+------------------+
| Tables_in_store |
+------------------+
| demo |
| product |
| sales |
+------------------+
3 rows in set (0.01 sec)

19
DROP TABLE
mysql> DROP TABLE demo;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;


+------------------+
| Tables_in_store |
+------------------+
| product |
| sales |
+------------------+
2 rows in set (0.00 sec)

Creation of Table
mysql> CREATE TABLE temp(
-> id INT,
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.04 sec)

Inserting Records
mysql> INSERT INTO temp VALUES
-> (1,'A'),
-> (2,'B'),
-> (3,'C'),
-> (4,'D');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

SELECT
mysql> SELECT * FROM temp;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+----+------+
4 rows in set (0.00 sec)

20
TRUNCATE TABLE
mysql> TRUNCATE TABLE temp;
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM temp;


Empty set (0.00 sec)

Aggregate Functions
mysql> SELECT COUNT(*) FROM product;
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+
1 row in set (0.00 sec)

Aggregate Functions
mysql> SELECT SUM(price) FROM product;
+------------+
| SUM(price) |
+------------+
| 102050 |
+------------+
1 row in set (0.00 sec)

Aggregate Functions
mysql> SELECT AVG(price) FROM product;
+------------+
| AVG(price) |
+------------+
| 6803.3333 |
+------------+
1 row in set (0.00 sec)

Aggregate Functions
mysql> SELECT MAX(price) FROM product;
+------------+
| MAX(price) |

21
+------------+
| 60000 |
+------------+
1 row in set (0.00 sec)

22
Aggregate Functions
mysql> SELECT SUM(qty) FROM sales;
+----------+
| SUM(qty) |
+----------+
| 158 |
+----------+
1 row in set (0.00 sec)

GROUP BY
mysql> SELECT category,COUNT(*)
-> FROM product
-> GROUP BY category;
+-------------+----------+
| category | COUNT(*) |
+-------------+----------+
| Electronics | 5 |
| Accessories | 2 |
| Stationery | 3 |
| Furniture | 2 |
| Appliances | 3 |
+-------------+----------+
5 rows in set (0.00 sec)

GROUP BY
mysql> SELECT city,SUM(qty)
-> FROM sales
-> GROUP BY city;
+-----------+----------+
| city | SUM(qty) |
+-----------+----------+
| Lucknow | 34 |
| Kanpur | 19 |
| Prayagraj | 9 |
| Agra | 22 |
| Raebareli | 34 |
| Ayodhya | 28 |
| Noida | 5 |
| Meerut | 6 |
| Varanasi | 1 |
+-----------+----------+
9 rows in set (0.00 sec)

23
HAVING
mysql> SELECT category,AVG(price)
-> FROM product
-> GROUP BY category
-> HAVING AVG(price)>5000;
+-------------+------------+
| category | AVG(price) |
+-------------+------------+
| Electronics | 15980.0000 |
+-------------+------------+
1 row in set (0.00 sec)

HAVING
mysql> SELECT city,SUM(qty)
-> FROM sales
-> GROUP BY city
-> HAVING SUM(qty)>20;
+-----------+----------+
| city | SUM(qty) |
+-----------+----------+
| Lucknow | 34 |
| Agra | 22 |
| Raebareli | 34 |
| Ayodhya | 28 |
+-----------+----------+
4 rows in set (0.00 sec)

24
INNER JOIN
mysql> SELECT [Link],
-> [Link],
-> [Link],
-> [Link],
-> [Link]
-> FROM product
-> INNER JOIN sales
-> ON [Link]=[Link];
+-----+--------------+-------+-----+-----------+
| pid | pname | price | qty | city |
+-----+--------------+-------+-----+-----------+
| 101 | Laptop | 60000 | 2 | Lucknow |
| 102 | Mouse | 700 | 5 | Kanpur |
| 103 | Keyboard | 1200 | 3 | Prayagraj |
| 104 | Monitor | 9500 | 1 | Lucknow |
| 105 | Printer | 8500 | 2 | Agra |
| 106 | Bag | 1500 | 4 | Raebareli |
| 107 | Bottle | 350 | 8 | Kanpur |
| 108 | Notebook | 80 | 15 | Lucknow |
| 109 | Pen | 20 | 25 | Ayodhya |
| 110 | Calculator | 650 | 3 | Noida |
| 111 | Chair | 2500 | 2 | Lucknow |
| 112 | Table | 4500 | 1 | Meerut |
| 113 | Fan | 2800 | 4 | Kanpur |
| 114 | LED Bulb | 250 | 20 | Agra |
| 115 | Water Cooler | 9500 | 1 | Varanasi |
| 101 | Laptop | 60000 | 1 | Lucknow |
| 102 | Mouse | 700 | 6 | Prayagraj |
| 103 | Keyboard | 1200 | 2 | Noida |
| 104 | Monitor | 9500 | 2 | Kanpur |
| 105 | Printer | 8500 | 1 | Lucknow |
| 106 | Bag | 1500 | 3 | Ayodhya |
| 107 | Bottle | 350 | 5 | Meerut |
| 108 | Notebook | 80 | 12 | Lucknow |
| 109 | Pen | 20 | 30 | Raebareli |
+-----+--------------+-------+-----+-----------+
24 rows in set (0.00 sec)

25
LEFT JOIN
mysql> SELECT [Link],
-> [Link],
-> [Link],
-> [Link],
-> [Link]
-> FROM product
-> LEFT JOIN sales
-> ON [Link]=[Link];
+-----+--------------+-------+-----+-----------+
| pid | pname | price | qty | city |
+-----+--------------+-------+-----+-----------+
| 101 | Laptop | 60000 | 1 | Lucknow |
| 101 | Laptop | 60000 | 2 | Lucknow |
| 102 | Mouse | 700 | 6 | Prayagraj |
| 102 | Mouse | 700 | 5 | Kanpur |
| 103 | Keyboard | 1200 | 2 | Noida |
| 103 | Keyboard | 1200 | 3 | Prayagraj |
| 104 | Monitor | 9500 | 2 | Kanpur |
| 104 | Monitor | 9500 | 1 | Lucknow |
| 105 | Printer | 8500 | 1 | Lucknow |
| 105 | Printer | 8500 | 2 | Agra |
| 106 | Bag | 1500 | 3 | Ayodhya |
| 106 | Bag | 1500 | 4 | Raebareli |
| 107 | Bottle | 350 | 5 | Meerut |
| 107 | Bottle | 350 | 8 | Kanpur |
| 108 | Notebook | 80 | 12 | Lucknow |
| 108 | Notebook | 80 | 15 | Lucknow |
| 109 | Pen | 20 | 30 | Raebareli |
| 109 | Pen | 20 | 25 | Ayodhya |
| 110 | Calculator | 650 | 3 | Noida |
| 111 | Chair | 2500 | 2 | Lucknow |
| 112 | Table | 4500 | 1 | Meerut |
| 113 | Fan | 2800 | 4 | Kanpur |
| 114 | LED Bulb | 250 | 20 | Agra |
| 115 | Water Cooler | 9500 | 1 | Varanasi |
+-----+--------------+-------+-----+-----------+
24 rows in set (0.00 sec)

26
RIGHT JOIN
mysql> SELECT [Link],
-> [Link],
-> [Link],
-> [Link],
-> [Link]
-> FROM product
-> RIGHT JOIN sales
-> ON [Link]=[Link];
+-----+--------------+-------+-----+-----------+
| pid | pname | price | qty | city |
+-----+--------------+-------+-----+-----------+
| 101 | Laptop | 60000 | 2 | Lucknow |
| 102 | Mouse | 700 | 5 | Kanpur |
| 103 | Keyboard | 1200 | 3 | Prayagraj |
| 104 | Monitor | 9500 | 1 | Lucknow |
| 105 | Printer | 8500 | 2 | Agra |
| 106 | Bag | 1500 | 4 | Raebareli |
| 107 | Bottle | 350 | 8 | Kanpur |
| 108 | Notebook | 80 | 15 | Lucknow |
| 109 | Pen | 20 | 25 | Ayodhya |
| 110 | Calculator | 650 | 3 | Noida |
| 111 | Chair | 2500 | 2 | Lucknow |
| 112 | Table | 4500 | 1 | Meerut |
| 113 | Fan | 2800 | 4 | Kanpur |
| 114 | LED Bulb | 250 | 20 | Agra |
| 115 | Water Cooler | 9500 | 1 | Varanasi |
| 101 | Laptop | 60000 | 1 | Lucknow |
| 102 | Mouse | 700 | 6 | Prayagraj |
| 103 | Keyboard | 1200 | 2 | Noida |
| 104 | Monitor | 9500 | 2 | Kanpur |
| 105 | Printer | 8500 | 1 | Lucknow |
| 106 | Bag | 1500 | 3 | Ayodhya |
| 107 | Bottle | 350 | 5 | Meerut |
| 108 | Notebook | 80 | 12 | Lucknow |
| 109 | Pen | 20 | 30 | Raebareli |
+-----+--------------+-------+-----+-----------+
24 rows in set (0.00 sec)

27

You might also like