Database Course

Ahmad Yoosofan

SQL 1

University of Kashan

https://yoosofan.github.io

https://yoosofan.github.io/slide/db/sql1/

Database Schema of SP

 1 create table s (
 2     sn      char(10) primary key,
 3     sname   char(30),
 4     status  int  default(0),
 5     city    char(20)
 6 );
 7 
 8 create table p (
 9     pn     char(10) primary key,
10     pname  char(30),
11     color  char(20),
12     weight NUMERIC(9, 2),
13     city   char(20)
14 );
15 
16 create table sp (
17     sn    char(10) references s,
18     pn    char(10) references p,
19     qty   int default(0),
20     primary key (sn, pn)
21 );

DSL(Data Sub Language)

 1 insert into s(sn, sname,  status, city)
 2 values('s1', 'Smith', 20, 'London')
 3 ;
 4 
 5 insert into p(pn, pname, color, weight, city)
 6 values('p1','Nut'  ,'Red'  ,12.0,'London')
 7 ;
 8 
 9 insert into p(pn, pname, color, weight, city)
10 values
11   ('p2', 'Bolt' , 'Green', 17.0, 'Paris' ),
12   ('p5', 'Cam'  , 'Blue' , 12.0, 'Paris' ),
13   ('p6', 'Cog'  , 'Red'  , 19.0, 'London')
14 ;

delete sp;

drop table sp;

DDL: Data Definition Language
DML: Data Manipluation Language
DCL: Data Control Language

DDL: create, drop
DML: insert, delete

 1 insert into s(sn, sname,  "status", city)
 2 values('s4', 'Clark', 20, 'London')
 3 ;
 4 insert into s(sname, status, city, sn)
 5 values('Adams', 30, 'Athens', 's5')
 6 ;
 7 insert into s
 8 values('s6', 'Ali', 40, 'کاشان')
 9 ;

DBMS(Database Management System)

An application
RDBMS (Relational Datababase Management System)
SQL (Structured Query Language)
DB2, Oracle, PostgreSQL, MySQL, SqlServer, MariaDB
SQLite (Lack of DCL commands, each db on a file)

SQLite

Online

نام قطعه‌ها را بیابید.

select pname
from p
;

p{pname};

pname
Nut
Bolt
Screw
Screw
Cam
Cog
Nut
Bolt

1 select sname, status
2 from s
3 ;

1 s{sname, status};
2 
3 -- Relational Algebra

    ╭───────┬────────╮
    │ sname │ status │
    ╞═══════╪════════╡
    │ Smith │     20 │
    │ Jones │     10 │
    │ Blake │     30 │
    │ Clark │     20 │
    │ Adams │     30 │
    │ Ali   │     40 │
    ╰───────┴────────╯

1 select *
2 from s;
3 ;

    ╭────┬───────┬────────┬────────╮
    │ sn │ sname │ status │  city  │
    ╞════╪═══════╪════════╪════════╡
    │ s1 │ Smith │     20 │ London │
    │ s2 │ Jones │     10 │ Paris  │
    │ s3 │ Blake │     30 │ Paris  │
    │ s4 │ Clark │     20 │ London │
    │ s5 │ Adams │     30 │ Athens │
    │ s6 │ Ali   │     40 │ کاشان  │
    ╰────┴───────┴────────┴────────╯

as (rename)

1 select sname, status + 4
2 from s;
3 ;

    ╭───────┬────────────╮
    │ sname │ status + 4 │
    ╞═══════╪════════════╡
    │ Smith │         24 │
    │ Jones │         14 │
    │ Blake │         34 │
    │ Clark │         24 │
    │ Adams │         34 │
    │ Ali   │         44 │
    ╰───────┴────────────╯

1 select sname, status + 4 as st4
2 from s;
3 ;

    ╭───────┬─────╮
    │ sname │ st4 │
    ╞═══════╪═════╡
    │ Smith │  24 │
    │ Jones │  14 │
    │ Blake │  34 │
    │ Clark │  24 │
    │ Adams │  34 │
    │ Ali   │  44 │
    ╰───────┴─────╯

نام قطعه‌ها و وزن آنها را بیابید.

select pname, weight
from p
;

p{pname, weight} ;

pname	weight
Nut	12
Bolt	17
Screw	17
Screw	14
Cam	12
Cog	19
Nut
Bolt

NULL

1 insert into p(pn, pname, color, city)
2 values('p7', 'Nut', 'Red', 'London')
3 ;

1 insert into p(pn, pname, color, weight, city)
2 values('p8', 'Bolt', 'Green', null, 'Paris')
3 ;

1 create table s (
2  sn      char(10) primary key,
3  sname   char(30) not null,
4  status  int  default 0,
5  city    char(20)
6 );

