Database - mysql queries -part1

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

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

mysql> create database adi;
Query OK, 1 row affected (0.01 sec)

mysql> use adi;
Database changed
mysql> create table Supplier
    -> (Supp_Num char(3) primary key,
    -> Supp_Name char(15),
    -> Street char(15),
    -> City char(15),
    -> Balance decimal(7),
    -> Credit_Limit char(7));
Query OK, 0 rows affected (0.30 sec)

mysql> insert into Supplier values
    -> ('001','Adi','Kg Enggong','Kuala krai','7500','8500');
Query OK, 1 row affected (0.09 sec)

mysql> insert into Supplier values
    -> ('002','Adawiah','Kg Pasir Era','Kuala Krai','7500','6500');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Supplier values
    -> ('003','Malikah','Bdr Sulaiman','Port Klang','10000','431');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Supplier values
    -> ('004','Nik Hassan','Bdr Sulaiman','Port Klang','7500','5758');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('005','Radziah','Pnklg Kubur','K.Baharu','5000','5285');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Supplier values
    -> ('006','Iklima','Damansara','Selangor','15000','12762');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Supplier values
    -> ('007','Ahmad','Kg Enggong','Kuala Krai','11000','2190');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('008','Sharifuddin','Kg Enggong','Kuala Krai','600','3985');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('009','Naji','Shah Alam','Selangor','9500','548');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Supplier values
    -> ('010','Qusti','Kg Enggong','Kuala Krai','7500','8221');
Query OK, 1 row affected (0.03 sec)

mysql> Select*
    -> from Supplier;
+----------+-------------+--------------+------------+---------+--------------+
| Supp_Num | Supp_Name   | Street       | City       | Balance | Credit_Limit |
+----------+-------------+--------------+------------+---------+--------------+
| 001      | Adi         | Kg Enggong   | Kuala krai |    7500 | 8500         |

| 002      | Adawiah     | Kg Pasir Era | Kuala Krai |    7500 | 6500         |

| 003      | Malikah     | Bdr Sulaiman | Port Klang |   10000 | 431          |

| 004      | Nik Hassan  | Bdr Sulaiman | Port Klang |    7500 | 5758         |

| 005      | Radziah     | Pnklg Kubur  | K.Baharu   |    5000 | 5285         |

| 006      | Iklima      | Damansara    | Selangor   |   15000 | 12762        |

| 007      | Ahmad       | Kg Enggong   | Kuala Krai |   11000 | 2190         |

| 008      | Sharifuddin | Kg Enggong   | Kuala Krai |     600 | 3985         |

| 009      | Naji        | Shah Alam    | Selangor   |    9500 | 548          |

| 010      | Qusti       | Kg Enggong   | Kuala Krai |    7500 | 8221         |

+----------+-------------+--------------+------------+---------+--------------+
10 rows in set (0.09 sec)

mysql> select Supp_Num,Supp_Name,Balance from Supplier where balance>='2000'OR b
alance<='5000';
+----------+-------------+---------+
| Supp_Num | Supp_Name   | Balance |
+----------+-------------+---------+
| 001      | Adi         |    7500 |
| 002      | Adawiah     |    7500 |
| 003      | Malikah     |   10000 |
| 004      | Nik Hassan  |    7500 |
| 005      | Radziah     |    5000 |
| 006      | Iklima      |   15000 |
| 007      | Ahmad       |   11000 |
| 008      | Sharifuddin |     600 |
| 009      | Naji        |    9500 |
| 010      | Qusti       |    7500 |
+----------+-------------+---------+
10 rows in set (0.03 sec)

mysql> Alter table Supplier
    -> Drop Street;
Query OK, 10 rows affected (0.23 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> Alter table Supplier
    -> Add Rep_Num char(5);
Query OK, 10 rows affected (0.30 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> Select Supp_Name from Supplier where(Supp_Num='007');
+-----------+
| Supp_Name |
+-----------+
| Ahmad     |
+-----------+
1 row in set (0.02 sec)

mysql> Select Supp_Num,Supp_Name,Balance from Supplier;
+----------+-------------+---------+
| Supp_Num | Supp_Name   | Balance |
+----------+-------------+---------+
| 001      | Adi         |    7500 |
| 002      | Adawiah     |    7500 |
| 003      | Malikah     |   10000 |
| 004      | Nik Hassan  |    7500 |
| 005      | Radziah     |    5000 |
| 006      | Iklima      |   15000 |
| 007      | Ahmad       |   11000 |
| 008      | Sharifuddin |     600 |
| 009      | Naji        |    9500 |
| 010      | Qusti       |    7500 |
+----------+-------------+---------+
10 rows in set (0.00 sec)

mysql> Select Supp_Num,Supp_Name,Balance from Supplier where (Balance='5000'||ba
lance='10000'||balance='15000');
+----------+-----------+---------+
| Supp_Num | Supp_Name | Balance |
+----------+-----------+---------+
| 003      | Malikah   |   10000 |
| 005      | Radziah   |    5000 |
| 006      | Iklima    |   15000 |
+----------+-----------+---------+
3 rows in set (0.00 sec)

mysql> Select Supp_Num,Credit_Limit from Supplier;
+----------+--------------+
| Supp_Num | Credit_Limit |
+----------+--------------+
| 001      | 8500         |
| 002      | 6500         |
| 003      | 431          |
| 004      | 5758         |
| 005      | 5285         |
| 006      | 12762        |
| 007      | 2190         |
| 008      | 3985         |
| 009      | 548          |
| 010      | 8221         |
+----------+--------------+
10 rows in set (0.00 sec)

mysql> select Count(Supp_Num),Sum(Balance) from Supplier;
+-----------------+--------------+
| Count(Supp_Num) | Sum(Balance) |
+-----------------+--------------+
|              10 |        81100 |
+-----------------+--------------+
1 row in set (0.00 sec)

mysql> Select Count(Supp_Num),Sum(Balance) from Supplier
    -> Group by Credit_Limit
    -> Having count(*)>1;
Empty set (0.06 sec)

mysql> Select Credit_Limit, count(*) from Supplier
    -> Group by Credit_Limit;
+--------------+----------+
| Credit_Limit | count(*) |
+--------------+----------+
| 12762        |        1 |
| 2190         |        1 |
| 3985         |        1 |
| 431          |        1 |
| 5285         |        1 |
| 548          |        1 |
| 5758         |        1 |
| 6500         |        1 |
| 8221         |        1 |
| 8500         |        1 |
+--------------+----------+
10 rows in set (0.01 sec)