Aprendiendo-Db--Sql-/script_emplea.sql at main · Carchaloneo22/Aprendiendo-Db--Sql- · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
DROP TABLE tab_nomina;
DROP TABLE tab_emplea;
DROP TABlE tab_cargos;
DROP TYPE DATOS_EMP;

CREATE TYPE DATOS_EMP AS
(
    dir_emplea  VARCHAR,
    tel_emplea  DECIMAL(10,0),
    val_edad    DECIMAL(2,0)
);

CREATE TABLE tab_cargos
(
    id_cargo    DECIMAL(1,0)        NOT NULL,
    nom_cargo   VARCHAR             NOT NULL,
    PRIMARY KEY(id_cargo)
);
INSERT INTO tab_cargos VALUES(1,'Gerente Gral.');
INSERT INTO tab_cargos VALUES(2,'Subgerente');
INSERT INTO tab_cargos VALUES(3,'Dirctor');
INSERT INTO tab_cargos VALUES(4,'Asistonto');
INSERT INTO tab_cargos VALUES(5,'Auxiliar');

CREATE TABLE tab_emplea
(
    id_emplea   DECIMAL(10,0)       NOT NULL,
    nom_emplea  VARCHAR             NOT NULL,
    ape_emplea  VARCHAR             NOT NULL,
    id_cargo    DECIMAL(1,0)        NOT NULL,
    datos       DATOS_EMP           NOT NULL,
    PRIMARY KEY(id_emplea),
    FOREIGN KEY(id_cargo)   REFERENCES tab_cargos(id_cargo)
);
INSERT INTO tab_emplea VALUES(1000,'Carlos EduARDOOO','Perez Rueda',1,ROW('Calle 20 carrera 32',3503421111,61));
INSERT INTO tab_emplea VALUES(2000,'María Camila','Perez Barrera',2,ROW('Avenida Bendevere San Ag. Guadalix',3122222222,28));
INSERT INTO tab_emplea VALUES(3000,'Laura Juliana','Perez Barrera',3,ROW('Carrera 54 Calle 138B',3133333333,26));
INSERT INTO tab_emplea VALUES(4000,'Paula Sofía','Perez Moscoso',4,ROW('Arboretto',3144444444,15));
INSERT INTO tab_emplea VALUES(5000,'Evila MARIA','Rueda Navas',5,ROW(NULL,NULL,NULL));

CREATE TABLE tab_nomina
(
    id_ano      DECIMAL(4,0)        NOT NULL,
    id_mes      DECIMAL(2,0)        NOT NULL,
    id_emplea   DECIMAL(10,0)       NOT NULL,
    val_neto    DECIMAL(10,0)       NOT NULL,
    PRIMARY KEY(id_ano,id_mes,id_emplea),
    FOREIGN KEY(id_emplea)   REFERENCES tab_emplea(id_emplea)
);
INSERT INTO tab_nomina VALUES(2025,1,1000,10000000);
INSERT INTO tab_nomina VALUES(2025,2,1000,12000000);
INSERT INTO tab_nomina VALUES(2025,3,1000,15000000);
INSERT INTO tab_nomina VALUES(2025,4,1000,10000000);
INSERT INTO tab_nomina VALUES(2025,5,1000,10000000);
INSERT INTO tab_nomina VALUES(2025,6,1000,10000000);

INSERT INTO tab_nomina VALUES(2025,1,2000,8000000);
INSERT INTO tab_nomina VALUES(2025,2,2000,8000000);
INSERT INTO tab_nomina VALUES(2025,3,2000,8000000);
INSERT INTO tab_nomina VALUES(2025,4,2000,8000000);
INSERT INTO tab_nomina VALUES(2025,5,2000,8000000);
INSERT INTO tab_nomina VALUES(2025,6,2000,8000000);

INSERT INTO tab_nomina VALUES(2025,1,3000,15000000);
INSERT INTO tab_nomina VALUES(2025,2,3000,20000000);
INSERT INTO tab_nomina VALUES(2025,3,3000,25000000);
INSERT INTO tab_nomina VALUES(2025,4,3000,15000000);
INSERT INTO tab_nomina VALUES(2025,5,3000,15000000);
INSERT INTO tab_nomina VALUES(2025,6,3000,12000000);

INSERT INTO tab_nomina VALUES(2025,1,4000,5000000);
INSERT INTO tab_nomina VALUES(2025,2,4000,5000000);
INSERT INTO tab_nomina VALUES(2025,3,4000,5000000);
INSERT INTO tab_nomina VALUES(2025,4,4000,5000000);
INSERT INTO tab_nomina VALUES(2025,5,4000,5000000);
INSERT INTO tab_nomina VALUES(2025,6,4000,5000000);

INSERT INTO tab_nomina VALUES(2025,1,5000,4000000);
INSERT INTO tab_nomina VALUES(2025,2,5000,4000000);
INSERT INTO tab_nomina VALUES(2025,3,5000,4000000);
INSERT INTO tab_nomina VALUES(2025,4,5000,4000000);
INSERT INTO tab_nomina VALUES(2025,5,5000,4000000);
INSERT INTO tab_nomina VALUES(2025,6,5000,4000000);