Shivam Parag Darekar Practical No.
2 2024510012
Aim: Solving SQL Languages and Constraints Problems.
Objectives:
1. Understanding and implementing SQL commands to create and modify tables.
2. Applying constraints like primary key, unique key, and foreign key in MySQL.
3. Practicing managing user permissions and adding check constraints for data validation.
Tools used: MySQL Workbench 8.0 CE
Concept:
Data Definition Language (DDL) Syntax:
1. Create Table Statement – Creating tables like student and marks.
2. Alter Table Statement – Modifying the student table to add columns, rename columns,
and change data types.
3. Primary Key and Foreign Key Constraints – Making student_id a primary key and sid a
foreign key.
4. Unique Constraint – Applying unique constraints on columns like sname.
5. Check Constraint – Adding a check constraint to ensure positive values for marks.
Data Manipulation Language (DML) Syntax:
1. Insert Statement – Adding values to student and marks tables.
2. Delete Statement – Deleting specific records from tables.
3. Update Statement – Updating records, such as modifying age.
Data Control Language (DCL) Syntax:
1. Create User and Grant Permissions – Creating a user (XYZ) and granting permissions to
modify tables.
Constraints Syntax:
1. Primary Key, Foreign Key, Unique Key – Adding constraints to the student and marks
tables as required.
2. Check Constraint – Ensuring only positive values are entered for subject1 in the marks
table.
Problem Statement: To Solve Problem statements on SQL Database Language and Constraints.
Solution:
Root user:
1) Create following student table in MySQL
student (student_id int, student_name varchar(20))
→ create database lab2;
create table student (student_id int, student_name varchar(20));
2) Add two more columns in student table namely (age int, phone_no int)
→ alter table student add column age int;
alter table student add column phone_no int;
3) Rename the column name student_name with sname
→ alter table student rename column student_name to sname;
4) Add the column (class varchar(20)) after sname
→ alter table student add column class varchar(20) after sname;
5) Rename the datatype of sname to varchar(30)
→ alter table student modify sname varchar(30);
6) Make student_id a primary key.
→ alter table student add primary key(student_id);
7) Make sname an Unique key.
→ alter table student add unique(sname);
8) Insert following values in the table
→ insert into student values
(1, "Sanjay", "symca", 23, 242543),
(2, "Vaidehi", "fymca", 24, 454354),
(3, "Akshata", "symca", 21, 543543),
(4, "Vidula", "fymca", 22, 435454),
(5, "Pratik", "symca", 23, 345435);
9) Modify the age of Akshata to 22
→ update student set age=22 where sname="Akshata";
10) Delete the record of Pratik
→ SET SQL_SAFE_UPDATES=0;
delete from student where sname="Pratik";
11) Create one user XYZ and give him a permission to make the changes in the above
table.
→ CREATE USER 'XYZ'@'localhost' IDENTIFIED BY 'root75';
GRANT ALL PRIVILEGES ON [Link] TO 'XYZ'@'localhost';
GRANT CREATE ON lab2.* TO 'XYZ'@'localhost';
GRANT SELECT ON lab2.* TO 'XYZ'@'localhost';
GRANT ALTER ON lab2.* TO 'XYZ'@'localhost';
GRANT INSERT ON lab2.* TO 'XYZ'@'localhost';
FLUSH PRIVILEGES;
12) Login to XYZ and make sure he is able to make the changes in student table
created by Root user.
→
13) Create following marks table in MySQL
Marks (sid, subject1, subject2, subject3)
→ create table marks (sid int, subject1 varchar(20), subject2 varchar(20), subject3 varchar(20));
select * from marks;
14) Make sid a foreign key which refers to student_id of student table
→ alter table marks add foreign key(sid) references student(student_id);
select * from marks;
15) Apply check constraint on subject1 to verify that no one can enter negative marks
to it.
→ alter table marks add check(subject1>=0);
select * from marks;
16) Insert following valunes in marks table
→ insert into marks values
(1, "89", "78", "89"),
(3, "99", "67", "56"),
(4, "90", "66", "45"),
(2, "89","88","88");
select * from marks;
Observation:
During this practical, I learned how to create and modify tables in MySQL using DDL and DML
commands. I also understood how to set up primary keys, foreign keys, unique constraints, and
check conditions to enforce data integrity. Creating a new user and assigning specific
permissions helped me see how user access is controlled in a database. Finally, adding
constraints and observing their effect on data entry gave me insight into maintaining data
accuracy and consistency.