sql

CREATE TABLE tb_clientePF(
  codigo NUMBER,
  nome VARCHAR(50)NOT NULL,
  saldo NUMBER(8,2)NULL,
  estado VARCHAR(2) NULL,
  PRIMARY KEY(codigo)
);

CREATE TABLE tb_gerente(
codfunc NUMBER (4)NOT NULL,
codClientePF NUMBER(4) NOT NULL,
nome VARCHAR (50)NOT NULL,
salario NUMBER(8,2)NOT NULL,
estado VARCHAR(2) NULL,
data_cadastro DATE NULL,
PRIMARY KEY(codfunc),
FOREIGN KEY (codClientePF) REFERENCES tb_clientePF(codigo))


CREATE SEQUENCE incrementClientePF
START WITH 1
INCREMENT BY 1
minvalue 1
maxvalue 1000000;

INSERT INTO tb_clientePF VALUES(incrementClientePF.NEXTVAL,'andre', 2500, 'SP');
INSERT INTO tb_clientePF VALUES(incrementClientePF.NEXTVAL,'fernando', 2500, 'SP');
INSERT INTO tb_clientePF VALUES(incrementClientePF.NEXTVAL,'gabriel', 2500, 'RJ');
INSERT INTO tb_clientePF VALUES(incrementClientePF.NEXTVAL,'erika', 2500, 'SP');
INSERT INTO tb_clientePF VALUES(incrementClientePF.NEXTVAL,'sales', 2500, 'MI');
INSERT INTO tb_clientePF VALUES(incrementClientePF.NEXTVAL,'venderson', 2500, 'SP');

------------------------------------------------------------------------------------------------------------------

relacionado a tabela

------------------------------------------------------------------------------------------------------------------

ALTER TABLE tb_clientePF ADD telefone CHAR(11)

ALTER TABLE tb_clientePF MODIFY telefone CHAR(12)

TRUNCATE TABLE tb_clientePF

DROP TABLE tb_clientePF

DELETE FROM tb_clientePF WHERE codigo = 1

UPDATE tb_clientePF SET saldo = saldo*1.2

DESC tb_clientePF

------------------------------------------------------------------------------------------------------------------

SELECT em gerais

------------------------------------------------------------------------------------------------------------------
SELECT*FROM tb_clientePF;

SELECT*FROM tb_clientePF ORDER BY codigo

SELECT*FROM tb_clientePF WHERE nome LIKE 'a%';

SELECT*FROM tb_clientePF WHERE nome LIKE '%a';

SELECT*FROM tb_clientePF WHERE nome = 'andre';

SELECT * FROM tb_clientePF WHERE nome = 'andre' OR nome = 'erika'

SELECT nome, saldo FROM tb_clientePF;

SELECT nome, saldo FROM tb_clientePF WHERE saldo = '2500';

SELECT avg(saldo) AS media, SUM(saldo) AS soma , MIN(saldo) AS minimo, MAX(saldo) AS maximo FROM tb_clientePF;

SELECT nome, (saldo*10%) AS aumento FROM tb_clientePF;

SELECT nome, (saldo*12) AS anual FROM tb_clientePF;

SELECT nome FROM tb_clientePF WHERE nome <> 'andre' AND nome <> 'erika'

------------------------------------------------------------------------------------------------------------------
SELECT * FROM tb_clientePF WHERE saldo >= '2500' AND saldo <= '3000';

SELECT * FROM tb_clientePF WHERE saldo BETWEEN '2500' AND '3000';

SELECT * FROM tb_clientePF WHERE saldo = '2500' OR saldo = '3000'

SELECT * FROM tb_clientePF WHERE saldo IN ('2500', '3000');

------------------------------------------------------------------------------------------------------------------

Velocidade em codigos

------------------------------------------------------------------------------------------------------------------

CREATE INDEX name ON tb_clientePF(nome);

SELECT *FROM tb_clientePF WHERE nome='andre';

SELECT *FROM user_indexes;

DROP INDEX name;

------------------------------------------------------------------------------------------------------------------

VIEW tabela

------------------------------------------------------------------------------------------------------------------

CREATE VIEW cliente_saldo AS
SELECT nome, saldo FROM tb_clientePF;

SELECT * FROM cliente_saldo;