Skip to content

Commit b9e67af

Browse files
Leishla PerezLeishla Perez
Leishla Perez
authored and
Leishla Perez
committed
Uploaded homework with ERD diagram, SQL files, and query result images
1 parent 0d5231e commit b9e67af

15 files changed

Lines changed: 274 additions & 0 deletions

File tree

‎.DS_Store‎

6 KB
Binary file not shown.

‎EmployeeSQL/.DS_Store‎

8 KB
Binary file not shown.

‎EmployeeSQL/.ipynb_checkpoints/Untitled-checkpoint.ipynb‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
{
2+
"cells": [],
3+
"metadata": {},
4+
"nbformat": 4,
5+
"nbformat_minor": 4
6+
}

‎EmployeeSQL/SQL Homework ERD.png‎

69.7 KB
Loading

‎EmployeeSQL/employee_db-queries.sql‎

Lines changed: 134 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,134 @@
1+
-- Link to schema: https://app.quickdatabasediagrams.com/#/d/ijGHGj
2+
3+
-- Create Tables, Primary Keys, and Foreign Keys
4+
CREATE TABLE "departments" (
5+
"dept_no" varchar(30) NOT NULL,
6+
"dept_name" varchar(30) NOT NULL,
7+
CONSTRAINT "pk_departments" PRIMARY KEY (
8+
"dept_no"
9+
)
10+
);
11+
12+
CREATE TABLE "titles" (
13+
"title_id" varchar(30) NOT NULL,
14+
"title" varchar(30) NOT NULL,
15+
CONSTRAINT "pk_titles" PRIMARY KEY (
16+
"title_id"
17+
)
18+
);
19+
20+
CREATE TABLE "employees" (
21+
"emp_no" int NOT NULL,
22+
"emp_title_id" varchar(30) NOT NULL,
23+
"birth_date" varchar(30) NOT NULL,
24+
"first_name" varchar(30) NOT NULL,
25+
"last_name" varchar(30) NOT NULL,
26+
"sex" varchar(30) NOT NULL,
27+
"hire_date" varchar(30) NOT NULL,
28+
CONSTRAINT "pk_employees" PRIMARY KEY (
29+
"emp_no"
30+
)
31+
);
32+
33+
CREATE TABLE "dept_manager" (
34+
"dept_no" varchar(30) NOT NULL,
35+
"emp_no" int NOT NULL
36+
);
37+
38+
CREATE TABLE "dept_emp" (
39+
"emp_no" int NOT NULL,
40+
"dept_no" varchar(30) NOT NULL
41+
);
42+
43+
CREATE TABLE "salaries" (
44+
"emp_no" int NOT NULL,
45+
"salary" int NOT NULL,
46+
CONSTRAINT "pk_salaries" PRIMARY KEY (
47+
"emp_no"
48+
)
49+
);
50+
51+
ALTER TABLE "employees" ADD CONSTRAINT "fk_employees_emp_title_id" FOREIGN KEY("emp_title_id")
52+
REFERENCES "titles" ("title_id");
53+
54+
ALTER TABLE "dept_manager" ADD CONSTRAINT "fk_dept_manager_dept_no" FOREIGN KEY("dept_no")
55+
REFERENCES "departments" ("dept_no");
56+
57+
ALTER TABLE "dept_manager" ADD CONSTRAINT "fk_dept_manager_emp_no" FOREIGN KEY("emp_no")
58+
REFERENCES "employees" ("emp_no");
59+
60+
ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_emp_no" FOREIGN KEY("emp_no")
61+
REFERENCES "employees" ("emp_no");
62+
63+
ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_dept_no" FOREIGN KEY("dept_no")
64+
REFERENCES "departments" ("dept_no");
65+
66+
ALTER TABLE "salaries" ADD CONSTRAINT "fk_salaries_emp_no" FOREIGN KEY("emp_no")
67+
REFERENCES "employees" ("emp_no");
68+
69+
-- List the following details of each employee: employee number, last name, first name, sex, and salary.
70+
71+
SELECT employees.emp_no, employees.last_name, employees.first_name, employees.sex, salaries.salary
72+
FROM employees
73+
INNER JOIN salaries ON
74+
employees.emp_no=salaries.emp_no;
75+
76+
-- List first name, last name, and hire date for employees who were hired in 1986.
77+
78+
SELECT first_name, last_name, hire_date
79+
FROM employees
80+
WHERE hire_date LIKE '%1986';
81+
82+
-- List the manager of each department with their department number, department name, employee number, last name, and first name.
83+
84+
SELECT departments.dept_no,departments.dept_name, dept_manager.emp_no,employees.last_name,employees.first_name
85+
FROM departments
86+
INNER JOIN dept_manager ON departments.dept_no=dept_manager.dept_no
87+
INNER JOIN employees ON dept_manager.emp_no=employees.emp_no;
88+
89+
-- List the department of each employee with their employee number, last name, first name, and department name.
90+
91+
SELECT dept_emp.dept_no,dept_emp.emp_no,employees.last_name,employees.first_name,departments.dept_name
92+
FROM dept_emp
93+
INNER JOIN employees ON dept_emp.emp_no=employees.emp_no
94+
INNER JOIN departments ON dept_emp.dept_no=departments.dept_no;
95+
96+
-- List first name, last name, and sex for employees whose first name is "Hercules" and last names begin with "B".
97+
98+
SELECT first_name, last_name, sex
99+
FROM employees
100+
WHERE first_name='Hercules' AND last_name LIKE 'B%';
101+
102+
-- List all employees in the Sales department, including their employee number, last name, first name, and department name.
103+
104+
SELECT departments.dept_name, dept_emp.emp_no, employees.last_name, employees.first_name
105+
FROM departments
106+
INNER JOIN dept_emp ON departments.dept_no=dept_emp.dept_no
107+
INNER JOIN employees ON dept_emp.emp_no=employees.emp_no
108+
WHERE dept_name in(
109+
SELECT dept_name
110+
FROM departments
111+
WHERE dept_name='Sales'
112+
);
113+
114+
-- List all employees in Sales and Development, including their employee number, last name, first name, and department name.
115+
116+
SELECT departments.dept_name, dept_emp.emp_no, employees.last_name, employees.first_name
117+
FROM departments
118+
INNER JOIN dept_emp ON departments.dept_no=dept_emp.dept_no
119+
INNER JOIN employees ON dept_emp.emp_no=employees.emp_no
120+
WHERE dept_name in(
121+
SELECT dept_name
122+
FROM departments
123+
WHERE dept_name='Sales' OR dept_name='Development'
124+
);
125+
126+
-- In descending order, list the frequency count of employee last names
127+
128+
SELECT last_name, COUNT (last_name)
129+
FROM employees
130+
GROUP BY last_name;
131+
132+
-- April Fools!
133+
SELECT * FROM employees
134+
WHERE emp_no = 499942;

