Commands
Get unique values
SELECT DISTINCT report_date FROM daily;
How many records?
SELECT count(*) from vanall WHERE Shipped_Date="2021-01-12";
Last Shipped Date
-- Current max date in the table
SELECT max(Shipped_Date) from vanall;
Insert Or Update
INSERT or replace INTO daily (date,name_id,testing_hour,sellable,liquidation,quality,concession)
VALUES ("2021-02-01",5,1.0,2,3,4,5);
Insert data get from other tables
-- insert new data from temp into table vanall
INSERT INTO vanall
SELECT * from temp where Shipped_Date>"2021-01-12";
Update
UPDATE associate SET show=1 WHERE show IS NULL
Delete
-- remove all the assets that are not shipped yet
DELETE FROM vanall WHERE Shipped_Date is NULL
-- delete table
DROP TABLE associate;
Creating Sub-Query
with yvr3(facility,class,cat, sub) as
(select "YVR3", class,cat, count(*) as sub from amazon where location < "QC-76.11"
group by class,cat),
yyc1(facility, class,cat,sub) as
(select "YYC1", class,cat,count(*) as sub from amazon where location > "QC-76.10"
group by class,cat)
select yyc1.facility,yyc1.class,yyc1.cat, yyc1.sub from yyc1
left join yvr3
on (yyc1.class=yvr3.class and yyc1.cat=yvr3.cat)
where yvr3.cat is null
order by yyc1.sub desc;
FULL OUT JOIN
Sqlite doesn’t support FULL OUT JOIN
or RIGHT JOIN
.
It’s easy to perform a RIGHT OUTER JOIN in SQLite by simply reversing the order of tables and using a LEFT OUTER JOIN.
It’s also possible to do a FULL OUTER JOIN by combining LEFT OUTER JOINs using the UNION keyword.