Aulas práticas - Ficha 4

Bases de Dados (CC2005), Dep. Ciência de Computadores, FCUP

Eduardo R. B. Marques, DCC/FCUP

Objectivos: introdução ao uso de SQL usando uma pequena base de dados como exemplo.

Referências: Introdução a SQL

1. Ambientação à BD exemplo

Carregamento da BD

Usaremos a BD "Social Network" exemplificada nas aulas teóricas.

Obtenha o arquivo sn.sql.

De seguida pode executar as instruções em sn.sql usando a consola mysql nos computadores de laboratório da seguinte forma:

Diagrama do esquema relacional

Explorando um pouco a BD ...

2. Consultas de dados

Forma simples de uma instrução SELECT:

SELECT <Campo 1>,..., <Campo n>    # Usar * para todos os campos
FROM <TABELA> WHERE <Condição>;

Use instruções SELECT por forma a obter:

  1. Os dados completos de todos os utilizadores do sexo feminino (Sex='F').

    +-----+----------+------------+---------------+------------+-----+-----------+------------------------------+
    | Num | Login    | Joined     | Name          | BirthDate  | Sex | Phone     | Email                        |
    +-----+----------+------------+---------------+------------+-----+-----------+------------------------------+
    |   3 | maria    | 2019-12-02 | Maria Silva   | 2005-11-17 | F   | 939939939 | maria@xyz.pt                 |
    |   5 | mendocas | 2019-12-11 | Filipa Mendes | 2002-05-03 | F   |      NULL | filipa.mendes@gmail.com      |
    |   6 | eva      | 2019-12-15 | Eva Mendes    | 1975-11-18 | F   |      NULL | i_ate_the_apple@paradise.com |
    |   9 | catwoman | 2019-12-16 | Selina Kyle   | 1940-01-01 | F   |      NULL | catwoman@dccomics.com        |
    |  11 | lady.z   | 2020-01-01 | Lady Stardust | 1972-01-01 | F   |      NULL | ladyz@mars.com               |
    +-----+----------+------------+---------------+------------+-----+-----------+------------------------------+
  2. Apenas o "login" e nome dos utilizadores de sexo feminino.

    +----------+---------------+
    | Login    | Name          |
    +----------+---------------+
    | maria    | Maria Silva   |
    | mendocas | Filipa Mendes |
    | eva      | Eva Mendes    |
    | catwoman | Selina Kyle   |
    | lady.z   | Lady Stardust |
    +----------+---------------+
  3. O "login" dos utilizadores que não têm nº de telefone definido (PHONE IS NULL).

    +------------+
    | Login      |
    +------------+
    | joao.pinto |
    | pedro      |
    | mendocas   |
    | eva        |
    | batman     |
    | catwoman   |
    | ziggy      |
    | lady.z     |
    +------------+ 
  4. O "login" e número de telefone dos utilizadores que têm nº de telefone definido (PHONE IS NOT NULL).

    +--------+-----------+
    | Login  | Phone     |
    +--------+-----------+
    | semedo | 223774327 |
    | maria  | 939939939 |
    | pedro2 | 213884445 |
    +--------+-----------+
  5. O "login" e a data de nascimento de utilizadores que tenham nascido antes do ano 2000 (Year(BirthDate) < 2000).

    +------------+------------+
    | Login      | BirthDate  |
    +------------+------------+
    | joao.pinto | 1976-12-19 |
    | semedo     | 1985-06-02 |
    | pedro      | 1982-01-03 |
    | eva        | 1975-11-18 |
    | pedro2     | 1993-02-22 |
    | batman     | 1939-01-01 |
    | catwoman   | 1940-01-01 |
    | ziggy      | 1972-01-01 |
    | lady.z     | 1972-01-01 |
    +------------+------------+
  6. O "login" e a data de nascimento de utilizadores que tenham nascido antes do ano 2000 e que sejam do sexo feminino (Year(BirthDate) < 2000 AND Sex = 'F').

    +----------+------------+
    | login    | birthdate  |
    +----------+------------+
    | eva      | 1975-11-18 |
    | catwoman | 1940-01-01 |
    | lady.z   | 1972-01-01 |
    +----------+------------+
  7. O "login" e o ano de nascimento de utilizadores que tenham nascido no ano 2000 ou depois (Year(BirthDate) >= 2000).

    +----------+-----------------+
    | Login    | Year(Birthdate) |
    +----------+-----------------+
    | maria    |            2005 |
    | mendocas |            2002 |
    +----------+-----------------+
  8. O número, autor e texto de "posts" que tenham a sequência '...' no seu texto (Content LIKE '%...%').

    Nota: A LIKE B é verdadeiro se A verifica a expressão regular SQL expressa por B. Caracteres especiais em B incluem _ que denotam um (e apenas um) qualquer caracter e % para qualquer sequência de 0 ou mais caracteres. Veja a documentação do MySQL a respeito.

    +-----+--------+----------------------------------------------------------------------+
    | Num | Author | Content                                                              |
    +-----+--------+----------------------------------------------------------------------+
    |   4 |      2 | Nothing to do except posting ... anyone there?                       |
    |   9 |      9 | Joker and I went out last night ... but pesky Batman had to show up. |
    |  10 |      9 | ... we ran away on the Joker-mobile!                                 |
    |  12 |     10 | Happy new year star-gazers! 2020 will rock ...                       |
    +-----+--------+----------------------------------------------------------------------+
  9. Os números de utilizadores que são seguidores do utilizador número 7.

    +----------+
    | Follower |
    +----------+
    |        1 |
    |       10 |
    |       11 |
    +----------+
  10. O autor e o conteúdo de comentários ao "post" número 8.

    +--------+---------------------+
    | Author | Content             |
    +--------+---------------------+
    |      2 | Poor Batman!        |
    |      9 | Get a life darling! |
    +--------+---------------------+

