Page 114 - Computer Software Application TP - Volume 1
P. 114
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