Fichier PDF

Partagez, hébergez et archivez facilement vos documents au format PDF

Partager un fichier Mes fichiers Boite à outils PDF Recherche Aide Contact



TP2 sql .pdf



Nom original: TP2_sql.pdf
Auteur: Laurent

Ce document au format PDF 1.5 a été généré par Microsoft® Word 2013, et a été envoyé sur fichier-pdf.fr le 29/08/2014 à 09:58, depuis l'adresse IP 78.116.x.x. La présente page de téléchargement du fichier a été vue 447 fois.
Taille du document: 288 Ko (4 pages).
Confidentialité: fichier public




Télécharger le fichier (PDF)









Aperçu du document


TP2 : SQL
1) SELECT first_name, salary, title,dept_name
FROM employees INNER JOIN salaries
INNER JOIN titles INNER JOIN dept_emp
ON employees.emp_no = salaries.emp_no
AND salaries.emp_no = titles.emp_no
AND titles.emp_no = dept_emp.emp_no
INNER JOIN departments ON
dept_emp.dept_no = departments.dept_no WHERE dept_emp.to_date = '9999-01-01'
AND titles.to_date ='9999-01-01'
AND salaries.to_date='9999-01-01'
ORDER BY salaries.salary;
2) SELECT first_name, last_name, dept_name
FROM employees
INNER JOIN dept_manager
INNER JOIN departments
ON employees.emp_no = dept_manager.emp_no
AND dept_manager.dept_no= departments.dept_no
WHERE to_date = ‘9999-01-01’;
3) SELECT DISTINCT title FROM titles
WHERE to_date = ‘9999-01-01’;
4) SELECT COUNT(DISTINCT title) AS nbtitres FROM titles WHERE to_date = ‘9999-01-01’;
5) SELECT COUNT( title) AS nb_engineer
FROM titles WHERE title =’Engineer’
AND to_date=’9999-01-01’;
6) SELECT AVG(salary) AS moyen_salaire,
MAX(salary) AS max_salaire,
MIN(salary) AS min_salaire
FROM salaries WHERE to_date=’9999-01-01’;
7) SELECT employees.emp_no, first_name, last_name, dept_no FROM employees
INNER JOIN dept_manager ON employees.emp_no = dept_manager.emp_no
WHERE((dept_manager.dept_no = 'd005' OR dept_manager.dept_no = 'd008')
AND dept_manager.to_date='9999-01-01') ;

8) SELECT last_name,first_name, dept_no
FROM employees INNER JOIN dept_emp
ON employees.emp_no = dept_emp.emp_no
WHERE dept_emp.dept_no = 'd003'
AND dept_emp.to_date = '9999-01-01'
ORDER BY last_name ;

9) SELECT employees.emp_no, last_name, first_name, hire_date
FROM employees WHERE (YEAR(hire_date)=1985)
AND emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date=’9999-01-01’) ;
10) SELECT title, COUNT(titles.emp_no), AVG(salary) AS moyen_salaire, MAX(salary) AS
max_salaire, MIN(salary) AS min_salaire FROM salaries INNER JOIN titles ON
salaries.emp_no=titles.emp_no WHERE (titles.to_date=’9999-01-01’)AND(salaries
to_date=’9999-01-01’) GROUP BY title;
11) SELECT first_name, last_name, dept_no, title FROM (employees INNER JOIN salaries
INNER JOIN titles INNER JOIN dept_emp ON employees.emp_no = salaries.emp_no
AND salaries.emp_no = titles.emp_no AND titles.emp_no = dept_emp.emp_no)
WHERE (titles.to_date = '9999-01-01') AND (salaries to_date=’9999-01-01’) AND
(salary>=40000 OR title = 'manager')
AND NOT dept_no ='d003';
12) SELECT first_name, last_name, salary, from_date, to_date FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no WHERE (salaries
to_date=’9999-01-01’);
13) SELECT dept_no ,MAX(salary) AS salaireMax
FROM dept_emp INNER JOIN salaries
ON dept_emp.emp_no = salaries.emp_no
WHERE (salaries.to_date ='9999-01-01') AND (dept_emp.to_date=’9999-01-01’)
GROUP BY dept_no;
14) SELECT dept_no, COUNT(salaries.emp_no) AS Nb_emp, AVG(salary) AS avg_salary
FROM dept_emp INNER JOIN salaries ON (dept_emp.emp_no=salaries.emp_no)
WHERE (salaries.to_date ='9999-01-01') AND (dept_no.to_date=’9999-01-01’)
GROUP BY dept_no HAVING COUNT(salaries.emp_no)>15000;
15) SELECT dept_name, title, COUNT(titles.emp_no) AS Nb_employees, MIN(salary),
AVG(salary), MAX(salary) FROM dept_emp INNER JOIN salaries INNER JOIN titles INNER JOIN

