LAB WORK SQL #2: ADVANCED TO SQL & MYSQL
Create 3 tables:
1. users
2. cats
3. details
Table 1: users
(kindly refer to Programming with PHP 5)
(must at least have 7 users: user_id from 1 to 7)
Table 2: cats
+----------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+-------+
| user_id | mediumint(8) unsigned | NO | | NULL | |
| cat_name | varchar(20) | NO | | NULL | |
+----------+-----------------------+------+-----+---------+-------+
1. Create table cats according to the above detail.
2. Insert few records to table cats.
3. Add on the following records.
+---------+----------+
| user_id | cat_name |
+---------+----------+
| 4 | Preety |
| 4 | Guy |
| 4 | Lion |
| 5 | Blue |
| 5 | Red |
| 5 | Green |
| 5 | Black |
| 5 | Grey |
| 5 | Silver |
| 6 | Cat |
| 7 | Honey |
| 7 | Bunny |
| 7 | Cutie |
+---------+----------+
Page-1 Madam Hana Munira
Table 3: details
+-----------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+-------+
| user_id | mediumint(8) unsigned | NO | PRI | NULL | |
| address | varchar(100) | NO | | NULL | |
| allocation_cats | decimal(10,2) | NO | | NULL | |
+-----------------+-----------------------+------+-----+---------+-------+
4. Create table details according to the above detail.
5. Add on the following records.
user_id Address allocation_cats
1 Rawang, 48300, Selangor 350.00
2 Setapak, 53300, Kuala Lumpur 200.00
3 Subang Jaya, 47600, Selangor 120.00
4 Bangi, 43600, Selangor 400.00
5 George Town, 11600, Pulau Pinang 600.00
6 Alor Gajah, 78000, Melaka 160.00
7 Gombak, 53100, Selangor 220.00
5(i). Insert 1 record (the first record)
5(ii). Insert multiple records (record 2 to 7)
6. Show all the user’s first name, address that are currently staying in Selangor.
7. Show all the user’s last name, cat name that allocate their money for cat between 100 to
300.
8. Show all John’s cat name.
9. Show Grey’s owner first name and address.
10. Display the number of cats that Ringgo have.
Page-2 Madam Hana Munira
