============================================================================
1 ) Write a query to return the employee number, first name and last name of all the employees. Order the employees in the alphabetical order of their first names.
sol 1 )
use upgrad;
select employeeNumber, firstName, lastName from employees order by firstName;
2 ) Write a query to return the email ids of all the employees in the increasing order of their employee numbers.
sol 2 )
use upgrad;
select email as emailID from employees order by employeeNumber;
3 ) Employees with a Specific Office Code
Write a query to retrieve the first names and last names of all the employees having an office code of 4. Arrange them in the alphabetical order of their last names.
sol 3 ) use upgrad;
select firstName, lastName from employees where officeCode = 4 order by lastName;
4 ) All Employee Details
Write a query to retrieve the entire data of all the employees from the employees table. Arrange them in the alphabetical order of their first names. Please click on the sample output to view it clearly.
sol 4 ) use upgrad;
select * from employees order by firstName;
5 ) Filtered Employees
Write a query to retrieve the email addresses of all the employees who have an office code of 6 and report to employees with code '1088'. Arrange these employees in the increasing order of their employee numbers.
sol 5 ) use upgrad;
select email from employees where officeCode = 6 and reportsTo = 1088 order by employeeNumber;
--------------------------------------------------------------------------------------------------------------------------------------------
1 ) More Filtered Employees
Write a query to retrieve the email addresses of all the employees who have an office code of 6 or report to employees with employee number '1088'. Arrange them in the reverse alphabetical order of their first names.
sol 1) use upgrad;
select email from employees where officeCode = 6 or reportsTo = 1088 order by firstName desc;
2 ) Employees from Specific Office Codes
Write a query to retrieve all the details of all the employees who have an office code from 2 to 4. Arrange them in the alphabetical order of their first names. Please click on the sample output to view it clearly.
sol 2 )
use upgrad;
select * from employees where officeCode between 2 and 4 order by firstName;
3 ) Employees with Odd-Numbered Office Codes
Write a query to retrieve the extensions and office codes of all the employees having an odd-numbered office code. Arrange them in the alphabetical order of their first names.
sol 3 ) use upgrad;
select extension, officeCode from employees where officeCode % 2 = 1 order by firstName;
4 ) Employees with Even-Numbered Office Codes
Write a query to retrieve the extensions and office codes of all the employees having an even-numbered office code. Arrange them in the alphabetical order of their first names.
4 )
use upgrad;
select extension, officeCode from employees where MOD(officeCode , 2) = 0 order by firstName;
5 ) Lucky Employees
Write a query to retrieve all the details of employees who don't report to anyone. Arrange them in the increasing order of their employee numbers. Please click on the sample output to view it clearly.
sol 5 ) use upgrad;
select * from employees where reportsTo is null order by employeeNumber;
1 ) Non Sales Representatives
Write a query to retrieve the first names, last names and job titles of all those employees who are not sales representatives. Arrange them in the alphabetical order of their job titles. If two employees have the same job title, arrange them in the alphabetical order of their first names.
sol 1 ) use upgrad;
select firstName, lastName, jobTitle from employees where jobTitle != 'Sales Rep' order by jobTitle, firstName;
2 ) Selected Extensions Write a query to retrieve the list of employee numbers and extensions of all the employees who have an extension ending with '1'. Arrange the list in the increasing order of the employee numbers.
sol 2 ) use upgrad;
select employeeNumber, extension
from employees
where extension like '%1'
order by employeeNumber;
3 ) Select Employees
Write a query to retrieve the first and last names of all employees who have the substring 'er' in their last names. List the output in the alphabetical order of the first names of the employees.
sol 3 ) use upgrad;
select firstName, lastName from employees where lastName like '%er%' order by firstName;
4 ) Last Names
Write a query to retrieve the last names of all employees and arrange them in the alphabetical order.
sol 4 ) use upgrad;
select lastName from employees order by lastName;
1 ) Employees with Specific Employee Numbers Write a query to retrieve the employee numbers, first and last names having employee numbers beginning with 1 and ending with 2. Arrange them in the alphabetical order of their first names.
Note that all employee numbers consist of 4 digits.
sol 1 )
use upgrad;
select employeeNumber,firstName, lastName from employees
where employeeNumber like '1%2'
order by firstName;
2 ) Product Codes with Patterns
Write a query to retrieve the product codes and prices of all products having the pattern '_32' in their product codes. Arrange these products in the decreasing order of their prices.
sol 2 )
use upgrad;
select productCode, priceEach from orderdetails where productcode like '__32' order by priceEach desc;
3 ) First Names of Employees
Write a query to list the first names of the employees in the reverse alphabetical order.
sol 3 ) use upgrad;
select firstName from employees order by firstName desc;
4 ) Minimum and Maximum Order Amounts
Write a query to retrieve the minimum and maximum order amounts among the products.
sol 4 )
use upgrad;
select min(priceEach * quantityOrdered) as minAmount, max(priceEach * quantityOrdered) as maxAmount
from orderdetails;
1 ) Total and Average Order Amounts Write a query to retrieve the total amount received from all orders, as well as the average amount.
sol 1 ) use upgrad;
select sum(priceEach * quantityOrdered) as totalAmount, avg(priceEach * quantityOrdered) as avgAmount
from orderdetails;
2 ) Rounded Total and Average Order Amounts Write a query to retrieve the total amount received from all orders, as well as the average amount and round the answers to the nearest integer.
sol 2 )use upgrad; select round(sum(quantityordered * priceeach)) as totalAmount, round(avg(quantityordered * priceeach)) as avgAmount from orderdetails;
3 ) Employee Strength vs. Office Code Write a query to retrieve the list of number of employees corresponding to each office code. Arrange the number of employees in the increasing order of the office codes. 
sol 3 ) use upgrad;
select officeCode, count(*) as no_Of_Employees from employees group by officeCode order by officeCode;
4 ) Rounded Total Order Amounts Write a query to retrieve the rounded total order amounts for each order line number. Arrange these amounts in the decreasing order.
sol 4 ) use upgrad;
select round(sum(priceEach * quantityOrdered)) as roundedAmount, orderLineNumber from orderdetails group by orderLineNumber order by roundedAmount desc;
5 ) Orders of Less Than One Lakh
Write a query to retrieve order line numbers having rounded line-wise order amounts of less than 1,00,000. Arrange them in the decreasing order of their amounts.
sol 5 )
use upgrad;
select round(sum(priceEach * quantityOrdered)) as roundedAmount, orderLineNumber
from orderdetails
group by orderLineNumber
having roundedAmount < 100000
order by roundedAmount desc;