SQL Select Skills

optional parameter to compose where condition

If our store procedure accepts some parameters to compose the SQL where clause, user might enter any of the field values all are optional. How to write the where clause with optional parameters

CREATE PROCEDURE
{
@LastName VARCHAR(100),
@Address VARCHAR(100)
}
AS
SELECT * FROM NamesTable 
WHERE lastname = @LastName OR @LastName IS null 
AND address = @Address or @Address IS null
GO

sum(case(..) end) syntax

Suppose to use a SELECT to get the percentage of boys rate in one class ?

SELECT (SUM(CASE WHEN gender="M" THEN 1 END) / count(*)) AS 'num' 
FROM studentTable;

select Nth high salary in the office

SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC)