SQL Database Exercise: Clients, Carriers and Orders Queries

SQL Database Exercise: Clients, Carriers and Orders

CREATE DATABASE EX4_1;

USE EX4_1;

Creating the clients table

CREATE TABLE clientes
(codcli INT,
nome VARCHAR(30),
endereço VARCHAR(40),
cidade VARCHAR(30),
fone CHAR(9),
CONSTRAINT pk_clientes PRIMARY KEY(codcli));

INSERT INTO clientes VALUES
(1,'Elevanir','Rua do Parque','Mauá','945653285'),
(2,'Lucas','Rua do Iorgute','Mauá','954658545'),
(3,'Ludirbe','Rua da Vadiagem','Santo André','954785452'),
(4,'Lairdison','Rua do Fermento','São Bernardo do Campo','954658532'),
(5,'Leandro','Rua do Bolo','Norte','954658545');

Creating the carriers table

CREATE TABLE transportadora
(codigo INT,
nome VARCHAR(20),
CONSTRAINT pk_transportadora PRIMARY KEY(codigo));

INSERT INTO transportadora VALUES
(1,'Fedex'),
(2,'Polar Express'),
(3,'TNT'),
(4,'PAC'),
(5,'Sedex');

INSERT INTO transportadora VALUES
(6,'Farra');

Creating the orders table

CREATE TABLE pedidos
(numped INT,
cliente INT,
data_entrega DATETIME,
transport INT,
CONSTRAINT pk_pedidos PRIMARY KEY(numped),
CONSTRAINT fk_pedidos FOREIGN KEY (cliente) REFERENCES clientes(codcli),
CONSTRAINT fk_pedidos1 FOREIGN KEY (transport) REFERENCES transportadora(codigo));

INSERT INTO pedidos VALUES
(1,2,'20160809',3),
(2,3,'20160809',2),
(3,1,'20160809',1),
(4,5,'20160809',4),
(5,4,'20160809',5);

INSERT INTO pedidos VALUES
(6,4,'20160910',5);

INSERT INTO clientes VALUES
(6,'Elevanilson','Rua do Paraiba','Mauá','945685485');

Queries

-- c)

SELECT clientes.nome, pedidos.cliente FROM clientes INNER JOIN pedidos ON clientes.codcli = pedidos.numped;

-- d)

SELECT clientes.nome, pedidos.numped FROM clientes LEFT OUTER JOIN pedidos ON clientes.codcli = pedidos.numped WHERE pedidos.numped IS NULL;

-- e)

SELECT transportadora.nome, pedidos.numped FROM transportadora LEFT OUTER JOIN pedidos ON transportadora.codigo = pedidos.numped WHERE pedidos.numped IS NULL;

-- f)

SELECT transportadora.nome, pedidos.numped FROM transportadora LEFT OUTER JOIN pedidos ON transportadora.codigo = pedidos.numped WHERE transportadora.nome = 'Sedex';

-- g)

SELECT COUNT(cliente) AS 'Numeros de pedidos para o cliente 2' FROM pedidos WHERE cliente LIKE 2;

-- h)

SELECT MAX(data_entrega) AS 'Entrega mais recente' FROM pedidos;

-- i)

SELECT COUNT(transport) AS 'Numero de pedidos para a transportadora Fedex' FROM pedidos WHERE transport LIKE 1;