SQL Data Retrieval

Syllabus

SQL: Basic Retrieval Queries in SQL: The Select from where structure, Ambiguous Attribute Names and Aliasing, Use of asterisk, Tables as Sets and Pattern Matching, INSERT, DELETE and UPDATE statements in SQL; (6.3, 6.4)

Table: emp_bn

CREATE TABLE emp_bn (
    emp_id INT NOT NULL,
    ename VARCHAR(15),
    job_name VARCHAR(15),
    manager_id INT,
    hire_date DATE,
    salary DECIMAL(10,2),
    commission DECIMAL(7,2),
    dept_id INT,
    PRIMARY KEY (emp_id)
);

Table: dept_bn

CREATE TABLE dept_bn (
    dept_id INT NOT NULL,
    dept_name VARCHAR(20),
    dept_location VARCHAR(15),
    PRIMARY KEY (dept_id)
);

Table: salary_grade_bn

CREATE TABLE salary_grade_bn (
    grade INT NOT NULL,
    min_sal INT,
    max_sal INT,
    PRIMARY KEY (grade)
);

Foreign Key Constraint

ALTER TABLE emp_bn
ADD FOREIGN KEY (dept_id)
REFERENCES dept_bn(dept_id);

Inserting Values into Tables

Insert values into dept_bn

INSERT INTO dept_bn VALUES (1001, 'Finance', 'Sydney');
INSERT INTO dept_bn VALUES (2001, 'Audit', 'Melborne');
INSERT INTO dept_bn VALUES (3001, 'Marketing', 'Perth');
INSERT INTO dept_bn VALUES (4001, 'Production', 'Brisbane');

Insert values into salary_grade_bn

INSERT INTO salary_grade_bn VALUES (1, 800, 1300);
INSERT INTO salary_grade_bn VALUES (2, 1301, 1500);
INSERT INTO salary_grade_bn VALUES (3, 1501, 2100);
INSERT INTO salary_grade_bn VALUES (4, 2101, 3100);
INSERT INTO salary_grade_bn VALUES (5, 3101, 9999);

Insert values into emp_bn

INSERT INTO emp_bn VALUES (68319, 'Kayling', 'President', NULL, '1991-11-18', 6000.00, NULL, 1001);
INSERT INTO emp_bn VALUES (66928, 'Blaze', 'Manager', 68319, '1991-05-01', 2750.00, NULL, 3001);
INSERT INTO emp_bn VALUES (67832, 'Clare', 'Manager', 68319, '1991-06-09', 2550.00, NULL, 1001);
INSERT INTO emp_bn VALUES (65646, 'Jonas', 'Manager', 68319, '1991-04-02', 2957.00, NULL, 2001);
INSERT INTO emp_bn VALUES (67858, 'Scarlet', 'Analyst', 65646, '1997-04-19', 3100.00, NULL, 2001);
INSERT INTO emp_bn VALUES (69062, 'Frank', 'Analyst', 65646, '1991-12-03', 3100.00, NULL, 2001);
INSERT INTO emp_bn VALUES (63679, 'Sandrine', 'Clerk', 69062, '1990-12-18', 900.00, NULL, 2001);
INSERT INTO emp_bn VALUES (64989, 'Adelyn', 'Salesman', 66928, '1991-02-20', 1700.00, 400.00, 3001);
INSERT INTO emp_bn VALUES (65271, 'Wade', 'Salesman', 66928, '1991-02-22', 1350.00, 600.00, 3001);
INSERT INTO emp_bn VALUES (66564, 'Madden', 'Salesman', 66928, '1991-09-28', 1350.00, 1500.00, 3001);
INSERT INTO emp_bn VALUES (68454, 'Tucker', 'Salesman', 66928, '1991-09-08', 1600.00, 0.00, 3001);
INSERT INTO emp_bn VALUES (68736, 'Adnres', 'Clerk', 67858, '1997-05-23', 1200.00, NULL, 2001);
INSERT INTO emp_bn VALUES (69000, 'Julius', 'Clerk', 66928, '1991-12-03', 1050.00, NULL, 3001);
INSERT INTO emp_bn VALUES (69324, 'Marker', 'Clerk', 67832, '1992-01-23', 1400.00, NULL, 1001);

Data Inserted Tables

Table: dept_bn

dept_id	dept_name	dept_location
1001	Finance	Sydney
2001	Audit	Melborne
3001	Marketing	Perth
4001	Production	Brisbane

---

Table: salary_grade_bn

