Practice Set-3

MY SQL DATABASE QUERIES

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

1. Show the details of employees drawing the lowest salary in the department.

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

 Output:-

SQL to select record under a range i.e between a starting value and ending value, here we selected records of employees who joined between the given two years.

2. Show the first name and join date of the employees who joined between last three years (2006-2008) in order.

Select first_name, hire_date from employees 
where extract(year from hire_date) between 2006 and 2008 order by hire_date asc;

 Output:-

SQL statement to display queried result in an order, here we displayed distinct years in which employees are hired and displayed result in an order that is ascending order.

3. Show the distinct year in which the employees are hired orderly.

Select distinct (extract (year from hire_date)) as hired_year 
from employees order by hired_year asc;

 Output:-

SQL statement to find details on the particular list of options, here we displayed people from the information that they are either Marketing manager or Marketing representative. Such list of option for search is put in IN() function.

4. Show first name and join date of the employees who is either Marketing Manager or Marketing Representative

Select first_name, hire_date from employees where job_id in (‘MK_MAN’,’MK_REP’);

 Output:-

SQL statements to find the employees with certain criteria, here details of employees having salary lesser than 7000 are displayed

5. Show first name, salary for employees with salary less than 7000.

Select first_name, salary from employees where salary<7000;

 Output:-

SQL statement that involves displaying result which is an operation like addition, subtraction, multiplication etc. as customised column using 'AS' keyword, here the difference between min salary and max salary is displayed as 'salary_diff' which is not an actual column in any table but a customised column.

6. Show job Title, with the difference between min. and max. Salaries for jobs.

Select job_title, max_salary – min_salary as salary_diff from jobs;

 Output:-

SQL statement to round the figure up to tens, hundreds, or thousands places is done by using round() function, for example here we round the salary up to 1000.

7. Show employee first name, salary, and round the salary to thousand.

Select first_name, round(salary,-3), salary from employees;

 Output:-

SQL statement to display details from a table in order of the job title i.e the values of job title column will be displayed in ascending order from a to z

8. Show details of jobs in the increasing order of the title.

Select * from jobs order by job_title asc;

 Output:-

SQL statement to display or operate on values of column which are null i.e they contain no value, and they are not left empty but having value that is NULL.

9. Show details of the employees where commission pct. is null and salary in the range 5000 to 7000.

Select * from employees where commission_pct is null and salary between 5000 and 7000;

 Output:-

SQL statement to convert the case of character from uppercase to lowercase or lowercase to uppercase, here we displayed the first name and last name in lowercase.

10. Show first name and last name after converting the character of each name to lower case.

Select lower(first_name),lower(last_name) from employees;

 Output:-