lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
display the department ID, full name (first and last name), salary for those employees who is highest salary in every department.

### SQL
SELECT first_name ,   last_name ,   salary ,  department_id ,  MAX(salary) FROM employees GROUP BY department_id
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
What are the department ids, full names, and salaries for employees who make the most in their departments?

### SQL
SELECT first_name ,   last_name ,   salary ,  department_id ,  MAX(salary) FROM employees GROUP BY department_id
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
display the first and last name, department, city, and state province for each employee.

### SQL
SELECT T1.first_name ,  T1.last_name ,  T2.department_name ,  T3.city ,  T3.state_province FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
What are the full names, departments, cities, and state provinces for each employee?

### SQL
SELECT T1.first_name ,  T1.last_name ,  T2.department_name ,  T3.city ,  T3.state_province FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
display those employees who contain a letter z to their first name and also display their last name, city.

### SQL
SELECT T1.first_name ,  T1.last_name ,  T3.city FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id WHERE T1.first_name LIKE '%z%'
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
What are the full names and cities of employees who have the letter Z in their first names?

### SQL
SELECT T1.first_name ,  T1.last_name ,  T3.city FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id WHERE T1.first_name LIKE '%z%'
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
display the department name, city, and state province for each department.

### SQL
SELECT T1.department_name ,  T2.city ,  T2.state_province FROM departments AS T1 JOIN locations AS T2 ON T2.location_id  =  T1.location_id
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
What are the department names, cities, and state provinces for each department?

### SQL
SELECT T1.department_name ,  T2.city ,  T2.state_province FROM departments AS T1 JOIN locations AS T2 ON T2.location_id  =  T1.location_id
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
display the full name (first and last name ) of employee with ID and name of the country presently where (s)he is working.

### SQL
SELECT T1.first_name ,  T1.last_name , T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_id
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
What the full names, ids of each employee and the name of the country they are in?

### SQL
SELECT T1.first_name ,  T1.last_name , T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_id
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
display the department name and number of employees in each of the department.

### SQL
SELECT department_name ,  COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY department_name
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
What are the department names and how many employees work in each of them?

### SQL
SELECT department_name ,  COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY department_name
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
display the full name (first and last name), and salary of those employees who working in any department located in London.

### SQL
SELECT first_name ,  last_name ,  salary FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN  locations AS T3 ON T2.location_id  =  T3.location_id WHERE  T3.city  =  'London'
### Database Schema
CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
)

CREATE TABLE `countries` (
  `COUNTRY_ID` varchar(2) NOT NULL,
  `COUNTRY_NAME` varchar(40) DEFAULT NULL,
  `REGION_ID` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`COUNTRY_ID`),
  FOREIGN KEY (`REGION_ID`) REFERENCES regions (`REGION_ID`)
)

CREATE TABLE `departments` (
  `DEPARTMENT_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `DEPARTMENT_NAME` varchar(30) NOT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `LOCATION_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`DEPARTMENT_ID`)
)

CREATE TABLE `jobs` (
  `JOB_ID` varchar(10) NOT NULL DEFAULT '',
  `JOB_TITLE` varchar(35) NOT NULL,
  `MIN_SALARY` decimal(6,0) DEFAULT NULL,
  `MAX_SALARY` decimal(6,0) DEFAULT NULL,
  PRIMARY KEY (`JOB_ID`)
)

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `job_history` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL,
  `START_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`,`START_DATE`),
  FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES employees(`EMPLOYEE_ID`),
  FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES departments(`DEPARTMENT_ID`),
  FOREIGN KEY (`JOB_ID`) REFERENCES jobs(`JOB_ID`)
)

CREATE TABLE `locations` (
  `LOCATION_ID` decimal(4,0) NOT NULL DEFAULT '0',
  `STREET_ADDRESS` varchar(40) DEFAULT NULL,
  `POSTAL_CODE` varchar(12) DEFAULT NULL,
  `CITY` varchar(30) NOT NULL,
  `STATE_PROVINCE` varchar(25) DEFAULT NULL,
  `COUNTRY_ID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`LOCATION_ID`),
  FOREIGN KEY (`COUNTRY_ID`) REFERENCES countries(`COUNTRY_ID`)
)

### Question
What are full names and salaries of employees working in the city of London?

### SQL
SELECT first_name ,  last_name ,  salary FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN  locations AS T3 ON T2.location_id  =  T3.location_id WHERE  T3.city  =  'London'
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is the name of the song that was released in the most recent year?

### SQL
SELECT song_name ,  releasedate FROM song ORDER BY releasedate DESC LIMIT 1
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is the name of the song that was released most recently?

### SQL
SELECT song_name ,  releasedate FROM song ORDER BY releasedate DESC LIMIT 1
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What is the id of the longest song?

### SQL
SELECT f_id FROM files ORDER BY duration DESC LIMIT 1
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
Find the id of the song that lasts the longest.

### SQL
SELECT f_id FROM files ORDER BY duration DESC LIMIT 1
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
Find the names of all English songs.

### SQL
SELECT song_name FROM song WHERE languages  =  "english"
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the names of all songs in English?

### SQL
SELECT song_name FROM song WHERE languages  =  "english"
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the id of songs whose format is mp3.

### SQL
SELECT f_id FROM files WHERE formats  =  "mp3"
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
What are the id of all the files in mp3 format?

### SQL
SELECT f_id FROM files WHERE formats  =  "mp3"
### Database Schema
CREATE TABLE genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
)

CREATE TABLE artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
)

CREATE TABLE files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
)

CREATE TABLE song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
)

### Question
List the name and country of origin for all singers who have produced songs with rating above 9.

### SQL
SELECT DISTINCT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.rating  >  9