Query.sql

-- CREATE DATABASE corsoSqcorsosqll;
-- DDl
-- CREATE
-- DROP
-- ALTER
-- RENAME
-- TRUNCATE

CREATE TABLE if NOT exists libro (id INT,
									titolo VARCHAR(100),
									prezzo DECIMAL,
									data_pub DATE);
							
							
drop TABLE if exists libro;

CREATE TABLE copia_libro LIKE libro;

DROP TABLE if EXISTS libro, copia_libro;

CREATE TABLE persona(id INT,
							nome VARCHAR(100));
							
-- ADD aggiunge 1 o PIù colonne							
ALTER TABLE persona ADD indirizzo CHAR(100),
							ADD eta INT;
							
-- modify modifica il tipo							
ALTER TABLE persona MODIFY COLUMN indirizzo VARCHAR(100)

-- change modifica il nome e il tipo della colonna
ALTER TABLE persona CHANGE COLUMN eta anni INT;

ALTER TABLE persona DROP COLUMN indirizo,
							DROP COLUMN anni;
-- rinomina della tabella							
RENAME TABLE persona TO persona_2;

-- comandi multipli					
ALTER TABLE persona_2
				CHANGE COLUMN nome nominativo VARCHAR(100),
				ADD eta INT;
				
CREATE TABLE maglia(id INT,
							tipo VARCHAR(100),
							taglia ENUM('xs','s','m','l','xl'));
							
CREATE TABLE utente(id INT,
							nome VARCHAR(100),
							sesso ENUM('m','f','x'),
							patentato ENUM('s','no'));
							
CREATE TABLE impiegato(id_impiegato INT(255),				
									nome VARCHAR(50),
									data_asssunzione DATETIME,
									stipendio_in_lire INT(255),
									stiendio_in_euro INT(255),
									num_reparto INT(150),
									livello INT(100),
									sesso ENUM('m','f'));


RENAME table impiegato to impiegati;

ALTER TABLE impiegati DROP column stipendio_in_lire,
								ADD cognome VARCHAR(100),
								MODIFY COLUMN livello VARCHAR(10),
								CHANGE COLUMN stiendio_in_euro stipendio;
								
-- CONSTRAINT -> sono dei vincoli sui valori che io posso insenrire nelle colonne
-- NOT NULL -> obbliga a inserire qualcosa nella colonna, la colonna onon può essere NULL

CREATE TABLE tizio (id INT NOT NULL,
							nome VARCHAR(100) NOT NULL,
							cognome VARCHAR(100),
							email VARCHAR(100));

-- rendo la colonna NOT NULL successivamente alla creazione
ALTER table tizio MODIFY cognome VARCHAR(100) NOT NULL;

-- unique -> rende i valori tutti diversi / non ammetet dupplicati
ALTER TABLE tizio ADD constraint email_uq UNIQUE (email);

ALTER TABLE tizio ADD COLUMN telefono VARCHAR(100),
										ADD CONSTRAINT telefono_uq UNIQUE(telefono);
										
ALTER TABLE tizio DROP CONSTRAINT telefono_uq;

ALTER table tizio MODIFY cognome VARCHAR(100) NULL;

ALTER TABLE tizio ADD COLUMN ETA INT;

-- check -> obbliga ad inserire solo alcuni valori
ALTER TABLE tizio ADD CONSTRAINT eta_ck CHECK(eta>18);

ALTER table tizio ADD constraint nome_ck CHECK(length(nome)>3);

CREATE TABLE tizio2(Id INT, email VARCHAR(50) NOT NULL UNIQUE CHECK(email LIKE '%@%'));


-- CREATE DATABASE if not exists corsosql;

-- use corsosql;

drop table if exists EMP;

select 'Eliminata emp';

drop table if exists DEPT;

select 'Eliminata dept';

create table DEPT
(
  DEPTNO int,
  DNAME  VARCHAR(30),
  CITY   VARCHAR(30)
);

select 'Creata Dept';

create table EMP
(
  EMPNO     int,
  ENAME     VARCHAR(20),
  JOB       VARCHAR(10),
  MGR       int,
  HIRE_DATE DATE,
  SAL       NUMERIC(8,2),
  DEPTNO    int
);

select 'Creata Emp';

insert into DEPT (DEPTNO, DNAME, CITY)
values (10, 'IT', 'Southlake');
insert into DEPT (DEPTNO, DNAME, CITY)
values (20, 'Marketing', 'Toronto');
insert into DEPT (DEPTNO, DNAME, CITY)
values (30, 'Human Resources', 'London');
insert into DEPT (DEPTNO, DNAME, CITY)
values (40, 'Research', 'New York');

select 'Inserita dept';


insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (100, 'Steven', 'ACCOUNT', null, '1987-06-17', 24000, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (101, 'Neena', 'PROG', 100, '1989-09-21', 17000, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (102, 'Lex', 'CLERK', 100, '1993-01-13', 17000, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (103, 'Alexander', 'PROG', 102, '1990-01-03', 9000, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (104, 'Bruce', 'PROG', 103, '1991-05-21', 6000, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (105, 'David', 'PROG', 103, '1997-06-25', 4800, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (106, 'Valli', 'PROG', 103, '1998-02-05', 4800, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (107, 'Diana', 'PROG', 103, '1999-02-07', 4200, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (108, 'Nancy', 'PROG', 101, '1994-08-17', 12000, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (109, 'Daniel', 'ACCOUNT', 108, '1994-08-16', 9000, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (110, 'John', 'ACCOUNT', 108, '1997-09-28', 8200, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (111, 'Ismael', 'ACCOUNT', 108, '1997-09-30', 7700, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (112, 'Jose Manuel', 'ACCOUNT', 108, '1998-03-07', 7800, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (113, 'Luis', 'ACCOUNT', 108, '1999-12-07', 6900, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (114, 'Den', 'CLERK', 100, '1994-12-07', 11000, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (115, 'Alexander', 'CLERK', 114, '1995-05-18', 3100, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (116, 'Shelli', 'CLERK', 114, '1997-12-24', 2900, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (117, 'Sigal', 'CLERK', 114, '1997-07-24', 2800, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIRE_DATE, SAL, DEPTNO)
values (118, 'Guy', 'CLERK', 114, '1998-11-15', 2600, 30);

select 'Inserita emp';


SET FOREIGN_KEY_CHECKS=0; 

DROP TABLE if exists EMPLOYEES;
 
SET FOREIGN_KEY_CHECKS=1;

select  'cancellata tabella EMPLOYEES';

DROP TABLE if exists DEPARTMENTS;

select  'cancellata tabella DEPARTMENTS';

create table DEPARTMENTS
(
  DEPARTMENT_ID   int not null,
  DEPARTMENT_NAME VARCHAR(30) not null,
  MANAGER_ID      int,
  LOCATION_ID     int
);

alter table DEPARTMENTS
  add constraint DEPT_ID_PK primary key (DEPARTMENT_ID);

select  'Creata tabella DEPARTMENTS';
  
create table EMPLOYEES
(
  EMPLOYEE_ID    int not null,
  FIRST_NAME     VARCHAR(20),
  LAST_NAME      VARCHAR(25) not null,
  EMAIL          VARCHAR(25) not null,
  PHONE_NUMBER   VARCHAR(20),
  HIRE_DATE      DATETIME not null,
  JOB_ID         VARCHAR(10) not null,
  SALARY         NUMERIC(8,2),
  COMMISSION_PCT NUMERIC(2,2),
  MANAGER_ID     int,
  DEPARTMENT_ID  int
);

select  'Creata tabella EMPLOYEES';

alter table EMPLOYEES
  add constraint EMP_EMP_ID_PK primary key (EMPLOYEE_ID);

alter table EMPLOYEES
  add constraint EMP_EMAIL_UK unique (EMAIL);

alter table EMPLOYEES
  add constraint EMP_SALARY_MIN
  check (salary > 0);
  
alter table EMPLOYEES
  add constraint EMP_DEPT_FK foreign key (DEPARTMENT_ID)
  references DEPARTMENTS (DEPARTMENT_ID)
  on delete cascade;

select  'creati constraint';  

insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (10, 'Administration', 200, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (20, 'Marketing', 201, 1800);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (30, 'Purchasing', 114, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (40, 'Human Resources', 203, 2400);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (50, 'Shipping', 121, 1500);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (60, 'IT', 103, 1400);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (70, 'Public Relations', 204, 2700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (80, 'Sales', 145, 2500);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (90, 'Executive', 100, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (100, 'Finance', 108, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (110, 'Accounting', 205, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (120, 'Treasury', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (130, 'Corporate Tax', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (140, 'Control And Credit', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (150, 'Shareholder Services', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (160, 'Benefits', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (170, 'Manufacturing', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (180, 'Construction', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (190, 'Contracting', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (200, 'Operations', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (210, 'IT Support', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (220, 'NOC', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (230, 'IT Helpdesk', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (240, 'vernment Sales', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (250, 'Retail Sales', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (260, 'Recruiting', null, 1700);
insert into DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (270, 'Payroll', null, 1700);

select  'inserito DEPARTMENTS';

insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', '2007-06-21', 'SH_CLERK', 2600, null, 124, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', '2008-01-13', 'SH_CLERK', 2600, null, 124, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '2003-09-17', 'AD_ASST', 4400, null, 101, 10);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '2004-02-17', 'MK_MAN', 13000, null, 100, 20);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', '2005-08-17', 'MK_REP', 6000, null, 201, 20);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', '2002-06-07', 'HR_REP', 6500, null, 101, 40);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', '2002-06-07', 'PR_REP', 10000, null, 101, 70);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '2002-06-07', 'AC_MGR', 12008, null, 101, 110);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', '2002-06-07', 'AC_ACCOUNT', 8300, null, 205, 110);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (100, 'Steven', 'King', 'SKING', '515.123.4567', '2003-06-17', 'AD_PRES', 24000, null, null, 90);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '2005-09-21', 'AD_VP', 17000, null, 100, 90);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '2001-01-13', 'AD_VP', 17000, null, 100, 90);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '2006-01-03', 'IT_PROG', 9000, null, 102, 60);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '2007-05-21', 'IT_PROG', 6000, null, 103, 60);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', '2005-06-25', 'IT_PROG', 4800, null, 103, 60);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', '2006-02-05', 'IT_PROG', 4800, null, 103, 60);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '2007-02-07', 'IT_PROG', 4200, null, 103, 60);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', '2002-08-17', 'FI_MGR', 12008, null, 101, 100);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '2002-08-16', 'FI_ACCOUNT', 9000, null, 108, 100);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (110, 'John', 'Chen', 'JCHEN', '515.124.4269', '2005-09-28', 'FI_ACCOUNT', 8200, null, 108, 100);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', '2005-09-30', 'FI_ACCOUNT', 7700, null, 108, 100);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', '2006-03-07', 'FI_ACCOUNT', 7800, null, 108, 100);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', '2007-12-07', 'FI_ACCOUNT', 6900, null, 108, 100);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '2002-12-07', 'PU_MAN', 11000, null, 100, 30);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', '2003-05-18', 'PU_CLERK', 3100, null, 114, 30);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', '2005-12-24', 'PU_CLERK', 2900, null, 114, 30);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '2005-07-24', 'PU_CLERK', 2800, null, 114, 30);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', '2006-11-15', 'PU_CLERK', 2600, null, 114, 30);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '2007-08-10', 'PU_CLERK', 2500, null, 114, 30);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '2004-07-18', 'ST_MAN', 8000, null, 100, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '2005-04-10', 'ST_MAN', 8200, null, 100, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '2003-05-01', 'ST_MAN', 7900, null, 100, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '2005-10-10', 'ST_MAN', 6500, null, 100, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (124, 'Kevin', 'Mours', 'KMOURS', '650.123.5234', '2007-11-16', 'ST_MAN', 5800, null, 100, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', '2005-07-16', 'ST_CLERK', 3200, null, 120, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', '2006-09-28', 'ST_CLERK', 2700, null, 120, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', '2007-01-14', 'ST_CLERK', 2400, null, 120, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', '2008-03-08', 'ST_CLERK', 2200, null, 120, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', '2005-08-20', 'ST_CLERK', 3300, null, 121, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', '2005-10-30', 'ST_CLERK', 2800, null, 121, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', '2005-02-16', 'ST_CLERK', 2500, null, 121, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', '2007-04-10', 'ST_CLERK', 2100, null, 121, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', '2004-06-14', 'ST_CLERK', 3300, null, 122, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', '2006-08-26', 'ST_CLERK', 2900, null, 122, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', '2007-12-12', 'ST_CLERK', 2400, null, 122, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', '2008-02-06', 'ST_CLERK', 2200, null, 122, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', '2003-07-14', 'ST_CLERK', 3600, null, 123, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', '2005-10-26', 'ST_CLERK', 3200, null, 123, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (139, 'John', 'Seo', 'JSEO', '650.121.2019', '2006-02-12', 'ST_CLERK', 2700, null, 123, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', '2006-04-06', 'ST_CLERK', 2500, null, 123, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', '2003-10-17', 'ST_CLERK', 3500, null, 124, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', '2005-01-29', 'ST_CLERK', 3100, null, 124, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', '2006-03-15', 'ST_CLERK', 2600, null, 124, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '2006-07-09', 'ST_CLERK', 2500, null, 124, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', '2004-10-01', 'SA_MAN', 14000, .4, 100, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', '2005-01-05', 'SA_MAN', 13500, .3, 100, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', '2005-03-10', 'SA_MAN', 12000, .3, 100, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', '2007-10-15', 'SA_MAN', 11000, .3, 100, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '2008-01-29', 'SA_MAN', 10500, .2, 100, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', '2005-01-30', 'SA_REP', 10000, .3, 145, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', '2005-03-24', 'SA_REP', 9500, .25, 145, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', '2005-08-20', 'SA_REP', 9000, .25, 145, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', '2006-03-30', 'SA_REP', 8000, .2, 145, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', '2006-12-09', 'SA_REP', 7500, .2, 145, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', '2007-11-23', 'SA_REP', 7000, .15, 145, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (156, 'Janette', 'King', 'JKING', '011.44.1345.429268', '2004-01-30', 'SA_REP', 10000, .35, 146, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', '2004-03-04', 'SA_REP', 9500, .35, 146, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', '2004-08-01', 'SA_REP', 9000, .35, 146, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', '2005-03-10', 'SA_REP', 8000, .3, 146, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', '2005-12-15', 'SA_REP', 7500, .3, 146, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', '2006-11-03', 'SA_REP', 7000, .25, 146, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', '2005-11-11', 'SA_REP', 10500, .25, 147, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', '2007-03-19', 'SA_REP', 9500, .15, 147, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', '2008-01-24', 'SA_REP', 7200, .1, 147, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', '2008-02-23', 'SA_REP', 6800, .1, 147, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', '2008-03-24', 'SA_REP', 6400, .1, 147, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', '2008-04-21', 'SA_REP', 6200, .1, 147, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', '2005-03-11', 'SA_REP', 11500, .25, 148, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', '2006-03-23', 'SA_REP', 10000, .2, 148, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', '2006-01-24', 'SA_REP', 9600, .2, 148, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', '2007-02-23', 'SA_REP', 7400, .15, 148, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', '2007-03-24', 'SA_REP', 7300, .15, 148, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', '2008-04-21', 'SA_REP', 6100, .1, 148, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', '2004-05-11', 'SA_REP', 11000, .3, 149, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', '2005-03-19', 'SA_REP', 8800, .25, 149, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', '2006-03-24', 'SA_REP', 8600, .2, 149, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', '2006-04-23', 'SA_REP', 8400, .2, 149, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', '2007-05-24', 'SA_REP', 7000, .15, 149, null);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', '2008-01-04', 'SA_REP', 6200, .1, 149, 80);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', '2006-01-24', 'SH_CLERK', 3200, null, 120, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', '2006-02-23', 'SH_CLERK', 3100, null, 120, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', '2007-06-21', 'SH_CLERK', 2500, null, 120, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', '2008-02-03', 'SH_CLERK', 2800, null, 120, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', '2004-01-27', 'SH_CLERK', 4200, null, 121, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', '2005-02-20', 'SH_CLERK', 4100, null, 121, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', '2006-06-24', 'SH_CLERK', 3400, null, 121, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', '2007-02-07', 'SH_CLERK', 3000, null, 121, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', '2005-06-14', 'SH_CLERK', 3800, null, 122, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', '2005-08-13', 'SH_CLERK', 3600, null, 122, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', '2006-07-11', 'SH_CLERK', 2900, null, 122, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', '2007-12-19', 'SH_CLERK', 2500, null, 122, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', '2004-02-04', 'SH_CLERK', 4000, null, 123, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', '2005-03-03', 'SH_CLERK', 3900, null, 123, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', '2006-07-01', 'SH_CLERK', 3200, null, 123, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', '2007-03-17', 'SH_CLERK', 2800, null, 123, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', '2006-04-24', 'SH_CLERK', 3100, null, 124, 50);
insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', '2006-05-23', 'SH_CLERK', 3000, null, 124, 50);

select  'inserito EMPLOYEES';

drop table if exists ORDERITEMS;


create table ORDERITEMS
(
  ORDER_NUM  INTEGER not null,
  ORDER_ITEM INTEGER not null,
  PROD_ID    CHAR(10),
  QUANTITY   INTEGER not null,
  ITEM_PRICE NUMERIC(8,2) not null
);

select 'Creata Ordeitems';


drop table if exists Orders;

CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) 
);

select 'Creata Orders';


drop table if exists Products;

CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  varchar(1000) NULL 
);

select 'Creata Products';


drop table if exists Vendors;

CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL 
);

select 'Creata Vendors';


drop table if exists Customers;

CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);

select 'Creata Customers';


ALTER TABLE OrderItems ADD CONSTRAINT PK_OrderItems PRIMARY KEY (order_num, order_item);
ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (cust_id);
ALTER TABLE Orders ADD CONSTRAINT PK_Orders PRIMARY KEY (order_num);
ALTER TABLE Products ADD CONSTRAINT PK_Products PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD CONSTRAINT PK_Vendors PRIMARY KEY (vend_id);

select 'Create Primary Keys';


ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products
ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

select 'Create Foreign Keys';


INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chica', 'IL', '54545', 'USA', 'Kim Howard');

select 'Inserita Customers';


INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

select 'Inserita Vendors';


INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

select 'Inserita Products';


INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2004-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2004-01-12', null);
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2004-01-03', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2004-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2004-02-08', null);

select 'Inserita Orders';


INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20005, 1, 'BR01      ', 100, 5.49);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20005, 2, 'BR03      ', 100, 10.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20006, 1, 'BR01      ', 20, 5.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20006, 2, 'BR02      ', 10, 8.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20006, 3, 'BR03      ', 10, 11.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20007, 1, 'BR03      ', 50, 11.49);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20007, 2, NULL, 100, 2.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20007, 3, 'BNBG02    ', 100, 2.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20007, 4, 'BNBG03    ', 100, 2.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20007, 5, 'RGAN01    ', 50, 4.49);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20008, 1, 'RGAN01    ', 5, 4.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20008, 2, 'BR03      ', 5, 11.99);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20008, 3, 'BNBG01    ', 10, 3.49);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20008, 4, NULL, 10, 3.49);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20008, 5, 'BNBG03    ', 10, 3.49);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20009, 1, 'BNBG01    ', 250, 2.49);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20009, 2, 'BNBG02    ', 250, 2.49);
INSERT INTO ORDERITEMS (ORDER_NUM, ORDER_ITEM, PROD_ID, QUANTITY, ITEM_PRICE) VALUES (20009, 3, NULL, 250, 2.49);

select 'Inserita Orderitems';


select 'Tutto ok :)';

COMMIT;


-- SELECT -- colonne da estrarre (colonne)
-- from --tabellla da dove prendere i valori
-- WHERE  --condizione di estrazione (righe)

SELECT * -- tutte le colonne
FROM emp;

SELECT 'ciao a tutti';

SELECT 1+1 AS somma; -- as si chiama alias e serve per rinominare una colonna

SELECT 'ciao a tutti',1+1;

SELECT empno AS "numero impiegato", 
		 ename AS nome, 
		 sal AS salario, 
		 job AS lavoro, 
		 hire_date AS "data assunzione", 
		 mgr AS manager,
		 deptno AS "numero dipartimento"
FROM emp;


SELECT order_num AS "numero ordine",
		 order_date AS "data ordine",
		 cust_id AS cliente
FROM orders;


SELECT p.prod_id AS "identificativo del prodotto",
		 p.vend_id AS "identificativo del venditore",
		 p.prod_name AS "nome prodotto",
		 p.prod_price AS "prezzo",
		 p.prod_desc AS "descrizione"		 
FROM products AS p; -- alias di tabella


SELECT *
FROM products p
ORDER BY p.prod_price; -- ordinamento crescente

SELECT *
FROM products p
ORDER BY p.prod_price desc; -- ordinamento decrescente

SELECT *
FROM products p
ORDER BY p.prod_name; -- ordinamento crescente

SELECT prod_name
FROM products
ORDER BY prod_price;

SELECT * FROM products p
order BY p.vend_id, p.prod_price DESC;

SELECT distinct vend_id -- distinct elimina i duplicati
FROM products;

SELECT DISTINCT job FROM emp;

SELECT * FROM emp LIMIT 0,5; -- limita il numero di righe da estrarre
-- limit parte da 0 quindi è uno indietro
SELECT * FROM emp LIMIT 5,10; -- dalla sesta alla decima

SELECT * FROM products p
ORDER BY p.prod_id DESC;

SELECT * FROM products p LIMIT 5,10;

SELECT * FROM emp
ORDER BY sal DESC
LIMIT 0,1;

-- inline view
SELECT *, (select ename from emp where empno=100)
FROM dept d 

SELECT ename, job
FROM (select empno, ename, job, sal, deptno from emp) AS e

SELECT ename, sal, job, salario_annuo
-- inline view serve per calcolare il salario annuo
FROM (SELECT *, sal*12 AS salario_annuo FROM emp) AS X 
WHERE salario_annuo>90000;

SELECT * 
fROM emp
WHERE mgr IS NULL; -- col null NON si usa = ma IS

SELECT * 
fROM emp
WHERE mgr IS not NULL; 

SELECT * FROM products;

SELECT *, case cust_state
				when 'mi'  then 'michigan'
				when 'oh'  then 'ohio'
				when 'in'  then 'indiana'
				when 'az'  then 'arizona'
				when 'il'  then 'illinois'
				ELSE 'stso nullo'
			END AS stato,
if(cust_email IS NULL,'email nulla',cust_email) AS 'presenza email'
FROM customers;

-- funzioni di gruppo --> prendono un insieme di dati e mi restituiscono un valore solo


SELECT COUNT(*) 
FROM customers
WHERE cust_email IS NOT NULL;

SELECT COUNT(cust_email),COUNT(*) -- COUNT * considera anche i valori nulli presenti nella colonna
FROM customers;

SELECT COUNT(mgr),COUNT(*)
FROM emp;

-- per le funzioni di gruppo non ho visibilità sul dettaglio, quindi non so chi sono le persone
SELECT MAX(sal), -- massimo
		 MIN(sal), -- minimo
		 AVG(sal) -- media
FROM emp;


-- quanti impiegati ci sono per ogni dipartimento		 
SELECT deptno,COUNT(*)		 
FROM emp
GROUP BY deptno; -- qui sto raggruppando per dipartimento	

-- quanti impiegati fanno i dversi lavori
SELECT job,COUNT(*)	 
FROM emp
GROUP BY job; -- qui raggruppo per job
HAVING MAX(sal)>17000

-- Quando c'è una funzione di gruppo (count, max, min, avg, sum) bisogna sempre mettere group by
SELECT deptno,MAX(sal),MIN(sal),AVG(sal),SUM(sal)
FROM emp
GROUP BY deptno
ORDER BY MIN(sal) -- si possono usare le funzioni di gruppo anche nella order by


-- visualizza la media dei salari nei vari dipartimenti 
-- per quelli il cui nome inizia per s e guadagnano una media del salario maggiore di 8000
SELECT deptno,AVG(sal)
FROM emp
WHERE ename LIKE'S%' -- filtro sulle righe. prendo i nomi che iniziano per S
GROUP BY deptno
having AVG(sal)>8000 -- filtro sui gruppi .la having è la where per le funzioni di gruppo


-- la somma dei salari per i vari tipi di lavoro per quelli che la somma dei salari è > 40000
SELECT job,SUM(sal)
FROM emp
GROUP BY job
HAVING sum(sal)>40000

-- il  salario massimo per ciascun dipartimento per quelli il cui salario medio è >20000
SELECT deptno,MAX(sal), AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>20000;

-- quanti impiegati hanno lo stesso nome?
SELECT ename,COUNT(ename)
FROM emp
GROUP BY ename
HAVING COUNT(ename)>1;

-- quanti prodotti che costano più di 3 euro ci sono per ciascun ordine, 
-- tra quelli che hanno più di 2 prodotti dentro l'ordine
SELECT ORDER_NUM,COUNT(PROD_ID) AS conteggio
FROM orderitems
WHERE ITEM_PRICE>3 -- filtro per righe
GROUP BY ORDER_NUM -- raggruppo per numero di ordine
HAVING COUNT(PROD_ID) >2 -- filtro per gruppi
ORDER BY conteggio DESC; -- ordino per conteggio dal più grande al più piccolo


-- quanti prodotti che costano più di 4 euro vengono venduti dai diversi venditori
SELECT vend_id,COUNT(prod_id) AS numero_prodotti
FROM products
WHERE prod_price>4
GROUP BY vend_id
HAVING numero_prodotti>2;

-- quanti sono e che lavoro fanno gli impiegati la cui media del salario è maggiore di 10000
SELECT job, COUNT(*),AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal)>10000;

-- visualizzare i numeri di ordine e quanti oggetti ci sono nell'ordine per quegli ordini che contengono 3 o più prodotti
SELECT order_num AS numero_ordine,COUNT(*) AS conteggio, SUM(QUANTITY)
FROM orderitems
GROUP BY order_num
HAVING conteggio>=3;

-- trovare i venditori che hanno 2 o più prodotti che costano almeno 4 euro
SELECT vend_id,COUNT(*) num_prod
FROM products
WHERE prod_price>=4
GROUP BY vend_id
HAVING num_prod>=2


-- trovare il prodotto che è stato ordinato di più
SELECT prod_id, COUNT(*) AS piu_venduto
FROM orderitems
GROUP BY prod_id
order BY piu_venduto DESC
LIMIT 1;


-- qual'è l'ordine più costoso tra gli ordini che contengono 3 o più prodotti

SELECT order_num,COUNT(*) quanti, SUM(ITEM_PRICE*quantity) prezzo_ordine
FROM orderitems
GROUP BY ORDER_NUM
HAVING COUNT(*)>=3
order BY prezzo_ordine DESC
LIMIT 1;



-- Esercizi:
-- 1) Trovare il dipartimento con il maggior numero di dipendenti, che guadagnano uno stipendio superiore a 4000 euro al mese.


SELECT deptno, COUNT(*) quanti
FROM emp
WHERE, sal>4000
gorp BY deptno
ORDER BY quanti DESC
LIMIT 1;

-- 2) Visualizzare il salario medio per dipartimento, per quei dipendenti che sono stati assunti prima del 2005/12/31  
-- e lavorano nei dipartimenti 10,20,50,  considerando solo quelli il cui salario massimo è maggiore di 5000
-- ordinati per il salario medio

SELECT deptno, AVG(sal)
FROM emp e
WHERE e,HIRE_DATE<'2005/12/31'
AND depptno IN(10,20,50)
GROUP BY deptno
HAVING AVG(sal)>9000
ordee BY AVG(sal);

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

-- over partition
SELECT enam, job, sal,SUM(sal) OVER(PARTITION BY deptno)
FROM emp;

-- conto chi sono ma, non so chi sono
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno;

-- conto quanti impiegati ci sono e anche chi sono
SELECT ename, job, sal,deptno,COUNT(*) OVER(PARTITION BY deptno) AS quanti_sono
FROM emp;

select ename, job, sal, deptno,
		COUNT(*) OVER(PARTITION BY deptno) AS quanti_sono,
		COUNT(*) OVER() AS conteggio_totale
FROM emp;

-- row_number()
SELECT ROW_NUMBER() OVER() AS numero_impiegato,
		ename, job, sal,deptno
		ROW_NUMBER() OVER (PARTITION BY deptno) AS num_imp_X_dipartimento
FROM emp;
		
-- report completo
SELECT ROW_NUMBER() AS numero_impiegato,
		ename,job, sal,deptno,
		ROW_NUMBER() OVER (PARTITION BY deptno) AS num_imp_X_dipartimento,
		SUM(sal) OVER(PARTITION BY deptno) AS sal_tot_X_dip,
		MAX(sal) OVER(parition BY job) AS sal_max_X_job,
		MAX(sal) OVER() AS sal_totale,
		COUNT(*) OVER() AS quanti_impiegati
FROM emp;

SELECT * FROM emp
WHERE sal( -- subquery < >= in between etc. deve ritornare una riga sola
		SELECT sal
		FROM emp
		WHERE ename = 'Daniel'
AND deptno=30;

UPDATE emp
SET sal=(SELECT da FROM emp WHERE empno=105) -- la subquery deve ritornare 1 valore solo
	job=(SELECT job FROM emp WHERE empno=105)
WHERE empno=114;


SELECT d.*, (SELECT ename FROM emp WHERE empno=100) AS steven
FROM dept d;


SELECT * FROM emp WHERE sal>5000;

SELECT * FROM
(SELECT * FROM emp WHERE sal >5000) AS X -- subquery coem tabella chiamata anche inile view
WHERE job='Account'

-- guadagnano più o meno della media
SELECT ename, sal,job,deptno,AVG(sal) OVER() AS media,
if(sal>(SELECT AVG(sal) FROM emp),'più della media', 'meno della media') AS piuOmeno
FROM emp;

-- stesso esempio con la "case"
SELECT ename, sal,job,deptno,AVG(sal) OVER() AS media,
	case
	when sal>(SELECT AVG(sal) FROM emp) then 'più della media'
	ELSE 'meno della media'
	END AS piOmeno
FROM emp;


-- chi fa lo stesso lavoro di Steven e qual'è il salario più alto di tutti

SELECT *, MAX(sal) OVER() AS salario_massimo
FROM emp WHERE job=(SELECT job FROM emp WHERE ename = 'Steven')
AND ename != 'Steven';


	-- quali sono i prodotti venduti dai venditori inglesi e il prezzo massimo
	
SELECT *, MAX(prod_price) OVER() AS "prezzo massimo di tutti i prodotti"
from products WHERE vend_id =(SELECT vend_id FROM vendors WHERE vend_country = 'England')


-- quali sono gli ordini fatti dal cliente che ha la mail sales@villagetoys.com ?
SELECT * from orders WHERE cust_id =(SELECT cust_id FROM customers WHERE cust_email = 'sales@villagetoys.com')

-- quali sono i prodotti ordinati da questo cliente

SELECT * FROM vendors WHERE vend_id IN
(SELECT vend_id
FROM products WHERE prod_id IN
									(SELECT prod_id 
									FROM orderitems 
									WHERE order_num=(SELECT order_num
															FROM orders
															where cust_id=(select cust_id 
																				FROM customers 
																				WHERE cust_email = 'sales@villagetoys.com'))));
																				
-- quali sono gli impiegati del dipartimento 20 che guadagnano aleno quanto gli impiegati del dipartimento 10?
-- visualizzare gli impiegati con il numero progressivo
SELECT ROW_NUMBER() OVER(), emp.*
FROM emp 
WHERE deptno=20
AND sal>=
	(SELECT MIN(sal)
	FROM emp
	WHERE deptno=10)
	
-- qual'è l'ordine che contiene il prodotto che costa di meno

SELECT * FROM orderitems
whwere prod_id IN(
SELECT prod_id FROM producta WHERE prod_price=(SELECT MIN(prod_price) FROM products)
);

SELECT * FROM orderitems WHERE item_price=(SELECT MIN(prod_price) FROM products )

-- -----------------------
-- le subquery devono ritornare un valore solo
-- nel caso dell'uguale si cambia con  in
-- nel caso dei > o < si usa >any o <any 
SELECT * FROM emp
WHERE sal IN (SELECT MIN(sal)
			FROM emp
			GROUP BY deptno
)

SELECT * FROM emp
WHERE sal > all (SELECT MIN(sal)
						FROM emp
						GROUP BY deptno
)

-- gli impiegati del dipeartimento 10 che guadagnano almeno quanto qualsiasi dipendente del dipartimento 30


SELECT * FROM emp
WHERE deptno=10
and sal>=ANY(SELECT sal FROM emp WHERE deptno=30);

-- visualizza il nome degli impiegati che lavorano in un dipartimento nel quale dipartimento
-- c'è un impiegato che ha il nome che contiene la lettera 't'

SELECT * FROM emp
WHERE ename like '%t%'
;

SELECT * FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%t%');

-- elencare tutti gli impiegati che non lavorano del dipartimento 30
-- ma che guadagnano più di ciauscun impiegato che lavora nel dipartimento 30

SELECT * FROM emp
WHERE deptno != 30 
AND sal > ANY (SELECT sal FROM emp WHERE deptno = 30);


SELECT ename, city
FROM emp e ,dept d
WHERE e.deptno=d.DEPTNO; -- condizione di join
-- prodotto cartesiano moltiplicazione di una riga della tabella X, tutte le altre da evite assolutamente


-- SQL standart iso qualcosa
SELECT ename, city
FROM emp e JOIN dept d-- la virgola viene sostituita dalla parola join
ON e.deptno=d.DEPTNO; -- la WHERE con la parola on

SELECT *
from emp e, dept d
WHERE e.DEPTNO=d.DEPTNO -- condizione di join
AND sal>5000;

SELECT *
FROM emp e JOIN dept d
ON e.DEPTNO=d.DEPTNO -- condizione di join
WHERE sal>5000;

SELECT *
FROM  customers c, orders d
WHERE c.cust_id=0.cust_id;

SELECT *
FROM vendors v JOIN products p
ON v.vend_id=p.vend_id;

-- quali sono gli ordini fatti dal cliente che ha la mail sales@villagetoys.com ?
SELECT * FROM orders WHERE cust_id=(SELECT cust_id FROM customers WHERE cust_email='sales@villagetoys.com');

SELECT *
FROM orders o, customers c
WHERE o.cust_id=c.cust_id
AND cust_email='sales@villagetoys.com';

-- chi è il venditore che ha venduto i prodotti al cliente con la mail sales@villagetoys.com ?

SELECT *
from customers c,orders o, orderitems oi, products p, vendors v
 WHERE c.cust_id=o.cust_id
 AND o.order_num=oi.ORDER_NUM
 AND oi.PROD_ID=p.prod_id
 AND p.vend_id=v.vend_id
 AND cust_email='sales@villagetoys.com';
 
 SELECT *
 FROM customers c JOIN orders o
 ON c.cust_id=o.cust_id
 JOIN orderitems oi
 ON o.order_num=oi.ORDER_NUM
 JOIN products p
 ON oi.PROD_ID=p.prod_id
 JOIN vendors v
 ON p.vend_id=v.vend_id
 WHERE cust_email='sales@villagetoys.com';
 
-- SELF JOIN
SELECT i.ENAME AS impiegato, 'lavora per', m.ENAME AS manager
FROM emp i, emp m
WHERE i.MGR=m.EMPNO;

-- in che città lavorano i dipendenti che fann i clerk
-- visuaizzare nome e città

SELECT *
FROM emp a, dept b
WHERE a.DEPTNO=b.DEPTNO
AND job = 'clerk';

-- chi è il cliente che ha ordinato il prodotto 'RGAN01'

SELECT *
FROM customers c, orders o, orderitems oi
WHERE c.cust_id=o.cust_id
AND o.order_num=oi.ORDER_NUM
AND oi.PROD_ID='RGAN01';

-- Visualizzare il nome del dipartimento dove ci sono almeno 6 lavoratori

SELECT d.DNAME, COUNT(*)
FROM dept d JOIN emp e
ON e.DEPTNO=d.DEPTNO
GROUP BY d.DNAME
HAVING COUNT(*)>=6;

-- trovare le persone che fanno lo stesso lavoro di chi di quelli che lavorano a londra
SELECT * FROM emp WHERE job IN
(SELECT job FROM emp e, dept d
WHERE e.DEPTNO=d.DEPTNO
AND city='London');

SELECT *
FROM emp e, dept d
WHERE e.DEPTNO=d.DEPTNO

SELECT * FROM emp e RIGHT OUTER JOIN dept d
ON e.DEPTNO=d.DEPTNO

SELECT * FROM dept d left OUTER JOIN emp e
ON d.DEPTNO=e.DEPTNO
WHERE empno IS NULL;

SELECT * FROM vendors v LEFT OUTER JOIN  products p
ON v.vend_id=p.vend_id
WHERE p.prod_id IS NULL;

SELECT * FROM customers c LEFT OUTER JOIN orders o
ON c.cust_id=o.cust_id
WHERE o.order_num IS NULL;

-- Quali prodotti sono stati venduti il 1 maggio e da chi?
-- chi sono gli impiegati che guadagano più della media del LORO STESSO	 dipartimento per ogni dipartimento

SELECT p.prod_name,p.prod_price,o.order_num,o.order_date,c.cust_name,v.vend_name
FROM orders o, customers c, vendors v, products p, orderitems oi
WHERE order_date = "2004-05-01"
AND o.cust_id=c.cust_id
AND o.order_num=oi.ORDER_NUM
AND oi.PROD_ID=p.prod_id
AND oi.PROD_ID=p.prod_id
AND p.vend_id=v.vend_id;


SELECT ename, job, city
FROM emp e, dept d
WHERE e.DEPTNO=d.DEPTNO;


SELECT ename, job, city, d.DEPTNO
FROM emp e RIGHT OUTER JOIN dept d
ON e.DEPTNO=d.DEPTNO
WHERE e.DEPTNO;


SELECT e.deptno, e,ename, e.sal
FROM emp
WHERE sal > (SELECT avg(sal) FROM emp
WHERE e.deptno = e.deptno
GROUP BY depno;


SELECT * FROM emp e
WHERE sal>(
				SELECT AVG(sal)
				FROM emp m
				WHERE e.DEPTNO=m.DEPTNO
				GROUP BY deptno;
				

SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.DEPTNO=d.DEPTNO
WHERE empno IS NULL;

-- tira fuori le righe quando la condizione di JOIN è falsa
SELECT * FROM dept d
WHERE NOT EXISTS( -- subquery correlata
	SELECT * FROM emp e
	WHERE e.DEPTNO=d.DEPTNO -- JOIN con una query esterna
);
	
	-- equivale a fare una join normale
SELECT * FROM dept d
WHERE EXISTS(-- subquery correlata
	SELECT * FROM emp e
	WHERE e.DEPTNO=d.DEPTNO -- JOIN con una query esterna
);

SELECT * FROM vendors v
WHERE NOT EXISTS(
	SELECT * FROM products p
	WHERE p.vend_id=v.vend_id
);


SELECT *
FROM vendors v LEFT OUTER JOIN products p
ON v.vend_id=p.vend_id
WHERE p.vend_id IS NULL;


SELECT * FROM customers c
WHERE NOT EXISTS(

	SELECT * FROM orders o
	WHERE c.cust_id=o.cust_id
	);
	
SELECT * FROM customers c LEFT OUTER JOIN orders o
ON c.cust_id=o.cust_id
WHERE o.cust_id IS NULL;

-- CTE common table expression
WITH q AS 
(SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno)
SELECT * FROM q
WHERE deptno = 30;

-- tabelle virtuali
WITH 
g AS 
(SELECT deptno, SUM(sal) AS sommaSal
FROM emp
GROUP BY deptno)
SELECT * 
FROM g, dept d
WHERE g.deptno=d.DEPTNO;

WITH 
s1 AS
(SELECT deptno, SUM(sal) AS sommaSal
FROM emp
GROUP BY deptno),
s2 AS (SELECT * FROM dept)
SELECT s1.sommaSal, s2.dname
FROM s1,s2
WHERE s1.deptno = s2.deptno;



SELECT * FROM emp
WHERE sal>
		(SELECT AVG(sal) as media
		FROM emp x
		WHERE e.deptno=x.DEPTNO
		GROUP BY x.deptno);
	

		
WITH x AS
(SELECT deptno, AVG(sal) AS media
FROM emp x
GROUP BY x.DEPTNO)

SELECT * FROM emp e,X
WHERE e.DEPTNO=x.DEPTNO
AND e.sal>x.media;



-- UNION
--ci devono essere lo stesso numiero di colonne , e preferibilmente dello stesso tipo

SELECT deptno, dname, FROM dept
UNION
SELECT deptno, ename FROM emp;



-- la union filtra i duplicati 
-- la union all mostra anche i duplicati
SELECT deptno AS 'numero dipartimento', COUNT(*) AS 'numero impiegati' FROM emp GROUP BY deptno
UNION all
SELECT 'impiegati totali', COUNT(*) FROM emp;

SELECT deptno FROM dept
UNION all
SELECT deptno FROM emp;


-- trovare gli impiegati che sono stati assunti dopo il 20-07-1998
-- e il loro manager 

WITH x AS
(SELECT ename, hire_date
FROM emp x
GROUP BY x.hire_date
WHERE hire_date > "20-07-1998");