null
not 'null'
not 0
not '0'
not ''

Do not know the value
Not applicable
- Address: city, street, alley, number

نام قطعه‌ها و وزن آن‌ها را به گرم بیابید.

1 select pname, weight * 1000 as gweight
2 from p
3 ;

NULL * 1000 → NULL

pname	gweight
Nut	12000
Bolt	17000
Screw	17000
Screw	14000
Cam	12000
Cog	19000
Nut
Bolt

Where

1 --   نام عرضه‌کنندگان شهر کاشان را بیابید.
2 
3 select sname
4 from s
5 where city = 'کاشان'
6 ;

-- (s where city = 'کاشان') {pname}

select sname
from s
where city = 'Paris'
;

sname
Jones
Blake

Arithmetic Operators
- `+ - * / %`
Comparison Operators
- `= < > >= <= <>`

شمارهٔ قطعه‌های عرضه شده را بیابید.

select pn
from sp
;

pn
p1
p2
p3
p4
p5
p6
p1
p2
p2
p2
p4
p5
p2

نام قطعه‌های عرضه شده را بیابید.

select pname
from p, sp
where p.pn = sp.pn
;

(
  (
     (
       p rename pn as ppn
     )
     times sp
  ) where ppn = pn
) {pname}

pname
Nut
Bolt
Screw
Screw
Cam
Cog
Nut
Bolt
Bolt
Bolt
Screw
Cam
Bolt

join

نام قطعه‌های عرضه شده را بیابید.

select pname
from p natural join sp
;

(p join sp) {pname}

select pname
from p join sp using(pn)
;

select pname
from p join sp on p.pn=sp.pn
;

نام قطعه‌هایی را بیابید که در شهر آن قطعه‌ها عرضه کننده‌ای وجود داشته باشد

select pname
from p join s using(city)
;

select pname
from p natural join s
;

select distinct pname
from p natural join s
;


    ╭───────╮
    │ pname │
    ╞═══════╡
    │ Nut   │
    │ Nut   │
    │ Bolt  │
    │ Bolt  │
    │ Screw │
    │ Screw │
    │ Cam   │
    │ Cam   │
    │ Cog   │
    │ Cog   │
    │ Nut   │
    │ Nut   │
    │ Bolt  │
    │ Bolt  │
    ╰───────╯

    ╭───────╮
    │ pname │
    ╞═══════╡
    │ Nut   │
    │ Bolt  │
    │ Screw │
    │ Cam   │
    │ Cog   │
    ╰───────╯

نام قطعاتی را بیابید که عرضه‌کننده‌ای از شهر کاشان آنها را عرضه کرده باشد.

select pname
from (p natural join sp)
  join s on s.sn=sp.sn
where s.city = 'کاشان'
;

select pname
from (p natural join sp)
  join s using(sn)
where s.city = 'کاشان'
;

pname
Bolt

SQLite (I)

sqlite3
sqlite3 sp.sqlite
.exit or .quit
.help
.read sp.sql
.output sp2.sql
.dump
.output

.open sp.sqlite
.databases
.backup FILE
.restore FILE
.system CMD
.system clear
.tables
.schema s

Use Another name for a Table in Query

create table t (
  a int primary key,
  name char(20)
);

insert into t values (1, 'a'),(2, 'b');

select *
from t, t as M;

a	name	a	name
1	a	1	a
1	a	2	b
2	b	1	a
2	b	2	b

select t.name
from t, t as M
where t.a < M.a;

name
a

select *
from t join t as M
  on t.a < M.a;

a	name	a	name
1	a	2	b

نام قطعاتی را بیابید که وزن آنها دست کم از وزن یک قطعهٔ دیگر بیشتر باشد نام تکراری در پاسخ نیاید.

نام همهٔ قطعات را بیابید به جز قطعه‌ یا قطعه‌هایی که کمترین وزن را دارند

1 select T.pname
2 from p as T
3 ;

1 select T.pname
2 from p as T, p
3 where p.weight < T.weight
4 ;

1 select distinct T.pname
2 from p as T, p
3 where p.weight < T.weight
4 ;

1 select distinct T.pname
2 from p as T join p on
3   p.weight < T.weight
4 ;

╭───────╮
│ pname │
╞═══════╡
│ Bolt  │
│ Screw │
│ Cog   │
╰───────╯

نام قطعه‌های عرضه شده را همراه با نام عرضه‌کنندگان‌شان بیابید زوج نام تکراری در پاسخ نیاید.

select pname, sname
from s, sp, p
where s.sn = sp.sn and
  p.pn = sp.pn
;

select distinct pname, sname
from s natural join sp
  join p using(pn)
