-- Link to schema: https://app.quickdatabasediagrams.com/#/d/ijGHGj

-- Create Tables, Primary Keys, and Foreign Keys

CREATE TABLE "departments" (

"dept_no" varchar(30) NOT NULL,

"dept_name" varchar(30) NOT NULL,

CONSTRAINT "pk_departments" PRIMARY KEY (

"dept_no"

)

);

CREATE TABLE "titles" (

"title_id" varchar(30) NOT NULL,

"title" varchar(30) NOT NULL,

CONSTRAINT "pk_titles" PRIMARY KEY (

"title_id"

)

);

CREATE TABLE "employees" (

"emp_no" int NOT NULL,

"emp_title_id" varchar(30) NOT NULL,

"birth_date" varchar(30) NOT NULL,

"first_name" varchar(30) NOT NULL,

"last_name" varchar(30) NOT NULL,

"sex" varchar(30) NOT NULL,

"hire_date" varchar(30) NOT NULL,

CONSTRAINT "pk_employees" PRIMARY KEY (

"emp_no"

)

);

CREATE TABLE "dept_manager" (

"dept_no" varchar(30) NOT NULL,

"emp_no" int NOT NULL

);

CREATE TABLE "dept_emp" (

"emp_no" int NOT NULL,

"dept_no" varchar(30) NOT NULL

);

CREATE TABLE "salaries" (

"emp_no" int NOT NULL,

"salary" int NOT NULL,

CONSTRAINT "pk_salaries" PRIMARY KEY (

"emp_no"

)

);

ALTER TABLE "employees" ADD CONSTRAINT "fk_employees_emp_title_id" FOREIGN KEY("emp_title_id")

REFERENCES "titles" ("title_id");

ALTER TABLE "dept_manager" ADD CONSTRAINT "fk_dept_manager_dept_no" FOREIGN KEY("dept_no")

REFERENCES "departments" ("dept_no");

ALTER TABLE "dept_manager" ADD CONSTRAINT "fk_dept_manager_emp_no" FOREIGN KEY("emp_no")

REFERENCES "employees" ("emp_no");

ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_emp_no" FOREIGN KEY("emp_no")

REFERENCES "employees" ("emp_no");

ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_dept_no" FOREIGN KEY("dept_no")

REFERENCES "departments" ("dept_no");

ALTER TABLE "salaries" ADD CONSTRAINT "fk_salaries_emp_no" FOREIGN KEY("emp_no")

REFERENCES "employees" ("emp_no");

-- List the following details of each employee: employee number, last name, first name, sex, and salary.

SELECT employees.emp_no, employees.last_name, employees.first_name, employees.sex, salaries.salary

FROM employees

INNER JOIN salaries ON

employees.emp_no=salaries.emp_no;

-- List first name, last name, and hire date for employees who were hired in 1986.

SELECT first_name, last_name, hire_date

FROM employees

WHERE hire_date LIKE '%1986';

-- List the manager of each department with their department number, department name, employee number, last name, and first name.

SELECT departments.dept_no,departments.dept_name, dept_manager.emp_no,employees.last_name,employees.first_name

FROM departments

INNER JOIN dept_manager ON departments.dept_no=dept_manager.dept_no

INNER JOIN employees ON dept_manager.emp_no=employees.emp_no;

-- List the department of each employee with their employee number, last name, first name, and department name.

SELECT dept_emp.dept_no,dept_emp.emp_no,employees.last_name,employees.first_name,departments.dept_name

FROM dept_emp

INNER JOIN employees ON dept_emp.emp_no=employees.emp_no

INNER JOIN departments ON dept_emp.dept_no=departments.dept_no;

-- List first name, last name, and sex for employees whose first name is "Hercules" and last names begin with "B".

SELECT first_name, last_name, sex

FROM employees

WHERE first_name='Hercules' AND last_name LIKE 'B%';

-- List all employees in the Sales department, including their employee number, last name, first name, and department name.

SELECT departments.dept_name, dept_emp.emp_no, employees.last_name, employees.first_name

FROM departments

INNER JOIN dept_emp ON departments.dept_no=dept_emp.dept_no

INNER JOIN employees ON dept_emp.emp_no=employees.emp_no

WHERE dept_name in(

SELECT dept_name

FROM departments

WHERE dept_name='Sales'

);

-- List all employees in Sales and Development, including their employee number, last name, first name, and department name.

SELECT departments.dept_name, dept_emp.emp_no, employees.last_name, employees.first_name

FROM departments

INNER JOIN dept_emp ON departments.dept_no=dept_emp.dept_no

INNER JOIN employees ON dept_emp.emp_no=employees.emp_no

WHERE dept_name in(

SELECT dept_name

FROM departments

WHERE dept_name='Sales' OR dept_name='Development'

);

-- In descending order, list the frequency count of employee last names

SELECT last_name, COUNT (last_name)

FROM employees

GROUP BY last_name;

-- April Fools!

SELECT * FROM employees

WHERE emp_no = 499942;