Gyakorló feladatok a 2. ZH-ra
1. feladat: Adott a következő adatbázis
Jármű (rendszám, márka,
hengerűrtartalom, lóerő, szín, férőhely,
kategória, gyártási_év, tulajdonos)
Tulaj
(személyiszám, név, cím, megye,
szül_dátum, nem, szül_hely),
ahol a márka a gépkocsi típusa(BMW, Mercedes, Ferrari, stb.), a kategória(személygépkocsi, teher, busz, stb.), a tulajdonos külső kulcs a Tulajdonos tábla személyi számára, a megye a tulaj lakhelyének megyéje.
a.) Hozzuk létre a fenti adattáblákat sql-ben!
b.) Minden Mercedes személygépkocsinak növeljük meg egyel a férőhelyeinek számát!
c.) Töröljünk a táblából minden olyan tulajdonost, akinek csak 1 gépjárműve van!
d.) Listázzuk ki a 3000 köbcentinél nagyobb hengerűrtartalmú gépjárművek számát!
e.) Listázzuk ki azokat a Csongrád megyében élő nőket, akiknek van tehergépkocsijuk (kategória), rendezzük a táblát névsorrendbe!
f.) Listázzuk ki azokat a férfiakat, akik Bács-Kiskun mégyében laknak, több mint egy gépjárművük van, és ezzek közül valamelyik piros és a rendszámában szerepel az "A" betű, rendezzük a táblát névsorrendbe.
1. feladat megoldása:
a.)
CREATE TABLE Jarmu (
rendszam CHAR(7)
PRIMARY KEY,
marka VARCHAR(10),
hengerur INTEGER,
loero
INTEGER,
szin CHAR(4),
ferohely DECIMAL(1),
kategoria
VARCHAR(10),
gyartev DECIMAL(4),
tulaj CHAR(12) REFERENCES Tulaj(szemszam)
);
CREATE TABLE Tulaj (
szemszam CHAR(12)
PRIMARY KEY,
nev CHAR(30),
cim CHAR(50),
megye CHAR(20),
szuldate DATE,
szulhely
CHAR(15),
nem CHAR(1)
);
b.)
UPDATE Jarmu
SET ferohely=ferohely+1
WHERE marka LIKE
'Mercedes'
AND kategoria = 'szemely';
c.)
DELETE FROM Tulaj AS T1
WHERE 1 = ( SELECT
COUNT(*)
FROM Tulaj AS T2, Jarmu
WHERE T2.szemszam=tulaj
AND T1.szemszam=T2.szemszam);
d.)
SELECT COUNT(*)
FROM Jarmu
WHERE hengerur > 3000;
e.)
SELECT DISTINCT nev
FROM Jarmu, Tulaj
WHERE tulaj =
szemszam
AND nem = 'n'
AND kategoria = 'teher'
ORDER BY nev;
f.)
SELECT DISTINCT T1.nev
FROM Jarmu AS J1, Tulaj AS T1
WHERE
J1.tulaj = T1.szemszam
AND T1.nem =
'f'
AND T1.megye = 'Bács-Kiskun'
AND 2 <= (SELECT COUNT(*)
FROM Jarmu AS J2, Tulaj AS T2
WHERE J2.tulaj = T2.szemszam
AND T1.szemszam = T2.szemszam)
AND
J1.tulaj IN (SELECT J3.tulaj
FROM
Jarmu AS J3
WHERE
J3.szin = 'piros'
AND
J3.rendszam LIKE '%A%')
ORDER BY T1.nev;
2. feladat: Adott a következő adatbázis
Zöldség (zölségkód, név,
raktáron, ár, vegyszer)
Bolt (kód,
név, cím, zöldségkód,
mennyiség, dátum)
Vegyszer (vkód,
név, hatás, ár),
ahol a raktáron a készleetben levő árú kilógrammban, a vegyszer külső kulcs a Vegyszer tábla vkód-jára, a mennyiség az adott dátumon megvásárolt zöldségmennyiség, a hatás a vegyszer hatása(gombaölő, rovarirtó, stb.).
a.) Hozzuk létre a fenti adattáblákat sql-ben!
b.) Töröljünk minden olyan zöldséget amiből nem vettek még!
c.) Minden zöldségnek módosítsuk az adatait. Legyen a kódja 550-nal nagyobb mint az eredeti, 0 kg van raktáron belőle, ára az eredeti árának 110%-a és nem kezelték vegyszerrel (NULL).
d.) Listázzuk ki azoknak a boltoknak a nevét akik vásároltak már valamilyen borsót(a zöldség nevében szerepel a borsó), rendezzük az eredményt a boltok neve szerint!
e.) Listázzuk azokat a zöldségeket, amelyek nem voltak rovarirtóval kezelve!
f.) Listázzuk ki azoknak a vegyszereknek a nevét, amellyel kezelt zöldséget már vásarolt szegedi bolt 500 kg-nál nagyobb tételben, az eredményt rendezzük névsorrendbe!
g.) Listázzuk ki minden vegyszerre, hogy átlagosan mekkora tételeket adtak el olyan zöldségből, amelyet az adott vegyszerrel kezeltek. A listában csak azok a vegyszerek szerepeljenek, amelyekkel kezelt zöldségekből már legalább 3 bolt vásárolt.
2. feladat megoldása
a.)
CREATE TABLE Zoldseg (
zoldsegkod INTEGER PRIMARY
KEY,
nev CHAR(20),
raktaron INTEGER,
ar
DECIMAL(4),
vegyszer INTEGER REFERENCES
Vegyszer(vkod)
);
CREATE TABLE Bolt (
kod INTEGER,
nev
CHAR(30),
cim CHAR(50),
zoldsegkod INTEGER
REFERENCES Zoldseg(zoldsegkod),
menny INTEGER,
datum
DATE,
PRIMARY KEY (kod, zoldsegkod, datum)
);
CREATE TABLE Vegyszer (
vkod INTEGER PRIMARY
KEY,
nev CHAR(30),
hatas CHAR(10),
ar
INTEGER
);
b.)
DELETE FROM Zoldseg
WHERE Zoldseg.zoldsegkod NOT IN ( SELECT
DISTINCT
Bolt.zoldsegkod
FROM Bolt);
c.)
UPDATE Zoldseg
SET zoldsegkod=zoldsegkod+550, raktaron=0,
ar=ar*1.1, vegyszer=NULL;
d.)
SELECT DISTINCT Bolt.nev
FROM Bolt, Zoldseg
WHERE
Bolt.zoldsegkod = Zoldseg.zoldsegkod
AND Zoldseg.nev LIKE '%borsó%'
ORDER BY Bolt.nev;
e.)
SELECT DISTINCT Zoldseg.nev
FROM Zoldseg, Vegyszer
WHERE
vegyszer = vkod
AND hatas NOT LIKE
'rovarirtó';
f.)
SELECT DISTINCT Vegyszer.nev
FROM Vegyszer, Zoldseg, Bolt
WHERE
vkod = vegyszer
AND Bolt.zoldsegkod =
Zoldseg.zoldsegkod
AND Bolt.cim LIKE
'%Szeged%'
AND menny > 500;
g.)
SELECT Vegyszer.nev, AVG (menny)
FROM Vegyszer, Zoldseg,
Bolt
WHERE vkod = vegyszer
AND
Bolt.zoldsegkod = Zoldseg.zoldsegkod
GRUOP BY vkod
HAVING
COUNT(DISTINCT kod)>2;
Tags: adott a, az adott, feladat, gyakorló, feladatok, adott