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 adi;
Query OK, 1 row affected (0.14 sec)
mysql> use adi;
Database changed
mysql> create table Quiz
-> (Student_Ic char(20) primary key,
-> Last_Name char(15),
-> First_Name char(15),
-> City char(20),
-> State char(20),
-> Age char(2));
Query OK, 0 rows affected (0.17 sec)
mysql> Describe student;
ERROR 1146 (42S02): Table 'adi.student' doesn't exist
mysql> Describe Quiz;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| Student_Ic | char(20) | NO | PRI | | |
| Last_Name | char(15) | YES | | NULL | |
| First_Name | char(15) | YES | | NULL | |
| City | char(20) | YES | | NULL | |
| State | char(20) | YES | | NULL | |
| Age | char(2) | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.05 sec)
mysql> create table Supplier
-> (Supp_Num char(3) primary key,
-> Supp_Name char(15),
-> Street char(20),
-> City char(20),
-> Zip_Code char(6),
-> Balance char(7),
-> Credit_Limit char(8),
-> Rep_Num char(3));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into Supplier values
-> ('148','Hadi','Kg Enggong','Kuala Krai','18000','6550','7500','20');
Query OK, 1 row affected (0.05 sec)
mysql> insert into Supplier values
-> ('282','Adawiah','Pasir Era','Kuala Krai','18000','431.50','10000','35');
Query OK, 1 row affected (0.03 sec)
mysql> insert into Supplier values
-> ('356','Ayub','Pasir Mas','K.Baharu','18500','5785','7500','65');
Query OK, 1 row affected (0.03 sec)
mysql> insert into Supplier values
-> ('408','Ahmad','Pasir Mas','K.Baharu','18500','5285.25','5000','35');
Query OK, 1 row affected (0.03 sec)
mysql> insert into Supplier values
-> ('462','Mat','Machang','K.Baharu','18502','3412','10000','65');
Query OK, 1 row affected (0.02 sec)
mysql> insert into Supplier values
-> ('608','Alan','Machang','K.Baharu','18502','12762','15000','20');
Query OK, 1 row affected (0.02 sec)
mysql> insert into Supplier values
-> ('524','Man','Kuala Krai','K.Baharu','18000','2106','10000','65');
Query OK, 1 row affected (0.02 sec)
mysql> insert into Supplier values
-> ('687','Wan','Kuala Krai','K.Baharu','18000','2851','5000','35');
Query OK, 1 row affected (0.03 sec)
mysql> insert into Supplier values
-> ('725','Nik Hassan','Kuala Krai','K.Baharu','18000','248','7500','35');
Query OK, 1 row affected (0.03 sec)
mysql> insert into Supplier values
-> ('842','Nik Mat','Kuala Krai','K.Baharu','18000','8221','7500','20');
Query OK, 1 row affected (0.01 sec)
mysql> select*
-> from Supplier;
+----------+------------+------------+------------+----------+---------+--------
------+---------+
| Supp_Num | Supp_Name | Street | City | Zip_Code | Balance | Credit_
Limit | Rep_Num |
+----------+------------+------------+------------+----------+---------+--------
------+---------+
| 148 | Hadi | Kg Enggong | Kuala Krai | 18000 | 6550 | 7500
| 20 |
| 282 | Adawiah | Pasir Era | Kuala Krai | 18000 | 431.50 | 10000
| 35 |
| 356 | Ayub | Pasir Mas | K.Baharu | 18500 | 5785 | 7500
| 65 |
| 408 | Ahmad | Pasir Mas | K.Baharu | 18500 | 5285.25 | 5000
| 35 |
| 462 | Mat | Machang | K.Baharu | 18502 | 3412 | 10000
| 65 |
| 524 | Man | Kuala Krai | K.Baharu | 18000 | 2106 | 10000
| 65 |
| 608 | Alan | Machang | K.Baharu | 18502 | 12762 | 15000
| 20 |
| 687 | Wan | Kuala Krai | K.Baharu | 18000 | 2851 | 5000
| 35 |
| 725 | Nik Hassan | Kuala Krai | K.Baharu | 18000 | 248 | 7500
| 35 |
| 842 | Nik Mat | Kuala Krai | K.Baharu | 18000 | 8221 | 7500
| 20 |
+----------+------------+------------+------------+----------+---------+--------
------+---------+
10 rows in set (0.01 sec)
mysql> select Supp_Name,Supp_Name,Balance from Supplier order by balance;
+----------+------------+---------+
| Supp_Num | Supp_Name | Balance |
+----------+------------+---------+
| 608 | Alan | 12762 |
| 524 | Man | 2106 |
| 725 | Nik Hassan | 248 |
| 687 | Wan | 2851 |
| 462 | Mat | 3412 |
| 282 | Adawiah | 431.50 |
| 408 | Ahmad | 5285.25 |
| 356 | Ayub | 5785 |
| 148 | Hadi | 6550 |
| 842 | Nik Mat | 8221 |
+----------+------------+---------+
10 rows in set (0.02 sec)
mysql> select count(Supp_Name),Sum(Balance) from Supplier;
+-----------------+--------------+
| count(Supp_Num) | Sum(Balance) |
+-----------------+--------------+
| 10 | 47651.75 |
+-----------------+--------------+
1 row in set (0.01 sec)
mysql> select Credit_Limit,Count(*) from Supplier
-> Group by Credit_Limit;
+--------------+----------+
| Credit_Limit | Count(*) |
+--------------+----------+
| 10000 | 3 |
| 15000 | 1 |
| 5000 | 2 |
| 7500 | 4 |
+--------------+----------+
4 rows in set (0.00 sec)
mysql> select Credit_Limit,Count(*) from Supplier
-> Group by credit_Limit
-> having count(*)>1;
+--------------+----------+
| Credit_Limit | Count(*) |
+--------------+----------+
| 10000 | 3 |
| 5000 | 2 |
| 7500 | 4 |
+--------------+----------+
3 rows in set (0.02 sec)
mysql> Alter table Supplier
-> Drop Zip_Code;
Query OK, 10 rows affected (0.17 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select Credit_Limit,Count(Supp_Num) from Supplier where Rep_Num=20
-> Group by Credit_limit;
+--------------+-----------------+
| Credit_Limit | Count(Supp_Num) |
+--------------+-----------------+
| 15000 | 1 |
| 7500 | 2 |
+--------------+-----------------+
2 rows in set (0.00 sec)
mysql> select sum(Balance),AVG(Balance),MAX(Balance),MIN(Balance) from Supplier;
+--------------+--------------+--------------+--------------+
| sum(Balance) | AVG(Balance) | MAX(Balance) | MIN(Balance) |
+--------------+--------------+--------------+--------------+
| 47651.75 | 4765.175 | 8221 | 12762 |
+--------------+--------------+--------------+--------------+
1 row in set (0.00 sec)
No comments:
Post a Comment