Join the states table to the book table to map state abbreviations to full names : Full Join « Join « SQL / MySQL

1. SQL / MySQL
2. Join
3. Full Join

Join the states table to the book table to map state abbreviations to full names

    
mysql>
mysql> CREATE TABLE book
    -> (
    ->  a_id    INT UNSIGNED NOT NULL,                                  # author ID
    ->  p_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,   # book ID
    ->  title   VARCHAR(100) NOT NULL,                                  # title of book
    ->  state   VARCHAR(2) NOT NULL,                                    # state where purchased
    ->  price   INT UNSIGNED,                                                   # purchase price (dollars)
    ->  INDEX (a_id),
    ->  PRIMARY KEY (p_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE states
    -> (
    ->  name            VARCHAR(30) NOT NULL,   # state name
    ->  abbrev          CHAR(2) NOT NULL,               # 2-char abbreviation
    ->  statehood       DATE,                                   # date of entry into the Union
    ->  pop                     BIGINT,                                 # population as of 4/1990
    ->  PRIMARY KEY (abbrev)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> SELECT book.title, states.name AS state
    -> FROM book, states
    -> WHERE book.state = states.abbrev
    -> ORDER BY state;
Empty set (0.00 sec)

mysql>
mysql> drop table states;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table book;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
  

Related examples in the same category