‎EmployeeSQL/employee_db-schema.sql‎

Lines changed: 134 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,134 @@
1+
-- Link to schema: https://app.quickdatabasediagrams.com/#/d/ijGHGj
2+
3+
-- Create Tables, Primary Keys, and Foreign Keys
4+
CREATE TABLE "departments" (
5+
"dept_no" varchar(30) NOT NULL,
6+
"dept_name" varchar(30) NOT NULL,
7+
CONSTRAINT "pk_departments" PRIMARY KEY (
8+
"dept_no"
9+
)
10+
);
11+
12+
CREATE TABLE "titles" (
13+
"title_id" varchar(30) NOT NULL,
14+
"title" varchar(30) NOT NULL,
15+
CONSTRAINT "pk_titles" PRIMARY KEY (
16+
"title_id"
17+
)
18+
);
19+
20+
CREATE TABLE "employees" (
21+
"emp_no" int NOT NULL,
22+
"emp_title_id" varchar(30) NOT NULL,
23+
"birth_date" varchar(30) NOT NULL,
24+
"first_name" varchar(30) NOT NULL,
25+
"last_name" varchar(30) NOT NULL,
26+
"sex" varchar(30) NOT NULL,
27+
"hire_date" varchar(30) NOT NULL,
28+
CONSTRAINT "pk_employees" PRIMARY KEY (
29+
"emp_no"
30+
)
31+
);
32+
33+
CREATE TABLE "dept_manager" (
34+
"dept_no" varchar(30) NOT NULL,
35+
"emp_no" int NOT NULL
36+
);
37+
38+
CREATE TABLE "dept_emp" (
39+
"emp_no" int NOT NULL,
40+
"dept_no" varchar(30) NOT NULL
41+
);
42+
43+
CREATE TABLE "salaries" (
44+
"emp_no" int NOT NULL,
45+
"salary" int NOT NULL,
46+
CONSTRAINT "pk_salaries" PRIMARY KEY (
47+
"emp_no"
48+
)
49+
);
50+
51+
ALTER TABLE "employees" ADD CONSTRAINT "fk_employees_emp_title_id" FOREIGN KEY("emp_title_id")
52+
REFERENCES "titles" ("title_id");
53+
54+
ALTER TABLE "dept_manager" ADD CONSTRAINT "fk_dept_manager_dept_no" FOREIGN KEY("dept_no")
55+
REFERENCES "departments" ("dept_no");
56+
57+
ALTER TABLE "dept_manager" ADD CONSTRAINT "fk_dept_manager_emp_no" FOREIGN KEY("emp_no")
58+
REFERENCES "employees" ("emp_no");
59+
60+
ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_emp_no" FOREIGN KEY("emp_no")
61+
REFERENCES "employees" ("emp_no");
62+
63+
ALTER TABLE "dept_emp" ADD CONSTRAINT "fk_dept_emp_dept_no" FOREIGN KEY("dept_no")
64+
REFERENCES "departments" ("dept_no");
65+
66+
ALTER TABLE "salaries" ADD CONSTRAINT "fk_salaries_emp_no" FOREIGN KEY("emp_no")
67+
REFERENCES "employees" ("emp_no");
68+
69+
-- List the following details of each employee: employee number, last name, first name, sex, and salary.
70+
71+
SELECT employees.emp_no, employees.last_name, employees.first_name, employees.sex, salaries.salary
72+
FROM employees
73+
INNER JOIN salaries ON
74+
employees.emp_no=salaries.emp_no;
75+
76+
-- List first name, last name, and hire date for employees who were hired in 1986.
77+
78+
SELECT first_name, last_name, hire_date
79+
FROM employees
80+
WHERE hire_date LIKE '%1986';
81+
82+
-- List the manager of each department with their department number, department name, employee number, last name, and first name.
83+
84+
SELECT departments.dept_no,departments.dept_name, dept_manager.emp_no,employees.last_name,employees.first_name
85+
FROM departments
86+
INNER JOIN dept_manager ON departments.dept_no=dept_manager.dept_no
87+
INNER JOIN employees ON dept_manager.emp_no=employees.emp_no;
88+
89+
-- List the department of each employee with their employee number, last name, first name, and department name.
90+
91+
SELECT dept_emp.dept_no,dept_emp.emp_no,employees.last_name,employees.first_name,departments.dept_name
92+
FROM dept_emp
93+
INNER JOIN employees ON dept_emp.emp_no=employees.emp_no
94+
INNER JOIN departments ON dept_emp.dept_no=departments.dept_no;
95+
96+
-- List first name, last name, and sex for employees whose first name is "Hercules" and last names begin with "B".
97+
98+
SELECT first_name, last_name, sex
99+
FROM employees
100+
WHERE first_name='Hercules' AND last_name LIKE 'B%';
101+
102+
-- List all employees in the Sales department, including their employee number, last name, first name, and department name.
103+
104+
SELECT departments.dept_name, dept_emp.emp_no, employees.last_name, employees.first_name
105+
FROM departments
106+
INNER JOIN dept_emp ON departments.dept_no=dept_emp.dept_no
107+
INNER JOIN employees ON dept_emp.emp_no=employees.emp_no
108+
WHERE dept_name in(
109+
SELECT dept_name
110+
FROM departments
111+
WHERE dept_name='Sales'
112+
);
113+
114+
-- List all employees in Sales and Development, including their employee number, last name, first name, and department name.
115+
116+
SELECT departments.dept_name, dept_emp.emp_no, employees.last_name, employees.first_name
117+
FROM departments
118+
INNER JOIN dept_emp ON departments.dept_no=dept_emp.dept_no
119+
INNER JOIN employees ON dept_emp.emp_no=employees.emp_no
120+
WHERE dept_name in(
121+
SELECT dept_name
122+
FROM departments
123+
WHERE dept_name='Sales' OR dept_name='Development'
124+
);
125+
126+
-- In descending order, list the frequency count of employee last names
127+
128+
SELECT last_name, COUNT (last_name)
129+
FROM employees
130+
GROUP BY last_name;
131+
132+
-- April Fools!
133+
SELECT * FROM employees
134+
WHERE emp_no = 499942;

‎EmployeeSQL/images/#1.png‎

190 KB
Loading

‎EmployeeSQL/images/#2.png‎

149 KB
Loading

‎EmployeeSQL/images/#3.png‎

237 KB
Loading

‎EmployeeSQL/images/#4.png‎

220 KB
Loading

0 commit comments

Comments
 (0)