SQL

SELECT ISNULL(NULL, 'www.stdpoint.com');
Result: 'www.stdpoint.com'

SELECT ISNULL('stdpoint.com', 'www.stdpoint.com');
Result: 'stdpoint.com'

SELECT ISNULL(NULL, 45);
Result: 45

SELECT ISNULL(12, 45);
Result: 12

SELECT ISNULL(NULL, '2014-05-01');
Result: '2014-05-01'

SELECT ISNULL('2014-04-30', '2014-05-01');
Result: '2014-04-30'

//============TOP (top_value) [ PERCENT ]===========

SELECT TOP (top_value) [ PERCENT ]

expressions

FROM tables

[WHERE conditions]

[ORDER BY expression [ ASC | DESC ]];

SELECT TOP(5)

contact_id, last_name, first_name

FROM contacts

WHERE last_name = 'Rajpoot'

ORDER BY contact_id;

SELECT TOP(10) PERCENT

contact_id, last_name, first_name

FROM contacts

WHERE last_name = 'Rajpoot'

ORDER BY contact_id;

//========LIMIT ===============

SELECT expressions

FROM tables

[WHERE conditions]

[ORDER BY expression [ ASC | DESC ]]

LIMIT number_rows [ OFFSET offset_value ];

SELECT contact_id, last_name, first_name

FROM contacts

WHERE website = 'Stdpoint.com'

ORDER BY contact_id DESC

LIMIT 5;

SELECT contact_id, last_name, first_name

FROM contacts

WHERE website = 'stdpoint.com'

ORDER BY contact_id ASC

LIMIT 5;

SELECT contact_id, last_name, first_name

FROM contacts

WHERE website = 'stdpoint.com'

ORDER BY contact_id DESC

LIMIT 5 OFFSET 2;

//===========EXISTS ===============
SELECT *
FROM customers
WHERE EXISTS 
  (SELECT *
   FROM orders
   WHERE customers.customer_id = orders.customer_id);

SELECT COUNT(*) FROM orders
WHERE EXISTS
  (SELECT *
   FROM customers
   WHERE customers.customer_id = orders.customer_id
   AND customers.last_name = 'Jackson');
//=====================GROUP BY==============================
SELECT expression1, expression2, ... expression_n,
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY expression [ ASC | DESC ]];


SELECT category_id, COUNT(*) AS total_products
FROM products
WHERE category_id IS NOT NULL
GROUP BY category_id
ORDER BY category_id;

SELECT dept_id, MIN(salary)/MAX(salary)AS lowest_salary
FROM employees
GROUP BY dept_id;