1 / 5

Übungen SQL

Übungen SQL. Neue Tabellen. mysql> select * from EMPPROJ; +-------+--------+---------+ | EMPNO | PROJNO | PERCENT | +-------+--------+---------+ | 7369 | 1 | 30 | | 7369 | 2 | 10 | | 7369 | 3 | 20 | | 7499 | 4 | 5 |

freya-hale
Télécharger la présentation

Übungen SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Übungen SQL

  2. Neue Tabellen mysql> select * from EMPPROJ; +-------+--------+---------+ | EMPNO | PROJNO | PERCENT | +-------+--------+---------+ | 7369 | 1 | 30 | | 7369 | 2 | 10 | | 7369 | 3 | 20 | | 7499 | 4 | 5 | | 7499 | 5 | 10 | | 7566 | 1 | 10 | | 7566 | 2 | 10 | | 7566 | 3 | 10 | | 7566 | 4 | 10 | | 7566 | 5 | 10 | | 7788 | 1 | 10 | | 7788 | 2 | 20 | | 7788 | 5 | 10 | +-------+--------+---------+ 13 rows in set (0.00 sec) mysql> select * from PROJECT; +--------+------------+--------+------------+-------+--------+ | PROJNO | PROJNAME | BUDGET | ORT | PLZ | DEPTNO | +--------+------------+--------+------------+-------+--------+ | 1 | Stargate | 100000 | Ulm | 89073 | 20 | | 2 | Terminator | 200000 | Heidenheim | 89520 | 20 | | 3 | Watergate | 500000 | Hamburg | 20095 | 20 | | 4 | Mini | 500 | Hamburg | 20095 | 30 | | 5 | Startup | 5000 | Hamburg | 20095 | 30 | +--------+------------+--------+------------+-------+--------+ 5 rows in set (0.00 sec)

  3. Neue Tabellen, Joins • Mitarbeiter (Namen) des Projekts „Stargate“ • Projekte nach Budget sortiert • Aufsteigend • Absteigend • Mitarbeiter, die an Projekten in „Ulm“ arbeiten • Projekte mit durchführender Abteilung

  4. Aggregate Functions • Mitarbeiter je Projekt (nicht Köpfe, sondern Prozentsatz)select sum(percent) from EMPPROJ group by PROJNO;select p.projname,sum(ep.percent) from EMPPROJ ep, PROJECT p where ep.projno=p.projno group by ep.PROJNO; • Projektbudget je Abteilungselect sum(budget) from PROJECT group by deptno;select d.dname,sum(p.budget) from PROJECT p, DEPT d where p.deptno=d.deptno group by p.deptno; • Mittleres Projektbudget je Abteilung • Ohne Kleinstprojekte (< 1000) • Nur für Abteilungen mit mehr als zwei Projektenselect avg(budget) from PROJECT group by deptno;select avg(budget) from PROJECT where budget > 1000 group by deptno;select avg(budget) from PROJECT group by deptno having (count(*)>2);

  5. weiter • Budget je PLZ-Gebiet (erste Ziffer)select sum(budget) from PROJECT group by left(plz,1); • Mitarbeiter je PLZ-Gebiet (Prozent, nicht Köpfe)select sum(ep.percent) from PROJECT p, EMPPROJ ep where p.projno=ep.projno group by left(p.plz,1); • Mitarbeiter, die in Projekten mitarbeitenselect distinct e.ename from EMP e, EMPPROJ ep where e.empno=ep.empno; • Abteilungen mit mind. Ein SALESMANselect deptno from EMP where job='SALESMAN' group by deptno having count(*) > 1;

More Related