grade	min_sal	max_sal
1	800	1300
2	1301	1500
3	1501	2100
4	2101	3100
5	3101	9999

---

Table: emp_bn

emp_id	ename	job_name	manager_id	hire_date	salary	commission	dept_id
68319	Kayling	President	NULL	18-Nov-1991	6000.00	NULL	1001
66928	Blaze	Manager	68319	01-May-1991	2750.00	NULL	3001
67832	Clare	Manager	68319	09-Jun-1991	2550.00	NULL	1001
65646	Jonas	Manager	68319	02-Apr-1991	2957.00	NULL	2001
67858	Scarlet	Analyst	65646	19-Apr-1997	3100.00	NULL	2001
69062	Frank	Analyst	65646	03-Dec-1991	3100.00	NULL	2001
63679	Sandrine	Clerk	69062	18-Dec-1990	900.00	NULL	2001
64989	Adelyn	Salesman	66928	20-Feb-1991	1700.00	400.00	3001
65271	Wade	Salesman	66928	22-Feb-1991	1350.00	600.00	3001
66564	Madden	Salesman	66928	28-Sep-1991	1350.00	1500.00	3001
68454	Tucker	Salesman	66928	08-Sep-1991	1600.00	0.00	3001
68736	Adnres	Clerk	67858	23-May-1997	1200.00	NULL	2001
69000	Julius	Clerk	66928	03-Dec-1991	1050.00	NULL	3001
69324	Marker	Clerk	67832	23-Jan-1992	1400.00	NULL	1001

SQL: Practice Questions

1. Write a query in SQL to display all the information of the employees.

SELECT * FROM emp_bn;

2. Write a query in SQL to find the salaries of all employees.

SELECT salary FROM emp_bn;

3. Write a query in SQL to display the unique designations for the employees.

SELECT DISTINCT(job_name) 
	FROM emp_bn;

4. Write a query in SQL to list the emp name and salary is increased by 15% and expressed as no.of Dollars.

SELECT ename, to_char(1.15*salary, '$99,999') 
	AS "Salary" FROM emp_bn;

5. Write a query in SQL to produce the output of employees as follows: Employee JONAS(manager).

SELECT ename || '(' || lower(job_name) || ')' 
	AS "Employee" FROM emp_bn;

6. Write a query in SQL to list the employees with Hire date in the format like February 22, 1991.

SELECT emp_id, ename, salary, 
	to_char(hire_date, 'MONTH DD,YYYY')
	FROM emp_bn;

SELECT emp_id, ename, salary, 
	to_char(hire_date, 'MONTH DD,YYY')
	AS Hire_Date 
	FROM emp_bn;

7. Write a query in SQL to count the no. of characters without considering the spaces for each name.

SELECT length(trim(ename)) 
	FROM emp_bn;

8. Write a query in SQL to list the emp id, salary, and commission of all the employees.

SELECT emp_id, salary, commission 
	FROM emp_bn;

9. Write a query in SQL to display the unique department with jobs.

SELECT DISTINCT dept_id, job_name 
	FROM emp_bn;

10. Write a query in SQL to list the employees who does not belong to department 2001.

SELECT * FROM emp_bn 
	WHERE dept_id 
	NOT IN (2001);

11. Write a query in SQL to list the employees who joined before 1991.

SELECT * FROM emp_bn 
	WHERE hire_date < ('01-Jan-1991');

12. Write a query in SQL to display the average salaries of all the employees who works as ANALYST.

SELECT avg(salary) FROM emp_bn 
	WHERE job_name = 'Analyst';

13. Write a query in SQL to display the details of the employee BLAZE.

SELECT * FROM emp_bn 
	WHERE ename = 'Blaze';

14. Write a query in SQL to display all the details of the employees whose commission is more than their salary.

SELECT * FROM emp_bn 
	WHERE commission > salary;

15. Write a query in SQL to list the employees whose salary is more than 3000 after giving 25% increment.

SELECT * FROM emp_bn 
	WHERE (1.25 * salary) > 3000;

16. Write a query in SQL to list the name of the employees, those having six characters to their name.

SELECT ename FROM emp_bn 
	WHERE length(ename) = 6;

17. Write a query in SQL to list the employees who joined in the month January.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'Mon') = 'Jan';

18. Write a query in SQL to list the name of employees and their manager separated by the string 'works for'.

SELECT e.ename || ' works for ' || m.ename
	FROM emp_bn e, emp_bn m
	WHERE e.manager_id = m.emp_id;

19. Write a query in SQL to list all the employees whose designation is CLERK.

SELECT * FROM emp_bn 
	WHERE job_name = 'Clerk';

20. Write a query in SQL to list the employee name and his experience in days.

SELECT ename, floor(sysdate - hire_date) 
	as experience_in_days 
	FROM emp_bn;

21. Write a query in SQL to extract year, month and date separately from the column hire date.

SELECT EMP_ID, ENAME, 
	extract(YEAR from hire_date) 
	AS Year_of_Joining FROM EMP_bn;
	
SELECT EMP_ID, ENAME, 
	extract(MONTH from hire_date) 
	AS Month_of_Joining FROM EMP_bn;

SELECT EMP_ID, ENAME, 
	extract(DAY from hire_date) 
	AS Day_of_Joining FROM EMP_bn;

22. Write a query in SQL to list the employees whose salaries are less than 3500.

SELECT * FROM emp_bn 
	WHERE salary < 3500;

23. Write a query in SQL to list the name, job name, and salary of any employee whose designation is ANALYST.

SELECT ename, job_name, salary 
	FROM emp_bn 
	WHERE job_name = 'Analyst';

24. Write a query in SQL to list the employees who have joined in the year 1991.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'YYYY') = '1991';

25. Write a query in SQL to list the name, id, hire date, and salary of all the employees joined before 1 apr 91.

SELECT e.emp_id, e.ename, e.hire_date, e.salary 
	FROM emp_bn e WHERE hire_date < '01-Apr-1991';

26. Write a query in SQL to list the employee name, and job name who are not working under a manager.

SELECT e.ename, e.job_name 
	FROM emp_bn e 
	WHERE manager_id IS NULL;

27. Write a query in SQL to list all the employees joined on 1st may 91.

SELECT * FROM emp_bn 
	WHERE hire_date = '01-May-1991';

28. Write a query in SQL to list the id, name, salary, and experience of all the employees who earn more than 100 as daily salary.

SELECT emp_id, ename, salary, 
	(to_date(SYSDATE) - to_date(hire_date)) experience_in_days 
	FROM emp_bn WHERE (salary / 30) > 100;

29. Write a query in SQL to list those employees whose salary is an odd value.

SELECT * FROM emp_bn 
	WHERE mod(salary, 2) = 1;

30. Write a query in SQL to list those employees whose salary contain only 3 digits.

SELECT * FROM emp_bn 
	WHERE length(TRIM(TO_CHAR(salary, '9999'))) = 3;

31. Write a query in SQL to list the employees who joined in the month of APRIL.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'MON') = 'APR';

32. List the employees who are SALESMAN and gathered an experience which month portion is more than 10.

SELECT * FROM emp_bn 
	WHERE job_name = 'Salesman' 
	AND floor(months_between(sysdate, hire_date)) > 10;

33. Write a query in SQL to list the employees of department id 3001 or 1001 joined in the year 1991.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'YYYY') = '1991' 
	AND (dept_id = 3001 OR dept_id = 1001);

34. Write a query in SQL to list all the employees of designation CLERK in department no 2001.

SELECT * FROM emp_bn 
	WHERE job_name = 'Clerk' 
	AND dept_id = 2001;

35. Write a query in SQL to list the ID, name, salary, and job name of the employees for:

• Annual salary is below 34000 but receiving some commission which should not be more than the salary,

• And designation is SALESMAN and working for department 3001.

SELECT emp_id, ename, salary, job_name 
	FROM emp_bn 
	WHERE 12 * (salary + commission) < 34000 
	AND commission IS NOT NULL 
	AND commission < salary 
	AND job_name = 'Salesman' 
	AND dept_id = 3001;

36. Write a query in SQL to list the employees who are either CLERK or MANAGER.

SELECT * FROM emp_bn 
	WHERE job_name IN ('Clerk', 'Manager');

37. Write a query in SQL to list the employees who joined in any year except the month February.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'MON') NOT IN ('FEB');

38. Write a query in SQL to list the employees who joined in the year 91.

SELECT * FROM emp_bn 
	WHERE hire_date 
		BETWEEN '01-Jan-1991' 
			AND '31-Dec-1991';

39. Write a query in SQL to list the employees who joined in the month of June in 1991.

SELECT * FROM emp_bn 
	WHERE hire_date 
		BETWEEN '01-Jun-1991' 
			AND '30-Jun-1991';
-- OR
SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'Mon-yyyy') = 'Jun-1991';

40. Write a query in SQL to list the employees whose annual salary is within the range 24000 and 50000.