3. Inserções de dados

Forma geral:

INSERT INTO <TABELA>(<Campo 1>, ..., <Campo n>)
VALUES (<Valor 1.1>, ..., <Valor 1.n>), 
       ...,
       (<Valor m.1>, ..., <Valor m.n>);

Nota: pode sempre reinicializar o estado da BD sn.sql, voltando a correr todos os comandos nesse ficheiro.

3.1

Use uma instrução INSERT para adicionar um novo utente master.of.sql na tabela USER com os seguintes dados:

Consulte a tabela USER para verificar o resultado da inserção. Tome nota do número do utilizador criado.

3.2

Proceda de forma análoga para as seguintes inserções, reflectindo as actividade na rede social do utilizador master.of.sql criado no passo anterior:

  1. POST_LIKED: Um "like" ao post com número 1;
  2. FOLLOWER: o seguimento dos utilizadores 1 e 2.
  3. COMMENT: a escrita de um comentário para o "post" 1 com conteúdo 'LOL' e o instante (data-hora) actual atribuido ao campo Creation recorrendo à função NOW();
  4. POST: A escrita de um "post" com conteúdo 'I love SQL' e o instante (data-hora) actual atribuido ao campo Creation recorrendo à função NOW();;
  5. HASHTAG: a definição das "hash-tags" 'sql' e 'db' para o "post" criado no passo anterior.

4. Actualização de dados

Forma geral de uma instrução UPDATE simples:

UPDATE <TABELA>
SET <Campo 1> = <Valor 1>,
    ...
    <Campo n> = <Valor n>
WHERE <Condição>;

Use instruções UPDATE por forma a actualizar:

  1. O email para 'maria@dcc.fc.up.pt' do utilizador com "login" 'maria'.
  2. O nome para 'Secret Identity' e o email para 'batman@gotham.com' para o utilizador com nome 'Bruce Wayne'.
  3. Os números de telefone para o valor NULL para utilizadores com número inferior a 5.

5. Remoção de dados

Forma geral:

DELETE FROM <TABELA> WHERE <Condição>;

Use instruções DELETE por forma a remover:

  1. O "post" com número 5.
  2. Os comentários feitos pelo utilizador 2.
  3. Todas as entradas em FOLLOWER que envolvam o utilizador 2 (User = 2 OR Follower=2).

6. Consultas com ORDER BY, LIMIT, e DISTINCT

