db_data.sql

USE dbms_lab;
TRUNCATE TABLE wards;
TRUNCATE TABLE doctors;
TRUNCATE TABLE consultants;
TRUNCATE TABLE patients;
TRUNCATE TABLE tests;
TRUNCATE TABLE bills;
TRUNCATE TABLE employee;
TRUNCATE TABLE department;
TRUNCATE TABLE employee_department;
TRUNCATE TABLE customer;
TRUNCATE TABLE orders;
TRUNCATE TABLE student;
TRUNCATE TABLE program;
TRUNCATE TABLE register;
TRUNCATE TABLE salary_audit;
INSERT INTO wards(ward_name,ward_type) VALUES('General Ward','General'),('Emergency Ward','Emergency'),('Pediatrics','Specific');
INSERT INTO doctors(doctor_name,specialty) VALUES('Dr A Sharma','Cardiology'),('Dr B Singh','Orthopedics'),('Dr C Rao','Pediatrics');
INSERT INTO consultants(consultant_name,specialty) VALUES('Dr X Verma','Cardiology'),('Dr Y Gupta','General Medicine');
INSERT INTO patients(patient_name,age,gender,address,contact,ward_id,consultant_id,lead_consultant_id) VALUES('Amit Kumar',30,'M','Delhi','9999999999',1,1,1),('Neha Gupta',22,'F','Pune','8888888888',2,2,2),('Rohit Sen',45,'M','Delhi','7777777777',1,1,2);
INSERT INTO tests(patient_id,test_name,test_date,result) VALUES(1,'Blood Test','2025-12-01','Normal'),(1,'X-Ray','2025-12-02','Clear'),(2,'MRI','2025-12-03','Findings');
INSERT INTO bills(patient_id,amount,bill_date) VALUES(1,1500.00,'2025-12-05'),(2,2500.00,'2025-12-06');
INSERT INTO employee(emp_id,name,department,age,salary,city) VALUES(2001,'aditya', 'Finance',22,55000,'Delhi'),(2002,'harsh', 'CSE',28,30000,'Pune'),(2003,'ayush', 'CSE',26,18000,'Delhi'),(2004,'raj', 'HR',35,45000,'Pune'),(2005,'sam', 'Finance',22,60000,'Delhi');
INSERT INTO department(dept_id,dept_name) VALUES(1,'CSE'),(2,'Finance'),(3,'HR');
INSERT INTO employee_department(emp_id,dept_id) VALUES(2002,1),(2003,1),(2001,2),(2005,2),(2004,3);
INSERT INTO customer(name,age,address,city) VALUES('Karan',30,'Delhi street','Delhi'),('Priya',25,'Pune lane','Pune'),('Sameer',28,'Mumbai road','Mumbai');
INSERT INTO orders(customer_id,order_date,order_city) VALUES(1,'2025-12-10','Delhi'),(2,'2025-12-11','Pune');
INSERT INTO student(roll_no,name,city) VALUES(101,'Ritu','Delhi'),(102,'Aman','Pune'),(103,'Sneha','Delhi');
INSERT INTO program(program_id,program_name,fee,department) VALUES(1,'MCA',30000,'CSE'),(2,'BSc',15000,'Science');
INSERT INTO register(program_id,roll_no) VALUES(1,101),(1,102);
START TRANSACTION;
INSERT INTO employee(emp_id,name,department,age,salary,city) VALUES(2010,'temp1','Temp',29,12000,'Bengaluru'),(2011,'temp2','Temp',31,13000,'Bengaluru'),(2012,'temp3','Temp',27,11000,'Bengaluru'),(2013,'temp4','Temp',26,14000,'Bengaluru'),(2014,'temp5','Temp',28,15000,'Bengaluru');
ROLLBACK;
ALTER TABLE employee ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'active';
ALTER TABLE employee ADD UNIQUE KEY unique_emp_name (name);
INSERT INTO employee(emp_id,name,department,age,salary,city,status) VALUES(3001,'haj','CSE',24,16000,'Delhi','active') ON DUPLICATE KEY UPDATE name=VALUES(name);
INSERT INTO employee(emp_id,name,department,age,salary,city) VALUES(4001,'trial','Temp',29,20000,'Delhi') ON DUPLICATE KEY UPDATE name=VALUES(name);
UPDATE employee SET salary=salary+1000 WHERE emp_id=4001;
DELETE FROM employee WHERE emp_id=4001;