Using the aliases p1 and p2 to refer to the book table different ways : Simple Select « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. Simple Select

Using the aliases p1 and p2 to refer to the book table different ways

        
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.00 sec)

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> 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> SELECT p2.title
    -> FROM book AS p1, book AS p2
    -> WHERE p1.title = 'XML'
    -> AND p1.a_id = p2.a_id;
+-------+
| title |
+-------+
| MySQL |
| XML   |
| Java  |
+-------+
3 rows in set (0.00 sec)

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

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

   
    
    
    
    
    
    
    
  

Related examples in the same category