Three SQL things I learned (from an old book)

Posted on Tuesday, September 30, 2014


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


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