Practice Set-4

MY SQL DATABASE QUERIES

SQL statement to search from the list of values, since it will be difficult to write such number of values in the query, we select that list of value in where clause, for example: here in IN() we selected the list of values which are in employee id from job history.

1. Show average salary, job title and number of employees who did a job in the past.

Select job_title, count(employee_id), avg(salary) 
from jobs natural join employees 
where employee_id in (Select employee_id from job_history) group by job_title;

 Output:-

SQL statement to search from values not present in a list, since the values are unknown, we select list of value in where clause, for example: here in NOT IN() function we selected the list of values which are employee id from job history.

2. Show the names of employees whose job history is not present.

Select first_name, last_name from employees 
where employee_id not in(Select employee_id from job_history);

 Output:-

SQL statement to display details regarding a common value, for example: here we displayed the details of the department which are managed by Jennifer.

3. Show details of department id and department name which are managed by ‘Jennifer’

Select * from departments
 where manager_id in (Select employee_id from employees where first_name = ‘Jennifer’);

 Output:-

SQL statement using the set of values from another table and then using it to display result from another table, this is achieved by using sub-query, here details are displayed from departments table but in where clause values are taken from employees table.

4. Display details of departments in which the maximum salary is more than 8000.

Select * from departments 
where department_id in (Select department_id from employees group by departments_id having max(salary)>8000;

 Output:-

SQL statement to display the number of days from a given date and the current date of the system, for example here the number of days from given date is computed by the system.

5. Display the number of days between current date and 1st March 2008.

Select sysdate – to-date(‘01-MAR-2008’) from dual;

 Output:-

SQL statement to group and display records by extracting years from dates and counting records for each year, for example, here the record of number of employees hired each year is displayed.

6. Show the number of employees joined in each year.

Select distinct(extract(year from hire_date)) as YEAR,
count(employee_id) as NO_EMPLOYEE 
from employees group by extract(year from hire_date);

 Output:-

SQL statement to group and display records for each manager, for example: here record of number of employee working under their respective manager is displayed.

7. Show the manager ID and number of emp. working under that manager.

Select manager_id, count(*) as NO_EMP from employees group by manager_id;

 Output:-

SQL statement to group and display records for each country, for example here the record of number of cities in the respective countries are displayed

8. Display the country ID and number of cities we have in the country.

Select country_id, count(city) from locations group by country_id;

Output:-

SQL statement to group and display records for each department name, for example: here record of number of employees working in respective department are displayed.

9. Display department name and number of employees in the department.

Select department-name, count(*) 
from employees natural join departments group by department_name;

 Output:-

SQL statement displaying column from two different table join on a common column, for example: here departments and employee tables are joined on manager id and employee id respectively.

10. Display department name and manager first name.

Select first_name, department_name 
from departments d join employees e on (d.manager_id=e.employee_id);

 Output:-