local admin rights required for software developer

• Locally site publish and test 
• Developers toolsets are often updated very regularly. Graphics libraries, code helpers, visual studio updates; they end up having updates coming out almost weekly that need to be installed. Desktop support usually gets very tired of getting 20 tickets every week to go install updated software on all the dev machines so they just give the devs admin rights to do it themselves.
• Debugging / Testing tools sometimes need admin rights to be able to function. No admin access means developers can’t do their job of debugging code. Managers don't like that.
• For configuring TNS files
•  

Data control in asp.net

Supported Funcationalities
Control	Paging	Data Grouping	Provide Flexible Layout	Update,Delete	Insert	Sorting
ListView	supported	supported	supported	supported	supported	supported
GridView	supported	Not supported	Not Supported	supported	Not Supported	supported
DataList	Not supported	supported	supported	Not supported	Not supported	Not supported
Repeater	Not supported	Not supported	supported	Not supported	Not supported	Not supported

* Supported: means that it's provided out of the box without any custom code or hacks.

* Not Supported: means that it's not provided out of the box by the control but it could be possible to implement it using custom code \ hacks.

The GridView : it supports paging but it doesn't provide a flexible layout , since its mainly used to display the data in a table based layout.And If we looked at data inserting , the Gridview doesn't have a built in support for inserting data( since it doesn't call the insert method of it underlying data source when you click on a button with a CommadName set to "Insert" ).

The DataList : it support data grouping ( through its RepeatColumns property) , but it doesn't have a built in support for paging,inserting ,deleting , updating the data. and if you looked at its laout , you will find that by default  the datalist renders as html table and you will have to set its flowLayout to "Flow" to stop that behaviour.

The Repeater control : you will find that it provides a flexible layout but it doesn't support data grouping ,inserting,deleting , updating  and paging through the data .

Summary :

The ListView control was added to provide a rich data control that can support all the required functionalities at the same time , so now you can easily display a fully customizable layout that supports Grouping,paging , inserting , deleting , updating and sorting the data.

OAuth - Open Authentication

OAuth is temporary credentials with which third party user can do a designated task only with in a short span of time.


http://pratapreddypilaka.blogspot.in/2012/08/oauth-basics.html#more

Compare multiple value .

class Program
    {
        static void Main(string[] args)
        {
            test("");
            test("10");
            test("20");
            test("30");
            test("40");
            Console.ReadKey();
        }
        public static void test(string username)
        {
            if (!(username == "10" || username == "20"))
                Console.WriteLine("wrong value " + username);
            else
                Console.WriteLine("right value" + username);
        }
    }

Cluster computing + Grid Computing = Cloud Computing

A computer cluster consists of a set of loosely or tightly connected computers that work together so that, in many respects, they can be viewed as a single system. Unlike grid computers, computer clusters have each node set to perform the same task, controlled and scheduled by software.


Grid computing is the collection of computer resources from multiple locations to reach a common goal. The grid can be thought of as a distributed system with non-interactive workloads that involve a large number of files. Grid computing is distinguished from conventional high performance computing systems such as cluster computing in that grid computers have each node set to perform a different task/application. Grid computers also tend to be more heterogeneous and geographically dispersed (thus not physically coupled) than cluster computers.^[1] Although a single grid can be dedicated to a particular application, commonly a grid is used for a variety of purposes. Grids are often constructed with general-purpose grid middleware software libraries. Grid sizes can be quite large

Team Foundation Server (TFS)

• TFS Server
• TFS Client (VS,browser)

Branching and merging 

TFS branching and merging : https://msdn.microsoft.com/en-us/library/bb668955.aspx

Copied from http://blog.tfsserver.com/a-straightforward-guide-to-branching/

My general recommendation is up to 3 branches:

• The Main Line – This is what gets deployed
• A Quality Branch (optional) – Typically known as the Dev line
• Production Support Branch (optional) – Needed when production breaks so often that we know we’re going to need an emergency fix in the middle of our sprint.

Do NOT Branch by Version

I see this all the time. Every time a team releases a new version of their build to production they spin off a new branch. When I audit these scenarios I’ll see something that looks a lot like this:

My first question is always, “When’s the last time anyone used Version 1.0?” If the answer is anything other than within the last week, I know we have a code organization problem. If you want to snapshot your code during a deployment, add a label. You don’t need a branch.

The Main Line

Everybody has a Main line, even if they didn’t call it that. This is where code that is going to be deployed resides. For many teams, this may be all you need. Your developers work in the Main line, they deploy from the Main line. What’s more simple than that?

If you only have a Main line, don’t be embarrassed or ashamed. It doesn’t mean you’ve done anything wrong. The Main line is efficient. It means you’re verifying your code works before you check it in. It means that your code is good enough that you don’t need to worry about having to support an emergency deployment. You know you can branch by label if an emergency happens, otherwise you’ll just solve the problem during your next release.

If you’re just starting on a project and you have a small team of close developers. This is where you should begin. Don’t add layers until you need to.

The Dev Line

One day you may decide that Main is just too precious. We need to protect Main from those other nasty developers. So you’ll decide to add a Dev line. This is what I call branching for quality.

When you branch for quality, you make all the changes in your Dev line. You want developers to check in early and often to avoid merge issues, but you also want to provide an additional quality gate before it makes it into the Main line. A few examples of such quality gates:

• Code Reviews
• Unit Testing
• Gated Check In or Continuous Integration Builds

This model is especially useful for contractors and vendor based development teams. By locking down your Main line and having the contractors work in the Dev line, it provides you an opportunity to review any code and ensure it passes your quality gates before making it into the Main line. Merging is extremely simple, as you’re only doing a one way merge from Dev into Main. No conflicts necessary.

The Release Line

If you often find yourself working on emergency break fixes outside of your normal release schedule, instead of creating a branch for each release, create a single Release branch. This code should always be what exists in production today. By having a single Release branch, merging stays simple and there’s never any confusion as to what code is live.

Day to day, your developers would still work in either your Dev or Main line. When you release to prod, you’ll do a merge from Main into Release, and hopefully it won’t need to be used. But if that day comes when you need to fix something and fast, the Release line will be there for you. Make the fix in the release line, test it, deploy it, and save the day. After you pulse returns to normal, merge the fix into Main and if necessary from Main into Dev.

Working in your Release line should not be common. If you’re in there more than a day or two, you’re probably not fixing a bug, you’re adding a new feature. I’ve seen projects where the client agreed to one thing in the sprint. You start working on the sprint, you’re two weeks in and now the client wants a new feature and he wants it now. The problem, you still have 2+ weeks left. So should you just create the feature in your Release line? NO! If your client can’t go two weeks without deciding he needs something else, then you don’t need more branches, you need shorter sprints.

Check In Early, Check In Often

The best way to avoid merge conflicts is to have developers check in their code as often as possible. Not only will this avoid merge issues down the road, but it keeps your code safe, which is a large reason you’re using source control in the first place. The longer a developer goes without checking in code, the more work and more money that’s wasted if something were to happen to their system. I’ve seen a developer leave a company who hadn’t checked in their code in a week, basically throwing away a week’s worth of work.

I recommend checking in at the end of each task and at the end of each day. You should never lose more than a days worth of work. If the code you’re working on at the end of the day would destroy the build, use shelving. Shelving is basically a private check in. The code is stored on the server, but it is unique to you. No worries about breaking the build, no worries about losing your work. Win-Win!

Drop all Database Object

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO




Drop all Store Procedure

declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec('drop procedure [' + @procName + ']')
    fetch next from cur into @procName
end
close cur
deallocate cur

Drop all tables

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table [' + TABLE_SCHEMA + '].['+ TABLE_NAME + ']'
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

select *
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

JQuery

jQuery is a JavaScript Library.

jQuery Syntax Examples

$(this).hide()  Demonstrates the jQuery hide() method, hiding the current HTML element.

$("#test").hide() Demonstrates the jQuery hide() method, hiding the element with id="test".

$("p").hide()  Demonstrates the jQuery hide() method, hiding all <p> elements.

$(".test").hide() Demonstrates the jQuery hide() method, hiding all elements with class="test".

 

jQuery Syntax
The jQuery syntax is tailor made for selecting HTML elements and perform some action on the element(s).

 

Basic syntax is: $(selector).action()

•A dollar sign to define jQuery
•A (selector) to "query (or find)" HTML elements
•A jQuery action() to be performed on the element(s)

 

Syntax Description
$(this) Selects the current HTML element
$("p#intro:first") Selects the first <p> element with id="intro"
$(".intro") Selects all elements with class="intro"
$("#intro") Selects the first element with id="intro"
$("ul li:first") Selects the first <li> element of the first <ul>
$("ul li:first-child") Selects the first <li> element of every <ul>
$("[href]") Selects all elements with an href attribute
$("[href$='.jpg']") Selects all elements with an href attribute that ends with ".jpg"
$("[href='#']") Selects all elements with an href value equal to "#"
$("[href!='#']") Selects all elements with an href value NOT equal to "#"
$("div#intro .head") Selects all elements with class="head" inside a <div> element with id="intro"

 

jQuery Name Conflicts
jQuery uses the $ sign as a shortcut for jQuery.

Some other JavaScript libraries also use the dollar sign for their functions.

 

Event Method Description
$(document).ready(function)   Binds a function to the ready event of a document
(when the document is finished loading)
$(selector).click(function) Triggers, or binds a function to the click event of selected elements
$(selector).dblclick(function) Triggers, or binds a function to the double click event of selected elements
$(selector).focus(function) Triggers, or binds a function to the focus event of selected elements
$(selector).mouseover(function) Triggers, or binds a function to the mouseover event of selected elements

 

jQuery Effects
Here are some examples of effect functions in jQuery:

Function Description
$(selector).hide() Hide selected elements
$(selector).show() Show selected elements
$(selector).toggle() Toggle (between hide and show) selected elements
$(selector).slideDown() Slide-down (show) selected elements
$(selector).slideUp() Slide-up (hide) selected elements
$(selector).slideToggle() Toggle slide-up and slide-down of selected elements
$(selector).fadeIn() Fade in selected elements
$(selector).fadeOut() Fade out selected elements
$(selector).fadeTo() Fade out selected elements to a given opacity
$(selector).animate() Run a custom animation on selected elements

A callback function is executed after the current animation is 100% finished.

--------------------------------------------------------------------------------

jQuery Callback Functions
JavaScript statements are executed line by line. However, with animations, the next line of code can be run even though the animation is not finished. This can create errors.

To prevent this, you can create a callback function.

A callback function is executed after the current animation (effect) is finished.

$("#div2").width("300px");

jQuery CSS Methods From this Page:

CSS Properties Description
$(selector).css(name) Get the style property value of the first matched element
$(selector).css(name,value) Set the value of one style property for matched elements
$(selector).css({properties}) Set multiple style properties for matched elements
$(selector).height(value) Set the height of matched elements
$(selector).width(value) Set the width of matched elements

jQuery AJAX Methods From This Page:

Request Description
$(selector).load(url,data,callback) Load remote data into selected elements
$.ajax(options) Load remote data into an XMLHttpRequest object

SQL Query Interview Questions and Answers

http://www.interviewquestionspdf.com/2014/07/sql-queries-interview-questions-answers.html

SQL Optimization

SQL Statements are used to retrieve data from the database. We can get same results by writing different SQL queries. But use of the best query is important when performance is considered. So you need to SQL query tuning based on the requirement. Here is the list of queries which we use regularly and how these SQL queries can be optimized for better performance.

Actual column names instead of *

The SQL query becomes faster if you use the actual column names in SELECT statement instead of *.

Write the query as

SELECT P_Id, FirstName, LastName, Address, City
FROM Persons

Instead of

SELECT *
FROM Persons

HAVING clause is like a filter

HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.

Write the query as

SELECT FirstName, COUNT(FirstName) FROM Persons WHERE FirstName <> 'John' AND FirstName <> 'Kate' GROUP BY FirstName

Instead of

SELECT FirstName, COUNT(FirstName) FROM Persons GROUP BY FistName HAVING FirstName <> 'John' AND FirstName <> 'Kate'

The number of subquery block in query

Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.

Write the query as

SELECT Name FROM Employees WHERE (Salary, Age ) = (SELECT MAX(Salary), MAX(Age) FROM EmployeeDetails) AND Dept = 'Electronics'

Instead of

SELECT Name FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM EmployeeDetails) AND Age = (SELECT MAX(Age) FROM EmployeeDetails) AND Dept = 'Electronics';

