HWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Lösungen zu den Smartphones ======

<sxh sql>
SELECT * FROM bsz.modelle;



# 1
SELECT * FROM bsz.modelle
ORDER BY jahr DESC;



# 2
SELECT hersteller, modellname
FROM bsz.modelle
WHERE gps = "Ja";



# 3
SELECT modellnr, hersteller, modellname, jahr
FROM bsz.modelle
WHERE jahr > 2016
ORDER BY jahr DESC;



# 4
SELECT modellnr, hersteller, modellname, ghz, gb
FROM bsz.modelle
WHERE hersteller = "Apple" and ghz > 1.5;



# 5
SELECT *
FROM bsz.modelle
WHERE (hersteller = "Apple" or hersteller = "Motorola")
  and (jahr = 2016 or jahr = 2017);



# 6
SELECT *
FROM bsz.modelle
WHERE mobilfunk LIKE "%LTE" and jahr < 2015;



# 7
SELECT hersteller, COUNT(*) AS "Anzahl der Modelle"
FROM bsz.modelle
GROUP BY hersteller;



# 8
SELECT hersteller, COUNT(*) AS "Anzahl der Modelle im Jahr 2017"
FROM bsz.modelle
WHERE jahr = 2017
GROUP BY hersteller;



# 9
SELECT jahr, COUNT(*) AS "Anzahl der Modelle",
       FORMAT(AVG(standby), 0) AS "Mittelwert Standby"
FROM bsz.modelle
WHERE jahr >= 2000
GROUP BY jahr;



# 10
SELECT hersteller, gps, COUNT(*) "Anzahl der Modelle"
FROM bsz.modelle
GROUP BY hersteller, gps;



# 11
SELECT COUNT(*) AS "Modelle mit WLAN ohne GPS"
FROM bsz.modelle
WHERE wlan = "Ja" and gps = "Nein";



# 12
SELECT COUNT(*) AS "Modelle mit LTE Modem"
FROM bsz.modelle
WHERE mobilfunk LIKE "%LTE%";



# 13
INSERT INTO bsz.modelle
(hersteller, modellname, jahr, gewicht, ghz, gb, wlan, gps, standby, preis)
VALUES
("Motorola", "Moto Z3", 2018, 143, 2.35, 128, "Ja", "Ja", 300, 420);



#14
UPDATE bsz.modelle
SET preis = preis * 0.85
WHERE modellnr = 33;



#15
DELETE FROM bsz.modelle
WHERE modellnr = 35;
</sxh>