Thanks to all who joined in the SQL Style presentation at the Tampa SQL Saturday. It was great to have folks involved during a presentation. It doesn’t look like I can attach the presentation, but it’s available on the SQL Saturday website. The queries are right here:
–Example Queries
use Fruit
go
select * from colors
select * from fruit
–Use ‘where’ clause
select *
from fruit
where colorKey = 1
–Dealing with null
select *
from fruit
–where colorKey = null
where colorKey is null
–Wildcard
select *
from fruit
where fruitName like ‘%e%’
–where fruitName like ‘%e ‘
–where rtrim(fruitName) like ‘%e’
–‘In’ clause
select *
from fruit
where fruitName in (‘apple’,’grape’)
–In with a subquery
select *
from fruit
where fruitName in (select fruitName from fruit where rtrim(fruitName) like ‘%e’)
–Everything from inner join
select *
from fruit f
inner join colors c
on f.colorKey = c.colorKey
–Relevent columns from inner join
select f.fruitName, c.color
from fruit f
inner join colors c
on f.colorKey = c.colorKey
–Everything from left outer join
select f.fruitName, c.color
from fruit f
left outer join colors c
on f.colorKey = c.colorKey
–Everything from right outer join
select f.fruitName, c.color
from fruit f
right outer join colors c
on f.colorKey = c.colorKey
–Everything from Cross join (match everything with everything – no ‘on’ condition)
select f.fruitName, c.color
from fruit f
cross join colors c
–on f.colorKey = c.colorKey
–Everything from full join (combination of left & right outer joins)
select f.fruitName, c.color
from fruit f
full join colors c
on f.colorKey = c.colorKey
–Check for Exists
select fruitName
from fruit f
where exists
(select *
from colors c
where c.colorKey = f.colorKey)
–Alternate version of Exists
select fruitName
from fruit f
left outer join colors c
on c.colorKey = f.colorKey
where f.colorKey is not null
–Aggregations
select colorKey, count(*) as cnt
from fruit f
group by colorKey
having count(*) > 1
–Self join
use [AdventureWorksDW]
go
select c.AccountDescription, p.AccountDescription
from DimAccount c
left outer join DimAccount p
on p.ParentAccountKey = c.AccountKey
order by c.accountKey
Leave a Reply