Berhinet

  Q12. Find the join date of employees   if they  join after 2 weeks of the hire date? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Consider below table (Sql_data) as a source data. select * from Sql_data; Table 1. Source Data -- Find the join date of employees select      empid,  FirstName,  LastName, DOB, Hire_date, DATEADD(WK, 2, Hire_date) as Join_date, Salary from Sql_data; Table 2. Join Date Explanation :  DATEADD  function adds a number to a datepart and returns a modified  value based on a   specified  datepart  (Year, Month, Week, Day …..etc...).  Syntax: DATEADD (datepart, NumberToAdd, Date) Parameters: datepart -  It is part of the date to which the dateadd () function will add a number. It can be Year(YY, YYYY), Quarter(QQ, Q), month(MM,M), day(DD, D), …..etc....  In our case, we are going to use Week (WK) NumberToAdd -  It is an ...

 Q11. Find the age of employees from the provided table using SQL? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Consider below table (Sql_data) as a source data. select * from Sql_data; Table 1. Source Data -- Find the age of employees select      empid,  FirstName,  LastName, DOB,  DATEDIFF(YY, DOB, GETDATE()) as Age from Sql_data;                                                                      Table 2. Age of Employees Explanation : DATEDIFF function is used to return the date difference between two dates according to the date part specified (Year, Month, Day …..etc...).  Syntax: DATEDIFF(datepart, Date1, Date2) Parameters: datepart - Used to get the time difference between the two dates. It can be Year(YY, YYYY), Quarter(QQ , Q)...

 Q10.  Write an SQL query to find the maximum, minimum, and average salary of the employees. N.B: I am using the following: Database -> MSSQL SQL Dialect -> T-SQL Consider below an employment  table   as a source data. Select  * from employment; Table 1. Employment Table Ans:  select  max(salary) as MaxSalary, min(salary) as MinSalary, avg(salary) as AvgSalary from employment; Table 2. A ggregate Function of SQL

  Q9. How to find the third highest salary from the table below using SQL? N.B: I am using the following: Database -> MSSQL SQL Dialect -> T-SQL Consider below an employment  table   as a source data. Table 1. Employment Table Ans: select top 1 salary from ( select top 3 salary from employment order by salary desc) as TopThree order by salary asc; Table 2. 3rd Highest Salary Explanation : To find the third highest salary from the provided  table, first I need to find the first three rows with highest salary ordered in descending order. Look at the the query and result below. select top 3 salary from employment order by salary desc; Table 3. First 3 highest salaries Now, we have the 3rd highest salary on the last row. In order to have the 3rd highest salary as an output, we have to order the above query (name as alias TopThree) in ascending order and select the first row using Top 1. 

  Q8. How to find duplicate rows using SQL? N.B:  I am using the following: Database -> MSSQL SQL Dialect -> T-SQL I have a table given below: Table1. Employees Table To find duplicate records from the table, we are going to use to use aggregate functions such as count and having. select  EmpId, count(*) as Count from employees group by EmpId having count(*) >=2;    Table 2. Duplicate Rows

  Q7. How to calculate cumulative sum using SQL? N.B: I am using the following: Database -> MSSQL SQL Dialect -> T-SQL Consider below an employment  table   as a source data. -- Create an employment table create table employment( EmpId int, FirstName varchar(20), LastName varchar(20), Salary int ); -- Insert values to the employment table insert into employment(EmpId, FirstName, LastName, Salary) Values (100, 'Genet', 'Mathew', 40000), (101, 'Mike', 'Mcarthy', 80000), (102, 'Teddy', 'Arthur', 60000), (103, 'Abrham', 'Zerai', 70000), (104, 'Aman','Mike', 65000), (105, 'Tedros','Haile', 50000), (106, 'Abi', 'Jacob', 60000), (107, 'Bruno', 'Yonus', 75000), (108, 'Mathew', 'Samuel',60000), (109, 'Rodas','Keyun', 65000), (110, 'Mat', 'Joseph', 100000); Select  * from employment; T...

  Q6. How to find the employee with the second MAX salary using SQL? N.B: I am using the following: Database -> MSSQL SQL Dialect -> T-SQL Consider below employment  table   as a source data. -- Create an employment table create table employment( EmpId int, FirstName varchar(20), LastName varchar(20), Salary int ); -- Insert values to the employment table insert into employment(EmpId, FirstName, LastName, Salary) Values (100, 'Genet', 'Mathew', 40000), (101, 'Mike', 'Mcarthy', 80000), (102, 'Teddy', 'Arthur', 60000), (103, 'Abrham', 'Zerai', 70000), (104, 'Aman','Mike', 65000), (105, 'Tedros','Haile', 50000), (106, 'Abi', 'Jacob', 60000), (107, 'Bruno', 'Yonus', 75000), (108, 'Mathew', 'Samuel',60000), (109, 'Rodas','Keyun', 65000), (110, 'Mat', 'Joseph', 100000); Select  * f...

   Q5. How to read last 5 records from a table using SQL? N.B: I am using the following: Database -> MSSQL SQL Dialect -> T-SQL Consider below  employment  table as a source data. -- Create an  employment  table create table employment( EmpId int, FirstName varchar(20), LastName varchar(20), Salary int ); -- Insert values to the  employment  table insert into employment(EmpId, FirstName, LastName, Salary) Values (100, 'Genet', 'Mathew', 40000), (101, 'Mike', 'Mcarthy', 80000), (102, 'Teddy', 'Arthur', 60000), (103, 'Abrham', 'Zerai', 70000), (104, 'Aman','Mike', 65000), (105, 'Tedros','Haile', 50000), (106, 'Abi', 'Jacob', 60000), (107, 'Bruno', 'Yonus', 75000), (108, 'Mathew', 'Samuel',60000), (109, 'Rodas','Keyun', 65000), (110, 'Mat', 'Joseph', 100000); Select  * from empl...

 Q4. How to read top 5 records from a table using SQL? N.B: I am using the following: Database -> MSSQL SQL Dialect -> T-SQL Consider below  employment  table as a source data. -- Create an  employment  table create table employment( EmpId int, FirstName varchar(20), LastName varchar(20), Salary int ); -- Insert values to the  employment  table insert into employment(EmpId, FirstName, LastName, Salary) Values (100, 'Genet', 'Mathew', 40000), (101, 'Mike', 'Mcarthy', 80000), (102, 'Teddy', 'Arthur', 60000), (103, 'Abrham', 'Zerai', 70000), (104, 'Aman','Mike', 65000), (105, 'Tedros','Haile', 50000), (106, 'Abi', 'Jacob', 60000), (107, 'Bruno', 'Yonus', 75000), (108, 'Mathew', 'Samuel',60000), (109, 'Rodas','Keyun', 65000), (110, 'Mat', 'Joseph', 100000); Select  * from employm...

 Q3. How to select duplicate records from a table using SQL? N.B: I am using the following: Database -> MSSQL SQL Dialect -> T-SQL I have a table given below: Table1. Employee Table To find duplicate records from the table, we need to use a row_number function to uniquely identify each row.  select Empid, Name, Salary, ROW_NUMBER() over(partition by empid, Name,  Salary order by empid) as Row_Num from employee                                                                 Table2. Row_Number Function If  you see the Row_Num column above, it  is uniquely identifying each row. Now, we need to fetch a duplicate records having Row_Num grater than one.  Let's see the SQL query: With cte as (select Empid, Name, Salary, ROW_NUMBER() over(partition...

 Q2: How to select unique values in SQL? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Below is an employee table given. It has 3 fields and 7 records.                                                       Table1: Employee Table To find unique values of the table, I am going to use three different ways:  1. Using distinct clause select distinct EmpId, Name, Salary from employee; Table2: Distinct Clause Method 2. Using Group by (Without using distinct keyword) select EmpId, Name, Salary from employee group by EmpId, Name, Salary; Table3. Group By Method 3. Using Row_Number Function select Empid, Name, Salary , ROW_NUMBER() over(partition by empid, Name,  Salary order by empid) as Row_Num from employee; Result: Table4. Row_Number Function Row_number is used to give a unique number for each row as you can see from ta...

 Q1. How to find the total number of records (number of rows) in SQL? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL   I have a created an employee table as follows: -- Create a SQL database Create database SQL; use sql; -- Create an employee table create table employee( EmpId int, Name varchar(20), Salary int ); -- Insert values to the employee table insert into employee (EmpId, Name, Salary) Values (100, 'Genet', 40000), (100, 'Genet', 40000), (101, 'Mike', 80000), (101, 'Mike', 80000), (102, 'Teddy', 60000), (102, 'Teddy', 60000), (103, 'Mat', 100000);                                                           Table1: Employee Table Ans : I have a couple of options to find the total records of the given table: using count or without count function. 1. Count () Function select count (*) a...