SELECT * FROM emp_bn 
	WHERE 12 * salary 
		BETWEEN 24000 AND 50000;

41. Write a query in SQL to list the employees who have joined on the following dates 1st May, 20th Feb, and 03rd Dec in the year 1991.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'DD-MON-YY') 
	IN ('01-MAY-91', '20-FEB-91', '03-DEC-91');

42. Write a query in SQL to list the employees working under the managers 63679, 68319, 66564, 69000.

SELECT * FROM emp_bn 
	WHERE manager_id 
		IN (63679, 68319, 66564, 69000);

43. Write a query in SQL to list the employees who joined after the month JUNE in the year 1991.

SELECT * FROM emp_bn 
	WHERE hire_date 
		BETWEEN '01-JUL-91' 
			AND '31-DEC-92';

44. Write a query in SQL to list the employees who joined in 90’s.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'YY') 
	BETWEEN '90' AND '99';

45. Write a query in SQL to list the managers of department 1001 or 2001.

SELECT * FROM emp_bn 
	WHERE job_name = 'Manager' 
	AND (dept_id = 1001 OR dept_id = 2001);

46. Write a query in SQL to list the employees, joined in the month FEBRUARY with a salary range between 1001 to 2000.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'MON') = 'FEB' 
	AND salary BETWEEN 1000 AND 2000;

47. Write a query in SQL to list all the employees who joined before or after 1991.

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'YYYY') 
	NOT IN ('1991');

-- OR

SELECT * FROM emp_bn 
	WHERE to_char(hire_date, 'YYYY') 
	NOT LIKE '1991';

48. Write a query in SQL to list the employees along with department name.

SELECT e.emp_id, e.ename, e.job_name, e.manager_id,
e.hire_date, e.salary, e.commission, e.dept_id, 
d.dept_name 
	FROM emp_bn e, dept_bn d 
	WHERE e.dept_id = d.dept_id;

49. Write a query in SQL to list the name, job name, annual salary, department id, department name and grade of the employees who earn 60000 in a year or not working as an ANALYST.

SELECT e.ename, e.job_name, (12 * e.salary) "Annual Salary", 
	e.dept_id, d.dept_name, s.grade 
	FROM emp_bn e, dept_bn d, salary_grade_bn s 
	WHERE e.dept_id = d.dept_id 
	AND e.salary BETWEEN s.min_sal AND s.max_sal 
	AND ((12 * e.salary) >= 60000 OR e.job_name != 'ANALYST');

50. Write a query in SQL to list the name, job name, manager id, salary, manager name, manager’s salary for those employees whose salary is greater than the salary of their managers.

SELECT w.ename, w.job_name, w.manager_id, w.salary,
	m.ename "Manager", m.emp_id, m.salary "Manager_Salary" 
	FROM emp_bn w, emp_bn m 
	WHERE w.manager_id = m.emp_id 
	AND w.salary > m.salary;

51. Write a query in SQL to list the employees name, department, salary and commission. For those whose salary is between 2000 and 5000 while location is PERTH.

SELECT e.ename, e.dept_id, e.salary, e.commission
	FROM emp_bn e, dept_bn d
	WHERE e.dept_id = d.dept_id
	AND d.dept_location = 'Perth'
	AND e.salary BETWEEN 2000 AND 5000;

52. Write a query in SQL to list the grade, employee name for the department id 1001 or 3001 but salary grade is not 4 while they joined the company before 1992-12-31.

SELECT s.grade, e.ename
	FROM emp_bn e, salary_grade_bn s
	WHERE e.dept_id IN (1001, 3001)
	AND hire_date < ('31-Dec-1992')
	AND (e.salary BETWEEN s.min_sal 
		AND s.max_sal 
		AND s.grade NOT IN (4));

53. Write a query in SQL to list the employees whose manager name is JONAS.

SELECT w.emp_id, w.ename, w.job_name, w.manager_id,
	w.hire_date, w.salary, w.dept_id, m.ename
	FROM emp_bn w, emp_bn m
	WHERE w.manager_id = m.emp_id 
		AND m.ename = 'Jonas';

54. Write a query in SQL to list the name and salary of FRANK if his salary is equal to max sal of his grade.

SELECT e.ename, e.salary
	FROM emp_bn e, salary_grade_bn s
	WHERE e.ename = 'Frank'
		AND e.salary BETWEEN s.min_sal 
		AND s.max_sal
		AND e.salary = s.max_sal;

55. Write a query in SQL to list the employees who are working either MANAGER or ANALYST with a salary range between 2000 to 5000 without any commission.

