· 6 years ago · May 12, 2019, 01:32 PM
1-- 1. feladat
2CREATE DATABASE IF NOT EXISTS `webshop`
3DEFAULT CHARACTER SET utf8
4COLLATE utf8_hungarian_ci
5-- 3. feladat
6ALTER TABLE szamlatetel
7ADD FOREIGN KEY (szamlafejid) REFERENCES szamlafej (id),
8ADD FOREIGN KEY (termekid) REFERENCES termekek (id)
9
10ALTER TABLE szamlafej
11ADD FOREIGN KEY (vevoid) REFERENCES vevok (id)
12
13-- 4. feladat
14SELECT megnevezes, ar
15FROM termekek
16ORDER BY ar
17
18-- 5. feladat
19UPDATE termekek
20SET ar = ar*0.95
21WHERE ar > 5000
22
23-- 6. feladat
24SELECT SUM(mennyiseg*bruttoegysegar) as bevétel
25FROM szamlatetel
26WHERE szamlafejid IN (
27 SELECT id
28 FROM szamlafej
29 WHERE teljesites
30 BETWEEN '2018-01-01' AND '2018-01-15'
31)
32
33-- 7. feladat
34SELECT nev, telepules
35FROM vevok
36WHERE id NOT IN (
37 SELECT vevoid
38 FROM szamlafej
39 WHERE teljesites
40 BETWEEN '2018-01-01' AND '2018-01-31'
41)
42ORDER BY 1
43
44-- 8. feladat
45SELECT
46 t.megnevezes,
47 SUM(mennyiseg*bruttoegysegar) as bevétel
48FROM szamlatetel sz, termekek t
49WHERE szamlafejid IN (
50 SELECT id
51 FROM szamlafej
52 WHERE teljesites
53 BETWEEN '2018-01-01' AND '2018-01-31'
54)
55AND sz.termekid = t.id
56GROUP BY t.megnevezes
57ORDER BY 2 DESC
58LIMIT 3