Multicommands

Agnieszka

Multicommands is a separated theme for mix of commands from various lessons. I am going to show you here some SQL variations.

quick repetition from last post. Let’s write several commands with Count:

select COUNT(*) as HowManyRows
 from dbo.PRODUCTS

select COUNT(Sales_Price) as HowManyPrices from dbo.PRODUCTS

select COUNT(Distinct(Sales_Price)) as HowManyDifferentPrices from dbo.PRODUCTS

what’s the difference between all this 3 commands?

if we want to put more then one column for a command including functions, we should and the request with phrase “Group by”. An Examples:

count two tables

I have already shown you select with words: Like, And, Or, Distinct and Desc, time for Between and In:

select * from dbo.ADDRESSES
where City in ('Gdańsk', 'Sopot') 
order by Street

select * from dbo.ADDRESSES
where City not in ('Gdańsk', 'Sopot') 
order by Street

select * from dbo.PRODUCTS
where Sales_Price between 14 and 25
order by Album

mix of functions

Example 1:

select COUNT(Product_ID) as IsthereAProduct from dbo.PRODUCTS
where Sales_price >= 2*(select AVG(Sales_price) from dbo.PRODUCTS)
Example 2

select Artist, Album, On_Stock,
(select AVG(On_stock) from dbo.PRODUCTS) 
as MediumAmount,
On_Stock -(select AVG(On_stock) from dbo.PRODUCTS)
As Rest from dbo.PRODUCTS

or for Sales_Price column:

select Artist, Album, Sales_price,
(select AVG(Sales_price) from dbo.PRODUCTS) 
as MediumPrice,
Sales_price -(select AVG(Sales_price) from dbo.PRODUCTS)
As Rest from dbo.PRODUCTS
Example 3
UNION

select ‘1.NameOfNewRow1’, COUNT(*) from dbo.Table1
UNION
select ‘2.NameOfNewRow2’, COUNT(*) from dbo.Table2
UNION
…….

select ‘n.NameOfNewRown’, COUNT(*) from dbo.Tablen

ORDER BY n

UNION
Example 4
Having:

select Artist, Album, On_Stock, 
(select AVG(On_stock) from dbo.PRODUCTS) 
as MediumAmount,
On_Stock -(select AVG(On_stock) from dbo.PRODUCTS)
As Rest from dbo.PRODUCTS
Group by Artist, Album, On_stock
Having AVG(On_stock) > 5

select Artist, Album, On_Stock, 
(select AVG(On_stock) from dbo.PRODUCTS) 
as MediumAmount,
On_Stock -(select AVG(On_stock) from dbo.PRODUCTS)
As Rest from dbo.PRODUCTS
Group by Artist, Album, On_stock
Having AVG(On_stock) < 5

what’s the difference in a result view?

Example 5
Mix of Joins with where and order by clause
select S.Company_Name, A.City, P.Artist, P.Album 
from dbo.SUPPLIERS As S
left outer join dbo.ADDRESSES as A on (S.ID_Address = A.ID)
inner join dbo.PRODUCTS AS P on 
(S.Supplier_ID = P.Delivery_Method )
where A.City = 'Olsztyn' or A.City = 'Gdańsk'
order by Album

a result

MultiJoin.jpg