Table join and insert : Insert « Insert Delete Update « SQL / MySQL

1. SQL / MySQL
2. Insert Delete Update
3. Insert

Table join and insert

     
mysql>
mysql> CREATE TABLE customer (
    ->   id int(11) default NULL,
    ->   first_name varchar(30) default NULL,
    ->   surname varchar(40) default NULL
    -> ) ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO customer VALUES (1, 'Tom', 'Clegg');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES (2, 'Jack', 'Smith');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES (3, 'Will', 'Powers');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES (4, 'Peter', 'Miller');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE sales (
    ->   code int(11) default NULL,
    ->   sales_rep int(11) default NULL,
    ->   customer int(11) default NULL,
    ->   value int(11) default NULL
    -> ) ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO sales VALUES (1, 1, 1, 2000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (2, 4, 3, 250);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (3, 2, 3, 500);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (4, 1, 4, 450);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (5, 3, 1, 3800);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (6, 1, 2, 500);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE sales_rep (
    ->   employee_number int(11) default NULL,
    ->   surname varchar(40) default NULL,
    ->   first_name varchar(30) default NULL,
    ->   commission tinyint(4) default NULL,
    ->   date_joined date default NULL,
    ->   birthday date default NULL
    -> ) ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO sales_rep VALUES (1, 'Ray', 'Sol', 10,  '2000-02-15', '1976-03-18');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales_rep VALUES (2, 'Goal', 'Charlene', 15, '1998-07-09', '1958-11-30');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales_rep VALUES (3, 'Soy', 'Mike', 10, '2001-05-14', '1971-06-18');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales_rep VALUES (4, 'Ray', 'Mongane', 10, '2002-11-23', '1982-01-04');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> CREATE TABLE customer_sales_values(first_name
    ->  VARCHAR(30), surname VARCHAR(40), value INT);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO customer_sales_values(first_name,surname,value)
    ->  SELECT first_name,surname, SUM(value) FROM sales NATURAL JOIN
    ->  customer GROUP BY first_name, surname;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM customer_sales_values;
+------------+---------+-------+
| first_name | surname | value |
+------------+---------+-------+
| Jack       | Smith   |  7500 |
| Peter      | Miller  |  7500 |
| Tom        | Clegg   |  7500 |
| Will       | Powers  |  7500 |
+------------+---------+-------+
4 rows in set (0.00 sec)

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

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

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

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

mysql>

   
    
    
    
    
  

Related examples in the same category