Diferencia entre revisiones de «Usuario:ManuelRomero/ProgramacionWeb/INAEM2017/BasesDatos/SQL/ejercicios»

CREATE TABLE REGIONS(
    REGION_ID NUMERIC,
    REGION_NAME VARCHAR(25),
    PRIMARY KEY (REGION_ID));
 
 
CREATE TABLE COUNTRIES(
    COUNTRY_ID CHAR(2),
    COUNTRY_NAME VARCHAR(40),
    REGION_ID NUMERIC,
    PRIMARY KEY (COUNTRY_ID),
    FOREIGN KEY(REGION_ID) REFERENCES REGIONS(REGION_ID));				
 
 
CREATE TABLE LOCATIONS (
     LOCATION_ID NUMERIC(4),
     STREET_ADDRESS VARCHAR(40),
     POSTAL_CODE VARCHAR(12),
     CITY VARCHAR(30) NOT NULL,
     STATE_PROVINCE VARCHAR(25),
     COUNTRY_ID CHAR(2),
     PRIMARY KEY(LOCATION_ID),
     FOREIGN KEY(COUNTRY_ID) REFERENCES COUNTRIES(COUNTRY_ID)); 
 
 
CREATE TABLE DEPARTMENTS(
   DEPARTMENT_ID NUMERIC(4),
   DEPARTMENT_NAME VARCHAR(30) NOT NULL,
   MANAGER_ID NUMERIC(6),
   LOCATION_ID NUMERIC(4),
   PRIMARY KEY(DEPARTMENT_ID),
   FOREIGN KEY(LOCATION_ID) REFERENCES LOCATIONS(LOCATION_ID));
 
 
CREATE TABLE JOBS(
   JOB_ID VARCHAR(10),
   JOB_TITLE VARCHAR(35) NOT NULL,
   MIN_SALARY NUMERIC(6),
   MAX_SALARY NUMERIC(6),
   PRIMARY KEY(JOB_ID));
 
 
CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID NUMERIC(6),
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(25) NOT NULL,
    EMAIL VARCHAR(25) NOT NULL,
    PHONE_numeric VARCHAR(20),
    HIRE_DATE DATE NOT NULL,
    JOB_ID VARCHAR(10) NOT NULL,
    SALARY NUMERIC(8,2),
    COMMISSION_PCT NUMERIC(2,2),
    MANAGER_ID NUMERIC(6),
    DEPARTMENT_ID NUMERIC(4),
    PRIMARY KEY(EMPLOYEE_ID),
    FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
    FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID),
    CONSTRAINT EMP_EMAIL_UK UNIQUE(EMAIL));
 
 
CREATE TABLE JOB_HISTORY(
  EMPLOYEE_ID NUMERIC(6),
  START_DATE DATE NOT NULL,
  END_DATE DATE NOT NULL,
  JOB_ID VARCHAR(10) NOT NULL,
  DEPARTMENT_ID NUMERIC(4),
  PRIMARY KEY(EMPLOYEE_ID, JOB_ID),
  FOREIGN KEY(EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID),
  FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
  FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID));
 
CREATE TABLE JOB_GRADES(
     GRADE_LEVEL VARCHAR(3),
     LOWEST_SAL NUMERIC,
     HIGHEST_SAL NUMERIC);

INSERT INTO REGIONS VALUES (1, 'Europe');
INSERT INTO REGIONS VALUES (2, 'Americas');
INSERT INTO REGIONS VALUES (3, 'Asia');
INSERT INTO REGIONS VALUES (4, 'Middle East and Africa');
 
 
 
INSERT INTO COUNTRIES VALUES( 'CA', 'Canada', 2);
INSERT INTO COUNTRIES VALUES( 'DE', 'Germany', 1);
INSERT INTO COUNTRIES VALUES( 'UK', 'United Kingdom', 1);
INSERT INTO COUNTRIES VALUES( 'US', 'United States of America', 2);
 
 
 