;

    ╭───────┬───────╮
    │ pname │ sname │
    ╞═══════╪═══════╡
    │ Nut   │ Smith │
    │ Bolt  │ Smith │
    │ Screw │ Smith │
    │ Cam   │ Smith │
    │ Cog   │ Smith │
    │ Nut   │ Jones │
    │ Bolt  │ Jones │
    │ Bolt  │ Blake │
    │ Bolt  │ Clark │
    │ Screw │ Clark │
    │ Cam   │ Clark │
    │ Bolt  │ Ali   │
    ╰───────┴───────╯

نام قطعاتی را بیابید که وزن‌شان دست کم از وزن یک قطعهٔ با رنگ قرمز کمتر باشد

select distinct T.pname
from p as T, p
where p.weight > T.weight
  and p.color='Red'
;

select distinct T.pname
from p as T join p on
  p.weight > T.weight
where p.color='Red'
;

pname
Nut
Bolt
Screw
Cam

select distinct p.pname
from p as p1 join p on
  p1.weight > p.weight and
  p1.color = 'Red'
;

LIKE

نام شهرهای قطعاتی را بیابید که با P آغاز شده باشد

select city
from p
where city like 'P%'
;

نام قطعاتی را بیابید که کاراکتر دوم نام‌شان o باشد.

select pname
from p
where city like '_o%'
;

نام شهر قطعاتی را بیابید که درون نام شهر آنها رشتهٔ is وجود داشته باشد

select city
from p
where city like '%is%'
;

نام قطعات و شهرهای آنها را بیابید که شهر آنها دست کم سه‌حرفی باشند و با رشتهٔ _bn آغاز شود.

select pname, city
from p
where city like 'bn\_%'
;

escape

select pname
from p
where city like 'P\_%' escape '\'
;

select pname
from p
where city like 'P!_%' escape '!'
;

select pname
from p
where city like 'P#_%' escape '#'
;

select pname
from p
where city like "an\_%" escape "\"
; -- "

ORDER BY

نام قطعاتی را بیابید که در شهر پاریس باشند و پاسخ بر پایهٔ نام قطعه از کوچک به بزرگ مرتب شده باشد.

select pname
from p
where city='Paris'
order by pname
;

نام و وزن قطعاتی را بیابید که در شهر پاریس هستند و پاسخ بر پایهٔ وزن قطعه از کوچک به بزرگ مرتب شده باشد

select pname, weight
from p
where city='Paris'
order by weight
;

select pname, weight
from p
where city='Paris'
order by weight asc
;

    ╭───────┬────────╮
    │ pname │ weight │
    ╞═══════╪════════╡
    │ Bolt  │ NULL   │
    │ Cam   │     12 │
    │ Bolt  │     17 │
    ╰───────┴────────╯

نام و وزن قطعاتی را بیابید که در شهر پاریس هستند و پاسخ بر پایهٔ وزن قطعه از بزرگ به کوچک مرتب شده باشد

select pname, weight
from p
where city='Paris'
order by weight desc
;

pname	weight
Bolt	17
Cam	12
Bolt

select pname, weight
from p
where city='Paris' and weight is not null
order by weight desc
;

pname	weight
Bolt	17
Cam	12

BETWEEN

نام و وزن قطعاتی را بیابید که وزن‌شان بین ۱۲ و ۱۴ باشد

pname	weight
Nut	12
Screw	14
Cam	12

select pname, weight
from p
where weight >= 12 and weight <= 14
;

select pname, weight
from p
where weight between 12 and 14;

نام و وزن قطعاتی را بیابید که وزن‌شان بین ۱۲ و ۱۴ نباشد

pname	weight
Bolt	17
Screw	17
Cog	19

select pname, weight
from p
where not (weight >= 12 and weight <= 14)
;

select pname, weight
from p
where weight not between 12 and 14
;

select pname, weight
from p
where weight < 12 or weight > 14
;

نام قطعاتی را بیاید که عرضه کننده‌ای در شهر آن قطعه‌ها آنها را عرضه کرده باشد

Record Comparison

select pname
from p, s, sp
where p.city = s.city and
  p.pn = sp.pn and
  s.sn = sp.sn
;

select pname
from p, s, sp
where (p.city, p.pn) = (s.city, sp.pn)
  and s.sn = sp.sn
;

select pname
from p join s on
  p.city = s.city
  join sp on
  (p.pn, s.sn) = (sp.pn, sp.sn)
;

select pname
from p natural join sp natural join s
;

Union

نام قطعاتی از شهر پاریس را بیابید که وزن آن‌ها بیشتر از ۱۲ است.

select distinct pname
from p
where city = 'Paris' or
  weight > 12;

  select pname
  from p
  where city='Paris'
union
  select pname
  from p
  where weight>12;

pname
Bolt
Cam
Cog
Screw

  select pname
  from p
  where city = 'kashan'
union all
  select pname
  from p
  where weight>10
 ;

