DATABASE MANAGEMENT SYSTEM
ASSIGNMENT 01
1.
mysql>SELECT FIRST_NAME AS W_NAME from Worker;
+----------+
| W_NAME |
+----------+
| Monika |
| Niharika |
| Vishal |
| Amitabh |
| Vivek |
| Vipul |
| Satish |
| Geetika |
+----------+
8 rows in set (0.03 sec)
2.
mysql> Select Upper(First_Name) as First_Name from Worker;
+------------+
| First_Name |
+------------+
| MONIKA |
| NIHARIKA |
| VISHAL |
| AMITABH |
| VIVEK |
| VIPUL |
| SATISH |
| GEETIKA |
+------------+
8 rows in set (0.10 sec)
3.
mysql> SELECT DISTINCT DEPARTMENT from Worker;
+------------+
| DEPARTMENT |
+------------+
| HR |
| Admin |
| Account |
+------------+
3 rows in set (0.04 sec)
4.
mysql> Select FIRST_NAME from Worker where FIRST_NAME LIKE 'M%' OR FIRST_NAME LIKE 'N%';
+------------+
| FIRST_NAME |
+------------+
| Monika |
| Niharika |
+------------+
2 rows in set (0.00 sec)
5.
mysql> SELECT SUBSTRING(FIRST_NAME,1,3) from Worker;
+---------------------------+
| SUBSTRING(FIRST_NAME,1,3) |
+---------------------------+
| Mon |
| Nih |
| Vis |
| Ami |
| Viv |
| Vip |
| Sat |
| Gee |
+---------------------------+
8 rows in set (0.01 sec)
6.
mysql> Select FIRST_NAME from worker where FIRST_NAME LIKE 'N%' OR FIRST_NAME LIKE 'M%' OR FIRST_NAME
LIKE 'V%';
+------------+
| FIRST_NAME |
+------------+
| Monika |
| Niharika |
| Vishal |
| Vivek |
| Vipul |
+------------+
5 rows in set (0.00 sec)
7.
mysql> SELECT WORKER_TITLE,COUNT(*) FROM Title GROUP BY WORKER_TITLE;
+---------------+----------+
| WORKER_TITLE | COUNT(*) |
+---------------+----------+
| Manager | 2|
| Executive | 3|
| Asst. Manager | 1|
| Lead | 2|
+---------------+----------+
4 rows in set (0.00 sec)
8.
mysql> SELECT DISTINCT(DEPARTMENT) FROM Worker;
+------------+
| DEPARTMENT |
+------------+
| HR |
| Admin |
| Account |
+------------+
3 rows in set (0.00 sec)
9.
mysql> SELECT REPLACE(FIRST_NAME,'a','aa') from Worker;
+------------------------------+
| REPLACE(FIRST_NAME,'a','aa') |
+------------------------------+
| Monikaa |
| Nihaarikaa |
| Vishaal |
| Amitaabh |
| Vivek |
| Vipul |
| Saatish |
| Geetikaa |
+------------------------------+
8 rows in set (0.01 sec)
10.
mysql> SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) AS COMPLETE_NAME FROM Worker;
+-----------------+
| COMPLETE_NAME |
+-----------------+
| Monika Arora |
| Niharika Verma |
| Vishal Singhal |
| Amitabh Singh |
| Vivek Bhati |
| Vipul Diwan |
| Satish Kumar |
| Geetika Chauhan |
+-----------------+
8 rows in set (0.04 sec)
11.
mysql> SELECT * from Worker ORDER BY FIRST_NAME ASC;
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin |
| 8 | Geetika | Chauhan | 90000 | 2014-04-11 09:00:00 | Admin |
| 1 | Monika | Arora | 100000 | 2014-02-20 09:00:00 | HR |
| 2 | Niharika | Verma | 80000 | 2014-06-11 09:00:00 | Admin |
| 7 | Satish | Kumar | 75000 | 2014-01-20 09:00:00 | Account |
| 6 | Vipul | Diwan | 200000 | 2014-06-11 09:00:00 | Account |
| 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR |
| 5 | Vivek | Bhati | 500000 | 2014-06-11 09:00:00 | Admin |
+-----------+------------+-----------+--------+---------------------+------------+
8 rows in set (0.16 sec)
12.
mysql> SELECT * from Worker ORDER BY FIRST_NAME asc,DEPARTMENT desc;
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin |
| 8 | Geetika | Chauhan | 90000 | 2014-04-11 09:00:00 | Admin |
| 1 | Monika | Arora | 100000 | 2014-02-20 09:00:00 | HR |
| 2 | Niharika | Verma | 80000 | 2014-06-11 09:00:00 | Admin |
| 7 | Satish | Kumar | 75000 | 2014-01-20 09:00:00 | Account |
| 6 | Vipul | Diwan | 200000 | 2014-06-11 09:00:00 | Account |
| 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR |
| 5 | Vivek | Bhati | 500000 | 2014-06-11 09:00:00 | Admin |
+-----------+------------+-----------+--------+---------------------+------------+
8 rows in set (0.00 sec)
13.
mysql> Select * from Worker where FIRST_NAME not in ('Vipul','Satish');
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 1 | Monika | Arora | 100000 | 2014-02-20 09:00:00 | HR |
| 2 | Niharika | Verma | 80000 | 2014-06-11 09:00:00 | Admin |
| 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR |
| 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin |
| 5 | Vivek | Bhati | 500000 | 2014-06-11 09:00:00 | Admin |
| 8 | Geetika | Chauhan | 90000 | 2014-04-11 09:00:00 | Admin |
+-----------+------------+-----------+--------+---------------------+------------+
6 rows in set (0.05 sec)
14.
mysql> Select * from Worker where FIRST_NAME in ('Vipul','Satish');
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 6 | Vipul | Diwan | 200000 | 2014-06-11 09:00:00 | Account |
| 7 | Satish | Kumar | 75000 | 2014-01-20 09:00:00 | Account |
+-----------+------------+-----------+--------+---------------------+------------+
2 rows in set (0.00 sec)
15.
mysql> select * from Worker where DEPARTMENT='Admin';
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 2 | Niharika | Verma | 80000 | 2014-06-11 09:00:00 | Admin |
| 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin |
| 5 | Vivek | Bhati | 500000 | 2014-06-11 09:00:00 | Admin |
| 8 | Geetika | Chauhan | 90000 | 2014-04-11 09:00:00 | Admin |
+-----------+------------+-----------+--------+---------------------+------------+
4 rows in set (0.00 sec)
16.
mysql> Select * from Worker where FIRST_NAME like '_i%';
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 2 | Niharika | Verma | 80000 | 2014-06-11 09:00:00 | Admin |
| 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR |
| 5 | Vivek | Bhati | 500000 | 2014-06-11 09:00:00 | Admin |
| 6 | Vipul | Diwan | 200000 | 2014-06-11 09:00:00 | Account |
+-----------+------------+-----------+--------+---------------------+------------+
4 rows in set (0.00 sec)
17.
mysql> Select * from Worker where FIRST_NAME like '%a' or FIRST_NAME like '%l';
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 1 | Monika | Arora | 100000 | 2014-02-20 09:00:00 | HR |
| 2 | Niharika | Verma | 80000 | 2014-06-11 09:00:00 | Admin |
| 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR |
| 6 | Vipul | Diwan | 200000 | 2014-06-11 09:00:00 | Account |
| 8 | Geetika | Chauhan | 90000 | 2014-04-11 09:00:00 | Admin |
+-----------+------------+-----------+--------+---------------------+------------+
5 rows in set (0.00 sec)
18.
mysql> Select * from Worker where FIRST_NAME like 'a%' or FIRST_NAME like 'b%' or FIRST_NAME like 'c%' or
FIRST_NAME like 'd%' or FIRST_NAME like 'e%' or FIRST_NAME like 'f%' or FIRST_NAME like 'g%' or FIRST_NAME
like 'h%' or FIRST_NAME like 'i%' or FIRST_NAME like 'j%' or FIRST_NAME like 'k%' or FIRST_NAME like 'l%' or
FIRST_NAME like 'm%';
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 1 | Monika | Arora | 100000 | 2014-02-20 09:00:00 | HR |
| 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin |
| 8 | Geetika | Chauhan | 90000 | 2014-04-11 09:00:00 | Admin |
+-----------+------------+-----------+--------+---------------------+------------+
3 rows in set (0.00 sec)
19.
mysql> select * from Worker where SALARY not in (100000,500000);
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 2 | Niharika | Verma | 80000 | 2014-06-11 09:00:00 | Admin |
| 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR |
| 6 | Vipul | Diwan | 200000 | 2014-06-11 09:00:00 | Account |
| 7 | Satish | Kumar | 75000 | 2014-01-20 09:00:00 | Account |
| 8 | Geetika | Chauhan | 90000 | 2014-04-11 09:00:00 | Admin |
+-----------+------------+-----------+--------+---------------------+------------+
5 rows in set (0.00 sec)
20.
mysql> select * from Worker where year(JOINING_DATE)=2014 and month(JOINING_DATE)=2;
+-----------+------------+-----------+--------+---------------------+------------+
| WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT |
+-----------+------------+-----------+--------+---------------------+------------+
| 1 | Monika | Arora | 100000 | 2014-02-20 09:00:00 | HR |
| 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR |
| 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin |
+-----------+------------+-----------+--------+---------------------+------------+
3 rows in set (0.04 sec)
21.
mysql> select count(DEPARTMENT) from Worker where DEPARTMENT='Admin' or DEPARTMENT='Account';
+-------------------+
| count(DEPARTMENT) |
+-------------------+
| 6|
+-------------------+
1 row in set (0.00 sec)
22.
mysql> select FIRST_NAME from Worker where SALARY between 50000 and 100000;
+------------+
| FIRST_NAME |
+------------+
| Monika |
| Niharika |
| Satish |
| Geetika |
+------------+
4 rows in set (0.00 sec)
23.
mysql> select DEPARTMENT,count(*) from Worker
-> group by DEPARTMENT
-> order by count(*);
+------------+----------+
| DEPARTMENT | count(*) |
+------------+----------+
| HR | 2|
| Account | 2|
24.
mysql> select WORKER_TITLE from Title where not WORKER_TITLE='Executive';
+---------------+
| WORKER_TITLE |
+---------------+
| Manager |
| Manager |
| Asst. Manager |
| Lead |
| Lead |
+---------------+
5 rows in set (0.00 sec)
| Admin | 4|
+------------+----------+
3 rows in set (0.00 sec)
25.
mysql> select count(JOINING_DATE) from Worker where year(JOINING_DATE)>2019 and date(JOINING_DATE)>14;
+---------------------+
| count(JOINING_DATE) |
+---------------------+
| 0|
+---------------------+
1 row in set (0.00 sec)
26.
mysql> select DEPARTMENT from Worker group by DEPARTMENT having count(*)>5;
Empty set (0.00 sec)
27.
mysql> select DEPARTMENT,count(*) from Worker
-> group by DEPARTMENT;
+------------+----------+
| DEPARTMENT | count(*) |
+------------+----------+
| HR | 2|
| Admin | 4|
| Account | 2|
+------------+----------+
3 rows in set (0.00 sec)
28.
mysql> select FIRST_NAME from Worker
-> where SALARY=(select max(SALARY)from Worker);
+------------+
| FIRST_NAME |
+------------+
| Amitabh |
| Vivek |
+------------+
2 rows in set (0.02 sec)
29.
mysql> select DEPARTMENT,sum(SALARY) from Worker group by DEPARTMENT;
+------------+-------------+
| DEPARTMENT | sum(SALARY) |
+------------+-------------+
| HR | 400000 |
| Admin | 1170000 |
| Account | 275000 |
+------------+-------------+
3 rows in set (0.01 sec)
30.
mysql> select max(Salary) from Worker where Salary<(select max(Salary) from Worker);
+-------------+
| max(Salary) |
+-------------+
| 300000 |
+-------------+
1 row in set (0.00 sec)
