mysql> create table course( c_id int primary key,
-> c_name varchar(20),
-> duration int,
-> fees decimal(10,2));
Query OK, 0 rows affected (0.00 sec)
mysql> desc course;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| c_id | int(11) | NO | PRI | NULL | |
| c_name | varchar(20) | YES | | NULL | |
| duration | int(11) | YES | | NULL | |
| fees | decimal(10,2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> create table student1(roll_no int primary key,
-> name varchar(20),
-> address varchar(20),
-> DOB date,
-> c_id int,
-> foreign key(c_id) references course(c_id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc student1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll_no | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| DOB | date | YES | | NULL | |
| c_id | int(11) | YES | MUL | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql> insert into course(c_id, c_name, duration, fees)
-> values
-> (101,'B-Tech', 4, 62000),
-> (102,'B-Sc', 3, 25000),
-> (103,'M-Tech', 2, 40000),
-> (104,'M-Sc', 2, 20000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from course;
+------+--------+----------+----------+
| c_id | c_name | duration | fees |
+------+--------+----------+----------+
| 101 | B-Tech | 4 | 62000.00 |
| 102 | B-Sc | 3 | 25000.00 |
| 103 | M-Tech | 2 | 40000.00 |
| 104 | M-Sc | 2 | 20000.00 |
+------+--------+----------+----------+
4 rows in set (0.02 sec)
mysql> insert into student1(roll_no, name, address, DOB, c_id)
-> values
-> (1, 'John Doe', 'Tezpur', '1995-05-15', 101),
-> (2, 'Alice Smith', 'Golaghat', '1996-08-20', 101),
-> (3, 'Bob Johnson', 'Dibrugarh', '1997-02-10', 102),
-> (4, 'Emily Brown', 'Darang', '1998-04-30', 102),
-> (5, 'David Lee', 'Barpeta', '1999-09-25', 102),
-> (6, 'Sarah White', 'Guwahati', '2000-12-12', 103),
-> (7, 'Michael Wilson', 'Morigaon', '2001-03-05', 104),
-> (8, 'Olivia Devis', 'Baksa', '2002-07-18', 104);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from student1;
+---------+----------------+-----------+------------+------+
| roll_no | name | address | DOB | c_id |
+---------+----------------+-----------+------------+------+
| 1 | John Doe | Tezpur | 1995-05-15 | 101 |
| 2 | Alice Smith | Golaghat | 1996-08-20 | 101 |
| 3 | Bob Johnson | Dibrugarh | 1997-02-10 | 102 |
| 4 | Emily Brown | Darang | 1998-04-30 | 102 |
| 5 | David Lee | Barpeta | 1999-09-25 | 102 |
| 6 | Sarah White | Guwahati | 2000-12-12 | 103 |
| 7 | Michael Wilson | Morigaon | 2001-03-05 | 104 |
| 8 | Olivia Devis | Baksa | 2002-07-18 | 104 |
+---------+----------------+-----------+------------+------+
8 rows in set (0.00 sec)
mysql> select roll_no, name, c_name, fees from student1 natural join
course;
+---------+----------------+--------+----------+
| roll_no | name | c_name | fees |
+---------+----------------+--------+----------+
| 1 | John Doe | B-Tech | 62000.00 |
| 2 | Alice Smith | B-Tech | 62000.00 |
| 3 | Bob Johnson | B-Sc | 25000.00 |
| 4 | Emily Brown | B-Sc | 25000.00 |
| 5 | David Lee | B-Sc | 25000.00 |
| 6 | Sarah White | M-Tech | 40000.00 |
| 7 | Michael Wilson | M-Sc | 20000.00 |
| 8 | Olivia Devis | M-Sc | 20000.00 |
+---------+----------------+--------+----------+
8 rows in set (0.00 sec)
mysql> select c_name, count(student1.roll_no) as Students_Number
-> from course
-> left join student1 on course.c_id = student1.c_id
-> group by c_name;
+--------+-----------------+
| c_name | Students_Number |
+--------+-----------------+
| B-Sc | 3 |
| B-Tech | 2 |
| M-Sc | 2 |
| M-Tech | 1 |
+--------+-----------------+
4 rows in set (0.01 sec)