FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numbers 
FROM Sales.Orders
WHERE custid = '71'
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear

FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numberorders
ORDER BY empid, orderyear

USE TSQL2012
GO
IF EXISTS(
SELECT 1
FROM Sales.Orders)
SELECT 'SELECT 1'
GO
IF EXISTS(
SELECT *
FROM Sales.Orders)
SELECT 'SELECT *'
GO

CREATE INDEX ix_shipname
ON Sales.Orders(shipname)

USE TSQL2012
GO
IF (
SELECT 1
FROM Sales.Orders
WHERE shipname = 'Ship to 85-B') = 1
SELECT 'SELECT 1'
GO
IF (
SELECT COUNT(*)
FROM Sales.Orders
WHERE shipname = 'Ship to 85-B') = 1
SELECT 'SELECT *'
GO

USE TSQL2012
GO
IF (
SELECT COUNT(1)
FROM Sales.Orders
WHERE freight = '41.3400') = 1
SELECT 'SELECT 1'
GO
IF (
SELECT COUNT(*)
FROM Sales.Orders
WHERE freight = '41.3400') = 1
SELECT 'SELECT *'
GO

USE TSQL2012
SELECT custid, companyname FROM Sales.Customers AS C
WHERE country = N'USA' AND
EXISTS (SELECT * FROM Sales.Orders AS O WHERE O.custid = C.custid)
GO
SELECT custid, companyname FROM Sales.Customers AS C
WHERE country = N'USA' AND
EXISTS (SELECT 1 FROM Sales.Orders AS O WHERE O.custid = C.custid)

USE TSQL2012
Go
CREATE VIEW SaleOdersView
AS
SELECT shipaddress,shipname,(SELECT unitprice FROM Sales.OrderDetails AS sod where sod.orderid = so.orderid) as tc3
FROM Sales.Orders AS so
GO

USE TSQL2012
SELECT 1 FROM dbo.SaleOdersView
go
SELECT * FROM dbo.SaleOdersView
go

I don't think there is any difference, as long as the SELECT 1/* is inside EXISTS, which really doesn't return any rows – it just returns boolean as soon as condition of the WHERE is checked.
I'm quite sure that the SQL Server Query Optimizer is smart enough not to search for the unneeded meta data in the case of EXISTS.
I agree that in all the other situations SELECT * shouldn't be used for the reasons Simon mentioned. Also, index usage wouldn't be optimal etc.
For me EXISTS (SELECT * ..) is the only place where I allow myself to write SELECT * in production code ;)