Practice Set-1

MY SQL DATABASE QUERIES

SQL statement to select details of the employee by using their particular attributes. These attribute can be anything but must be related to employee somewhere.

1. Show the information of employees with ID 127 or 170.

Select * from employees where employee_id in (127,170);

 Output:-

To select details of job title under specified criteria like records to be in a range, for example:-

Here only those job titles are shown which are greater than 8000. Other operation can also be implemented like between, lesser than etc.

2. Show the details of jobs where the min. salary is greater than or equal to 8000.

Select * from jobs where min_salary>=8000;

 Output:-

To search for information that is to be operated on/by dates, it can be achieved simply as we use where clause for looking a record by values, here dated are also valued using which you can search records.

3. Show the information of employees joined after 1st April 2005.

Select * from employees where hire_date > ‘1-APR-2005’;

 Output:-

Some searching use a logic to get an output, for example like here simple logic that current

that is system date from which if hire date is subtracted will give the experience of the

employee.

4. Show the first name and experience of all the employees.

 Select first_name, hire_date, (sysdate – hire_date) as experience from employees;

 Output:-

To search for records not using exact values because you have forgotten that value, but you

remember some part of that value, then we can use concept of “like” where you enter

the part of value in the order how it is the part of value.

5. Show names of employees where the first name or last name starts with A.

Select first_name, last_name from employees where first_name like ‘A%’ or last_name like ‘A%’;

 Output:-

Here first name and last name are filtered on basis of only where names start by letter M and as % is used after that so it ignores whatever be after letter M.

6. Show details of employees where the first name and last name starts with M.

Select first_name, last_name from employees where first_name like ‘M%’ and last_name like ‘M%’;

 Output:-

For using search where values are part of dates like date, month or year that are extracted from dated. Example here year is extracted from hire date and then it is searched by giving value to that extracted part of date.

7. Show first name of employees who joined in 2007.

Select first_name from employees where extract (year from hire_date) = 2007;

 Output:-

Arithmetic operations can be done on dated, where addition on dated gives a new date and subtraction gives the differences of dated that is no of days. Multiplication and division operation on dated are meaningless here.

8. Show first name and date when they were given their first salary.

Select first_name, hire_date+30 as first_salary from employees;

 Output:-

To display the records of employees who joined in the month of august, we displayed it by extracting months from dates and giving value of month august i.e 08.

9. Show employees who joined in month of August.

Select first_name from employees where extract (month from hire_date)=08;

 Output:-

To display the count of records is done by using count() function that will count only the records that matches the query entered by user.

10. Count number of employees joined after 10th of the month.

Select count (employee_id) from employees where extract (day from hire_date) > 10;

 Output:-