SELECT * FROM emp_bn
	WHERE job_name IN ('Manager', 'Analyst')
		AND salary BETWEEN 2000 
		AND 5000
		AND commission IS NULL;

56. Write a query in SQL to list the id, name, salary, and location of the employees working at PERTH, or MELBOURNE with an experience over 10 years.

SELECT e.emp_id, e.ename, e.dept_id, e.salary,
	d.dept_location
	FROM emp_bn e, dept_bn d
	WHERE e.dept_id = d.dept_id
		AND d.dept_location IN ('Perth', 'Melborne')
		AND (to_date(SYSDATE) - to_date(hire_date)) > 10;

57. Write a query in SQL to list the employees along with their location who belongs to SYDNEY, MELBOURNE with a salary range between 2000 and 5000 and joined in 1991.

SELECT e.emp_id, e.ename, e.dept_id, 
	e.salary, d.dept_location
	FROM emp_bn e, dept_bn d
		WHERE e.dept_id = d.dept_id
			AND d.dept_location IN ('Sydney', 'Melborne')
			AND to_char(e.hire_date, 'YY') = '91'
			AND e.salary BETWEEN 2000 AND 5000;

58. Write a query in SQL to list the employees with their location and grade for MARKETING department who comes from MELBOURNE or PERTH within the grade 3 to 5 and experience over 5 years.

SELECT e.dept_id, e.emp_id, e.ename, e.salary, 
	d.dept_name, d.dept_location, s.grade
	FROM emp_bn e, salary_grade_bn s, dept_bn d
	WHERE e.dept_id = d.dept_id
		AND e.salary BETWEEN s.min_sal AND s.max_sal
		AND s.grade IN (3, 4, 5)
		AND (to_date(SYSDATE) - to_date(hire_date)) > 5
		AND (d.dept_name = 'Marketing' 
			AND d.dept_location 
				IN ('Melborne', 'Perth'));

59. Write a query in SQL to list the employees who are senior to their own manager.

SELECT * FROM emp_bn w, emp_bn m
	WHERE w.manager_id = m.emp_id
		AND w.hire_date < m.hire_date;

60. Write a query in SQL to list the employee with their grade for the grade 4.

SELECT * FROM emp_bn e, salary_grade_bn s
	WHERE e.salary BETWEEN s.min_sal AND s.max_sal
		AND s.grade = 4;

61. Write a query in SQL to list the employees in department PRODUCTION or AUDIT who joined after 1991 and they are not MARKER or ADELYN to their name.

SELECT e.ename FROM emp_bn e, dept_bn d, 
	salary_grade_bn s
	WHERE e.dept_id = d.dept_id
		AND d.dept_name IN ('Production', 'Audit')
		AND e.salary BETWEEN s.min_sal AND s.max_sal
		AND e.ename NOT IN ('Marker', 'Adelyn')
		AND to_char(hire_date, 'YYYY') > '1991';

62. Write a query in SQL to list the employees in the ascending order of their salaries.

SELECT * FROM emp_bn 
	ORDER BY salary ASC;

63. Write a query in SQL to list the details of the employees in ascending order to the department id and descending order to the jobs.

SELECT * FROM emp_bn 
	ORDER BY dept_id ASC, job_name DESC;

64. Write a query in SQL to display all the unique job in descending order.

SELECT DISTINCT job_name FROM emp_bn 
	ORDER BY job_name DESC;

65. Write a query in SQL to list the id, name, monthly salary, daily salary of all the employees in the ascending order of their annual salary.

SELECT emp_id, ename, salary Monthly_Salary, 
	salary / 30 Daily_Salary, 
	12 * salary Annual_Salary
	FROM emp_bn
		ORDER BY Annual_Salary ASC;

66. Write a query in SQL to list the employees in descending order who are either 'CLERK' or 'ANALYST'.

SELECT * FROM emp_bn 
	WHERE job_name = 'Clerk' 
		OR job_name = 'Analyst' 
		ORDER BY job_name DESC;

67. Write a query in SQL to display the location of CLARE.

SELECT dept_location
	FROM dept_bn d, emp_bn e
	WHERE e.ename = 'Clare' 
		AND e.dept_id = d.dept_id;

68. Write a query in SQL to list the employees in ascending order of seniority who joined on 1-MAY-91, or 3-DEC-91, or 19-JAN-90.

SELECT * FROM emp_bn
	WHERE hire_date 
		IN ('01-May-1991', '03-Dec-1991', '19-Jan-1990')
		ORDER BY hire_date ASC;

