Page 114 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

name VARCHAR(50),
department id INT,

FOREIGN KEY (department id) REFERENCES departments(id)
);
3 INSERT SOME VALUES in the Tables:
INSERT INTO departments VALUES (1, ‘IT’);
INSERT INTO employees VALUES (1, ‘John Doe’, 1);
INSERT INTO employees VALUES (2, ‘Jane Smith’, 1);

INSERT INTO employees VALUES (3, ‘Bob Johnson’, 2);
Write the Sub Query:
4 write a subquery to find the employees who work in the ‘IT’ department.
SELECT name

FROM employee
WHERE department id = (SELECT id FROM department WHERE name = ‘IT’);
This subquery (SELECT id FROM departments WHERE name = ‘IT’) retrieves the department ID for the ‘IT’
department.
Execute the Query:
Run the entire query to get the result.
SELECT employee name
FROM employee
WHERE department id = (SELECT department id FROM department WHERE department name = ‘IT’);

The output should be:

This example demonstrates how to use a sub query to retrieve information based on conditions from another
table.
Questions
1 How to retrieve a list of employees with their department names?
SELECT employees.name, departments.name AS department
FROM employees

JOIN departments ON employees.department id = departments.id;
2 How to get a count of employees in each department?
SELECT departments.name AS department, COUNT(*) AS employee count
FROM employees

99
CITS : IT & ITES - Computer Software Application - Exercise 29 CITS : IT & ITES - Computer Software Application - Exercise 29