BASIC DEMO EXERCISES

select * from transactions2005 limit 200;

select itemtype from transactions2005 limit 200;

select distinct itemtype from transactions2005;

select itemtype,count(*) from transactions2005 group by itemtype;

select deweyClass,count(*) from transactions2005 group by deweyClass;

Select callNumber from transactions2005 where deweyClass is null;

select deweyClass,count(*) from transactions2005 

where deweyClass is not null group by deweyClass order by count(*) desc;

select * from transactions2010 

where  DATE(ckoutDateTime)= '2010-12-3'

select itemtype,count(*) from transactions2010 

where  DATE(ckoutDateTime)= '2010-12-3' group by  itemtype;

select weekday(ckoutDateTime), weekday(ckinDateTime) from transactions2010 

where DATE(ckoutDateTime)= '2010-12-3'

/* return day of week */

select  weekday(ckinDateTime), count(*) from transactions2010 

where DATE(ckoutDateTime)= '2010-12-3' group by weekday(ckinDateTime)

/* return tom hour - basic */

select hour(ckinDateTime) from transactions2005 where ckinDateTime > DATE('2005-11-20')

and ckinDateTime < DATE('2005-11-22')

select hour(ckinDateTime), count(*) from transactions2005

 where ckinDateTime = DATE('2005-11-20')

or ckinDateTime = DATE('2005-11-22') group by hour(ckinDateTime)

/* number of check-ins on a specific day by hour */

select hour(ckinDateTime), count(*) from transactions2005 where ckinDateTime > DATE('2005-11-20')

and ckinDateTime < DATE('2005-11-22') group by hour(ckinDateTime) order by  hour(ckinDateTime) 

/* avg check-in time on a specific date */

select avg(hour(ckinDateTime)) from transactions2010 where ckinDateTime > DATE('2005-11-20')

and ckinDateTime < DATE('2005-11-22')



select itemtype,deweyClass, count(*) from transactions2005 group by deweyClass;

SELECT deweyClass, count(*) from transactions2005 group by year(ckoutDateTime) limit 20;

select distinct itemtype, title, month(`ckoutDateTime`) from transactions2010 order by month(`ckoutDateTime`)  desc limit 200 // long time

select distinct callnumber, title, deweyClass, collcode, ckoutDateTime, count(*) as num from transactions2010 where itemtype like '%dvd%' group by callnumber LIMIT 200;

---------------------------------------------------------------------------------------------------------------------------


select month(ckoutDateTime), count(*) '$dvd$' from transactions2006 group by month(ckoutDateTime);

select month(ckoutDateTime), count(*) '$bk$' from transactions2006 where deweyClass >= 200 AND deweyClass < 300 group by month(ckoutDateTime);