69. Write a query in SQL to list the employees who are drawing the salary less than 1000 and sort the output in ascending order on salary.

SELECT * FROM emp_bn 
	WHERE salary < 1000 
		ORDER BY salary;

70. Write a query in SQL to list the details of the employees in ascending order on the salary.

SELECT * FROM emp_bn 
	ORDER BY salary ASC;

71. Write a query in SQL to list the employees in ascending order on job name and descending order on employee id.

SELECT * FROM emp_bn e 
	ORDER BY e.job_name ASC, 
			e.emp_id DESC;

72. Write a query in SQL to list the unique jobs of department 2001 and 3001 in descending order.

SELECT DISTINCT job_name FROM emp_bn 
	WHERE dept_id IN (2001, 3001) 
		ORDER BY job_name DESC;

73. Write a query in SQL to list all the employees except PRESIDENT and MANAGER in ascending order of salaries.

SELECT * FROM emp_bn 
	WHERE job_name NOT IN ('President', 'Manager') 
		ORDER BY salary ASC;

74. Write a query in SQL to list the employees in ascending order of the salary whose annual salary is below 25000.

SELECT * FROM emp_bn 
	WHERE (12 * salary) < 25000 
		ORDER BY salary ASC;

75. Write a query in SQL to list the employee id, name, annual salary, daily salary of all the employees in the ascending order of annual salary who works as a SALESMAN.

SELECT e.emp_id, e.ename, 
	12 * salary "Annual Salary", 
	floor((12 * salary)/365) "Daily Salary"
	FROM emp_bn e
		WHERE e.job_name = 'Salesman'
		ORDER BY "Annual Salary" ASC;

-- OR --

SELECT e.emp_id, e.ename, 
	12 * salary "Annual Salary", 
	(12 * salary)/365 "Daily Salary"
	FROM emp_bn e
		WHERE e.job_name = 'Salesman'
		ORDER BY "Annual Salary" ASC;

76. Write a query in SQL to list the employee id, name, hire date, current date and experience of the employees in ascending order on their experiences.

SELECT emp_id, ename, hire_date, CURRENT_DATE,
	(to_date(SYSDATE) - to_date(hire_date)) EXP
	FROM emp_bn
	ORDER BY EXP ASC;

77. Write a query in SQL to list the employees in ascending order of designations of those, joined after the second half of 1991.

SELECT * FROM emp_bn
	WHERE hire_date > to_char('30-Jun-1991')
		AND to_char(hire_date, 'YYYY') > '1991'
		ORDER BY job_name ASC;

78. Write a query in SQL to list the total information of employees table along with department, and location of all the employees working under FINANCE and AUDIT in the ascending department no.

SELECT * FROM emp_bn e, dept_bn d
	WHERE (dept_name = 'Finance' OR dept_name = 'Audit')
		AND e.dept_id = d.dept_id
		ORDER BY e.dept_id ASC;

79. Write a query in SQL to display the total information of the employees along with grades in ascending order.

SELECT * FROM emp_bn e, salary_grade_bn s
	WHERE e.salary BETWEEN s.min_sal AND s.max_sal
	ORDER BY grade ASC;

-- OR --

SELECT * FROM emp_bn e, salary_grade_bn s
	WHERE e.salary >= s.min_sal
		AND e.salary <= s.max_sal
		ORDER BY s.grade ASC;

80. Write a query in SQL to list the name, job name, department, salary, and grade of the employees according to the department in ascending order.

SELECT e.ename, e.job_name, d.dept_name, 
	e.salary, s.grade
	FROM emp_bn e, dept_bn d, salary_grade_bn s
		WHERE e.dept_id = d.dept_id
			AND e.salary BETWEEN s.min_sal AND s.max_sal
			ORDER BY e.dept_id;

81. Write a query in SQL to list the name, job name, salary, grade and department name of employees except CLERK and sort result set on the basis of highest salary.

SELECT e.ename, e.job_name, e.salary, 
	s.grade, d.dept_name
	FROM emp_bn e, dept_bn d, salary_grade_bn s
	WHERE e.dept_id = d.dept_id
		AND e.salary BETWEEN s.min_sal AND s.max_sal
		AND e.job_name NOT IN ('Clerk')
		ORDER BY e.salary DESC;

82. Write a query in SQL to list the employee ID, name, salary, department, grade, experience, and annual salary of employees working for department 1001 or 2001.

