HTMLify

Q.2 Write a SQl block to create a department table and add the constraints department name, department number and department head.
Solution:
mysql> CREATE database practical;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| bca                |
| information_schema |
| mysql              |
| performance_schema |
| practical          |
| sakila             |
| student            |
| sys                |
| world              |
+--------------------+
9 rows in set (0.01 sec)

mysql> USE practical;
Database changed
mysql> CREATE TABLE department(
    -> dept_id INT PRIMARY KEY NOT NULL,
    -> email VARCHAR(20) NOT NULL,
    -> address VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC department;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| dept_id | int         | NO   | PRI | NULL    |       |
| email   | varchar(20) | NO   |     | NULL    |       |
| address | varchar(20) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>  ALTER TABLE department
    -> ADD COLUMN dept_name VARCHAR(10) NOT NULL,
    -> ADD COLUMN dept_number INT NOT NULL,
    -> ADD COLUMN dept_head VARCHAR(10) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc department;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| dept_id     | int         | NO   | PRI | NULL    |       |
| email       | varchar(20) | NO   |     | NULL    |       |
| address     | varchar(20) | NO   |     | NULL    |       |
| dept_name   | varchar(10) | NO   |     | NULL    |       |
| dept_number | int         | NO   |     | NULL    |       |
| dept_head   | varchar(10) | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)