SQLLite and SQL Commands

Hi All,

I did a quick search on "SQL" only to find 0 items. My Collection has reached over 10,000 movies and several hundred TV Series. I installed SQLLite and have created several SQL Statements to search and catalog my collection. I thought i'd share these and hopefully others will have done the same. I have tried on several occasions to update the DB using SQLLite but this seems to corrupt the DB and so i've stuck to "read only" type of SQL statements. My 3 most used SQL are as follows:

(1) This SQL selects some of the fields in the DB to list them -- i use the list in an excel spreadsheet before buying a DVD to convert it to disk:

select IMDB_ID, Title, file, Duration/60 as Duration, printf("%.2f", CAST((cast(Size as real(10.2)) / 1073741824) AS real(5.2))) AS GB,
Year, strftime('%m-%d-%Y', datetime(DateAdded, 'unixepoch')) as DateAdd from MGOFile
where disc = '0'
and not imdb_id = ""
Order by Imdb_id;

(2) This SQL gives me a breakdown of all my files by size.

select t.range as [sz range], count(*) as [Count]
from (
select
case
when size between '0' and '2684354560' then "1- less than 2.5GB"
when size between '2684354561' and '5368709120' then "2- 2.5GB to 5.0GB"
when size between '5368709121' and '10737418240' then "3- 5.0GB to 10GB"
when size between '10737418241' and '16106127360' then "4- 10GB to 15GB"
else "5- more than 15GB"
end as range
from MGOfile where Disc = '0'
and not (
imdb_id = ""
)) t
group by range

(3) Finally this SQL gives me breakdown by decade of the movie count

select t.range as [year range], count(*) as [Decade Count]
from (
select
case

when year between '0' and '1919' then "A - Before 1920s "
when year between '1920' and '1929' then "B - The 1920s"
when year between '1930' and '1939' then "C - The 1930s"
when year between '1940' and '1949' then "D - The 1940s "
when year between '1950' and '1959' then "E- The 1950s "
when year between '1960' and '1969' then "F- The 1960s "
when year between '1970' and '1979' then "G- The 1970s "
when year between '1980' and '1989' then "H- The 1980s "
when year between '1990' and '1999' then "I- The 1990s "
when year between '2000' and '2009' then "J- The 2000s "
when year between '2010' and '2019' then " K- The 2010s "
when year between '2020' and '2029' then " L- The 2020s "
else " M- The 2030s "
end as range
from MGOfile where Disc = '0'
and year > 0
and not (
imdb_id = ""
)) t
group by range
order by range

I hope this gets some traction and others have develop SQL statements to use the DB directly