NAME – SOUDAGAR OWAIS JAVED
ROLL NO – 24CO121
BATCH - 03
DDL COMMANDS
1. QUERY – (DEPARTMENT TABLE)
mysql> CREATE TABLE department (
-> dept_id INT PRIMARY KEY,
-> dept_name VARCHAR(100),
-> office_phone VARCHAR(20),
-> head_of_department VARCHAR(100)
-> );
Query OK, 0 rows affected (0.05 sec)
OUTPUT -
mysql> desc department;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| dept_id | int | NO | PRI | NULL | |
| dept_name | varchar(100) | YES | | NULL | |
| office_phone | varchar(20) | YES | | NULL | |
| head_of_department | varchar(100) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
2. QUERY – (INSTRUCTOR TABLE)
mysql> CREATE TABLE instructor (
-> instructor_id INT PRIMARY KEY,
-> name VARCHAR(100) ,
-> email VARCHAR(100),
-> hire_date DATE,
-> dept_id INT,
-> FOREIGN KEY (dept_id) REFERENCES department(dept_id)
-> );
Query OK, 0 rows affected (0.04 sec)
OUTPUT
mysql> desc instructor;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| instructor_id | int | NO | PRI | NULL | |
| name | varchar(100) | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
| dept_id | int | YES | MUL | NULL | |
+---------------+--------------+------+-----+---------+-------+
3 . QUERY - ( STUDENT TABLE )
mysql> CREATE TABLE student (
-> student_id INT PRIMARY KEY,
-> name VARCHAR(100),
-> email VARCHAR(100),
-> enrollment_year YEAR,
-> dept_id INT,
-> FOREIGN KEY (dept_id) REFERENCES department(dept_id)
-> );
Query OK, 0 rows affected (0.04 sec)
OUTPUT -
mysql> desc student;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| student_id | int | NO | PRI | NULL | |
| name | varchar(100) | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
| enrollment_year | year | YES | | NULL | |
| dept_id | int | YES | MUL | NULL | |
+-----------------+--------------+------+-----+---------+-------+
4 . QUERY – (COURSE TABLE )
mysql> CREATE TABLE course (
-> course_id INT PRIMARY KEY,
-> course_name VARCHAR(100),
-> credits INT,
-> semester_offered VARCHAR(10),
-> dept_id INT,
-> instructor_id INT,
-> FOREIGN KEY (dept_id) REFERENCES department(dept_id),
-> FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id)
-> );
Query OK, 0 rows affected (0.16 sec)
OUTPUT -
mysql> desc course;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| course_id | int | NO | PRI | NULL | |
| course_name | varchar(100) | YES | | NULL | |
| credits | int | YES | | NULL | |
| semester_offered | varchar(10) | YES | | NULL | |
| dept_id | int | YES | MUL | NULL | |
| instructor_id | int | YES | MUL | NULL | |
+------------------+--------------+------+-----+---------+-------+
5 . QUERY – (ENROLLMENT TABLE)
mysql> CREATE TABLE enrollment (
-> enrollment_id INT PRIMARY KEY,
-> student_id INT,
-> course_id INT,
-> semester VARCHAR(10),
-> grade CHAR(2),
-> FOREIGN KEY (student_id) REFERENCES student(student_id),
-> FOREIGN KEY (course_id) REFERENCES course(course_id)
-> );
Query OK, 0 rows affected (0.07 sec)
OUTPUT -
mysql> desc enrollment;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| enrollment_id | int | NO | PRI | NULL | |
| student_id | int | YES | MUL | NULL | |
| course_id | int | YES | MUL | NULL | |
| semester | varchar(10) | YES | | NULL | |
| grade | char(2) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
6 . QUERY – ( ALTER )
ALTER TABLE enrollment DROP COLUMN year;
OUTPUT –
+----------------+----------+------+-----+---------+
| Field | Type | Null | Key | Default |
+----------------+----------+------+-----+---------+
| enrollment_id | int | NO | PRI | NULL |
| student_id | int | NO | MUL | NULL |
| course_id | int | NO | MUL | NULL |
| semester | int | YES | | NULL |
| grade | char(2) | YES | | NULL |
+----------------+----------+------+-----+---------+
7 . QUERY – ( TRUNCATE )
mysql> TRUNCATE TABLE ENROLLMENT;
OUTPUT -
mysql> DESC ENROLLMENT;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| enrollment_id | int | NO | PRI | NULL | |
| student_id | int | YES | MUL | NULL | |
| course_id | int | YES | MUL | NULL | |
| semester | varchar(10) | YES | | NULL | |
| grade | char(2) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
mysql> SELECT * FROM ENROLLMENT;
Empty set (0.00 sec)
8 . QUERY - (DROP)
DROP TABLE enrollment;
OUTPUT –
ERROR 1146 (42S02): Table '24CO121.enrollment' doesn't exist
