Cacher is the code snippet organizer for pro developers

###########
# Python

CMD> python -m pip install sqlite3

###########
# Windows 

  1. Obtain binaries
        - URL: https://www.sqlite.org/download.html
        - Local: c:\sqlite
        - Backup: Desktop\Programs
        - Backup: GoogleDrive
  2. Unzip in C:\sqlite\
  3. Include sqlite3.exe in Windows path
  
  
###########
# Linux

  1. Obtain binaries
        - URL:  https://www.sqlite.org/download.html
        - Local: None
  2. 
  3. 
  
  
###########
# Viewers and editors

    1. Local
        - https://sqlitestudio.pl/index.rvt
        - https://www.sqlite.org/cli.html
        - https://sqlitebrowser.org/
    
    
    2. Online
        - https://sqliteonline.com/

/* -- */
/* Sample SUBSELECT (Select within select) */
SELECT AGE FROM COMPANY 
   WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
   
SELECT * FROM COMPANY 
   WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

/* -- */
/* Sample delete v2 */
//* this will delete only when condition is matched */
DELETE FROM table_name
WHERE [condition];
 
/* this will delete all */
DELETE FROM COMPANY;
 
/* -- */
/* Sample delete v2 with Where */
DELETE FROM Products6
	WHERE ProductID = 2

/* -- */
/* Sample insert v1 */
INSERT INTO Products6 values
	(2, 'Mary', 200, 240)
 
/* -- */
/* Sample insert v2 */
INSERT INTO Products6 VALUES (5, 'Panos', 10000, 20000)
 

/* -- */
/* Sample Select 1 value */
SELECT * 
	FROM Products6
	WHERE ProductID='1'
	
/* -- */
/* Select with 2 parameters */
SELECT * FROM Products6
	WHERE Price >= 1000 AND Price <= 10000
	
/* -- */
/* Select with OR - one liner */
SELECT * FROM Products6 WHERE Name='Mary' OR Name='John'
 
/* -- */
/* Select with order by */
SELECT ProductID, Name FROM Products6 
		GROUP BY Name
	
/* -- */
/* Select with GROUPBY / ORDERBY */
SELECT ProductID, Name FROM Products6 
		GROUP BY Name
		ORDER BY Name, ProductID
		
/* -- */ 
/* Select with AND / OR */
SELECT * 	FROM Products6 
			WHERE NAME='John'
			AND NEWPRICE=100.0
			OR NEWPRICE=101.0
			
			
/* -- */
/* Select with wildcard */
SELECT * FROM Products6 Where Name glob '*an*'
 
/* -- */
/* Select with wildcard v2 */
SELECT * FROM Products6 Where Name LIKE '%an%'
 
/* -- */
/* Select with use of 'as' */
SELECT Name, Price, NewPrice AS POUTSES
  FROM Products6
 
/* -- */
/* Select with use of range of values in where */
SELECT *
	FROM Products6
	WHERE Price in (1000, 10000)
	
/* -- */
/* Select using range of values and 'between' */
SELECT *
	FROM Products6
	WHERE Price BETWEEN 1000 AND 15000
	
/* -- */
/* Select with 'not null' */
SELECT * FROM Products6 WHERE Price IS NOT NULL
 
 
 
/* -- */
/* Select with the use of 'not in' */
SELECT *
	FROM Products6
	WHERE Price not in (1000, 10000)

/* -- */
/* Create table in db */
CREATE TABLE Products12321312
              (ProductID integer,
              Name text,
              Price real,
              NewPrice real,
              primary key(ProductID))
 

/* -- */
/* Sample Update 1 value */
UPDATE Products6 
	SET Price=5000
	WHERE Name='Mary'

/* -- */
/* Sample Update two values */
UPDATE Products6 
	SET Price=5000, NewPrice=10000
	WHERE Name='Mary'
	

/* -- */
/* Sample update one liner */
UPDATE Products6 SET Price=6000 WHERE Name='Mary'

/* -- */
/* DROP table from db */
DROP table Products2

/* -- */
/* Select all tables in db from master */
/* Will bring tables for the db we re connected to */
SELECT tbl_name FROM sqlite_master WHERE type = 'table';
 
/* -- */
/* Select to get info on specific table */
/* table|employee|employee|2|CREATE TABLE employee(empid integer, name varchar(20), title varchar(10)) */
SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'Products6';

/* -- Count Table entries -- */
SELECT COUNT(*) AS "RECORDS" FROM Products6