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)