INFORMATICS PRACTICES NEW (065)

CLASS – XII
PRACTICAL FILE (PART 3)

Database Management

1. Write SQL query to create and show a database Test.

mysql> CREATE DATABASE TEST;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;

+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| loans |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)

2. Write SQL query to open database Test.

mysql> USE TEST;
Database changed

3. Write SQL query to create following Table name emp and dept

mysql> create table dept

-> (
-> deptno int(2) primary key,
-> dname varchar(14),
-> loc varchar(13)
-> );
Query OK, 0 rows affected, 1 warning (0.17 sec)

mysql> create table emp(

-> empno int(4) primary key,
-> ename varchar(8),
-> job varchar(9),
-> mgr int(4),
-> hiredate date,
-> sal float(7,2),
-> comm float(7,2),
-> deptno int(2)
-> );
Query OK, 0 rows affected, 5 warnings (0.14 sec)

Page 1 of 18
4. Write SQL query to show the structure of tables emp and dept.
mysql> DESCRIBE EMP;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(8) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | int | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | float(7,2) | YES | | NULL | |
| comm | float(7,2) | YES | | NULL | |
| deptno | int | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> DESC DEPT;

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int | NO | PRI | NULL | |
| dname | varchar(14) | YES | | NULL | |
| loc | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5. Write SQL query to Insert records in emp and dept table as per the
question.

mysql> insert into DEPT

-> values(10, 'ACCOUNTING', 'NEW YORK'),
-> (20, 'RESEARCH', 'DALLAS'),
-> (30, 'SALES', 'CHICAGO'),
-> (40, 'OPERATIONS', 'BOSTON');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into emp

