Database - mysql queries -part11

Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database custRepsystem;
Query OK, 1 row affected (0.03 sec)

mysql> use custRepsystem;
Database changed

mysql> create table customer (cust_num char(8) primary key,cust_name char(20) no
t null,street char(20),city char(20),balance decimal(8,2)not null ,credit_limit
decimal(8,2) not null ,rep_num char(3));
Query OK, 0 rows affected (0.17 sec)

mysql> create table rep(rep_num char(3) primary key not null,
not  null,first_name char(20) not null,street char(20),city char(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into customer
    -> values('148','hot sports center','2115 jln mansoor','kangar','6555.00','7
500.00','20');
Query OK, 1 row affected (0.14 sec)

mysql> insert into customer
    -> values('282','panas direct','3827 nawawi','kuching','4315.40','10000.00',
'35');
Query OK, 1 row affected (0.02 sec)

mysql> insert into customer
    -> values('356','fergusons','482 wildwest','delhi','5785.00','7500.00','65'
);
Query OK, 1 row affected (0.03 sec)

mysql> insert into customer
    -> values('409','everyone corner','1827 fojia','tunis','5285.00','5000.00','
35');
Query OK, 1 row affected (0.03 sec)

mysql> insert into customer
    -> values('462','bargains galore','3829 central','klang','3412.00','10000.00
','65');
Query OK, 1 row affected (0.01 sec)

mysql> insert into customer
    -> values('524','klines','838 ridge','tripoli','12762.00','15000.00','20');
Query OK, 1 row affected (0.01 sec)

mysql> insert into customer
    -> values('608','uncle don','321 nizra','kuching','2100.00','10000.00','65')
;
Query OK, 1 row affected (0.03 sec)

mysql> insert into customer
    -> values('687','lees corner','213 west','penang','2851.00','5000.00','35');

Query OK, 1 row affected (0.03 sec)

mysql> select * from customer;
+----------+-------------------+------------------+---------+----------+--------
------+---------+
| cust_num | cust_name         | street           | city    | balance  | credit_
limit | rep_num |
+----------+-------------------+------------------+---------+----------+--------
------+---------+
| 148      | hot sports center | 2115 jln mansoor | kangar  |  6555.00 |      75
00.00 | 20      |
| 282      | panas direct      | 3827 nawawi      | kuching |  4315.40 |     100
00.00 | 35      |
| 356      | fergusons         | 482 wildwest     | delhi   |  5785.00 |     75
00.00 | 65      |
| 409      | everyone corner   | 1827 fojia       | tunis   |  5285.00 |      50
00.00 | 35      |
| 462      | bargains galore   | 3829 central     | klang   |  3412.00 |     100
00.00 | 65      |
| 524      | klines            | 838 ridge        | tripoli | 12762.00 |     150
00.00 | 20      |
| 608      | uncle don         | 321 nizra        | kuching |  2100.00 |     100
00.00 | 65      |
| 687      | lees corner       | 213 west         | penang  |  2851.00 |      50
00.00 | 35      |
+----------+-------------------+------------------+---------+----------+--------
------+---------+
8 rows in set (0.06 sec)


mysql> insert into rep
    -> values('20','mohd','zambri','621 telawi','kuantan');
Query OK, 1 row affected (0.05 sec)

mysql> insert into rep
    -> values('35','fuad','razi','532 mansoor','kangar');
Query OK, 1 row affected (0.02 sec)

mysql> insert into rep
    -> values('65','perez','ali','1625 taylor','johor');
Query OK, 1 row affected (0.02 sec)

mysql> select * from rep;
+---------+-----------+------------+-------------+---------+
| rep_num | last_name | first_name | street      | city    |
+---------+-----------+------------+-------------+---------+
| 20      | mohd      | zambri     | 621 telawi  | kuantan |
| 35      | fuad      | razi       | 532 mansoor | kangar  |
| 65      | perez     | ali        | 1625 taylor | johor   |
+---------+-----------+------------+-------------+---------+
3 rows in set (0.00 sec)

mysql> select cust_num,cust_name,first_name,rep.rep_num from customer,rep where
(customer.rep_num=rep.rep_num);
+----------+-------------------+------------+---------+
| cust_num | cust_name         | first_name | rep_num |
+----------+-------------------+------------+---------+
| 148      | hot sports center | zambri     | 20      |
| 282      | panas direct      | razi       | 35      |
| 356      | fergusons         | ali        | 65      |
| 409      | everyone corner   | razi       | 35      |
| 462      | bargains galore   | ali        | 65      |
| 524      | klines            | zambri     | 20      |
| 608      | uncle don         | ali        | 65      |
| 687      | lees corner       | razi       | 35      |
+----------+-------------------+------------+---------+
8 rows in set (0.03 sec)

mysql> select cust_num,cust_name,balance,first_name,rep.rep_num from customer,re
p where (customer.rep_num=rep.rep_num);
+----------+-------------------+----------+------------+---------+
| cust_num | cust_name         | balance  | first_name | rep_num |
+----------+-------------------+----------+------------+---------+
| 148      | hot sports center |  6555.00 | zambri     | 20      |
| 282      | panas direct      |  4315.40 | razi       | 35      |
| 356      | fergusons         |  5785.00 | ali        | 65      |
| 409      | everyone corner   |  5285.00 | razi       | 35      |
| 462      | bargains galore   |  3412.00 | ali        | 65      |
| 524      | klines            | 12762.00 | zambri     | 20      |
| 608      | uncle don         |  2100.00 | ali        | 65      |
| 687      | lees corner       |  2851.00 | razi       | 35      |
+----------+-------------------+----------+------------+---------+
8 rows in set (0.00 sec)




mysql> select cust_num,cust_name,credit_limit,first_name,rep.rep_num,rep.city fr
om customer,rep where (customer.rep_num=rep.rep_num);
+----------+-------------------+--------------+------------+---------+---------+

| cust_num | cust_name         | credit_limit | first_name | rep_num | city    |

+----------+-------------------+--------------+------------+---------+---------+

| 148      | hot sports center |      7500.00 | zambri     | 20      | kuantan |

| 282      | panas direct      |     10000.00 | razi       | 35      | kangar  |

| 356      | fergusons         |     75000.00 | ali        | 65      | johor   |

| 409      | everyone corner   |      5000.00 | razi       | 35      | kangar  |

| 462      | bargains galore   |     10000.00 | ali        | 65      | johor   |

| 524      | klines            |     15000.00 | zambri     | 20      | kuantan |

| 608      | uncle don         |     10000.00 | ali        | 65      | johor   |

| 687      | lees corner       |      5000.00 | razi       | 35      | kangar  |

+----------+-------------------+--------------+------------+---------+---------+

8 rows in set (0.00 sec)


mysql> select cust_num,cust_name,first_name,rep.rep_num from customer,rep where
customer.rep_num=rep.rep_num AND credit_limit='7500.00';
+----------+-------------------+------------+---------+
| cust_num | cust_name         | first_name | rep_num |
+----------+-------------------+------------+---------+
| 148      | hot sports center | zambri     | 20      |
| 356      | fergusons         | ali        | 65      |
+----------+-------------------+------------+---------+
2 rows in set (0.00 sec)

mysql>