Finding 2nd Maximum Salary in a employee Table
Here N = nth Highest Salary eg. 3rd Highest salary : N=3 .
SELECT ename,sal from Employee e1 where
N-1 = (SELECT COUNT(DISTINCT sal)from Employee e2 where e2.sal > e1.sal) what is ACID property?
Having vs Where Clause in SQL
The difference between the having and where clause in SQL is that the where clause cannot be used with aggregates, but the having clause can.
The where clause works on row’s data, not on aggregated data.
Subsets of SQL:
Data Definition Language (DDL) – It allows you to perform various operations on the database such as CREATE, ALTER, and DELETE objects.
Data Manipulation Language(DML) – It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
Data Control Language(DCL) – It allows you to control access to the database. Example – Grant, Revoke access permissions.
OLAP vs OLTP
OLTP stands for online transaction processing, whereas OLAP stands for online analytical processing. OLTP is an online database modification system, whereas OLAP is an online database query response system
Deleting Duplicate ID from a table:
DELETE FROM table WHERE ID IN (
SELECT
ID, COUNT(ID)
FROM table
GROUP BY ID
HAVING
COUNT (ID) > 1);
Joins:
DELETE Vs TRUNCATE
Delete command is used to delete a row in a table.
Truncate is used to delete all the rows from a table. You can rollback data after using Delete statement. You cannot rollback data. It is a DML command.
Truncate is a DDL command. It is slower than truncate statement. It is faster.
Denormalization:
Denormalization refers to a technique which is used to access data from higher to lower forms of a database. It helps the database managers to increase the performance of the entire infrastructure as it introduces redundancy into a table. It adds the redundant data into a table by incorporating database queries that combine data from various tables into a single table.
Aggregate functions:
AVG(): returns the average value from specified columns.
COUNT(): returns the number of table rows, including rows with null values.
MAX(): returns the largest value among the group.
MIN(): returns the smallest value among the group.
SUM(): returns the total summed values(non-null) of the specified column.
FIRST(): returns the first value of an expression.
LAST(): returns the last value of an expression.
Creating empty tables with the same structure as another table?
SELECT * INTO Students_copy FROM Students WHERE 1 = 2;
Write an SQL query to fetch the position of a given character(s) in a field.
SELECT INSTR(FullName, 'Snow') FROM EmployeeDetails;
Write an SQL query to display both the EmpId and ManagerId together
SELECT CONCAT(EmpId, ManagerId) as NewId FROM EmployeeDetails;
Write an SQL query to fetch project-wise count of employees sorted by project’s count in descending order
SELECT Project, count(EmpId) EmpProjectCount FROM EmployeeSalary GROUP BY Project ORDER BY EmpProjectCount DESC;
Write an SQL query to remove duplicates from a table without using a temporary table.
DELETE E1 FROM EmployeeDetails E1 INNER JOIN EmployeeDetails E2 WHERE E1.EmpId > E2.EmpId AND E1.FullName = E2.FullName AND E1.ManagerId = E2.ManagerId AND E1.DateOfJoining = E2.DateOfJoining AND E1.City = E2.City;
Write an SQL query to fetch top n records?
SELECT * FROM EmployeeSalary ORDER BY Salary DESC LIMIT N;
What is Query to display last 5 Records from Employee table?
Select * from Employee e where rownum <=5
union
select * from (Select * from Employee e order by rowid desc) where rownum <=5;
How to get distinct records from the table without using distinct keyword.
select * from Employee a where rowid = (select max(rowid) from Employee b where a.Employee_no=b.Employee_no);
Display following in the query:
*
**
***
SELECT lpad (‘*’, ROWNUM,’*’) FROM Student WHERE ROWNUM <4;
How to display 1 to 100 Numbers with query
Select level from dual connect by level <=100;
Comments