human
Schema:
works_on (Essn, Pno, Hours)
employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
department (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
project (Pname, Pnumber, Plocation, Dnum)
dependent (Essn, Dependent_name, Sex, Bdate, Relationship)
dept_locations (Dnumber, Dlocation)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the number of employees of each gender whose salary is lower than 50000.
assistant
SELECT count(*) , sex FROM employee WHERE salary < 50000 GROUP BY sex
human
Return the first names and last names of employees who earn more than 30000 in salary.
assistant
SELECT fname , lname FROM employee WHERE salary > 30000
human
find all dependent names who have a spouse relation with some employee.
assistant
SELECT Dependent_name FROM dependent WHERE relationship = 'Spouse'
human
Find the names of departments that are located in Houston.
assistant
SELECT t1.dname FROM department AS t1 JOIN dept_locations AS t2 ON t1.dnumber = t2.dnumber WHERE t2.dlocation = 'Houston'
human
list the first and last names, and the addresses of all employees in the ascending order of their birth date.
assistant
SELECT fname , lname , address FROM employee ORDER BY Bdate
human
List all department names ordered by their starting date.
assistant
SELECT dname FROM department ORDER BY mgr_start_date ;