Headers and Columns: sqlite> .headers ON sqlite> .mode columns Creating Temporary Views:

Headers and Columns:


sqlite> .headers ON

sqlite> .mode columns



Creating Temporary Views:


Get names and addresses of guests not arriving on 15 May.


Instead of :


sqlite> select Name, StreetNr, City


...> from Guest


...> where GuestNr not in (


.
..> select GuestNr


...> from Reservation


...> where ArrivalDate = "15 May");

Name


StreetNr City

----------

----------

----------

Carter 10 Main Hartford

Jones 6 Elm Hartford

Smith 4 Oak Prov
idence

Johnson 15 Main Boston


Create and use a view:


sqlite> create temp view May15Arrivals as


...> select GuestNr


...> from Reservation


...> where ArrivalDate = "15 May";

sqlite> select Name, StreetNr, City


...> from Guest


...> wher
e GuestNr not in May15Arrivals;

Name


StreetNr City

----------

----------

----------

Carter 10 Main Hartford

Jones 6 Elm Hartford

Smith 4 Oak Providence

Johnson 15 Main Boston


This is helpful for dev
eloping complex queries because you can get subparts of the query
to work without having to do the entire query. Then, if you want a single query, you can
plug in the view definition for the view.



The Operator “like” and Wild
-
Card Characters:


Get reser
vation information for reservations with arrival dates ending in zero.


sqlite> select *


...> from Reservation


...> where ArrivalDate like "_0%";

GuestNr RoomNr ArrivalDate NrDays

----------

----------

-----------

----------

101

1 10 May 2

101 2 20 May 1

102 3 10 May 5

104 4 10 May 2


The wild
-
card character “_” stands for a single character, and
“%” stands for zero or
m
ore characters.































Joins:


SQLite directly supports natural join.


Instead of:


sqlite> select


...> GuestNr, Guest.Name, StreetNr, City, RoomNr, NrBeds, Cost


...> from Guest, Room


...> where Guest.Name = Room.Name;

Gu
est.GuestNr Guest.Name Guest.StreetNr Guest.City Room.RoomNr Room.NrBeds

Room.Cost

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

----------

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

----------

-----------

-----------

----------

102 Carter

10 Main Hartford 3 2
80

105 Green 10 Main Boston 5 1
50


We can write:


sqlite> select

*


...> from Gue
st natural join Room;

Guest.GuestNr Guest.Name Guest.StreetNr Guest.City Room.RoomNr Room.NrBeds
Room.Cost

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

----------

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

----------

-----------

-----------

----------

102

Carter 10 Main Hartford 3 2
80

105 Green 10 Main Boston 5 1
50
















You can do a natural left outer join:


sqlite> select

*


...> from Guest natural left outer join Room;

Guest.GuestNr Guest.Name Guest.StreetNr Guest.City Room.RoomNr Room.NrBeds
Room.Cost

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

----------

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

----------

-----------

-----------

----------

101 Smith 12 Maple Boston



102 Carter 10 Main Hartford 3

2
80

103 Jones 6 Elm Hartford



104 Smith 4 Oak Providence



105

Green 10 Main Boston 5 1
50

106 Johnson 15 Main Boston




Unfortunately,
not all types of outer joins are supported:


sqlite> select distin
ct *


...> from Guest natural full outer join Room;

SQL error: RIGHT and FULL OUTER JOINs are not currently supported