Consider a table Employee with attributes Name, Age and Branch. Select the SQL query to retrieve the Age and Branch of the youngest Employee over the age of 18 for branches with atleast 10 employees. Select all that apply.
Question
Consider a table Employee with attributes Name, Age and Branch. Select the SQL query to retrieve the Age and Branch of the youngest Employee over the age of 18 for branches with atleast 10 employees. Select all that apply.
Solution
To retrieve the Age and Branch of the youngest Employee over the age of 18 for branches with at least 10 employees, you can use the following SQL query:
SELECT MIN(Age), Branch
FROM Employee
WHERE Age > 18 AND Branch IN (
SELECT Branch
FROM Employee
GROUP BY Branch
HAVING COUNT(*) >= 10
)
GROUP BY Branch;
Here's how this query works:
-
The subquery
(SELECT Branch FROM Employee GROUP BY Branch HAVING COUNT(*) >= 10)selects the branches that have at least 10 employees. -
The main query then selects the minimum age (which corresponds to the youngest employee) and the branch from the Employee table, but only for rows where the age is greater than 18 and the branch is one of those selected by the subquery.
-
The
GROUP BY Branchclause in the main query ensures that the minimum age is selected for each branch separately. Without this clause, the query would only return the minimum age across all branches.
Similar Questions
How do you retrieve the youngest employee's name and age? *SELECT Name, Age FROM Employees ORDER BY Age ASC LIMIT 1;SELECT MIN(Age), Name FROM Employees;SELECT LAST(Name), Age FROM Employees ORDER BY Age DESC;SELECT Name, Age FROM Employees WHERE Age = MIN(Age);
Which SQL statement will order records by their age field, from youngest to oldest?
Consider the following table Employee.Select the output returned by the following query: SELECT branch AS department, AVG(age) AS average_ageFROM employeeGROUP BY branchHAVING COUNT(*) >= 2ORDER BY average_age DESC;
How do you list all users records with age > 21 in this table?+-------+-------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` ( `id` int(11) DEFAULT NULL, `name` varchar(256) DEFAULT NULL, `age` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-------+-------------------------------------------------------------------------------------------------------------------------------+SELECT * FROM users WHERE age < 21;SELECT * FROM users WHERE age IS UP TO 21;SELECT * FROM users WHERE age > 21;SELECT * FROM users WHERE age BETWEEN 21 AND 89;
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderKnowsidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Knows table is that personA knows personB.Write a query that returns the name of all persons that are more than 5 years older than everybody they know.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.