
create table if not exists dept (
 dept_no  int,
 dnom     varchar(14) not null unique,
 loc      varchar(14),
 primary key (dept_no) ) ;


insert into dept values (10, 'comptabilitat', 'sevilla');
insert into dept values (20, 'investigació', 'madrid');
insert into dept values (30, 'vendes', 'barcelona');
insert into dept values (40, 'producció', 'bilbao');


create table if not exists emp (
 emp_no    int  ,
 cognom    varchar (10) not null,
 ofici     varchar (10),
 cap       int  ,
 data_alta date,
 salari    int  ,
 comissio  int  ,
 dept_no   int   not null,
 primary key (emp_no),
 foreign key (dept_no) references  dept(dept_no)) ;




insert into  emp values (7369,'sánchez','empleat',7902, '1980-12-17', 104000,null,20);
insert into  emp values (7499,'arroyo','venedor',7698, '1980-02-20', 208000,39000,30);
insert into  emp values (7521,'sala','venedor',7698, '1981-02-22', 162500,65000,30);
insert into  emp values (7566,'jiménez','director',7839, '1981-04-02', 386750,null,20);
insert into  emp values (7654,'martín','venedor',7698, '1981-09-29', 162500,182000,30);
insert into  emp values (7698,'negro','director',7839, '1981-05-01', 370500,null,30);
insert into  emp values (7782,'cerezo','director',7839, '1981-06-09', 318500,null,10);
insert into  emp values (7788,'gil','analista',7566, '1981-11-09', 390000,null,20);
insert into  emp values (7839,'rey','president',null, '1981-11-17', 650000,null,10);
insert into  emp values (7844,'tovar','venedor',7698, '1981-09-08', 195000,0,30);
insert into  emp values (7876,'alonso','empleat',7788, '1981-09-23', 143000,null,20);
insert into  emp values (7900,'jimeno','empleat',7698, '1981-12-03', 123500,null,30);
insert into  emp values (7902,'fernández','analista',7566, '1981-12-03', 390000,null,20);
insert into  emp values (7934,'muñoz','empleat',7782, '1982-01-23', 169000,null,10);


alter table  emp
add foreign key (cap) references emp(emp_no);



create table if not exists  client (
 client_cod          int   primary key,
 nom                 varchar (45) not null,
 adreca              varchar (40) not null,
 ciutat              varchar (30) not null,
 estat               varchar (2),
 codi_postal         varchar (9) not null,
 area                int,
 telefon             varchar (9),
 repr_cod            int  ,
 limit_credit        decimal(9,2)  ,
 observacions        text,
 foreign key (repr_cod) references  emp(emp_no));




insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (100, 'jocksports', '345 viewridge', 'belmont', 'ca', '96711', 415,
        '598-6609', 7844, 5000, 
        'very friendly people to work with -- sales rep likes to be called mike.');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (101, 'tkb sport shop', '490 boli rd.', 'redwood ciutat', 'ca', '94061', 415,
        '368-1223', 7521, 10000, 
        'rep called 5/8 about change in order - contact shipping.');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (102, 'vollyrite', '9722 hamilton', 'burlingame', 'ca', '95133', 415,
        '644-3341', 7654, 7000, 
        'company doing heavy promotion beginning 10/89. prepare for large orders during winter.');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (103, 'just tennis', 'hillview mall', 'burlingame', 'ca', '97544', 415,
        '677-9312', 7521, 3000, 
        'contact rep about new line of tennis rackets.');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (104, 'every mountain', '574 surry rd.', 'cupertino', 'ca', '93301', 408,
        '996-2323', 7499, 10000, 
        'client with high market share (23%) due to aggressive advertising.');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (105, 'k + t sports', '3476 el paseo', 'santa clara', 'ca', '91003', 408,
        '376-9966', 7844, 5000, 
        'tends to order large amounts of merchandise at once. accounting is considering raising their credit limit. usually pays on time.');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (106, 'shape up', '908 sequoia', 'palo alto', 'ca', '94301', 415,
        '364-9777', 7521, 6000, 
        'support intensive. orders small amounts (< 800) of merchandise at a time.');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (107, 'women sports', 'valco village', 'sunnyvale', 'ca', '93301', 408,
        '967-4398', 7499, 10000, 
        'first sporting goods store geared exclusively towards women. unusual promotion al style and very willing to take chances towards new productes!');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (108, 'north woods health and fitness supply center', '98 lone pine way', 'hibbing', 'mn', '55649', 612,
        '566-9123', 7844, 8000, '');
