DROP TABLE IF EXISTS employees;
CREATE TABLE employees
(
EmployeeId integer,
FirstName varchar(50),
LastName varchar(50),
Title varchar(50),
ManagerEmpId integer,
HiredOn date,
Salary integer,
Commission float,
DeptId integer
);
CREATE TABLE departments
(
DeptId integer,
DeptName varchar(50)
);
CREATE TABLE employeeDependents
(
DependentId integer,
EmployeeId integer,
FirstName varchar(50),
LastName varchar(50)
);
INSERT INTO employees VALUES (1,'John', 'White','IT Admin Assistant',6,'2010-12-17',18000,NULL,4);
INSERT INTO employees VALUES (2,'Alison', 'Harding','Sales Manager',9,'1998-02-02',52000,0.02,3);
INSERT INTO employees VALUES (3,'Tim', 'Anderson','Sales Representative I',2,'2018-01-02',25000,0.10,3);
INSERT INTO employees VALUES (4,'Natalie', 'Watts','Sales Representative I',2,'2014-04-02',27000,0.07,3);
INSERT INTO employees VALUES (5,'Ray', 'Smith','Help Desk Tech',6,'2014-06-23',22500,1400,4);
INSERT INTO employees VALUES (6,'Suzanne', 'Davis','IT Manager',9,'2006-05-01',54000,NULL,4);
INSERT INTO employees VALUES (7,'Ethan', 'Miller','Systems Administrator',6,'2001-09-22',25000,NULL,4);
INSERT INTO employees VALUES (8,'Jane', 'Mitchell','Product Engineer',10,'1997-03-30',32000,NULL,2);
INSERT INTO employees VALUES (9,'Alice', 'Jackson','CEO',NULL,'1990-01-01',75000,NULL,2);
INSERT INTO employees VALUES (10,'Frank', 'Foster','COO',9,'1994-08-09',56000,NULL,2);
INSERT INTO employees VALUES (11,'Cory', 'Robertson','Product Engineer Team Lead',10,'1995-03-15',48000,NULL,2);
INSERT INTO employees VALUES (12,'Bob', 'Walker','Operations Admin Assistant',11,'1998-04-16',18000,NULL,2);
INSERT INTO employees VALUES (13,'Madison', 'Hayes','Product Engineer',10,'2000-12-03',30000,NULL,2);
INSERT INTO employees VALUES (14,'Francis', 'Morris','Accounting Director',9,'2007-10-12',35000,NULL,1);
INSERT INTO employees VALUES (15,'Abby', 'Norris','Sales Representative I',2,'2019-02-20',29000,0.05,3);
INSERT INTO employees VALUES (16,'Aubrey', 'Luther','Product Engineer',10,'2007-10-12',38000,NULL,2);
INSERT INTO departments VALUES (1,'Accounting');
INSERT INTO departments VALUES (2,'Operations');
INSERT INTO departments VALUES (3,'Sales');
INSERT INTO departments VALUES (4,'Information Technology');
INSERT INTO employeeDependents VALUES (1, 7, 'Max', 'Miller');
INSERT INTO employeeDependents VALUES (1, 7, 'Courtney', 'Miller');
INSERT INTO employeeDependents VALUES (1, 4, 'McKayla', 'Watts');
INSERT INTO employeeDependents VALUES (1, 4, 'Charlotte', 'Watts');
INSERT INTO employeeDependents VALUES (1, 9, 'Max', 'Jackson');
INSERT INTO employeeDependents VALUES (1, 9, 'Katherine', 'Jackson');
INSERT INTO employeeDependents VALUES (1, 9, 'David', 'Jackson');
- Write a select statement for all data contained within the employees table.
- Write a select statement identifying all employee IDs associated with deptID 2.
- Write a select statement identifying the maximum salary grouped by deptid.
- Write a select statement identifying all employee names whose title contains “engineer”.
- Describe the difference between an inner and outer join.
- Write a select statement identifying all employees and their dependents.