DBMS
PRACTICAL FILE
Department of Computer Science
Aryabhatta College
University of Delhi
Session: 2023-2024
Submitted To: Submitted By:
Mrs. NEHA NATH SHRUTI VERMA
Semester: 4
Rollno:21057590053
Creating database and tables :
Setting default values :
Populating vales :
Creating the employee table :
Adding foreign key :
Updating data :
Department Table After Inserting the Data:
Employee Table After Inserting the Data:
1. Query to display Employee Name, Job, Hire Date, Employee
Number; for each employee with the Employee Number
appearing first.
Q 6: Query to display Employee Name and Department
Number for the Employee No= 79.
Q7: Query to display Employee Name and Salary for all
employees whose salary is not in the
range of $1500 and $2850.
Q8:
Q9: Query to display Name and Hire Date of every Employee
who was hired in 1981.
Q10: . Query to display Name and Job of all employees who
have not assigned a supervisor
Date : 14Feb 2023
Q11: Query to display the Name, Salary and Commission for
all the employees who earn commission.
Q12: Sort the data in descending order of Salary and
Commission.
Q13: . Query to display Name of all the employees where the
third letter of their name is ‘A’.
Q14: 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 = 30 or their Manger’s Employee No
= 7788.
Q15 : Query to display Name, Salary and Commission for all
employees whose Commission
amount is greater than their Salary increased by 5%.
Q16 : Query to display the Current Date along with the day name.
Q17 : Query to display Name, Hire Date and Salary Review Date
which is the 1st Monday after six months of employment.
Q18: Query to display Name and calculate the number of months
between today and the date on which employee was hired of
department ‘Purchase’.
Q19: Query to display the following for each employee <E-Name>
earns < Salary> monthly
but wants < 3 * Current Salary >. Label the Column as Dream Salary.
(Assumption : Anyone having salary <1500 wants 3*salary)
20. Query to display Name with the 1st letter capitalized and all
other letter lower case and
length of their name of all the employees whose name starts with ‘J’,
’A’ and ‘M’.
Q21. Query to display Name, Hire Date and Day of the week on
which the employee started.
Q22. Query to display Name, Department Name and Department No
for all the employees.
Q23. Query to display Unique Listing of all Jobs that are in
Department number 30.
Q24. Query to display Name, Dept Name of all employees who have
an ‘A’ in their name.
Q25. Query to display Name, Job, Department No. And Department
Name for all the employees working at the Dallas location.
Q26. Query to display Name and Employee no. Along with their
supervisor’s Name and the supervisor’s employee no; along with the
Employees’ Name who do not have a supervisor.
Q27. Query to display Name, Dept No. And Salary of any employee
whose department No. and salary matches both the department no.
And the salary of any employee who earns acommission.
Q28. Query to display Name and Salaries represented by asterisks,
where each asterisk (*)
signifies $100.
Q29. Query to display the Highest, Lowest, Sum and Average Salaries
of all the employees.
Q30. Query to display the number of employees performing the
same Job type functions.
Q31. Querry to display total number of supervisors without listing
their names :
Q32.Querry to display the department names, location names , no.
of employees and the average salary of all the employees of that
department :111!!!!!!
Q33. Querry to display name and hire date of all the employees in
the same department as “Blake” :
Q34. Query to display the Employee no. and name for all employees
who earn more than the average salary :
Q35. Querry to display Employee number and name for all
employees who work in a department with any employee whose
name contains a “T” :
Q36:Query to display names and salaries of all the employees who
report to supervisor named “King” :
Q37 : Query to display the department no. name and job for all
employees in the sales department :
Q38 : Display names of employees along with their department
name who have more than 20 years of experience :
Q39 : Display total number of departments in each location :
Q40 : Find the department name in which at least 20 employees
work in :
Q41 :Querry to find the employee’s name who is not supervisor and
name of supervisor supervising more than 5 employees :
Q42 : Query to display the job type with maximum and minimum
employees :