insert into  client (client_cod, nom, adreca, ciutat, estat, codi_postal, area, 
                      telefon, repr_cod, limit_credit, observacions)
values (109, 'springfield nuclear power plant', '13 percebe str.', 'springfield', 'nt', '0000', 636,
        '999-6666', null, 10000, '');



create table if not exists  producte (
 prod_num     int primary key,
 descripcio   varchar (30) not null  unique);


insert into  producte (prod_num, descripcio)
values (100860, 'ace tennis racket i');
insert into  producte (prod_num, descripcio)
values (100861, 'ace tennis racket ii');
insert into  producte (prod_num, descripcio)
values (100870, 'ace tennis balls-3 pack');
insert into  producte (prod_num, descripcio)
values (100871, 'ace tennis balls-6 pack');
insert into  producte (prod_num, descripcio)
values (100890, 'ace tennis net');
insert into  producte (prod_num, descripcio)
values (101860, 'sp tennis racket');
insert into  producte (prod_num, descripcio)
values (101863, 'sp junior racket');
insert into  producte (prod_num, descripcio)
values (102130, 'rh: "guide to tennis"');
insert into  producte (prod_num, descripcio)
values (200376, 'sb energy bar-6 pack');
insert into  producte (prod_num, descripcio)
values (200380, 'sb vita snack-6 pack');


create table if not exists  comanda  (
 com_num             int   primary key,
 com_data            date,
 com_tipus           char (1) check (com_tipus in ('a','b','c')),
 client_cod          int   not null,
 data_tramesa        date,
 total               decimal(8,2)  ,
   
 foreign key (client_cod) references  client(client_cod) );




insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (610, '1987-01-07', 'a', 101, '1987-01-08', 101.4);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (611, '1987-01-11', 'b', 102,'1987-01-11', 45);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (612, '1987-01-15', 'c', 104, '1987-01-20', 5860);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (601, '1986-05-01', 'a', 106, '1986-05-30', 2.4);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (602, '1986-06-05', 'b', 102, '1986-06-20', 56);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (604, '1986-06-15', 'a', 106, '1986-06-30', 698);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (605, '1986-07-14', 'a', 106,  '1986-07-30', 8324);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (606, '1986-07-14', 'a', 100,  '1986-07-30', 3.4);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (609, '1986-08-01', 'b', 100,  '1986-08-15', 97.5);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (607, '1986-07-18', 'c', 104,  '1986-07-18', 5.6);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (608, '1986-07-25', 'c', 104,  '1986-07-25', 35.2);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (603, '1986-06-05', null, 102, '1986-06-05', 224);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (620, '1987-03-12', null, 100, '1987-03-12', 4450);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (613, '1987-02-01', null, 108, '1987-02-01', 6400);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (614, '1987-02-01', null, 102, '1987-02-05', 23940);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (616, '1987-02-03', null, 103, '1987-02-10', 764);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (619, '1987-02-22', null, 104, '1987-02-04', 1260);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (617, '1987-02-05', null, 104, '1987-03-03', 46370);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (615, '1987-02-01', null, 107, '1987-02-06', 710);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (618, '1987-02-15', 'a', 102, '1987-03-06', 3510.5);
insert into  comanda (com_num, com_data, com_tipus, client_cod, data_tramesa, total)
values (621, '1987-03-15', 'a', 100, '1987-01-01', 730);



