SQL ASSIGNMENT

Q1. Write a query to display all column structures and all row records from the Emp table.

Q2. Write a query to select only the employee numbers (EMPNO), names (ENAME), and
monthly salaries (SALARY) from the Emp table.

Q3. Fetch the employee name (ENAME) and their designated job profile (JOB) for all
employees.

Q4. Display the employee number, hire date (HIREDATE), and department number (DEPTNO)
for every record inside the database.

Q5. Write a query to display the employee name along with their monthly salary increased by a
flat amount of 500.

Q6. Display each employee's name alongside their monthly salary reduced by 200.

Q7. Calculate the annual salary for each employee (monthly salary multiplied by 12) and display
it alongside the employee's name.

Q8. Fetch the employee name and their daily salary rate, assuming a standard cycle of 30
working days in a month.

Q9. Display the name of each employee alongside their calculated weekly salary rate, assuming
exactly 4 working weeks in a month.

Q10. Write a query to list the employee names and their jobs, renaming the column headers to
"Staff Name" and "Designation" using column aliasing.

Q11. Display the employee name and their annual salary, labeling the calculated column with
the custom alias "Yearly_Pay".

Q12. Calculate a 10% performance bonus on each employee's monthly salary and display it
labeled as "Bonus_Amount" alongside their ENAME.

Q13. Write a query to display the employee number, name, and a column showing their salary
multiplied by 1.5, labeled as "Revised_Salary".

Q14. Display the employee name, monthly salary, and department number for all workers
belonging specifically to Department 10.

Q15. Write a query to list the names and jobs of employees who earn a monthly salary strictly
greater than 2500.

Q16. Find all employee details for those whose monthly salary is less than or equal to 1300.

Q17. Fetch the employee numbers, names, and job profiles of all workers who operate under
the exact designation of 'MANAGER'.
Q18. List the employee numbers and names of anyone whose assigned department number
equals 30.

Q19. Write a query to display the details of all employees who do not work in Department 20.

Q20. Find all employee details for those whose employee number is exactly 7566.

Q21. Display the name and job designation of any employee whose monthly salary is exactly
3000.

Q22. Write a query to find the name and hire date of any employee who holds the exact
designation of 'PRESIDENT'.

Q23. Display all details of employees who work in Department 20 and hold the exact job title of
an 'ANALYST'.

Q24. Write a query to find all employees who earn a monthly salary greater than 2000 and work
as a 'MANAGER'.

Q25. Display the details of employees who work in Department 10 or work in Department 20.

Q26. List the details of all employees who work as either a 'CLERK' or a 'SALESMAN'.

Q27. Write a query to display the details of all employees except those who hold the job
designation of 'ANALYST'.

Q28. Write a query using the NOT operator to display all employees who are not assigned to
Department 30.

Q29. Display the details of employees who work in Department 30 and simultaneously earn a
monthly salary greater than 1500.

Q30. Display all employees who work as a 'CLERK' in Department 20.

Q31. Find the name, job, and salary of any employee who is a 'SALESMAN' and earns exactly
1250.

Q32. Display the details of employees who either earn less than 1000 or earn more than 4000.

Q33. Display all employee names and their salaries, sorted by name in alphabetical ascending
order.

Q34. Display all employee details sorted by their monthly salary in descending order, putting the
highest paying roles first.

Q35. Display the name, job, and department number of all employees, sorted by their job
designation in ascending order.

Q36. Display the details of all employees, sorted first by their department number in ascending
order, and then by their monthly salary in descending order.

Q37. Write a query to display all employee names and hire dates, sorted by their hire date from
the oldest employee to the most recent.
Q38. Fetch the employee name, job, and salary for those who earn more than 1500, sorted
alphabetically by their job role.

Q39. Display all employee names transformed completely into lowercase format.

Q40. Write a query to display the employee names transformed entirely into uppercase format.

Q41. Extract the first 3 characters from the beginning of each employee's name using the
substring function.

Q42. Calculate the daily salary for each employee (SALARY / 30) and use the CEIL function to
round the calculated daily rate up to the nearest whole integer.

Q43. Write a query to calculate the maximum salary, minimum salary, and average salary
across the entire Emp table.

Q44. Display the total number of employees working in the organization using the COUNT
function.

Q45. Write a query to display the department number and the total sum of monthly salaries paid
to each department.

Q46. Display each department number along with the total count of employees working in that
department.

Q47. Write a query to display each job designation (JOB) along with the average monthly salary
earned by employees in that job role.

Q48. Display the department number and the maximum salary earned in each department, but
only for departments where the maximum salary is greater than 2500.

Q49. Write a query to display each job role along with the count of employees in that role, but
only include job roles that have 2 or more employees.

Q50. Display the department number and average salary for all employees who are NOT
managers, grouped by department, including only those departments where the group average
salary exceeds 1200, sorted by average salary in descending order.