Collection of SQL Queries


COUNT
SELECT COUNT(*) FROM transactions2010;

DISTINCT
SELECT DISTINCT barcode from transactions2010; // 

LIMIT sets limit
SELECT DISTINCT collcode from transactions2010 LIMIT 20;

LIKE / wildcard  WHERE //filter condition
select ckoutDateTime, title, collcode from transactions2010 where title like '% war %' limit 20;

BETWEEN //look at a range between two numbers
select ckoutDateTime, title, collcode, deweyClass from transactions2010 where deweyClass between 640 and 642;

SOUNDEX // sounds like
select ckinDateTime, title, deweyClass from transactions2010 where Soundex(title) = Soundex ('happy') limit 20;

GROUP // takes longer time
select ckoutDateTime, title, collcode from transactions2010 group by collcode limit 20;

DISTINCT 
select distinct title, collcode, deweyClass from transactions2010 where deweyClass between 470 and 490;

select distinct title, collcode, deweyClass, count(*) from transactions2010 where deweyClass between 470 and 490 group by title;

FLOOR // 20sec how many transactions in each of the Dewey
select floor(deweyClass/10)*10,year(ckinDateTime),count(*)
from transactions2010 where deweyClass < 1000
group by floor(deweyClass/10)*10, year(ckinDateTime);


SUM & CASE // SUM, CASE is a conditional statement - / 112 sec

select floor(deweyClass/10)*10 as dewey,
SUM(CASE WHEN year(ckinDateTime) = '2005' THEN 1 ELSE 0 END) as yr2005,
SUM(CASE WHEN year(ckinDateTime) = '2006' THEN 1 ELSE 0 END) as yr2006,
SUM(CASE WHEN year(ckinDateTime) = '2007' THEN 1 ELSE 0 END) as yr2007,
SUM(CASE WHEN year(ckinDateTime) = '2008' THEN 1 ELSE 0 END) as yr2008,
SUM(CASE WHEN year(ckinDateTime) = '2009' THEN 1 ELSE 0 END) as yr2009,
SUM(CASE WHEN year(ckinDateTime) = '2010' THEN 1 ELSE 0 END) as yr2010
from transactionsallID
where deweyClass < 1000
group by floor(deweyClass/10)*10 order by dewey;