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
how many departments are there?
assistant
SELECT COUNT( * ) FROM department;
human
show all their info.
assistant
SELECT * FROM department;
human
where are they each located?
assistant
SELECT Dlocation FROM department AS t1 JOIN dept_locations AS t2 ON t1.dnumber = t2.dnumber;
human
what are 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" ;