query.sql

CREATE TABLE IF NOT EXISTS PROJETO_FULLSTACK_USERS (
  id VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  nickname VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM("NORMAL", "ADMIN") DEFAULT "NORMAL"
);

SELECT * FROM PROJETO_FULLSTACK_USERS;
SELECT * FROM PROJETO_FULLSTACK_ALBUNS;
SELECT * FROM PROJETO_FULLSTACK_IMAGES;

DELETE FROM PROJETO_FULLSTACK_ALBUNS WHERE id = "209ad371-2ebe-4fbf-8263-ffb00c1f935a";

DELETE FROM PROJETO_FULLSTACK_ALBUNS WHERE id = "f066fad3-d87a-4848-96d0-fe67ab08f816";

SELECT * FROM PROJETO_FULLSTACK_USERS WHERE email = "lopsan" OR nickname = "lopsan";

UPDATE PROJETO_FULLSTACK_ALBUNS 
SET albumImageUrl = "https://i.imgur.com/rVDLVWr.jpg"
where id = "8d05d257-047c-4c15-af97-a619cab34738";

CREATE TABLE IF NOT EXISTS PROJETO_FULLSTACK_ALBUNS(
	id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description text NOT NULL,
    albumImageUrl text,
    user_id VARCHAR(255) NOT NULL,
    FOREIGN KEY(user_id) REFERENCES PROJETO_FULLSTACK_USERS(id)
);

CREATE TABLE IF NOT EXISTS PROJETO_FULLSTACK_IMAGES(
	id VARCHAR(255) PRIMARY KEY,
    description text,
    photoUrl text NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    album_id VARCHAR(255) NOT NULL,
    FOREIGN KEY(user_id) REFERENCES PROJETO_FULLSTACK_USERS(id),
    FOREIGN KEY(album_id) REFERENCES PROJETO_FULLSTACK_ALBUNS(id)
);

SELECT PROJETO_FULLSTACK_ALBUNS.id, PROJETO_FULLSTACK_ALBUNS.name, description, user_id, PROJETO_FULLSTACK_USERS.name as user_name FROM PROJETO_FULLSTACK_USERS
JOIN PROJETO_FULLSTACK_ALBUNS ON PROJETO_FULLSTACK_USERS.id = PROJETO_FULLSTACK_ALBUNS.user_id;

SELECT * FROM PROJETO_FULLSTACK_ALBUNS
WHERE user_id = "65e24262-1175-43fd-9b7e-5e2bfd93d501"
AND name LIKE '%u%';

ALTER TABLE PROJETO_FULLSTACK_IMAGES
MODIFY createdAt date NOT NULL;

describe PROJETO_FULLSTACK_IMAGES;

ALTER TABLE PROJETO_FULLSTACK_IMAGES
ADD createdAt date NOT NULL DEFAULT "2020-10-14";

select * from PROJETO_FULLSTACK_IMAGES;

UPDATE PROJETO_FULLSTACK_IMAGES
SET createdAt = "2020-10-13"
WHERE id = "92bb2fa3-98d9-4731-9f78-6b88175e3f72";

SELECT * FROM PROJETO_FULLSTACK_IMAGES
WHERE album_id = "8d05d257-047c-4c15-af97-a619cab34738"
AND description LIKE "%am%";

CREATE TABLE IF NOT EXISTS PROJETO_FULLSTACK_USER_RELATION (
  user_id VARCHAR(255) NOT NULL,
  user_to_follow_id VARCHAR(255) NOT NULL,
  primary key(user_id, user_to_follow_id),
  FOREIGN KEY(user_id) references PROJETO_FULLSTACK_USERS(id),
  FOREIGN KEY(user_to_follow_id) references PROJETO_FULLSTACK_USERS(id)
);

describe PROJETO_FULLSTACK_USER_RELATION;

select * from PROJETO_FULLSTACK_USERS
WHERE not id = "65e24262-1175-43fd-9b7e-5e2bfd93d501"
AND NOT role = "ADMIN";

select * from PROJETO_FULLSTACK_USER_RELATION;

SELECT PROJETO_FULLSTACK_USERS.id, PROJETO_FULLSTACK_USERS.name, PROJETO_FULLSTACK_USERS.nickname, PROJETO_FULLSTACK_ALBUNS.id as album_id, PROJETO_FULLSTACK_ALBUNS.name as album_name, PROJETO_FULLSTACK_ALBUNS.description as album_description, PROJETO_FULLSTACK_ALBUNS.albumImageUrl as album_imageUrl,
PROJETO_FULLSTACK_ALBUNS.createdAt as album_createdAt
FROM PROJETO_FULLSTACK_USERS
JOIN PROJETO_FULLSTACK_ALBUNS on PROJETO_FULLSTACK_USERS.id = PROJETO_FULLSTACK_ALBUNS.user_id
JOIN PROJETO_FULLSTACK_USER_RELATION on PROJETO_FULLSTACK_ALBUNS.user_id = PROJETO_FULLSTACK_USER_RELATION.user_to_follow_id
WHERE PROJETO_FULLSTACK_USER_RELATION.user_id = "1ae8efae-c263-4ea7-8155-43c42feaae11";