Trabalho de SQL

Objetivo:

O objetivo deste trabalho é se familiarizar com a linguagem SQL e serve como nota do trabalho T1. O trabalho deverá ser realizado com o SGBD SQLite3 que pode ser encontrado facilmente para instalacao em qualquer sistema operacional. Verifique se o seu computador ja possui o SGBD instalado digitando sqlite3 no terminal. A mensagem abaixo deve aparecer no seu terminal:

$ sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Especificação:

O trabalho possui 10 questões sendo o valor total 1.0 ponto (0.1 cada consulta SQL). Para cada questão voce deverá construir um código SQL para executar no SGBD SQLite. A entrega deverá seguir o formato descrito abaixo. As questões tambem seguem abaixo.

Entrega:

Este arquivo deve ser enviado por e-mail ao endereço do professor com o assunto "CI218-Trab1" (exatamente). IMPORTANTE: Minha caixa de email usa o assunto do email como filtro. O trabalho é individual.

Formato de entrega:

O formato do arquivo será GRR000000.tar.gz contendo a sua matricula enviado para o meu email. O arquivo devera conter SOMENTE os c?digos SQL. Segue abaixo o comando para gerar os arquivos de entrega:

$ mkdir GRR000000
$ cd GRR000000
$ touch q1_test.sql \
q2_tipo_pecas.sql \
q3_segmento_cliente.sql \
q4_tipo_encomendas.sql \
q5_qtd_encomendas.sql \
q6_qtd_itens.sql \
q7_qtd_itens_maior.sql \
q8_not_special.sql \
q9_not_ordem.sql \
q10_only_special_ordem.sql

Carregando e verificando o BD:

Aqui está o download do BD TPC-H que iremos usar no trabalho ou use o comando:

$ wget http://www.inf.ufpr.br/eduardo/ensino/ci218/SQL/tpch.db

Apos baixar o BD, opcionalmente voce podera verificar sua integridade com o seguinte commando (os valores devem ser iguais):

$ cksum tpch.db 
536004849 12226560 tpch.db

O BD tem tamanho aproximado de 12M e deve ser acessado no SQLite de acordo com o comando abaixo:

                                                                                                         
$ sqlite3 tpch.db

Voce podera imprimir o esquema do BD com o commando abaixo:

                                                                                                         
sqlite> .tables
CUSTOMER  LINEITEM  NATION    ORDERS    PART      PARTSUPP  REGION    SUPPLIER

O BD do TPC-H possui o seguinte esquema relacional:

Voce podera imprimir o esquema de cada tabela com o commando .schema. Por exemplo, listamos o esquema da tabela NATION abaixo:

                                                                                                         
sqlite> .schema NATION
CREATE TABLE NATION (
  N_NATIONKEY INTEGER PRIMARY KEY NOT NULL,
  N_NAME      TEXT NOT NULL,
  N_REGIONKEY INTEGER NOT NULL,
  N_COMMENT   TEXT,
  FOREIGN KEY (N_REGIONKEY) REFERENCES REGION(R_REGIONKEY)
);

A seguir vemos uma linha tabela NATION (note que os atributos são separado por "|"):

0|ALGERIA|0|final accounts wake quickly. special reques

Programando em SQL:

Agora vamos programar em SQL e colocar os programas nos arquivos de entrega. Primeiro faremos um SQL teste pra verificar se a resposta bate com o esperado da avaliacao.

Q1 Teste:

sqlite> select distinct(r_name) from REGION;
AFRICA
AMERICA
ASIA
EUROPE
MIDDLE EAST

LEMBRETE: Voce deve colocar SOMENTE o comando SQL no arquivo "q1_test.sql" SEM os dados. Este arquivo devera ser colocado no subdiretorio com seu GRR (por ex., GRR000000). Nao esqueca que o ";" finaliza o comando SQL.

Q2 Tipos de peças disponiveis:

Objetivo: liste os tipos distintos de peça dos produtos (tabela PART).

Detalhe: mostre somente o tipo distinto de peça sem ordenar os dados.

Arquivo: q2_tipo_pecas.sql

Q3 Quantidade de clientes pelo segmento de mercado:

Objetivo: Conhecer o mercado da clientela (tabela CUSTOMER).

Detalhe: mostre o segmento de mercado e conte o numero de clientes para cada segmento. Por ex. uma linha da resposta:

AUTOMOBILE|302

Arquivo: q3_segmento_cliente.sql

Q4 Liste o status das encomendas:

Objetivo: liste os tipos distintos de status das encomendas (tabela ORDERS).

Detalhe: mostre somente o tipo distinto de status da encomenda sem ordenar os dados

Arquivo: q4_tipo_encomendas.sql

Q5 Liste os países que mais realizaram encomendas:

Objetivo: liste os países e a quantidade de encomendas de cada país.

Detalhe: mostre o nome do país e sua quantidade de encomendas ordenada de forma decrescente pela quantidade.

Arquivo: q5_qtd_encomendas.sql

Q6 Liste a quantidade de itens encomendados pelos países (somente os TOP 10):

Objetivo: liste os países e a quantidade de itens de cada país.

Detalhe: mostre o nome do país e sua quantidade de itens encomendados ordenada de forma decrescente pela quantidade. Mostre somente os 10 países que mais possuem itens.

Arquivo: q6_qtd_itens.sql

Q7 Liste a quantidade de itens encomendados pelos países com mais de 62000 itens :

Objetivo: liste os países e a quantidade de itens de cada país.

Detalhe: mostre o nome do país e sua quantidade de itens encomendados ordenada de forma decrescente pela quantidade. Mostre somente os países que possuem mais de 62.000 de itens encomendados.

Arquivo: q7_qtd_itens_maior.sql

Q8 Quantidade de clientes que nunca fizeram 'special request':

Objetivo: contar os clientes que fizeram ordem de compra mas nunca fizeram 'special request': não podem ter o string 'special request' no comentário de compra O_COMMENT.

Detalhe: mostre somente a quantidade dos clientes

Arquivo: q8_not_special.sql

Q9 Quantidade de clientes que nunca fizeram ordem de compra:

Objetivo: contar os clientes que nunca fizeram ordem de compra.

Detalhe: mostre somente a quantidade dos clientes.

Arquivo: q9_not_ordem.sql

Q10 Quantidade de clientes que somente fizeram 'special request', mas sem pacote incomum:

Objetivo: contar os clientes que fizeram ordem de compra com O_COMMENT '%special request%' e nunca pediram O_COMMENT '%unusual package%'.

Detalhe: mostre somente a quantidade dos clientes.

Arquivo: q10_only_special_ordem.sql