Transact SQL 101 - Lesson 03_01_SQL.txt

--Simple SELECT statement, * specifies all columns are returned
SELECT * 
FROM saleslt.customer

--Simple SELECT statement specifying column names
SELECT firstname, lastname 
FROM saleslt.customer

--Simple SELECT statement specifying column names with an alias
SELECT firstname as First, lastname as 'Last Name'
FROM saleslt.customer

--table alias example
SELECT c.firstname, c.lastname 
FROM saleslt.customer c

--ORDER BY
SELECT c.firstname, c.lastname 
FROM saleslt.customer c
ORDER BY c.firstname --ASC/DESC

--ORDER BY
--multiple sort columns;
--can include items not appearing in the select list unless SELECT DISCTINCT is specified (DON'T Need to mention 
	--unless this is a test question)
SELECT firstname, lastname, companyname
FROM saleslt.customer
ORDER BY firstname, lastname --ASC/DESC
ORDER BY customerid

--ORDER BY
--Aliases 
SELECT c.firstname as first, c.lastname 
FROM saleslt.customer c
ORDER BY first

--ALL
SELECT firstname
FROM saleslt.customer
ORDER BY firstname

--DISTINCT
SELECT firstname
FROM saleslt.customer
ORDER BY firstname



--NEXT VIDEO
--Using the WHERE clause
--Comparison Operators
SELECT *
FROM saleslt.product
WHERE productid = 680 --int numeric

SELECT *
FROM saleslt.product
WHERE color = 'black' --nvarchar character

SELECT *
FROM saleslt.product
WHERE size = 58 -- nvarchar

SELECT *
FROM saleslt.product
WHERE size = '58' -- nvarchar

SELECT * 
FROM saleslt.product
WHERE listprice > 9.50 --money
ORDER BY listprice ASC

SELECT * 
FROM saleslt.product
WHERE listprice >= 9.50 --money
ORDER BY listprice ASC


SELECT * 
FROM saleslt.product
WHERE listprice < 9.50 --money
ORDER BY listprice ASC

SELECT * 
FROM saleslt.product
WHERE listprice <= 9.50 --money
ORDER BY listprice ASC

SELECT standardcost, listprice
FROM saleslt.product
WHERE standardcost < listprice --money
ORDER BY listprice ASC

SELECT * 
FROM saleslt.product
WHERE sellstartdate > '6/1/1998' --datetime
ORDER BY sellstartdate ASC

SELECT * 
FROM saleslt.product
WHERE sellstartdate <= '6/1/1998' --datetime
ORDER BY sellstartdate ASC

SELECT * 
FROM saleslt.product
WHERE sellstartdate <> '6/1/1998' --money
ORDER BY sellstartdate ASC

--NULLS
SELECT * 
FROM saleslt.product
WHERE sellenddate > '6/1/1998' --datetime
ORDER BY sellstartdate ASC

SELECT * 
FROM saleslt.product
WHERE sellenddate IS NULL
ORDER BY sellstartdate ASC

SELECT * 
FROM saleslt.product
WHERE sellenddate IS NOT NULL
ORDER BY sellstartdate ASC

SELECT * 
FROM saleslt.product
WHERE sellstartdate < sellenddate


--NEXT VIDEO
--Using the WHERE clause
--Logical Operators