Complexe DML

De onderstaande 2 opdrachten had ik doordat ik de eerste verkeerd had begrepen samen met 1 query uitgevoerd.

/* Vanuit de helpdesk zoeken ze de gegevens van gebruiker "John Designer", maar ze kunnen hem niet vinden.

  Hij krijgt namelijk een nieuwe rol als "ontwerper". Voeg dus een nieuwe rol toe hiervoor.

  Bepaal de laatste rol aan de hand van een query en niet via harde getypte waardes!

*/

* Zojuist heb je een nieuwe rol toegevoegd. TOP! Kun je nu ook deze rol ("ontwerper") koppelen aan een nieuwe gebruiker die

  momenteel nog geen rol heeft? Pas hiervoor het bestaande record aan.

  TIP: Gebruik een subquery om te bepalen wat het ID is van de rol ontwerper.Dit wordt dan het id dat je aanpast in de tabel.

*/

WITH nieuwe_rol AS (

   INSERT INTO public.roles (name)

   VALUES ('ontwerper')

   RETURNING id

)

UPDATE public.users

SET role_id = (SELECT id FROM nieuwe_rol)

WHERE username = 'john_designer';

/* Geef in de users tabel in dat jij ook iets vanuit de webshop wil bestellen.

  Dit betekent dat jij ook hierin moet voorkomen zoals alle andere gebruikers getoond worden.

  Dus ook een "_" tussen je twee namen. Hierbij heb je de rol "customer".

  EXTRA: De gegevens moet je ophalen via een query met de functie MAX(usr.id) om de nieuwe user-id toe te voegen.

  TIP: Gebruik een subquery in de FROM om te bepalen wat het ID is van de rol ontwerper, maar ook wat het maximum gebruiker ID is. */

INSERT INTO public.users (id, username, password_hash, email, role_id, created_at)

SELECT

   (sub.max_id + 1),              

   'ralf_conraads',                   

   'hashed_wachtwoord_1234',       

   '517226@vistacollege.nl',      

   sub.customer_role_id,          

   NOW()                          

FROM (

   SELECT

       MAX(id) AS max_id,

       (SELECT id FROM public.roles WHERE name = 'customer') AS customer_role_id

   FROM public.users

) AS sub;

SELECT u.id, u.username, u.password_hash, u.email, r.name AS rol_naam

FROM public.users u

JOIN public.roles r ON u.role_id = r.id

WHERE u.id = (SELECT MAX(id) FROM public.users);

/* Zojuist heeft zich een nieuwe ontwerper aangemeld bij de helpdesk met de naam; Martin Rodrigo.

  Vanuit de administratie in het bedrijf hadden ze deze persoon graag toegevoegd gezien.

  Bepaal nu ook de user_id door de laatste te nemen en het id te verhogen.

  TIP: Gebruik de voorgaande query, maar pas de waardes enkel iets aan!

*/

INSERT INTO public.users (id, username, password_hash, email, role_id, created_at)

SELECT

   (sub.max_id + 1),              

   'martin_rodrigo',                   

   'hashed_wachtwoord_12324124',       

   'martin@rodrigo.nl',      

   sub.customer_role_id,          

   NOW()                          

FROM (

   SELECT

       MAX(id) AS max_id,

       (SELECT id FROM public.roles WHERE name = 'ontwerper') AS customer_role_id

   FROM public.users

) AS sub;

SELECT u.id, u.username, u.password_hash, u.email, r.name AS rol_naam

FROM public.users u

JOIN public.roles r ON u.role_id = r.id

WHERE u.id = (SELECT MAX(id) FROM public.users);

/* En meteen erna komt er ook de vraag bij de helpdesk je een bestelling kan plaatsen voor de huidige ontwerper 'John Designer'.

  Hij wil namelijk 10  "Bluetooth Speaker" bestellen, gelukkig voor jou staat deze wel al in de lijst.

  De vraag is nu of jij die kan bestellen voor hem. Hanteer de prijs en andere gegevens uit de andere tabellen.

  Hiervoor moet je ook de toevoeging via een Query uitvoeren niet met harde waardes.

*/

INSERT INTO public.orders (id, user_id, product_id, quantity, order_date)

SELECT

   (sub.max_order_id + 1),        

   sub.target_user_id,            

   sub.target_product_id,         

   10,                            

   NOW()                          

FROM (

   SELECT

       (SELECT MAX(id) FROM public.orders) AS max_order_id,

       (SELECT id FROM public.users WHERE username = 'john_designer') AS target_user_id,

       (SELECT id FROM public.products WHERE name = 'Bluetooth Speaker') AS target_product_id

) AS sub;

SELECT

   u.username,

   p.name AS product,

   o.quantity,

   (o.quantity * p.price) AS totaalprijs

FROM public.orders o

JOIN public.users u ON o.user_id = u.id

JOIN public.products p ON o.product_id = p.id

WHERE u.username = 'john_designer'

ORDER BY o.order_date DESC

LIMIT 1;

/* Maak nu een query waarbij je de producten die niet besteld zijn. Zet deze in een aparte tabel(backup: products_bck)

  Zodoende je daarna de gegevens kunt verwijderen uit de tabel products.

  Oplossing: 10 producten mag je verwijderen. Ralf Conraads 517226

*/

-- Het CREATE statement dat de nieuwe tabel aanmaakt mbv een query.

CREATE TABLE public.products_bck AS

SELECT * FROM public.products

WHERE id NOT IN (

   SELECT DISTINCT product_id

   FROM public.orders

   WHERE product_id IS NOT NULL

);

-- Hier kan het DELETE statement terecht komen. TIP: Gebruik een EXISTS om te zien welke gegevens verwijdert moeten worden.

DELETE FROM public.products

WHERE id IN (

   SELECT id

   FROM public.products_bck

);

-- controle

SELECT COUNT(*) AS "Aantal in Backup" FROM public.products_bck;