Lab 3: Use of Commit and Rollback
1. Create a database, connect to it and create a table named student
CREATE DATABASE LAB3
USE LAB 3;
CREATE TABLE student(
sid int identity(1,1) primary key,
sname varchar(20) NOT NULL,
saddress varchar(20),
DOB DATE,
Marks float
);
2. Display Student table
SELECT * FROM Student;
3. Rollback
ROLLBACK
4. Create Student Table again
CREATE TABLE student(
sid int identity(1,1) primary key,
sname varchar(20) NOT NULL,
saddress varchar(20),
DOB DATE,
Marks float
);
5. Commit
COMMIT
6. Display Student Table
SELECT * FROM student;
7. Insert Data
INSERT INTO student (sname, saddress, DOB, Marks) VALUES
('Aarav Sharma', 'Kathmandu', '2005-03-15', 85.5),
('Sita Thapa', 'Lalitpur', '2004-07-22', 78.0),
('Ramesh Karki', 'Bhaktapur', '2005-11-05', 92.3),
('Anjali Rai', 'Pokhara', '2006-01-30', 88.7),
('Bikash Gurung', 'Biratnagar', '2004-09-12', 74.5),
('Nisha Shrestha', 'Dharan', '2005-06-18', 81.2),
('Prakash Lama', 'Butwal', '2006-04-25', 69.8),
('Meena Raut', 'Besisahar', '2004-11-11', 89.4),
('Bibek Regmi', 'Jumla', '2006-01-05', 77.8),
('Laxmi Bhandari', 'Ilam', '2005-08-19', 86.6);
8. Check Rollback, Commit
Rollback
COMMIT
9. Delete records
DELETE FROM student WHERE saddress = 'kathmandu';
10. Check Rollback, Commit
ROLLBACK
COMMIT
11. Update Data
UPDATE student
SET saddress = 'Bhaktapur'
WHERE Marks > 80
12. Check Commit, Rollback
COMMIT
ROLLBACK
ROLLBACK
13. Drop Table
DROP TABLE student;
14. Check Roll, Commit
ROLLBACK
C
OMMIT
LAB 4: Use of Constraint
CREATE DATABASE constraintlab4
CREATE TABLE department(
did int primary key,
dname varchar(20) NOT NULL,
location varchar(20) default 'head office'
)
CREATE TABLE employee(
eid int identity(1,1) primary key,
fname varchar(20) NOT NULL,
mname varchar(20),
lname varchar(20) NOT NULL,
Email varchar(20) unique,
salary decimal(10,2) check(salary >= 18000),
did int , foreign key(did) references department(did),
joindate date default getdate()
)
select*
from department
select *
from employee
1) Insert 10/10 valid records to each of the table
INSERT INTO department (did, dname, location) VALUES
(1, 'Human Resources', 'Kathmandu'),
(2, 'Finance', 'Pokhara'),
(3, 'IT', 'Lalitpur'),
(4, 'Marketing', 'Biratnagar'),
(5, 'Sales', 'Butwal'),
(6, 'Operations', 'Dharan'),
(7, 'Legal', 'Bhaktapur'),
(8, 'Research', 'Janakpur'),
(9, 'Customer Service', 'Nepalgunj'),
(10, 'Administration', 'head office');
INSERT INTO employee (fname, mname, lname, Email, salary, did, joindate)
VALUES
('Rajan', 'Kumar', 'Joshi', '[Link]@[Link]', 24000.00, 1, '2024-05-10'),
('Sneha', NULL, 'Tamang', '[Link]@[Link]', 22000.00, 2, '2023-10-01'),
('Bibek', 'Prasad', 'Shah', '[Link]@[Link]', 26000.00, 3, '2022-08-15'),
('Laxmi', NULL, 'Malla', '[Link]@[Link]', 20000.00, 4, '2025-02-20'),
('Kamal', 'Raj', 'Thapa', '[Link]@[Link]', 23000.00, 3, '2023-06-30'),
('Nabin', NULL, 'Rai', 'nabin.rai123@[Link]', 21000.00, 6, '2024-07-12'),
('Pooja', 'Devi', 'Shrestha', '[Link]@[Link]', 25000.00, 1, '2022-11-05'),
('Suman', NULL, 'Lama', 'suman.lama12@[Link]', 19500.00, 8, '2023-03-18'),
('Asha', 'Kumari', 'Yadav', 'asha.yadav12@[Link]', 21500.00, 9, '2024-09-25'),
('Manoj', NULL, 'Karki', '[Link]@[Link]', 27000.00, 2, '2025-01-01');
2) Insert any two employees of email same
INSERT INTO employee(fname, mname, lname, Email, salary, did)
VALUES('uncle','kumar','jha','adsss@[Link]',15000,1)
3) Insert any two employees without their join date
INSERT INTO employee(fname, mname, lname, Email, salary, did)
VALUES('abd','kumar','jha','abd@[Link]',150000,1),
('bad','kumar','jha','abd@[Link]',150000,1);
4) Find all employees of department unknown.
SELECT *
from employee e
LEFT JOIN department d
ON [Link] = [Link]
WHERE [Link] IS NULL;
5) Delete IT department
DELETE from department
WHERE dname = 'IT'