Aulas práticas - Ficha 4 - algumas soluções

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

Eduardo R. B. Marques, DCC/FCUP

1.

1.1

DROP TABLE IF EXISTS USER_GROUP;

CREATE TABLE IF NOT EXISTS USER_GROUP
(
   Num INT PRIMARY KEY AUTO_INCREMENT,
   Creation DATETIME NOT NULL DEFAULT NOW(),
   Name VARCHAR(64) UNIQUE NOT NULL,
   Description TEXT
);

1.4

INSERT INTO USER_GROUP(Num, Creation, Name, Description)
VALUES (1, '2020-01-01 12:34:56', 'Horror Movies', NULL);

Integridade de chave primária: ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 

INSERT INTO USER_GROUP(Name,Description)
VALUES ('Azul e branco', 'Adeptos do FCP - grupo alternativo');

Integridade de chave secundária (campo Name tem modificador UNIQUE): ERROR 1062 (23000): Duplicate entry 'Azul e branco' for key 'Name' 

INSERT INTO USER_GROUP(Creation, Name,Description)
VALUES (NULL, 'Horror Movies', 'A group about horror movies');

Integridade de domínio: ERROR 1048 (23000): Column 'Creation' cannot be null

INSERT INTO USER_GROUP(Creation, Name,Description)
VALUES ('2020-02-31 12:34:56', 'Horror Movies', 'A group about horror movies');

Integridade de domínio (31 de Fevereiro!): ERROR 1292 (22007): Incorrect datetime value: '2020-02-31 12:34:56' for column 'Creation' at row 1

2

2.1

DROP TABLE IF EXISTS GROUP_MEMBER;

CREATE TABLE IF NOT EXISTS GROUP_MEMBER
(
   UNum INT NOT NULL,
   GNum INT NOT NULL,
   Role ENUM('Admin','Moderator','Member') NOT NULL,
   PRIMARY KEY(UNum,GNum),
   FOREIGN KEY(UNum) REFERENCES USER(Num),
   FOREIGN KEY(GNum) REFERENCES USER_GROUP(Num)
);

3

ALTER TABLE POST 
ADD COLUMN GNum INT DEFAULT NULL;

ALTER TABLE POST 
ADD FOREIGN KEY(GNum) REFERENCES USER_GROUP(Num);

4

ALTER TABLE GROUP_MEMBER 
MODIFY Role ENUM('Admin','Moderator','Member','Guest') NOT NULL;

ALTER TABLE USER 
ADD UNIQUE(Email);

ALTER TABLE USER
DROP COLUMN Phone;

5

5.3

SELECT Login,Name,BirthDate
FROM USER
WHERE Sex='M'
ORDER BY BirthDate DESC, Login;

5.4

SELECT Login,Name,BirthDate
FROM USER
ORDER BY BirthDate DESC, Login
LIMIT 3;

5.7

SELECT DISTINCT Tag 
FROM HASHTAG 
ORDER BY Tag;

5.9

SELECT Login, Name 
FROM USER 
WHERE Sex='F' AND Name Like '%y%' 
ORDER BY login;

5.10

SELECT Num,Creation,Content 
FROM POST 
WHERE HOUR(Creation) >= 5 AND HOUR(Creation) < 18 
ORDER BY Creation DESC;

5.11

SELECT Login,BirthDate 
FROM USER 
WHERE Sex = 'M' 
ORDER BY BirthDate 
LIMIT 3;

5.13

SELECT 
  Login, 
  BirthDate, 
  TIMESTAMPDIFF(YEAR, BirthDate, '2020-01-10') AS Age 
 FROM USER 
 ORDER BY Age
 LIMIT 4;