Data Visualization 2013

< mySQL Lab Demo >
(Originally titled "sql_sepand.txt" Updated by Yoon Chung Han) 

Basic Demo Exercises

select * from inraw limit 200;

select itemtype from inraw limit 200;

select distinct itemtype from inraw;

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

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

Select callNumber from inraw where deweyClass is null;

select deweyClass,count(*) from inraw where deweyClass is not null group by deweyClass order by count(*) desc;

select * from inraw where  DATE(cout)= '2010-12-3';

select itemtype,count(*) from inraw where  DATE(cout)= '2010-12-3' group by itemtype;

select weekday(cout), weekday(cin) from inraw where DATE(cout)= '2010-12-3';

/* return day of week */

select  weekday(cin), count(*) from inraw where DATE(cout)= '2010-12-3' group by weekday(cin);

/* return tom hour - basic */

select hour(cin) from inraw where cin > DATE('2006-11-20') and cin < DATE('2006-11-22');

select hour(cin), count(*) from inraw where cin = DATE('2006-11-20') or cin = DATE('2006-11-22') group by hour(cin);

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

select hour(cin), count(*) from inraw where cin > DATE('2006-11-20') and cin < DATE('2006-11-22') group by hour(cin) order by  hour(cin);

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

select avg(hour(cin)) from inraw where cin > DATE('2006-11-20') and cin < DATE('2006-11-22');

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

SELECT deweyClass, count(*) from inraw group by year(cout) limit 20;

select distinct itemtype, title, month(`cout`) from inraw order by month(`cout`)  desc limit 200;
 // long time

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

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

select month(cout), count(*) '$dvd$' from inraw group by month(cout);

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