Scribd
Upload
8 views4 pages

Script SQL Server

The document outlines the creation of a database named 'maga_Ryan_1info' with several tables including 'PESSOA', 'PESSOA_DOC', 'TB_ESTOQUE', and others, each designed to store specific information such as personal details, inventory, and financial transactions. It includes SQL commands for creating these tables with defined columns and data types, as well as sample insert statements for populating the 'PESSOA' and 'TB_Caixa' tables with data. The structure supports a relational database model for managing personal and transactional information.

Uploaded by

ryansamuka065
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
8 views4 pages

Script SQL Server

The document outlines the creation of a database named 'maga_Ryan_1info' with several tables including 'PESSOA', 'PESSOA_DOC', 'TB_ESTOQUE', and others, each designed to store specific information such as personal details, inventory, and financial transactions. It includes SQL commands for creating these tables with defined columns and data types, as well as sample insert statements for populating the 'PESSOA' and 'TB_Caixa' tables with data. The structure supports a relational database model for managing personal and transactional information.

Uploaded by

ryansamuka065
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 4

CREATE DATABASE maga_Ryan_1info

USE maga_Ryan_1info

CREATE TABLE [DBO].[PESSOA]


(
[CNPJ_CPF] [VARCHAR] (15) NOT NULL,
[NOME_RAZAO] [VARCHAR] (45) NULL,
[ENDERECO] [VARCHAR] (45) NULL,
[BAIRRO] [VARCHAR] (25) NULL,
[FONE] [VARCHAR] (15) NULL,
[CELULAR] [VARCHAR] (15),
[EMAIL] [VARCHAR] (40)
PRIMARY KEY CLUSTERED
(
[CNPJ_CPF] ASC
)
) ON[PRIMARY]
CREATE TABLE [DBO].[PESSOA_DOC]
( [CNPJ_CPF] [VARCHAR] (15) NOT NULL,[DTCAD] [DATE] NULL,
[DTNASC] [DATE] NULL,[IE_PES] [VARCHAR] (15) NULL,
[RG_PES] [VARCHAR] (12) NULL, [CNH_PES] [VARCHAR] (15) NULL,
[LETRA_CNH] [VARCHAR] (1),[DT_CNH] [DATE] NULL,
[RESERVISTA_PES] [VARCHAR] (12),[CTPS] [VARCHAR] (12) NULL,
[SERIE_CTPS] [VARCHAR] (10) NULL, [ESTADO_CIVIL] [VARCHAR] (25) NULL,
[CPF_CONJ] [VARCHAR] (12) NULL, [NOME_CONJ] [VARCHAR] (45) NULL,
[DTNASC_CONJ] [DATE] NULL,
[N_FILHOS] [INT] NULL
PRIMARY KEY CLUSTERED ( [CNPJ_CPF] ASC )
) ON[PRIMARY]

CREATE TABLE [DBO].[PESSOA_DOC_FIL]


( [CNPJ_CPF] [VARCHAR] (15) NOT NULL,[CPF_FIL] [VARCHAR] (15) NOT NULL,
[DTNASC_FIL] [DATE] NULL,[NOME_FIL] [VARCHAR] (45) NULL
PRIMARY KEY CLUSTERED ([CNPJ_CPF]ASC,[CPF_FIL] ASC)
) ON[PRIMARY]

CREATE TABLE [DBO].[TB_ESTOQUE]


([COD_PROD] [VARCHAR] (10) NOT NULL,[DESC_PROD] [VARCHAR] (35) NULL,
[DT_CAD] [DATE] NULL,[CNPJ_CPF] [VARCHAR] (15) NULL,
[N_FISC] [VARCHAR] (10) NULL PRIMARY KEY CLUSTERED ( [COD_PROD] ASC )
) ON[PRIMARY]

CREATE TABLE [DBO].[TB_EST_ENT]


