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
Enter the password to open this PDF file:
File name:
-
File size:
-
Title:
-
Author:
-
Subject:
-
Keywords:
-
Creation Date:
-
Modification Date:
-
Creator:
-
PDF Producer:
-
PDF Version:
-
Page Count:
-
Preparing document for printing…
0%
Σχόλια 0
Συνδεθείτε για να κοινοποιήσετε σχόλιο