Database - mysql queries -part4

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

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

mysql> use adi;
Database changed
mysql> create table Vehicle
    -> (Car_No char(10) primary key,
    -> Type char(15),
    -> Model char(10),
    -> Owner_ID char(10));
Query OK, 0 rows affected (0.17 sec)

mysql> insert into Vehicle values
    -> ('WAC5887','PERODUA','MYVI','123456');
Query OK, 1 row affected (0.06 sec)

mysql> insert into Vehicle values
    -> ('BFG1254','PERODUA','KELISA','452114');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('TF81','HONDA','CIVIC','213321');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('AR15','MITSUBSHI','LANCER','150819');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Vehicle values
    -> ('BCF1007','PERODUA','KENARI','234540');
Query OK, 1 row affected (0.05 sec)

mysql> select*
    -> from Vehicle;
+---------+-----------+--------+----------+
| Car_No  | Type      | Model  | Owner_ID |
+---------+-----------+--------+----------+
| AR15    | MITSUBSHI | LANCER | 150819   |
| BCF1007 | PERODUA   | KENARI | 234540   |
| BFG1254 | PERODUA   | KELISA | 452114   |
| TF81    | HONDA     | CIVIC  | 213321   |
| WAC5887 | PERODUA   | MYVI   | 123456   |
+---------+-----------+--------+----------+
5 rows in set (0.03 sec)

mysql> create table Owner
    -> (Owner_ID char(8) primary key,
    -> Name char(15),
    -> PymtDate Date,
    -> PymtMade decimal(8,2),
    -> ActualPrice decimal(8,2),
    -> PymtType decimal(8,2),
    -> Address char(15));
Query OK, 0 rows affected (0.14 sec)

mysql> Alter table Owner modify PymtType char(15);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into Owner values
    -> ('123456','Harrison','1999/01/23','610.00','610.00','Cash','Bangi');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('452114','Baker','1999/01/31','450.00','600.00','Installment','Gombak');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('213321','Aziz','1999/01/31','350.00','700.00','Installment','Klang');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Owner values
    -> ('150819','Ackim','1999/01/25','650.00','650.00','Cash','Sentul');
Query OK, 1 row affected (0.03 sec)

mysql> select*
    -> from Owner;
+----------+----------+------------+----------+-------------+-------------+-----
----+
| Owner_ID | Name     | PymtDate   | PymtMade | ActualPrice | PymtType    | Addr
ess |
+----------+----------+------------+----------+-------------+-------------+-----
----+
| 123456   | Harrison | 1999-01-23 |   610.00 |      610.00 | Cash        | Bang
i   |
| 150819   | Ackim    | 1999-01-25 |   650.00 |      650.00 | Cash        | Sent
ul  |
| 213321   | Aziz     | 1999-01-31 |   350.00 |      700.00 | Installment | Klan
g   |
| 452114   | Baker    | 1999-01-31 |   450.00 |      600.00 | Installment | Gomb
ak  |
+----------+----------+------------+----------+-------------+-------------+-----
----+
4 rows in set (0.01 sec)

mysql> insert into Vehicle values
    -> ('DAU4019','HONDA','WAVE','5775');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('DBF15','PERODUA','VIVA','57775');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Vehicle values
    -> ('DBB32','PERODUA','KANCIL','57875');
Query OK, 1 row affected (0.05 sec)

mysql> insert into Vehicle values
    -> ('DBB321','HONDA','SIVICMAYAT','5755');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('DBG123','PERODUA','PERSONA','85585');
Query OK, 1 row affected (0.05 sec)

mysql> select*
    -> from Vehicle;
+---------+-----------+------------+----------+
| Car_No  | Type      | Model      | Owner_ID |
+---------+-----------+------------+----------+
| AR15    | MITSUBSHI | LANCER     | 150819   |
| BCF1007 | PERODUA   | KENARI     | 234540   |
| BFG1254 | PERODUA   | KELISA     | 452114   |
| DAU4019 | HONDA     | WAVE       | 5775     |
| DBB32   | PERODUA   | KANCIL     | 57875    |
| DBB321  | HONDA     | SIVICMAYAT | 5755     |
| DBF15   | PERODUA   | VIVA       | 57775    |
| DBG123  | PERODUA   | PERSONA    | 85585    |
| TF81    | HONDA     | CIVIC      | 213321   |
| WAC5887 | PERODUA   | MYVI       | 123456   |
+---------+-----------+------------+----------+
10 rows in set (0.00 sec)

