SQL Assignment – NEW

                                      SQL Assignment – NEW  SQL Assignment – NEW

Questions :

1 Display the “FIRST_NAME” from Employee table using the alias name as Employee_name.

 2 Display “LAST_NAME” from Employee table in upper case. 

3 Display unique values of DEPARTMENT from EMPLOYEE table. 

4 Display the first three characters of LAST_NAME from EMPLOYEE table. 

5 Display the unique values of DEPARTMENT from EMPLOYEE table and prints its length. 

6 Display the FIRST_NAME and LAST_NAME from EMPLOYEE table into a single column AS FULL_NAME. a space char should separate them. 

7 DISPLAY all EMPLOYEE details from the employee table order by FIRST_NAME Ascending. 

8. Display all EMPLOYEE details order by FIRST_NAME Ascending and DEPARTMENT Descending. 

9 Display details for EMPLOYEE with the first name as “VEENA” and “KARAN” from EMPLOYEE table. 

10 Display details of EMPLOYEE with DEPARTMENT name as “Admin”. 

11 DISPLAY details of the EMPLOYEES whose FIRST_NAME contains ‘V’. 

12 DISPLAY details of the EMPLOYEES whose SALARY lies between 100000 and 500000.

 13 Display details of the employees who have joined in Feb-2020. 

14 Display employee names with salaries >= 50000 and <= 100000. 

15 Display the number of Employees for each department in the descenting order. 

16 DISPLAY details of the EMPLOYEES who are also Managers. 

17 DISPLAY duplicate records having matching data in some fields of a table. 

18 Display only odd rows from a table. 

19 Clone a new table from EMPLOYEE table. 

20 DISPLAY the TOP 2 highest salary from a table. 

21. DISPLAY the list of employees with the same salary.

 22 Display the second highest salary from a table.

 23 Display the first 50% records from a table. 

24. Display the departments that have less than 4 people in it. 

25. Display all departments along with the number of people in there. 

26 Display the name of employees having the highest salary in each department.

 27 Display the names of employees who earn the highest salary.

 28 Display the average salaries for each department 

29 display the name of the employee who has got maximum bonus 

30 Display the first name and title of all the employees

Answer :

---ARNAB BOSE---

SELECT * FROM employee_bonus

SELECT * FROM employee_table

SELECT * FROM employee_title_table

---Q1---

select first_name as 'Employee_name' from employee_table

---Q2---

select upper(last_name)  as ' Uppercase_firstname 'from employee_table

---Q3---

select distinct(department ) as 'unique_values' from employee_table

---Q4---

select SUBSTRING( last_name,1,3) from employee_table 

---Q5---

SELECT DISTINCT(DEPARTMENT) AS 'DISTINCT_VALUES'  ,LEN(DEPARTMENT)  AS 'LENGTH ' FROM EMPLOYEE_TABLE

---Q6---

SELECT FIRST_NAME, LAST_NAME,CONCAT(FIRST_NAME, '  ' , LAST_NAME) AS ' FULL_NAME' FROM employee_table 

---Q7---

SELECT * FROM EMPLOYEE_TABLE ORDER BY first_name

---Q8---

 SELECT * FROM EMPLOYEE_TABLE ORDER BY DEPARTMENT DESC,first_name ASC

 ---ARNAB BOSE---

---Q9---

  select * from employee_table where first_name = 'veena' or  first_name = 'karan'

---Q10---

  select * from employee_table where department = 'admin'

---Q11---

  select * from employee_table where first_name like  '%V%'

---Q12---

   select * from employee_table where salary between 100000 and 500000

---Q13---

    select   first_name from employee_table group by first_name ,joining_date having datepart(YEAR ,joining_date ) ='2020 ' and DATEPART(month,joining_date) = '2'

---Q14---

  select first_name, last_name from employee_table where salary >=50000 and salary <=100000

  ---ARNAB BOSE---

---Q15---

  select count(department) TOTAL,department  

  from employee_table group by department order by department desc