INSERT INTO LOCATIONS VALUES( 1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO LOCATIONS VALUES( 1500, '2001 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO LOCATIONS VALUES( 1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO LOCATIONS VALUES( 1800, '460 Bloor St. W.', 'ON M5S 1X8', 'Toronto', 'Ontario', 'CA');
INSERT INTO LOCATIONS VALUES( 2500, 'Magdalen Centre, The Ofxord Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
 
 
 
 
INSERT INTO DEPARTMENTS VALUES (10, 'Administration', 200, 1700);
INSERT INTO DEPARTMENTS VALUES (20, 'Marketing', 201, 1800);
INSERT INTO DEPARTMENTS VALUES (50, 'Shipping', 124, 1500);
INSERT INTO DEPARTMENTS VALUES (60, 'IT', 103, 1400);
INSERT INTO DEPARTMENTS VALUES (80, 'Sales', 149, 2500);
INSERT INTO DEPARTMENTS VALUES (90, 'Executive', 100, 1700);
INSERT INTO DEPARTMENTS VALUES (110, 'Accounting', 205, 1700);
INSERT INTO DEPARTMENTS VALUES (190, 'Contracting', NULL, 1700);
 
 
 
INSERT INTO JOBS VALUES('AD_PRES', 'President', 20000, 40000);
INSERT INTO JOBS VALUES('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO JOBS VALUES('AD_ASST', 'Administration Assistant', 3000, 6000);
INSERT INTO JOBS VALUES('AC_MGR', 'Accounting Manager', 8200, 16000);
INSERT INTO JOBS VALUES('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
INSERT INTO JOBS VALUES('SA_MAN', 'Sales Manager', 10000, 20000);
INSERT INTO JOBS VALUES('SA_REP', 'Sales Representative', 6000, 12000);
INSERT INTO JOBS VALUES('ST_MAN', 'Stock Manager', 5500, 8500);
INSERT INTO JOBS VALUES('ST_CLERK', 'Stock Clerk', 2000, 5000);
INSERT INTO JOBS VALUES('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO JOBS VALUES('MK_MAN', 'Marketing Manager', 9000, 15000);
INSERT INTO JOBS VALUES('MK_REP', 'Marketing Representative', 4000, 9000);
 
 
 
INSERT INTO EMPLOYEES VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567',  DATE '1987-06-17','AD_PRES', 24000, NULL, NULL, 90);
INSERT INTO EMPLOYEES VALUES (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',  DATE '1989-09-21','AD_VP', 17000, NULL, 100, 90); 
INSERT INTO EMPLOYEES VALUES (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569',  DATE '1993-01-13','AD_VP', 17000, NULL, 100, 90); 
INSERT INTO EMPLOYEES VALUES (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567',  DATE '1990-01-03','IT_PROG', 9000, NULL, 102, 60); 
INSERT INTO EMPLOYEES VALUES (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568',  DATE '1991-05-21','IT_PROG', 6000, NULL, 103, 60); 
INSERT INTO EMPLOYEES VALUES (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567',  DATE '1999-02-07','IT_PROG', 4200, NULL, 103, 60); 
INSERT INTO EMPLOYEES VALUES (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234',  DATE '1999-11-16','ST_MAN', 5800, NULL, 100, 50); 
INSERT INTO EMPLOYEES VALUES (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009',  DATE '1995-10-15','ST_CLERK', 3500, NULL, 124, 50); 
INSERT INTO EMPLOYEES VALUES (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994',  DATE '1997-01-29','ST_CLERK', 3100, NULL, 124, 50); 
INSERT INTO EMPLOYEES VALUES (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874',  DATE '1998-03-15','ST_CLERK', 2600, NULL, 124, 50); 
INSERT INTO EMPLOYEES VALUES (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004',  DATE '1998-07-09','ST_CLERK', 2500, NULL, 124, 50); 
INSERT INTO EMPLOYEES VALUES (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018',  DATE '2000-01-29','SA_MAN', 10500, .2, 100, 80); 
INSERT INTO EMPLOYEES VALUES (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267',  DATE '1996-05-11','SA_REP', 11000, .3, 149, 80); 
INSERT INTO EMPLOYEES VALUES (176, 'Jonathan', 'Taylor', 'JTAYLOR', '011.44.1644.429265',  DATE '1998-03-24','SA_REP', 8600, .2, 149, 80); 
INSERT INTO EMPLOYEES VALUES (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263',  DATE '1999-05-24','SA_REP', 7000, .15, 149, NULL); 
INSERT INTO EMPLOYEES VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444',  DATE '1987-09-17','AD_ASST', 4400, NULL, 101, 10); 
INSERT INTO EMPLOYEES VALUES (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555',  DATE '1996-02-17','MK_MAN', 13000, NULL, 100, 20); 
INSERT INTO EMPLOYEES VALUES (202, 'Pat', 'Fay', 'PFAY', '603.123.6666',  DATE '1997-08-17','MK_REP', 6000, NULL, 201, 20); 
INSERT INTO EMPLOYEES VALUES (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080',  DATE '1994-06-07','AC_MGR', 12000, NULL, 101, 110); 
INSERT INTO EMPLOYEES VALUES (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181',  DATE '1994-06-07','AC_ACCOUNT', 8300, NULL, 205, 110);  
 
 
INSERT INTO JOB_HISTORY VALUES( 102,DATE '1993-01-13',DATE  '1998-07-24', 'IT_PROG', 60);
INSERT INTO JOB_HISTORY VALUES( 101,DATE '1989-09-21',DATE  '1993-10-27', 'AC_ACCOUNT', 110);
INSERT INTO JOB_HISTORY VALUES( 101,DATE '1993-10-28',DATE  '1997-03-15', 'AC_MGR', 110);
INSERT INTO JOB_HISTORY VALUES( 201,DATE '1996-06-02', DATE '1999-12-19', 'MK_REP', 20);
INSERT INTO JOB_HISTORY VALUES( 144,DATE '1998-03-24',DATE '1999-12-31', 'ST_CLERK', 50);
INSERT INTO JOB_HISTORY VALUES( 142,DATE '1997-01-29',DATE  '1999-12-31', 'ST_CLERK', 50);
INSERT INTO JOB_HISTORY VALUES( 200,DATE '1987-09-17',DATE '1993-06-17', 'AD_ASST', 90);
INSERT INTO JOB_HISTORY VALUES( 176,DATE '1998-03-24',DATE '1998-12-31', 'SA_REP', 80);
INSERT INTO JOB_HISTORY VALUES( 176,DATE '1999-01-01', DATE '1999-12-31', 'SA_MAN', 80);
INSERT INTO JOB_HISTORY VALUES( 200,DATE '1994-07-01',DATE  '1998-12-31', 'AC_ACCOUNT', 90);
 
 
 
INSERT INTO JOB_GRADES VALUES('A', 1000, 2999);
INSERT INTO JOB_GRADES VALUES('B', 3000, 5999);
INSERT INTO JOB_GRADES VALUES('C', 6000, 9999);
INSERT INTO JOB_GRADES VALUES('D', 10000, 14999);
INSERT INTO JOB_GRADES VALUES('E', 15000, 24999);
INSERT INTO JOB_GRADES VALUES('F', 25000, 40000);

DROP TABLE JOB_GRADES;
DROP TABLE JOB_HISTORY;
DROP TABLE EMPLOYEES;
DROP TABLE JOBS;
DROP TABLE DEPARTMENTS;
DROP TABLE LOCATIONS;
DROP TABLE COUNTRIES;
DROP TABLE REGIONS;