CREATE TABLE LOCATION $($

Location$\_$ID INT PRIMARY KEY,

City VARCHAR$(50)$

$)$;

INSERT INTO LOCATION $($Location$\_$ID$,$ City$)$

VALUES $(122,$ 'New Yo

Simple Queries:

$1.$ List all the employee details.

$2.$ List all the department details.

$3.$ List all job details.

$4.$ List all the locations.

$5.$ List out the First Name, Last Name, Salary, Commission for all Employees.

$6.$ List out the Employee ID$,$ Last Name, Department ID for all employees and

alias

Employee ID as $"$ID of the Employee", Last Name as "Name of the

Employee", Department ID as "Dep$\_$id$".$

$7.$ List out the annual salary of the employees with their names only.

WHERE Condition:

$1.$ List the details about "Smith".

$2.$ List out the employees who are working in department $20.$

$3.$ List out the employees who are earning salaries between $3000$ and$4500.$

$4.$ List out the employees who are working in department $10$ or $20.$

$5.$ Find out the employees who are not working in department $10$ or $30.$

$6.$ List out the employees whose name starts with $\text{'}$S$\text{'}.$

SQL Certification Training

Contact us: support@intellipaat.com $/$ Copyright Intellipaat $/$ All rights reserved

$7.$ List out the employees whose name starts with $\text{'}$S$\text{'}$ and ends with $\text{'}$H$\text{'}.$

$8.$ List out the employees whose name length is $4$ and start with $\text{'}$S$\text{'}.$

$9.$ List out employees who are working in department $10$ and draw salaries more

than $3500.$

$10.$ List out the employees who are not receiving commission.

ORDER BY Clause:

$1.$ List out the Employee ID and Last Name in ascending order based on the

Employee ID$.$

$2.$ List out the Employee ID and Name in descending order based on salary.

$3.$ List out the employee details according to their Last Name in ascending$-$order.

$4.$ List out the employee details according to their Last Name in ascending

order and then Department ID in descending order.

GROUP BY and HAVING Clause:

$1.$ How many employees are in different departments in the organization?

$2.$ List out the department wise maximum salary, minimum salary and

average salary of the employees.

$3.$ List out the job wise maximum salary, minimum salary and average

salary of the employees.

$4.$ List out the number of employees who joined each month in ascendingorder.

$5.$ List out the number of employees for each month and year in

ascending order based on the year and month.

$6.$ List out the Department ID having at least four employees.

$7.$ How many employees joined in the month of January?

$8.$ How many employees joined in the month of January or September?

$9.$ How many employees joined in $1985?$

$10.$ How many employees joined each month in $1985?$

$11.$ How many employees joined in March $1985?$

$12.$ Which is the Department ID having greater than or equal to $3$ employees

joining in April $1985?$

SQL Certification Training

Contact us: support@intellipaat.com $/$ Copyright Intellipaat $/$ All rights reserved

Joins:

$1.$ List out employees with their department names.

$2.$ Display employees with their designations.

$3.$ Display the employees with their department names and regional groups.

$4.$ How many employees are working in different departments? Display with

department names.

$5.$ How many employees are working in the sales department?

$6.$ Which is the department having greater than or equal to $5$

employees? Display the department names in ascending

order.

$7.$ How many jobs are there in the organization? Display with designations.

$8.$ How many employees are working in "New York"?

$9.$ Display the employee details with salary grades. Use conditional statement to

create a grade column.

$10.$ List out the number of employees grade wise. Use conditional statementto

create a grade column.

$11.$ Display the employee salary grades and the number of employees

between $2000$ to $5000$ range of salary.

$12.$ Display all employees in sales or operation departments.

SET Operators:

$1.$ List out the distinct jobs in sales and accounting departments.

$2.$ List out all the jobs in sales and accounting departments.

$3.$ List out the common jobs in research and accounting

departments in ascending order.

Subqueries:

$1.$ Display the employees list who got the maximum salary.

SQL Certification Training

Contact us: support@intellipaat.com $/$ Copyright Intellipaat $/$ All rights reserved

$2.$ Display the employees who are working in the sales department.

$3.$ Display the employees who are working as 'Clerk'.

$4.$ Display the list of employees who are living in "New York".

$5.$ Find out the number of employees working in the sales department.

$6.$ Update the salaries of employees who are working as clerks on the basis of

$10\%.$

$7.$ Delete the employees who are working in the accounting department.

$8.$ Display the second highest salary drawing employee details.

$9.$ Display the nth highest salary drawing employee details.

$10.$ List out the employees who earn more than every employee in department $30.$

$11.$ List out the employees who earn more than the lowest salary in

department. Find out whose department has no employees.

$12.$ Find out which department has no employees.

$13.$ Find out the employees who earn greater than the average salary for

their department.