27. Designing of tables, applying DML statements, enforcing constraints.
VESRN TECHNOLOGIES
Database & MySQL Practical
DDL | DML | Constraints | ITI Practical Exercises
Designing Tables & Applying DML Statements
Aim: To design database tables, apply DML commands and enforce constraints using MySQL.
Requirements
- Computer System
- MySQL / XAMPP / phpMyAdmin
Create Database
CREATE DATABASE iti_college; USE iti_college;
Create Department Table
CREATE TABLE department (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50) NOT NULL UNIQUE
);
Create Student Table
CREATE TABLE student (
stu_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
trade VARCHAR(30),
marks INT CHECK (marks>=0 AND marks<=100),
dept_id INT,
FOREIGN KEY (dept_id)
REFERENCES department(dept_id)
);
Insert Records
INSERT INTO department(dept_name)
VALUES ('Electrician'),
('Fitter'),
('Welder');
INSERT INTO student(name,trade,marks,dept_id)
VALUES
('Raju','Electrician',78,1),
('Kumar','Fitter',82,2),
('Ravi','Welder',69,3);
Update Record
UPDATE student SET marks = 85 WHERE name='Raju';
Delete Record
DELETE FROM student WHERE stu_id=3;
Constraints Used
| Constraint | Purpose |
|---|---|
| PRIMARY KEY | Unique record identification |
| NOT NULL | Prevent empty value |
| UNIQUE | No duplicate data |
| CHECK | Limit value range |
| FOREIGN KEY | Maintain relationship |
Example Constraint Violations
INSERT INTO department(dept_name)
VALUES ('Electrician');
-- Error occurs
INSERT INTO student(name,trade,marks,dept_id)
VALUES('Test','Electrician',150,1);
-- CHECK constraint fails
Result
Tables designed successfully. DML operations performed and constraints enforced properly.
Precautions
- Insert parent table data before child table
- Always use WHERE in UPDATE & DELETE
- Follow correct data types
I practiced all under my trainer Venugopal Sir.