pname
Nut
Bolt
Screw
Screw
Cam
Cog

Style of Writing

  select pname
  from p
  where city='Paris'
union
  select pname
  from p
  where weight>12
;

select pname
from p
where city='kashan'
union
select pname
from p
where weight>10
;

select pname
from p
where city='kashan'

union

select pname
from p
where weight>10
;

Intersect

  select pname
  from p
  where city='Paris'
intersect
  select pname
  from p
  where weight>10
;

select distinct pname
from p
where city='Paris' and
  weight>10
;

pname
Bolt
Cam

  select pname
  from p
  where city = 'Paris'
intersect all -- sqlite error
  select pname
  from p
  where weight > 10
;

select pname
from p
where city='Paris' and
  weight>10
;

pname
Bolt
Cam

Except

  select pname
  from p
  where city = 'Paris'
except
  select pname
  from p
  where weight > 14
;

select distinct pname
from p
where city='Paris' and
  weight<=14
;

pname
Cam

select pname
  from p
  where city='Paris'
except all -- sqlite error
  select pname
  from p
  where weight>10
;

select pname
from p
where city='Paris' and
  weight<=14
;

pname
Cam

نام شهرهای قطعاتی را بیابید که در آنها عرضه‌کننده‌ای وجود ندارد

select city
from p
except
select city
from s
;

city
Oslo

شمارهٔ قطعات و شمارهٔ عرضه‌کنندگانی را بیابید که قطعات یاد شده را آن عرضه کنندگان عرضه نکرده باشند

1 select pn, sn
2 from p, s
3 except
4 select pn, sn
5 from sp;

1 select distinct p.pn, s.sn
2 from p, s, sp         -- incorrect
3 where (s.sn, p.pn) <> (sp.sn, sp.pn)
4 ; -- s1, p2

1 select * from (
2     select pn, sn
3     from p, s
4   except
5     select pn, sn
6     from sp
7 ) order by pn,sn;

1 select distinct p.pn, s.sn
2 from p, s, sp -- incorrect
3 where (s.sn, p.pn) <> (sp.sn, sp.pn)
4 order by sp.pn,sp.sn
5 ; -- s1, p2

1 select pn,sn
2 from sp order by pn,sn
3 ; -- s1, p2

نام قطعات و نام عرضه‌کنندگانی را بیابید که قطعات یاد شده را آن عرضه کنندگان عرضه نکرده باشند

select pname, sname  -- نادرست
from p, s
except
select pname, sname
from p natural join sp
  natural join s;

select pname, sname from p, s
except
select pname, sname
from s natural join sp
  join p using(pn);

select sname , pname
from (
  select pn, sn from p, s
  except
  select pn, sn from sp
  ) join p using (pn)
  join s using (sn);

pname	sname
Bolt	Adams
Cam	Adams
Cam	Ali
Cam	Blake
Cam	Jones
Cog	Adams
Cog	Ali
Cog	Blake

pname	sname
Cog	Clark
Cog	Jones
Nut	Adams
Nut	Ali
Nut	Blake
Nut	Clark
Screw	Adams
Screw	Ali
Screw	Blake
Screw	Jones

زوج نام عرضه‌کنندگانی را بیابید که در یک شهر باشند و پاسخ تکراری نداشته باشید

-- (1) نادرست
select s.sname, T.sname
from s, s as T
where s.city = T.city
;

-- (2) نادرست
select s.sname, T.sname
from s, s as T
where s.city = T.city and
  s.sn != T.sn
;

-- (3)
select s.sname, T.sname
from s, s as T
where s.city = T.city and
  s.sn < T.sn
;

-- (4)
select s.sname, T.sname
from s as T join s using(city)
where s.sn < T.sn
;

-- (5)
select s.sname, T.sname
from s as T join s on
  T.city = s.city and
  s.sn < T.sn
;

sname	sname
Smith	Clark
Jones	Blake

LIMIT

select distinct city
from p
order by weight, city
;

city
London
Oslo
Paris

select distinct city
from p
order by weight, city
limit 2
;

city
London
Oslo

Scalar value(I)

شماره و وزن قطعاتی را بیابید که کمترین وزن را داشته باشند.

1 select pn, weight  -- incorrect
2 from p
3 order by weight asc
4 limit 1
5 ;

pn	weight
P7	NULL

1 select pn, weight
2 from p
3 where weight is not null
4 order by weight asc
5 limit 1
6 ;

pn	weight
p1	12

1 select weight
2 from p
3 where weight is not null
4 order by weight asc
5 limit 1
6 ;

weight
12

شماره و وزن قطعاتی را بیابید که کمترین وزن را داشته باشند.

select pn, weight
from p   -- incorrect
where weight = (
    select weight
    from p
    order by weight asc
    limit 1
);

