Programação e Bases de Dados - Ficha 09 - Guia de resolução

Exercício 2

2.1

INSERT INTO UTENTE(Num, CC, Nome, DataNasc, Sexo, Telefone, Email)
VALUES(8, 18848333, 'Mariana Silva', '2000-01-23', 'F', 227348900, NULL);

2.2

select * from utente where num=8;

2.3

mysql> select ISBN from livro where título = 'Os Lusíadas';
+---------------+
| ISBN          |
+---------------+
| 9789722709620 |
+---------------+
1 row in set (0.00 sec)
mysql> select num from cópia where isbn = 9789722709620 and emputente is NULL;
+-----+
| num |
+-----+
|   2 |
+-----+
1 row in set (0.01 sec)
mysql> update cópia set
      emputente=8,
      empdata='2019-03-05'
      where
          isbn = 9789722709620
      and num = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.4

mysql> select emputente from cópia
       where isbn = 9789722709620 and num=1;
+-----------+
| emputente |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
mysql> update cópia set
         emputente = NULL,
         empdata = NULL
        where isbn = 9789722709620 and num=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.5

mysql> update cópia set estante='E13' where estante='E12';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> delete from prateleira where estante='E12';
Query OK, 3 rows affected (0.00 sec)
mysql> delete from estante where código='E12';
Query OK, 1 row affected (0.01 sec)

2.6

mysql> select isbn from livro where ano > 2010;
+---------------+
| isbn          |
+---------------+
| 9789720049759 |
| 9789722526289 |
+---------------+
2 rows in set (0.00 sec)
mysql> select isbn from autores
       where nome='Luís de Camões'
       or nome='Fernando Pessoa';
+---------------+
| isbn          |
+---------------+
| 9789720049759 |
| 9789722526289 |
| 9789722709620 |
+---------------+
3 rows in set (0.01 sec)
mysql> select isbn from cópia
       where  prateleira = 3 and
       estante = 'E99'
       and emputente is null;
+---------------+
| isbn          |
+---------------+
| 9780131103627 |
| 9780321356680 |
+---------------+
2 rows in set (0.00 sec)

Exercício 3

3.1

CREATE TABLE FUNCIONÁRIO
(
  /* Como em UTENTE */
  Num INT NOT NULL,
  CC INT NOT NULL,
  Nome VARCHAR(64) NOT NULL,
  DataNasc DATE NOT NULL,
  Sexo ENUM('M', 'F') NOT NULL,
  Telefone INT NOT NULL,
  Email VARCHAR(32),
  PRIMARY KEY(Num),
  UNIQUE(CC),

  /* Caracterização extra */
  Cargo VARCHAR(16) NOT NULL,
  Supervisor INT,
  FOREIGN KEY(Supervisor) REFERENCES FUNCIONÁRIO(Num)
);

3.2

CREATE TABLE REVISTA
(
  Num INT NOT NULL,
  Título VARCHAR(64) NOT NULL,
  Periodicidade ENUM('S', 'M', 'A') NOT NULL,
  Editora VARCHAR(32) NOT NULL,
  PRIMARY KEY(Num),
  UNIQUE(Título)
);
CREATE TABLE EDIÇÃO_DE_REVISTA
(
  NumR INT NOT NULL,
  NumEd INT NOT NULL,
  Data DATE NOT NULL,
  PRIMARY KEY(NumR, NumEd),
  FOREIGN KEY(NumR) REFERENCES REVISTA(Num)
);

Exercício 4

4.1

/* 1 */
SELECT Nome, YEAR(DataNasc)
FROM UTENTE;

/* 2 */
SELECT Nome, MONTH(DataNasc), YEAR(DataNasc)
FROM UTENTE
WHERE YEAR(DataNasc) > 2000;

/* 3 */
SELECT Nome
FROM UTENTE
WHERE Nome LIKE 'Pedro%' OR Nome LIKE '%Silva';

/* 4 */
SELECT ISBN, Num, EmpUtente
FROM CÓPIA
WHERE EmpUtente IS NOT NULL AND EmpUtente <> 1;

/* 5 */
SELECT CONCAT(Título, ', ', Editora, ', ', Ano, ', ', ISBN)
FROM LIVRO;

4.2

/* 1 */
UPDATE UTENTE
SET Email = UPPER(Email);

/* 2 */
UPDATE CÓPIA
SET Estante='E99', Prateleira = Prateleira + 1
WHERE Estante LIKE 'E1%';

Exercício 5

5.1

Alterações em bd_bib.sql:


