Some sql statements with arithmetic functions

create table data1(id integer, weight real, height real, gpa real);


insert into data1 values(100,350,43,3.56);
insert into data1 values(101,175,63,1.78);
insert into data1 values(102,250,88,3.21);


insert into data1(id,weight)  values(103,111,;
insert into data1(weight,height)  values(185,23);
Insert into data1(height)  values(88.3);

select * from data1 order by weight;

select * from data1 order by weight desc;

select height,weight from data order by gpa;

select count(id) from data1;


select count(id),count(height),count(weight),count(gpa) from data1;

select count(id),max(weight),min(height),avg(gpa) from data1;

------------------------------------------------------------------


using the where clause

select * from data1 where weight>70;

select * from data1 where weight>200 and height<80;

select * from data1 where weight>height ;


create table part(partnum integer primary key not null, desc text);
insert into part values(101,'widget');
insert into part values(102,'fidget');
insert into part values(103,'ipod');
insert into part values(104,'macbook);


create table invoice (id integer primary key not null, pnum integer,quantity integer, foreign key(pnum) references part(partnum));



insert into invoice(1000,101,3);

insert into invoice values(1000,101,3);
sqlite> insert into invoice values(1001,101,2);
sqlite> insert into invoice values(1002,102,2);
sqlite> insert into invoice values(1003,102,5);
sqlite> insert into invoice values(1004,101,2);
sqlite> insert into invoice values(1005,103,3);
sqlite> insert into invoice values(1006,103,2);
sqlite> 

sqlite> select partnum from part where desc='ipod';
103
sqlite> select * from invoice where pnum in select partnum from part where desc='ipod';
Error: near "select": syntax error
sqlite> select * from invoice where pnum in (select partnum from part where desc='ipod');
1005|103|3
1006|103|2
sqlite> select count(*) from invoice where pnum in (select partnum from part where desc='ipod');
2
sqlite> select sum(quantity) from invoice where pnum in (select partnum from part where desc='ipod');
5
--------------------------------------------------------------------------

Do some experiments with the following


UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

DELETE FROM table_name
WHERE [condition];

read about GROUP BY

---------------------------------------------------------------------------

Joins in sql!!

create table customer(id integer primary key not null, name text, state text);

insert into customer values(100,'joe smith','ny');
insert into customer values(101,'jane doe','ny');
insert into customer values(102,'harry smith','ny');
insert into customer values(103,'sue doe','nj');
insert into customer values(104,'eddie smith','ny');
insert into customer values(105,'mary doe','nj');
insert into customer values(106,'harry smith','nj');
insert into customer values(107,'harriet doe','ny');



create table vendor(id integer primary key not null, name text, state text);

insert into vendor values(100,'harris printing','ny');
insert into vendor values(101,'general motors','pa');
insert into vendor values(102,'apple computer','ny');
insert into vendor values(103,'cisco','pa');
insert into vendor values(104,'amazon','ny');
insert into vendor values(105,'dell computer','nj');
insert into vendor values(106,'kellog','nj');
insert into vendor values(107,'blue cross','ny');



JOINS!!!

sqlite> select customer.id,customer.name,vendor.name from customer,vendor where customer.state=vendor.state;

sqlite> select customer.id,customer.name,vendor.name from customer,vendor where customer.state<>vendor.state;