oracal pratical for BCA 2nd year
Question 1. create database ems.
Question 3. Create table emp and instert records
> CREATE TABLE emp (
dep_id INT ,
dep_name VARCHAR (50 ),
Location VARCHAR (50 ),
emp_id INT ,
Name VARCHAR (255 ),
Job VARCHAR (50 ),
ManagerID INT ,
Salary DECIMAL (10 , 2 ),
Commission DECIMAL (10 , 2 ),
HireDate DATE
);
INSERT INTO emp VALUES
(10 , ' IT' , ' Mumbai' , 101 , ' ram Kumar' , ' Software Engineer' , 201 , 13000 .00 , NULL , ' 2023-01-15' ),
(20 , ' IT' , ' Mumbai' , 102 , ' Priya Sharma' , ' Software Engineer' , 201 , 70000 .00 , NULL , ' 2001-02-20' ),
(30 , ' HR' , ' Delhi' , 103 , ' Rahul Singh' , ' HR Manager' , NULL , 80000 .00 , 5000 .00 , ' 2023-03-10' ),
(40 , ' HR' , ' Delhi' , 104 , ' Ananya Gupta' , ' Analyst' , 103 , 55000 .00 , NULL , ' 2023-04-05' ),
(50 , ' Finance' , ' Bangalore' , 105 , ' Rajesh Patel' , ' Manager' , 301 , 80000 .00 , 3000 .00 , ' 2023-05-12' ),
(60 , ' Marketing' , ' Chennai' , 106 , ' Neha Kapoor' , ' Manager' , 302 , 60000 .00 , 2000 .00 , ' 2023-06-25' ),
(70 , ' Admin' , ' Pune' , 107 , ' Vikram Joshi' , ' Clerk' , 7698 , 50000 .00 , NULL , ' 2023-07-10' );
> select * from emp;
+ -- ------------+----------------+-----------+------------+--------------+-------------------+-----------+----------+------------+------------+
| dep_id | dep_name | Location | emp_id | Name | Job | ManagerID | Salary | Commission | HireDate |
+ -- ------------+----------------+-----------+------------+--------------+-------------------+-----------+----------+------------+------------+
| 10 | IT | Mumbai | 101 | ram Kumar | Software Engineer | 201 | 13000 .00 | NULL | 2023 - 01 - 15 |
| 20 | IT | Mumbai | 102 | Priya Sharma | Software Engineer | 201 | 70000 .00 | NULL | 2001 - 02 - 20 |
| 30 | HR | Delhi | 103 | Rahul Singh | HR Manager | NULL | 80000 .00 | 5000 .00 | 2023 - 03 - 10 |
| 40 | HR | Delhi | 104 | Ananya Gupta | ANAYLST | 103 | 55000 .00 | NULL | 2023 - 04 - 05 |
| 50 | Finance | Bangalore | 105 | Rajesh Patel | Manager | 301 | 80000 .00 | 3000 .00 | 2023 - 05 - 12 |
| 60 | Marketing | Chennai | 106 | Neha Kapoor | Manager | 302 | 60000 .00 | 2000 .00 | 2023 - 06 - 25 |
| 70 | Admin | Pune | 107 | Vikram Joshi | Clerk | 7698 | 50000 .00 | NULL | 2023 - 07 - 10 |
+ -- ------------+----------------+-----------+------------+--------------+-------------------+-----------+----------+------------+------------+
Question 4.Display all the employees’ details that belong to department 10.
> SELECT * FROM emp WHERE dep_id = 10 ;
+ -- ------------+----------------+----------+------------+-----------+-------------------+-----------+----------+------------+------------+
| dep_id | dep_name | Location | emp_id | Name | Job | ManagerID | Salary | Commission | HireDate |
+ -- ------------+----------------+----------+------------+-----------+-------------------+-----------+----------+------------+------------+
| 10 | IT | Mumbai | 101 | ram Kumar | Software Engineer | 201 | 13000 .00 | NULL | 2023 - 01 - 15 |
+ -- ------------+----------------+----------+------------+-----------+-------------------+-----------+----------+------------+------------+
1 row in set (0 .001 sec)
Question 5.Display employees name along with their Salary who are MANAGER.
> SELECT Name, Salary FROM emp WHERE job = ' MANAGER' ;
+ -- ------------+----------+
| Name | Salary |
+ -- ------------+----------+
| Rajesh Patel | 80000 .00 |
| Neha Kapoor | 60000 .00 |
+ -- ------------+----------+
2 rows in set (0 .001 sec)AND manager_id = 7698 ;
Question 6. Display the employees who are getting Salary between 10000 and 25000.
> SELECT name,Salary FROM emp WHERE Salary BETWEEN 12000 AND 25000 ;
+ -- ---------+----------+
| name | Salary |
+ -- ---------+----------+
| ram Kumar | 13000 .00 |
+ -- ---------+----------+
1 row in set (0 .000 sec)
Question 7. Display the annual Salary of employees of dept. 30.
> SELECT Name, Salary * 12 AS annual_salary FROM emp WHERE dep_id = 30 ;
+ -- -----------+---------------+
| Name | annual_salary |
+ -- -----------+---------------+
| Rahul Singh | 960000 .00 |
+ -- -----------+---------------+
Question 8. Display employees that are CLERK and managed by 7698.
> SELECT Name , ManagerID , Job FROM emp WHERE job = ' CLERK' AND ManagerID = 7698 ;
+ -- ------------+-----------+-------+
| Name | ManagerID | Job |
+ -- ------------+-----------+-------+
| Vikram Joshi | 7698 | Clerk |
+ -- ------------+-----------+-------+
1 row in set (0 .001 sec)
Question 9. Display employees of department 10 and 20.
> SELECT Name , dep_id FROM emp WHERE dep_id = 10 OR dep_id = 20 ;
+ -- ------------+--------+
| Name | dep_id |
+ -- ------------+--------+
| ram Kumar | 10 |
| Priya Sharma | 20 |
+ -- ------------+--------+
2 rows in set (0 .001 sec)
Question 10. Display employees that are not managers.
> SELECT emp_id, Name ,Job FROM emp WHERE Job <> ' MANAGER' ;
+ -- ------+--------------+-------------------+
| emp_id | Name | Job |
+ -- ------+--------------+-------------------+
| 101 | ram Kumar | Software Engineer |
| 102 | Priya Sharma | Software Engineer |
| 103 | Rahul Singh | HR Manager |
| 104 | Ananya Gupta | Analyst |
| 107 | Vikram Joshi | Clerk |
+ -- ------+--------------+-------------------+
Question 11. Display employees whose name begins with Character ‘R’.
> SELECT Name FROM emp WHERE Name LIKE ' R%' ;
+ -- ------------+
| Name |
+ -- ------------+
| ram Kumar |
| Rahul Singh |
| Rajesh Patel |
+ -- ------------+
Question 12. Display employees that are analyst but getting salary greater than 10000.
> SELECT Name,Salary FROM emp WHERE Job = ' analyst' AND Salary > 10000 .00 ;
+ -- ------------+----------+
| Name | Salary |
+ -- ------------+----------+
| Ananya Gupta | 55000 .00 |
+ -- ------------+----------+
Question 13. Display employees those are not getting any commission.
> SELECT Name , Commission FROM emp WHERE Commission IS NULL ;
+ -- ------------+------------+
| Name | Commission |
+ -- ------------+------------+
| ram Kumar | NULL |
| Priya Sharma | NULL |
| Ananya Gupta | NULL |
| Vikram Joshi | NULL |
+ -- ------------+------------+
Question 14. Display all the employees name along with their jobs.
> SELECT Name, Job
FROM emp;
+ -- ------------+-------------------+
| Name | Job |
+ -- ------------+-------------------+
| ram Kumar | Software Engineer |
| Priya Sharma | Software Engineer |
| Rahul Singh | HR Manager |
| Ananya Gupta | analyst |
| Rajesh Patel | Manager |
| Neha Kapoor | Manager |
| Vikram Joshi | Clerk |
+ -- ------------+-------------------+
Question 15. Display all the employees having ‘A’ in their names.
> SELECT Name FROM emp WHERE Name LIKE ' %A%' ;
+ -- ------------+
| Name |
+ -- ------------+
| ram Kumar |
| Priya Sharma |
| Rahul Singh |
| Ananya Gupta |
| Rajesh Patel |
| Neha Kapoor |
| Vikram Joshi |
+ -- ------------+
Question 16. Display all the employees having T and ‘R’ in their names.
> SELECT Name
FROM emp
WHERE Name LIKE ' %T%' AND Name LIKE ' %R%' ;
-- ------------+
| Name |
+ -- ------------+
| Rajesh Patel |
+ -- ------------+
Question 17. Display Department located in ‘pune’.
> SELECT dep_name , Location
FROM emp
WHERE Location = ' Pune' ;
+ -- --------+----------+
| dep_name | Location |
+ -- --------+----------+
| Admin | Pune |
+ -- --------+----------+
Question 18. Display all the employees who are not ‘SALESMAN’ or ‘CLERK’.
> SELECT Name , Job
FROM emp
WHERE Job NOT IN (' SALESMAN' , ' CLERK' );
+ -- ------------+-------------------+
| Name | Job |
+ -- ------------+-------------------+
| ram Kumar | Software Engineer |
| Priya Sharma | Software Engineer |
| Rahul Singh | HR Manager |
| Ananya Gupta | analyst |
| Rajesh Patel | Manager |
| Neha Kapoor | Manager |
+ -- ------------+-------------------+
Question 19. Display all the employees Names in lowercase.
> SELECT LOWER (Name) AS LowercaseName
FROM emp;
+ -- -------------+
| LowercaseName |
+ -- -------------+
| ram kumar |
| priya sharma |
| rahul singh |
| ananya gupta |
| rajesh patel |
| neha kapoor |
| vikram joshi |
+ -- -------------+
Question 20. Display all employees name with their length.
> SELECT Name, LENGTH(Name) AS NameLength
FROM emp;
+ -- ------------+------------+
| Name | NameLength |
+ -- ------------+------------+
| ram Kumar | 9 |
| Priya Sharma | 12 |
| Rahul Singh | 11 |
| Ananya Gupta | 12 |
| Rajesh Patel | 12 |
| Neha Kapoor | 11 |
| Vikram Joshi | 12 |
+ -- ------------+------------+
Question 21.> Write a query to calculate the length of time any employee has been with the company.
SELECT
emp_id,
Name,
DATEDIFF(CURDATE(), HireDate) AS DaysWithCompany
FROM
emp;
+ -- ------+--------------+-----------------+
| emp_id | Name | DaysWithCompany |
+ -- ------+--------------+-----------------+
| 101 | ram Kumar | 343 |
| 102 | Priya Sharma | 8342 |
| 103 | Rahul Singh | 289 |
| 104 | Ananya Gupta | 263 |
| 105 | Rajesh Patel | 226 |
| 106 | Neha Kapoor | 182 |
| 107 | Vikram Joshi | 167 |
+ -- ------+--------------+-----------------+
Question 22.> List the employee name and salary increased by 15% and expressed as a whole number.
> SELECT
Name,
ROUND(Salary * 1 .15 ) AS IncreasedSalary
FROM
emp;
+ -- ------------+-----------------+
| Name | IncreasedSalary |
+ -- ------------+-----------------+
| ram Kumar | 14950 |
| Priya Sharma | 80500 |
| Rahul Singh | 92000 |
| Ananya Gupta | 63250 |
| Rajesh Patel | 92000 |
| Neha Kapoor | 69000 |
| Vikram Joshi | 57500 |
+ -- ------------+-----------------+
Question 23 .> List all the employees who joined after ’01-jan-2000’ and before 18-aug-2005.
SELECT Name,HireDate,Dep_id,Salary FROM emp WHERE HireDate > ' 2000-01-01' AND HireDate < ' 2005-08-18' ;
+ -- ------------+------------+--------+----------+
| Name | HireDate | Dep_id | Salary |
+ -- ------------+------------+--------+----------+
| Priya Sharma | 2001 - 02 - 20 | 20 | 70000 .00 |
+ -- ------------+------------+--------+----------+
DO IT BERFOR THE NEXT QUESTION :
-- ADD MAX AND MIN SALARY COLUMN to the existing table and insert a single record for given question no __
ALTER TABLE emp
ADD COLUMN max_sal DECIMAL (10 , 2 ),
ADD COLUMN min_sal DECIMAL (10 , 2 );
-- updating records into max_sal and min_sal columns (FOR EMS BASE NOT )
UPDATE emp SET max_sal = 50000 .00 , min_sal = 12000 .00 WHERE emp_id = 101 ;
UPDATE emp SET max_sal = 80000 .00 , min_sal = 60000 .00 WHERE emp_id = 102 ;
UPDATE emp SET max_sal = 85000 .00 , min_sal = 75000 .00 WHERE emp_id = 103 ;
UPDATE emp SET max_sal = 75000 .00 , min_sal = 60000 .00 WHERE emp_id = 104 ;
UPDATE emp SET max_sal = 85000 .00 , min_sal = 80000 .00 WHERE emp_id = 105 ;
UPDATE emp SET max_sal = 70000 .00 , min_sal = 60000 .00 WHERE emp_id = 106 ;
UPDATE emp SET max_sal = 95000 .00 , min_sal = 45000 .00 WHERE emp_id = 107 ;
INSERT INTO emp VALUES (10 , ' IT' , ' Mumbai' , 108 , ' Smith' , ' Software Engineer' , 201 , 75000 .00 , NULL , ' 2023-08-15' , 80000 .00 , 70000 .00 );
select * from emp;
+ -- ------+-----------+-----------+--------+--------------+-------------------+-----------+----------+------------+------------+----------+----------+
| dep_id | dep_name | Location | emp_id | Name | Job | ManagerID | Salary | Commission | HireDate | max_sal | min_sal |
+ -- ------+-----------+-----------+--------+--------------+-------------------+-----------+----------+------------+------------+----------+----------+
| 10 | IT | Mumbai | 101 | ram Kumar | Software Engineer | 201 | 13000 .00 | NULL | 2023 - 01 - 15 | 50000 .00 | 12000 .00 |
| 20 | IT | Mumbai | 102 | Priya Sharma | Software Engineer | 201 | 70000 .00 | NULL | 2001 - 02 - 20 | 80000 .00 | 60000 .00 |
| 30 | HR | Delhi | 103 | Rahul Singh | HR Manager | NULL | 80000 .00 | 5000 .00 | 2023 - 03 - 10 | 85000 .00 | 75000 .00 |
| 40 | HR | Delhi | 104 | Ananya Gupta | Analyst | 103 | 55000 .00 | NULL | 2023 - 04 - 05 | 75000 .00 | 60000 .00 |
| 50 | Finance | Bangalore | 105 | Rajesh Patel | Manager | 301 | 80000 .00 | 3000 .00 | 2023 - 05 - 12 | 85000 .00 | 80000 .00 |
| 60 | Marketing | Chennai | 106 | Neha Kapoor | Manager | 302 | 60000 .00 | 2000 .00 | 2023 - 06 - 25 | 70000 .00 | 60000 .00 |
| 70 | Admin | Pune | 107 | Vikram Joshi | Clerk | 7698 | 50000 .00 | NULL | 2023 - 07 - 10 | 95000 .00 | 85000 .00 |
| 10 | IT | Mumbai | 108 | Smith | Software Engineer | 201 | 75000 .00 | NULL | 2023 - 08 - 15 | 80000 .00 | 70000 .00 |
+ -- ------+-----------+-----------+--------+--------------+-------------------+-----------+----------+------------+------------+----------+----------+
Question 24. > Display the difference between Highest and the lowest salary for each department.
que 25 > List all jobs for MANAGER and difference between Average and maximum salary.
SELECT
- > Job,
- > MAX (Salary) AS Max_Salary,
- > AVG (Salary) AS Avg_Salary,
- > AVG (Salary) - MAX (Salary) AS Salary_Difference
- > FROM
- > emp
- > WHERE
- > Job = ' MANAGER'
- > GROUP BY
- > Job;
+ -- -------+------------+--------------+-------------------+
| Job | Max_Salary | Avg_Salary | Salary_Difference |
+ -- -------+------------+--------------+-------------------+
| Manager | 80000 .00 | 70000 .000000 | - 10000 .000000 |
+ -- -------+------------+--------------+-------------------+
Question 26.> Display Minimum and Maximum salary for each job type.
SELECT
- > Job,
- > MIN (min_sal),
- > MAX (max_sal)
- > FROM
- > emp
- > GROUP BY
- > Job;
+ -- -----------------+--------------+--------------+
| Job | MIN (min_sal) | MAX (max_sal) |
+ -- -----------------+--------------+--------------+
| Analyst | 60000 .00 | 75000 .00 |
| Clerk | 85000 .00 | 95000 .00 |
| HR Manager | 75000 .00 | 85000 .00 |
| Manager | 60000 .00 | 85000 .00 |
| Software Engineer | 12000 .00 | 80000 .00 |
+ -- -----------------+--------------+--------------+
que 27.> Display employees who earn more than lowest salary of department 30.
SELECT
- > *
- > FROM
- > emp
- > WHERE
- > Salary > (SELECT MIN (min_sal) FROM emp WHERE dep_id = 30 );
+ -- ------+----------+-----------+--------+--------------+------------+-----------+----------+------------+------------+----------+----------+
| dep_id | dep_name | Location | emp_id | Name | Job | ManagerID | Salary | Commission | HireDate | max_sal | min_sal |
+ -- ------+----------+-----------+--------+--------------+------------+-----------+----------+------------+------------+----------+----------+
| 30 | HR | Delhi | 103 | Rahul Singh | HR Manager | NULL | 80000 .00 | 5000 .00 | 2023 - 03 - 10 | 85000 .00 | 75000 .00 |
| 50 | Finance | Bangalore | 105 | Rajesh Patel | Manager | 301 | 80000 .00 | 3000 .00 | 2023 - 05 - 12 | 85000 .00 | 80000 .00 |
+ -- ------+----------+-----------+--------+--------------+------------+-----------+----------+------------+------------+----------+----------+
2 rows in set (0 .000 sec)
que 28 > Display all the employees who do not manage anyone.
SELECT
- > Name , ManagerID
- > FROM
- > emp
- > WHERE
- > ManagerID IS NULL ;
+ -- -----------+-----------+
| Name | ManagerID |
+ -- -----------+-----------+
| Rahul Singh | NULL |
+ -- -----------+-----------+
que 29. > Find all the employees who have the same job as ‘RAM’.
SELECT
name ,Job
FROM
emp
WHERE
Job = (SELECT Job FROM emp WHERE Name = ' ram kumar' );
+ -- ------------+-------------------+
| name | Job |
+ -- ------------+-------------------+
| ram Kumar | Software Engineer |
| Priya Sharma | Software Engineer |
| Smith | Software Engineer |
+ -- ------------+-------------------+
que 30 > Display all the employees who working in same department on same post where SMITH is working.
SELECT
dep_id,
Name,
Job
FROM
emp
WHERE
dep_id = (SELECT dep_id FROM emp WHERE Name = ' Smith' )
AND Job = (SELECT Job FROM emp WHERE Name = ' Smith' );
+ -- ------+-----------+-------------------+
| dep_id | Name | Job |
+ -- ------+-----------+-------------------+
| 10 | ram Kumar | Software Engineer |
| 10 | Smith | Software Engineer |
+ -- ------+-----------+-------------------+