Escreva consultas para obter (os resultados esperados são listados para cada consulta; reponha o estado original da BD em sn.sql).

  1. O "login" e o nome de utilizadores, ordenados por "login";

    +------------+----------------+
    | Login      | Name           |
    +------------+----------------+
    | batman     | Bruce Wayne    |
    | catwoman   | Selina Kyle    |
    | eva        | Eva Mendes     |
    | joao.pinto | João Pinto     |
    | lady.z     | Lady Stardust  |
    | maria      | Maria Silva    |
    | mendocas   | Filipa Mendes  |
    | pedro      | Pedro Costa    |
    | pedro2     | Pedro Simões   |
    | semedo     | Carlos Semedo  |
    | ziggy      | Ziggy Stardust |
    +------------+----------------+
    11 rows in set (0.01 sec)
  2. O "login", nome, e data de nascimento de utilizadores ordenados primeiro por data de nascimento de forma decrescente, e depois (como segundo critério) por "login" (de forma crescente).

    +------------+----------------+------------+
    | Login      | Name           | BirthDate  |
    +------------+----------------+------------+
    | maria      | Maria Silva    | 2005-11-17 |
    | mendocas   | Filipa Mendes  | 2002-05-03 |
    | pedro2     | Pedro Simões   | 1993-02-22 |
    | semedo     | Carlos Semedo  | 1985-06-02 |
    | pedro      | Pedro Costa    | 1982-01-03 |
    | joao.pinto | João Pinto     | 1976-12-19 |
    | eva        | Eva Mendes     | 1975-11-18 |
    | lady.z     | Lady Stardust  | 1972-01-01 |
    | ziggy      | Ziggy Stardust | 1972-01-01 |
    | catwoman   | Selina Kyle    | 1940-01-01 |
    | batman     | Bruce Wayne    | 1939-01-01 |
    +------------+----------------+------------+
    11 rows in set (0.00 sec)
  3. Os mesmos dados da consulta 2, mas restritos a utilizadores do sexo masculino (use WHERE).

    +------------+----------------+------------+
    | Login      | Name           | BirthDate  |
    +------------+----------------+------------+
    | pedro2     | Pedro Simões   | 1993-02-22 |
    | semedo     | Carlos Semedo  | 1985-06-02 |
    | pedro      | Pedro Costa    | 1982-01-03 |
    | joao.pinto | João Pinto     | 1976-12-19 |
    | ziggy      | Ziggy Stardust | 1972-01-01 |
    | batman     | Bruce Wayne    | 1939-01-01 |
    +------------+----------------+------------+
    6 rows in set (0.00 sec)
  4. Os mesmos dados da consulta 2, mas limitados a 3 resultados (use LIMIT).

    +----------+---------------+------------+
    | Login    | Name          | BirthDate  |
    +----------+---------------+------------+
    | maria    | Maria Silva   | 2005-11-17 |
    | mendocas | Filipa Mendes | 2002-05-03 |
    | pedro2   | Pedro Simões  | 1993-02-22 |
    +----------+---------------+------------+
    3 rows in set (0.00 sec)
  5. O número, autor, conteúdo, e data de criação dos 5 posts mais recentes, ordenados de forma decrescente por data de criação.

    +-----+--------+----------------------------------------------------------------------+---------------------+
    | Num | Author | Content                                                              | Creation            |
    +-----+--------+----------------------------------------------------------------------+---------------------+
    |  11 |      9 | Joker is a busy man, he has no time for social networking.           | 2020-02-18 17:00:30 |
    |  10 |      9 | ... we ran away on the Joker-mobile!                                 | 2020-02-17 23:51:12 |
    |   9 |      9 | Joker and I went out last night ... but pesky Batman had to show up. | 2020-02-17 23:51:00 |
    |   3 |      1 | Não foi nada de especial, chutei com o pé que estava mais a mão!     | 2020-02-13 12:31:59 |
    |   8 |      9 | Batman is a troll! Joker is my sweetheart these days!                | 2020-02-10 23:41:00 |
    +-----+--------+----------------------------------------------------------------------+---------------------+
  6. As "hash-tags" usadas em "posts" (conforme a tabela HASHTAG) ordenadas por "post" (primeiro) e (depois) por "hash tag".

    +------+-------------+
    | Post | Tag         |
    +------+-------------+
    |    1 | fcp         |
    |    2 | azulibranco |
    |    2 | fcp         |
    |    3 | fcp         |
    |    4 | lonely      |
    |    5 | lonely      |
    |    6 | joker       |
    |    7 | kitty       |
    |    8 | batman      |
    |    8 | troll       |
    |    9 | batman      |
    |    9 | joker       |
    |    9 | troll       |
    |   10 | batman      |
    |   10 | joker       |
    |   10 | troll       |
    |   11 | joker       |
    |   12 | 2020        |
    +------+-------------+
    18 rows in set (0.00 sec)
  7. As "hash-tags" distintas usadas em associação a posts , ordenadas por "hashtag" (use DISTINCT além de ORDER BY).

    +-------------+
    | Tag         |
    +-------------+
    | 2020        |
    | azulibranco |
    | batman      |
    | fcp         |
    | joker       |
    | kitty       |
    | lonely      |
    | troll       |
    +-------------+
    8 rows in set (0.00 sec)
  8. Os números distintos de "posts" que têm algum comentário associado (na tabela COMMENT), ordenados de forma decrescente.

    +------+
    | Post |
    +------+
    |   12 |
    |    8 |
    |    1 |
    +------+
    3 rows in set (0.00 sec)
  9. O login, e o nome de todos os utilizadores do sexo feminino que tenham um 'y' no seu nome (use o operador LIKE), ordenados por "login".

    +----------+---------------+
    | Login    | Name          |
    +----------+---------------+
    | catwoman | Selina Kyle   |
    | lady.z   | Lady Stardust |
    +----------+---------------+
  10. O número, altura de criação e o texto de todos os "posts" feitos entre as 05:00 e as 18:00, ordenados pela altura de criação descendente.

    +-----+---------------------+---------------------------------------------------------------------+
    | Num | Creation            | Content                                                             |
    +-----+---------------------+---------------------------------------------------------------------+
    |  11 | 2020-02-18 17:00:30 | Joker is a busy man, he has no time for social networking.          |
    |   3 | 2020-02-13 12:31:59 | Não foi nada de especial, chutei com o pé que estava mais a mão!    |
    |   1 | 2020-01-13 12:31:59 | Comigo, ou sem-migo, o Porto vai ser campeão!                       |
    |   6 | 2020-01-01 11:23:56 | Joker, you are dead!                                                |
    |   5 | 2019-12-24 05:00:00 | I guess nobody is online!                                           |
    +-----+---------------------+---------------------------------------------------------------------+
    5 rows in set (0.00 sec)
  11. O "login" e a data de nascimento dos 3 utilizadores do sexo masculino mais velhos, ordenados por data de nascimento.

    +------------+------------+
    | Login      | BirthDate  |
    +------------+------------+
    | batman     | 1939-01-01 |
    | ziggy      | 1972-01-01 |
    | joao.pinto | 1976-12-19 |
    +------------+------------+
  12. O "login" de utlizadores, a altura de entrada na rede social (valor de Joined), e o tempo há que faziam parte da rede social à data de '2020-01-10' na escala de dias, ordenados por este último valor.

    Use TIMESTAMPDIFF(...) AS Days (consulte a documentação MySQL) para obter o campo (derivado) correspondente chamado Days na resposta.

    +------------+------------+------+
    | Login      | Joined     | Days |
    +------------+------------+------+
    | lady.z     | 2020-01-01 |    9 |
    | ziggy      | 2019-12-31 |   10 |
    | pedro2     | 2019-12-16 |   25 |
    | batman     | 2019-12-16 |   25 |
    | catwoman   | 2019-12-16 |   25 |
    | eva        | 2019-12-15 |   26 |
    | mendocas   | 2019-12-11 |   30 |
    | pedro      | 2019-12-10 |   31 |
    | semedo     | 2019-12-02 |   39 |
    | maria      | 2019-12-02 |   39 |
    | joao.pinto | 2019-12-01 |   40 |
    +------------+------------+------+
    11 rows in set (0.00 sec)
    
  13. De forma análoga, obtenha o "login", data de nascimento, e idade dos 4 utilizadores mais novos à data de '2020-01-10', ordenados por idade.

    +----------+------------+------+
    | Login    | BirthDate  | Age  |
    +----------+------------+------+
    | maria    | 2005-11-17 |   14 |
    | mendocas | 2002-05-03 |   17 |
    | pedro2   | 1993-02-22 |   26 |
    | semedo   | 1985-06-02 |   34 |
    +----------+------------+------+
    4 rows in set (0.00 sec)