DBMS Practical B.Sc 4th Semester.

 DBMS Practical B.Sc 4th Semester.

1.Create database
CREATE DTABASE Lab_4thsem

2. To use database
USE Lab_4thsem

3. To create the department table
CREATE TABLE department(
    -> Dno int PRIMARY KEY,
    -> Dname varchar(50),
    -> location varchar(50) default 'New Delhi');

4.To create the employee table
CREATE TABLE employee(
    -> Eno char(3) PRIMARY KEY,
    -> Ename varchar(50) NOT NULL,
    -> Job_type varchar(50) NOT NULL,
    -> Manager char(3),
    -> Hire_date date NOT NULL,
    -> Dno int,
    -> Commission decimal(10,2),
    -> Salary decimal(7,2) NOT NULL,
    -> FOREIGN KEY(Dno) REFERENCES department(Dno));

5. To add foriegn key in Employee table on Manager attribute
ALTER TABLE employee ADD FOREIGN KEY(Manager) REFERENCES
    -> employee(Eno);

6. To insert data or records in department table
 INSERT INTO department VALUES
    -> (101,'Computer Science','Dalgaon'),
    -> (102,'Physics','Kharupetia'),
    -> (103,'Mathematics','Mangaldai'),
    -> (104,'Administration','Guwahati'),
    -> (105,'Finance','Dispur');

7. To insert data or record in employee table.
 INSERT INTO employee VALUES
    -> ('E01','Gautam Kumar','Data Scientist',NULL,'2020-10-03', 101, 700000.00,5000.00);

INSERT INTO employee VALUES
    -> ('E02','Bhabesh Paul','Manager',NULL,'2019-01-12', 104, 900000.00,6000.00),
    -> ('E03','Washim Akram','Database Administrator','E02','2020-02-12', 104, 600000.00,5000.00),
    -> ('E04','Ikbal Hassan','Programmer','E02','2021-04-15', 104, 600000.00,5500.00),
    -> ('E05','Kamal Uddin','Driver Programmer',NULL,'2022-12-15', 101, 800000.00,7500.00);

Answeer th following queries
*****************************
1. Query to display Employee Name, Job, Hire Date, Employee Number; for each
employee with the Employee Number appearing first.

SELECT Eno,Ename,Job_type,Hire_date FROM employee;

2. Query to display unique Jobs from the Employee Table.

select distinct job_type from employee;

3. Query to display the Employee Name concatenated by a Job separated by a comma.

SELECT concat(ename,', ',job_type) AS Name_Job from employee;

4. Query to display all the data from the Employee Table. Separate each Column by a
comma and name the said column as THE_OUTPUT.

SELECT concat(eno,', ',ename,', ',job_type,', ',hire_date,', ',dno,', ',commission,', ',salary) AS THE_OUTPUT from employee;

5. Query to display the Employee Name and Salary of all the employees earning more
than $6000.

SELECT ename, salary from employee
    -> where salary>6000;

6. Query to display Employee Name and Department Number for the Employee No=
'E03'.

SELECT ename, dno from employee
    -> where eno='E03';

7. Query to display Employee Name and Salary for all employees whose salary is not in
the range of $5000 and $6000.

SELECT ename,salary from employee
    -> where salary not between 5000 and 6000;

8. Query to display Employee Name and Department No. of all the employees in Dept
101 and Dept 103 in the alphabetical order by name.

SELECT ename,dno from employee
    -> where dno=101 or dno=103
    -> ORDER BY ename;

9. Query to display Name and Hire Date of every Employee who was hired in 2021.

SELECT ename,hire_date from employee
    -> where year(hire_date)=2021;

10. Query to display Name and Job of all employees who don‘t have a current Manager.

select ename,job_type from employee
    -> where manager IS NULL;

11. Query to display the Name, Salary and Commission for all the employees who earn
commission.

SELECT ename,salary,commission from employee
    -> where commission is NOT NULL and commission>0;

12. Sort the data in descending order of Salary and Commission.
select * from employee
    -> order by salary desc, commission desc;
13. Query to display Name of all the employees where the third letter of their name is 'A'.
select ename from employee
    -> where ename like '_A%';
14. Query to display Name of all employees either have two 'R's or have two 'A's in
their name and are either in Dept No = 102 or their Manger‘s Employee No = 'E02'.
select ename from employee
    -> where (ename like '%R%R%' or ename like '%A%A%')
    -> and
    -> (dno=102 or manager='E02');
15. Query to display Name, Salary and Commission for all employees whose Commission
Amount is 100 times greater than their Salary increased by 5%.

update employee set salary=salary*1.05
    -> where commission>salary*100;


16. Query to display the Current Date.
select current_date();

17. Query to display Name, Hire Date and Salary Review Date which is the 1st Monday
after six months of employment.

18. Query to display Name and calculate the number of days between today and the
date each employee was hired.
select ename,datediff(current_date,hire_date) as days from employee;

19. Query to display the following for each employee
<EName> earns < Salary> monthly but wants < 3 * Current Salary >.
Label the Column as Dream Salary.
select concat(ename,' earns ',salary,' monthly but wants ',3*salary) as 'dream salary' from employee;