mysql> insert into Vehicle values
    -> ('WHN1234','PROTON','PERSONA','M111');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Vehicle values
    -> ('WHW4536','HYUNDAI','SONATA','M123');
Query OK, 1 row affected (0.03 sec)

mysql> select*
    -> from Vehicle;
+---------+-----------+------------+----------+
| Car_No  | Type      | Model      | Owner_ID |
+---------+-----------+------------+----------+
| AR15    | MITSUBSHI | LANCER     | 150819   |
| BCF1007 | PERODUA   | KENARI     | 234540   |
| BFG1254 | PERODUA   | KELISA     | 452114   |
| DAU4019 | HONDA     | WAVE       | 5775     |
| DBB32   | PERODUA   | KANCIL     | 57875    |
| DBB321  | HONDA     | SIVICMAYAT | 5755     |
| DBF15   | PERODUA   | VIVA       | 57775    |
| DBG123  | PERODUA   | PERSONA    | 85585    |
| TF81    | HONDA     | CIVIC      | 213321   |
| WAC5887 | PERODUA   | MYVI       | 123456   |
| WHN1234 | PROTON    | PERSONA    | M111     |
| WHW4536 | HYUNDAI   | SONATA     | M123     |
+---------+-----------+------------+----------+
12 rows in set (0.00 sec)

mysql> insert into Owner values
    -> ('1245','Adi','1999/01/19','350.00','600.00','CASH','KELANTAN');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('12456','Adawiah','1999/01/30','450.00','650.00','Installment','KELANTAN
');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('24756','Ayub','2000/01/10','400.00','750.00','Installment','KELANTAN');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('2468','Mat','2000/02/21','400.00','450.00','Cash','KELANTAN');
Query OK, 1 row affected (0.03 sec)

mysql> insert into Owner values
    -> ('24689','Shah','2000/02/29','500.00','600.00','CASH','Kuala krai');
Query OK, 1 row affected (0.05 sec)

mysql> select*
    -> from Owner;
+----------+----------+------------+----------+-------------+-------------+-----
-------+
| Owner_ID | Name     | PymtDate   | PymtMade | ActualPrice | PymtType    | Addr
ess    |
+----------+----------+------------+----------+-------------+-------------+-----
-------+
| 123456   | Harrison | 1999-01-23 |   610.00 |      610.00 | Cash        | Bang
i      |
| 1245     | Adi      | 1999-01-19 |   350.00 |      600.00 | CASH        | KELA
NTAN   |
| 12456    | Adawiah  | 1999-01-30 |   450.00 |      650.00 | Installment | KELA
NTAN   |
| 150819   | Ackim    | 1999-01-25 |   650.00 |      650.00 | Cash        | Sent
ul     |
| 213321   | Aziz     | 1999-01-31 |   350.00 |      700.00 | Installment | Klan
g      |
| 2468     | Mat      | 2000-02-21 |   400.00 |      450.00 | Cash        | KELA
NTAN   |
| 24689    | Shah     | 2000-02-29 |   500.00 |      600.00 | CASH        | Kual
a krai |
| 24756    | Ayub     | 2000-01-10 |   400.00 |      750.00 | Installment | KELA
NTAN   |
| 452114   | Baker    | 1999-01-31 |   450.00 |      600.00 | Installment | Gomb
ak     |
+----------+----------+------------+----------+-------------+-------------+-----
-------+
9 rows in set (0.00 sec)







mysql> select Name,Car_NO from Vehicle,Owner
    -> where owner.Owner_ID = Vehicle.Owner_ID and type='PERODUA';
+----------+---------+
| Name     | Car_NO  |
+----------+---------+
| Baker    | BFG1254 |
| Harrison | WAC5887 |
+----------+---------+
2 rows in set (0.00 sec)

mysql> select count(Car_NO), type from Vehicle where type = 'PERODUA'
    -> Group by type;
+---------------+---------+
| count(Car_NO) | type    |
+---------------+---------+
|             6 | PERODUA |
+---------------+---------+
1 row in set (0.02 sec)

mysql>