1. To find the total number of rows in a Table
select rows from sysindexes where id=object_id('employees') and indid <2
2. Select the details of 3 max salaried employees from employee table.
SELECT TOP 3 empid, salary FROM employee ORDER BY salary DESC
3.Display all managers from the table. (manager id is same as emp id)
SELECT empname FROM employee WHERE (empid IN (SELECT DISTINCT mgrid FROM employee))
4. Write a Select statement to list the Employee Name, Manager Name under a particular manager?
SELECT e1.empname AS EmpName, e2.empname AS ManagerName
FROM Employee e1 INNER JOIN
Employee e2 ON e1.mgrid = e2.empid
ORDER BY e2.mgrid
5. How to know how many tables contains empno as a column in a database?
SELECT COUNT(*) AS Counter FROM syscolumns WHERE (name = 'empno')
6.How to delete the rows which are duplicate - yourtable(name1,age1) (don’t delete both duplicate records).
SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0
7.How to find 6th highest salary
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
8.Write a query to convert all the letters in a word to upper case
SELECT UPPER('test')
9. Write a SQL Query to find first day of month?
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay
10. Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.
SELECT CEILING (7.1)
11.How to list all the tables in a particular database?
sp_help
|
No responses found. Be the first to respond and make money from revenue sharing program.
|