SAP HANA: SELECT Statements

HANA SQL Restricting and Sorting data: [9 exercises with solution]

1. Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000.

CODE:

SELECT "FIRST_NAME", "LAST_NAME", "SALARY","DEPARTMENT_ID"

FROM "KABIL_PRACTICE"."EMPLOYEE_SAMPLE"

WHERE salary NOT BETWEEN 10000 AND 15000

AND "DEPARTMENT_ID" IN (30, 100);

RESULT:

2. Write a query to display the name (first_name, last_name) and department ID of all employees in departments 30 or 100 in ascending order.

CODE:

SELECT  "FIRST_NAME", "LAST_NAME","DEPARTMENT_ID"

FROM "KABIL_PRACTICE"."EMPLOYEE_SAMPLE"

WHERE "DEPARTMENT_ID" IN (30, 100)

ORDER BY  "DEPARTMENT_ID"  ASC;

RESULT:

3. Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100.

CODE:

SELECT "FIRST_NAME", "LAST_NAME", "SALARY","DEPARTMENT_ID"

FROM "KABIL_PRACTICE"."EMPLOYEE_SAMPLE"

WHERE "SALARY" NOT BETWEEN 10000 AND 15000

AND "DEPARTMENT_ID" IN (30, 100);

RESULT:

4. Write a query to display the name (first_name, last_name) and hire date for all employees who were hired in 1987.

CODE:

SELECT "FIRST_NAME", "LAST_NAME","HIRE_DATE"

FROM "KABIL_PRACTICE"."EMPLOYEE_SAMPLE"

WHERE YEAR("HIRE_DATE")  LIKE '1987%';

RESULT:

5. Write a query to display the first_name of all employees who have both "b" and "c" in their first name.

CODE:

SELECT "FIRST_NAME"

FROM "KABIL_PRACTICE"."EMPLOYEE_SAMPLE"

WHERE "FIRST_NAME" LIKE '%B%'

OR  "FIRST_NAME"LIKE '%C%';

RESULT:

6. Write a query to display the last name, job, and salary for all employees whose job is that of a Programmer or a Shipping Clerk, and whose salary is not equal to $4,500, $10,000, or $15,000.

CODE:

SELECT "LAST_NAME","JOB_ID","SALARY"

FROM "KABIL_PRACTICE"."EMPLOYEE_SAMPLE"

WHERE "JOB_ID" IN ('IT_PROG', 'SH_CLERK')

AND "SALARY"NOT IN (4500,10000, 15000);

RESULT:

7. Write a query to display the last name of employees whose names have exactly 6 characters.

CODE:

SELECT "LAST_NAME" FROM "KABIL_PRACTICE"."EMPLOYEE_SAMPLE" WHERE "LAST_NAME" LIKE '______';

RESULT:

8. Write a query to display the last name of employees having 'e' as the third character.

CODE:

SELECT "LAST_NAME" FROM "KABIL_PRACTICE"."EMPLOYEE_SAMPLE" WHERE "LAST_NAME" LIKE '__e';

RESULT:

9. Write a query to display the name (first_name, last_name), salary and PF (15% of salary) of all employees.

CODE:

SELECT "FIRST_NAME", "LAST_NAME", "SALARY", "SALARY"*.15 as "PF" from "KABIL_PRACTICE"."EMPLOYEE_SAMPLE";

RESULT: