I recently learned a
few SQL tidbits I never knew before because reading a very old Java Book
written in 1999, Java Enterprise in a nutshell.
I picked up the book at a used bookstore for $0.50 and figured I would
learn some of the problems Java was trying to solve in 1999 (when I honestly
should have been reading books like… but I was too dim to be).
Well I ended up learning
three things about SQL I did not know.
These are very common SQL things… so no ground breaking research here.
First I need a database
I have an install on
Ubuntu 14.04 on which I am going to install Postgres. The current default install of postgres
is 9.3.5
From the command
line run
|
> sudo
apt-get update
>
sudo apt-get upgrade
> sudo
apt-get install postgresql postgresql-contrib
|
To confirm its
installed run the following (switch to the postgres user)
|
> sudo
su postgres
> psql -U postgres
|
Here you can see you
are running version 9.3.5
To quit postgres
just run the command
|
> \q
|
From the command
line you could have checked the version of Postgres by running the following
command.
|
> psql --version
|
Create a Database
Log into the database
|
> sudo
su postgres
>
psql -U postgres
|
Create the database
"TEST_DB" and switch to it.
|
> create
database test_db;
> \c
test_db
|
Create the following
three tables.
CUSTOMER
CUSTOMER_ORDER
ORDER_ITEM
ORDER_ITEM
Run the following
SQL to create the tables.
|
CREATE TABLE CUSTOMER
(
CUSTOMER_ID serial PRIMARY KEY,
NAME
VARCHAR(255) NOT NULL,
PHONE
VARCHAR(64)
);
|
|
CREATE
TABLE CUSTOMER_ORDER
(
ORDER_ID serial PRIMARY KEY,
CUSTOMER_ID INT REFERENCES CUSTOMER
(CUSTOMER_ID) ON UPDATE CASCADE ON DELETE CASCADE,
TOTAL DECIMAL(10, 2) NOT NULL
);
|
|
CREATE TABLE ORDER_ITEM
(
ORDER_ID
INT REFERENCES CUSTOMER_ORDER (ORDER_ID) ON UPDATE CASCADE ON DELETE CASCADE,
ITEM_NO
INT,
COST
DECIMAL(10, 2) NOT NULL
);
|
No run
|
> \dt
|
And there are our
tables.
Run the following to
put some data into them.
|
begin;
insert into CUSTOMER (name, phone) values ('Jeff Jones',
'720-555-1234');
insert into CUSTOMER (name, phone) values ('Paul Parker',
'720-555-2234');
insert into CUSTOMER (name, phone) values ('Bob Bailey',
'720-555-3234');
insert into CUSTOMER (name, phone) values ('Gill Gardner',
'720-555-4234');
insert into CUSTOMER (name, phone) values ('Matt McDoogle',
'720-555-5234');
insert into CUSTOMER (name, phone) values ('Kate Kensington',
'720-555-6234');
insert into CUSTOMER (name, phone) values ('Cindy Cunnings',
'720-555-7234');
commit;
|
|
begin;
insert into CUSTOMER_ORDER (customer_id, total) values (4, 48.03);
insert into CUSTOMER_ORDER (customer_id, total) values (6, 16.27);
insert into CUSTOMER_ORDER (customer_id, total) values (7, 5.11);
insert into CUSTOMER_ORDER (customer_id, total) values (1, 72.19);
insert into CUSTOMER_ORDER (customer_id, total) values (3, 53.17);
insert into CUSTOMER_ORDER (customer_id, total) values (1, 21.07);
insert into CUSTOMER_ORDER (customer_id, total) values (5, 37.62);
commit;
|
|
begin;
insert into ORDER_ITEM (ORDER_ID, ITEM_NO, COST) values (2, 4012,
12.05);
insert into ORDER_ITEM (ORDER_ID, ITEM_NO, COST) values (2, 6719,
4.22);
insert into ORDER_ITEM (ORDER_ID, ITEM_NO, COST) values (3, 6719, 5.11);
insert into ORDER_ITEM (ORDER_ID, ITEM_NO, COST) values (4, 6719,
16.72);
insert into ORDER_ITEM (ORDER_ID, ITEM_NO, COST) values (4, 6719,
41.10);
insert into ORDER_ITEM (ORDER_ID, ITEM_NO, COST) values (4, 6719,
14.37);
insert into ORDER_ITEM (ORDER_ID, ITEM_NO, COST) values (5, 6719,
16.72);
insert into ORDER_ITEM (ORDER_ID, ITEM_NO, COST) values (5, 6719,
17.21);
commit;
|
Underscores in LIKE (first thing I learned)
Now I have some data to query against! The first thing I learned was that you can
use underscores in LIKE.
I am use to creating queries like the following.
|
select * from customer_order where cast(total as TEXT)
LIKE'%.1%';
|
Which results in
The % is just a
wildcard. SQL has underscore '_' which
will match any single character. So I
could write this query.
|
select * from customer_order where cast(total as TEXT)
LIKE'__.1_';
|
Which gets me
slightly different results.
Since the query
requires two characters before the '.'
not just a wildcard.
I can see me using
this underscore feature from time to time.
IN Operator (second thing I learned)
Details about the IN
operator can be found at http://www.w3schools.com/sql/sql_in.asp [1]
For some reason I never use the IN operator (Maybe I just
was never aware). I would typically
write a long winded query like this.
|
select * from customer_order
where
total = 72.19
or
total = 53.17
or
total = 5.11;
|
This query works but
it’s a lot of typing
Instead I could have
used IN to shorten it up a bit and make it clearer to read.
|
select * from customer_order where total IN (72.19, 53.17, 5.11);
|
JOIN Operator (third thing I learned)
Details about the
JOIN operator can be found at http://www.w3schools.com/sql/sql_join.asp [2]
OK, this operator I
knew about but I was missing out on some aspects of it.
When I wanted to
join several tables together and query them I would do something like the
following
|
select * from CUSTOMER, CUSTOMER_ORDER
where CUSTOMER.customer_id = CUSTOMER_ORDER.customer_id;
|
Resulting in
Or something like this.
|
select *
from CUSTOMER, CUSTOMER_ORDER, ORDER_ITEM
where CUSTOMER.customer_id = CUSTOMER_ORDER.customer_id
and CUSTOMER_ORDER.order_id = ORDER_ITEM.order_id;
|
Resulting in
OK here is where the book misled me, but it lead me to do
some research of my own and figure a few things out.
It seemed to suggest I could do the following
|
select * from CUSTOMER_ORDER JOIN CUSTOMER;
|
And that it would do an inner join and match do it on a
column that both tables share, in this case CUSTOMER_ID. But that did not pan out.
Before I go on too far a good site to understand SQL joins
visually is
I want to do an INNER JOIN on my tables using the JOIN
keyword I can accomplish this in a few ways.
|
select * from CUSTOMER_ORDER INNER JOIN CUSTOMER
ON
customer_order.customer_id = customer.customer_id;
|
I can drop the INNER (the default type of a join is an INNER
JOIN)
|
select * from CUSTOMER_ORDER JOIN CUSTOMER
ON
customer_order.customer_id = customer.customer_id;
|
If you have the case where the column name you are joining
on are identical you can save a few keystrokes and use USING.
|
select * from CUSTOMER_ORDER JOIN CUSTOMER
USING
(customer_id);
|
Here are some ways to join all three tables using JOIN.
|
select * from ORDER_ITEM
INNER
JOIN CUSTOMER_ORDER
ON
ORDER_ITEM.order_id = CUSTOMER_ORDER.order_id
INNER
JOIN CUSTOMER
ON
CUSTOMER_ORDER.customer_id = CUSTOMER.customer_id;
|
You can of course remove the INNER
|
select * from ORDER_ITEM
JOIN
CUSTOMER_ORDER
ON
ORDER_ITEM.order_id = CUSTOMER_ORDER.order_id
JOIN
CUSTOMER
ON
CUSTOMER_ORDER.customer_id = CUSTOMER.customer_id;
|
And finally you can use USING
|
select * from ORDER_ITEM
JOIN
CUSTOMER_ORDER USING(order_id)
JOIN
CUSTOMER USING(customer_id);
|
Wrap up
Just goes to show you that you always learn something from a
good book (even an outdated one). I
picked up an old Java book and learned something about SQL.
References
[1] W3schools SQL IN
Operator
Visited 9/2014
[2] W3schools SQL
JOIN Operator
Visited 9/2014
[3] Understanding
JOINs in MySQL and Other Relational Databases
Visited 9/2014
No comments:
Post a Comment