SELECT e.emp_id, e.ename, e.salary, 
	s.grade, d.dept_name, 
	(to_date(SYSDATE) - to_date(hire_date)) 
		AS "Experience",
	12 * e.salary "Annual Salary"
	FROM emp_bn e, dept_bn d, salary_grade_bn s
	WHERE e.dept_id IN (1001, 2001)
		AND e.dept_id = d.dept_id
		AND e.salary BETWEEN s.min_sal AND s.max_sal;

83. Write a query in SQL to list the details of the employees along with the details of their departments.

SELECT * FROM emp_bn e, dept_bn d
	WHERE e.dept_id = d.dept_id;

84. Write a query in SQL to list the employees who are senior to their own MANAGERS.

SELECT * FROM emp_bn w, emp_bn m
	WHERE w.manager_id = m.emp_id
		AND w.hire_date < m.hire_date;

-- OR --

SELECT * FROM emp_bn w, emp_bn m
	WHERE w.emp_id = m.manager_id
		AND w.hire_date > m.hire_date;

85. Write a query in SQL to list the employee id, name, salary, and department id of the employees in ascending order of salary who works in the department 1001.

SELECT e.emp_id, e.ename, e.salary, e.dept_id
	FROM emp_bn e
	WHERE e.dept_id = 1001
	ORDER BY e.salary ASC;

86. Write a query in SQL to find the highest salary from all the employees.

SELECT max(salary) FROM emp_bn;

87. Write a query in SQL to find the average salary and average total remuneration (salary and commission) for each type of job.

SELECT job_name, avg(salary), avg(salary + commission)
	FROM emp_bn
	GROUP BY job_name;

88. Write a query in SQL to find the total annual salary distributed against each job in the year 1991.

SELECT job_name, sum(12 * salary)
	FROM emp_bn
	WHERE to_char(hire_date, 'YYYY') = '1991'
	GROUP BY job_name;

89. Write a query in SQL to list the employee id, name, department id, location of all the employees.

SELECT e.emp_id, e.ename, e.dept_id, d.dept_location
	FROM emp_bn e, dept_bn d
	WHERE e.dept_id = d.dept_id;

90. Write a query in SQL to list the employee id, name, location, department of all the departments 1001 and 2001.

SELECT e.emp_id, e.ename, e.dept_id, 
	d.dept_location, d.dept_name
	FROM emp_bn e, dept_bn d
	WHERE e.dept_id = d.dept_id
		AND e.dept_id IN (1001, 2001);

91. List the employee id, name, salary, grade of all the employees.

SELECT e.emp_id, e.ename, e.salary, s.grade
	FROM emp_bn e, salary_grade_bn s
	WHERE e.salary BETWEEN s.min_sal AND s.max_sal;

92. Write a query in SQL to list the manager no and the number of employees working for those managers in ascending order on manager id.

SELECT w.manager_id, count(*)
	FROM emp_bn w, emp_bn m
	WHERE w.manager_id = m.emp_id
	GROUP BY w.manager_id
	ORDER BY w.manager_id ASC;

93. Write a query in SQL to display the number of employee for each job in each department.

SELECT dept_id, job_name, count(*)
	FROM emp_bn
	GROUP BY dept_id, job_name;

94. Write a query in SQL to list the department where at least two employees are working.

SELECT dept_id, count(*)
	FROM emp_bn
	GROUP BY dept_id
	HAVING count(*) >= 2;

95. Write a query in SQL to display the Grade, Number of employees, and maximum salary of each grade.

SELECT s.grade, count(*), max(salary)
	FROM emp_bn e, salary_grade_bn s
	WHERE e.salary BETWEEN s.min_sal AND s.max_sal
	GROUP BY s.grade;

96. Write a query in SQL to display the department name, grade, no. of employees where at least two employees are working as a SALESMAN.

SELECT d.dept_name, s.grade, count(*)
	FROM emp_bn e, dept_bn d, salary_grade_bn s
	WHERE e.dept_id = d.dept_id
		AND e.job_name = 'Salesman'
		AND e.salary BETWEEN s.min_sal AND s.max_sal
	GROUP BY d.dept_name, 
		s.grade
		HAVING count(*) >= 2;

97. Write a query in SQL to list the no. of employees in each department where the no. is less than 4.

SELECT dept_id, count(*)
	FROM emp_bn
	GROUP BY dept_id
	HAVING count(*) < 4;

98. Write a query in SQL to list the name of departments where at least 2 employees are working in that department.

