Others Interview Questions and Answers (265) - Page 12

DB2 - Display the LAST_NAME concatenated with DEPARTMENT_ID, separated by a comma and space, and name the column as ‘Employee - Dept’.

SELECT CONCAT( LAST_NAME ,
CONCAT(‘ , ‘ , DEPARTMENT_ID))“Employee – Dept”
FROM EMPLOYEES
DB2 - Display the employee LAST_NAME, DEPARTMENT_ID and HIRE_DATE of employees hired between May 1st 2003 and March 30th 2005. Order the query in ascending order by HIRE_DATE

SELECT LAST_NAME, DEPARTMENT_ID, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE ‘2003-05-01’ AND ‘2005-03-30’
ORDER BY HIRE_DATE
DB2 - Display the LAST_NAME and HIRE_DATE of every employee who was hired in 2006

SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE YEAR(HIRE_DATE)=2006


SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE LIKE '2006%'
DB2 - Display the DEPARTMENT_ID &DEPARTMENT_NAME from DEPARTMENTS table which does not have a manager

SELECT DEPARTMENT_ID , DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE MANAGER_ID IS NULL
DB2 - Display the LAST_NAME of all employees where the first letter of the name is ‘S’

SELECT LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE ‘S%’


or


SELECT LAST_NAME
FROM EMPLOYEES
WHERE LEFT(LAST_NAME,1) =‘S’
DB2 - Display the LAST_NAME of all employees where the third letter of the name is ‘A’

SELECT LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE ‘__A%’
DB2 - Display the highest, lowest, sum, and average salary of all employees. Label the columns maximum, minimum, sum, and average, respectively, for each DEPARTMENT_ID

SELECT DEPARTMENT_ID, MAX(SALARY) MAXIMUM, MIN(SALARY) MINIMUM, SUM(SALARY) SUM, AVG(SALARY) AVARAGE
FROM EMPLOYEES
Group BY DEPARTMENT_ID
DB2 - Write a query to display the number of people working in dept. 100

SELECT COUNT(*) “COUNT OF EMPLOYEES IN 100TH DEPARTMENT”
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100
DB2 - Display the average salaries of each DEPARTMENT_ID, include only the DEPARTMENT_ID s having more than $8000 of average salary.

SELECT department_id, AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(salary) > 8000
DB2 - Difference between DROP & TRUNCATE

• Drop deletes the whole structure of table by means it removes the space for that object from database


• Truncate retains the table structure and removes the all records

Example:
--Truncate table Item
TRUNCATE TABLE Item IMMEDIATELY;
GO
SELECT * FROM Item;

We will get the table structure with no records

--DROP TABLE
DROP TABLE Item
GO
SELECT * FROM Item
GO
We will get the error due to inexistence of object called Item.
DB2 - How FECH FIRST clause works. Give an example.

The FETCH FIRST clause sets a maximum number of rows that can be retrieved. FETCH FIRST specifies that only integer rows should be made available to be retrieved, regardless of how many rows there might be in the result table when this clause is not specified. An attempt to fetch beyond integer rows is handled the same way as normal end of data. The value of integer must be a positive integer (not zero). The default is 1.
Ex-
SELECT FIRSTNME
FROM EMP
ORDER BY SALARY DESC
FETCH FIRST 5 ROWS ONLY;
DB2 - Write down the syntax order & execution order of SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY& FETCH FIRST clauses

SYNTAX ORDER
============
SELECT FROM WHERE GROUP BY
HAVING
ORDER BY FETCH FIRST


EXECUTION ORDER
===============
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
FETCH FIRST
DB2 - Write a query to display employee name who gets highest salary

SELECT FIRST_NAME
FROM EMPLOYEES
WHERE SALARY = (SELECT MAX(SALARY)
FROM EMPLOYEES
)
DB2 - Display minimum salary from each department

SELECT DEPARTMENT_ID, MIN(SALARY) “MIN SAL FOR EACH DEPARTMENT”
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
DB2 - Display number of employees in each department, if the department contains more than 5 employees

SELECT DEPARTMENT_ID,COUNT(*) AS “NUMBER OF EMPLOYEES”
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>5
DB2 - Write a query to get the employee name who joined the company recently (newest employee)

SELECT FIRST_NAME AS “Recently joined employeeS”
FROM (SELECT FIRST_NAME,
RANK() OVER( ORDER BY hire_date DESC) RNK FROM EMPLOYEES) WHERE RNK =1
DB2 - Write a query to get the employee name who joined the company recently (newest employee)

SELECT FIRST_NAME AS “Recently joined employeeS”
FROM (SELECT FIRST_NAME,
RANK() OVER( ORDER BY hire_date DESC) RNK FROM EMPLOYEES) WHERE RNK =1
DB2 - Find the count of departments having average salary greater than maximum salary of department 50

SELECT COUNT (DEPARTMENT_ID) FROM
(SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING AVG (SALARY) > (SELECT MAX (SALARY) FROM EMPLOYEES
WHERE DEPARTMENT_ID=50)
)
DB2 - Find the NAMEs that have an even number of letters in them

SELECT LAST_NAME FROM EMPLOYEES
WHERE MOD (LENGTH (LAST_NAME), 2)=0
DB2 - Fetch the current date and time in the following format 24/APRIL/12 17:30:22:2000

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'DD/MONTH/YY HH24: MI:SS: NNNNNN’) FROM SYSIBM.DUAL
Found this useful, bookmark this page to the blog or social networking websites. Page copy protected against web site content infringement by Copyscape

 Interview Questions and Answers Categories