[go: up one dir, main page]
Scribd
Upload
7 views3 pages

SQL Query Solutions Section 2

The document provides SQL query solutions for various employee-related data retrieval tasks. It includes queries to filter employees based on commission, name characteristics, hire dates, and salary calculations. Additionally, it demonstrates sorting and formatting techniques for displaying employee information.

Uploaded by

bishramoraon896
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views3 pages

SQL Query Solutions Section 2

The document provides SQL query solutions for various employee-related data retrieval tasks. It includes queries to filter employees based on commission, name characteristics, hire dates, and salary calculations. Additionally, it demonstrates sorting and formatting techniques for displaying employee information.

Uploaded by

bishramoraon896
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL Query Solutions - Section II

SECTION-II Solutions:

1. Query to display the Name, Salary, and Commission for all the employees who earn commission:

SELECT Ename AS Name, Salary, Commission

FROM Employee

WHERE Commission IS NOT NULL;

2. Sort the data in descending order of Salary and Commission:

SELECT *

FROM Employee

ORDER BY Salary DESC, Commission DESC;

3. Query to display Name of all the employees where the third letter of their name is 'A':

SELECT Ename AS Name

FROM Employee

WHERE SUBSTRING(Ename, 3, 1) = 'A';

4. Query to display Name of all employees either who have two 'R's or have two 'A's in their name

and are either in Dept No = 30 or their Manager's Employee No = 7788:

SELECT Ename AS Name

FROM Employee

WHERE (Ename LIKE '%R%R%' OR Ename LIKE '%A%A%')

AND (Dno = 30 OR Manager = '7788');


5. Query to display Name, Salary, and Commission for all employees whose Commission Amount is

14 greater than their Salary increased by 5%:

SELECT Ename AS Name, Salary, Commission

FROM Employee

WHERE Commission = (Salary * 1.05) + 14;

6. Query to display the Current Date:

SELECT CURRENT_DATE() AS Current_Date;

7. Query to display Name, Hire Date, and Salary Review Date, which is the 1st Monday after six

months of employment:

SELECT Ename AS Name,

Hire_date,

DATE_ADD(Hire_date, INTERVAL 6 MONTH) + INTERVAL (9 -

DAYOFWEEK(DATE_ADD(Hire_date, INTERVAL 6 MONTH))) DAY AS Salary_Review_Date

FROM Employee;

8. Query to display Name and calculate the number of months between today and the date each

employee was hired:

SELECT Ename AS Name,

TIMESTAMPDIFF(MONTH, Hire_date, CURRENT_DATE()) AS Months_Worked

FROM Employee;

9. Query to display the following for each employee: <E-Name> earns <Salary> monthly but wants

<3 * Current Salary>. Label the column as Dream_Salary:

SELECT CONCAT(Ename, ' earns ', Salary, ' monthly but wants ', (3 * Salary)) AS Dream_Salary

FROM Employee;
10. Query to display Name with the 1st letter capitalized and all other letters lowercase, and the

length of their name for all employees whose name starts with 'J', 'A', or 'M':

SELECT CONCAT(UCASE(LEFT(Ename, 1)), LCASE(SUBSTRING(Ename, 2))) AS

Formatted_Name,

LENGTH(Ename) AS Name_Length

FROM Employee

WHERE Ename LIKE 'J%' OR Ename LIKE 'A%' OR Ename LIKE 'M%';

You might also like