SQLite3 commands Day 15 Fall 2019

.mode csv
.import f19_reviewer.csv reviewer
.tables
.import f19_rating.csv rating
.import f19_movie.csv movie
.tables
.tables
.table
select * from reviewer ;
.mode column
select * from reviewer ;
select rid firstname from reviewer;
select rid,firstname from reviewer;
select rid,firstname from reviewer limit 5;
select rid,firstname as first from reviewer limit 5;
.header on
select rid,firstname as first from reviewer limit 5;
select first from (select rid,firstname as first from reviewer limit 5);
select first from (select rid,firstname as first from reviewer limit 5) order by first;
.mode csv
.import f19_reviewer.csv reviewer
.tables
.import f19_rating.csv rating
.import f19_movie.csv movie
.tables
.tables
select * from reviewer
;
.mode column
select * from reviewer;
.header on
select * from reviewer;
select * from reviewer limit 5;
select firstname from reviewer limit 5;
select firstname from reviewer order by firstname limit 5;
select firstname,lastname from reviewer order by firstname limit 5;
select firstname,lastname from reviewer order by firstname desc limit 5;
select * from rating join reviewer on rating.rid = reviewer.rid limit 5;
create view foo aselect * from rating join reviewer on rating.rid = reviewer.rid limit 5;
create view foo as select * from rating join reviewer on rating.rid = reviewer.rid limit 5;
select * from foo;
create view foo as select * from rating join reviewer on rating.rid = reviewer.rid limit 5;
drop view if exists foo;
create view foo as select * from rating rat join reviewer rev on rat.rid = rev.rid limit 5;
select * from rating rat join reviewer rev on rat.rid = rev.rid limit 5;
select * from rating rat left outer join reviewer rev on rat.rid = rev.rid limit 5;
select * from rating rat right outer join reviewer rev on rat.rid = rev.rid limit 5;
select * from rating natural join reviewer limit 5;
select * from rating outer reviewer;
select * from rating outer join reviewer;
select * from rating limit 5'
;
;
'
select * from rating limit 5;
.mode column
select * from rating limit 5;
.header on
select * from rating limit 5;
select mid, avg(stars)  from rating group by mid;
select mid, avg(stars)  from rating group by mid limit 5;
select mid, avg(stars) as avstars from rating group by mid limit 5;
.tables
select * from rating left outer join movie on rating.mid = movie.mid;
select * from rating left outer join movie on rating.mid = movie.mid;