departments ON (dept_emp.emp_no=salaries.emp_no) AND
(dept_emp.emp_no=titles.emp_no) AND (dept_emp.dept_no=departments.dept_no)
WHERE (salaries.to_date ='9999-01-01') AND (dept_emp.to_date=’9999-01-01’) AND
(titles.to_date=’9999-01-01’)
GROUP BY dept_name, title;

16) SELECT dept_name, AVG(salary) AS avg_salary FROM dept_emp INNER JOIN
departments INNER JOIN salaries ON (dept_emp.dept_no=departments.dept_no) AND
(dept_emp.emp_no=salaries.emp_no)
WHERE (salaries.to_date ='9999-01-01') AND (dept_emp.to_date=’9999-01-01’)
GROUP BY dept_name HAVING AVG(salary)>60000;
17) SELECT first_name, last_name, hire_date FROM employees
WHERE hire_date>CURDATE()-150000;
18) CREAT TEMPORARY TABLE Manager AS (
SELECT last_name, hire_date,dept_no FROM employees INNER JOIN dept_manager ON
(dept_manager.emp_no=employees.emp_no)
WHERE (dept_manager.to_date ='9999-01-01');)
SELECT employees.last_name, employees.hire_date, Manager.last_name,
Manager.hire_date FROM employees INNER JOIN Manager INNER JOIN dept_emp ON
(dept_emp.emp_no=employees.emp_no) AND
(dept_emp.dept_no=dept_manager.dept_no)
WHERE (dept_emp.to_date='9999-01-01') AND (employees.hire_date<Manager.hire_date);
19) ?
20) SELECT first_name, last_name, title, dept_name
FROM employees INNER JOIN titles
INNER JOIN dept_emp INNER JOIN departments
ON (employees.emp_no = titles.emp_no) AND (titles.emp_no = dept_emp.emp_no) AND
(dept_emp.dept_no=departments.dept_no) WHERE title = ‘Manager’
AND titles.to_date = ‘9999-01-01’;
21) SELECT first_name, last_name, salary
FROM employees INNER JOIN salaries
ON employees.emp_no = salaries.emp_no
WHERE to_date =’9999-01-01’ AND salary>=(SELECT AVG(salary) FROM salaries);

22) SELECT first_name, last_name, hire_date FROM employees WHERE hire_date=(SELECT
DINSTINCT hire_date FROM employees WHERE last_name=’Pappas’);
23) SELECT first_name, last_name, dept_no FROM employees INNER JOIN dept_emp ON
employees.emp_no=dept_emp.emp_no) WHERE dept_no=(SELECT DINSTINCT dept_no
FROM employees INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no)
WHERE last_name=’Ashish’);
24) SELECT first_name, last_name, dept_no FROM employees INNER JOIN titles ON
employees.emp_no=titles.emp_no) WHERE title=(SELECT DINSTINCT title FROM employees
INNER JOIN titles ON employees.emp_no=titles.emp_no) WHERE last_name=’Kambil’);


TP2_sql.pdf - page 1/4
TP2_sql.pdf - page 2/4
TP2_sql.pdf - page 3/4
TP2_sql.pdf - page 4/4

Documents similaires


Fichier PDF tp2 sql
Fichier PDF pl sql exercices corriges
Fichier PDF database finalexam 13 fr solution
Fichier PDF 2013 14 ecuad executive compensation
Fichier PDF database finalexam 13 en solution
Fichier PDF i6lcm1s


Sur le même sujet..