SELECT d.dept_name, count(*)
	FROM emp_bn e, dept_bn d
	WHERE e.dept_id = d.dept_id
	GROUP BY d.dept_name
	HAVING count(*) >= 2;

99. Write a query in SQL to check whether all the employee’s numbers are indeed unique.

SELECT emp_id, count(*) FROM emp_bn 
	GROUP BY emp_id;

100. Write a query in SQL to list the no. of employees and average salary within each department for each job name.

SELECT count(*), avg(salary), dept_id, job_name
	FROM emp_bn
	GROUP BY dept_id, job_name;

101. Write a query in SQL to list the highest salary in each department in ascending order of department id.

SELECT dept_id, max(salary)
	FROM emp_bn
	GROUP BY dept_id
	ORDER BY dept_id ASC;

102. Write a query in SQL to list the department name and number of employees where more than 3 employees are working in that department.

SELECT d.dept_name, count(*)
	FROM emp_bn e, dept_bn d
	WHERE e.dept_id = d.dept_id
	GROUP BY d.dept_name
	HAVING count(*) > 3;

103. Write a query in SQL to list the grade and number of employees where at least 2 employees should be working in that grade.

SELECT s.grade, count(*)
	FROM emp_bn e, salary_grade_bn s
	WHERE e.salary BETWEEN s.min_sal AND s.max_sal
	GROUP BY s.grade
	HAVING count(*) >= 2;

104. Write a query in SQL to list the total no. of employees working in grade 3 or department 2001.

SELECT count(*)
	FROM emp_bn e, salary_grade_bn s
	WHERE (e.salary BETWEEN s.min_sal AND s.max_sal 
		AND s.grade = 3)
	OR e.dept_id = 2001;

105. Write a query in SQL to list the department name and no. of employees who joined in 1991.

SELECT d.dept_name, count(*)
	FROM emp_bn e, dept_bn d
	WHERE e.dept_id = d.dept_id
		AND to_char(hire_date, 'YYYY') = '1991'
	GROUP BY d.dept_name;

106. Write a query in SQL to list the no. of employees drawing commission in each department.

SELECT dept_id, count(*)
	FROM emp_bn
	WHERE commission IS NOT NULL
	GROUP BY dept_id;

107. Write a query in SQL to list the department name, grade, job name and number of employees drawing a salary greater than 2000.

SELECT d.dept_name, s.grade, e.job_name, count(*)
	FROM emp_bn e, dept_bn d, salary_grade_bn s
	WHERE e.salary > 2000
		AND e.salary BETWEEN s.min_sal AND s.max_sal
		AND e.dept_id = d.dept_id
	GROUP BY d.dept_name, s.grade, e.job_name;

108. Write a query in SQL to list the maximum annual salary department wise.

SELECT dept_id, max(12 * salary)
	FROM emp_bn
	GROUP BY dept_id;

109. Write a query in SQL to list the annual salary for the employees of grade 3.

SELECT e.ename, 12 * e.salary
	FROM emp_bn e, salary_grade_bn s
	WHERE e.salary BETWEEN s.min_sal AND s.max_sal
		AND s.grade = 3;

110. Write a query in SQL to list the employees who are earning more than any of the MANAGERS.

SELECT * FROM emp_bn
	WHERE salary > ANY (
			SELECT salary
			FROM emp_bn
			WHERE job_name = 'Manager'
			);

111. Write a query in SQL to list the employees who are earning more than all of the SALESMEN.

SELECT * FROM emp_bn
	WHERE salary > ALL (
			SELECT salary
			FROM emp_bn
			WHERE job_name = 'Salesman'
			);

112. Write a query in SQL to find the 2nd maximum salary of the employee.

SELECT MAX(salary) FROM emp_bn
	WHERE salary NOT IN (
		  SELECT MAX(salary)
		  FROM emp_bn
		);

113. Write a query in SQL to find the employees whose salaries are in the range of salaries of SALESMAN.

SELECT * FROM emp_bn
	WHERE salary BETWEEN (
		  SELECT MIN(salary) FROM emp_bn 
		  WHERE job_name = 'Salesman'
			) AND (
		  SELECT MAX(salary) FROM emp_bn 
		  WHERE job_name = 'Salesman'
			);

114. Write a query in SQL to list the employees of grade 3 who are working as a SALESMAN.

SELECT e.emp_id, e.ename, e.job_name, 
	e.salary, s.grade
	FROM emp_bn e, salary_grade_bn s
	WHERE e.salary BETWEEN s.min_sal AND s.max_sal
		AND s.grade = 3
		AND e.job_name = 'Salesman';