select pn, weight
from p
where weight = (
    select weight
    from p
    where weight is not null
    order by weight asc
    limit 1
);

1 select pn, 1 as qt
2 from p
3 where city = 'Paris'
4 ;

    ╭────┬────╮
    │ pn │ qt │
    ╞════╪════╡
    │ p2 │  1 │
    │ p5 │  1 │
    │ p8 │  1 │
    ╰────┴────╯

 1 select pn,  (
 2     select weight
 3     from p
 4     where weight is not null
 5     order by weight asc
 6     limit 1
 7   ) as qt
 8 from p
 9 where city = 'Paris' ;

    ╭────┬────╮
    │ pn │ qt │
    ╞════╪════╡
    │ p2 │ 12 │
    │ p5 │ 12 │
    │ p8 │ 12 │
    ╰────┴────╯

Update(I)

update p
set weight = weight + 15
where city = 'Oslo'
;

update s
set status = status * 2,
    city = 'kashan'
where city = 'London'
;

update employees
set email = LOWER(
    firstname || "." ||
    lastname || "@chinookcorp.com"
);

update P
set weight = null
where pn='p6'
;

Update(II)

update s
set
  status = case
    when city = 'london' then status * 2
    else status
  end;

update s
set
  status = case
  when city = 'London' then status * 2
  when city = 'Paris'  then status * 3
  else status
end;

update s
set
  status = case
    when city = 'London' then status / 4
    when city = 'Paris'  then status / 3
    else status
  end;

update s
set city = 'kashan',
    status = case
      when status > 20 then 53
      else status
    end
where sname = 'Smith'
returning sn, sname;

Update or Replace

1 CREATE TABLE users (
2     id INTEGER PRIMARY KEY,
3     email CHAR(20) UNIQUE,
4     name CHAR(15)
5 );

1 INSERT INTO users VALUES
2     (1, 'ada@x.com', 'Ada'),
3     (2, 'boris@x.com', 'Boris');

1 -- This would fail with a UNIQUE
2 -- constraint violation:
3 
4 UPDATE users SET email = 'boris@x.com'
5 WHERE id = 1;

1 -- OR REPLACE deletes the conflicting row,
2 -- then performs the update.
3 UPDATE OR REPLACE users
4 SET email = 'boris@x.com'
5 WHERE id = 1;

1 SELECT * FROM users;-

    ╭────┬─────────────┬──────╮
    │ id │    email    │ name │
    ╞════╪═════════════╪══════╡
    │  1 │ boris@x.com │ ada  │
    ╰────┴─────────────┴──────╯

Data Types

Numeric
- TINYINT , 1 byte
- SMALLINT , 2 bytes
- INT , 4 bytes
- BIGINT , 8 bytes
- MONEY , 8 bytes
- FLOAT , 1.79E+308
- REAL , 3.40E+38
- DECIMAL(size, d) , 10^38
- NUMERIC(size, d) , 10^38
- DOUBLE(size, d) , 1.79E+308
Character and String
- CHAR (n)
- VARCHAR (n)
- TEXT
- NCHAR (n)
- NVARCHAR (n)
- NTEXT

Date and Time
- DATE , YYYY-MM-DD
- TIME , HH:mm:ss
- DATETIME , YYYY-MM-DD HH:mm:ss
- TIMESTAMP, 1970-01-01 00:00:00
- INTERVAL
Binary
- BINARY (n)
- VARBINARY (n)
- BLOB
Miscellaneous
- CLOB
- BLOB
- XML
- CURSOR
- TABLE
- JSON (PostgreSQL jsonb)
- BOOLEAN
- ARRAY (PostgreSQL)

SQLite Types

Datatypes
- NULL :
- INTEGER: 0 - 8 bytes
- REAL : 8-byte IEEE
- TEXT : UTF-8 or UTF-16
- BLOB :
Boolean
- 0 false
- 1 true
Date and Time
- TEXT, YYYY-MM-DD HH:MM:SS.SSS
- REAL, day numbers
- INTEGER, timestamp
Any
Affinity
1. INT → INTEGER
2. CHAR, CLOB, TEXT → TEXT
3. BLOB → BLOB
4. REAL, FLOAT, DOUBLE → REAL
5. Otherwise, NUMERIC.

Example Typenames From The CREATE TABLE Statement or CAST Expression	Resulting Affinity	Rule Used To Determine Affinity
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT UNSIGNED BIG INT, INT2, INT8	INTEGER	1
CHARACTER(20), VARCHAR(255) VARYING CHARACTER(255) NCHAR(55), NATIVE CHARACTER(70) NVARCHAR(100), TEXT, CLOB	TEXT	2
BLOB no datatype specified	BLOB	3
REAL, DOUBLE DOUBLE PRECISION, FLOAT	REAL	4
NUMERIC, DECIMAL(10,5), BOOLEAN DATE, DATETIME	NUMERIC	5

