daily pastebin goal
8%
SHARE
TWEET

one more INSERT

11eimilia11 Feb 13th, 2018 74 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. use ichibasupermarket;
  2.  
  3. -- povoando produtos
  4.  
  5. /*
  6.     Foreign keys
  7.  
  8.     foreign key (id_unidade) references unidade (cod),
  9.     foreign key (id_marca) references marca (codmarca),
  10.     foreign key (id_ncm) references ncm (id),
  11.     foreign key (id_categoria) references categoria (codcategoria),
  12.     foreign key (id_subcategoria) references subcategoria (codsubcategoria),
  13.     foreign key (id_fornecedor) references fornecedor (cod)
  14.    
  15. */
  16.  
  17.  
  18. /*
  19.     Id unidade
  20.  
  21.  unidade (cod, descricao , sigla)
  22.  
  23. ('0001', 'quilogramas', 'kg'),
  24. ('0002' , 'mililitros', 'mL'),
  25. ('0003', 'gramas' , 'g'),
  26. ('0004', 'litros' , 'L'),
  27. ('0005', 'miligramas' , 'mg');
  28.  
  29.     ID marca
  30.    
  31.    
  32.  marca ( codmarca , descricaomarca )
  33.  
  34. ('0001', 'Sadia'),
  35. ('0002' , 'Knorr'),
  36. ('0003' , 'Camponesa'),
  37. ('0004' , 'Kicaldo'),
  38. ('0005' , 'Vitarela'),
  39. ('0006' , 'Bauduco'),
  40. ('0007', 'OMO'),
  41. ('0008' , 'Dona Benta'),
  42. ('0009', 'Nestle');
  43.  
  44.  
  45.  
  46.     id NCM
  47.    
  48.    
  49.  NCM ( id , descricao , cod_mercosul)
  50.  
  51. ('0001', ' ' , '000000001'),
  52. ('0002' , ' ' , '000000002'),
  53. ('0003' , ' ', '000000003'),
  54. ('0004' , ' ', '000000004'),
  55. ('0005', ' ' , '000000005');
  56.  
  57.  
  58.     id categoria
  59.    
  60.      
  61.  categoria ( codcategoria , descricaocategoria )
  62.  
  63. ('0001', 'Condimentos' ),
  64. ('0002' , 'Laticinios'),
  65. ('0003', 'HortiFruti'),
  66. ('0004' , 'Conservas'),
  67. ('0005', 'Limpeza'),
  68. ('0006' , 'Bebidas Alcoolicas'),
  69. ('0007' , 'Bebidas nao Alcoolicas'),
  70. ('0008' , 'Graos e Cereais');
  71.  
  72.    
  73.     id subcategoria
  74.    
  75.     subcategoria ( codsubcategoria , cod_categoria , descricaosubcategoria )
  76.  
  77. ('0001' , '0001' , 'Vinagres'),
  78. ('0002' , '0001' , 'Temperos'),
  79. ('0003' , '0001' , 'Sal' ),
  80. ('0004' , '0001', 'Azeites'),
  81. ('0005' , '0001', 'Oleos'),
  82. ('0006' , '0001', 'Especiarias'),
  83. ('0007' , '0002', 'Leites'),
  84. ('0008' , '0002', 'Iogurtes'),
  85. ('0009' , '0002', 'Fermentados'),
  86. ('0010' , '0002', 'Queijos'),
  87. ('0011' , '0003', 'Ovos'),
  88. ('0012' , '0003', 'Frutas secas'),
  89. ('0013' , '0003', 'Legumes'),
  90. ('0014' , '0004' , 'Frutas'),
  91. ('0015' , '0004', 'Peixes'),
  92. ('0016' , '0004', 'Vegetais'),
  93. ('0017' , '0004', 'Cogumelos'),
  94. ('0018' , '0005', 'Detergente'),
  95. ('0019' , '0005', 'Desinfetante'),
  96. ('0020' , '0005', 'Sabao em po'),
  97. ('0021' , '0005', 'Sabao em barra'),
  98. ('0022' , '0005', 'Amaciante');
  99.      
  100.     id fornecedor
  101.    
  102.     fornecedor ( cod , nome , CNPJ , rua , bairro , CEP , estado , ativo ) VALUES
  103.  
  104. ('0001' , 'Sadia'     , '55274471000180' , 'Rua valadares' , 'ipsep', '54330315' , 'Pernambuco' , true ) ,
  105. ('0002' , 'Pampers'   , '26724671000180' , 'Rua cartomante' , 'ibura', '54202010' , 'Pernambuco' , true ) ,
  106. ('0003' , 'Vitarela'  , '21712241000162' , 'Rua maniac' , 'algodão', '54215322' , 'São paulo' , true ) ,
  107. ('0004' , 'Coca-cola' , '26804531000180' , 'Rua argola' , 'vale tinhaem ', '51215020' , 'Rio de janeiro' , true ) ,
  108. ('0005' , 'Helmans'   , '77411981000180' , 'Rua sartre' , 'gitacity ', '44650201' , 'São paulo' , true ),
  109. ('0006' , 'Bombril'   , '53686527000188' , 'Rua 3' , 'Varzea' , '50980320' , 'Recife' , true );
  110.  
  111. */
  112.  
  113. insert into fornecedor ( cod , nome , CNPJ , rua , bairro , CEP , estado , ativo ) VALUES
  114.  
  115. ('0007' , 'Parmalat' , '66288196000170' , 'Rua 4' , 'Barro' , '87055100'  , 'Pernambuco' , true),
  116. ('0008' , 'Camponesa', '70190394000173' , 'Rua nova' , 'Dois Carneiros' , '54280642' , 'Pernambuco' , true),
  117. ('0009' , 'Camil' ,    '87536865000155' , 'Rua Sao Braz' , 'Vila Rica' , '54100604' , 'Pernambuco' , true),
  118. ('0010' , 'Veja' ,     '31089938000153' , 'Travessa 2' , 'San Martin' , '50760172' , 'Pernambuco' , true),
  119. ('0011' , 'OMO'  ,     '10339397000102' , 'Rua Perfeita' , 'Conquista' , '53190380' , 'Pernambuco' , true),
  120. ('0012' , 'Jonhsons' , '19967649000104' ,'Real da Torre' , 'Madalena' , '50650340' , 'Pernambuco' , true),
  121. ('0013' , 'Ypê' ,      '26305112000144' , 'Rua Vale'  , 'Cosme e Damiao' , '53110805' , 'Pernambuco' , true);
  122.  
  123.  
  124. insert into produto_ref (cod, id_unidade , id_marca , id_ncm , id_categoria, id_subcategoria, id_fornecedor ) values
  125.  
  126. ('0021' , '0001' , '0001' , '0005' , '0002' , '0010' , '0001' ),
  127. ('0022' , '0001' , '0001' , '0005' , '0008' , '0013' , '0001'),
  128. ('0023' , '0004' , '0003' , '0005' , '0002' , '0007' , '0008' ),
  129. ('0024' , '0004' , '0007' , '0005' , '0005' , '0018' , '0011' ),
  130. ('0025' , '0001' , '0002' , '0001' , '0001' , '0002' , '0008' );
  131.  
  132.  
  133.  
  134. /*
  135.  
  136.     ITEM ESTOQUE TIHNGS
  137.    
  138.    
  139.     foreign key (cod_lote) references lote (cod),
  140.     foreign key (id_estoque) references estoque (id),
  141.     CONSTRAINT fk_produtoestoque foreign key ( cod_produto ) references produto_ref( cod) ON DELETE CASCADE ON UPDATE CASCADE,
  142.     foreign key (id_avaria ) references avaria ( id),
  143.     foreign key (id_prateleira ) references prateleira ( codigo)
  144.    
  145.    
  146.     id Lote
  147.    
  148.     lote ( cod, cod_pedido_fornecedor, descricao , data_chegada) VALUES
  149.  
  150. ( '0001' , '0001', 'Lote de coca-cola ' , '2017-03-03' ),
  151. ( '0002' , '0002', 'Lote de pepsi ' , '2017-03-20' ),
  152. ( '0003' , '0003', 'Lote de margarina deline ' , '2017-05-03' ),
  153. ( '0004' , '0004', 'Lote de açucar pretinho ' , '2017-02-03' ),
  154. ( '0005' , '0005', null , '2017-04-03' );
  155.  
  156.  
  157.     id Estoque
  158.    
  159.     estoque ( id , seq_filial , cnpj_matriz , descricao , dt_ultima_entrada ) VALUES
  160.  
  161. ( '0001' , '0001' ,  '23416393000114' , null , '2018-01-04'),
  162. ( '0002' , '0002' ,  '23416393000114' , null , '2018-01-04'),
  163. ( '0003' , '0003' ,  '23416393000140' , null , '2018-01-04'),
  164. ( '0004' , '0004' ,  '23416393000169' , null , '2018-01-04');
  165.  
  166.  
  167.    
  168.     id Produto_ref
  169.    
  170.  
  171.    
  172.     id avaria
  173.    
  174.     avaria (id , causa , preco , obs ) VALUES
  175.  
  176. ('0001' , 'Queda' , 30 , null ),
  177. ('0002' , 'Queda' , 10 , null ),
  178. ('0003' , 'Queda' , 200 , null ),
  179. ('0004' , 'Queda' , 32.2 , null ),
  180. ('0005' , 'Queda' , 230 , null ),
  181. ('0006' , 'Queda' , 92.2 , null );
  182.    
  183.     id prateleira
  184.    
  185.     prateleira ( codigo , altura , comprimento , posicao_nivel ) VALUES
  186.  
  187. ('0001' , 5 , 10 , 'A1' ),
  188. ('0002' , 5 , 10 , 'A2' ),
  189. ('0003' , 5 , 10 , 'A3' ),
  190. ('0004' , 5 , 10 , 'A4' ),
  191. ('0005' , 5 , 10 , 'A5' ),
  192. ('0006' , 5 , 10 , 'B1' ),
  193. ('0007' , 5 , 10 , 'B2' ),
  194. ('0008' , 5 , 10 , 'B3' ),
  195. ('0009' , 5 , 10 , 'B4' ),
  196. ('0010' , 5 , 10 , 'B5' ),
  197. ('0011' , 5 , 10 , 'C1' ),
  198. ('0012' , 5 , 10 , 'C2' ),
  199. ('0013' , 5 , 10 , 'C3' ),
  200. ('0014' , 5 , 10 , 'C4' ),
  201. ('0015' , 5 , 10 , 'C5' );
  202.  
  203. */
  204.  
  205. insert into item_estoque (cod_lote, id_estoque , cod_produto , id_avaria , id_prateleira , data_validade ,
  206.  data_fabricacao , data_entrada , valor_compra, quantidade)  VALUES
  207.  
  208.  ( '0001' , '0001' , '0025' , null , '0003' , '2020-03-01' , '2017-02-01' , '2018-01-01' , 400 , 200),
  209.  ( '0001' , '0003' , '0024' , null , '0015' , '2019-05-02' , '2017-02-05' , '2017-09-02' , 320 , 500),
  210.  ( '0001' , '0001' , '0021' , null , '0015' , '2019-08-08' , '2018-01-10' , '2018-02-11' , 200.56 , 100),
  211.  ( '0001' , '0002' , '0021' , '0001' , '0006' , '2018-02-02' , '2017-09-09'  , '2017-01-10' , 500  , 780),
  212.  ( '0001' , '0003' , '0022' , null , '0007' , '2020-10-10' , '2017-03-03' , '2017-09-03' , 20 ,  90),
  213.  ( '0002' , '0003' , '0022' , '0003' , '0007' , '2020-09-12' , '2018-01-01' , '2018-02-01' , 300 , 600),
  214.  ( '0001' , '0003' , '0025' , null , '0003' , '2020-03-01' , '2017-02-03' , '2018-02-12' , 500 , 1000),
  215.  ( '0001' , '0003' , '0020' , '0005' , '0003' , '2019-06-05' , '2017-03-01' , '2017-03-10' , 300 , 300),
  216.  ( '0001' , '0003' , '0021' , null , '0006' , '2018-02-02' , '2017-09-09'  , '2017-01-10' , 500  , 780),
  217.  ( '0003' , '0003' , '0001' , null , '0002' , '2019-02-10' , '2018-12-12' , '2018-01-02' , 500, 100),
  218.  ( '0001' , '0003' , '0016' , null , '0002' , '2020-02-12' , '2017-12-12' , '2018-01-01' , 1000.50 , 300),
  219.  ( '0002' , '0003' , '0020',  null,  '0004' , '2018-02-02' , '2017-01-01' , '2017-08-10' , 300 , 60),
  220.  ( '0001' , '0003' , '0018' , '0003' , '0005' , '2019-04-04' , '2017-01-01'  , '2018-03-02' , 600 , 93),
  221.  ( '0001 ', '0002' , '0015'  ,null , '0002' , '2020-04-12' , '2017-12-12' , '2018--2-13' , 450.50 , 200),
  222.  ( '0001' , '0002', '0014' ,  null, '0005',  '2021-11-10' , '2018-01-10' , '2018-01-15' , 3000.00 , 600),
  223.  ( '0002' , '0002' , '0019' , null,  '0003', '2018-01-02' , '2017-01-01' , '2017-11-06' , 500 , 300),
  224.  ( '0001'  ,'0002' , '0017' , null , '0003', '2019-01-13', '2017-12-14' , '2017-09-15' , 600 , 100),
  225.  ( '0001' , '0002' , '0016' , null , '0003' , '2020-04-17' , '2017-06-13' , '2018-01-22' , 567 , 80 ),
  226.  ( '0001' , '0002' , '0011' , null , '0006' , '2019-03-15' , '2016-07-22', '2017-01-19' , 2000 , 500),
  227.  ( '0001',  '0002',  '0012',  '0006' , '0003' , '2018-01-01' , '2016-05-21' , '2017-03-23' , 300 , 35),
  228.  ( '0001' , '0002' , '0013' , null , '0001' , '2019-03-01' , '2017-07-04' , '2017-09-09' , 1200 , 356),
  229.  ( '0002' , '0004',  '0007' , null , '0003' , '2020-02-13' , '2017-06-21' , '2018-07-15' , 600.50 , 215),
  230.  ( '0001' , '0002' , '0008' , null , '0003' , '2018-01-01' , '2016-09-09' , '2017-11-02' , 300 , 50),
  231.  ( '0002' , '0004' , '0009' , null , '0002' , '2019-01-18' , '2016-05-12' , '2017-11-11' , 1200 , 599),
  232.  ( '0001' , '0004' , '0010 ', null , '0002' , '2019-01-18' , '2016-05-12' , '2017-10-01' , 500.75 , 260),
  233.  ( '0003' , '0004' , '0011' , null , '0001' , '2018-02-13' , '2016-01-03' , '2017-01-10' , 370 , 120),
  234.  ( '0004' , '0004' , '0013' , null , '0003' , '2020-02-10' , '2017-03-15' , '2017-12-30' , 500 , 110),
  235.  ( '0002' , '0003' , '0015' , null , '0004' , '2018-01-01', '2016-12-12' , '2017-01-22' , 900 , 450),
  236.  ( '0001' , '0003 ', '0014' , null , '0004' , '2019-03-04' ,'2017-12-11' , '2017-05-19' , 300 , 123),
  237.  ( '0003' , '0003' , '0010', null , '0001' , '2019-10-12' , '2017-03-04' , '2017-04-15' , 450 , 120),
  238.  ( '0002 ', '0004 ', '0013' , null ,'0004' , '2018-02-01' , '2016-03-12' , '2017-04-11' , 500, 220),
  239.  ( '0001' , '0003' , '0012', null , '0004' , '2019-01-23' , '2016-01-17' , '2017-04-28' , 600 , 300),
  240.  ( '0003' , '0004' , '0002', null , '0003' , '2019-03-19' , '2016-09-27' , '2017-05-18' , 300 , 79);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top