TP2 sql .pdf


Nom original: TP2_sql.pdfAuteur: 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 733 fois.
Taille du document: 288 Ko (4 pages).
Confidentialité: fichier public


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’);


Aperçu du document TP2_sql.pdf - page 1/4

Aperçu du document TP2_sql.pdf - page 2/4

Aperçu du document TP2_sql.pdf - page 3/4

Aperçu du document TP2_sql.pdf - page 4/4




Télécharger le fichier (PDF)


TP2_sql.pdf (PDF, 288 Ko)

Télécharger
Formats alternatifs: ZIP



Documents similaires


tp2 sql
pl sql exercices corriges
database finalexam 13 en solution
database finalexam 13 fr solution
11 34 attorney 1
accounting 1 quickstudy

Sur le même sujet..