Tech Studio Online

Write a SQL query to fetch the second last record from employee table. Table Name : EMPLOYEE Approach : Using window function sort the data in descending order based on employee id. Provide a row number to each of the record and fetch the record having row number as 2. Solution-1 select * from employees where employee_id in( select MAX(employee_id)-1 as employee_id from employees ); — there is issue in this query, lets say employee_id 205 is not present then this query will fail Solution-2 –PERFECT SOLUTION USING WINDOW FUNCTION WITHOUT PARTION BY SELECT * FROM( SELECT ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS RN, EMP.* FROM employees EMP) WHERE RN=2;

Prepare Sample Data To Practice SQL Skill. Sample Table – Worker WORKER_ID FIRST_NAME LAST_NAME SALARY JOINING_DATE DEPARTMENT 001 Monika Arora 100000 2014-02-20 09:00:00 HR 002 Niharika Verma 80000 2014-06-11 09:00:00 Admin 003 Vishal Singhal 300000 2014-02-20 09:00:00 HR 004 Amitabh Singh 500000 2014-02-20 09:00:00 Admin 005 Vivek Bhati 500000 2014-06-11 09:00:00 Admin 006 Vipul Diwan 200000 2014-06-11 09:00:00 Account 007 Satish Kumar 75000 2014-01-20 09:00:00 Account 008 Geetika Chauhan 90000 2014-04-11 09:00:00 Admin Sample Table – Bonus WORKER_REF_ID BONUS_DATE BONUS_AMOUNT 1 2016-02-20 00:00:00 5000 2 2016-06-11 00:00:00 3000 3 2016-02-20 00:00:00 4000 1 2016-02-20 00:00:00 4500 2 2016-06-11 00:00:00 3500 Sample Table – Title WORKER_REF_ID WORKER_TITLE AFFECTED_FROM 1 Manager 2016-02-20 00:00:00 2 Executive 2016-06-11 00:00:00 8 Executive 2016-06-11 00:00:00 5 Manager 2016-06-11 00:00:00 4 Asst. Manager 2016-06-11 00:00:00 7 Executive 2016-06-11 00:00:00 6 Lead 2016-06-11 00:00:

1.What is query to Find Second Highest Salary Of Employee? | Find 2nd Highest salary examples Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary; Alternative: select min(salary)from(select distinct salary from emp order by salary desc)where rownum<=2; 2.Query to find Duplicate Records in Table? Select * from Employee a where rowid <>( select max(rowid) from Employee b where a.Employee_num=b.Employee_num); 3.Query to Find Monthly Salary of Employee if Annual salary is Given? select Employee_name,Salary/12 as ‘Monthly Salary’ from employee; 4. What is the Query to fetch first record from Employee table? Select * from Employee where rownum=1; 5.What is Query to display first 5 Records from Employee table?(90% asked Complex SQL Queries Examples) Answer: Select * from Employee where Rownum <= 5; 6.What is Query to display last 5 Records from Employee table?(90% asked Complex SQL Queries Examples) Answer: S

Q1) What is the difference between PL SQL and SQL? Ans.  PL SQL vs SQL Comparison SQL PL/SQL Execution Single command at a time Block of code Application Source of data to be displayed Application created by data acquired by SQL Structures include DDL and DML based queries and commands Includes procedures, functions, etc Recommended while Performing CRUD operations on data Creating applications to display data obtained using SQL Compatibility with each other SQL can be embedded into PL/SQL PL/SQL cant be embedded in SQL Q2) What is SQL and also describe types of SQL statements? Ans:  SQL stands for  Structured Query Language . SQL is a language used to communicate with the server to access, manipulate, and control data. There are 5 different types of SQL statements. Data Retrieval: SELECT Data Manipulation Language (DML) : INSERT, UPDATE, DELETE, MERGE Data Definition Language (DDL):  CREATE, ALTER, DROP, RENAME, TRUNCATE. Transaction Control Statements:  COMMIT, ROLLBACK, SAVEPOINT D