Efficient use of EXISTS and IN

Use operator EXISTS, IN and table joins appropriately in your query.

Usually IN has the slowest performance;IN is efficient when most of the filter criteria is in the subquery;EXISTS is efficient when most of the filter criteria is in the main query.

Write the query as

SELECT * FROM Products WHERE EXISTS (select * from OrderItems WHERE Product_Id = Product_Id_p)

Instead of

SELECT * FROM Products WHERE Product_Id IN (SELECT Product_Id FROM OrderItems)

Using EXISTS instead of DISTINCT

Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationships.

Write the query as

SELECT D.Dept_Id, D.Dept FROM Dept.D WHERE EXISTS (SELECT 'X' FROM Employees 

WHERE E.Dept = D.Dept)

Instead of

SELECT DISTINCT D.Dept_Id, D.Dept FROM Dept.D, Employees WHERE E.Dept = E.Dept

UNION ALL in place of UNION

Try to use UNION ALL in place of UNION.

Write the query as

SELECT Id, FirstName FROM Students UNION ALL SELECT Id, FirstName FROM SportsTeam

Instead of

SELECT Id, FistName, Subject FROM Students UNION SELECT Id, FirstName FROM SportsTeam

Conditions in WHERE clause

Be careful while using conditions in WHERE clause.