String Functions

ltrim(' abc ')
- 'abc '
rtrim(' abc ')
- ' abc'
trim(' abc ')
- 'abc'
upper('sql')
- SQL
lower('SQL')
- sql
concate('SQL', 'ite')
- SQLite
'SQL' || 'ite'
- SQLite
concate('SQL', NULL, 'ite')
- SQLite

1
2
3
4
5
6
S
Q
L
i
t
e
-6
-5
-4
-3
-2
-1
substr('SQLite', 2, 4)
- QLit
substr('SQLite', 4)
- ite
substr('SQLite', -4)
- Lite
substr(NULL, 4)
- NULL
substr('SQLite')
- Parse error:
substring
instr('yo@a.com', '@')
- 3
PostgreSQl position
position('om' in 'Thomas')
- 3

format('%s is %d', 'Ada', 30)
- Ada is 30
- printf
length('abc')
- 3
length(NULL)
- NULL
PostgreSQl
- char_length
PostgreSQl String Functions
substring('Thomas' from 2 for 3)
- hom
substring('Thomas' from 3)
- omas
substring('Thomas' for 2)
- Th
replace('acdefcdb', 'cd', 'YY')
- aYYefYYb

Date and Time I

DATE(): YYYY-MM-DD
TIME(): HH:MM:SS
DATETIME(): YYYY-MM-DD HH:MM:SS
JULIANDAY(): floating-point
UNIXEPOCH(): seconds 1970-01-01
STRFTIME():

1 SELECT date('2024-06-01', '+1 day')
2   AS next_day,
3   date('2024-06-01', '+1 month')
4   AS next_month,
5   date('2024-06-01', '-1 month')
6   AS last_month;

1 SELECT
2   (julianday('2024-06-15') -
3     julianday('2024-06-01')
4   ) AS days_difference;

1 SELECT * FROM events
2 WHERE event_date >= '2024-06-01'
3   AND event_date < '2024-07-01';

1 SELECT datetime(1717231200, 'unixepoch')
2   AS human_readable_date,
3   strftime('%s', '2024-06-01 14:00:00')
4   AS unix_epoch;

 1 CREATE TABLE events (
 2     id INTEGER PRIMARY KEY,
 3     event_name TEXT NOT NULL,
 4     event_timestamp INTEGER NOT NULL
 5 );
 6 INSERT INTO events VALUES
 7   (1, 'Meeting',
 8     strftime('%s', '2024-06-01 14:00:00')),
 9   (2, 'Conference',
10     strftime('%s', '2024-06-15 09:30:00'));
11 SELECT event_name,
12   datetime(event_timestamp, 'unixepoch') AS event_date
13 FROM events;

Date and Time II

 1 CREATE TABLE book (
 2     bn INT PRIMARY KEY,
 3     title VARCHAR(50) NOT NULL,
 4     author VARCHAR(50) NOT NULL,
 5     ofpd DECIMAL(5, 2) NOT NULL DEFAULT 0.50
 6 );
 7 CREATE TABLE member (
 8     mn INT PRIMARY KEY,
 9     name VARCHAR(50) NOT NULL,
10     bn INT, -- Favorite book
11     fines DECIMAL(10, 2) DEFAULT 0.00,
12     FOREIGN KEY (bn) REFERENCES book(bn)
13 );
14 CREATE TABLE borrow (
15     bn INT,
16     mn INT,
17     ddt DATE, -- Due date
18     dtr DATE, -- Date returned
19     -- (NULL means still borrowed)
20     PRIMARY KEY (bn, mn, ddt),
21     FOREIGN KEY (bn) REFERENCES book(bn)
22     FOREIGN KEY (mn) REFERENCES member(mn)
23 );

 1 INSERT INTO book (bn, title, author, ofpd) VALUES
 2 (101, 'Dune', 'Frank Herbert', 0.50),
 3 (102, 'Ghazal of Hafez', 'Hafez Shirazi', 1.25),
 4 (103, 'Foundation', 'Isaac Asimov', 0.50),
 5 (104, 'The Hobbit', 'J.R.R. Tolkien', 0.25),
 6 (105, 'We', 'Yevgeny Zamyatin', 1.00);
 7 
 8 INSERT INTO member (mn, name, bn, fines) VALUES
 9 -- Alice's favorite is Dune
