Page 118 - CITS - CSA - TP (Volume 1) - Hindi

कंप्यूटर सॉफ्टवेयर एप्लीकेशन- CITS

(Questions):

1 कम चा रयों की सूची उनके िवभाग के नामों के साथ कै से ा कर ?
SELECT employees.name, departments.name AS department
FROM employees

JOIN departments ON employees.department_id = departments.id;
2 ेक िवभाग म कम चा रयों की सं ा कै से ा कर ?

SELECT departments.name AS department, COUNT(*) AS employee_count
FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY departments.name;

3 ेक िवभाग म कम चा रयों का औसत वेतन कै से ा कर ?
SELECT departments.name AS department, AVG(salary) AS average_salary

FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY departments.name;

4 अपने िवभाग म औसत वेतन से अिधक वेतन पाने वाले कम चा रयों को कै से खोज ?
SELECT name, salary
FROM employees

WHERE salary > (
SELECT AVG(salary)
FROM employees AS e2

WHERE e2.department_id = employees.department_id
);

5 सबसे अिधक औसत वेतन वाले िवभागों को कै से ा कर ?
SELECT departments.name AS department, AVG(employees.salary) AS average_salary
FROM employees
JOIN departments ON employees.department_id = departments.id

GROUP BY departments.name
HAVING AVG(employees.salary) = (

SELECT MAX(avg_salary)
FROM (
SELECT AVG(salary) AS avg_salary

FROM employees
GROUP BY department_id

) AS department_avg

);

102

CITS : IT & ITES - कं ूटर सॉ वेयर ए ीके शन - अ ास 29