Database I Practice SQL Database Schema hr jobhistory
Database I. Practice SQL
Database Schema hr. job_history (employee_id, start_date, end_date, job_id, department_id) hr. jobs (job_id, job_title, min_salary, max_salary) hr. departments (department_id, department_name, manager_id, location_id) hr. employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, comission_pct, manager_id, department_id) hr. locations (location_id, street_address, postal_code, city, state_province, country_id) hr. countries (country_id, country_name, region_id)
Exercises • What is the telephone number and email address of Mozhe Atkinson? • In which department does he work? (All attributes of the department in question should be returned. ) • How much does he earn more than the minimal salary that he could get for his job? • What is the name of his manager? • What are the names of those employees who work in a department whose name contains letters a and s in arbitrary order? • What are the phone numbers of those employees who started their job after 15 -03 -2003? • What are the email addresses of those emloyees who finished their job in March?
More Exercises… • What are the names of those employees who work in Brazil and earn as much money as can be earned in that job they profess. • What are the names of those employees who work in at least two different departments? • What are the names of those departments that have at least two employees? • What are those job titles that are professed in China but not in France? • What are the name of those employees who worked more than 500 days in a department without interruption?
Subqueries • What are the names of those departments that are located in Paris? (use IN) • What are the names of those employees who have a colleague working at the same department and earning the same salary? (use EXISTS) • What are the names of those departments, where everybody earns more than 1000 dollars? (use NOT IN) • What is the name of the employee who has the highest salary? • What are the names of those employees whose salary is greater than the lowest salary given for an employee? • What are the names of those employees who earns more than at least two other employees? • What is the name of that employee who has the second highest salary?
Aggregation • How many employees work at the IT department? • What are the maximum and minimum salaries that are paid to an employee? (The result should contain a single row with two columns. ) • Which job has the lowest minimum salary? • What is the name of that department, where the employees earn the most all together? • Who is that employee, who worked for the longest time in a department? • Which is the longest email address?
Grouping • For each department give the number of employees, who work in the department in question and the average of the salaries of these employees. • Which is that city, where the most departments are located? • Which is that job, where the sum of the salaries is the highest? • Which are those jobs that are professed by at least five employees? • What are the names of those departments in which even the lowest salary is higher than 2000?
- Slides: 7