-- Opdracht 1 | Products /* Toon de vend_id, prod_name en prod_price. De vend_id moet gelijk zijn aan DLL01 of BRS01. Alle prijzen moeten groter of gelijk zijn dan 5. */ -- vend_id prod_name prod_price -- BRS01 8 inch teddy bear 5.99 -- BRS01 12 inch teddy bear 8.99 -- BRS01 18 inch teddy bear 11.99 SELECT vend_id, prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id ='BRS01') AND prod_price >=5; -- Opdracht 2 | Products /* Toon alle products m.u.v. de vend_id DLL01 en FNG01. */ -- prod_id vend_id prod_name prod_price prod_desc -- BR01 BRS01 8 inch teddy bear 5.99 8 inch teddy bear, comes with cap and jacket -- BR02 BRS01 12 inch teddy bear 8.99 12 inch teddy bear, comes with cap and jacket -- BR03 BRS01 18 inch teddy bear 11.99 18 inch teddy bear, comes with cap and jacket SELECT * FROM Products WHERE vend_id NOT IN ('DLL01', 'FNG01'); -- Opdracht 3 | Customers /* Toon alle Fun4All customers of customers zonder e-mail. */ -- cust_id cust_name cust_address cust_city cust_state cust_zip cust_country cust_contact cust_email -- 1000000002 Kids Place 333 South Lake Drive Columbus OH 43333 USA Michelle Green NULL -- 1000000003 Fun4All 1 Sunny Place Muncie IN 42222 USA Jim Jones jjones@fun4all.com -- 1000000004 Fun4All 829 Riverside Drive Phoenix AZ 88888 USA Denise L. Stephens dstephens@fun4all.com -- 1000000005 The Toy Store 4545 53rd Street Chicago IL 54545 USA Kim Howard NULL SELECT * FROM Customers WHERE cust_name = 'Fun4All' OR cust_email IS NULL; -- Opdracht 4 | Products /* Toon alle products waarvan de prod_desc begint met een numerieke waarde. */ -- prod_id vend_id prod_name prod_price prod_desc -- BR01 BRS01 8 inch teddy bear 5.99 8 inch teddy bear, comes with cap and jacket -- BR02 BRS01 12 inch teddy bear 8.99 12 inch teddy bear, comes with cap and jacket -- BR03 BRS01 18 inch teddy bear 11.99 18 inch teddy bear, comes with cap and jacket -- RGAN01 DLL01 Raggedy Ann 4.99 18 inch Raggedy Ann doll -- RYL01 FNG01 King doll 9.49 12 inch king doll with royal garments and crown -- RYL02 FNG01 Queen doll 9.49 12 inch queen doll with royal garments and crown SELECT * FROM Products WHERE ISNUMERIC(LEFT(prod_desc,1)) = 1; -- MySQL SELECT * FROM Products WHERE LEFT(prod_desc,1) REGEXP '^-?[0-9]+$' = 1
Download hier het bestand.