Answer Posted / babi

--1)select all the Employees who does not have phone?

SELECT * FROM EMPLOY WHERE EMPID NOT IN(SELECT EMPID FROM PHONE)


--2)Dispaly all managers from table(Manager id is same as Empid)

SELECT * FROM EMPLOY E WHERE E.EMPID=E.MGRID


--3)How to know How many tables contain Empno as a column in database?

SELECT name FROM sys.objects WHERE OBJECT_ID IN (SELECT object_id FROM sys.columns WHERE NAME like 'EMPNO')

--4)Find duplicate rows in a table or
-- if we have table with one column which has many records which are not distinct. How to find out the distinct values from that column and number of times its repeated


SELECT * FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY EMPNAME ORDER BY EMPID ) AS NUMS FROM EMPLOY ) A WHERE NUMS>1

CREATE TABLE ONE(ID INT)
INSERT INTO ONE VALUES('1'),('2'),('3'),('4'),('5'),('1'),('2'),('3'),('4'),('5'),('1'),('2'),('3'),('4'),('5')
SELECT ID,COUNT(*) FROM ONE group by ID having COUNT(*)>1



--5) How to delete the rows which are duplicate?(Don't remove both duplicate records.)


WITH TA
AS
(
SELECT EMPID,EMPNAME,ROW_NUMBER() OVER(PARTITION BY EMPNAME ORDER BY SAL DESC ) AS Nums
FROM EMPLOY
)
DELETE FROM TA WHERE Nums>1

WITH AT
AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY PHONENUMBER ORDER BY EMPID DESC)AS PHONENUMS FROM PHONE
)
DELETE FROM AT WHERE PHONENUMS>1

--6)how to Find 6th highest sal ?


SELECT MIN(SAL) FROM EMPLOY WHERE SAL IN(
SELECT DISTINCT TOP(6) SAL FROM EMPLOY ORDER BY SAL DESC
)
SELECT TOP 1 SAL FROM (SELECT DISTINCT TOP 6 SAL FROM EMPLOY ORDER BY SAL DESC) EMPLOY ORDER BY SAL

SELECT * FROM
( SELECT DISTINCT TOP 6 SAL , DENSE_RANK() OVER(ORDER BY SAL DESC ) AS RANKS FROM EMPLOY)
EMPLOY WHERE RANKS=6

Do we have vacancy for HR RECRUITER ?

1365

---

what is placement paper?

2137

---

what you will do when you struck in traffic ?

1034

---

speak 1min on topic Travelling,memorable moment ,memorable day?

1725

---

What kind of Issues/bugs arise in ETL projects? I would like to know few issues (critical to somewhat bugs ) normally occur in ETL process especially when testing? Domains can be banking, retail or any.

1408

---

---

what is difference between uv and visible spectroscopy and how to differentiate the drug compounds in uv visible region

1578

---

A frog wants to climb a pole which is 15 feet in height, frog can jump 3 feet every minute after which he rests for a minute where he slips down 2 feet. How much time will the frog take to reach the top of the pole?

583

---

my question is : reverse a string in COBOL program (please write a code asap) input file: 1234abcdef output file: 8765_____ abc*$#1___ 1_2_3_4__ cdef____ghi h_k_z_a__ a_z_k_h___ ihg____ghi 4_3_2_1___ 1#$*cba__ ______5678 fedcb4321

1283

---

aptitude

680

---

Sks Microfinance

966

---

What were the occupations of the people who lived within the Mauryan Empire?

568

---

why didn't get placed so far?

1181

---

Danlaw placement questions and answers

1338

---

Hi.. Guys Im pranitha, presently im working in a organisation & im look for job at MNC's like DELL, Delloitte, Bank of America, Genpect, etc., I have completed B.com . Pls help me...

1130

---

what is the use of air in gc

1422

---