-> values
-> (7839, 'KING', 'PRESIDENT', null, '1981-11-17',5000,0, 10),
-> (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01',2850,null, 30),
-> (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09',2450,null, 10),
-> (7566, 'JONES', 'MANAGER', 7839, '1981-04-02',2975,null, 20),
-> (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13',3000,null, 20),
-> (7902, 'FORD', 'ANALYST', 7566, '1981-12-03',3000,null, 20),
-> (7369, 'SMITH', 'CLERK', 7902, '1980-12-17',800,0, 20),
-> (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20',1600,300, 30),
-> (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22',1250,500, 30),
-> (7654, 'MARTIN','SALESMAN', 7698, '1981-09-28',1250,1400, 30),
-> (7844, 'TURNER','SALESMAN', 7698, '1981-09-08',1500,0, 30),

Page 2 of 18
 -> (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13',1100,null, 20),
-> (7900, 'JAMES', 'CLERK', 7698, '1981-12-03',950, null, 30),
-> (7934, 'MILLER','CLERK', 7782, '1982-01-23',1300,null, 10);
Query OK, 14 rows affected (0.02 sec)
Records: 14 Duplicates: 0 Warnings: 0

6. Write SQL query to display all the records from table emp.
mysql> SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | 0.00 | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | 0.00 | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

7. Write SQL query to display all the records from table dept.
mysql> SELECT * FROM DEPT;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)

8. Write SQL query to display EmpNo and EName of all employees from the
table emp.
mysql> SELECT EMPNO, ENAME FROM EMP;
+-------+--------+
| EMPNO | ENAME |
+-------+--------+
| 7369 | SMITH |
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
Page 3 of 18
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+
14 rows in set (0.00 sec)

9. Write SQL query to display Ename, Sal, and Sal added with comm from table emp
mysql> SELECT ENAME, SAL, SAL+COMM FROM EMP;
+--------+---------+----------+
| ENAME | SAL | SAL+COMM |
+--------+---------+----------+
| SMITH | 800.00 | 800.00 |
| ALLEN | 1600.00 | 1900.00 |
| WARD | 1250.00 | 1750.00 |
| JONES | 2975.00 | NULL |
| MARTIN | 1250.00 | 2650.00 |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | 5000.00 |
| TURNER | 1500.00 | 1500.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+----------+
14 rows in set (0.00 sec)

10. Write SQL query to display Ename, Sal, and deptno who are not getting commission from table emp.
mysql> SELECT ENAME, SAL, DEPTNO FROM EMP WHERE COMM IS NULL;
+--------+---------+--------+
| ENAME | SAL | DEPTNO |
+--------+---------+--------+
| JONES | 2975.00 | 20 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
8 rows in set (0.00 sec)

Page 4 of 18
11. Write SQL query to display Ename, Sal, and deptno who are getting commission from table emp.
mysql> SELECT ENAME, SAL, DEPTNO FROM EMP WHERE COMM IS NOT NULL;
+--------+---------+--------+
| ENAME | SAL | DEPTNO |
+--------+---------+--------+
| SMITH | 800.00 | 20 |
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| MARTIN | 1250.00 | 30 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
+--------+---------+--------+
6 rows in set (0.00 sec)
12. Write SQL query to display Eno, Ename ,Sal, and Annual Salary from table emp. (Annual Salary = Sal*12).
mysql> SELECT EMPNO, ENAME, SAL, SAL*12 AS "ANNUAL SALARY" FROM EMP;
+-------+--------+---------+---------------+
| EMPNO | ENAME | SAL | ANNUAL SALARY |
+-------+--------+---------+---------------+
| 7369 | SMITH | 800.00 | 9600.00 |
| 7499 | ALLEN | 1600.00 | 19200.00 |
| 7521 | WARD | 1250.00 | 15000.00 |
| 7566 | JONES | 2975.00 | 35700.00 |
| 7654 | MARTIN | 1250.00 | 15000.00 |
| 7698 | BLAKE | 2850.00 | 34200.00 |
| 7782 | CLARK | 2450.00 | 29400.00 |
| 7788 | SCOTT | 3000.00 | 36000.00 |
| 7839 | KING | 5000.00 | 60000.00 |
| 7844 | TURNER | 1500.00 | 18000.00 |
| 7876 | ADAMS | 1100.00 | 13200.00 |
| 7900 | JAMES | 950.00 | 11400.00 |
| 7902 | FORD | 3000.00 | 36000.00 |
| 7934 | MILLER | 1300.00 | 15600.00 |
+-------+--------+---------+---------------+
14 rows in set (0.00 sec)
13. Write SQL query to display employee no, name, job in ascending order of name.
mysql> SELECT EMPNO, ENAME, JOB FROM EMP ORDER BY ENAME;
+-------+--------+-----------+
| EMPNO | ENAME | JOB |
+-------+--------+-----------+
| 7876 | ADAMS | CLERK |
| 7499 | ALLEN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7902 | FORD | ANALYST |
| 7900 | JAMES | CLERK |
| 7566 | JONES | MANAGER |
| 7839 | KING | PRESIDENT |
| 7654 | MARTIN | SALESMAN |
| 7934 | MILLER | CLERK |
| 7788 | SCOTT | ANALYST |
| 7369 | SMITH | CLERK |
| 7844 | TURNER | SALESMAN |
| 7521 | WARD | SALESMAN |
+-------+--------+-----------+
14 rows in set (0.00 sec)
Page 5 of 18
14. Write SQL query to display employee name, salary in descending order of salary.
mysql> SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC;
+--------+---------+
| ENAME | SAL |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)

15. Write SQL query to display employee no, name, job in ascending order of job and name.
mysql> SELECT EMPNO, ENAME, JOB FROM EMP ORDER BY JOB, ENAME;
+-------+--------+-----------+
| EMPNO | ENAME | JOB |
+-------+--------+-----------+
| 7902 | FORD | ANALYST |
| 7788 | SCOTT | ANALYST |
| 7876 | ADAMS | CLERK |
| 7900 | JAMES | CLERK |
| 7934 | MILLER | CLERK |
| 7369 | SMITH | CLERK |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7566 | JONES | MANAGER |
| 7839 | KING | PRESIDENT |
| 7499 | ALLEN | SALESMAN |
| 7654 | MARTIN | SALESMAN |
| 7844 | TURNER | SALESMAN |
| 7521 | WARD | SALESMAN |
+-------+--------+-----------+
14 rows in set (0.00 sec)

16. Write SQL query to display employee no, name, job in descending order of job and ascending order of name.
mysql> SELECT EMPNO, ENAME, JOB FROM EMP ORDER BY JOB DESC, ENAME ASC;
+-------+--------+-----------+
| EMPNO | ENAME | JOB |
+-------+--------+-----------+
| 7499 | ALLEN | SALESMAN |
| 7654 | MARTIN | SALESMAN |
| 7844 | TURNER | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7839 | KING | PRESIDENT |

Page 6 of 18
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7566 | JONES | MANAGER |
| 7876 | ADAMS | CLERK |
| 7900 | JAMES | CLERK |
| 7934 | MILLER | CLERK |
| 7369 | SMITH | CLERK |
| 7902 | FORD | ANALYST |
| 7788 | SCOTT | ANALYST |
+-------+--------+-----------+
14 rows in set (0.00 sec)

17. Display the details of all employee whose annual salary is between 2000 to 4000
mysql> SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 4000;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)

18. Display the details of all employee those how were hired (date of joining) is year 1987.
mysql> SELECT * FROM EMP WHERE HIREDATE BETWEEN '1987-01-01' AND '1987-12-31';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)

19. Display the department numbers. Each department should be displayed once.
mysql> SELECT DISTINCT DEPTNO FROM EMP;
+--------+
| DEPTNO |
+--------+
| 20 |
| 30 |
| 10 |
+--------+
3 rows in set (0.00 sec)
20. Display name, job title and salary of employee who do not have manager.
mysql> SELECT ENAME, JOB, SAL FROM EMP WHERE MGR IS NULL;
+-------+-----------+---------+
| ENAME | JOB | SAL |
+-------+-----------+---------+
| KING | PRESIDENT | 5000.00 |
+-------+-----------+---------+
1 row in set (0.00 sec)

Page 7 of 18
21. Display name and salary of employee who draw salary more than 2500.

mysql> SELECT ENAME, SAL FROM EMP WHERE SAL>2500;

+-------+---------+
| ENAME | SAL |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
5 rows in set (0.00 sec)

22. Display name and salary of employee who draw salary more than 2500 and works in department no 20.

mysql> SELECT ENAME, SAL FROM EMP WHERE SAL>2500 AND DEPTNO=20;
+-------+---------+
| ENAME | SAL |
+-------+---------+
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
+-------+---------+
3 rows in set (0.00 sec)

23. Display name and hire date of employee who has joined before 16-Mar-1981.

mysql> SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE < "1981-03-16";
+-------+------------+
| ENAME | HIREDATE |
+-------+------------+
| SMITH | 1980-12-17 |
| ALLEN | 1981-02-20 |
| WARD | 1981-02-22 |
+-------+------------+
3 rows in set (0.00 sec)

24. Display name and job of employee those who are working as clerk or manager.

mysql> SELECT ENAME, JOB FROM EMP WHERE JOB='CLERK' OR JOB='MANAGER';

+--------+---------+
| ENAME | JOB |
+--------+---------+
| SMITH | CLERK |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ADAMS | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
+--------+---------+
7 rows in set (0.00 sec)
Page 8 of 18
25. Display name and job of employee those who are working as clerk or manager (using IN operator).
mysql> SELECT ENAME, JOB FROM EMP WHERE JOB IN ('CLERK','MANAGER');
+--------+---------+
| ENAME | JOB |
+--------+---------+
| SMITH | CLERK |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ADAMS | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
+--------+---------+
7 rows in set (0.00 sec)

26. Display the details of employees whose name have only four letters.
mysql> SELECT * FROM EMP WHERE ENAME LIKE "____";
+-------+-------+-----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+--------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | 0.00 | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)

27. Display the name of employee whose name contains “K” anywhere in name.
mysql> SELECT * FROM EMP WHERE ENAME LIKE "%k%";
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | 0.00 | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

28. Display the name of employee whose name contains “A” as third letter.
mysql> SELECT * FROM EMP WHERE ENAME LIKE "__A%";
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

29. Change salary of Smit to 1000.

mysql> UPDATE EMP
-> SET SAL = 1000
-> WHERE ENAME = 'SMITH';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Page 9 of 18
OR

mysql> UPDATE EMP

-> SET SAL = 1000
-> WHERE EMPNO = 7369;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT ENAME, SAL FROM EMP WHERE ENAME = 'SMITH';

+-------+---------+
| ENAME | SAL |
+-------+---------+
| SMITH | 1000.00 |
+-------+---------+
1 row in set (0.00 sec)

30. Increase the salary of all employee by 5%.

mysql> UPDATE EMP

-> SET SAL = SAL + SAL * 0.05;
Query OK, 14 rows affected (0.00 sec)
Rows matched: 14 Changed: 14 Warnings: 0

mysql> SELECT ENAME, SAL FROM EMP;

+--------+---------+
| ENAME | SAL |
+--------+---------+
| KING | 5250.00 |
| BLAKE | 2992.50 |
| CLARK | 2572.50 |
| JONES | 3123.75 |
| SCOTT | 3150.00 |
| FORD | 3150.00 |
| SMITH | 1050.00 |
| ALLEN | 1680.00 |
| WARD | 1312.50 |
| MARTIN | 1312.50 |
| TURNER | 1575.00 |
| ADAMS | 1155.00 |
| JAMES | 997.50 |
| MILLER | 1365.00 |
+--------+---------+
14 rows in set (0.00 sec)

31. Delete records whose commission is 0 (zero).

mysql> DELETE FROM EMP WHERE COMM=0;

Query OK, 3 rows affected (0.00 sec)

Page 10 of 18
mysql> SELECT * FROM EMP;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2992.50 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2572.50 | NULL | 10 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3123.75 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3150.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3150.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1680.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1312.50 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1312.50 | 1400.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1155.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 997.50 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1365.00 | NULL | 10 |
+-------+--------+----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)

32. Add new column Phno to emp table.

mysql> ALTER TABLE EMP

-> ADD PHNO VARCHAR(10);
Query OK, 11 rows affected, 1 warning (0.10 sec)
Records: 11 Duplicates: 0 Warnings: 1

mysql> DESC EMP;

+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(8) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | int | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | float(7,2) | YES | | NULL | |
| comm | float(7,2) | YES | | NULL | |
| deptno | int | YES | | NULL | |
| PHNO | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.01 sec)

33. Increase the size of data type of column Phno from 10 to 12 in emp table.

mysql> ALTER TABLE EMP

-> MODIFY PHNO VARCHAR(12);
Query OK, 11 rows affected (0.09 sec)
Records: 11 Duplicates: 0 Warnings: 0

Page 11 of 18
mysql> DESC EMP;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(8) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | int | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | float(7,2) | YES | | NULL | |
| comm | float(7,2) | YES | | NULL | |
| deptno | int | YES | | NULL | |
| PHNO | varchar(12) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

34. Delete the column Phno from emp table.

mysql> ALTER TABLE EMP

-> DROP PHNO;
Query OK, 11 rows affected (0.05 sec)
Records: 11 Duplicates: 0 Warnings: 0

mysql> DESC EMP;

+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(8) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | int | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | float(7,2) | YES | | NULL | |
| comm | float(7,2) | YES | | NULL | |
| deptno | int | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