/* 1 */
CREATE TABLE UTENTE (
  ...
  Email VARCHAR(32) DEFAULT NULL,
  ...
);

/* 2 */
CREATE TABLE ESTANTE (
  ...
  Secção VARCHAR(3) NOT NULL DEFAULT 'L',
  ...
);

/* 3 */
CREATE TABLE CÓPIA (
  ...
  EmpUtente INT DEFAULT NULL,
  EmpData DATE DEFAULT NULL,
  ...
);

5.2

Basta adicionar AUTO_INCREMENT a UTENTE.Num e depois editar as inserções.

5.3

/* 1 */
ALTER TABLE LIVRO
DROP COLUMN Editora;

/* 2 */
ALTER TABLE LIVRO
ADD COLUMN
Conservação ENUM('Novo','Bom','Mau') NOT NULL
DEFAULT 'Bom';

5.4

CREATE TABLE AUTORES
(
  ...
  FOREIGN KEY(ISBN) REFERENCES LIVRO(ISBN)
  ON UPDATE CASCADE
);

CREATE TABLE CÓPIA
(
  ...
  FOREIGN KEY(ISBN) REFERENCES LIVRO(ISBN)
  ON UPDATE CASCADE,
  ...
);

Exercício 6

/* 1 */
SELECT Nome
FROM AUTORES
ORDER BY Nome;

/* 2 */
SELECT DISTINCT Nome
FROM AUTORES
ORDER BY Nome;

/* 3 */
SELECT *
FROM UTENTE
WHERE Sexo = 'M'
ORDER BY DataNasc DESC;

/* 4 */
SELECT *
FROM UTENTE
WHERE Sexo = 'F'
ORDER BY DataNasc DESC
LIMIT 1;

/* 5 */
SELECT *
FROM CÓPIA
WHERE EmpUtente IS NOT NULL
ORDER BY ISBN, EmpUtente;

/* 6 */
SELECT DISTINCT ISBN
FROM CÓPIA
WHERE EmpUtente IS NOT NULL;

/* 7 */
SELECT *
FROM CÓPIA
ORDER BY EmpData DESC
LIMIT 3;

Exercício 7

/* 1 */
SELECT COUNT(*) AS Total
FROM CÓPIA;

/* 2 */
SELECT COUNT(EmpUtente) AS TotalEmp
FROM CÓPIA;

/* 3 */
SELECT ISBN, COUNT(EmpUtente) AS TotalEmp
FROM CÓPIA
GROUP BY ISBN;

/* 4 */
SELECT ISBN, COUNT(EmpUtente) as TotalEmp
FROM CÓPIA
GROUP BY ISBN
HAVING TotalEmp >= 2;

/* 5 */
SELECT ISBN, MAX(EmpData) as UltEmp
FROM CÓPIA
GROUP BY ISBN
ORDER BY UltEmp;

/* 6 */
SELECT  AVG(TIMESTAMPDIFF(DAY, EmpData, NOW())) AS Média
FROM CÓPIA;

/* 7 */
SELECT  ISBN, AVG(TIMESTAMPDIFF(DAY, EmpData, NOW())) AS Média
FROM CÓPIA
GROUP BY ISBN;

Exercício 8

/* 1 */
SELECT
  U.Nome, C.ISBN, C.Num, C.EmpData
FROM
  UTENTE U, CÓPIA C
WHERE
  C.EmpUtente = U.Num
ORDER BY U.Nome;

/* 2 */
SELECT
  U.Nome, L.Título, C.Num, C.EmpData
FROM
  UTENTE U, LIVRO L, CÓPIA C
WHERE
  C.EmpUtente = U.Num
  AND
  L.ISBN = C.ISBN
ORDER BY U.Nome, L.Título;

/* 3*/
SELECT
  L.Título,
  COUNT(*) AS T,
  COUNT(EmpUtente) AS E,
  COUNT(*) -  COUNT(EmpUtente) AS D
FROM
  LIVRO L, CÓPIA C
WHERE
  L.ISBN = C.ISBN
GROUP BY Título
ORDER BY T, L.Título;

Exercício 9

/* 1 */
SELECT
 Título
FROM
 LIVRO L
WHERE
  L.ISBN IN
    (SELECT ISBN FROM CÓPIA
     WHERE ISBN = L.ISBN);

/* OU */
SELECT
 Título
FROM
 LIVRO L
WHERE
  EXISTS
    (SELECT * FROM CÓPIA
     WHERE ISBN = L.ISBN);

/* 2 */
SELECT
  U.Num, U.Nome
FROM
  UTENTE U
