Select all columns from some table (system table in this case):

SELECT *
FROM sys.objects

Or, select just some specific columns:

SELECT object_id, name, type, create_date
FROM sys.objects

WHERE clause filters only those rows that satisfy some condition:

SELECT *
FROM sys.objects
WHERE type = 'IT'

ORDER BY clause sorts rows in the returned result set by some column or expression:

SELECT *
FROM sys.objects
ORDER BY create_date

GROUP BY clause groups rows by some value:

SELECT type, count(*) as c
FROM sys.objects
GROUP BY type

You can apply some function on each group (aggregate function) to calculate sum or count of the records in the group.

HAVING clause removes groups that do not satisfy condition:

SELECT type, count(*) as c
FROM sys.objects
GROUP BY type
HAVING count(*) < 10

TOP clause returns only first N rows in the result:

SELECT TOP 10 *
FROM sys.objects

OFFSET FETCH clause is more advanced version of TOP. It enables you to skip N1 rows and take next N2 rows:

SELECT *
FROM sys.objects
ORDER BY object_id
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY

You can use OFFSET without fetch to just skip first 50 rows:

SELECT *
FROM sys.objects
ORDER BY object_id
OFFSET 50 ROWS

SELECT statement can be executed without FROM clause:

declare @var int = 17;

SELECT @var as c1, @var + 2 as c2, 'third' as c3 

In this case, one row with values/results of expressions are returned.