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)