SQL QUERIES FOR CLASS 12 PRACTICAL FILE

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.03 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| club           |
| student        |
+----------------+
2 rows in set (0.01 sec)

mysql> select * from club;
+---------+-----------+------+------------+------------+------+------+
| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |
+---------+-----------+------+------------+------------+------+------+
|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |
|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |
|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |
|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |
|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |
+---------+-----------+------+------------+------------+------+------+
5 rows in set (0.03 sec)

mysql> desc club;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| coachid   | int(11)     | NO   | PRI | NULL    |       |
| coachname | varchar(30) | YES  |     | NULL    |       |
| age       | int(11)     | YES  |     | NULL    |       |
| sports    | varchar(30) | YES  |     | NULL    |       |
| dateofapp | date        | YES  |     | NULL    |       |
| pay       | int(11)     | YES  |     | NULL    |       |
| sex       | char(2)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql> select * from club order by age;
+---------+-----------+------+------------+------------+------+------+
| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |
+---------+-----------+------+------------+------------+------+------+
|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |
|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |
|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |
|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |
|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |
+---------+-----------+------+------------+------------+------+------+
5 rows in set (0.02 sec)

mysql> select * from club order by age desc;
+---------+-----------+------+------------+------------+------+------+
| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |
+---------+-----------+------+------------+------------+------+------+
|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |
|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |
|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |
|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |
|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |
+---------+-----------+------+------------+------------+------+------+
5 rows in set (0.00 sec)

mysql> select * from club order by age asc;
+---------+-----------+------+------------+------------+------+------+
| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |
+---------+-----------+------+------------+------------+------+------+
|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |
|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |
|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |
|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |
|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |
+---------+-----------+------+------------+------------+------+------+
5 rows in set (0.00 sec)

mysql> select sports from club;
+------------+
| sports     |
+------------+
| karate     |
| karate     |
| squash     |
| basketball |
| swimmining |
+------------+
5 rows in set (0.00 sec)

mysql> select distinct (sport)s from club;
ERROR 1054 (42S22): Unknown column 'sport' in 'field list'
mysql> select distinct (sports) from club;
+------------+
| sports     |
+------------+
| karate     |
| squash     |
| basketball |
| swimmining |
+------------+
4 rows in set (0.01 sec)

mysql> select * from club;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

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

5 rows in set (0.03 sec)

mysql> desc club;

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

| Field     | Type        | Null | Key | Default | Extra |

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

| coachid   | int(11)     | NO   | PRI | NULL    |       |

| coachname | varchar(30) | YES  |     | NULL    |       |

| age       | int(11)     | YES  |     | NULL    |       |

| sports    | varchar(30) | YES  |     | NULL    |       |

| dateofapp | date        | YES  |     | NULL    |       |

| pay       | int(11)     | YES  |     | NULL    |       |

| sex       | char(2)     | YES  |     | NULL    |       |

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

7 rows in set (0.01 sec)

mysql> select * from club order by age;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

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

5 rows in set (0.02 sec)

mysql> select * from club order by age desc;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

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

5 rows in set (0.00 sec)

mysql> select * from club order by age asc;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

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

5 rows in set (0.00 sec)

mysql> select sports from club;

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

| sports     |

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

| karate     |

| karate     |

| squash     |

| basketball |

| swimmining |

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

5 rows in set (0.00 sec)

mysql> select distinct (sport)s from club;

ERROR 1054 (42S22): Unknown column 'sport' in 'field list'

mysql> select distinct (sports) from club;

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

| sports     |

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

| karate     |

| squash     |

| basketball |

| swimmining |

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

4 rows in set (0.01 sec)

mysql> create table employee(empno int not null primary key, empname varchar(30)

, salary decimal);

Query OK, 0 rows affected (0.38 sec)

mysql> select * from employee;

Empty set (0.00 sec)

mysql> desc employee;

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

| Field   | Type          | Null | Key | Default | Extra |

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

| empno   | int(11)       | NO   | PRI | NULL    |       |

| empname | varchar(30)   | YES  |     | NULL    |       |

| salary  | decimal(10,0) | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

mysql> insert into employee values(1000,'Pari',120000.50);

Query OK, 1 row affected, 1 warning (0.09 sec)

mysql> insert into employee values(2000,'Harneet',220000.50);

Query OK, 1 row affected, 1 warning (0.14 sec)

mysql> insert into employee values(3000,'Anoushka',210000.50);

Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> insert into employee values(4000,'Akshat',216000.70);

Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> select * from employee;

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

| empno | empname  | salary |

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

|  1000 | Pari     | 120001 |

|  2000 | Harneet  | 220001 |

|  3000 | Anoushka | 210001 |

|  4000 | Akshat   | 216001 |

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

4 rows in set (0.00 sec)

mysql> show tables;

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

| Tables_in_test |

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

| club           |

| employee       |

| ipmarks        |

| niki           |

| student        |

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

5 rows in set (0.00 sec)

mysql> select * from club;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

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

5 rows in set (0.02 sec)

mysql> select * from club where pay>1000;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

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

3 rows in set (0.00 sec)

mysql> select * from club where coachname like 'k%';

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

| coachid | coachname | age  | sports | dateofapp  | pay  | sex  |

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

|       1 | kukreja   |   35 | karate | 1996-03-27 | 1000 | m    |

|       3 | karan     |   34 | squash | 1998-02-19 | 2000 | m    |

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

2 rows in set (0.00 sec)

mysql> select * from club where coachname like '%a';

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

| coachid | coachname | age  | sports | dateofapp  | pay  | sex  |

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

|       1 | kukreja   |   35 | karate | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate | 1998-01-20 | 1200 | f    |

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

2 rows in set (0.00 sec)

mysql> select * from club order by pay;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

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

5 rows in set (0.00 sec)

mysql> select * from club order by pay asc;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

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

5 rows in set (0.00 sec)

mysql> select * from club order by pay desc;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

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

5 rows in set (0.00 sec)

mysql> select coachname,sports from club where sex='m';

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

| coachname | sports     |

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

| kukreja   | karate     |

| karan     | squash     |

| tarun     | basketball |

| zubin     | swimmining |

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

4 rows in set (0.00 sec)

mysql> select * from club where pay >1000 and pay<2000;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

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

2 rows in set (0.00 sec)

mysql> select * from club where pay between 1000 and 2000;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

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

4 rows in set (0.00 sec)

mysql> select * from club where pay >=1000 and pay<=2000;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       1 | kukreja   |   35 | karate     | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate     | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash     | 1998-02-19 | 2000 | m    |

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

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

4 rows in set (0.00 sec)

mysql> select * from club where pay not between 1000 and 2000;

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

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

1 row in set (0.00 sec)

mysql> select * from club where sports='karate' or sports='squash';

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

| coachid | coachname | age  | sports | dateofapp  | pay  | sex  |

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

|       1 | kukreja   |   35 | karate | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash | 1998-02-19 | 2000 | m    |

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

3 rows in set (0.00 sec)

mysql> select * from club where sports in('karate','squash');

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

| coachid | coachname | age  | sports | dateofapp  | pay  | sex  |

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

|       1 | kukreja   |   35 | karate | 1996-03-27 | 1000 | m    |

|       2 | ravina    |   34 | karate | 1998-01-20 | 1200 | f    |

|       3 | karan     |   34 | squash | 1998-02-19 | 2000 | m    |

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

3 rows in set (0.00 sec)

mysql> select * from club where sports not in('karate','squash');

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

| coachid | coachname | age  | sports     | dateofapp  | pay  | sex  |

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

|       4 | tarun     |   33 | basketball | 1998-01-01 | 1500 | m    |

|       5 | zubin     |   36 | swimmining | 1998-01-12 |  750 | m    |

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

2 rows in set (0.00 sec)