Examples worked through with DataGrip & the following basic table structure:
The data source is based on a hypothetical data-center with different rackspace units, the current/planned rentals, prices and configuration of the racks.
nb some odd comments left in there for my own reference like -- // foo has some kind of meaning about getting a return of foo number of rows from the query run individually
| Table name | Fields | Types/Notes |
|---|---|---|
| configuration | cpu, memory, hd, speed | |
| facebookcontacts | facebookcontact, facebookphone | |
| linkedincontacts | linkedincontact, linkedinphone | |
| price | config, startdate, enddate, rent | |
| rentals | rentid, unitno, startdate, enddate | |
| unit | unitno, type, config, position |
select *
from configuration
order by speed desc
limit 3
select *
from rentals
where extract(MONTH from startdate)
select *, extract(month from startdate) as Month, extract(Year from startdate) as Year
from rentals
where extract(Month from startdate) = 1
AND extract(year from startdate) = 2016
order by startdate
limit 1 offset 4
select *
from configuration
where config like 'M%'
and speed > 700
select *
from configuration
where NOT(memory between 32 and 128)
select *
,CASE WHEN HD < 65 then 'EntryHD'
WHEN HD between 65 and 300 then 'Mid'
WHEN HD > 300 then 'EnterpiseHD' end as "HD Class"
from configuration
Order by HD desc
select *
,case when extract(Day from startdate) = 1 then 'FirstofMonth'
when extract(Day from (startdate + 1 )) = 1 then 'EndofMonth'
else 'MiddleofMonth' end as TimeinMonth
from rentals
order by case when extract(Day from startdate)= 1 then 1
when extract(Day from (startdate + 1)) = 1 then 3
else 2 end
select count(*) as cnt
from configuration
select sum(memory) as totmem
From Configuration
Aggregator: only one record
select max(speed), min(speed), avg(speed), avg(memory)
from configuration
where config like 'M%'
select distinct cpu
from configuration
order by cpu
select avg(speed)
from configuration
where cpu = 12
select cpu, avg(speed)
from configuration
group BY cpu
select cpu, memory, avg(speed), count(*)
from configuration
where config like 'M%'
group by cpu, memory order by cpu
select case when hd < 60 then 'SmallHD'
when hd > 60 and hd < 300 then 'MidHD'
when hd > 300 then 'LargeHD' end HDBucket
, cast(avg(speed) as decimal(10,4)) as AvgSpeed
, min(memory) as MinMemory
, max(memory) as MaxMemory
, count(*) as ConfigCount
from configuration
group by case when hd < 60 then 'SmallHD'
when hd > 60 and hd <300 then 'MidHD'
when hd > 300 then 'LargeHD' end
having count(*) >= 3
and max(memory) < 32
and avg(speed) > 500
select count(*), count(enddate)
from rentals
select extract(Year from startdate) as Year
, extract(Month from startdate) as Month
, extract(Year from enddate) as endYear
, extract(Month from enddate) as endMonth
-- , count(*) as AllRentals
-- , count(*) - count(enddate) as ActiveRentals
from rentals
group by extract(Year from startdate)
, extract(Month from startdate)
, extract(Year from enddate)
, extract(Month from enddate)
order by extract(Year from startdate) DESC
, extract(Month from startdate) ASC
-- Average rental length in months
SELECT age(timestamp '2015-01-01', timestamp 'now()');
select *, extract(MONTH FROM
age((CASE WHEN enddate IS NULL
THEN now()
ELSE enddate END)
, startdate)
) +
extract(YEAR FROM
age((CASE WHEN enddate IS NULL
THEN now()
ELSE enddate END)
, startdate)
) * 12 as months
from rentals
FYI
Table 1 = facebookcontacts
Table 2 = linkedincontacts
Inner Join on condition with equality ( or other conditions )
select *
From facebookcontacts
Inner join linkedincontacts
On facebookcontacts.facebookcontact = linkedincontacts.linkedincontact
select *
from facebookcontacts
join linkedincontacts
on facebookcontact = LinkedinContact
--
Select *
From Table1, table2
Where Table1.P1 = Table2.P2
Select *
From table1
Join Table2
On Table1.P1 = Table2.P2
Select *
From table1
Full Outer Join table2
On Table1.P1 = Table2.P2
Select *
From table1
Left Outer Join table2
On Table1.P1 = Table2.P2
select *
from unit as u
left join rentals as r
on u.unitNo = r.unitno
and r.startdate < '01-01-2016'
and (r.enddate > '01-01-2016'
OR r.enddate is null)
where r.rentid is NULL
select count(*)-count(rentid)
from unit as u
left join rentals as r
on u.unitNo = r.unitno
and r.startdate < '01-01-2016'
and (r.enddate > '01-01-2016'
OR r.enddate is null)
where r.rentid is NULL
-- // 25
select *
from configuration
where cpu = (
select min(cpu)
from (
select configuration
order by speed DESC
limit 3) as a)
select *
from configuration
where config in ( select config
from price
where startdate >= '01-01-2016'
and rent > 150)