35. Delete entire (data + structure) table.

mysql> DROP TABLE EMP;

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;

+----------------+
| Tables_in_test |
+----------------+
| dept |
+----------------+
1 row in set (0.01 sec)

Page 12 of 18
36. Find average salary of all employees in emp table.
mysql> SELECT AVG(SAL) FROM EMP;
+-------------+
| AVG(SAL) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

37. Find average, sum, minimum, maximum salary and count no of records of all employees in emp table.
mysql> SELECT AVG(SAL), SUM(SAL), MIN(SAL), MAX(SAL), COUNT(SAL) FROM EMP;
+-------------+----------+----------+----------+------------+
| AVG(SAL) | SUM(SAL) | MIN(SAL) | MAX(SAL) | COUNT(SAL) |
+-------------+----------+----------+----------+------------+
| 2073.214286 | 29025.00 | 800.00 | 5000.00 | 14 |
+-------------+----------+----------+----------+------------+
1 row in set (0.00 sec)

38. Count number of records of all employees in emp table.

mysql> SELECT COUNT(*) FROM EMP;
+----------+
| COUNT(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)

39. Count number of employees department wise from emp table.

mysql> SELECT DEPTNO, COUNT(*) FROM EMP
-> GROUP BY DEPTNO;
+--------+----------+
| DEPTNO | COUNT(*) |
+--------+----------+
| 10 | 3 |
| 30 | 6 |
| 20 | 5 |
+--------+----------+
3 rows in set (0.01 sec)

40. Display job / designation from emp table where number of employees is less than 3.
mysql> SELECT JOB, COUNT(*) FROM EMP
-> GROUP BY JOB
-> HAVING COUNT(*) < 3;
+-----------+----------+
| JOB | COUNT(*) |
+-----------+----------+
| PRESIDENT | 1 |
| ANALYST | 2 |
+-----------+----------+
2 rows in set (0.00 sec)

Page 13 of 18
41. Display department no, job / designation and number of employees in particular job group by department no,
job from emp table.
mysql> SELECT DEPTNO, JOB, COUNT(*) FROM EMP
-> GROUP BY DEPTNO, JOB;
+--------+-----------+----------+
| DEPTNO | JOB | COUNT(*) |
+--------+-----------+----------+
| 10 | PRESIDENT | 1 |
| 30 | MANAGER | 1 |
| 10 | MANAGER | 1 |
| 20 | MANAGER | 1 |
| 20 | ANALYST | 2 |
| 20 | CLERK | 2 |
| 30 | SALESMAN | 4 |
| 30 | CLERK | 1 |
| 10 | CLERK | 1 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

● Math functions: POWER (), ROUND (), MOD ().

