SQL videocenter.schule.de

list1.sql

#1
SELECT kunr, kuname, kuvorna, kustras, kuplz, kuort FROM kunden

#2
SELECT * FROM videos WHERE vititel LIKE 'A%'

#3
SELECT vititel, viart, vidauer FROM videos WHERE
  vidauer < 30 
    AND
  vidauer > 0
  ORDER BY vidauer

#4
SELECT COUNT(*) FROM kunden limit 100

#5
SELECT COUNT(*) FROM kunden WHERE kusex = 'w'

#6
SELECT COUNT(*) FROM kunden WHERE kuort = 'Berlin'

#7
SELECT COUNT(*) FROM kunden WHERE kuort = 'Berlin' AND kustras LIKE 'Wrangelstr%' 

#8
SELECT COUNT(*) FROM kunden WHERE kuort = 'Berlin' AND kustras LIKE 'Wrangelstr%' AND kusex = 'm'

#9
SELECT COUNT(*) FROM kunden WHERE kgebdat BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 YEAR) AND DATE_SUB(CURDATE(), INTERVAL 20 YEAR)

#10
SELECT  vidarsteller1, vidarsteller2, vititel FROM videos GROUP BY vidarsteller1, vidarsteller2

#11
SELECT viart, COUNT(viart) FROM videos GROUP BY viart limit 100

#12
SELECT vifsk, COUNT(vifsk) FROM videos GROUP BY vifsk limit 100

#13
SELECT MAX(viart) FROM videos limit 100

liste2.sql

#1 
SELECT videos.vinr, videos.vititel FROM videos, ausleihe 
    WHERE videos.vinr = ausleihe.vinr 
    GROUP BY videos.vinr

#2
SELECT COUNT(DISTINCT kunden.kunr) FROM kunden, ausleihe 
    WHERE kunden.kunr = ausleihe.kunr limit 100

#3
SELECT ausleihe.kunr, ausleihe.vinr, vititel
FROM ausleihe, videos
WHERE ausleihe.vinr = videos.vinr AND ausleihe.kunr=5244 AND vititel LIKE "%Berlin%"

#4
SELECT videos.vinr, videos.vititel FROM videos, ausleihe 
    WHERE videos.vinr = ausleihe.vinr AND ausleihe.leirueck = '0000-00-00'
    GROUP BY videos.vinr


#5
SELECT videos.vinr, videos.vititel, COUNT(ausleihe.vinr) FROM videos, ausleihe 
    WHERE ausleihe.vinr = videos.vinr
    GROUP BY videos.vinr

#6
SELECT  kunden.kunr, kunden.kuname, COUNT(ausleihe.kunr) FROM ausleihe, kunden

#7
SELECT * FROM kunden WHERE kunden.kunr NOT IN (SELECT kunr FROM ausleihe)

#8
SELECT videos.viart, COUNT(ausleihe.vinr) FROM videos, ausleihe 
  WHERE videos.vinr = ausleihe.vinr 
  GROUP BY videos.viart 
  ORDER BY COUNT(ausleihe.vinr) DESC

#9
SELECT 
  kunden.kusex, COUNT(ausleihe.vinr)
FROM 
  kunden, ausleihe, videos 
WHERE
  kunden.kunr = ausleihe.kunr
AND
  videos.vinr = ausleihe.vinr
AND
  videos.viart = 'Komödien'
GROUP BY
  kunden.kusex

#10
SELECT 
  videos.viart, COUNT(ausleihe.vinr)
FROM 
  kunden, ausleihe, videos 
WHERE
  kunden.kunr = ausleihe.kunr
AND
  videos.vinr = ausleihe.vinr
AND
  kunden.kusex = 'w'
GROUP BY
  videos.viart
ORDER BY
  COUNT(ausleihe.vinr) DESC
  
#11
SELECT 
  kunden.kunr, kunden.kuname, kunden.kustras, kunden.kuplz, kunden.kuort
FROM 
  kunden, ausleihe, videos 
WHERE
  kunden.kunr = ausleihe.kunr
AND
  videos.vinr = ausleihe.vinr
AND
  videos.viart LIKE 'Geschichte'
GROUP BY kunden.kunr
ORDER BY kunden.kunr

#12
SELECT 
  videos.vititel, kunden.kunr, kunden.kuname, ausleihe.leiausda, ausleihe.leirueck
FROM 
  kunden, ausleihe, videos
WHERE
  kunden.kunr = ausleihe.kunr
AND
  videos.vinr = ausleihe.vinr
AND
  DATEDIFF(ausleihe.leiausda, ausleihe.leirueck) < -2
GROUP BY kunden.kunr
  
#13
SELECT 
  kunden.kunr, kunden.kuname, kunden.kuort
FROM 
  kunden, ausleihe
WHERE
  kunden.kunr = ausleihe.kunr
AND
  DATEDIFF(CURDATE(), ausleihe.leiausda) >= 365
GROUP BY kunden.kunr
ORDER BY kunden.kunr

#14
SELECT 
  kunden.kunr, kunden.kuname, kunden.kgebdat, videos.vititel, videos.viart
FROM 
  kunden, ausleihe, videos
WHERE
  kunden.kunr = ausleihe.kunr
AND
  videos.vinr = ausleihe.vinr
AND
  kunden.kusex = 'm'
AND
  videos.viart LIKE 'keine Angabe'
GROUP BY videos.vititel
ORDER BY kunden.kgebdat

liste3.sql

#1

#2

#3

#4

#5

#6

#7

#8