DROP TABLE IF EXISTS employees; CREATE TABLE employees ( EmployeeId

Question

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');

1. Write a select statement for all data contained within the employees table.
2. Write a select statement identifying all employee IDs associated with deptID 2.
3. Write a select statement identifying the maximum salary grouped by deptid.
4. Write a select statement identifying all employee names whose title contains “engineer”.
5. Describe the difference between an inner and outer join.
6. Write a select statement identifying all employees and their dependents.