To display the author who painted each book : And « Where Clause « SQL / MySQL






To display the author who painted each book

       
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> CREATE TABLE author
    -> (
    ->  a_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,   # author ID
    ->  name    VARCHAR(30) NOT NULL,                                   # author name
    ->  PRIMARY KEY (a_id),
    ->  UNIQUE (name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO author (name) VALUES
    ->  ('Tom'),
    ->  ('Monet'),
    ->  ('Jack'),
    ->  ('Picasso'),
    ->  ('Mary')
    -> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'Database', 'IN', 34 FROM author WHERE name = 'Tom';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'SQL', 'MI', 87 FROM author WHERE name = 'Tom';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'MySQL', 'KY', 48 FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'XML', 'KY', 67    FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'Java', 'IA', 33   FROM author WHERE name = 'Jack';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO book (a_id,title,state,price)
    ->  SELECT a_id, 'HTML', 'NE', 64   FROM author WHERE name = 'Mary';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> insert into states(name, abbrev, statehood, pop)values
    -> ("Alabama","AL","1819-12-14",4040587),
    -> ("Alaska","AK","1959-01-03",550043),
    -> ("Arizona","AZ","1912-02-14",3665228),
    -> ("Arkansas","AR","1836-6-15",2350725),
    -> ("California","CA","1850-9-9",29760021),
    -> ("Colorado","CO","1876-8-1",3294394),
    -> ("Connecticut","CT","1788-1-9",3287116),
    -> ("Delaware","DE","1787-12-7",666168),
    -> ("Florida","FL","1845-3-3",12937926),
    -> ("Georgia","GA","1788-1-2",6478216),
    -> ("Hawaii","HI","1959-08-21",1108229),
    -> ("Idaho","ID","1890-7-3",1006749),
    -> ("Illinois","IL","1818-12-3",11430602),
    -> ("Indiana","IN","1816-12-11",5544159),
    -> ("Iowa","IA","1846-12-28",2776755),
    -> ("Kansas","KS","1861-1-29",2477574),
    -> ("Kentucky","KY","1792-6-1",3685296),
    -> ("Louisiana","LA","1812-4-30",4219973),
    -> ("Maine","ME","1820-3-15",1227928),
    -> ("Maryland","MD","1788-4-28",4781468),
    -> ("Massachusetts","MA","1788-2-6",6016425),
    -> ("Michigan","MI","1837-1-26",9295297),
    -> ("Minnesota","MN","1858-5-11",4375099);
Query OK, 23 rows affected (0.00 sec)
Records: 23  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT author.name, book.title, states.name AS state
    -> FROM author, book, states
    -> WHERE author.a_id = book.a_id AND book.state = states.abbrev;
+------+----------+----------+
| name | title    | state    |
+------+----------+----------+
| Tom  | Database | Indiana  |
| Tom  | SQL      | Michigan |
| Jack | MySQL    | Kentucky |
| Jack | XML      | Kentucky |
| Jack | Java     | Iowa     |
+------+----------+----------+
5 rows in set (0.00 sec)

mysql>
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> drop table author;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
    
    
  








Related examples in the same category

1.Combine conditions
2.Use AND to combine conditions
3.AND and OR may be intermixed
4.Combine conditions in select clause
5.Use AND for int value
6.The AND construct means that both clauses must be true.
7.Define multiple conditions in a clause
8.retrieve the name of the customer placing order 4
9.Put the entire original expression in parentheses and negate the whole thing with NOT
10.Compare two conditions
11.And 1