Informatics Practices My SQL Practical file

1
 Informatics Practices My SQL Practical file

1. Create a student table with the student id, name, and marks as attributes where the student id is the
primary key.
create
database q;
use q;
create table students(student_id integer(4) primary key,name varchar(30),marks
integer(3)); desc students;

2. Insert the details of a new student in the above table.

insert into students values(1271,"Utkarsh
Madaan",82); insert into students
values(1324,"Naresh Sharma",89); insert into
students values(1325,"Md. Yusuf",66); insert
into students values(1328,"Sumedha",94);
insert into students values(1364,"Subya
Akhtar",78); insert into students
values(1434,"Varuna",56); insert into
students values(1461,"David DSouza",88);
insert into students values(2324,"Satinder
Singh",92); insert into students
values(2328,"Peter Jones",45); insert into
students values(2371,"Mohini Mehta",20);
select * from students;

3 Delete the details of a particular student in the above table.

delete from students where name='Varuna';
4 Use the select command to get the details of the students with marks more than 80.

2
 Informatics Practices My SQL Practical file

select * from students where marks>80;

5 Find the min, max, sum, and average of the marks in a student marks table.
select min(marks),max(marks),sum(marks),avg(marks) from students;

6. Find the total number of customers from each country in the table (customer ID, customer
Name, country) using group by.
select count(country),country from order2 group by country;

3
Informatics Practices My SQL Practical file

Reference Tables:
 EMP TABLE :

SELECT * FROM EMP;

+-------+-----------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | salary | comm | deptno |
+-------+-----------+-----------+------+------------+---------+---------+--------+
| 8369 | SMITH | CLERK | 8902 | 1990-12-18 | 800.00 | NULL | 20 |
| 8499 | ANYA | SALESMAN | 8698 | 1991-02-20 | 1600.00 | 300.00 | 30 |
| 8521 | SETH | SALESMAN | 8698 | 1991-02-22 | 1250.00 | 500.00 | 30 |
| 8566 | MAHADEVAN | MANAGER | 8839 | 1991-04-02 | 2985.00 | NULL | 20 |
| 8654 | MOMIN | SALESMAN | 8698 | 1991-09-28 | 1250.00 | 1400.00 | 30 |
| 8698 | BINA | MANAGER | 8839 | 1991-05-01 | 2850.00 | NULL | 30 |
| 8882 | SHIAVNSH | MANAGER | 8839 | 1991-06-09 | 2450.00 | NULL | 10 |
| 8888 | SCOTT | ANALYST | 8566 | 1992-12-09 | 3000.00 | NULL | 20 |
| 8839 | AMIR | PRESIDENT | NULL | 1991-11-18 | 5000.00 | NULL | 10 |
| 8844 | KULDEEP | SALESMAN | 8698 | 1991-09-08 | 1500.00 | 0.00 | 30 |
| 8886 | ANOOP | CLERK | 8888 | 1993-01-12 | 1100.00 | NULL | 20 |
| 8900 | JATIN | CLERK | 8698 | 1991-12-03 | 950.00 | NULL | 30 |
| 8902 | FAKIR | ANALYST | 8566 | 1991-12-03 | 3000.00 | NULL | 20 |
| 8934 | MITA | CLERK | 8882 | 1992-01-23 | 1300.00 | NULL | 10 |
+-------+-----------+-----------+------+------------+---------+---------+--------+

 DEPT TABLE :

SELECT * FROM DEPT;

+--------+------------+-----------+
| deptno | dname | loc |
+--------+------------+-----------+
| 10 | ACCOUNTING | NEW DELHI |
| 20 | RESEARCH | CHENNAI |
| 30 | SALES | KOLKATA |
| 40 | OPERATIONS | MUMBAI |
+--------+------------+-----------+

4
Informatics Practices My SQL Practical file

QUERY 1 : Write a query to display employee no., employee name and

commission who are getting no commission from ‘emp’ table.
mysql> SELECT EMPNO , ENAME , COMM
FROM EMP
WHERE COMM IS NULL OR COMM = 0 ;

Output:
+-------+-----------+------+
| EMPNO | ENAME | COMM |
+-------+-----------+------+
| 8369 | SMITH | NULL |
| 8566 | MAHADEVAN | NULL |
| 8698 | BINA | NULL |
| 8882 | SHIAVNSH | NULL |
| 8888 | SCOTT | NULL |
| 8839 | AMIR | NULL |
| 8844 | KULDEEP | 0.00 |
| 8886 | ANOOP | NULL |
| 8900 | JATIN | NULL |
| 8902 | FAKIR | NULL |
| 8934 | MITA | NULL |
+-------+-----------+------+
11 rows in set (0.00 sec)10 rows in set (0.00 sec)

QUERY 2 : Write a query to display employee name, employee no. and

hire date from ‘EMP’ table who have joined in year 1991.
mysql> SELECT ENAME , EMPNO , HIREDATE
FROM EMP
WHERE YEAR(HIREDATE) = 1991 ;

Output:
+-----------+-------+------------+
| ENAME | EMPNO | HIREDATE |
+-----------+-------+------------+
| ANYA | 8499 | 1991-02-20 |
| SETH | 8521 | 1991-02-22 |
| MAHADEVAN | 8566 | 1991-04-02 |
| MOMIN | 8654 | 1991-09-28 |
| BINA | 8698 | 1991-05-01 |
| SHIAVNSH | 8882 | 1991-06-09 |
| AMIR | 8839 | 1991-11-18 |
| KULDEEP | 8844 | 1991-09-08 |
| JATIN | 8900 | 1991-12-03 |
| FAKIR | 8902 | 1991-12-03 |
+-----------+-------+------------+
10 rows in set (0.00 sec)

5
Informatics Practices My SQL Practical file

QUERY 3: Write a query to display sum of salaries of employees of each

category of job along with the job name from ‘EMP’ table.
mysql> SELECT JOB , SUM(SALARY)
-> FROM EMP
-> GROUP BY JOB ;

Output:
+-----------+-------------+
| JOB | SUM(SALARY) |
+-----------+-------------+
| ANALYST | 6000.00 |
| CLERK | 4150.00 |
| MANAGER | 8285.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 5600.00 |
+-----------+-------------+
5 rows in set (0.00 sec)

QUERY 4: Write a query to display employee name, employee no.,

department no., job and salary of employee in ascending order
of department no. and in that name of job should be in
alphabetical order from ‘EMP’ table.
mysql> SELECT ENAME , EMPNO , DEPTNO , JOB , SALARY
-> FROM EMP
-> ORDER BY DEPTNO , JOB ;

Output:
+-----------+-------+--------+-----------+---------+
| ENAME | EMPNO | DEPTNO | JOB | SALARY |
+-----------+-------+--------+-----------+---------+
| MITA | 8934 | 10 | CLERK | 1300.00 |
| SHIAVNSH | 8882 | 10 | MANAGER | 2450.00 |
| AMIR | 8839 | 10 | PRESIDENT | 5000.00 |
| FAKIR | 8902 | 20 | ANALYST | 3000.00 |
| SCOTT | 8888 | 20 | ANALYST | 3000.00 |
| ANOOP | 8886 | 20 | CLERK | 1100.00 |
| SMITH | 8369 | 20 | CLERK | 800.00 |
| MAHADEVAN | 8566 | 20 | MANAGER | 2985.00 |
| JATIN | 8900 | 30 | CLERK | 950.00 |
| BINA | 8698 | 30 | MANAGER | 2850.00 |
| MOMIN | 8654 | 30 | SALESMAN | 1250.00 |
| SETH | 8521 | 30 | SALESMAN | 1250.00 |
| KULDEEP | 8844 | 30 | SALESMAN | 1500.00 |
| ANYA | 8499 | 30 | SALESMAN | 1600.00 |
+-----------+-------+--------+-----------+---------+
14 rows in set (0.00 sec)

6
Informatics Practices My SQL Practical file

QUERY 5 : Write a query to display minimum salary, maximum salary

and average salary from each job category from ‘EMP’ table.

mysql> SELECT JOB , MIN(SALARY) "MINIMUM SALARY", AVG(SALARY)"AVERAGE

SALARY", MAX(SALARY) "MAXIMUM SALARY"
-> FROM EMP
-> GROUP BY JOB ;

Output:
+-----------+----------------+----------------+----------------+
| JOB | MINIMUM SALARY | AVERAGE SALARY | MAXIMUM SALARY |
+-----------+----------------+----------------+----------------+
| ANALYST | 3000.00 | 3000.000000 | 3000.00 |
| CLERK | 800.00 | 1037.500000 | 1300.00 |
| MANAGER | 2450.00 | 2761.666667 | 2985.00 |
| PRESIDENT | 5000.00 | 5000.000000 | 5000.00 |
| SALESMAN | 1250.00 | 1400.000000 | 1600.00 |
+-----------+----------------+----------------+----------------+
5 rows in set (0.00 sec)

QUERY 6 : Write a query to display no. of employee in each department

along with the department name from ‘EMP’ table.
mysql> SELECT DEPTNO , COUNT(EMPNO) "NO. OF EMPLOYEE"
-> FROM EMP
-> GROUP BY DEPTNO ;
Output:
+--------+-----------------+
| DEPTNO | NO. OF EMPLOYEE |
+--------+-----------------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
+--------+-----------------+
3 rows in set (0.00 sec)

7
Informatics Practices My SQL Practical file

QUERY 7 : Write a query to display no. of employee doing a particular job

in each department along with department no. and job, where
this no. is greater than 1 from ‘EMP’ table.
mysql> SELECT DEPTNO , JOB , COUNT(EMPNO)"NO. OF EMPLOYEE"
-> FROM EMP
-> GROUP BY DEPTNO , JOB
-> HAVING COUNT(EMPNO) > 1 ;

Output:
+--------+----------+-----------------+
| DEPTNO | JOB | NO. OF EMPLOYEE |
+--------+----------+-----------------+
| 20 | ANALYST | 2 |
| 20 | CLERK | 2 |
| 30 | SALESMAN | 4 |
+--------+----------+-----------------+
3 rows in set (0.00 sec)

QUERY 8 : Write a query to display no. of category of jobs in each

department along with the department name from ‘EMP’ table.
mysql> SELECT DEPTNO , COUNT(DISTINCT JOB) "NO OF CATEGORY OF JOBS"
-> FROM EMP
-> GROUP BY DEPTNO ;

Output:
+--------+------------------------+
| DEPTNO | NO OF CATEGORY OF JOBS |
+--------+------------------------+
| 10 | 3 |
| 20 | 3 |
| 30 | 3 |
+--------+------------------------+
3 rows in set (0.00 sec)

8
Informatics Practices My SQL Practical file

QUERY 9 : Write a query to display employee no., employee name, job,

manager, hiredate, salary, commission and department no.,
where first letter of the employee name is a vowel from
‘EMP’ table.
mysql> SELECT * FROM EMP
-> WHERE SUBSTRING(ENAME,1,1) IN('A','E','I','O','U') ;

Output:
+-------+-------+-----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+--------+--------+
| 8499 | ANYA | SALESMAN | 8698 | 1991-02-20 | 1600.00 | 300.00 | 30 |
| 8839 | AMIR | PRESIDENT | NULL | 1991-11-18 | 5000.00 | NULL | 10 |
| 8886 | ANOOP | CLERK | 8888 | 1993-01-12 | 1100.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)

QUERY 10 : Write a query to display the following:

<SID> , <First Name> + <Last Name> AS “Full Name” and
<Salary> from ‘customer’ table.
mysql> SELECT SID , CONCAT(FIRST_NAME,' ',LAST_NAME) AS FULL_NAME , SALARY
-> FROM CUSTOMER ;

Output:
+-----+-------------------+----------+
| SID | Full_Name | Salary |
+-----+-------------------+----------+
| 1 | Luv Tomer | 20000.00 |
| 2 | Aakash Dhaka | 20000.00 |
| 3 | Sushant Agnihotri | 25000.00 |
| 4 | Saurav Umre | 25000.00 |
| 5 | Ashish Saini | 30000.00 |
+-----+-------------------+----------+
5 rows in set (0.00 sec)

9
Informatics Practices My SQL Practical file

QUERY 11: Write a query to display date after 6 months the date of hire
along with the date of hire from ‘EMP’ table.
mysql> SELECT HIREDATE ,
ADDDATE(HIREDATE , INTERVAL 6 MONTH) AS "HIREDATE + 6 MONTHS"
FROM EMP ;

Output:
+------------+---------------------+
| HIREDATE | HIREDATE + 6 MONTHS |
+------------+---------------------+
| 1990-12-18 | 1991-06-18 |
| 1991-02-20 | 1991-08-20 |
| 1991-02-22 | 1991-08-22 |
| 1991-04-02 | 1991-10-02 |
| 1991-09-28 | 1992-03-28 |
| 1991-05-01 | 1991-11-01 |
| 1991-06-09 | 1991-12-09 |
| 1992-12-09 | 1993-06-09 |
| 1991-11-18 | 1992-05-18 |
| 1991-09-08 | 1992-03-08 |
| 1993-01-12 | 1993-07-12 |
| 1991-12-03 | 1992-06-03 |
| 1991-12-03 | 1992-06-03 |
| 1992-01-23 | 1992-07-23 |
+------------+---------------------+
14 rows in set (0.00 sec)

10
Informatics Practices My SQL Practical file

QUERY 12 : Write a query to display employee no., employee name, job,

salary in alphabetical order of employee name from ‘EMP’
table.
mysql> SELECT EMPNO , ENAME , JOB , SAL
-> FROM EMP
-> ORDER BY ENAME ;

Output:
+-------+-----------+-----------+---------+
| EMPNO | ENAME | JOB | SAL |
+-------+-----------+-----------+---------+
| 8839 | AMIR | PRESIDENT | 5000.00 |
| 8886 | ANOOP | CLERK | 1100.00 |
| 8499 | ANYA | SALESMAN | 1600.00 |
| 8698 | BINA | MANAGER | 2850.00 |
| 8902 | FAKIR | ANALYST | 3000.00 |
| 8900 | JATIN | CLERK | 950.00 |
| 8844 | KULDEEP | SALESMAN | 1500.00 |
| 8566 | MAHADEVAN | MANAGER | 2985.00 |
| 8934 | MITA | CLERK | 1300.00 |
| 8654 | MOMIN | SALESMAN | 1250.00 |
| 8888 | SCOTT | ANALYST | 3000.00 |
| 8521 | SETH | SALESMAN | 1250.00 |
| 8882 | SHIAVNSH | MANAGER | 2450.00 |
| 8369 | SMITH | CLERK | 800.00 |
+-------+-----------+-----------+---------+
14 rows in set (0.02 sec)

11
Informatics Practices My SQL Practical file

QUERY 13 : Write a query to display employee name whose name starts

with ‘A’,’M’,’N’,’S’,’Z’ in alphabetical order of names from
‘EMP’ table.
mysql> SELECT ENAME FROM EMP
-> WHERE LEFT(ENAME , 1) IN('A','M','N','S','Z')
-> ORDER BY ENAME ;

Output:
+-----------+
| ENAME |
+-----------+
| AMIR |
| ANOOP |
| ANYA |
| MAHADEVAN |
| MITA |
| MOMIN |
| SCOTT |
| SETH |
| SHIAVNSH |
| SMITH |
+-----------+
10 rows in set (0.00 sec)

QUERY 14 : Write a query to display all the employee names which

contain letter ‘A’ or letter ‘P’ from ‘EMP’ table.
mysql> SELECT ENAME FROM EMP
-> WHERE ENAME LIKE "%A%" || ENAME LIKE "%P%" ;

Output:
+-----------+
| ENAME |
+-----------+
| ANYA |
| MAHADEVAN |
| BINA |
| SHIAVNSH |
| AMIR |
| KULDEEP |
| ANOOP |
| JATIN |
| FAKIR |
| MITA |
+-----------+
10 rows in set (0.00 sec)

12
Informatics Practices My SQL Practical file

QUERY 15 : Write a query to display total salary of all the employees, if

salary of each employee is increased by Rs. 500 along with the
previous total salary from ‘EMP’ table.
mysql> SELECT SUM(SAL) AS "TOTAL SALARY(PREVIOUS)" , SUM(SAL + 500 ) AS
"TOTAL SALARY(MODIFIED)" FROM EMP ;

Output:
+------------------------+------------------------+
| TOTAL SALARY(PREVIOUS) | TOTAL SALARY(MODIFIED) |
+------------------------+------------------------+
| 29035.00 | 36035.00 |
+------------------------+------------------------+
1 row in set (0.00 sec)

13