Practice Set-2

MY SQL DATABASE QUERIES

SQL statement to count records for the query specified by the user. For example, here we selected year which has more than 20 employees hired.

1. Show years in which more than 20 employees joined.

Select to_char(hire_date, ‘yyyy’),count(*) from employees 
group by to _char(hire_date, ‘yyyy’) having count(employee_id)>20;

Output:-

SQL statement to find the number of character in the search record is done by using length() function where the column name is given in parenthesis for which it returns the length of value, for example here first name and its length are given, also here the names are filtered on the pattern such that is has ‘a’ as the third character in the name.

2. Show the length of name for employees where the first name has character ‘a’ at 4th place.

Select first_name, length (first_name) from employees where first_name like ‘__a%’;

Output:-

SQL statement to give details from more than one table by using the concept of JOINS, where two tables are joined on the common column in both, here manager id in departments table and employee id in employees table are common so they are joined, and we get our query.

3. Show manager’s first name, department name and city.

Select d.department_name, l.city, first_name 
from departments d, locations l , employees e 
where (d.manager_id=e.employee_id) and (l.location_id=d.location_id);

 Output:-

SQL statement to give details from more than one table by using concept of JOINS, where tables are joined on the common attributes in both tables at a time, here location id is common in both locations table  and department table so they are joined, and third table is also joined with any common attribute in any of the two tables, and we get our result.

4. Show country name, department name, & city

Select l.city, c.country_name, d.department_name 
from locations l, countries c, departments d 
where (d.locations_id=l.location_id) and (c.country_id=l.country_id);

 Output:-

SQL statements to find the average of salary for every job title, here avg() is an inbuilt function that computes the average of values in the attributes.

5. Show job title with the average salary of employees having respective job title

Select job_title, avg(salary) from jobs natural join employees group by job_title;

 Output:-

SQL statement that joins three tables, done by joining two table at a time using common attribute and then joining the third table using any one of other two table using common columns. Here three tables departments, employees and country are joined using same way as described.

6. Show employee name, department and country in which he is working.

Select d.department_name, l.city, e.first_name 
from departments d, locations l, employees e 
where (d.manager_id=e.employee_id) and (l.location_is=d.location_id);

 Output:-

SQL statement finding average salary and showing whether the department is commissioned and counted the record satisfying the query entered.

7. Show department name, average salary and count of the number of employees with the commission within the department.

Select departments_name, avg(salary), count(commission_pct) 
from departments join employees using(department_id) 
group by department_name;

 Output:-

SQL statements to join two tables and finding a particular record by giving specific value from the column of any of the two table, for example here the value of employee id is given and the result displayed is from other table related to that specific employee id.

8. Show the city of employee whose employee ID is 186.

Select city 
from locations join departments using (location_id) join employees using (department_id) 
where employee_id=186;

 Output:-

SQL statements for finding a particular ranked record among the list, for example here from the list of distinct values in salary the third highest salary is displayed, where the third record is displayed by 2 because indexing of list is started from 0.

9. Show the third highest salary among all employees.

Select salary from employees main 
where 2= (Select count (distinct salary) from employees where salary>main.salary);

 Output:-

To select the highest value  among all in the particular category, for example here details of employees on the basis of highest salary from the category departments  are displayed

10. Show the details of employees drawing the highest salary in the department

Select * from employees e 
where salary= (Select max(salary) from employees where department_id = e.department_id);

 Output:-