( [COD_PROD] [VARCHAR] (10) NOT NULL,
[DT_ENT] [DATE],
[QT_ENT] [INT]
PRIMARY KEY CLUSTERED
([COD_PROD] ASC, [DT_ENT] ASC)
) ON[PRIMARY]
CREATE TABLE [DBO].[TB_EST_SAI]
( [COD_PROD] [VARCHAR] (10) NOT NULL,
[DT_SAI] [DATE],
[QT_SAI] [INT]
PRIMARY KEY CLUSTERED
([COD_PROD] ASC,[DT_SAI] ASC)
) ON[PRIMARY]
CREATE TABLE [DBO].[TB_CAIXA]
( [N_FISC] [VARCHAR] (10) NOT NULL,
[QT_PAR] [INT] NOT NULL,
[DT_VENDA] [DATE] NULL,
[VL_VENDA] [DECIMAL] (9,2) NULL,
[CNPJ_CPF] [VARCHAR] (15) NOT NULL,
PRIMARY KEY CLUSTERED
([N_FISC] ASC)
) ON[PRIMARY]
CREATE TABLE [DBO].[TB_SAI_CAI]
( [N_FISC] [VARCHAR] (10) NOT NULL,
[QT_PAR] [INT] NOT NULL,
[DT_PG_PAR] [DATE] NULL,
[VL_PAR] [DECIMAL] (9,2) NULL,
[DT_PG_PAR_ATUAL] [DATE] NULL,
[VL_PAR_ATUAL] [DECIMAL] (9,2) NULL
PRIMARY KEY CLUSTERED
([N_FISC] ASC,[QT_PAR] ASC)
) ON[PRIMARY]
CREATE TABLE [DBO].[TB_ENT_CAI]
( [N_FISC] [VARCHAR] (10) NOT NULL,
[QT_PAR] [INT] NOT NULL,
[DT_PG_PAR] [DATE] NULL,
[VL_PAR] [DECIMAL] (9,2) NULL,
[DT_PG_PAR_ATUAL] [DATE] NULL,
[VL_PAR_ATUAL] [DECIMAL] (9,2) NULL
PRIMARY KEY CLUSTERED
([N_FISC] ASC, [QT_PAR] ASC)
) ON[PRIMARY]

insert into PESSOA(CNPJ_CPF,nome_RAZAO, ENDERECO, bairro, fone, celULAR, email)


values('1','Arthur Costa', 'Rua Um', 'Vila Um',11111111, 999999999,
'[email protected]'),
('2','Danilo Ferroni', 'Rua dois', 'Vila Dois', 22222222,
888888888, '[email protected]'),
('3','Luiz Alves', 'Rua três', 'Vila três', 33333333, 777777777,
'[email protected]'),
('4','Rosa Aparecida', 'Rua Quatro', 'Vila Quatro', 44444444,
666666666, '[email protected]'),
('5','Talita Ferreira', 'Rua Cinco', 'Vila Cinco', 55555555,
555555555, '[email protected]'),
('6','Pamela Gonçalves', 'Rua Seis', 'Vila Seis', 66666666,
444444444, '[email protected]'),
('7','Victor Guzella', 'Rua Sete', 'Vila sete', 77777777,
333333333, '[email protected]'),
('8','Tania Silva', 'Rua oito', 'Vila Oito', 88888888, 222222222,
'[email protected]'),
('9','Felipe Andares', 'Rua nove', 'Vila nove', 99999999,
111111111, '[email protected]'),
('10','Natalia Cristina', 'Rua dez', 'Vila dez', 12121212,
989898989, '[email protected]');
select * from PESSOA

insert into tb_Caixa(n_Fisc,qt_Par, dt_VENDA, VL_VENDA, CNPJ_CPF)


values('1', 1, '2019/07/03', 300.00, '1'),
('2', 1,'2019/01/10',1000.00, '4'),
('3', 2,'2019/05/31', 200.00, '2'),
('4', 2, '2019/09/18', 90.00, '3'),
('5', 3, '2019/10/10', 2500.00, '5'),
('6', 3, '2019/11/02',300.00, '2'),
('7', 4, '2019/01/23',700.00, '2'),
('8', 4, '2019/01/23',170.00, '1'),
('9', 5, '2019/07/03',900.00, '3'),
('10', 5, '2019/11/07', 250.00, '1'),
('11', 6, '2019/01/03', 3000.00, '10'),
('12', 6, '2019/08/17',250.00, '5'),
('13', 7, '2019/02/12', 700.00,'10'),
('14', 7, '2019/01/12',1000.00, '5'),
('15', 8, '2019/03/27',400.00, '4'),
('16', 8, '2019/04/15', 600.00,'3'),
('17', 9, '2019/06/10',1250.00, '5'),
('18', 9, '2019/03/07', 1500.00, '8'),
('19', 10, '2019/01/17', 850.00,'1'),
('20', 10, '2019/01/17',300.00,'1');

select * from tbCaixa

