COMP3311 20T3 SQLite Database Systems Summary information about what SQLite v3 provides.
In the examples below, x, y, s, etc. are attributes. Data Types
Base Data Types
integer, real, char(N), varchar(N), text, date, time, datetime
Domains
create domain Name as BaseType check Constraint;
Tuple Types
create type Name as (Attr1 Type1, …, Attrn Typen);
Expressions
Arithmetic
+-*/%
e.g.x+3, x+y+z, y%4
Logic
and or not = <>
e.g.x>5andx<10, z<>‘abc’, x between 6 and 9
String
|| like ilike ~
~*
e.g. s1 || s2, name like ‘%abc%’ string literals: ‘John”s book’,
e’hello\n’
Note: double-quotes (“…”) are not strings
NULL
is null,
coalesce()
e.g. x is null, y is not null,
note that x = null always fails, coalesce(x,y,99) value is first non-null or 99
Functions
Case-insensitive SQL pattern matching
Attribute like Pattern
String concatenation
String1 || String2
String concatenation aggregrate
group_concat(TextAttribute[,Separator])
Substring
substr(TextAttribute,Start,Length)
Aggregates
count(), min(), max(), avg(), sum()
Constraints
Attribute
check, not null,
unique
e.g. check (x >= 0), x not null, x unique
Foreign key
foreign key
foreign key (Attributes) references Table(Attributes)
e.g. foreign key (x,y) references R(a,b)
Primary key
primary key
primary key (Attributes) e.g. primary key (x)
implies x is not null & unique
Defining Things
Tables
create table Name (Attributes+Constraints); e.g.
create table R (
s text primary key,
x integer not null,
y real check (y > 0),
foreign key (x) references T(x)
);
Views
drop view if exists Name; create or replace view Name
as SQL_Statement e.g.
drop view if exists Movies;
create view Movies as
select mid as id, name as title
from Titles
where format = ‘movie’;
Functions
No stored procedures in SQLite
Domains
No domain definitions in SQLite
Enums
No enums in SQLite
Queries
General format
select Expressions as Names from Table1
join Table2 on JoinCondition1
…
join Tablen on JoinConditionn-1
Commands outside sqlite3
Getting a list of databases
ls
Connect to a database to ask SQL queries
sqlite3 DatabaseName
or, in the exam, simply
sql
Create a database
sqlite3 DatabaseName
Remove a database
rm DatabaseName
where Condition group by Attributes having Condition order by Attributes
Examples
— assume schema: R(x,y,z), S(a,b)
— show all info from table R
select * from R;
— show unique x values from table R
select distinct x from R;
— show unique x values from table R
select *
from R join S on R.x = S.a
where R.y > 5;
— how many of each x value
select x, count(*) from R group by x;
— how many of each common x value
select x, count(*)
from R
group by x
having count(*) > 10;
— how many of each R.x value is
— associated with an S.a value;
— if no associated S.a value, count = 0 select x, count(a)
from R left outer join S on R.x = S.a group by x;
Notes
no right or full outer joins
Commands within sqlite3
Exit the SQL shell
.q or control-D
Get a list of tables/views in a database
.schema
Execute an SQL statement
select * from TableName;
Excecute SQL commands from a file
.read FileName
Go to previous command
Up-arrow
Create a view
drop view if exists V;
create or replace V(a,b,c)
as select x,y,z …
Union, Intersect, Difference
SelectStatement1 union SelectStatement2