SQL Quick look: Dec17th 2021- Jan17th 2022

 17/12/2021

............................................................................................................................................

create table ration (id integer, firstname varchar(100), lastname varchar(100), items varchar(100), cost integer, type varchar(100));

insert into ration (id, firstname, lastname,items,cost,type) values (1, 'albert', 'einstein', 'apple', 25, 'fruit');

insert into ration (id, firstname, lastname,items,cost,type) values (2, 'isaac', 'newton','peanut butter', 500, 'ingredient');

insert into ration (id, firstname, lastname,items,cost,type) values (3, 'marie', 'curie','apple', 25, 'fruit');

                insert into ration (id, firstname, lastname,items,cost,type) values (4, 'Thomson', 'Edison','bulb', 10, 'electronics');

                insert into ration (id, firstname, lastname,items,cost,type) values (5, 'Bill', 'Gates','Windows', 35, 'S');

create table Customer (Cust_ID integer, firstname varchar(100),Date_Purchase DATE NOT NULL );

insert into Customer (Cust_ID, firstname, Date_Purchase) values (10177, 'albert', '2022-01-17');

select Cust_ID, first_name.ration,items.ration,Date_Purchase from Customer

inner join ration

ON first_name.ration = fisrt_name.Customer

...............................................................................................................................................

12:43 PM 22-Dec-21

Count:

select count(distinct(type)) from ration;

ORDER BY:

|ascending and descending order|

select firstname,lastname,type from ration

order by firstname asc

LIMIT:

select * from ration

order by lastname

limit 3

inserting new column to the table:

alter table ration

add column purchase_date date;

inserting data into 

update ration set purchase_date = '12-12-2021' where id=1

===========================================================================================================================

12:50 PM 27-Dec-21

Between:

select * from ration 

where cost between 10 and 500

#ration is the table name and cost is the column name#

IN:

select * from ration

where cost in (10,500)

NOT IN

select * from ration

where cost not in (10,500)

LIKE:

select * from ration

where lastname like 'e%'

if you're not sure of case then:

select * from ration

where lastname ilike 'E%'

if you want to get people whose names dont start with alphabet

select * from ration

where lastname not like 'E%'

MAX and MIN:

select max(cost),min(cost) from ration;

Average:

select avg(cost) from ration;

average function returns float values which are like recurring zeroes that can be avoided using below syntax:

select round(avg(cost),1) 

             from ration;

======================================================================================================================

4:58 PM 04-Jan-22

Having:

select type,sum(cost) from ration

where type != 'S'

group by type

having sum(cost)>100

===================================================================================================================

4:53 PM 17-Jan-22

Inner Join:

select Cust_ID,ration.firstname,Date_Purchase 

from Customer

inner join ration

ON ration.firstname = Customer.firstname