Ein IT-Dienstleister bietet seinen Kunden eine Plattform zur einfachen Verwaltung und Installation ihrer Software Installationen. Um ein besseres Verständnis für seine Kunden zu erhalten und möglicherweise den Umsatz zu erhöhen, beauftragt er Sie, einige Auswertungen über den im laufenden Betrieb gesammelten Daten durchzuführen. (DSGVO Konformität ist sichergestellt.) Die für den Betrieb der Plattform nötigen Informationen werden in der hier beschriebenen Datenbank verwaltet. Alle Benutzer sind durch die Kombination ihres Logins (Login) und Passworts (PW) eindeutig identifiziert. Zusätzlich werden zur korrekten Anreden die Vornamen (VName) und Nachnamen (NName) gespeichert. Benutzer können einer Organisation angehören. Für Organisationen sind der eindeutige Name (Name) sowie Kontaktinformationen (Kontakt) und Anmerkungen (Kommentar) vermerkt. Für jede Sofware werden ihr Name (Name) sowie der Name des Herstellers (Hersteller) gespeichert, wobei beide Werte gemeinsam eindeutig für jede Sofware sind. Jede Software kann in mehreren Versionen vorlegen, wobei die unterschiedlichen Versionen einer Software durch ihre Versionsnummer (VNr) unterscheidbar sind. Zusätzlich benötigt jede Softwareversion eine minimal Menge an Arbeitsspeicher (MinRAM), und ihr Release Datum (RelDatum) ist ebenfalls vermerkt. Die angebotene Sofware ist in Kategorien und Schwierigkeitsgrade eingeteilt, wobei jede Kategorie eine eindeutige Bezeichnung (Bezeichnung) hat. Schwieriegkeitsgrade haben einen Namen (Name), eine Stufe (Level) deren Kombination eindeutig ist, und es wird ein Kommentar vermerkt. Für jede Sofware wird vermerkt, welchen Kategorien sie zuzuordnen ist, und welchen Schwierigkeitsgrad sie in dieser Kategorie besitzt. Benutzer können in den Kategorien auf einem bestimmten Schwierigkeitsgrad geschult werden, und erhalten für diese Schulungen eine Note. Computer sind eindeutig identifiert durch ihre MAC-Adresse (MAC) gemeinsam mit einer aus den Hardwarekomponenten errechneten Nummer (PID). Zusätzlich werden die Anzahl der Cores (Cores), der verfügbare Arbeitsspeicher (RAM), sowie der Computername (Name) gespeichert. Für Laptops wir außerdem ihr Gewicht (Gewicht) und ihre Akkuleistung (Akku) gespeichert, für Desktop-PC ihr Standort (Standort). Computer können (maximal) einer Organisation gehören, was ebenfalls gespeichert wird. Software-Installationen sind durch den Computer auf dem sie installiert sind, die installierte Software-Version, sowie das Installationsverzeichnis (Verzeichnis) eindeutig identifiziert. Benutzer können Anfragen für neue Softwareinstallationen stellen. Verschiedene Anfragen eines Benutzer können durch eine Kombination des Datums (Datum) der Anfrage mit einer fortlaufenden Nummer (NR) eindeutig bestimmt werden. Zusätzlich wird jeder Anfrage vom Benutzer eine Priorität (Prioritaet) zugeordnet. Jede Anfrage kann zwischen 1 und 5 Installationswünsche enthalten, wobei für jeden Wunsch vermerkt wird welche Software-Version auf welchem Computer installiert werden soll. Anfragen können von Benutzern mit Freigabeberechtigung genehmigt werden, wobei das Datum der Genehmigung (Datum) sowie ein Kommentar (Kommentar) dazu gespeichert werden. Für freigabeberechtigte Benutzer wird ein Budgetrahmen vermekt, bis zu dem sie genehmigen dürfen (Grenze). Falls keine Budgetgrenze besteht wird dies mittels eines NULL Wertes vermerkt. Für jeden Benutzer wird aufgezeichnet, welche Installation wie lange (Dauer) verwendet wurde. Software-Lizenzen haben einen eindeutigen Key (Key), sowie einen Preis (Preis) und ein Ablaufdatum (laueft_ab). Falls eine Lizenz nicht verfällt enthält das Ablaufdatum einen NULL-Wert. Jede Lizenz gilt für eine oder mehrere Software-Versionen, und es wird vermerkt welche Software-Installation mit welcher Lizenz läuft. Schlussendlich wird vermerkt, falls eine Lizenz der Ersatz einer anderen Lizenz nach derem Ablauf ist. English: An IT service provider offers a software distribution platform for the easy management and installation of their software. To get a better understanding of its customers and possibly increase profit, you are tasked with running some analysis on the data collected by the system. (GDPR compliance is warranted :) ). To do so, you receive the following description of the database backing up the platform. All Users (Benutzer) are uniquely identified by the combination of their login (Login) and password (PW). In addition, their first (VName) and last name (Nname) are stored. Users may be part of an organization (Organisation). For organizations, their unique name (Name), as well as some contact information (Kontakt) and a comment (Kommentar) are saved. For each software, its name (Name) and the name of the Producer (Hersteller) are stored. Together, these two values identify each software. Different versions of a software can be distinguished by their version number (VNr). In addition, for each version the minimal required amount of RAM (MinRAM) and the initial release date (RelDatum) are noted. The software offered by the service provider is grouped into categories (Kategorie) and Skill-Levels, each category having a unique denomination (Bezeichnung), and each Skill-Level being identified by a combination of its name (Name) and level (LEVEL). In addition, a note (Kommentar) is stored for each SkillLevel. For each software, a SkillLevel is provided for each category it is assigned to. Users can get a training (geschult) for the different categories at the different Skill Levels. The grade (Note) they achieve on these trainings is stored. Computers are uniquely identified by their MAC-address (MAC-address) in combination with a number (PID) computer based on its hardware components. In addition, the number of cores (Cores), the amount of RAM installed (RAM), and the name of the computer (Name) are recorded. For laptops, in addition their weight (Gewicht) and the capacity of their battery (Akku) are stored. For desktop PC, their location (Standort) is stored. Computers may be owned (gehören) by (at most) a organization, which is recorded as well. Specific installations of a software version are uniquely identified by the computer they are on, the software version, and the installation directory (Verzeichnis). Users may make a request (Anfrage) for new installations. Different request of the same users can be distinguished by a combination of the date (Datum) of the request and some number (NR). In addition, a user assigns a priority (Prioritaet) to each request. Each request may contain between 1 and 5 concrete requests to install a certain software version on a certain computer. Requests can be granted (genehmigt) by special users (Freigabeberechtigt). In such a case, the date (Datum) of the approval and some comment (Kommentar) are recorded. For these special users, a limit (Grenze) is stored, up to which they may approve requests. If no such limit applies, a NULL value is recorded. For all user it is recorded how long (Dauer) he or she used each software installation. Licenses (Lizenzen) for software posses a unqiue key (Key), as well as a price (Preis) and an expiration date (laeuft_ab). If a license never expires, this value is NULL. Each licence covers one or more software versions, and it is recorded which software installation uses which license. Finally, if a license is intended to be the replace of another license after its expiration, this is also stored.
1)
Geben Sie eine Liste aller Installationen (Herstellers der Software, Name der Software, Installationsverzeichnis, MAC-Adresse des Computers) aus, welche im Verzeichnis '/bin' oder einem Unterverzeichnis davon installiert sind. Sortieren Sie die Ausgabe aufsteigend nach dem Namen der Programme. Einträge mit dem selben Programm-Namen sollen absteigend nach dem Hersteller sortiert werden. Auf diesen Werten idente Einträge sollen weiter absteigend nach der Versionsnummer, dann aufsteigend nach dem Installationsverzeichnis und zuletzt aufsteigend nach der PID des Computers sortiert werden. <br/><br/> English:<br/> List all installations ('Installationen') that are installed in the "/bin"-directory ('Verzeichnis'), or any of its subdirectories. State the producer ('Hersteller') of the software, the name ('Name') of the software, the installation directory ('Verzeichnis'), and the MAC-address ('MAC-Adresse'). Sort the list ascending by the name ('Name') of the program. Entries with the same name shall be ordered first descending by the producer ('Hersteller'), and entries with the same program name and producer shall be sorted descending by their version number, then ascending by the installation directory, and finally ascending by the PID of the computer.
SELECT sw_name as Software, sw_hersteller as Hersteller, verzeichnis, c_mac as MAC FROM Installation WHERE verzeichnis like '/bin/%' OR verzeichnis='/bin' ORDER BY software ASC, hersteller DESC, v_vnr DESC, verzeichnis ASC, c_pid ASC;
2)
Geben Sie alle BenutzerInnen (Login, VName, NName) aus welche bereits in mindestens 4 verschiedenen Kategorien auf einem Skill-Level von 3 oder niedriger geschult wurden, aber für eine Schulung auf so einem Skill-Level noch nie eine bessere Note als eine 4 erhalten haben. Sortieren Sie das Ergebnis aufsteigend nach der durchschnittlichen Note der Personen. Achten Sie darauf, keine Person doppelt auszugeben. <br/><br/> English:<br/> List all users ('Benutzer') - state their login ('Login'), first name ('VName'), and last name ('NName') - that were trained ('geschult') in at least four different categories ('Kategorien') at a skill level of 3 or lower, but never received a better grade ('Note') than 4 on any training at these skill levels. Sort the list ascending by the average grade ('Note') of each person. Make sure to output each person at most once.
SELECT login, vname, nname FROM Benutzer NATURAL JOIN geschult WHERE lvl <= 3 GROUP BY login, pw HAVING count(DISTINCT bezeichnung) >= 4 AND MIN(Note) >= 4 ORDER BY AVG(Note) ASC;
SELECT login, vname, nname FROM Benutzer NATURAL JOIN geschult WHERE lvl <= 3 AND 4 <= ALL (SELECT note FROM geschult g WHERE Benutzer.login=g.login AND Benutzer.pw=g.pw AND g.lvl <=3) GROUP BY login, pw HAVING count(DISTINCT bezeichnung) >= 4 ORDER BY AVG(Note) ASC;
3)
Geben Sie für alle BenutzerInnen (VName, NName) die schon zumindest einmal ein Programm verwendet haben das meistverwendete Programm (über alle Installationen und Versionen hinweg) aus, sowie den Namen des Programms sowie wie lange sie das Programm verwendet haben. Sortieren Sie das Ergebnis aufsteigend nach VName, NName, Name des Programms. <br/><br/> English:<br/> List for all users ('Benützer') - state their first ('VName') and last ('NName') name - who have already used ('verwendet'/'Verwendung') some software their most heavily used program (across all versions and installations). For each such user, state the name ('Name') of the software and how long ('Dauer') that program was used. Sort the result ascending by first name ('VName'), last name ('NName'), and name of the software.
SELECT vname, nname, sw_name, SUM(Dauer) as dauer FROM Verwendung NATURAL JOIN Benutzer GROUP BY login, pw, vname, nname, sw_name, sw_hersteller HAVING (login, pw, SUM(dauer)) IN (SELECT login, pw, MAX(nutzung) FROM (SELECT login, pw, sw_name, sw_hersteller, SUM(dauer) as nutzung FROM Verwendung GROUP BY login, pw, sw_name, sw_hersteller) as nutzungsdauer GROUP BY login, pw) ORDER BY VName, NName, sw_name;
SELECT vname, nname, sw_name, SUM(Dauer) as dauer FROM Verwendung NATURAL JOIN Benutzer b GROUP BY b.login, b.pw, vname, nname, sw_name, sw_hersteller HAVING SUM(dauer) >= (SELECT MAX(nutzung) FROM (SELECT sw_name, sw_hersteller, SUM(dauer) as nutzung FROM Verwendung WHERE login=b.login AND pw=b.pw GROUP BY sw_name, sw_hersteller) as nutzungsdauer ) ORDER BY VName, NName, sw_name;
SELECT vname, nname, sw_name, SUM(Dauer) as dauer FROM Verwendung NATURAL JOIN Benutzer b GROUP BY b.login, b.pw, vname, nname, sw_name, sw_hersteller HAVING SUM(dauer) >=ALL (SELECT SUM(dauer) as nutzung FROM Verwendung WHERE login=b.login AND pw=b.pw GROUP BY sw_name, sw_hersteller) ORDER BY VName, NName, sw_name;
SELECT vname, nname, sw_name, maxi FROM (SELECT login, pw, MAX(nutzung) as maxi FROM (SELECT login, pw, sw_name, sw_hersteller, SUM(dauer) as nutzung FROM Verwendung GROUP BY login, pw, sw_name, sw_hersteller) as nutzungsdauer GROUP BY login, pw) as maxnutzung NATURAL JOIN ( SELECT login, pw, sw_name, sw_hersteller, SUM(dauer) as maxi FROM Verwendung GROUP BY login, pw, sw_name, sw_hersteller) as nutzungsdauer2 NATURAL JOIN Benutzer ORDER BY VName, NName, sw_name
SELECT VName, NName, sw_name, dauer FROM (SELECT b.VName, b.NName, v.sw_hersteller, v.sw_name, sum(dauer) AS dauer, RANK() OVER (PARTITION BY b.login, b.pw ORDER BY sum(dauer) DESC NULLS LAST) AS rang FROM benutzer b JOIN verwendung v ON (b.login = v.login AND b.pw = v.pw) GROUP BY b.login, b.pw, v.sw_hersteller, v.sw_name ) tmp WHERE rang = 1 ORDER BY VName, NName, sw_name
4)
Geben Sie alle geizigen Organisationen (Name, Kontakt, Kommentar) aus, das sind Organisationen in welchen weniger als die Hälfte der Anfragen bislang genehmigt wurden. Geben Sie zu jeder dieser Organisationen außerdem den Prozentsatz (ohne Nachkommastelle) der genehmigten Anfragen aus. <br/><br/> English:<br/> List for all stingy organizations ('Organisation'), i.e. organizations where less than half of the requests ('Anfrage') have been granted ('genehmigt') so far. For each such organization, state their name ('Name'), contact information ('Kontakt'), the note on them ('Kommentar'), as well as the percentage of granted requests of this organization as an integer.
SELECT o.name, o.kontakt, o.kommentar, 100*COUNT(anfrage_nr)/COUNT(nr) as ProzentOK FROM Anfrage a LEFT OUTER JOIN genehmigt g ON (a.login=g.anfrage_login AND a.pw=g.anfrage_pw AND a.nr=g.anfrage_nr AND a.datum=g.anfrage_datum) JOIN magehoertzu mag ON (a.login=mag.login AND a.pw=mag.pw) JOIN Organisation o ON (o.name=mag.name) GROUP BY o.name, o.kontakt, o.kommentar HAVING COUNT(anfrage_nr) * 2 < COUNT(nr);
SELECT o.name, o.kontakt, o.kommentar, 100*COUNT(anfrage_nr)/COUNT(nr) as ProzentOK FROM Anfrage a LEFT OUTER JOIN genehmigt g ON (a.login=g.anfrage_login AND a.pw=g.anfrage_pw AND a.nr=g.anfrage_nr AND a.datum=g.anfrage_datum) JOIN magehoertzu mag ON (a.login=mag.login AND a.pw=mag.pw) JOIN Organisation o ON (o.name=mag.name) GROUP BY o.name, o.kontakt, o.kommentar HAVING COUNT(anfrage_nr) < COUNT(nr)/2.0;
SELECT o.name, o.kontakt, o.kommentar, 100*COUNT(anfrage_nr)/COUNT(nr) as ProzentOK FROM Anfrage a LEFT OUTER JOIN genehmigt g ON (a.login=g.anfrage_login AND a.pw=g.anfrage_pw AND a.nr=g.anfrage_nr AND a.datum=g.anfrage_datum) JOIN magehoertzu mag ON (a.login=mag.login AND a.pw=mag.pw) JOIN Organisation o ON (o.name=mag.name) GROUP BY o.name, o.kontakt, o.kommentar HAVING COUNT(anfrage_nr) < COUNT(nr)*0.5;
SELECT o.name, o.kontakt, o.kommentar, 100*COUNT(anfrage_nr)/COUNT(nr) as ProzentOK FROM Anfrage a LEFT OUTER JOIN genehmigt g ON (a.login=g.anfrage_login AND a.pw=g.anfrage_pw AND a.nr=g.anfrage_nr AND a.datum=g.anfrage_datum) JOIN magehoertzu mag ON (a.login=mag.login AND a.pw=mag.pw) JOIN Organisation o ON (o.name=mag.name) GROUP BY o.name, o.kontakt, o.kommentar HAVING COUNT(anfrage_nr) < COUNT(nr)*(1/2.0);
SELECT o.name, o.kontakt, o.kommentar, 100*COUNT(anfrage_nr)/COUNT(nr) as ProzentOK FROM Anfrage a LEFT OUTER JOIN genehmigt g ON (a.login=g.anfrage_login AND a.pw=g.anfrage_pw AND a.nr=g.anfrage_nr AND a.datum=g.anfrage_datum) JOIN magehoertzu mag ON (a.login=mag.login AND a.pw=mag.pw) JOIN Organisation o ON (o.name=mag.name) GROUP BY o.name, o.kontakt, o.kommentar HAVING COUNT(anfrage_nr) < COUNT(nr)*(1.0/2);
SELECT o.name, o.kontakt, o.kommentar, 100*COUNT(anfrage_nr)/COUNT(nr) as ProzentOK FROM Anfrage a LEFT OUTER JOIN genehmigt g ON (a.login=g.anfrage_login AND a.pw=g.anfrage_pw AND a.nr=g.anfrage_nr AND a.datum=g.anfrage_datum) JOIN magehoertzu mag ON (a.login=mag.login AND a.pw=mag.pw) JOIN Organisation o ON (o.name=mag.name) GROUP BY o.name, o.kontakt, o.kommentar HAVING COUNT(anfrage_nr) < COUNT(nr)*(1.0/2.0);
SELECT o.name, o.kontakt, o.kommentar, floor(100.0*COUNT(anfrage_nr)/COUNT(nr)) as ProzentOK FROM Anfrage a LEFT OUTER JOIN genehmigt g ON (a.login=g.anfrage_login AND a.pw=g.anfrage_pw AND a.nr=g.anfrage_nr AND a.datum=g.anfrage_datum) JOIN magehoertzu mag ON (a.login=mag.login AND a.pw=mag.pw) JOIN Organisation o ON (o.name=mag.name) GROUP BY o.name, o.kontakt, o.kommentar HAVING COUNT(anfrage_nr) * 2 < COUNT(nr);
5)
Geben Sie eine Liste mit ALLEN Computern mit mindestens 2 und maximal 8 Cores aus (MAC-Adresse, Name, RAM, Cores), gemeinsam mit den im Verzeichnis (direkt, kein Unterverzeichnis) '/usr/bin' installierten Programmen (Name), sowie den Keys der dazugehörigen Lizenzen mit einem Preis von maximal 10. Sollte es für einen Computer keine Installation in '/usr/bin' geben, so soll statt des Names der Software 'kein *x System' ausgegeben werden. Gibt es zu einer Installation keine Lizenz oder die dazugehörige Lizenz kostet mehr als 10 soll statt des keys 'nicht billig' ausgegeben werden. <br/><br/> English:<br/> List ALL computers ('Computer') with at least 2 and at most 8 cores and all programs installed in '/usr/bin' (no subdirectories) with licenses that cost less than 10 on each such machine. Provide the MAC-address ('MAC-Adresse'; MAC), name ('Name'), RAM ('RAM'), and number of cores ('Cores') of each computer, the name ('Name') of the program (software), and the key (Key) of the license, if the license fee ('Preis') is at most 10. If for a computer there is no program installed in '/usr/bin', then instead of the name of the program, the string 'kein *x System' (no *x system) shall be returned. If, for a program installed in '/usr/bin', there either exists no license or the license fee is more than 10, then 'nicht billig' (not cheap) shall be output.
SELECT mac, name, ram, cores, coalesce(i.sw_name, 'kein *x System') as Installation, coalesce(l.key, 'nicht billig') as Preis FROM Computer c LEFT OUTER JOIN Installation i ON (c.mac = i.c_mac AND c.pid = i.c_pid AND i.verzeichnis = '/usr/bin') LEFT OUTER JOIN (InstallationToLizenz NATURAL JOIN Lizenz) l ON (i.sw_name = l.sw_name AND i.sw_hersteller = l.sw_hersteller AND i.v_vnr = l.v_vnr AND i.verzeichnis = l.verzeichnis AND i.c_mac = l.c_mac AND i.c_pid = l.c_pid AND l.preis <= 10 ) WHERE cores >= 2 AND cores <=8;
6)
Geben Sie alle MitarbeiterInnen einer Organisation (VName, NName) aus, welche bereits auf allen DesktopPCs ihrer Organisation gearbeitet haben. Achten Sie darauf, pro Person maximal einen Eintrag auszugeben. Sortieren Sie die Ausgabe aufsteigend nach VName, NName. <br/><br/> English:<br/> List all employees ('Mitarbeiter') of all organizations that have already used ('verwendet'; 'Verwendung') all DesktopPCs of their organization ('Organisation'). Make sure to produce at most one row per person. State the first- (VName) and last (NName) names of each person, and sort the result ascending by first- and last name.
SELECT vname, nname FROM Benutzer b NATURAL JOIN MAgehoertZu o WHERE NOT EXISTS (SELECT * FROM DesktopPC d NATURAL JOIN computergehoert g WHERE g.name=o.name AND NOT EXISTS ( SELECT * FROM Verwendung v WHERE b.pw = v.pw AND b.login = v.login AND v.c_mac = d.mac AND v.c_pid = d.pid)) ORDER BY vname ASC, nname ASC;
SELECT vname, nname FROM Benutzer b NATURAL JOIN MAgehoertZu o WHERE NOT EXISTS (SELECT * FROM DesktopPC d NATURAL JOIN computergehoert g WHERE g.name=o.name AND (b.login, b.pw, d.mac, d.pid) NOT IN ( SELECT v.login, v.pw, v.c_mac, v.c_pid FROM Verwendung v )) ORDER BY vname ASC, nname ASC;
SELECT vname, nname FROM ( SELECT b.login, b.pw, vname, nname, mag.name, ci.c_mac FROM Benutzer b NATURAL JOIN MAgehoertZu mag LEFT OUTER JOIN (SELECT * FROM verwendung v JOIN computergehoert cg ON (v.c_mac=cg.mac AND v.c_pid=cg.pid) JOIN DesktopPC dpc ON (v.c_mac=dpc.mac AND v.c_pid=dpc.pid)) ci ON (b.login=ci.login AND b.pw=ci.pw AND mag.name=ci.name) GROUP BY b.login, b.pw, b.vname, b.nname, ci.c_mac, ci.c_pid, mag.name) tmp GROUP BY login, pw, vname, nname, name HAVING count(tmp.c_mac) = (SELECT count(*) FROM computergehoert cg1 NATURAL JOIN DesktopPC WHERE cg1.name = tmp.name) ORDER BY vname ASC, nname ASC;
SELECT vname, nname FROM ( SELECT b.login, b.pw, vname, nname, mag.name, v.c_mac FROM Benutzer b NATURAL JOIN MAgehoertZu mag LEFT OUTER JOIN (verwendung v JOIN computergehoert cg ON (v.c_mac=cg.mac AND v.c_pid=cg.pid) JOIN DesktopPC dpc ON (v.c_mac=dpc.mac AND v.c_pid=dpc.pid)) ON (b.login=v.login AND b.pw=v.pw AND mag.name=cg.name) GROUP BY b.login, b.pw, b.vname, b.nname, v.c_mac, v.c_pid, mag.name) tmp GROUP BY login, pw, vname, nname, name HAVING count(tmp.c_mac) = (SELECT count(*) FROM computergehoert cg1 NATURAL JOIN DesktopPC WHERE cg1.name = tmp.name) ORDER BY vname ASC, nname ASC;
7)
Geben Sie für ALLE BenutzerInnen (Login, PW) den am meist verwendeten Computer (Name, MAC-Adresse) aus. "Meist verwendet" bezieht sich dabei auf die Gesamtdauer der Verwendung. Sollte einE BenutzerIn noch nie irgendeinen Computer verwendet haben, so soll statt dem Namen 'inaktiv' und statt der MAC-Adresse 404 ausgegeben werden. Sortieren Sie das Ergebnis absteigend nach der am Computer verbrachten Zeit, und anschließend aufsteigend zuerst nach dem Namen des Computers und dann dem Passwort. <br/><br/> English:<br/> For EVERY user ('Benutzer'), provide the computer the user spent the most time ('Dauer') at (in total). State the login ('Login'), password (PW) of the user as well as the name ('Name') and MAC-address (MAC) of the Computer. If a user has never used any computer, output 'inaktiv' (inactive) instead of the name of the computer and 404 instead of the MAC-address. Sort the result descending by the time spent on this computer, sort entries with equal values first ascending by the name of the computer, and then by the password of the user.
SELECT b.login, b.pw, COALESCE(name, 'inaktiv') as name, COALESCE(mac,404) as mac FROM Verwendung v JOIN Computer ON (mac=c_mac AND pid=c_pid) RIGHT OUTER JOIN Benutzer b ON (b.login=v.login AND b.pw=v.pw) GROUP BY b.login, b.pw, mac, pid, name HAVING ((b.login, b.pw, sum(dauer)) in (SELECT login, pw, MAX(dauer) FROM (SELECT login, pw, c_mac, c_pid, SUM(dauer) as dauer FROM Verwendung GROUP BY login, pw, c_mac, c_pid) tmp GROUP BY login, pw)) OR sum(dauer) IS NULL ORDER BY COALESCE(sum(dauer),0) DESC, name ASC, pw ASC;
SELECT login, pw, name, mac, sum(dauer) FROM Verwendung JOIN Computer ON (mac=c_mac AND pid=c_pid) GROUP BY login, pw, mac, pid, name HAVING (login, pw, sum(dauer)) in (SELECT login, pw, MAX(dauer) FROM (SELECT login, pw, c_mac, c_pid, SUM(dauer) as dauer FROM Verwendung GROUP BY login, pw, c_mac, c_pid) tmp GROUP BY login, pw) UNION (SELECT login, pw, 'inaktiv', 404, 0 FROM Benutzer WHERE (login, pw) NOT IN (SELECT login, pw FROM Verwendung) ) ORDER BY sum DESC, name ASC, pw ASC;
SELECT b.login, b.pw, coalesce(d.name,'inaktiv'), coalesce(d.mac,404) FROM Benutzer b LEFT OUTER JOIN (Verwendung v JOIN Computer d ON (v.c_mac = d.mac AND v.c_pid = d.pid) ) ON (b.login = v.login AND b.pw = v.pw) GROUP BY b.login, b.pw, d.name, d.mac, d.pid HAVING coalesce(SUM(dauer),0) >= ALL (SELECT coalesce(SUM(dauer),0) FROM Benutzer b2 LEFT OUTER JOIN Verwendung V ON (b2.login = v.login AND b2.pw = v.pw) WHERE b.login = b2.login AND b2.pw = b.pw GROUP BY b2.login,b2.pw,v.c_mac,v.c_pid) ORDER BY coalesce(SUM(dauer),0) DESC, coalesce(d.name,'inaktiv')ASC, b.pw ASC
SELECT login, pw, status, mac FROM ( SELECT b.login, b.pw, c.name, coalesce(c.name,'inaktiv') as status, coalesce(c.mac,404) as mac, COALESCE(sum(dauer),0) as dauer, rank() OVER(PARTITION BY b.login, b.pw ORDER BY COALESCE(sum(dauer),0) DESC) as pos FROM Benutzer b LEFT OUTER JOIN (Verwendung v JOIN Computer c ON (v.c_mac = c.mac AND v.c_pid = c.pid) ) ON (b.login = v.login AND b.pw = v.pw) GROUP BY b.login, b.pw, c.mac, c.pid, c.name) tmp WHERE pos=1 ORDER BY dauer DESC, name ASC, pw ASC
8)
Geben Sie alle Benutzer (login, VName, NName) aus, welche bereits aus jeder Kategorie in der sie geschult wurden und mit der Note 3 oder besser beurteilt wurden mindestens ein Programm des Skilllevels 3 oder höher verwendet haben. <br/><br/> English:<br/> Return a table of users ('Benutzer'); state their login ('Login'), first- ('VName') and last name ('NName'). This table shall contain exactly those users who have, for each category they have received training for ('geschult') and passed with a grade ('Note') of 3 or better (i.e., smaller), used at least one program (software) of that category with a skill level of 3 or higher.
SELECT login, vname, nname FROM Benutzer b WHERE NOT EXISTS (SELECT * FROM geschult g WHERE b.login = g.login AND b.pw = g.pw AND Note <= 3 AND NOT EXISTS (SELECT * FROM Verwendung NATURAL JOIN SWvonTyp WHERE lvl >= 3 AND b.login = login AND b.pw = pw AND bezeichnung = g.bezeichnung));
SELECT b.login, VName, NName FROM Benutzer b LEFT OUTER JOIN geschult g ON (b.login=g.login AND b.pw=g.pw AND g.note <= 3) GROUP BY b.login, b.pw HAVING count(distinct g.bezeichnung) = (SELECT count(distinct bezeichnung) FROM verwendung v JOIN swvontyp swt ON (v.sw_name=swt.sw_name AND v.sw_hersteller=swt.sw_hersteller) WHERE swt.lvl >=3 AND v.login=b.login AND v.pw=b.pw AND swt.bezeichnung IN (SELECT bezeichnung FROM geschult g2 WHERE g2.login=b.login AND g2.pw=b.pw AND g2.note <= 3) )
9)
Geben Sie für jede Organisation (Name, Kommentar) mit mindestens einer Mitarbeiterin/einem Mitarbeiter jene Software (Name) aus, welche (in irgendwelchen Versionen) auf allen Laptops der Organisation installiert sind. Gibt es kein solches Programm, so soll statt dem Namen des Programms 'kein Pflichtprogramm gefunden' ausgegeben werden. <br/><br/> English:<br/> For each organization ('Organisation') with at least one employee ('Mitarbeiter'), list all software that is (in any version) installed on all laptops of this organization. Give the name ('Name') and note ('Kommentar') of the organization as well as the name ('Name') of the software. If no such software exists, instead of its name the string 'kein Pflichtprogramm gefunden' (no compulsory program found) shall be stated.
SELECT o1.name, o1.kommentar, COALESCE(o2.sw_name, 'kein Pflichtprogramm gefunden') FROM Organisation o1 NATURAL JOIN magehoertzu LEFT OUTER JOIN ( SELECT o.name, o.kommentar, s.name as sw_name, s.hersteller as sw_hersteller FROM Organisation o, Software s WHERE NOT EXISTS(SELECT * FROM Laptop l NATURAL JOIN computergehoert cg WHERE cg.name = o.name AND NOT EXISTS (SELECT * FROM Installation i WHERE i.sw_name = s.name AND i.sw_hersteller = s.hersteller AND i.c_pid = l.pid AND i.c_mac = l.mac)) ) o2 ON (o1.name=o2.name) GROUP BY o1.name, o1.kommentar, o2.sw_name, o2.sw_hersteller;
SELECT name, kommentar, COALESCE(softname,'kein Pflichtprogramm gefunden') FROM Organisation NATURAL JOIN magehoertzu LEFT OUTER JOIN ( SELECT orgname, softname, hersteller FROM (SELECT o.name as orgname, o.kommentar, s.name as softname, s.hersteller, il.mac FROM Organisation o NATURAL JOIN magehoertzu CROSS JOIN Software s LEFT OUTER JOIN (installation i JOIN (Laptop NATURAL JOIN computergehoert) l ON (c_mac=mac AND c_pid=pid)) il ON (s.name=il.sw_name AND s.hersteller=il.sw_hersteller AND o.name=il.name) GROUP BY o.name, s.name, s.hersteller, il.mac, il.pid) sws GROUP BY orgname, softname, hersteller HAVING count(mac) = (SELECT count(lg.mac) FROM Organisation o LEFT OUTER JOIN (computergehoert NATURAL JOIN Laptop) lg ON (lg.name=o.name) WHERE o.name=orgname GROUP by o.name)) sub ON (name=orgname) GROUP BY name, kommentar, softname, hersteller
10)
Geben Sie für ALLE Computer (Name, RAM, Cores) folgende Informationen an: <ul> <li>die Anzahl der Installationen auf dem Computer</li> <li>die Anzahl der BenutzerInnen auf dem Computer</li> <li>die durchschnittliche Nutzungsdauer pro BenutzerIn</li> <li>in einer Spalte 'Laptop' oder 'Desktop', falls es sich um einen Laptop oder DesktopPC handelt (Sie können davon ausgehen, dass ein Computer entweder Laptop oder DesktopPC ist, aber nie beides), bzw. 'Anderes' falls der Computer weder ein Laptop noch ein Desktop Gerät ist.</li> </ul> <br/><br/> English:<br/> For ALL computer, state the name ('Name'), main memory (RAM), cores, and the following information: <ul> <li>the number of installations on this computer</li> <li>the number of users ('Benutzer') on this computer</li> <li>the average time ('Dauer') a user ('Benutzer') has used this computer</li> <li>in an additional column, state 'Laptop' or 'Desktop', if the computer is a laptop or DesktopPC (you may assume that no computer is both), and 'Anderes' (others) if it is neither. </li>
SELECT c.name, c.ram, c.cores, (SELECT count(*) FROM Installation i WHERE i.c_mac=c.mac AND i.c_pid=c.pid) as Installationen, (SELECT count(*) FROM (SELECT 'a' FROM Verwendung v WHERE v.c_mac=c.mac AND v.c_pid=c.pid GROUP BY login, pw) as u) as BenutzerInnen, (SELECT AVG(nutzung) FROM (SELECT SUM(dauer) as nutzung FROM Verwendung v WHERE v.c_mac=c.mac AND v.c_pid=c.pid GROUP BY login, pw) as n) as AVGNutzung, CASE WHEN l.mac IS NOT NULL THEN 'Laptop' WHEN d.mac IS NOT NULL THEN 'Desktop' ELSE 'Anderes' END as type FROM Computer c LEFT OUTER JOIN Laptop l ON (c.pid=l.pid AND c.mac=l.mac) LEFT OUTER JOIN DesktopPC d ON (c.mac=d.mac AND c.pid=d.pid)
SELECT c.name, c.ram, c.cores, instcount, usercount, avgnutzung, CASE WHEN l.mac IS NOT NULL THEN 'Laptop' WHEN d.mac IS NOT NULL THEN 'Desktop' ELSE 'Anderes' END as type FROM Computer c LEFT OUTER JOIN Laptop l ON (c.pid=l.pid AND c.mac=l.mac) LEFT OUTER JOIN DesktopPC d ON (c.mac=d.mac AND c.pid=d.pid) JOIN (SELECT c_mac, c_pid, count(*) as usercount, AVG(nutzung) as AVGNutzung FROM (SELECT c_mac, c_pid, login, pw, sum(dauer) as nutzung FROM Verwendung GROUP BY c_mac, c_pid, login, pw) as vu GROUP BY c_mac, c_pid) as avgn ON (c.mac=avgn.c_mac AND c.pid=avgn.c_pid) NATURAL JOIN (SELECT c_mac, c_pid, count(*) as instcount FROM Installation GROUP BY c_mac, c_pid) as ic;
11.1)
Geben Sie alle BenutzerInnen (Login, PW, NName) aus, welche bereits ein Programm verwendet haben, für welches sie nicht geschult waren (keine Schulung in einer dem Programm zugeordneten Kategorie auf dem gleichen oder einem höheren Skill-Level). Achten Sie darauf, keine Person doppelt auszugeben. <br/><br/> English:<br/> List all users ('Benutzer') - state their login ('Login'), password (PW), and last name (NName) - that have used ('verwendet') a software they have not been trained ('geshult') for (i.e., they had no training in any category ('Kategorie') the software is classified at on the same or a higher skill level). Make sure to output each person at most once.
SELECT login, pw, nname FROM Benutzer b NATURAL JOIN Verwendung v NATURAL JOIN SWvonTyp t WHERE NOT EXISTS (SELECT * FROM geschult g WHERE g.login = b.login AND g.pw = b.pw AND g.bezeichnung = t.bezeichnung AND g.lvl >= t.lvl) GROUP BY login, pw, nname;
11.2)
Geben Sie alle Installationen aus für welche es nach dem 01.10.2019 nach aktuellen Stand keine Lizenz gibt (d.h. entweder gibt es gar keine Lizenz für diese Installation, oder die Lizenz läuft vor oder an diesem Datum aus und es ist derzeit noch keine Nachfolgelizenz vorgesehen; der Fall dass die Nachfolgelizenz ebenfalls vor dem 01.10.2019 abläuft darf dabei ignoriert werden, bei Existenz einer Nachfolgelizenz braucht die Installation nicht ausgegeben werden). Geben Sie den Namen der Software, den Hersteller der Software, den Namen des Computers sowie das Datum an, zu dem die Lizenz abläuft. Falls es für eine Installation keine Lizenz gibt soll 'keine Lizenz vorhanden' statt des Datums angegeben werden. Verwenden Sie die Funktionen TO_DATE and TO_CHAR mit dem Datumsformat 'DD.MM.YYYY'. <br/><br/> English:<br/> List all installations for which no valid license ('Lizenz') exists after October 1st, 2019. I.e., either there exists no license ('Lizenz') for that installation ('Installation') at all, or the current license expires before or on the given date, and there is no license marked to replace ('ersetzt') this license (it is not necessary to check whether the replacing license is valid beyond the given date, if a replacement exists, the installation shall not be part of the output). State the name of the software, the name of the producer ('Hersteller'), the name of the computer, and the date ('Datum') at which the license expires. If there is no license for the installation, instead of the date the string 'keine Lizenz vorhanden' (no license exists) shall be printed. Use the functions TO_DATE and TO_CHAR with the date-format 'DD.MM.YYYY'.
SELECT i.sw_name, i.sw_hersteller, c.name,
COALESCE(TO_CHAR(l.gueltig_bis, 'DD.MM.YYYY'), 'keine Lizenz vorhanden') as Ablaufdatum, l.key
FROM Installation i JOIN Computer c ON (i.c_mac = c.mac AND i.c_pid = c.pid)
LEFT OUTER JOIN (InstallationtoLizenz NATURAL JOIN Lizenz) l ON
(i.sw_name=l.sw_name AND i.sw_hersteller=l.sw_hersteller AND i.v_vnr=l.v_vnr AND i.verzeichnis=l.verzeichnis)
WHERE l.key IS NULL
OR (l.gueltig_bis IS NOT NULL AND l.gueltig_bis <= TO_DATE('01.10.2019', 'DD.MM.YYYY')
AND l.key NOT IN (
SELECT alt_key FROM l_ersetzt));
11.3)
Geben Sie eine Liste aller "Poweruser" aus (Login, VName, NName). "Poweruser" sind BenutzerInnen die mindestens 3 Programme verwendet haben welche in keiner Kategorie ein Skill-Level kleiner als 3 haben (über alle Versionen und Installationen hinweg). Sortieren Sie die Liste absteigend nach der Anzahl solcher Programme die eine Person verwendet hat. Personen mit der selben Anzahl sollen aufsteigend nach Nachname, Vorname und zuletzt Login sortiert werden. Achten Sie darauf für jede Person welche die Eigenschaft erfüllt genau einen Eintrag auszugeben. <br/><br/> English:<br/> List all "Powerusers", i.e. users who have used ('Verwendung') at least three pieces of software that are classified in no category ('Kategorie') with a skill level of less than three (usage over all installations and versions). For each such user state the login, first name (VName) and last name (NName). Sort the list first descending by the number of such programs used by each Poweruser, next ascending by their last name (NName) and first name (VName), and finally by their login. Make sure to produce at most one row for each person.
SELECT login, vname, nname FROM Benutzer NATURAL JOIN (SELECT login, pw, sw_name, sw_hersteller FROM Verwendung GROUP BY login, pw, sw_name, sw_hersteller ) swuse WHERE (sw_name, sw_hersteller) in (SELECT sw_name, sw_hersteller FROM SWvonTyp GROUP BY sw_name, sw_hersteller HAVING min(LVL) >= 3) GROUP BY login, pw HAVING count(*) >= 3 ORDER BY count(*) DESC, nname ASC, vname ASC, login ASC;
11.4)
Geben Sie eine Liste aller "Tripleuser" aus (Login, VName, NName). "Tripleuser" sind BenutzerInnen die mindestens 3 Programme welche in keiner Kategorie ein Skill-Level kleiner als 3 haben jeweils mehr als 333 Minuten verwendet haben (über alle Versionen und Installationen hinweg). Sortieren Sie die Liste absteigend nach der Anzahl solcher Programme die eine Person verwendet hat. Personen mit der selben Anzahl sollen aufsteigend nach Nachname, Vorname und zuletzt Login sortiert werden. Achten Sie darauf für jede Person welche die Eigenschaft erfüllt genau einen Eintrag auszugeben. <br/><br/> English:<br/> List all "Tripleusers", i.e. users who have used ('Verwendung') at least three pieces of software that are classified in no category ('Kategorie') with a skill level of less than three more than 333 minutes each (usage over all installations and versions). FOr each such user state the login, first name (VName) and last name (NName). Sort the list first descending by the number of such programs used by each Poweruser, next ascending by their last name (NName) and first name (VName), and finally by their login. Make sure to produce at most one row for each person.
SELECT login, vname, nname FROM Benutzer NATURAL JOIN (SELECT login, pw, sw_name, sw_hersteller, sum(dauer) as dauer FROM Verwendung GROUP BY login, pw, sw_name, sw_hersteller ) swuse WHERE (sw_name, sw_hersteller) in (SELECT sw_name, sw_hersteller FROM SWvonTyp GROUP BY sw_name, sw_hersteller HAVING min(LVL) >= 3) AND DAUER > 333 GROUP BY login, pw HAVING count(*) >= 3 ORDER BY count(*) DESC, nname ASC, vname ASC, login ASC;
SELECT login, vname, nname FROM Benutzer NATURAL JOIN (SELECT login, pw, sw_name, sw_hersteller, sum(dauer) as dauer FROM Verwendung GROUP BY login, pw, sw_name, sw_hersteller HAVING sum(dauer) > 333 ) swuse WHERE (sw_name, sw_hersteller) in (SELECT sw_name, sw_hersteller FROM SWvonTyp GROUP BY sw_name, sw_hersteller HAVING min(LVL) >= 3) GROUP BY login, pw HAVING count(*) >= 3 ORDER BY count(*) DESC, nname ASC, vname ASC, login ASC;
11.5)
Geben Sie für jeden Laptop (Name, RAM, Cores, MAC) mit weniger als 3.5 (kg) an, welcher Organisation (Name) er gehört. Falls ein Laptop keiner Organisation gehört, soll statt dem Organisationsnamen 'FREI!' ausgegeben werden. <br/><br/> English:<br/> State for every laptop with a weight ('Gewicht') of less than 3.5 (kg) which organization it is property of ('gehoert'). Provide the name of the laptop, its RAM, the number of cores it has and its MAC address and the name of the organization. If the laptop is not property of any organization, state 'FREI!' (free!) instead of the name of the organization.
SELECT c.name, c.ram, c.cores, c.mac, coalesce(cg.name, 'FREI!') FROM Computer c NATURAL JOIN Laptop LEFT OUTER JOIN ComputerGehoert cg ON (cg.mac = c.mac AND cg.pid = c.pid) WHERE gewicht < 3.5;
12.1)
Geben Sie für jeden Computer (Name, MAC) mit einer geraden MAC-Adresse die Anzahl der installierten Betriebsysteme (Software welche zur Kategorie "Betriebsysteme" gehört) aus. Verschiedene Versionen oder Installationen der selben Software sollen nur einmal gezählt werden. <br/><br/> English:<br/> For each computer with an odd MAC-address (MAC), state its name ('Name'), MAC-address (MAC), and the number of operating systems ('Betriebsysteme') installed on that machine (i.e., the number of programs that are classified as 'Betriebsysteme', meaning they belong to the category ('Kategorie') 'Betriebsysteme').
SELECT mac, name, count(*) FROM (SELECT DISTINCT mac, pid, c.name, i.sw_hersteller, i.sw_name FROM Installation i JOIN Computer c ON (i.c_mac = c.mac AND i.c_pid = c.pid) JOIN SWvonTyp t ON (t.sw_hersteller = i.sw_hersteller AND t.sw_name = i.sw_name) WHERE mac%2 = 0 AND Bezeichnung='Betriebsysteme' ) installedSW GROUP BY mac, pid, name
12.2)
Geben Sie für jedes Programm (über alle Versionen hinweg) an, wie viele Anfragen für Installationen von diesem Programm noch am selben Tag genehmigt wurden. Geben Sie den Namen des Programms, die Anzahl der am selben Tag bestätigten Anfragen, sowie (für jedes Programm) den ersten Tag an dem eine solche Anfrage autrat aus. Sortieren Sie das Ergebnis absteigend nach der Anzahl am selben Tag bestätigten Anfragen. Software mit der gleichen Anzahl solcher Anfragen soll aufsteigend zuerst nach dem Hersteller, und anschließend nach dem Namen sortiert werden. Achten Sie darauf dass kein Programm doppelt in der Ausgabe auftritt. <br/><br/> English:<br/> State for each software, how many requests ('Anfragen') to get this program installed ('Installation') on a computer were granted ('genehmigt') on the same day as the request was made. For each program, provide its name ('Name'), the number of such requests, and the first day such a request was made (and thus granted). Sort the output ascending by the number of such requests, the name of the producer, and finally the name of the program. Make sure no program appears twice in the list.
SELECT w.sw_name, count(*), min(w.Datum) FROM wuenscht w JOIN genehmigt g ON (w.anfrage_login = g.anfrage_login AND w.anfrage_pw = g.anfrage_pw AND w.nr = g.anfrage_nr AND w.datum = g.anfrage_datum) WHERE w.Datum = g.Datum GROUP BY w.sw_name, w.sw_hersteller ORDER BY count(*) DESC, w.sw_name ASC, w.sw_hersteller ASC;
12.3)
Geben Sie eine Liste aller Programme (Hersteller, Name der Software, Minimaler RAM) aus, welche auf einem Computer mit mindestens der geforderten RAM-Menge im Verzeichnis '/usr/sbin' oder einem Unterverzeichnis davon installiert sind. Geben Sie für jeden betroffenen Computer auch den Namen des Computers und die Anzahl dessen Cores aus. Sortieren Sie die Ausgabe aufsteigend nach dem Namen der Programme. Einträge mit dem selben Programm-Namen sollen absteigend nach dem Hersteller sortiert sein. Auf diesen Werten idente Einträge sollen weiter absteigend nach der Versionsnummer und zuletzt aufsteigend nach dem RAM des Computers sortiert werden. <br/><br/> English:<br/> List all programs installed in the directory '/usr/sbin' or any of its subdirectories on a computer that satisfy the minimal RAM requirement of the installed version of the program. State the name of the produced ('Hersteller') of the software, the name ('Name') of the software, the required memory (RAM), the name of the computer and the number of its cores. Sort the result ascending by the name of the software, then descending by the name of the producer ('Hersteller'), descending by the version number and ascending by the main memory (RAM) of the computer.
SELECT i.sw_hersteller, i.sw_name, minram, c.name, c.cores FROM Installation i JOIN Version v ON (i.sw_name = v.sw_name AND i.sw_hersteller = v.sw_hersteller AND i.v_vnr = v.vnr) JOIN Computer c ON (i.c_pid = c.pid AND i.c_mac = c.mac) WHERE (verzeichnis = '/usr/sbin' OR verzeichnis LIKE '/usr/sbin/%') AND v.minram <= c.ram ORDER BY i.sw_name ASC, i.sw_hersteller DESC, i.v_vnr DESC, c.ram ASC;
13.1)
Geben Sie das Verzeichnis an, in welchem Programme mit dem Namen 'TeaTimer' am häufigsten installiert ist, und wie oft das ist. <br/><br/> English:<br/> Output the directory ('Verzeichnis'), in which programs ('Software') with the name 'TeaTimer' are most often installed in. Also state how often a software with that name is installed in the directory.
SELECT verzeichnis, count(*) FROM Installation i WHERE sw_name = 'TeaTimer' GROUP BY verzeichnis HAVING count(*) = (SELECT max(anz) FROM (SELECT verzeichnis, count(*) as anz FROM Installation i WHERE sw_name = 'TeaTimer' GROUP BY verzeichnis) installcount);
13.2)
Geben Sie jene Organisation (Name, Kontakt) mit den (im Summe) größten Lizenzkosten aus. (Die Lizenzkosten einer Organisation setzen sich aus dem Preis aller Lizenzen zusammen, welche aktuell Installationen auf einem der Organisation gehörenden Computer zugewiesen sind; unabhängig von dem Enddatum der Lizenz.) Achten Sie darauf, keine Lizenz doppelt zu zählen. <br/><br/> English:<br/> List the organizations ('Organisation') - state their name ('Name') and contact information ('Kontakt') - with the highest overall license fees (Lizenzen). The license fees of an organization are the sum of the fees ('Preis') of all licenses ('Lizenzen') currently linked with an installation ('Installation') on a computer owned ('gehört') by the organization - independent of whether the license is expired or not. Make sure to not count any license twice.
SELECT name, kontakt, SUM(PREIS) FROM ( SELECT name, key FROM Computergehoert JOIN Installation ON (mac = c_mac AND pid = c_pid) NATURAL JOIN InstallationToLizenz GROUP BY name, key ) as l NATURAL JOIN Lizenz NATURAL JOIN Organisation GROUP BY name, kontakt HAVING SUM(Preis) >= ALL (SELECT SUM(PREIS) FROM ( SELECT name, key FROM Computergehoert JOIN Installation ON (mac = c_mac AND pid = c_pid) NATURAL JOIN InstallationToLizenz GROUP BY name, key ) as l NATURAL JOIN Lizenz NATURAL JOIN Organisation GROUP BY name)
13.3)
Geben Sie für jedes installierte Programm (Name, Hersteller) die am häufigsten installierte Versionsnummer aus, und wie viele Installationen es davon gibt. <br/><br/> English:<br/> Find for each installed software - state name ('Name') and producer ('Hersteller') - the version (vnr) installed most often. Also state how many times this version is installed.
SELECT sw_name, sw_hersteller, v_vnr, count(*) FROM Installation GROUP BY sw_name, sw_hersteller, v_vnr HAVING (sw_name, sw_hersteller, count(*)) IN (SELECT sw_name, sw_hersteller, MAX(numbinstalls) FROM (SELECT sw_name, sw_hersteller, v_vnr, count(*) as numbinstalls FROM Installation GROUP BY sw_name, sw_hersteller, v_vnr) installcounts GROUP BY sw_name, sw_hersteller);
14.1)
Geben Sie alle BenutzerInnen (VName, NName) aus, welche sich für sehr wichtig halten. D.h. BenutzerInnen, von welchen mehr als die Hälfte ihrer Anfragen Priorität 1 hatte. Geben Sie zu jeder solchen Person außerdem die Anzahl ihrer Priorität 1 Anfragen an. <br/><br/> English:<br/> List for user ('Benutzer') who deem themselves to be VIPs. I.e., all users for which the majority of their requests ('Anfrage') are of priority ('Priorität') 1. For each such person, state their first name (VName), their last name (NName), as well as the number of requests of priority 1.
SELECT vname, nname, count(*) FROM Benutzer b NATURAL JOIN Anfrage WHERE prioritaet = 1 GROUP BY login, pw, vname, nname HAVING count(*) >= 0.5*(SELECT numbanfragen FROM (SELECT a.login, a.pw, count(*) as numbanfragen FROM Anfrage a GROUP BY a.login, a.pw) tmp WHERE tmp.login = b.login AND tmp.pw = b.pw);
SELECT vname, nname, count(*) FROM Benutzer b NATURAL JOIN Anfrage WHERE prioritaet = 1 GROUP BY login, pw, vname, nname HAVING 2*count(*) >= (SELECT numbanfragen FROM (SELECT a.login, a.pw, count(*) as numbanfragen FROM Anfrage a GROUP BY a.login, a.pw) tmp WHERE tmp.login = b.login AND tmp.pw = b.pw);
SELECT vname, nname, count(*) FROM Benutzer b NATURAL JOIN Anfrage WHERE prioritaet = 1 GROUP BY login, pw, vname, nname HAVING count(*) >= (SELECT numbanfragen FROM (SELECT a.login, a.pw, count(*) as numbanfragen FROM Anfrage a GROUP BY a.login, a.pw) tmp WHERE tmp.login = b.login AND tmp.pw = b.pw)/2.0;
15.1)
Geben Sie jene Benutzer (Login, PW, VName, NName) mit der meisten Arbeitszeit (= Summe der Verwendung irgendwelcher Software) aus die noch nie eine Anfrage gestellt haben. <br/><br/> English:<br/> List all users ('Benutzer') with the highest usage (most time - 'Dauer' - spent using - 'Verwendung' - any software) that never filed a request ('Anfrage'). State the login ('Login'), password (PW), first name (VName) and last name (NName).
SELECT login, pw, VName, NName FROM Benutzer NATURAL JOIN Verwendung NATURAL JOIN MAGehoertZu WHERE (login, pw) NOT IN (SELECT login, pw FROM Anfrage) GROUP BY login, pw HAVING sum(dauer) >=ALL (SELECT sum(dauer) FROM Benutzer NATURAL JOIN Verwendung NATURAL JOIN MAGehoertZu WHERE (login, pw) NOT IN (SELECT login, pw FROM Anfrage) GROUP BY login, pw );
SELECT login, pw, VName, NName FROM (SELECT login, pw, VName, NName, RANK() OVER (ORDER BY sum(dauer) DESC) AS pos FROM Benutzer NATURAL JOIN Verwendung NATURAL JOIN MAGehoertZu WHERE (login, pw) NOT IN (SELECT login, pw FROM Anfrage) GROUP BY login, pw ) tmp WHERE pos=1
15.2)
Geben Sie die MitarbeiterInnen (VName, NName) der Organisation(en) mit den wenigsten MitarbeiterInnen an. <br/><br/> English:<br/> List the people (VName, NName) of those organizations (Organisationen) with the lowest number of employees.
SELECT VName, NName FROM MAGehoertZu NATURAL JOIN Benutzer WHERE name IN (SELECT name FROM MAGehoertZu GROUP BY Name HAVING count(*) <= ALL(SELECT count(*) FROM MAGehoertZu GROUP BY name));
SELECT VName, NName FROM MAGehoertZu NATURAL JOIN Benutzer WHERE (name,1) IN (SELECT name, RANK() OVER (ORDER BY count(*)) FROM MAGehoertZu GROUP BY Name);
15.3)
Geben Sie für jeden Computer jene Person an, welche am meisten Installationen auf dem Gerät verwendet, und welche Software diese Person auf dem Computer verwendet. Geben Sie den Namen des Computers, den Login und das PW der Person sowie den Namen der Software und des Herstellers aus. <br/><br/> English:<br/> For each Computer, list all persons ('Benutzer') who use the most installations ('Installationen') on the computer, and which pieces of software they use. State the name of the computer, the login and password of the person, and the name of the software and the producer ('Hersteller').
SELECT name, login, pw, sw_name, sw_hersteller FROM (SELECT c_mac, c_pid, login, pw FROM Verwendung GROUP BY c_mac, c_pid, login, pw HAVING (c_mac, c_pid, count(*)) IN ( SELECT c_mac, c_pid, max(usage) FROM (SELECT c_mac, c_pid, login, pw, count(*) as usage FROM Verwendung GROUP BY c_mac, c_pid, login, pw) as u GROUP BY c_mac, c_pid) ORDER BY c_mac, c_pid) as v1 NATURAL JOIN Verwendung JOIN Computer c ON (v1.c_mac=c.mac AND v1.c_pid=c.pid) GROUP BY name, login, pw, sw_name, sw_hersteller
15.4)
Geben Sie unter allen BenutzerInnen welche schon in mindestens zwei Schulungen die Note 2 erhalten haben, für jenen BenutzerInnen welche am meisten verschiedene Software verwendet haben alle verwendeten Installationen aus. Geben Sie dabei folgende Informationen an: VName, NName, Name der Software, Hersteller der Software, Versionsnummer und Installationsverzeichnis. <br/><br/> English:<br/> Among all users ('Benutzer') who got a grade ('Note') 2 in at least two trainings ('Schulung'/geschult'), for those who used the most different pieces of software, list all used installations. Provide the first (VName) and last (NName) name of the person, the name of the software and the producer ('Hersteller'), as well as the version number and the installation directory ('verzeichnis').
SELECT VName, NName, sw_name, sw_hersteller, v_vnr, verzeichnis FROM Verwendung WHERE (login, pw) IN (SELECT login, pw FROM (SELECT login, pw, sw_name, sw_hersteller FROM Verwendung WHERE (login, pw) in (SELECT login, pw FROM geschult WHERE note = 2 GROUP BY login, pw HAVING count(*) > 1) GROUP BY login, pw, sw_name, sw_hersteller) as t1 GROUP BY login, pw HAVING count(*) >= ALL (SELECT count(*) FROM ( SELECT login, pw, sw_name, sw_hersteller FROM Verwendung WHERE (login, pw) in (SELECT login, pw FROM geschult WHERE note = 2 GROUP BY login, pw HAVING count(*) > 1) GROUP BY login, pw, sw_name, sw_hersteller) as t1 GROUP BY login, pw) )