ScriptAulaDML.sql

INSERT INTO T_SIP_DEPTO(CD_DEPTO, NM_DEPTO, SG_DEPTO)
VALUES (1, 'Intelig�ncia Digital', 'ITD');

INSERT INTO T_SIP_DEPTO(CD_DEPTO, NM_DEPTO, SG_DEPTO)
VALUES (2, 'Financeiro', 'FIN');

--
--  Insert posicional
--
INSERT INTO T_SIP_DEPTO
VALUES (3, 'Contabilidade', 'CTB');

SELECT * FROM T_SIP_DEPTO;


ALTER TABLE T_SIP_DEPTO ADD ST_DEPTO CHAR(1);

UPDATE T_SIP_DEPTO SET ST_DEPTO = 'A';

ALTER TABLE T_SIP_DEPTO MODIFY (ST_DEPTO CONSTRAINT CK_SIP_DEPTO_ST_DEPTO_NN NOT NULL);

SELECT * FROM T_SIP_DEPTO;

--
-- INSERT ALL
--

INSERT ALL
    INTO T_SIP_DEPTO (NM_DEPTO, SG_DEPTO, CD_DEPTO, ST_DEPTO) VALUES ('Contas a Pagar', 'CTP', 4, 'A')
    INTO T_SIP_DEPTO (CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO) VALUES (5,'RH', 'DRH', 'A')
    INTO T_SIP_DEPTO (CD_DEPTO, NM_DEPTO, SG_DEPTO, ST_DEPTO) VALUES (6,'Tesouraria', 'DTE', 'A')
SELECT * FROM dual;

SELECT * FROM T_SIP_DEPTO;

-- || CONCATENA��O
UPDATE T_SIP_DEPTO SET ST_DEPTO = 'I', NM_DEPTO = NM_DEPTO || '* Inativo' WHERE CD_DEPTO = 5;

--
-- delete
--
DELETE FROM T_SIP_DEPTO WHERE CD_DEPTO = 4;
DELETE FROM T_SIP_DEPTO WHERE SG_DEPTO = 'DTE';

-- delete multiplo
DELETE FROM T_SIP_DEPTO WHERE SG_DEPTO IN ('DTE', 'DRH');
DELETE FROM T_SIP_DEPTO WHERE SG_DEPTO = 'DTE' OR SG_DEPTO = 'DRH';
DELETE FROM T_SIP_DEPTO WHERE CD_DEPTO = 100;

--
-- Select com || para adicionar string no select
--
SELECT 'O depto ' || cd_depto || ' tem o seu nome ' || nm_depto from T_SIP_DEPTO;