10 (1, 'Alice Smith', 101, 0.00),
11 -- Bob's favorite is The Hobbit
12 (2, 'Bob Johnson', 104, 2.50),
13 -- Charlie hasn't picked a favorite yet
14 (3, 'Charlie Davis', NULL, 0.00);
15 
16 INSERT INTO borrow (bn, mn, ddt, dtr) VALUES
17 -- Alice borrowed Dune and returned it early
18 (101, 1, '2023-10-01', '2023-09-28'),
19 -- Bob borrowed The Hobbit and returned it 5 days late
20 (104, 2, '2023-10-15', '2023-10-20'),
21 -- Alice borrowed We and hasn't returned it yet (NULL)
22 (105, 1, '2023-11-01', NULL),
23 -- Charlie borrowed Hafez and hasn't returned it yet
24 (102, 3, '2023-11-05', NULL);

Math Functions
- acosh(X)
- asin(X)
- atanh(X)
- ceil(X)ceiling(X)
- cos(X)
- exp(X)
- floor(X)
- ln(X)
- log(X) log10(X)
- log(B,X)
- log2(X)
- mod(X,Y)
- pi()
- pow(X,Y) power(X,Y)
- sin(X)
- sqrt(X)
- tan(X)
- trunc(X)

Check I

1 create table s (
2   sn      char(10) primary key,
3   sname   char(30) not null,
4   status  int check(status >= 10),
5   city    char(20) default 'Shiraz',
6   unique(sname, city)
7 );

1 create table sp (
2    sn    char(10) references s,
3    pn    char(10) references p,
4    qty   int check(qty > 0),
5    primary key (sn, pn)
6 );

1 create table p (
2   pn     char(10) primary key,
3   pname  char(30) not null,
4   color  char(20),
5   weight numeric(9, 2)
6       check(weight > 2 and weight < 90000),
7   city   char(20)
8 );

1 CREATE TABLE t (
2   a NUMERIC CHECK (a >= 0),
3   b NUMERIC CHECK (b >= 0),
4   CHECK ( a + b <= 10 )
5 );

Check II

 1 CREATE TABLE people (
 2   id  INTEGER PRIMARY KEY,
 3   age INTEGER CHECK (age >= 0)
 4 );
 5 INSERT INTO people (id, age) VALUES
 6 (1,25);
 7 INSERT INTO people (id, age) VALUES
 8 (2,NULL);
 9 INSERT INTO people (id, age) VALUES
10 (3,-5);

 1 CREATE TABLE employees (
 2   id     INTEGER PRIMARY KEY,
 3   name   CHAR(20) NOT NULL,
 4   salary REAL NOT NULL,
 5   bonus  REAL NOT NULL DEFAULT 0,
 6   CONSTRAINT salary_positive
 7     CHECK (salary > 0),
 8   CONSTRAINT bonus_not_negative
 9     CHECK (bonus >= 0),
10   CONSTRAINT bonus_not_huge
11     CHECK (bonus <= salary)
12 );
13 INSERT INTO employees VALUES
14 (1, 'Ada', 60000, 70000);

 1 CREATE TABLE users (
 2   id       INTEGER PRIMARY KEY,
 3   email    CHAR(20)
 4     CHECK(email LIKE '%_@_%.__%'),
 5   username CHAR(20)
 6     CHECK(length(username) BETWEEN 3 AND 20),
 7   country  CHAR(20)
 8     CHECK(country = upper(country))
 9 );
10 INSERT INTO users VALUES
11   (1, 'ada@example.com', 'ada', 'US');
12 INSERT INTO users VALUES
13   (2, 'not-an-email', 'ada2', 'US');
14 INSERT INTO users VALUES
15   (3, 'boris@x.io', 'b', 'US');
16 INSERT INTO users VALUES
17   (4, 'cara@x.io', 'cara', 'us');

Transaction(I)

1 CREATE TABLE accounts (
2     account_no INTEGER,
3     balance DECIMAL DEFAULT 0,
4     PRIMARY KEY(account_no),
5       CHECK(balance >= 0)
6 );

1 UPDATE accounts
2  SET balance = balance - 1000
3 WHERE account_no = 100;
4 
5 UPDATE accounts
6  SET balance = balance + 1000
7 WHERE account_no = 200;

 1 BEGIN TRANSACTION;
 2 
 3 UPDATE accounts
 4  SET balance = balance - 1000
 5 WHERE account_no = 100;
 6 
 7 UPDATE accounts
 8  SET balance = balance + 1000
 9 WHERE account_no = 200;
10 
11 
12 COMMIT;

 1 BEGIN TRANSACTION;
 2 
 3 UPDATE accounts
 4  SET balance = balance - 1000
 5 WHERE account_no = 100;
 6 
 7 UPDATE accounts
 8  SET balance = balance + 1000
 9 WHERE account_no = 200;
10 
11 
12 ROLLBACK;

Transaction(II)

CREATE TABLE accounts (
  account_no INTEGER NOT NULL,
  balance DECIMAL NOT NULL DEFAULT 0,
  PRIMARY KEY(account_no),
             CHECK(balance >= 0)
);

