MySQL Assignment 12

Hi,

The following questions are based on Foreign Key Constraint (Part 1). I have given output format at some questions because to cross check the expected output and your output are same or not.

1. Create a database named with ‘fkeydb‘.

2. Create a table named with ‘student‘. The attributes of the table include ‘name‘,’id‘,’contactno‘.

The constraints include:

All the attributes should not allow null values and ‘id‘ is an integer and used as a primary key.

3. Display the schema of the table. It is something like this.

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(30) | NO   |     | NULL    |       |
| id        | int(11)     | NO   | PRI | NULL    |       |
| contactno | int(10)     | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

4. Insert the following records in to the ‘student‘ table.

5. Display the ‘student‘ table. It is something  like this.

+------+----+-----------+
| name | id | contactno |
+------+----+-----------+
| aaa  |  1 |      1234 |
| bbb  |  2 |      1236 |
| ddd  |  3 |      4321 |
+------+----+-----------+

6. Create a table with name ‘test‘. The attributes the test include

‘testname’,’testid’,’studentid‘.

Each attribute should not allow null values, ‘testid‘ is the primary key for this table. ‘studentid‘ is the foreign key with a name ‘student fk‘ references to ‘student‘ table for the attribute ‘id‘.

The reference action include :  on delete cascade and on update null [set null]

7.  Display the schema of the ‘test‘ table.

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| testname | varchar(30) | NO   |     | NULL    |       |
| testid   | int(11)     | NO   | PRI | NULL    |       |
| sid      | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

8. Insert the following records in to the ‘test‘ table. The order given in the following records is not same as the order of the attributes while creating the table. Observe and insert in the correct order.

After inserting the values in the correct order, display the data of the ‘test’ table. It is something like this:

+----------+--------+------+
| testname | testid | sid  |
+----------+--------+------+
| python   |    101 |    1 |
| c++      |    102 |    2 |
| java     |    103 |    3 |
+----------+--------+------+

9. Display the data in the ‘test‘ table, after deleting the entry of student number ‘2’ in student table. Found error? What is the reason in single line.

10. Display the data in the ‘test‘ table, after updating the entry of student name to ‘zzz‘ whose id is ‘3’.

0

0