---Q16---

select UPPER (CONCAT(FIRST_NAME, '  ' , LAST_NAME)) NAME, joining_date 

from employee_table e 

inner join employee_title_table e1

on e.employee_id = e1.employee_id 

group by CONCAT(FIRST_NAME, '  ' , LAST_NAME), joining_date ,

employee_title,

e.employee_id 

having employee_title = 'manager' 

---Q17---

SELECT FIRST_NAME, LAST_NAME, COUNT(*) AS Duplicate_Count

FROM employee_table

GROUP BY FIRST_NAME, LAST_NAME

HAVING COUNT(*) > 1

---Q18---

SELECT * FROM employee_table  WHERE employee_id %2 != 0

---Q19---

SELECT *

INTO EMPLOYEE_Clone

FROM employee_table

SELECT * FROM EMPLOYEE_Clone

---ARNAB BOSE---

---Q20--

SELECT TOP 2 * FROM EMPLOYEE_Clone ORDER BY SALARY DESC 

---Q21---

SELECT s1.first_name, s1.last_name, s1.Salary

FROM employee_table s1

INNER JOIN employee_table  s2 ON s1.Salary = s2.Salary AND s1.first_name <> s2.first_name

---Q22---

SELECT TOP 1 Salary

FROM (

    SELECT DISTINCT TOP 2 Salary

    FROM employee_table

    ORDER BY Salary DESC

) AS SecondHighestSalary

ORDER BY Salary ASC

---Q23--

select top 50 percent * from employee_table

---Q24---

SELECT DEPARTMENT, COUNT(*)  Employee_Count

FROM employee_table

GROUP BY DEPARTMENT

HAVING COUNT(*) < 4

---Q25---

select  department ,count(employee_id) Total

from employee_table  group by department

---Q26---

SELECT MAX(e.SALARY)  MaxSalary, e.DEPARTMENT

FROM employee_table e

INNER JOIN employee_title_table e1 ON e.employee_id = e1.employee_id

GROUP BY e.DEPARTMENT

---OR---

WITH RankedEmployees AS (

    SELECT

        Department,

        FIRST_NAME,

        LAST_NAME,

        SALARY,

        ROW_NUMBER() OVER (PARTITION BY Department ORDER BY SALARY DESC) AS Rank

    FROM employee_table

)

SELECT Department, FIRST_NAME, LAST_NAME, SALARY

FROM RankedEmployees

WHERE Rank = 1

---OR---

SELECT e.DEPARTMENT, E.FIRST_NAME, E.LAST_NAME, E.SALARY

FROM employee_table E

JOIN (

    SELECT DEPARTMENT, MAX(SALARY) AS MAX_SALARY

    FROM employee_table

    GROUP BY DEPARTMENT

) AS D_MAX ON E.DEPARTMENT = D_MAX.DEPARTMENT AND E.SALARY = D_MAX.MAX_SALARY

---Q27---

SELECT FIRST_NAME, LAST_NAME, SALARY

FROM employee_table

WHERE SALARY = (SELECT MAX(SALARY) FROM employee_table)

---Q28---

SELECT DEPARTMENT, AVG(SALARY)  Avg_Salary

FROM employee_table

GROUP BY DEPARTMENT

---Q29---

SELECT E.FIRST_NAME, E.LAST_NAME, EB.bonus_amount

FROM employee_table E

JOIN Employee_Bonus EB ON E.EMPLOYEE_ID = EB.EMPLOYEE_ID

WHERE EB.bonus_amount = (SELECT MAX(bonus_amount) FROM Employee_Bonus)

---Q30---

SELECT UPPER (E.FIRST_NAME ) NAME , UPPER (ET.employee_title) TITLE

FROM employee_table  E

JOIN employee_title_table ET ON E.EMPLOYEE_ID = ET.EMPLOYEE_ID

---ARNAB BOSE---

Kindly Noted : We provide the table data in images format .