SQLite example with create table, insert, COUNT(), GROUP BY etc

BEGIN TRANSACTION;

/* Create a table called Customer */



Create table Customer(Cust_id int NOT NULL PRIMARY KEY,
			Cust_name varchar(255) NOT NULL);
			
			
			
/* Create a table called Invoice */


Create table Invoice(Cust_id int NOT NULL,
			Invoice_Number int NOT NULL PRIMARY KEY,
			Invoice_Amount DECIMAL(7,2),FOREIGN KEY(Cust_id) REFERENCES Customer(Cust_id));
			

/* Create few records in this table */


insert into Customer values(1,'Brandon Thompson');
insert into Customer values(2,'Jason Richardson');
insert into Customer values(3,'Nicholas Ed');
insert into Customer values(4,'Ben King');
insert into Customer values(5,'Aqueela Lawson');
insert into Customer values(6,'Jay Taylor');
insert into Customer values(7,'Carson Knight');



insert into Invoice values(6,15001,500);
insert into Invoice values(2,15007,289);
insert into Invoice values(3,15987,1290);
insert into Invoice values(4,15980,120);
insert into Invoice values(5,15098,50);
insert into Invoice values(6,15976,19870);
insert into Invoice values(4,19877,1870);
insert into Invoice values(5,17980,300);
insert into Invoice values(6,19088,346);
insert into Invoice values(2,20090,25);
insert into Invoice values(4,13209,499);
insert into Invoice values(4,12098,89);
COMMIT;

/* Display all the records from the table */
SELECT * FROM Customer;

SELECT * FROM Invoice;



Select Invoice_Number, Invoice_Amount  from Invoice where Invoice_Amount > 500 order by Invoice_Amount desc;

/*
Select  C.Cust_name,Count(Invoice_Number) as Num_Of_Invoices  from Customer C,Invoice I where C.Cust_id= I.Cust_id;


select Count(Invoice_Number) from Invoice group by Cust_id;



Select C.Cust_name,((select Count(Invoice_Number) from Invoice group by Cust_id))as Num_Of_Invoices  from Customer C,Invoice I where C.Cust_id= I.Cust_id; 




select Count(Invoice_Number) from Invoice group by Cust_id

*/

SELECT 
  C.Cust_name,
  COUNT(I.Invoice_Number) AS "TOTAL Invoices"
FROM Customer C INNER JOIN Invoice I  ON C.Cust_id= I.Cust_id
GROUP BY C.Cust_id;


Select  C.Cust_name as Cust from Customer C where C.Cust_id not in (select  I.Cust_id from Invoice I) order by C.Cust_name;