Write the query as

SELECT Id, FirstName, Age FROM Persons WHERE Age > 25

Instead of

SELECT Id, FirstName, Age FROM Persons WHERE Age <> 25

Write the query as

SELECT Id, FirstName, Age FROM Persons WHERE FirtName LIKE 'Chan%'

Instead of

SELECT Id, FirstName, Age FROM Persons WHERE SUBSTR(FirstName, 1, 3) = 'Cha'

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT Id, Name, Salary FROM Employees WHERE Salary < 25000

Instead of

SELECT Id, Name, Salary FROM Employees WHERE Salary + 10000 < 35000

Use DECODE to avoid the scanning of same rows

Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY.

Write the query as

SELECT Id FROM Employees WHERE Name LIKE 'Ramesh%' AND Location = 'Bangalore'

Instead of

SELECT DECODE(Location, 'Bangalore', Id, NULL) Id FROM Employees WHERE Name LIKE 'Ramesh%';

Storing large binary objects

To store large binary objects, first place them in the file system and add the file path in the database.

General SQL Standard Rules

To write queries which provide efficient performance follow the general SQL standard rules:

Use single case for all SQL verbs;Begin all SQL verbs on a new line;Separate all words with a single space;Right or left aligning verbs within the initial SQL verb.

SQL Query to find second highest salary of Employee


SELECT max(salary) FROM Employee 
WHERE salary NOT IN (SELECT max(salary) FROM Employee);

SELECT max(salary) FROM Employee 
WHERE salary < (SELECT max(salary) FROM Employee);

SELECT TOP 1 salary 
FROM ( SELECT TOP 2 salary FROM employees ORDER BY salary DESC) AS emp 
ORDER BY salary ASC


SQL Query to find Max Salary from each department.

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.


Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?

SELECT * 
FROM emp a 
WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)

--nth Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

--3rd Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

row number ----
SELECT name, sal, row_number() over(order by sal desc) rownum_by_sal
FROM EMPLOYEE o

;with T as
(
 select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
 from employee_test1
)

delete
from T
where rank > 1

SELECT contact_id,
CASE
  WHEN website_id = 1 THEN 'TechOnTheNet.com'
  WHEN website_id = 2 THEN 'CheckYourMath.com'
  ELSE 'BigActivities.com'
END
FROM contacts;

SELECT contact_id,
CASE website_id
  WHEN 1 THEN 'TechOnTheNet.com'
  WHEN 2 THEN 'CheckYourMath.com'
END
FROM contacts;

Oracle 

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
2. To select ALTERNATE records from a table. (ODD NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
3. Find the 3rd MAX salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
5. Select FIRST n records from a table.select * from emp where rownum <= &n;
6. Select LAST n records from a tableselect * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
7. List dept no., Dept name for all the departments in which there are no employees in the department.select * from dept where deptno not in (select deptno from emp);  
   alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
   altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
8. How to get 3 Max salaries ?select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
9. How to get 3 Min salaries ?select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
10. How to get nth max salaries ?
    select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
11. Select DISTINCT RECORDS from emp table.select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
12. How to delete duplicate rows in a table?delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
13. Count of number of employees in  department  wise.select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
14.  Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
    select ename,sal/12 as monthlysal from emp;
15. Select all record from emp table where deptno =10 or 40.
    select * from emp where deptno=30 or deptno=10;
16. Select all record from emp table where deptno=30 and sal>1500.
    select * from emp where deptno=30 and sal>1500;
17. Select  all record  from emp where job not in SALESMAN  or CLERK.
    select * from emp where job not in ('SALESMAN','CLERK');
18. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
    select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
19. Select all records where ename starts with ‘S’ and its lenth is 6 char.
    select * from emp where ename like'S____';
20. Select all records where ename may be any no of  character but it should end with ‘R’.
    select * from emp where ename like'%R';
21. Count  MGR and their salary in emp table.
    select count(MGR),count(sal) from emp;
22. In emp table add comm+sal as total sal  .
    select ename,(sal+nvl(comm,0)) as totalsal from emp;
23. Select  any salary <3000 from emp table. 
    select * from emp  where sal> any(select sal from emp where sal<3000);
24. Select  all salary <3000 from emp table. 
    select * from emp  where sal> all(select sal from emp where sal<3000);
25. Select all the employee  group by deptno and sal in descending order.
    select ename,deptno,sal from emp order by deptno,sal desc;
26. How can I create an empty table emp1 with same structure as emp?
    Create table emp1 as select * from emp where 1=2;
27. How to retrive record where sal between 1000 to 2000?
    Select * from emp where sal>=1000 And  sal<2000
28. Select all records where dept no of both emp and dept table matches.
    select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
29. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
    (Select * from emp) Union (Select * from emp1)
30. How to fetch only common records from two tables emp and emp1?
    (Select * from emp) Intersect (Select * from emp1)
31.  How can I retrive all records of emp1 those should not present in emp2?
    (Select * from emp) Minus (Select * from emp1)
32. Count the totalsa  deptno wise where more than 2 employees exist.
    SELECT  deptno, sum(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2