Others Interview Questions and Answers (265) - Page 12

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

265 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

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