Useful Queries to know for HANA SQL Script Development. In this session I will discuss about few sample queries that can help fo individual script developer for script project.

Query 1 :

How to get top 10 salary data in sql query : 

/*--- Solition 1 -------------*/

SELECT *
FROM table
WHERE 
(
  sal IN 
  (
    SELECT TOP (10) sal
    FROM table as table1
    GROUP BY sal
    ORDER BY sal DESC
  )
)

/*--- Solition 2 -------------*/

select  * from employee where salary in (select distinct top 10 salary from employee order by salary desc)

/*--- Solition 3 -------------*/

select distinct salary from employee order by salary desc limit 10;

Query 2:  To get 2nd max salary –

select max(salary) from employee where salary not in(select max(salary) from employee);

/* Secoend Alternative */

select MAX(Salary) from Employee

WHERE Salary <> (select MAX(Salary) from Employee )

This is same as option 1 but we are using <> instead of NOT IN.

Query 3: Get even number of Record from a table –

SELECT *

FROM (SELECT rownum, ID, Name

FROM STUDENT)

WHERE MOD(rownum,2)=0

Query 4: Get Odd number of Record from a table –

SELECT *

FROM (SELECT rownum, ID, Name

FROM STUDENT)

WHERE MOD(rownum,2)=1

Query 5: find records in Table C that are not in Table D :

SELECT * FROM TableC

MINUS

SELECT * FROM TableD

/ Alternate  */

SELECT * FROM Table_A Where student_name NOT IN (SELECT student_name FROM TableD)

 

Query 6 :  Find Student that have same name and email.

SELECT name, email, COUNT(*)

FROM STUDENT

GROUP BY name, email

HAVING

COUNT(*) > 1

Query 7 :  Write SQL query to get the nth highest salary among all Employees..

We can use following sub query approach for this:

SELECT  *

FROM Employee emp1

WHERE (N-1) = (

SELECT COUNT(DISTINCT(emp2.salary))

FROM Employee emp2

WHERE emp2.salary > emp1.salary)

Query 8 :  Query to get the Quarter from date.

SELECT TO_CHAR(TO_DATE('3/31/2016', 'MM/DD/YYYY'), 'Q')

AS quarter

FROM DUAL

Query 9 : Query to find STUDENT with duplicate email.

SELECT name, COUNT(email)

FROM STUDENT

GROUP BY email

HAVING ( COUNT(email) > 1 )

Query 10 : Difference between DELETE and TRUNCATE.

1. DELETE is a Data Manipulation Language (DML) command. TRUNCATE is a Data Definition Language (DDL) command.
2. Number of Rows: We can use DELETE command to remove one or more rows from a table. TRUNCATE command will remove all the rows from a table.
3. WHERE clause: DELETE command provides support for WHERE clause that can be used to filter the data that we want to delete. TRUNCATE command can only delete all the rows. There is no WHERE clause in TRUNCATE command.
4.  Commit: After DELETE command we have to issue COMMIT or ROLLBACK command to confirm our changes. After TRUNCATE command there is no need to run COMMIT. Changes done by TRUNCATE command can not be rolled back.

Query 11 : Difference between UNION and UNION ALL :

• Main difference between UNION and UNION ALL is that UNION removes duplicate records, but UNION ALL does not remove duplicate records.
• Performance of UNION ALL is considered better than UNION, since UNION ALL does not require additional work of removing duplicates.

Query 12 :  Delete duplicate rows in a table.

DELETE FROM student a WHERE  a.rowid >  ANY (SELECT  b.rowid FROM STUDENT b
 WHERE a.column_1 = b.column_1 AND    a.column_2 = b.column_2);