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
