Notes

create database Employee;
use Employee;
show tables;
create table employees(
employeeid NUMERIC(9),
Firstname VARCHAR(10),
Lastname VARCHAR(20),
Deptcode CHAR(5),
Salary NUMERIC(9,2)
);
insert into employees values
(1,'Johanna','Ngo','ADMIN',30000.00),
(2,'Jason','Abalos','ACCNT',18000.00),
(4,'Michael','Santiago','SALES',15000.00),
(6,'Nazario','Santos',NULL,24000.00);
create table departments(
Code CHAR(5),
Name VARCHAR(30),
Managerid NUMERIC(9),
Subdeptof CHAR(5)
);
insert into departments values
('ADMIN','Administration',1,NULL),
('ACCNT','Accounting',2,'ADMIN'),
('SALES','Sales',3,'ADMIN'),
('MKTG','Marketing',4,'SALES');
create table projects(
Projected CHAR(8),
Deptcode CHAR(5),
description VARCHAR(200),
Startdate DATE,
Stopdate DATE,
Revenue NUMERIC(12,2)
);
insert into projects values
('EMPHAPPY','ADMIN','Employee Moral','2008-09-15',NULL, 0.00),
('ADT4MFIA','ACCNT','Mofia Audit','2008-07-03','2008-11-30',100000.00),
('ROBOSPSE','SALES','Robot','2008-03-14',NULL,242000.00),
('DNLCLNT','MKTG','Download Client','2008-02-03',NULL,18150.00);
create table workson(
employeeid NUMERIC(9),
Projected CHAR(8),
assignedtime DECIMAL(3,2)
);
insert into workson values
(2,'ADT4MFIA',0.50),
(3,'ROBOSPSE',0.75),
(4,'ROBOSPSE',0.75),
(5,'ROBOSPSE',0.50),
(5,'ADT4MFIA',0.6),
(3,'DNLDCLNT',0.25);

select * from employees;
select avg(Salary) from employees;
select min(Revenue) from projects
where Revenue>0;
select max(Revenue) from projects
where Revenue>0;
select count(Stopdate) from projects;
select employeeid,count(Projected)
from workson
group by employeeid;
select max(Lastname) from employees;
select count(Deptcode) from employees;
select count(employeeid), Deptcode
from employees
group by Deptcode;
select Deptcode, avg(Revenue), count(Projected)
from projects
group by Deptcode;
select Deptcode, avg(Revenue), count(Projected)
from projects
group by Deptcode
having count(Projected)>2;
select Deptcode, avg(Revenue), count(Projected)
from projects
where Stopdate is null
group by Deptcode
order by count(Projected) desc;
select employeeid
from workson
where assignedtime>1;
select salary*1.1,Deptcode
from employees
where not(Lastname like '%re')
group by Deptcode;


create table examples(
c integer
);

insert into examples values
(1),(2),(3),(null);

select * from examples;
select avg(c) from examples;
use mydb;
select avg(orderprice) from sales;
select sum(orderprice)/count(8) from sales;
select * from sales where orderprice> 100;