Aula Banco de Dados Anhanguera I Revisao Prova Resolvida

...

(3, 3, 14, 'JOSE ROBERTO DOS SANTOS', 'RUA DOS CONTABILISTAS 4-17', 'NUCLEO GASPARINI', '17022-500', '825.130.588-87', ' ', 3),

(4, 1, 14, 'PAULO ROBERTO CAMARGO MAIA', 'ALAMEDA TROIA, 7-20', 'STA. EDWIRGES', '17064-570', '959.266.078-68', ' ', 4),

(5, 1, 2, 'CELSO RODRIGUES', 'ANTIGA 6 N.217.R.MARIA BIGHETI LIMAO2.17', 'NOVA BAURU', '17021-370', '040.224.648-95', ' ', 5),

(6, 3, 2, 'FERNANDO VIEGAS', 'R:PRUDENTE DE MORAES 4-27', 'VILA SOUTO', '17051-170', '708.403.008-59', ' ', 6),

(7, 5, 17, 'JOSE DIAS JUNIOR', 'R: DR. ANTONIO XAVIER DE MENDONCA, 9-48', 'JD. DONA SARA', '17012-385', '436.986.978-15', ' ', 7),

(8, 4, 1, 'JOAO ROGER DE SANTIS GUEDES', 'RUA BATISTA DE CARVALHO N§ 10-46', 'CENTRO', '17010-000', '959.520.558-34', ' ', 8),

(9, 4, 3, 'GUARACY FRANCISCO INGRACIA', 'R:JOAQUIM MARQUES FIGUEREDO N§5-25', 'DIST.INDUSTRIAL', '17000-000', '012.536.468-72', ' ', 9),

(10, 1, 2, 'MARCOS ANTONIO DO NASCIMENTO', 'R:4 N§119', 'ORLANDO BAGGIO', '16500-000', '015.365.278-07', ' ', 10);

DROP TABLE IF EXISTS segmentos;

CREATE TABLE IF NOT EXISTS segmentos (

Seg_Codigo smallint(5) unsigned NOT NULL AUTO_INCREMENT,

Seg_Descricao varchar(40) DEFAULT NULL,

PRIMARY KEY (Seg_Codigo)

) ;

REPLACE INTO segmentos (Seg_Codigo, Seg_Descricao) VALUES

(1, 'ATACADO'),

(2, 'MARCENARIA'),

(3, 'VAREJO'),

(4, 'CONSTRUTORA'),

(5, 'PISO'),

(10, 'ORGAO PUBLICO'),

(11, 'FORNECEDOR'),

(12, 'PEDREIRO/ENCANADOR'),

(13, 'DEVOLUCAO'),

(14, 'CLIENTE'),

(15, 'INSTALADOR'),

(16, 'OUTROS'),

(17, 'CLIENTE PREFERENCIAL'),

(18, 'CLIENTE EM DEBITO'),

(19, 'CASA EM CONSTRUÇÃO'),

(20, 'CLIENTE VIP'),

(21, 'PERFURADOR DE POCO'),

(22, 'CASA EM REFORMA'),

(23, 'PARCEIRO DE NEGOCIOS');

DROP TABLE IF EXISTS vendas;

CREATE TABLE IF NOT EXISTS vendas (

Vnd_Numero mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

Vend_Codigo smallint(5) unsigned NOT NULL,

Cli_Codigo mediumint(8) unsigned NOT NULL,

Vnd_Data date DEFAULT NULL,

Vnd_Subtotal float(10,2) DEFAULT NULL,

Vnd_PercDesconto float(5,3) DEFAULT NULL,

Vnd_NumeroWeb mediumint(8) unsigned DEFAULT NULL,

PRIMARY KEY (Vnd_Numero),

KEY vendas_FKIndex1 (Cli_Codigo),

KEY vendas_FKIndex2 (Vend_Codigo)

) ;

REPLACE INTO vendas (Vnd_Numero, Vend_Codigo, Cli_Codigo, Vnd_Data, Vnd_Subtotal, Vnd_PercDesconto, Vnd_NumeroWeb) VALUES

(27905, 5, 1, '2013-01-04', 219.33, 0.000, 0),

(27906, 8, 2, '2013-01-04', 144.87, 10.264, 0),

(27907, 8, 3, '2013-01-04', 38.16, 0.000, 0),

(27908, 5, 4, '2013-01-04', 52.44, 0.000, 0),

(27909, 7, 5, '2013-01-04', 475.30, 0.000, 0),

(27910, 3, 6, '2013-01-04', 29.72, 3.769, 0),

(27911, 8, 7, '2013-01-04', 569.18, 0.395, 0),

(27912, 5, 8, '2013-01-04', 75.90, 0.000, 0),

(27913, 8, 9, '2013-01-04', 496.79, 0.000, 0),

(27914, 8, 10, '2013-01-04', 62.17, 3.490, 0),

(27915, 8, 5, '2013-01-04', 14.48, 0.000, 0),

(27916, 3, 5, '2013-01-04', 284.40, 12.096, 0),

(27917, 5, 6, '2013-01-04', 100.00, 0.000, 0),

(27918, 8, 7, '2013-01-04', 69.73, 0.000, 0),

(27919, 8, 8, '2013-01-04', 66.57, 0.000, 0),

(27920, 3, 9, '2013-01-04', 29.30, 0.000, 0),

(27921, 8, 10, '2013-01-04', 12.60, 0.000, 0),

(27922, 5, 2, '2013-01-04', 30.75, 0.000, 0),

(27923, 8, 3, '2013-01-04', 23.71, 2.995, 0),

(27924, 5, 6, '2013-01-04', 9.43, 0.000, 0),

(27925, 5, 8, '2013-01-04', 324.45, 0.000, 0);

DROP TABLE