insert into tb_Ent_CAI(n_Fisc, QT_Par, dt_Pg_PAR,VL_PAR, DT_PG_PAR_ATUAL,


VL_PAR_ATUAL)
values(1, 1, '2019/07/03', 300.00, '2019/07/03',0),
(2, 1, '2019/01/20', 250.00, '2019/01/21',0),
(2, 2, '2019/02/20', 250.00, '2019/02/20',0),
(2, 3, '2019/03/20', 250.00, '2019/03/20',0),
(2, 4, '2019/04/20', 250.00, '2019/04/22',0),
(3, 1, '2019/06/30', 100.00, '2019/07/01',0),
(3, 2, '2019/07/31', 100.00, '2019/07/31',0),
(4, 1, '2019/09/30', 30.00, '2019/09/30',0),
(4, 2, '2019/10/31', 30.00, '2019/10/31',0),
(4, 3, '2019/11/30', 30.00, '2019/12/02',0),
(5, 1, '2019/09/30', 500.00, '2019/09/30',0),
(5, 2, '2019/10/31', 500.00, '2019/10/31',0),
(5, 3, '2019/11/30', 500.00, '2019/12/02',0),
(5, 4, '2019/12/31', 500.00, '2019/01/02',0),
(5, 5, '2020/01/31', 500.00, '2020/01/31',0),
(6, 1, '2019/11/15', 150.00, '2019/11/15',0),
(6, 2, '2019/12/15', 150.00, '2019/12/16',0),
(7, 1, '2019/12/31', 350.00, '2020/01/02',0),
(7, 2, '2019/01/31', 350.00, '2020/01/31',0),
(8, 1, '2020/01/23', 170.00, '2020/01/31',0),
(9, 1, '2019/07/15', 300.00, '2019/07/15',0),
(9, 2, '2019/08/15', 300.00, '2019/08/15',0),
(9, 3, '2019/09/15', 300.00, '2019/09/16',0),
(10, 1, '2019/11/15', 250.00, '2019/11/15',0),
(11, 1, '2019/01/20', 300.00, '2019/01/21',0),
(11, 2, '2019/02/20', 300.00, '2019/02/20',0),
(11, 3, '2019/03/20', 300.00, '2019/03/20',0),
(11, 4, '2019/04/20', 300.00, '2019/04/20',0),
(11, 5, '2019/05/20', 300.00, '2019/05/20',0),
(11, 6, '2019/06/20', 300.00, '2019/06/21',0),
(11, 7, '2019/07/20', 300.00, '2019/07/22',0),
(11, 8, '2019/08/20', 300.00, '2019/08/20',0),
(11, 9, '2019/09/20', 300.00, '2019/09/20',0),
(11, 10, '2019/10/20', 300.00, '2019/10/21',0),
(12, 1, '2019/08/20', 50.00, '2019/08/20',0),
(12, 2, '2019/09/20', 50.00, '2019/09/20',0),
(12, 3, '2019/10/20', 50.00, '2019/10/21',0),
(12, 4, '2019/11/20', 50.00, '2019/11/21',0),
(12, 5, '2019/12/20', 50.00, '2019/12/20',0),
(13, 1, '2019/05/20', 70.00, '2019/05/20',0),
(13, 2, '2019/06/20', 70.00, '2019/06/21',0),
(13, 3, '2019/07/20', 70.00, '2019/07/22',0),
(13, 4, '2019/08/20', 70.00, '2019/08/20',0),
(13, 5, '2019/09/20', 70.00, '2019/09/21',0),
(13, 6, '2019/10/20', 70.00, '2019/10/21',0),
(13, 7, '2019/11/20', 70.00, '2019/11/21',0),
(13, 8, '2019/12/20', 70.00, '2019/12/20',0),
(13, 9, '2020/01/20', 70.00, '2020/01/20',0),
(13, 10, '2020/02/20', 70.00, '2020/02/20',0),
(14, 1, '2019/10/15', 200.00, '2019/10/15',0),
(14, 2, '2019/11/15', 200.00, '2019/11/18',0),
(14, 3, '2019/12/15', 200.00, '2019/12/16',0),
(14, 4, '2020/01/15', 200.00, '2020/01/15',0),
(14, 5, '2020/02/15', 200.00, '2020/02/17',0),
(15, 1, '2019/04/05', 100.00, '2019/04/05',0),
(15, 2, '2019/05/05', 100.00, '2019/05/06',0),
(15, 3, '2019/06/05', 100.00, '2019/06/05',0),
(15, 4, '2019/07/05', 100.00, '2019/07/05',0),
(16, 1, '2019/04/25', 200.00, '2019/04/25',0),
(16, 2, '2019/05/25', 200.00, '2019/05/27',0),
(16, 3, '2019/06/25', 200.00, '2019/06/25',0),
(17, 1, '2019/06/20', 250.00, '2019/06/21',0),
(17, 2, '2019/07/20', 250.00, '2019/07/22',0),
(17, 3, '2019/08/20', 250.00, '2019/08/20',0),
(17, 4, '2019/09/20', 250.00, '2019/09/20',0),
(17, 5, '2019/10/20', 250.00, '2019/10/21',0),
(18, 1, '2019/03/15', 187.50, '2019/03/15',0),
(18, 2, '2019/04/15', 187.50, '2019/04/15',0),
(18, 3, '2019/05/15', 187.50, '2019/05/15',0),
(18, 4, '2019/06/15', 187.50, '2019/06/17',0),
(18, 5, '2019/07/15', 187.50, '2019/07/15',0),
(18, 6, '2019/08/15', 187.50, '2019/08/15',0),
(18, 7, '2019/09/15', 187.50, '2019/09/16',0),
(18, 8, '2019/10/15', 187.50, '2019/10/15',0),
(19, 1, '2020/01/20', 850.00, '2020/01/20',0),
(20, 1, '2020/01/20', 300.00, '2020/01/20',0);

You might also like