mysql> SELECT POWER(2,5);
+------------+
| POWER(2,5) |
+------------+
| 32 |
+------------+
1 row in set (0.00 sec)
OR
mysql> SELECT POWER(2,5) FROM DUAL;
+------------+
| POWER(2,5) |
+------------+
| 32 |
+------------+

mysql> SELECT ROUND(2.15), ROUND(2.567), ROUND(2.567,2), ROUND(2.153,2);

+-------------+--------------+----------------+----------------+
| ROUND(2.15) | ROUND(2.567) | ROUND(2.567,2) | ROUND(2.153,2) |
+-------------+--------------+----------------+----------------+
| 2 | 3 | 2.57 | 2.15 |
+-------------+--------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT MOD(5,3), MOD(3,5);

+----------+----------+
| MOD(5,3) | MOD(3,5) |
+----------+----------+
| 2 | 3 |
+----------+----------+
1 row in set (0.00 sec)
Page 14 of 18
● Text functions: UCASE ()/UPPER (), LCASE ()/LOWER (), MID ()/SUBSTRING ()/SUBSTR (), LENGTH (), LEFT (),
RIGHT (), INSTR (), LTRIM (), RTRIM (), TRIM ().

mysql> SELECT UPPER('informatics practices'),UPPER('INFORMATICS PRACTICES');

+--------------------------------+--------------------------------+
| UPPER('informatics practices') | UPPER('INFORMATICS PRACTICES') |
+--------------------------------+--------------------------------+
| INFORMATICS PRACTICES | INFORMATICS PRACTICES |
+--------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LOWER('informatics practices'),LOWER('INFORMATICS PRACTICES');

+--------------------------------+--------------------------------+
| LOWER('informatics practices') | LOWER('INFORMATICS PRACTICES') |
+--------------------------------+--------------------------------+
| informatics practices | informatics practices |
+--------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MID("COMPUTER",4,3);

+---------------------+
| MID("COMPUTER",4,3) |
+---------------------+
| PUT |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MID("COMPUTER",4);

+-------------------+
| MID("COMPUTER",4) |
+-------------------+
| PUTER |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH("INFORMATICS PRACTICES");

+---------------------------------+
| LENGTH("INFORMATICS PRACTICES") |
+---------------------------------+
| 21 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LEFT("INFORMATICS PRACTICES",4);

+---------------------------------+
| LEFT("INFORMATICS PRACTICES",4) |
+---------------------------------+
| INFO |
+---------------------------------+
1 row in set (0.00 sec)

Page 15 of 18
mysql> SELECT RIGHT("INFORMATICS PRACTICES",5);
+----------------------------------+
| RIGHT("INFORMATICS PRACTICES",5) |
+----------------------------------+
| TICES |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT INSTR("INFORMATICS PRACTICES","FOR");

+--------------------------------------+
| INSTR("INFORMATICS PRACTICES","FOR") |
+--------------------------------------+
| 3 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT INSTR("INFORMATICS PRACTICES","TO");

+-------------------------------------+
| INSTR("INFORMATICS PRACTICES","TO") |
+-------------------------------------+
| 0 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LTRIM(" COMPUTER ");

+---------------------------+
| LTRIM(" COMPUTER ") |
+---------------------------+
| COMPUTER |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT RTRIM(" COMPUTER ");

+---------------------------+
| RTRIM(" COMPUTER ") |
+---------------------------+
| COMPUTER |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(" COMPUTER ");

+--------------------------+
| TRIM(" COMPUTER ") |
+--------------------------+
| COMPUTER |
+--------------------------+
1 row in set (0.00 sec)

Page 16 of 18
● Date Functions: NOW (), DATE (), MONTH (), MONTHNAME (), YEAR (), DAY (),

mysql> SELECT NOW();

+---------------------+
| NOW() |
+---------------------+
| 2022-03-07 [Link] |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE(NOW());

+-------------+
| DATE(NOW()) |
+-------------+
| 2022-03-07 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT DATE("2020-03-07");

+--------------------+
| DATE("2020-03-07") |
+--------------------+
| 2020-03-07 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT DAY("2020-03-07");

+-------------------+
| DAY("2020-03-07") |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTH("2020-03-07");

+---------------------+
| MONTH("2020-03-07") |
+---------------------+
| 3 |
+---------------------+
1 row in set (0.00 sec)
Page 17 of 18
mysql> SELECT YEAR("2020-03-07");

+--------------------+
| YEAR("2020-03-07") |
+--------------------+
| 2020 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME("2020-03-07");

+-------------------------+
| MONTHNAME("2020-03-07") |
+-------------------------+
| March |
+-------------------------+
1 row in set (0.00 sec)

Page 18 of 18