SQL Commands

 select 

AS forcolumn name 

select name, continent as "continent name" from Country order by Name;

select name, continent as "continent name" from Country order by Name;

select name, continent , region from Country where continent= "Europe" order by Name LIMIT 5 offset 5;

select name as "Country" from Country;

select count(*) from Country where population> 100000 AND Continent= "Europe";

    

 select * from customer;

insert into customer(name,address, city, state, zip)

values("FRD ","abc","KK","CL","334");

update customer SET address= "klllll" where id = 2;

delete from customer where id = 4;

select * from customer ;

create table test(

a integer,

b text

);

insert into test values (1,'a');

insert into test values (2,'b');

insert into test default values;

select * from test;

drop table test ;

drop table if exists test;

create table test(

a integer not null,

b text not null,

c text

);

insert into test values (1,'this','that');

insert into test (a,b,c) values ('one','two','');

select * from test;

 

null is not a value the null state represnts a lack  of value 

Constraints in SQL

 not nul, uniques 

create table test (a Text unique, b text, c text default 'cat');

insert into test (a,b) values ('one','two');

insert into test (a,b) values ('three','two');

select * from test;

alter table test add e text default 'dog';

select * from test;

alter table test add e text default 'dog';

select * from test;

select name, population from country

where population <50000 OR population is null order by population desc;

select name, population, continent from country

create table left (id integer, description text);

create table right(id integer, description text);

insert into left values (1, 'leftone'); 

insert into left values (2, 'lefttwo');

insert into left values (3, 'leftthree');

insert into left values (4, 'leftfour');

insert into left values (5, 'leftfive');

insert into left values (6, 'leftsix');

insert into left values (7, 'leftseven');

insert into left values (8, 'lefteight');

insert into right values(1, 'rightone');

insert into right values(2, 'righttwo');

insert into right values(3, 'rightthree');

insert into right values(4, 'rightfour');

insert into right values(5, 'rightfive');

insert into right values(6, 'rightsix');

insert into right values(7, 'rightseven');

select * from left;

select  *from right;

select l.description as left , r.description as right 

    from left as l

    left join right as r on l.id= r.id

    ;

where continent in ('Asia', 'Europe') order by name;

select distinct continent from country; more unique results 

select s.id as sale ,i.name, s.price

from sale as s

join item as i on s.item_id= i.id;

insert into customer (name) values ('jane smith');

select * from customer;

select c.name as cust, c.zip, i.name as item,i.description, s.quantity as quan,s.price as price

from sale as s

join item as i on s.item_id= i.id

join customer as c on s.customer_id= c.id

order by cust, item

;

insert into customer (name) values ('jane smith');

select * from customer;

select c.name as cust, c.zip, i.name as item,i.description, s.quantity as quan,s.price as price

from customer as c

left join sale as s on s.customer_id= c.id

left join item as i on s.item_id= i.id

order by cust, item

;

strings- 

select released,

substr(released, 1, 4) as year,

substr(released, 6, 2)as month,

substr(released, 1, 4) as day

from album order by released 

;

select released,

substr(released, 1, 4) as year,

substr(released, 6, 2)as month,

substr(released, 1, 4) as day

from album order by released;

select  RTRIM ('   string');

select 'string'= 'STRing';

select LOWER('string')= lower('STRing');

select upper('string')= lower('STRing');

numeric type --

select typeof(1+1);

select typeof(1+1.2);

select typeof('first');

select typeof('first' + 'name');

select 1/2;

select cast(1 as real)/2;

select 17/5;

select round(2.455555);

select 17%5;

date and time

select datetime('now');

select date('now');

select time('now');

select datetime('now','+1 day');

aggregates:

having for aggregate data 

where by for non aggregate data 

select a.title as album , count(t.track_number) as tracks

from track as t

join album as a

on a.id= t.album_id

group by a.id

order by Tracks desc,album

;

Use the __distinct___ keyword to remove duplicates from a result

Use the _group by ____ clause to collate groups of results before calling an aggregate function.
















You can undo an unfinished transaction by using the ___rollback__ statement.

begin transction

end transaction







create table updatesale(id interger primary key, itemid integer, quan integer, covalue integer);

insert into updatesale(itemid, quan ,covalue) values (31,2,33);

create trigger updatesale before update on sale

Begin

select raise(rollback ,"can not update") from updatesale

where id=new.id and covalue= 1;

End

;

Begin transaction;

update updatesale set quan= 9 where id=3;

End Transaction;

select * from updatesale;









The SELECT ___distinct__ statement removes duplicates from a result set.




To insert a blank row use the _default____ clause.




To delete a table, use the ___drop__ statement.

What are the three arguments for the SUBSTR() function in SQLite?



You are correct!


string to evaluate, starting position, length of string


The type of trigger to use for preventing updates to reconciled rows is _____


delete rows - delete cmd


delete table- drop table


alter table - add new coulmn


id integer primary key


substr(stringstart,end)


length


trim


upper lower