INSERT INTO accounts values
  (100, 5000),
  (200, 2000),
  (300, 4000);

CREATE TABLE account_changes (
  change_no INT NOT NULL PRIMARY KEY,
  account_no INTEGER NOT NULL,
  flag TEXT NOT NULL,
  amount DECIMAL NOT NULL,
  changed_at DATETIME NOT NULL,
  foreign key (account_no)
      references accounts(account_no)
);

BEGIN TRANSACTION;

UPDATE accounts
 SET balance = balance - 1000
WHERE account_no = 100;

UPDATE accounts
 SET balance = balance + 1000
WHERE account_no = 200;

INSERT INTO account_changes
VALUES(10, 100,'-',1000,datetime('now'));

INSERT INTO account_changes
VALUES(11, 200,'+',1000,datetime('now'));

COMMIT;

Auto commit

Transaction(III)

 1 CREATE TABLE counter(n INTEGER);
 2 INSERT INTO counter VALUES (1);
 3 
 4 BEGIN;
 5 SELECT n FROM counter; -- 1
 6 UPDATE counter SET n = 99;
 7 SELECT n FROM counter;
 8 -- 99, your own write is visible
 9 COMMIT;
10 
11 SELECT n FROM counter;

BEGIN DEFERRED(default)
BEGIN IMMEDIATE
BEGIN EXCLUSIVE

DDL inside transactions works
VACUUM cannot run inside a transaction
A failed COMMIT is still a real failure
Long transactions block writers

Savepoint

 1 CREATE TABLE accounts
 2     (name TEXT, balance INTEGER);
 3 INSERT INTO accounts VALUES
 4     ('Ada', 100), ('Boris', 100);
 5 
 6 BEGIN;
 7   UPDATE accounts
 8   SET balance = balance - 30
 9   WHERE name = 'Ada';
10 
11   SAVEPOINT risky;
12     UPDATE accounts
13     SET balance = balance + 30
14     WHERE name = 'Nobody';
15 
16   ROLLBACK TO risky;
17 
18   UPDATE accounts
19   SET balance = balance + 30
20   WHERE name = 'Boris';
21 
22 COMMIT;
23 
24 SELECT * FROM accounts;

 1 CREATE TABLE log (msg TEXT);
 2 
 3 BEGIN;
 4   INSERT INTO log VALUES ('start');
 5 
 6   SAVEPOINT step1;
 7     INSERT INTO log VALUES ('step 1 done');
 8   RELEASE step1;
 9 
10   SAVEPOINT step2;
11     INSERT INTO log VALUES ('step 2 attempt');
12   ROLLBACK TO step2;
13     INSERT INTO log VALUES ('step 2 retry');
14   RELEASE step2;
15 COMMIT;

COMMIT always commits the whole transaction
ROLLBACK (without TO) aborts everything
A savepoint stays open until released or rolled back through
Names aren't required to be unique

Vacuum

vacuum;

vacuum full;

pragma auto_vacuum = full;
pragma auto_vacuum = incremental;
pragma auto_vacuum = none;

Alter Table

DDL

alter table sp add "comment" varchar(50);

alter table sp drop "comment";

alter table sp add "comment" varchar(50) default '';

Alter table Foreign key

MySQL / SQL Server / Oracle / MS Access

alter table Orders
add constraint FK_PersonOrder
foreign key (PersonID) references Persons(PersonID);

END

Unique(I)

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

create table "student"(
  "SSN" varchar(20) unique not null,
  "name" varchar(40) not null,
  "student_number" bigint Primary key
  );

insert into
  "student"("SSN", "name", "student_number")
values
("38947389", "کامران خداپرستی", 973433),
("38472389", "کوروش پارسایی", 9632847),
("38947389", ")احمد یوسفان", 93802932);

Unique(II)

create table contacts(
    contact_id integer primary key,
    first_name text,
    last_name text,
    email text not null UNIQUE
);

create table shapes(
  shape_id integer primary key,
  background_color text,
  foreground_color text,
  UNIQUE(background_color,foreground_color)
);

ALTER TABLE Persons
ADD UNIQUE (ID);

ALTER TABLE Persons
ADD CONSTRAINT UC_Person
  UNIQUE (ID,LastName);

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

Unique(III)

create table contacts (
  contact_id integer primary key,
  first_name text    not null,
  last_name  text    not null,
  email      text,
  phone      text    not null
    check (length(phone) >= 10)
);

create table products (
  product_id   integer         primary key,
  product_name text            not null,
  list_price   DECIMAL (10, 2) not null,
  discount     DECIMAL (10, 2) not null
                              default 0,
  check (list_price >= discount and
      discount >= 0 and
      list_price >= 0)
);