WHERE
  1 = (SELECT COUNT(EmpUtente) FROM CÓPIA
       WHERE EmpUtente = U.Num);

/* 3 */
SELECT
  U.Num, U.Nome
FROM
  UTENTE U
WHERE
  1 < (SELECT COUNT(EmpUtente) FROM CÓPIA
       WHERE EmpUtente = U.Num);

/* 4 */
SELECT
  U.Nome,
  (SELECT COUNT(EmpUtente)
   FROM CÓPIA
   WHERE EmpUtente = U.Num) AS NumCópias
FROM UTENTE U;

Exercício 10

/* 1 */
/* Não deveria ser necessário
  WHERE EmpUtente IS NOT NULL
mas é! Aparentemente as entradas NULL
afectam o comportamento de IN!
*/
DELETE FROM UTENTE
WHERE Num NOT IN
(SELECT EmpUtente FROM CÓPIA
 WHERE EmpUtente IS NOT NULL);

/* Alternativa (IS NOT NULL novamente necessário!) */
DELETE FROM UTENTE
WHERE Num <> ALL
(SELECT EmpUtente FROM CÓPIA
 WHERE EmpUtente IS NOT NULL);

/* 2 */
DELETE FROM CÓPIA
WHERE EmpUtente IS NULL
AND ISBN =
(SELECT ISBN FROM LIVRO
 WHERE Título = 'Astérix o Gaulês');

/* 3 */
UPDATE CÓPIA
SET EmpUtente = NULL,
    EmpData = NULL
WHERE EmpUtente =
      (SELECT Num FROM UTENTE
       WHERE NOME='Pedro Costa');

/* 4 */
UPDATE CÓPIA
SET Estante = 'E12',
    Prateleira = 3
WHERE
   ISBN IN
      (SELECT ISBN FROM AUTORES
       WHERE Nome='Luís de Camões'
       OR Nome = 'Fernando Pessoa');

Exercício 11


/* 1 */
SELECT Nome, Título
FROM UTENTE U JOIN CÓPIA C ON(U.Num = C.EmpUtente)
ORDER BY Nome,Título;

/* 2 */
SELECT Nome, COUNT(*) AS NumCópias
FROM UTENTE U JOIN CÓPIA C ON(U.Num = C.EmpUtente)
GROUP BY Nome
ORDER BY Nome;

/* 3 */
SELECT Nome, Título
FROM AUTORES NATURAL JOIN  LIVRO
ORDER BY Nome,Título;

/* 4 */
SELECT Nome, Título, COUNT(*) As NumCópias
FROM AUTORES A JOIN  LIVRO L JOIN CÓPIA C
ON(A.ISBN = L.ISBN AND A.ISBN = C.ISBN)
GROUP BY Nome, Título
ORDER BY Nome,Título;

SELECT Nome, Título, COUNT(*) As NumCópias
FROM AUTORES NATURAL JOIN LIVRO NATURAL JOIN CÓPIA
GROUP BY Nome, Título
ORDER BY Nome,Título;

/* 5 */
SELECT Nome, Título, COUNT(*) As NumCópias, COUNT(EmpData) AS NumEmprestadas
FROM AUTORES NATURAL JOIN LIVRO NATURAL JOIN CÓPIA
GROUP BY Nome, Título
ORDER BY Nome,Título;

/* 6 */
SELECT S.Código, COUNT(*)
FROM
SECÇÃO S JOIN ESTANTE E JOIN CÓPIA C
ON(E.Secção = S.Código AND C.Estante=E.Código)
GROUP BY S.Código;

/* 7 */
SELECT *
FROM ESTANTE E LEFT OUTER JOIN CÓPIA C
On(C.Estante = E.Código);

/* ou o inverso */
SELECT *
FROM  CÓPIA C RIGHT OUTER JOIN  ESTANTE E
On(C.Estante = E.Código);

Exercício 12

/* 1 */
 CREATE VIEW ARRUMAÇÃO(Título, NumCópia, Prateleira, Estante)
 AS (
   SELECT L.Título, C.Num, C.Estante, C.Prateleira
   FROM LIVRO L NATURAL JOIN CÓPIA C
   ORDER BY L.Título, C.Num
 );

/* 2 */
CREATE VIEW LIVROS_POR_SECÇÃO(SCódigo, SDesc, Total)
AS (
  SELECT S.Código, S.Descrição, COUNT(*)
  FROM
       CÓPIA C JOIN ESTANTE E ON(C.Estante = E.Código)
       JOIN Secção S ON(S.Código = E.Secção)
  GROUP BY S.Código
  ORDER BY S.Código
);