Microsoft SQL
opdracht
Regio College Zaanstreek Waterland Afdeling ICT‐Opleidingen
In deze opdracht wordt gebruik gemaakt van de database ORCADB, waarop drie tabellen zijn gedefinieerd namelijk: EMP, DEPT en SALGRADE. SELECT * FROM EMP; levert op: Tabel: EMP EMPNO 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934
!
ENAME SMITH ALLEN WARD JONES MARTIN BLAKE CLERK SCOTT KING TURNER ADAMS JAMES FORD MILLER
JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK
MGR 7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782
HIREDATE 12/17/80 02/20/80 02/22/81 04/02/81 09/28/81 05/01/81 06/09/81 11/26/86 11/17/81 09/08/81 12/30/80 12/03/81 12/03/81 01/23/82
COMM 300 500 1400 0
LET OP: SAL is per maand, COMM is per jaar, HIREDATE is DATE
De relaties tussen de “chefs”en “onderschikten” is als volgt:
SELECT * FROM DEPT; levert op: Tabel: DEPT DEPTNO 10 20 30 40
SAL 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300
DNAME ACCOUNTING RESEARCH SALES OPERATIONS
LOC NEW YORK DALLAS CHIGACO BOSTON
DEPTNO 20 30 30 20 30 30 10 20 10 30 20 30 20 10
SELECT * FROM SALGRADE; levert op: Tabel: SALGRADE GRADE 1 2 3 4 5
LOSAL 700 1201 1401 2001 3001
HISAL 1200 1400 2000 3000 9999
Opgaven 1. 2. 3.
Wie heeft employeenummer 7900? Laat alle gegevens van King zien. Welke klerks verdienen minstens 1100? EMPNO 7934 7876
4.
MGR 7782 7788
ENAME SMITH JONES SCOTT KING ADAMS FORD
JOB CLERK MANAGER ANALYST PRESIDENT CLERK ANALYST
MGR 7902 7839 7566 7788 7566
ENAME SCOTT MILLER
JOB ANALYST CLERK
MGR 7566 7782
SAL 1300 1100
COMM
DEPTNO 10 20
HIREDATE 12/17/80 04/02/81 11/26/86 11/17/81 12/30/80 12/03/81
SAL 800 2975 3000 5000 1100 3000
COMM
DEPTNO 20 20 20 10 20 20
HIREDATE 11/26/86 01/23/82
SAL 3000 1300
COMM
DEPTNO 20 10
Er zijn meerdere manieren om uit te vinden wie er niet in afdeling 30 werken. Welke van de onderstaande manieren zijn incorrect en waarom?
1.
SELECT * FROM EMP WHERE NOT DEPTNO=30;
2.
SELECT * FROM EMP WHERE DEPTNO<10 AND DEPTNO>30;
3.
SELECT * FROM EMP WHERE DEPTNO<30 OR DEPTNO>30;
HIREDATE 01/23/82 12/30/80
Wie kwamen er pas na 1981 in dienst? EMPNO 7788 7934
6.
JOB CLERK CLERK
Geef de president en de werknemers van afdeling 20. EMPNO 7369 7566 7788 7839 7876 7902
5.
ENAME MILLER ADAMS
4. 7.
Wie in afdeling 30 hebben er minder dan 500 commissie of helemaal geen commissie? EMPNO 7499 7698 7844 7900
8.
MGR 7698 7839 7698 7698
ENAME SMITH ADAMS JAMES FORD MILLER
JOB CLERK CLERK CLERK ANALYST CLERK
MGR 7902 7788 7698 7566 7782
ENAME SCOTT
JOB ANALYST
MGR 7566
ENAME SMITH ALLEN WARD MARTIN TURNER ADAMS JAMES MILLER
JOB CLERK SALESMAN SALESMAN SALESMAN SALESMAN CLERK CLERK CLERK
MGR 7902 7698 7698 7698 7698 7788 7698 7782
HIREDATE 12/17/80 12/30/80 12/03/81 12/03/81 01/23/82
HIREDATE 11/26/86
HIREDATE 12/17/80 02/20/80 02/22/81 09/28/81 09/08/81 12/30/80 12/03/81 01/23/82
Maak de volgende lijst: WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER WERKNEMER
HIREDATE 02/20/80 05/01/81 09/08/81 12/03/81
SAL 1600 2850 1500 950
COMM 300 0
DEPTNO 30 30 30 30
SAL 800 1100 950 3000 1300
COMM
DEPTNO 20 20 30 20 10
SAL 3000
COMM
DEPTNO 20
SAL 800 1600 1250 1250 1500 1100 950 1300
COMM 300 500 1400 0
DEPTNO 20 30 30 30 30 20 30 10
Geeft iedereen die geen manager, analist of president is. EMPNO 7369 7499 7521 7654 7844 7876 7900 7934
11.
JOB SALESMAN MANAGER SALESMAN CLERK
Vind de medewerkers die niet in de periode 1980‐1982 in dienst kwamen. EMPNO 7788
10.
ENAME ALLEN BLAKE TURNER JAMES
Wie kwamen er in december of in januari in dienst? EMPNO 7369 7876 7900 7902 7934
9.
SELECT * FROM EMP WHERE DEPTNO<>30;
ENAME SMITH ALLEN WARD JONES MARTIN BLAKE CLERK SCOTT KING TURNER ADAMS JAMES FORD MILLER
12.
Geeft alle verschillende afdeling/beroep/salaris combinaties. DEPTNO 10 10 10 20 20 20 20 30 30 30 30 30
13.
SAL 1300 2450 5000 3000 500 1100 2975 950 2850 1250 1500 1600
Sorteer iedereen eerst op salaris (hoogste salarissen eerst), dan op naam. EMPNO 7839 7902 7788 7566 7698 7782 7499 7844 7934 7654 7521 7876 7900 7369
14.
JOB CLERK MANAGER PRESIDENT ANALYST CLERK CLERK MANAGER CLERK MANAGER SALESMAN SALESMAN SALESMAN
ENAME KING FORD SCOTT JONES BLAKE CLERK ALLEN TURNER MILLER MARTIN WARD ADAMS JAMES SMITH
JOB PRESIDENT ANALYST ANALYST MANAGER MANAGER MANAGER SALESMAN SALESMAN CLERK SALESMAN SALESMAN CLERK CLERK CLERK
SAL 5000 3000 3000 2975 2850 2450 1600 1500 1300 1250 1250 1100 950 800
Sorteer iedereen op eerst op afdeling, dan op functie, dan op datum van indiensttreding (nieuwkomers eerst).
EMPNO 7934 7782 7839 7788 7902 7876 7369 7566 7900 7698 7654 7844 7521 7499
15.
ENAME MILLER CLERK KING SCOTT FORD ADAMS SMITH JONES JAMES BLAKE MARTIN TURNER WARD ALLEN
MGR 7782 7839 7566 7566 7788 7902 7839 7698 7839 7698 7698 7698 7698
HIREDATE 01/23/82 06/09/81 11/17/81 11/26/86 12/03/81 12/30/80 12/17/80 04/02/81 12/03/81 05/01/81 09/28/81 09/08/81 02/22/81 02/20/80
SAL 1300 2450 5000 3000 3000 1100 800 2975 950 2850 1250 1500 1250 1600
COMM 1400 0 500 300
DEPTNO 10 10 10 20 20 20 20 20 30 30 30 30 30 30
Laat alle mogelijke en onmogelijke werknemer‐chef combinaties zien, dus ook ik ben mijn eigen chef, WARD is chef van JONES en andersom.
Totaal 126 combinaties.
JOB CLERK MANAGER PRESIDENT ANALYST ANALYST CLERK CLERK MANAGER CLERK MANAGER SALESMAN SALESMAN SALESMAN SALESMAN
16.
In welke salarisschaal zou iedereen terecht komen na een algemene salarisverhoging van 12%?
ENAME SMITH JAMES ADAMS MILLER ALLEN TURNER CLERK JONES BLAKE SCOTT FORD KING
17.
ENAME_A 1 1 2 3 3 3 4 5 5 5 5 5
Laat alle mogelijke interlokale telefoongesprekpartners zien. 126 verschillende mogelijkheden
18.
Geef voor iedereen zijn naam en de naam van de manager. ENAME SCOTT FORD JAMES WARD ALLEN MARTIN TURNER MILLER ADAMS JONES CLERK BLAKE SMITH
19.
Geef voor iedereen de naam en de naam (namen) van de ondergeschikte(n).
ENAME_A JONES JONES BLAKE BLAKE BLAKE BLAKE BLAKE CLERK SCOTT KING KING KING FORD
ENAME JONES JONES BLAKE BLAKE BLAKE BLAKE BLAKE CLERK SCOTT KING KING KING FORD
ENAME_A SCOTT FORD JAMES WARD ALLEN MARTIN TURNER MILLER ADAMS JONES CLERK BLAKE SMITH
20.
Wie verdient er meer dan zijn manager? ENAME SCOTT FORD
21.
SAL 3000 3000
SAL 800 950 1100 1300 1600 1500 2450 2975 2850 3000 3000 5000
JOB PRESIDENT
Wie hebben er een uniek salaris? EMPNO 7369 7499 7566 7698 7782 7839 7844 7876 7900 7934
24.
ENAME SMITH ALLEN JONES BLAKE CLERK KING TURNER ADAMS JAMES MILLER
JOB CLERK SALESMAN MANAGER MANAGER MANAGER PRESIDENT SALESMAN CLERK CLERK CLERK
MGR 7902 7698 7839 7839 7839 7698 7788 7698 7782
HIREDATE 12/17/80 02/20/80 04/02/81 05/01/81 06/09/81 11/17/81 09/08/81 12/30/80 12/03/81 01/23/82
SAL 800 1600 2975 2850 2450 5000 1500 1100 950 1300
COMM 300 0
Geef de beroepen uit afdeling 30 die niet in afdeling 20 voorkomen. JOB SALESMAN
GRADE 1 1 1 2 3 3 4 4 4 4 4 5
Geef de mensen met een uniek beroep. ENAME KING
23.
SAL_A 2975 2975
Geef van iedereen naam, salaris en de bijbehorende salarisschaal. ENAME SMITH JAMES ADAMS MILLER ALLEN TURNER CLERK JONES BLAKE SCOTT FORD KING
22.
ENAME_A JONES JONES
DEPTNO 20 30 20 30 10 10 30 20 30 10
25.
Sorteer iedereen op het salaris van zijn of haar manager. EMPNO MILLER JAMES WARD ALLEN TURNER MARTIN SCOTT FORD ADAMS SMITH JONES BLAKE CLERK
26.
ENAME 2450 2850 2850 2850 2850 2850 2975 2975 3000 3000 5000 5000 5000
JOB 1300 950 1250 1600 1500 1250 3000 3000 1100 800 2975 2850 24500
Wie hebben er ondergeschikten, die zelf ook weer ondergeschikten hebben? ENAME JONES KING
27.
Wat voor totaalbedragen worden er zoal uitbetaald, hetzij maandelijks, hetzij jaarlijks? COLUMN_1 350500
28.
COLUMN_2 29025
Geef alle afdelingsnummers die niet in de EMP tabel voorkomen, maar uiteraard wel in de DEPT tabel.
DEPTNO 40
29.
Welk beroep komt in afdeling 10, in afdeling 20 en in afdeling 30 voor? JOB CLERK MANAGER
30.
Geef het gemiddelde salaris. COLUMN_1 2073
31.
Geef de gemiddelde commissie. COLUMN_1 157
32.
Hoeveel rijen bevat de EMP tabel? COLUMN_1 14
33.
Wie zitten er precies op de bovengrens van een salarisschaal? ENAME SCOTT FORD
34.
GRADE 4 4
SAL 3000 3000
Laat alle mogelijke EMP/SALGRADE combinaties zien (ENAME, SAL en alle SALGRADE kolommen).
70 combinaties 35.
Geef het gemiddelde salaris voor alle beroepsgroepen, met meer dan 2 mensen in die groep. JOB CLERK MANAGER SALESMAN
36.
COLUMN_2 12450 33100 16800
Geef de werknemers in afdeling 10, die hetzelfde beroep hebben als medewerkers in de SALES afdeling.
ENAME MILLER CLERK
37.
JOB CLERK MANAGER
Wat is het verschil tussen het hoogste en het laagste salaris? COLUMN_1 4200
38.
Hoeveel klerken zijn er? COLUMN_1 4
39.
In welke afdeling van minstens 4 mensen is het minimum salaris kleiner dan 900 of het maximum salaris groter dan 4000?
DEPTNO 20
40. 41. 42.
COLUMN_2 5
Verander de naam van alle klerken die werken op afdeling 30 in “CLEANER”. Verwijder uit EMP alle klerken. Voeg jezelf toe aan de EMP tabel, zonder commissie. Je werkt in Boston.
