Adatbázis feladat: Forint - megoldások

3.

SELECT "cimlet", "tomeg"

FROM "erme", "akod", "anyag"

WHERE "erme"."ermeid" = "akod"."ermeid"

AND "akod"."femid" = "anyag"."femid"

AND "anyag"."femnev" LIKE '%Ezüst%'

f3.png


4.

SELECT DISTINCT "tervezo"."nev"

FROM "erme", "tkod", "tervezo"

WHERE "erme"."ermeid" = "tkod"."ermeid"

AND "tkod"."tervezoid" = "tervezo"."tid"

AND "bevonas" IS NULL

f4.png


5.

SELECT "cimlet", year("kiadas")

FROM "erme"
WHERE “bevonas” IS NOT NULL

ORDER BY DATEDIFF('day',"kiadas", "bevonas") DESC

LIMIT 1

f5.png


6.

SELECT "cimlet", "tomeg" * "darab" / 1000

FROM "erme"

ORDER BY "tomeg" DESC

LIMIT 1

f6.png


7.

SELECT "tervezo"."nev", COUNT( "tkod"."ermeid" )

FROM "tkod", "tervezo"

WHERE "tkod"."tervezoid" = "tervezo"."tid"

GROUP BY "tervezo"."nev"

ORDER BY COUNT( "tkod"."ermeid" ) DESC

f7.png


8.

SELECT "erme"."cimlet", "tervezo"."nev", "erme"."kiadas"

FROM "tkod", "erme", "tervezo"

WHERE "tkod"."ermeid" = "erme"."ermeid"

AND "tervezo"."tid" = "tkod"."tervezoid"

AND ( "erme"."kiadas" <= {D '1995-12-31' } AND "erme"."bevonas" >= {D '2000-01-01' } OR "erme"."kiadas" <= {D '1995-12-31' } AND "erme"."bevonas" IS NULL )

f8.png


9.

SELECT "cimlet", "kiadas"

FROM "erme", "tkod", "tervezo"

WHERE "erme"."ermeid" = "tkod"."ermeid" AND "tkod"."tervezoid" = "tervezo"."tid"

AND "tervezo"."tid" =

( SELECT "tid" FROM "erme", "tkod", "tervezo" WHERE "erme"."ermeid" = "tkod"."ermeid" AND "tkod"."tervezoid" = "tervezo"."tid" AND "erme"."cimlet" = 200 )

AND NOT "cimlet" = 200

f9.png


10.

SELECT “cimlet”, YEAR(“kiadas”)

FROM “erme”

WHERE “ermeid” NOT IN  (

SELECT “akod”.”ermeid”

         FROM “akod”, “anyag”

WHERE “akod”.”femid” = “anyag”.”femid”

AND “femnev” = ‘nikkel’)