Complexe Queries
Opdracht Roger Boessen
Embedded Files
/* --- Gedeelte COMPLEXE QUERIES ---
Database: WEBSHOP
Applicatie: PostGreSQL
Opdrachten: (Complexe Queries) voor leerlingen */
/* 1. Onze Admin-gebruikers hebben allerlei producten besteld. En ze willen graag terugzien wat er de afgelopen tijd besteld is
en met welke prijs besteld is. Al deze info willen ze graag terug zien.
(Let op de andere naamgevingen!) */
SELECT
u.username AS "Gebruikersnaam",
p.name AS "Productnaam",
p.price AS "Stukprijs",
o.quantity AS "Aantal",
(p.price * o.quantity) AS "Totaalprijs",
o.order_date AS "Besteldatum"
FROM public.orders o
JOIN public.users u ON o.user_id = u.id
JOIN public.products p ON o.product_id = p.id
JOIN public.roles r ON u.role_id = r.id
WHERE r.name = 'admin'
ORDER BY o.order_date DESC;
/* 2. De afdeling inkoop wil graag weten wie in totaal meer dan 1 artikel besteld heeft en welke rol deze persoon heeft.
Laat daarbij ook zien WAT deze persoon dan besteld heeft. */
SELECT
u.username AS "Gebruikersnaam",
r.name as "rol",
p.name AS "Productnaam",
o.quantity AS "Aantal"
FROM public.orders o
JOIN public.users u ON o.user_id = u.id
JOIN public.products p ON o.product_id = p.id
JOIN public.roles r ON u.role_id = r.id
WHERE o.quantity >= 2
ORDER BY o.order_date DESC;
/* 3. Vanuit het warehouse willen ze graag weten wat de totale stock van elk product bij de laatste bestelling die gedaan is.
Hiermee weten we wat de huidige opslag is. */
SELECT
p.name AS "Productnaam",
p.stock AS "Totale Voorraad",
o.order_date AS "Datum Laatste Bestelling"
FROM public.products p
JOIN public.orders o ON p.id = o.product_id
WHERE o.id = (SELECT MAX(id) FROM public.orders)
OR o.order_date = (SELECT MAX(order_date) FROM public.orders);
/* 4. Het management wil weten welke producten allemaal zijn aangekocht door het bedrijf waarbij het totaal bedrag meer dan
200 Euro bedraagd. */
SELECT
p.name AS "Productnaam",
o.quantity AS "Aantal",
p.price AS "Stukprijs",
(o.quantity * p.price) AS "Totaalbedrag",
o.order_date AS "Besteldatum"
FROM public.orders o
JOIN public.products p ON o.product_id = p.id
WHERE (o.quantity * p.price) > 200
ORDER BY "Totaalbedrag" DESC;
/* 5. Vanuit het management zien ze een vreemd bedrag voor de 27" Monitoren.
Ze willen daarom nu graag weten wat de totaal prijs die betaald is voor elke gebruiker
per bestelling van de monitors (Tip: gebruik LIKE)
Je mag hier 2 verschillende soorten queries toepassen (subqueries of CTE) */
WITH MonitorOrders AS (
SELECT
u.username,
o.id AS order_id,
o.quantity,
p.price,
p.name
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 p.name LIKE '%Monitor 27"%'
)
SELECT
username AS "Gebruiker",
order_id AS "Bestelnummer",
name AS "Product",
(quantity * price) AS "Betaald Totaalbedrag"
FROM MonitorOrders;
/* 6. De afdeling Warehouse wil graag weten wat de laatste toegevoegde gebruikers per rol besteld hebben (werkelijke gebruikers).
Hierbij willen ze graag zien wat dan hun laatste besteldatum is van deze laatste bestellingen met de juiste naam van de gebruiker als het bestelde product.
Antwoord: Er zouden 5 producten te zien moeten zijn */
SELECT DISTINCT ON (u.username)
r.name AS "Rol",
u.username AS "Naam Gebruiker",
p.name AS "Besteld Product",
o.order_date AS "Laatste Besteldatum"
FROM public.users u
JOIN public.roles r ON u.role_id = r.id
JOIN public.orders o ON u.id = o.user_id
JOIN public.products p ON o.product_id = p.id
WHERE u.created_at IN (
SELECT DISTINCT created_at
FROM public.users
WHERE id IN (SELECT user_id FROM public.orders)
ORDER BY created_at DESC
)
AND u.id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (ORDER BY created_at DESC) as row_num
FROM public.users
WHERE id IN (SELECT user_id FROM public.orders)
) as ranked_users
WHERE row_num <= 5
)
ORDER BY u.username, o.order_date DESC;
Page updated
Google Sites
Report abuse