Creating and Deleting Database Tables with SQL
________________________________
Creating and Deleting Tables
________________________________
CREATE TABLE F (
F_cod INT(11) NOT NULL PRIMARY KEY,
F_nome CHAR(50) NOT NULL,
F_status INT NOT NULL,
F_cidade CHAR(25) NOT NULL)
CREATE TABLE P (
P_cod INT(11) NOT NULL PRIMARY KEY,
P_nome CHAR(50) NOT NULL,
P_cor CHAR(15) NOT NULL,
P_preco FLOAT(10,2) NOT NULL,
P_cidade CHAR(25) NOT NULL )
CREATE TABLE FP (
F_cod INT(11) NOT NULL,
P_cod INT(11) NOT NULL,
qtde INT NOT NULL,
CONSTRAINT FOREIGN KEY (F_cod) REFERENCES F(F_cod),
CONSTRAINT FOREIGN KEY (P_cod) REFERENCES P(P_cod))
________________________________
Inserting Values
________________________________
INSERT INTO F VALUES (1,’x’, 29, ‘LONDRES’), (2,’Y’,19,’PARIS’), (3,’L’,10,’PARIS’), (4,’Z’,10,’LONDRES’), (5,’LIXO’,19,’LAVRAS’)
INSERT INTO P VALUES (1,’NN’,’AZUL’,5,’LONDRES’), (2,’KK’,’VERMELHA’,10,’PARIS’), (3,’MM’,’PRETO’,14,’ROMA’), (4,’TT’,’VERMELHA’,12,’LONDRES’)
INSERT INTO FP VALUES (1, 1, 30), (2, 1, 30), (3, 2, 10), (4, 3, 50)
________________________________
Updating Data
________________________________
UPDATE F SET F_status = F_status + 1
WHERE F_status > 15
________________________________
Deleting Data
________________________________
DELETE FROM F WHERE F_cidade = ‘LAVRAS’
_______________________________________________
Accessing a Single Table (Queries and Functions)
_______________________________________________
SELECT SUM(P_preco) FROM P
SELECT AVG(P_preco) FROM P
SELECT MAX(P_preco) FROM P
SELECT MIN(P_preco) FROM P
SELECT COUNT(P_preco) FROM P
SELECT COUNT(P_preco), P_cidade FROM P WHERE P_cidade = ‘PARIS’
______________________
ORDER BY
______________________
SELECT * FROM P ORDER BY P_nome
SELECT * FROM P ORDER BY P_nome DESC
_____________________
GROUP BY
______________________
SELECT SUM(P_preco) FROM P
SELECT SUM(P_preco), P_cidade FROM P GROUP BY P_cidade
SELECT AVG(P_preco), P_cidade FROM P GROUP BY P_cidade
SELECT MAX(P_preco), P_cidade FROM P GROUP BY P_cidade
SELECT COUNT(P_preco), P_cidade FROM P GROUP BY P_cidade
_____________________________________________
Accessing Two Tables
_______________________________________________
SELECT F.F_nome, P.P_nome FROM F, P WHERE F.F_cidade = ‘PARIS’ AND P.P_cidade= ‘PARIS’
SELECT F.F_nome, P.P_nome FROM F, P WHERE F.F_cidade = P.P_cidade
SELECT F.F_nome FROM F, P WHERE F.F_cidade = P.P_cidade
SELECT DISTINCT F.F_nome FROM F, P WHERE F.F_cidade = P.P_cidade
SELECT DISTINCT P.P_nome, P.P_cor FROM P, FP WHERE FP.qtde = 30 AND FP.P_cod=P.P_cod
SELECT F.F_nome, F.F_cidade FROM F, FP WHERE FP.qtde > 10 AND FP.P_cod = 1 AND FP.F_cod=F.F_cod
SELECT SUM(FP.qtde) FROM FP, F WHERE F.F_cidade = ‘LONDRES’ AND FP.P_cod = 1 AND FP.F_cod = F.F_cod
SELECT AVG(P.P_preco) FROM P, FP WHERE FP.F_cod = 3 AND FP.P_cod=P.P_cod
________________________________________________
Accessing Three Tables
________________________________________________
SELECT P.P_nome FROM P, F, FP WHERE F.F_cidade = ‘PARIS’ AND F.F_cod=FP.F_cod AND FP.P_cod=P.P_cod
SELECT DISTINCT (F_nome) FROM P, F, FP WHERE P.P_cor = ‘VERMELHA’ AND P.P_cod=FP.P_cod AND FP.F_cod=F.F_cod
SELECT DISTINCT (F.F_cidade) FROM F, P, FP WHERE P.P_preco > 5 AND P.P_cod=FP.P_cod AND FP.F_cod=F.F_cod
SELECT DISTINCT (P.P_nome) FROM P, F, FP WHERE F.F_status > 10 AND F.F_cod=FP.F_cod AND FP.P_cod=P.P_cod
______________________
Chained Queries
________________________________________________
SELECT P_nome, P_cor FROM P WHERE P_preco > (SELECT AVG(P_preco) FROM P)
SELECT F_nome FROM F WHERE F_cidade = (SELECT F_cidade FROM F WHERE F_cod = 1)
SELECT F_nome FROM F WHERE F_status > (SELECT AVG(F_status) FROM F WHERE F_nome <> ‘Y’ AND (F_cidade = ‘PARIS’ OR F_cidade = ‘LONDRES’))
SELECT F_nome FROM F WHERE F_status IN (SELECT F_status FROM F WHERE F_cidade = ‘PARIS’)
SELECT P.P_nome FROM P, FP WHERE FP.qtde > (SELECT AVG(FP.qtde) FROM FP) AND P.P_cod = FP.P_cod
SELECT DISTINCT (P.P_cor) FROM P, F, FP WHERE F.F_status > (SELECT AVG(F_status) FROM F WHERE F_cidade = ‘PARIS’) AND F.F_cod = FP.F_cod AND FP.P_cod = P.P_cod