create table if not exists  detall  (
 com_num             int  ,
 detall_num         int  ,
 prod_num            int  not null,
 preu_venda          decimal(8,2)  ,
 quantitat           int,
 import              decimal(8,2),
 constraint detall_pk primary key (com_num,detall_num),
 foreign key (com_num) references  comanda(com_num),
 foreign key (prod_num) references  producte(prod_num));





insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (610, 3, 100890, 58, 1, 58);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (611, 1, 100861, 45, 1, 45);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (612, 1, 100860, 30, 100, 3000);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (601, 1, 200376, 2.4, 1, 2.4);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (602, 1, 100870, 2.8, 20, 56);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (604, 1, 100890, 58, 3, 174);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (604, 2, 100861, 42, 2, 84);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (604, 3, 100860, 44, 10, 440);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (603, 2, 100860, 56, 4, 224);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (610, 1, 100860, 35, 1, 35);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (610, 2, 100870, 2.8, 3, 8.4);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (613, 4, 200376, 2.2, 200, 440);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (614, 1, 100860, 35, 444, 15540);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (614, 2, 100870, 2.8, 1000, 2800);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (612, 2, 100861, 40.5, 20, 810);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (612, 3, 101863, 10, 150, 1500);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (620, 1, 100860, 35, 10, 350);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (620, 2, 200376, 2.4, 1000, 2400);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (620, 3, 102130, 3.4, 500, 1700);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (613, 1, 100871, 5.6, 100, 560);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (613, 2, 101860, 24, 200, 4800);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (613, 3, 200380, 4, 150, 600);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (619, 3, 102130, 3.4, 100, 340);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 1, 100860, 35, 50, 1750);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 2, 100861, 45, 100, 4500);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (614, 3, 100871, 5.6, 1000, 5600);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (616, 1, 100861, 45, 10, 450);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (616, 2, 100870, 2.8, 50, 140);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (616, 3, 100890, 58, 2, 116);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (616, 4, 102130, 3.4, 10, 34);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (616, 5, 200376, 2.4, 10, 24);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (619, 1, 200380, 4, 100, 400);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (619, 2, 200376, 2.4, 100, 240);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (615, 1, 100861, 45, 4, 180);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (607, 1, 100871, 5.6, 1, 5.6);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (615, 2, 100870, 2.8, 100, 280);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 3, 100870, 2.8, 500, 1400);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 4, 100871, 5.6, 500, 2800);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 5, 100890, 58, 500, 29000);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 6, 101860, 24, 100, 2400);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 7, 101863, 12.5, 200, 2500);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 8, 102130, 3.4, 100, 340);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 9, 200376, 2.4, 200, 480);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (617, 10, 200380, 4, 300, 1200);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (609, 2, 100870, 2.5, 5, 12.5);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (609, 3, 100890, 50, 1, 50);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (618, 1, 100860, 35, 23, 805);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (618, 2, 100861, 45.11, 50, 2255.5);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (618, 3, 100870, 45, 10, 450);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (621, 1, 100861, 45, 10, 450);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (621, 2, 100870, 2.8, 100, 280);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (615, 3, 100871, 5, 50, 250);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (608, 1, 101860, 24, 1, 24);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (608, 2, 100871, 5.6, 2, 11.2);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (609, 1, 100861, 35, 1, 35);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (606, 1, 102130, 3.4, 1, 3.4);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (605, 1, 100861, 45, 100, 4500);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (605, 2, 100870, 2.8, 500, 1400);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (605, 3, 100890, 58, 5, 290);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (605, 4, 101860, 24, 50, 1200);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (605, 5, 101863, 9, 100, 900);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (605, 6, 102130, 3.4, 10, 34);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (612, 4, 100871, 5.5, 100, 550);
insert into  detall (com_num, detall_num, prod_num, preu_venda, quantitat, import)
values (619, 4, 100871, 5.6, 50, 280);



