ALL MEGHA MAM ASS TILL CASE STUDY 1

create database gourav;
use gourav;

CREATE TABLE department(dno int primary key,dname varchar(30));
insert into department(dno,dname)
values(1,"Mathematics"),(2,"Physics"),(3,"Computer Science"),(4,"MCA"),(5,"Management");

create table branch( bcode int primary key, bname varchar(30), dno int);
alter table branch add foreign key (DNO) references department(DNO);

insert into branch (bcode,bname,dno)
values(101,"Data Science",4),(102,"Calculus",1),(103,"Data Structure",3),(104,"Artificial intelligence",4),(105,"Supply Chain",5),(106,"Algebra",1),(107,"Quantum Theory",2),(108,"Human Resources",5),(109,"Thermodynamics",2),(110,"Algorithms",3);

create table branch_course( bcode int, Ccode int, semester varchar(50));
ALTER TABLE branch_course Modify COLUMN semester INT;
desc branch_course;
alter table branch_course add primary key (bcode,Ccode);
alter table branch_course add foreign key (bcode) references branch (bcode);
alter table branch_course add foreign key (ccode) references course (Ccode);

create table course( Ccode int primary key, Cname varchar(30), Credits int , dno int);
alter table course add foreign key (dno) references department (dno);

alter table branch_course add foreign key (ccode) references course (Ccode);

INSERT INTO course (Ccode, Cname, Credits, dno) VALUES
(1011, 'Programming in Python', 3, 2),
(1012, 'Database Management Systems', 4, 3),
(1013, 'Introduction to Statistics', 3, 2),
(1014, 'Software Engineering', 4, 4),
(1015, 'Digital Marketing', 3, 5),
(1016, 'Microeconomics', 3, 1),
(1017, 'Computer Graphics', 4, 3),
(1018, 'Leadership and Management', 3, 5),
(1019, 'Web Development', 4, 2),
(1020, 'Organizational Behavior', 3, 5),
(1021, 'Machine Learning', 5, 4),
(1022, 'Operating Systems', 4, 3),
(1023, 'Corporate Finance', 4, 5),
(1024, 'Network Security', 4, 2),
(1025, 'Introduction to Psychology', 3, 1),
(1026, 'Ethical Hacking', 4, 3),
(1027, 'Human-Computer Interaction', 4, 4),
(1028, 'Strategic Management', 4, 5),
(1029, 'Mobile Application Development', 4, 4),
(1030, 'Artificial Intelligence Ethics', 3, 4),
(1031, 'Data Mining', 4, 3),
(1032, 'Macroeconomics', 3, 1),
(1033, 'Database Design', 4, 3),
(1034, 'Project Management', 4, 4);


INSERT INTO branch_course (bcode, Ccode, semester) VALUES
(101, 1011, 1),
(102, 1012, 2),
(103, 1013, 3),
(104, 1014, 4),
(105, 1015, 5),
(106, 1016, 6),
(107, 1017, 1),
(108, 1018, 2),
(109, 1019, 3),
(110, 1020, 4),
(101, 1021, 5),
(102, 1022, 6),
(103, 1023, 1),
(104, 1024, 2),
(105, 1025, 3),
(106, 1026, 4),
(107, 1027, 5),
(108, 1028, 6),
(109, 1029, 1),
(110, 1030, 2),
(101, 1031, 3),
(102, 1032, 4),
(103, 1033, 5),
(104, 1034, 6);
INSERT INTO branch_course (bcode, Ccode, semester) VALUES
(102, 1011, 1),
(103, 1011, 2),
(104, 1011, 3),
(105, 1011, 4);



CREATE TABLE STUDENT (ROLLNO int(5),NAME VARCHAR(20),DOB DATE,GENDER CHAR(2),DOA DATE,BCODE int);
alter table student add primary key (rollno);
alter table student add foreign key (bcode) references branch (bcode);
ALTER TABLE STUDENT ADD CONSTRAINT CHECK (GENDER IN ('M','F'));
ALTER TABLE STUDENT ADD CONSTRAINT CHECK ( DOA between DATE '2023-01-01' and '2025-11-06');



INSERT INTO student (rollno, name, dob, gender, doa, bcode) VALUES
(12001, 'Gourav Upadhyay', '2002-10-07', 'M', '2023-10-10', 101),
(12002, 'Priya Sharma', '2003-05-15', 'F', '2024-02-21', 102),
(12003, 'Rahul Patel', '2002-07-25', 'M', '2023-12-05', 103),
(12004, 'Sneha Gupta', '2003-01-12', 'F', '2024-01-08', 104),
(12005, 'Amit Singh', '2002-11-30', 'M', '2023-11-25', 105),
(12006, 'Ananya Reddy', '2003-09-18', 'F', '2024-05-30', 106),
(12007, 'Rajesh Kumar', '2002-04-05', 'M', '2023-09-15', 107),
(12008, 'Pooja Mishra', '2003-02-20', 'F', '2024-03-12', 108),
(12009, 'Nitin Sharma', '2002-08-10', 'M', '2023-12-20', 109),
(12010, 'Shreya Das', '2003-06-28', 'F', '2024-04-03', 110),
(12011, 'Vikram Tiwari', '2002-03-14', 'M', '2023-08-05', 101),
(12012, 'Divya Singh', '2003-11-09', 'F', '2024-06-15', 102),
(12013, 'Rohan Gupta', '2002-01-25', 'M', '2023-11-30', 103),
(12014, 'Aishwarya Patel', '2003-04-18', 'F', '2024-02-28', 104),
(12015, 'Ankur Dubey', '2002-10-02', 'M', '2023-10-18', 105),
(12016, 'Sneha Rao', '2003-08-16', 'F', '2024-05-25', 106),
(12017, 'Rajat Sharma', '2002-05-30', 'M', '2023-09-05', 107),
(12018, 'Anjali Gupta', '2003-03-25', 'F', '2024-03-20', 108),
(12019, 'Aryan Kumar', '2002-09-10', 'M', '2023-12-25', 109),
(12020, 'Sakshi Singh', '2003-07-28', 'F', '2024-04-10', 110),
(12021, 'Varun Mishra', '2002-04-14', 'M', '2023-08-15', 101),
(12022, 'Aditi Reddy', '2003-12-09', 'F', '2024-06-25', 102),
(12023, 'Kunal Gupta', '2002-02-25', 'M', '2023-12-02', 103),
(12024, 'Meera Patel', '2003-05-18', 'F', '2024-03-02', 104),
(12025, 'Shivam Dubey', '2002-11-02', 'M', '2023-11-20', 105),
(12026, 'Preeti Rao', '2003-09-16', 'F', '2024-05-28', 106),
(12027, 'Vivek Sharma', '2002-06-30', 'M', '2023-09-10', 107),
(12028, 'Neha Gupta', '2003-04-25', 'F', '2024-03-18', 108),
(12029, 'Rahul Kumar', '2002-08-12', 'M', '2023-12-22', 109),
(12030, 'Simran Das', '2003-07-02', 'F', '2024-04-05', 110),
(12031, 'Aditya Tiwari', '2002-03-18', 'M', '2023-08-08', 101),
(12032, 'Ananya Singh', '2003-11-14', 'F', '2024-06-18', 102),
(12033, 'Akash Gupta', '2002-01-28', 'M', '2023-12-05', 103),
(12034, 'Riya Patel', '2003-04-22', 'F', '2024-02-25', 104);



create table enrolls( rollno INT, Ccode INT, sess varchar(15) , grade char(2));
alter table enrolls add primary key (rollno,Ccode,sess);
alter table enrolls add foreign key (rollno) references student (rollno);
alter table enrolls add foreign key (Ccode) references course (Ccode);
ALTER TABLE enrolls ADD CONSTRAINT CHECK (grade IN ('A','B','C','D','E','F','S'));
-- if you want remove any constraint
-- ALTER TABLE enrolls DROP CONSTRAINT enrolls_chk_1;

INSERT INTO enrolls (rollno, Ccode, sess, grade) VALUES
(12001, 1011, 'dec 2023', 'A'),
(12002, 1012, 'jan 2024', 'B'),
(12003, 1013, 'feb 2024', 'C'),
(12004, 1014, 'mar 2024', 'D'),
(12005, 1015, 'apr 2024', 'E'),
(12006, 1016, 'may 2024', 'F'),
(12007, 1017, 'jun 2024', 'A'),
(12008, 1018, 'jul 2024', 'B'),
(12009, 1019, 'aug 2024', 'C'),
(12010, 1020, 'sep 2024', 'D'),
(12011, 1021, 'oct 2024', 'E'),
(12012, 1022, 'nov 2024', 'F'),
(12013, 1023, 'dec 2024', 'A'),
(12014, 1024, 'jan 2025', 'B'),
(12015, 1025, 'feb 2025', 'C'),
(12016, 1026, 'mar 2025', 'D'),
(12017, 1027, 'apr 2025', 'E'),
(12018, 1028, 'may 2025', 'F'),
(12019, 1029, 'jun 2025', 'A'),
(12020, 1030, 'jul 2025', 'B'),
(12021, 1031, 'aug 2025', 'C'),
(12022, 1032, 'sep 2025', 'D'),
(12023, 1033, 'oct 2025', 'E'),
(12024, 1034, 'nov 2025', 'F'),
(12001, 1012, 'dec 2023', 'A'),
(12002, 1013, 'jan 2024', 'B'),
(12003, 1014, 'feb 2024', 'C'),
(12004, 1015, 'mar 2024', 'D'),
(12005, 1016, 'apr 2024', 'E'),
(12006, 1017, 'may 2024', 'F'),
(12007, 1018, 'jun 2024', 'A'),
(12008, 1019, 'jul 2024', 'B'),
(12009, 1020, 'aug 2024', 'C'),
(12010, 1021, 'sep 2024', 'D'),
(12011, 1022, 'oct 2024', 'E'),
(12012, 1023, 'nov 2024', 'F'),
(12013, 1024, 'dec 2024', 'A'),
(12014, 1025, 'jan 2025', 'B'),
(12015, 1026, 'feb 2025', 'C'),
(12016, 1027, 'mar 2025', 'D'),
(12017, 1028, 'apr 2025', 'E'),
(12018, 1029, 'may 2025', 'F'),
(12019, 1030, 'jun 2025', 'A'),
(12020, 1031, 'jul 2025', 'B'),
(12021, 1032, 'aug 2025', 'C'),
(12022, 1033, 'sep 2025', 'D'),
(12023, 1034, 'oct 2025', 'E'),
(12002, 1011, 'nov 2023', 'A'),
(12003, 1012, 'dec 2023', 'B'),
(12004, 1013, 'jan 2024', 'C'),
(12005, 1014, 'feb 2024', 'D'),
(12006, 1015, 'mar 2024', 'E'),
(12007, 1016, 'apr 2024', 'F'),
(12008, 1017, 'may 2024', 'A'),
(12009, 1018, 'jun 2024', 'B'),
(12010, 1019, 'jul 2024', 'C'),
(12011, 1020, 'aug 2024', 'D'),
(12012, 1021, 'sep 2024', 'E'),
(12013, 1022, 'oct 2024', 'F');
INSERT INTO enrolls (rollno, Ccode, sess, grade) VALUES
(12002, 1011, 'jan 2023', 'A'),
(12002, 1012, 'oct 2023', 'A'),
(12002, 1013, 'feb 2024', 'A');
INSERT INTO enrolls (rollno, Ccode, sess, grade) VALUES
(12003, 1011, 'sep 2023', 'A'),
(12003, 1012, 'feb 2024', 'B'),
(12003, 1013, 'mar 2024', 'A');
UPDATE enrolls
SET grade = 'S'
WHERE rollno IN (12003, 12002);


 SELECT *from department As d where d.dno IN
 (SELECT b.dno FROM branch AS b Group BY b.dno HAVING COUNT(b.dno) >3 );

  SELECT *from department As d where d.dno IN
 (SELECT c.dno FROM course AS c Group BY c.dno HAVING COUNT(c.dno) >5 );


SELECT * FROM course AS c WHERE c.Ccode IN
(SELECT bc.Ccode FROM branch_course AS bc
GROUP BY bc.Ccode HAVING COUNT(bc.Ccode) > 3);


SELECT * FROM STUDENT S WHERE S.ROLLNO IN (SELECT E.ROLLNO FROM ENROLLS
E WHERE E.GRADE ='S' GROUP BY E.ROLLNO HAVING count(E.GRADE) > 2);


CREATE view stu_cour_comp AS SELECT S.ROLLNO, S.NAME,COUNT(E.CCODE) AS course_completed
FROM STUDENT as S,ENROLLS as E
WHERE S.ROLLNO = E.ROLLNO AND E.GRADE != 'E'
GROUP BY E.ROLLNO;

select *from stu_cour_comp order by course_completed;

















create database gourav_ass_2;
use gourav_ass_2;

create table customer(Customerno varchar(5),cname varchar(25));
 alter table customer ADD primary key(customerno);
 alter table customer ADD constraint chk3 CHECK (customerno LIKE 'C%');

 create table cust_order(orderno varchar(5), odate date, customerno varchar(5),
 ord_amt numeric(6) default 0);
  alter table cust_order ADD primary key (orderno);
  alter table cust_order ADD foreign key(customerno) references customer(customerno);
  alter table cust_order ADD constraint chk2 CHECK (orderno LIKE 'O%');
  -- ALTER TABLE cust_order DROP CONSTRAINT chk2;  if you want to delete constraint

create table item(
    itemno varchar(5),
    intem_name varchar(30),
    unit_price numeric(5)
);
alter table item add primary key(itemno);
alter table item ADD constraint chk1 CHECK(itemno LIKE 'I%');

create table order_item(
    orderno varchar(5),
    itemno varchar(5),
    qty numeric(3)
);
alter table order_item ADD primary key (orderno,itemno);
alter table order_item ADD foreign key (orderno) references cust_order (orderno);
alter table order_item ADD foreign key (itemno) references item (itemno);


INSERT INTO customer (Customerno, cname) VALUES
    ('C0001', 'Gourav Upadhyay'),
    ('C0002', 'Priya Patel'),
    ('C0003', 'Rahul Sharma'),
    ('C0004', 'Neha Singh'),
    ('C0005', 'Anjali Gupta'),
    ('C0006', 'Manish Verma'),
    ('C0007', 'Deepika Reddy'),
    ('C0008', 'Rohit Kumar'),
    ('C0009', 'Pooja Choudhary'),
    ('C0010', 'Alok Mishra');

INSERT INTO item (itemno,intem_name, unit_price) values
    ('I0002', 'Mouse',150),
    ('I0002', 'Mouse',150),
    ('I0003', 'Keyboard', 200),
    ('I0004', 'Hard Drive', 500),
    ('I0005', 'RAM', 1200),
    ('I0006', 'Webcam', 1500),
    ('I0007', 'Ethernet Cable', 300);

INSERT INTO cust_order (orderno, odate, customerno, ord_amt) VALUES
    ('O0001', STR_TO_DATE('01,01,2022', '%d,%m,%Y'), 'C0005', 0),
    ('O0002', STR_TO_DATE('15,04,2022', '%d,%m,%Y'), 'C0003', 0),
    ('O0003', STR_TO_DATE('22,07,2022', '%d,%m,%Y'), 'C0007', 0),
    ('O0004', STR_TO_DATE('10,10,2022', '%d,%m,%Y'), 'C0002', 0),
    ('O0005', STR_TO_DATE('05,03,2023', '%d,%m,%Y'), 'C0004', 0),
    ('O0006', STR_TO_DATE('20,06,2023', '%d,%m,%Y'), 'C0001', 0),
    ('O0007', STR_TO_DATE('12,09,2023', '%d,%m,%Y'), 'C0006', 0),
    ('O0008', STR_TO_DATE('03,12,2022', '%d,%m,%Y'), 'C0007', 0),
    ('O0009', STR_TO_DATE('18,05,2022', '%d,%m,%Y'), 'C0010', 0),
    ('O0010', STR_TO_DATE('26,08,2022', '%d,%m,%Y'), 'C0001', 0),
    ('O0011', STR_TO_DATE('14,11,2022', '%d,%m,%Y'), 'C0003', 0),
    ('O0012', STR_TO_DATE('09,02,2023', '%d,%m,%Y'), 'C0006', 0),
    ('O0013', STR_TO_DATE('24,04,2023', '%d,%m,%Y'), 'C0005', 0),
    ('O0014', STR_TO_DATE('21,07,2023', '%d,%m,%Y'), 'C0004', 0),
    ('O0015', STR_TO_DATE('04,10,2023', '%d,%m,%Y'), 'C0002', 0),
    ('O0016', STR_TO_DATE('10,01,2022', '%d,%m,%Y'), 'C0006', 0),
    ('O0017', STR_TO_DATE('13,06,2022', '%d,%m,%Y'), 'C0005', 0),
    ('O0018', STR_TO_DATE('27,09,2022', '%d,%m,%Y'), 'C0003', 0),
    ('O0019', STR_TO_DATE('08,12,2022', '%d,%m,%Y'), 'C0007', 0),
    ('O0020', STR_TO_DATE('16,03,2023', '%d,%m,%Y'), 'C0004', 0),
    ('O0021', STR_TO_DATE('18,04,2022', '%d,%m,%Y'), 'C0008', 0);

INSERT INTO order_item (orderno, itemno, qty) VALUES
    ('O0001', 'I0007', 1),
    ('O0001', 'I0002', 3),
    ('O0002', 'I0003', 4),
    ('O0002', 'I0004', 2),
    ('O0003', 'I0005', 6),
    ('O0003', 'I0002', 5),
    ('O0004', 'I0003', 2),
    ('O0004', 'I0004', 1),
    ('O0005', 'I0007', 3),
    ('O0005', 'I0002', 4),
    ('O0006', 'I0003', 1),
    ('O0006', 'I0004', 2),
    ('O0007', 'I0005', 3),
    ('O0007', 'I0007', 4),
    ('O0008', 'I0002', 1),
    ('O0008', 'I0003', 2),
    ('O0009', 'I0004', 1),
    ('O0009', 'I0005', 4),
    ('O0010', 'I0006', 2),
    ('O0010', 'I0005', 4),
    ('O0011', 'I0002', 3),
    ('O0011', 'I0006', 1),
    ('O0012', 'I0007', 1),
    ('O0012', 'I0003', 1),
    ('O0013', 'I0004', 4),
    ('O0013', 'I0005', 2),
    ('O0014', 'I0005', 3),
    ('O0014', 'I0004', 4),
    ('O0015', 'I0003', 3),
    ('O0015', 'I0004', 1),
    ('O0016', 'I0006', 1),
    ('O0016', 'I0005', 2),
    ('O0017', 'I0003', 2),
    ('O0017', 'I0002', 5),
    ('O0018', 'I0006', 5),
    ('O0018', 'I0004', 2),
    ('O0019', 'I0006', 1),
    ('O0019', 'I0002', 2),
    ('O0020', 'I0003', 1),
    ('O0021', 'I0005', 2);


select *from customer AS c
where c.customerno IN(select o.customerno
from cust_order AS o GROUP BY o.customerno
having count(o.orderno)>2);

select *from item AS k
where k.unit_price < (select avg
(unit_price) from item);

SELECT orderno, SUM(qty)
FROM order_item
GROUP BY orderno;

select *from item where itemno
IN (select itemno from order_item
group by itemno having
count(itemno) >= (select (count(*)/4)
from cust_order));

SELECT * FROM item
WHERE itemno IN (
    SELECT itemno
    FROM order_item
    GROUP BY itemno
    HAVING COUNT(itemno) >= (SELECT (COUNT(*) / 4) FROM cust_order)
);

update cust_order set ord_amt = (select sum(o.qty * i.unit_price)
from order_item AS o, item AS i where
cust_order.orderno = o.orderno AND o.itemno = i.itemno);

CREATE VIEW customer_details AS
SELECT c.Customerno, c.cname, COUNT(orderno) AS order_count
FROM customer AS c , cust_order AS o
where c.customerno = o.customerno
GROUP BY c.Customerno, c.cname;











create database gourav_ass_3;
use gourav_ass_3;
create table depts(deptno varchar(5),dname varchar(30));
alter table depts add primary key(deptno);
alter table depts add constraint chk1 check(deptno like 'D%');

create table skill(skill_code varchar(5),description varchar(30),charge_outrage numeric(3));
alter table skill add primary key(skill_code);
alter table skill add constraint chk2 check(skill_code like 'S%');

CREATE TABLE STAFF (STAFFNO numeric (5), NAME VARCHAR(30), DOB DATE, GENDER CHAR (2), DOJ DATE, DESIGNATION VARCHAR(30), BASIC_PAY numeric (6), DEPTNO VARCHAR(5));
ALTER TABLE STAFF ADD PRIMARY KEY(STAFFNO);
ALTER TABLE STAFF ADD FOREIGN KEY(DEPTNO) REFERENCES DEPTS (DEPTNO);
ALTER TABLE STAFF ADD CONSTRAINT CHK3 CHECK(GENDER IN('M','F'));

CREATE TABLE STAFF_SKILL (STAFFNO NUMERIC(5), SKILL_CODE VARCHAR(5));
ALTER TABLE STAFF_SKILL ADD PRIMARY KEY (STAFFNO, SKILL_CODE);
ALTER TABLE STAFF_SKILL ADD FOREIGN KEY(STAFFNO) REFERENCES STAFF(STAFFNO);
ALTER TABLE STAFF_SKILL ADD FOREIGN KEY(SKILL_CODE) REFERENCES SKILL(SKILL_CODE);

CREATE TABLE PROJECT(PROJECTNO VARCHAR(5), PNAME VARCHAR(30), START_DATE DATE, END_DATE DATE, PROJECT_MANAGER_STAFFNO numeric(5));
ALTER TABLE PROJECT ADD PRIMARY KEY (PROJECTNO);
ALTER TABLE PROJECT ADD CONSTRAINT CHK4 CHECK(PROJECTNO LIKE 'P%');
ALTER TABLE PROJECT ADD FOREIGN KEY (PROJECT_MANAGER_STAFFNO) REFERENCES STAFF(STAFFNO);

CREATE TABLE WORKS (STAFFNO NUMERIC(5), PROJECTNO VARCHAR(5),DATE_WORKED_ON DATE, INTIME TIMESTAMP, OUTTIME TIMESTAMP);
ALTER TABLE WORKS ADD PRIMARY KEY (STAFFNO,PROJECTNO);
ALTER TABLE WORKS ADD FOREIGN KEY(STAFFNO) REFERENCES STAFF(STAFFNO);
ALTER TABLE WORKS ADD FOREIGN KEY(PROJECTNO) REFERENCES PROJECT(PROJECTNO);

INSERT INTO depts VALUES('D0001','Accounts');
INSERT INTO depts VALUES('D0002','Human Resources');
INSERT INTO depts VALUES('D0003','Marketing');
INSERT INTO depts VALUES('D0004','Information Technology');
INSERT INTO depts VALUES('D0005','Operations');
INSERT INTO depts VALUES('D0006','Sales');
INSERT INTO depts VALUES('D0007','Research and Development');
INSERT INTO depts VALUES('D0008','Customer Service');


INSERT INTO STAFF VALUES (10001, 'Gourav Upadhyay', '1990-05-15', 'M', '2015-03-20', 'Manager', 50000, 'D0001');
INSERT INTO STAFF VALUES (10002, 'Rahul Sharma', '1992-07-10', 'M', '2016-05-25', 'Assistant Manager', 45000, 'D0002');
INSERT INTO STAFF VALUES (10003, 'Priya Patel', '1988-11-30', 'F', '2014-02-15', 'Senior Developer', 55000, 'D0003');
INSERT INTO STAFF VALUES (10004, 'Amit Kumar', '1995-04-20', 'M', '2017-09-10', 'HR Specialist', 48000, 'D0004');
INSERT INTO STAFF VALUES (10005, 'Neha Gupta', '1993-08-05', 'M', '2016-12-12', 'Sales Executive', 42000, 'D0005');
INSERT INTO STAFF VALUES (10006, 'Anjali Singh', '1990-01-25', 'F', '2015-08-20', 'Financial Analyst', 49000, 'D0006');
INSERT INTO STAFF VALUES (10007, 'Rajesh Sharma', '1987-03-18', 'M', '2013-04-30', 'Marketing Coordinator', 47000, 'D0007');
INSERT INTO STAFF VALUES (10008, 'Sneha Kapoor', '1991-06-12', 'F', '2016-10-08', 'Operations Manager', 52000, 'D0008');
INSERT INTO STAFF VALUES (10009, 'Ravi Singh', '1989-09-08', 'M', '2015-07-05', 'Customer Service', 43000, 'D0001');
INSERT INTO STAFF VALUES (10010, 'Pooja Sharma', '1994-02-28', 'F', '2017-11-15', 'Finance Manager', 56000, 'D0002');
INSERT INTO STAFF VALUES (10011, 'Manoj Patel', '1996-06-20', 'M', '2018-03-03', 'Research Analyst', 46000, 'D0001');
INSERT INTO STAFF VALUES (10012, 'Deepak Gupta', '1992-10-15', 'F', '2016-06-18', 'Sales Manager', 53000, 'D0004');
INSERT INTO STAFF VALUES (10013, 'Smita Sharma', '1986-12-05', 'M', '2012-09-22', 'IT Specialist', 48000, 'D0005');
INSERT INTO STAFF VALUES (10014, 'Vikram Singh', '1990-04-30', 'F', '2015-02-10', 'HR Assistant', 44000, 'D0006');
INSERT INTO STAFF VALUES (10015, 'Nisha Kapoor', '1988-08-15', 'M', '2013-11-05', 'Operations Coordinator', 51000, 'D0001');
INSERT INTO STAFF VALUES (10016, 'Rahul Gupta', '1993-03-10', 'F', '2016-08-28', 'Marketing Manager', 54000, 'D0008');
INSERT INTO STAFF VALUES (10017, 'Suresh Singh', '1987-07-20', 'M', '2014-04-15', 'Customer Service Supervisor', 47000, 'D0001');
INSERT INTO STAFF VALUES (10018, 'Preeti Patel', '1991-11-12', 'F', '2017-10-08', 'Finance Analyst', 49000, 'D0002');
INSERT INTO STAFF VALUES (10019, 'Ajay Kumar', '1995-01-25', 'M', '2018-06-20', 'Research Coordinator', 45000, 'D0003');
INSERT INTO STAFF VALUES (10020, 'Kavita Sharma', '1990-05-08', 'F', '2015-09-05', 'Sales Associate', 42000, 'D0004');
INSERT INTO STAFF VALUES (10021, 'Prakash Singh', '1986-09-30', 'M', '2012-12-20', 'IT Manager', 55000, 'D0001');
INSERT INTO STAFF VALUES (10022, 'Anita Gupta', '1994-01-15', 'F', '2017-02-10', 'HR Coordinator', 46000, 'D0006');
INSERT INTO STAFF VALUES (10023, 'Rajiv Kapoor', '1989-05-05', 'M', '2014-08-18', 'Operations Supervisor', 51000, 'D0007');
INSERT INTO STAFF VALUES (10024, 'Sunita Sharma', '1992-07-10', 'F', '2016-10-25', 'Marketing Assistant', 48000, 'D0008');
INSERT INTO STAFF VALUES (10025, 'Vijay Singh', '1988-11-15', 'M', '2013-03-03', 'Customer Service Manager', 53000, 'D0001');

INSERT INTO SKILL VALUES ('S0001', 'Programming', 100);
INSERT INTO SKILL VALUES ('S0002', 'Database Management', 120);
INSERT INTO SKILL VALUES ('S0003', 'Web Development', 110);
INSERT INTO SKILL VALUES ('S0004', 'Network Administration', 130);
INSERT INTO SKILL VALUES ('S0005', 'Cybersecurity', 260);
INSERT INTO SKILL VALUES ('S0006', 'Data Analysis', 150);
INSERT INTO SKILL VALUES ('S0007', 'Project Management', 110);
INSERT INTO SKILL VALUES ('S0008', 'UI/UX Design', 120);
INSERT INTO SKILL VALUES ('S0009', 'Mobile App Development', 180);
INSERT INTO SKILL VALUES ('S0010', 'Cloud Computing', 100);
INSERT INTO SKILL VALUES ('S0011', 'Machine Learning', 200);
INSERT INTO SKILL VALUES ('S0012', 'Artificial Intelligence', 210);
INSERT INTO SKILL VALUES ('S0013', 'Digital Marketing', 110);
INSERT INTO SKILL VALUES ('S0014', 'Business Intelligence', 230);
INSERT INTO SKILL VALUES ('S0015', 'Quality Assurance', 200);
INSERT INTO SKILL VALUES ('S0016', 'Accounting', 120);
INSERT INTO SKILL VALUES ('S0017', 'Finance', 260);
INSERT INTO SKILL VALUES ('S0018', 'Human Resources', 100);
INSERT INTO SKILL VALUES ('S0019', 'Sales', 110);
INSERT INTO SKILL VALUES ('S0020', 'Marketing', 120);

INSERT INTO STAFF_SKILL VALUES
(10001, 'S0001'), (10001, 'S0002'), (10001, 'S0003'), (10001, 'S0004'),
(10001, 'S0005'), (10001, 'S0006'), (10004, 'S0007'), (10004, 'S0008'),
(10005, 'S0009'), (10005, 'S0010'), (10006, 'S0011'), (10006, 'S0012'),
(10007, 'S0013'), (10007, 'S0014'), (10008, 'S0015'), (10008, 'S0001'),
(10009, 'S0002'), (10009, 'S0003'), (10010, 'S0004'), (10010, 'S0005'),
(10011, 'S0004'), (10011, 'S0007'), (10012, 'S0004'), (10012, 'S0009'),
(10013, 'S0004'), (10013, 'S0011'), (10014, 'S0004'), (10014, 'S0013'),
(10015, 'S0014'), (10015, 'S0015'), (10016, 'S0001'), (10016, 'S0002'),
(10017, 'S0003'), (10017, 'S0004'), (10018, 'S0005'), (10018, 'S0006'),
(10019, 'S0007'), (10019, 'S0008'), (10020, 'S0009'), (10020, 'S0010');

INSERT INTO PROJECT (PROJECTNO, PNAME, START_DATE, END_DATE, PROJECT_MANAGER_STAFFNO) VALUES
('P0001', 'Project Alpha', '2023-01-15', '2023-07-15', 10001),
('P0002', 'Project Beta', '2023-03-20', '2023-09-20', 10002),
('P0003', 'Project Gamma', '2023-05-10', '2024-01-10', 10003),
('P0004', 'Project Delta', '2023-07-25', '2024-02-25', 10004),
('P0005', 'Project Epsilon', '2023-09-05', '2024-03-05', 10005),
('P0006', 'Project Zeta', '2023-11-15', '2024-05-15', 10006),
('P0007', 'Project Eta', '2024-01-20', '2024-07-20', 10007);

INSERT INTO WORKS (STAFFNO, PROJECTNO, DATE_WORKED_ON, INTIME, OUTTIME) VALUES
(10001, 'P0001', '2023-01-15', '2023-01-15 09:00:00', '2023-01-15 17:00:00'),
(10001, 'P0002', '2023-03-20', '2023-03-20 08:30:00', '2023-03-20 16:30:00'),
(10002, 'P0001', '2023-01-16', '2023-01-16 09:15:00', '2023-01-16 17:15:00'),
(10002, 'P0003', '2023-05-11', '2023-05-11 08:45:00', '2023-05-11 16:45:00'),
(10003, 'P0004', '2023-07-26', '2023-07-26 09:30:00', '2023-07-26 17:30:00'),
(10003, 'P0002', '2023-03-21', '2023-03-21 08:00:00', '2023-03-21 16:00:00'),
(10004, 'P0005', '2023-09-06', '2023-09-06 09:45:00', '2023-09-06 17:45:00'),
(10004, 'P0006', '2023-11-16', '2023-11-16 08:30:00', '2023-11-16 16:30:00'),
(10005, 'P0003', '2023-05-12', '2023-05-12 10:00:00', '2023-05-12 18:00:00'),
(10005, 'P0007', '2024-01-21', '2024-01-21 08:15:00', '2024-01-21 16:15:00'),
(10006, 'P0001', '2023-01-17', '2023-01-17 10:15:00', '2023-01-17 18:15:00'),
(10006, 'P0004', '2023-07-27', '2023-07-27 08:45:00', '2023-07-27 16:45:00'),
(10007, 'P0002', '2023-03-22', '2023-03-22 10:30:00', '2023-03-22 18:30:00'),
(10007, 'P0005', '2023-09-07', '2023-09-07 08:00:00', '2023-09-07 16:00:00'),
(10008, 'P0006', '2023-11-17', '2023-11-17 10:45:00', '2023-11-17 18:45:00'),
(10008, 'P0003', '2023-05-13', '2023-05-13 08:30:00', '2023-05-13 16:30:00'),
(10009, 'P0001', '2023-01-18', '2023-01-18 11:00:00', '2023-01-18 19:00:00'),
(10009, 'P0004', '2023-07-28', '2023-07-28 09:00:00', '2023-07-28 17:00:00'),
(10010, 'P0002', '2023-03-23', '2023-03-23 11:15:00', '2023-03-23 19:15:00'),
(10010, 'P0005', '2023-09-08', '2023-09-08 08:45:00', '2023-09-08 16:45:00'),
(10011, 'P0006', '2023-11-18', '2023-11-18 11:30:00', '2023-11-18 19:30:00'),
(10011, 'P0003', '2023-05-14', '2023-05-14 09:30:00', '2023-05-14 17:30:00'),
(10012, 'P0001', '2023-01-19', '2023-01-19 11:45:00', '2023-01-19 19:45:00'),
(10012, 'P0004', '2023-07-29', '2023-07-29 10:00:00', '2023-07-29 18:00:00');

SELECT deptno, COUNT(staffno) FROM staff GROUP BY deptno;

SELECT *FROM staff WHERE basic_pay IN (SELECT AVG(basic_pay) from staff);

SELECT *FROM staff WHERE staffno IN(SELECT staffno FROM staff_skill GROUP BY staffno HAVING COUNT(staffno) > 3);

select *from staff where staffno IN
(select staffno from staff_skill where skill_code IN
(select skill_code from skill where charge_outrage > 200));

CREATE VIEW staffcount AS SELECT d.deptno,d.dname,COUNT(s.staffno)
AS STAFF_COUNT,SUM(S.BASIC_PAY) AS Depeartment_expense FROM
DEPTS D,STAFF S WHERE D.DEPTNO = S.DEPTNO GROUP BY D.DEPTNO,D.DNAME;

SELECT D.deptno,D.dname ,COUNT(S.staffno) AS Number_Of_Staff
FROM depts D, STAFF S WHERE D.deptno = S.deptno
GROUP BY D.deptno, D.dname HAVING COUNT(S.staffno) > 5;



drop table ACCOUNT;
TRUNCATE TABLE branch;
DELETE FROM branch WHERE BNAME = 'Surat';

-- SET SQL_SAFE_UPDATES = 0;



-- ASSIGNMENT 4
create database gourav_ass_4;
use gourav_ass_4;
CREATE TABLE customer(CID INT PRIMARY KEY,CNAME VARCHAR(30));

CREATE TABLE BRANCH( BCODE VARCHAR(5) PRIMARY KEY, BNAME VARCHAR(30) );

ALTER TABLE BRANCH ADD CONSTRAINT CPK CHECK (BCODE LIKE 'B');
CREATE TABLE ACCOUNT(ANO VARCHAR(5) PRIMARY KEY, ATYPE CHAR(2), BALANCE numeric(7), CID int, BCODE VARCHAR(5));
ALTER TABLE ACCOUNT ADD FOREIGN KEY(CID) REFERENCES CUSTOMER(CID);
ALTER TABLE ACCOUNT ADD FOREIGN KEY(BCODE) REFERENCES BRANCH(BCODE);
ALTER TABLE ACCOUNT ADD CONSTRAINT CPK2 CHECK (atype IN('S','C'));
ALTER TABLE ACCOUNT ADD CONSTRAINT CPK3 CHECK (ano LIKE 'A%');

CREATE TABLE TRANSACTION( TID VARCHAR(5) PRIMARY KEY,
ANO VARCHAR(5), TTYPE CHAR(2), TDATE DATE, TAMOUNT INTEGER );
ALTER TABLE TRANSACTION ADD FOREIGN KEY(ANO) REFERENCES ACCOUNT(ANO);
ALTER TABLE TRANSACTION ADD CONSTRAINT CPK4 CHECK(TTYPE IN('W','D'));
ALTER TABLE TRANSACTION ADD CONSTRAINT CPK5 CHECK (tid LIKE 'T%');

INSERT INTO customer (CID, CNAME)
VALUES
(10001, 'Gourav Upadhyay'),
(10002, 'Priya Patel'),
(10003, 'Rahul Singh'),
(10004, 'Ananya Sharma'),
(10005, 'Aarav Gupta'),
(10006, 'Neha Verma'),
(10007, 'Vivek Mishra'),
(10008, 'Kritika Sharma'),
(10009, 'Sandeep Yadav'),
(10010, 'Shreya Joshi');

INSERT INTO branch (BCODE, BNAME)
VALUES
('B0001', 'Ahmedabad'),
('B0002', 'Surat'),
('B0003', 'Vadodara'),
('B0004', 'Rajkot');

INSERT INTO account (ANO, ATYPE, BALANCE, CID, BCODE)
VALUES
('A0001', 'S', 5000, 10001, 'B0001'),
('A0002', 'S', 7000, 10002, 'B0004'),
('A0003', 'S', 3000, 10002, 'B0001'),
('A0004', 'C', 9000, 10004, 'B0001'),
('A0005', 'S', 6000, 10001, 'B0002'),
('A0006', 'C', 2000, 10002, 'B0004'),
('A0007', 'S', 4000, 10007, 'B0004'),
('A0008', 'C', 8000, 10002, 'B0001'),
('A0009', 'S', 10000, 10009, 'B0002'),
('A0010', 'C', 12000, 10010, 'B0004'),
('A0011', 'S', 15000, 10001, 'B0001'),
('A0012', 'C', 17000, 10002, 'B0001'),
('A0013', 'S', 20000, 10003, 'B0002'),
('A0014', 'S', 22000, 10001, 'B0001'),
('A0015', 'S', 25000, 10002, 'B0004');

INSERT INTO account (ANO, ATYPE, BALANCE, CID, BCODE)
VALUES
('A0016', 'C', 1000, 10001, 'B0001');


INSERT INTO transaction (TID, ANO, TTYPE, TDATE, TAMOUNT)
VALUES
('T0001', 'A0001', 'W', '2024-03-30', 1000),
('T0002', 'A0001', 'D', '2024-03-30', 2000),
('T0003', 'A0001', 'W', '2024-03-30', 1500),
('T0004', 'A0001', 'D', '2024-03-30', 3000),
('T0005', 'A0001', 'W', '2024-03-30', 2500),
('T0006', 'A0001', 'D', '2024-03-30', 4000),
('T0007', 'A0007', 'W', '2024-03-10', 2000),
('T0008', 'A0008', 'D', '2024-03-30', 3500),
('T0009', 'A0009', 'W', '2024-03-30', 3000),
('T0010', 'A0010', 'D', '2024-03-30', 5000),
('T0011', 'A0011', 'W', '2024-03-20', 4000),
('T0012', 'A0012', 'D', '2024-04-30', 6000),
('T0013', 'A0003', 'W', '2024-04-30', 4500),
('T0014', 'A0014', 'D', '2024-03-30', 7000),
('T0015', 'A0015', 'W', '2024-03-30', 5500),
('T0016', 'A0001', 'D', '2024-06-30', 8000),
('T0017', 'A0002', 'W', '2024-03-10', 1000),
('T0018', 'A0003', 'D', '2024-07-20', 2000),
('T0019', 'A0002', 'W', '2024-03-30', 1500),
('T0020', 'A0002', 'D', '2024-01-30', 3000),
('T0021', 'A0002', 'W', '2024-03-30', 2500),
('T0022', 'A0002', 'D', '2024-01-30', 4000),
('T0023', 'A0008', 'W', '2024-03-30', 2000),
('T0024', 'A0009', 'D', '2024-03-30', 3500),
('T0025', 'A0003', 'W', '2024-03-30', 3000);

-- C
SELECT *FROM CUSTOMER WHERE CID IN
(SELECT A1.CID FROM ACCOUNT A1, ACCOUNT A2 WHERE A1.ATYPE = 'S' AND A2.ATYPE = 'C' AND A1.CID = A2.CID);

SELECT DISTINCT C.Cid, C.Cname  FROM Customer C
INNER JOIN Account A ON C.Cid = A.Cid
INNER JOIN Account A1 ON C.Cid = A1.Cid
WHERE A.Atype = 'S' AND A1.Atype = 'C';

-- D
SELECT B.BCODE, B.BNAME, COUNT(A.ANO) as No_of_accounts FROM BRANCH B, ACCOUNT A
WHERE B.BCODE=A.BCODE GROUP BY B.BCODE;


SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_accounts
FROM Branch B
JOIN Account A ON B. Bcode=A. Bcode
GROUP BY B. Bcode, B. Bname;

-- E

SELECT B.BCODE, B. BNAME, COUNT(A.bcode) AS No_of_Accounts
FROM BRANCH B, ACCOUNT A WHERE B.BCODE = A.BCODE
GROUP BY B.BCODE, B.BNAME
HAVING COUNT(A.bcode) <
(SELECT COUNT(a1.ano)/4 FROM account AS a1);


SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_Accounts
FROM Branch B, Account A WHERE B.Bcode = A.Bcode
GROUP BY B.Bcode, B. Bname
HAVING COUNT(A. Ano)<
(SELECT COUNT(Ano)/(SELECT COUNT(Bcode) FROM Branch) FROM Account);

SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS NoOfAccount
FROM Branch B JOIN Account A ON B.Bcode=A.Bcode
GROUP BY B. Bcode, B. Bname
HAVING COUNT(A. Ano) <
(SELECT COUNT(Ano)/ (SELECT COUNT(Bcode) FROM Branch) FROM Account);

-- F
SELECT c.cid, c.cname
FROM customer c, account a, transaction t
WHERE c.cid = a.cid AND a.ano = t.ano
group by t.tdate
having COUNT(t.tdate) = 3;

SELECT c.cid, c.cname
FROM customer c
JOIN account a ON c.cid = a.cid
JOIN transaction t ON a.ano = t.ano
GROUP BY c.cid, c.cname
HAVING COUNT(t.tdate) = 3;


SELECT c.cid, c.cname FROM customer c, account a, transaction t
WHERE c.cid = a.cid AND a.ano = t.ano
GROUP BY c.cid, c.cname, t.tdate
HAVING COUNT(t.tdate) = 3;


-- G
CREATE VIEW BRANCH_DATA AS SELECT B.BCODE, B.BNAME, COUNT(A.ANO) as No_of_accounts FROM BRANCH B, ACCOUNT A
WHERE B.BCODE=A.BCODE GROUP BY B.BCODE;

create view BRANCH_DATA2 AS
SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_accounts
FROM Branch B
JOIN Account A ON B. Bcode=A. Bcode
GROUP BY B. Bcode, B. Bname;

select *from branch_data;





                -- &^^^^^^^^^^^^^^case study 1st  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
create database CS1;
use CS1;

CREATE TABLE DEPARTMENT (
    D_NAME VARCHAR(30) PRIMARY KEY,
    D_LOCATION VARCHAR(50),
    FACILITIES INT
);

CREATE TABLE ALL_DOCTORS (
    DOC_NO VARCHAR(30) PRIMARY KEY,
    DEPARTMENT VARCHAR(30),
    FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT(D_NAME)
);

CREATE TABLE DOC_REG (
    DOC_NO VARCHAR(30) PRIMARY KEY,
    D_NAME VARCHAR(30),
    QUALIFICATION VARCHAR(30),
    SALARY DECIMAL(10, 2),
    EN_TIME TIME,
    EX_TIME TIME,
    ADDRESS VARCHAR(50),
    PH_NO VARCHAR(10),
    DOJ DATE,
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE DOC_ON_CALL (
    DOC_NO VARCHAR(30),
    D_NAME VARCHAR(50),
    QUALIFICATION VARCHAR(30),
    FS_PR_CL DECIMAL(10, 2),
    PYMT_DU DECIMAL(10, 2),
    ADDRESS VARCHAR(100),
    PH_NO VARCHAR(10),
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE PAT_ENTRY (
    PAT_NO VARCHAR(30) PRIMARY KEY,
    PAT_NAME VARCHAR(30),
    CHKUP_DT DATE,
    PT_AGE INT,
    SEX CHAR(1),
    RFRG_CSTNT VARCHAR(50),
    DIAGNOSIS VARCHAR(30),
    ADDRESS VARCHAR(100),
    CITY VARCHAR(30),
    PH_NO VARCHAR(10),
    DEPARTMENT VARCHAR(30),
    FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT(D_NAME)
);

CREATE TABLE PAT_CHKUP (
    PAT_NO VARCHAR(30),
    DOC_NO VARCHAR(30),
    DIAGNOSIS VARCHAR(30),
    STATUS VARCHAR(30),
    TREATMENT VARCHAR(30),
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE PAT_ADMIT (
    PAT_NO VARCHAR(30) PRIMARY KEY,
    ADV_PYMT DECIMAL(10, 2),
    MODE_PYMT VARCHAR(10),
    ROOM_NO INT,
    DEPTNAME VARCHAR(50),
    ADMTD_ON DATE,
    COND_ON VARCHAR(50),
    INVSTGTN_DN VARCHAR(30),
    TRMT_SDT VARCHAR(50),
    ATTDNT_NM VARCHAR(50),
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO)
);

CREATE TABLE PAT_DIS (
    PAT_NO VARCHAR(30),
    TR_ADVS VARCHAR(100),
    TR_GVN VARCHAR(100),
    MEDICINES VARCHAR(100),
    PYMT_GV DECIMAL(10, 2),
    DIS_ON DATE,
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO)
);

CREATE TABLE PAT_REG (
    PAT_NO VARCHAR(30) PRIMARY KEY,
    DATE_VIS DATE,
    CONDITIONN VARCHAR(30),
    TREATMENT VARCHAR(30),
    MEDICINES VARCHAR(30),
    DOC_NO VARCHAR(30),
    PAYMT DECIMAL(10, 2),
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE PAT_OPR (
    PAT_NO VARCHAR(30) PRIMARY KEY,
    DATE_OPR DATE,
    IN_COND VARCHAR(100),
    AFOP_COND VARCHAR(100),
    TY_OPERATION VARCHAR(100),
    MEDICINES VARCHAR(100),
    DOC_NO VARCHAR(30),
    OPTH_NO VARCHAR(20),
    OTHER_SUG VARCHAR(100),
    FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
    FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
);

CREATE TABLE ROOM_DETAILS (
    ROOM_NO VARCHAR(30) PRIMARY KEY,
    TYPE VARCHAR(50),
    STATUS VARCHAR(20),
    RM_DL_CRG DECIMAL(10, 2),
    OTHER_CRG DECIMAL(10, 2)
);


ALTER TABLE DOC_REG
ADD CONSTRAINT fk_doctor_number_prefix CHECK (DOC_NO LIKE 'DR%');

ALTER TABLE DOC_ON_CALL
ADD CONSTRAINT fk_doctor_number_prefix_call CHECK (DOC_NO LIKE 'DR%');

ALTER TABLE PAT_ENTRY
ADD CONSTRAINT pk_patient_number_prefix_entry CHECK (PAT_NO LIKE 'PT%'),
ADD CONSTRAINT valid_sex_entry CHECK (SEX IN ('M', 'F'));

ALTER TABLE ROOM_DETAILS
ADD CONSTRAINT valid_room_type CHECK (TYPE IN ('G', 'P')),
ADD CONSTRAINT valid_room_status CHECK (STATUS IN ('Y', 'N'));


-- Inserting data into DEPARTMENT table
INSERT INTO DEPARTMENT (D_NAME, D_LOCATION, FACILITIES) VALUES
('Cardiology', 'Mumbai', 5),
('Pediatrics', 'Delhi', 3),
('Orthopedics', 'Bangalore', 4),
('Gynecology', 'Kolkata', 3),
('Neurology', 'Chennai', 5);

-- Inserting data into ALL_DOCTORS table
INSERT INTO ALL_DOCTORS (DOC_NO, DEPARTMENT) VALUES
('DR001', 'Cardiology'),
('DR002', 'Pediatrics'),
('DR003', 'Orthopedics'),
('DR004', 'Gynecology'),
('DR005', 'Neurology');

-- Inserting data into DOC_REG table
INSERT INTO DOC_REG (DOC_NO, D_NAME, QUALIFICATION, SALARY, EN_TIME, EX_TIME, ADDRESS, PH_NO, DOJ) VALUES
('DR001', 'Gourav Upadhyay', 'MD Cardiology', 150000, '08:00:00', '16:00:00', '123 ABC Street, Mumbai', '9876543210', '2023-01-15'),
('DR002', 'Priya Sharma', 'MD Pediatrics', 120000, '09:00:00', '17:00:00', '456 XYZ Street, Delhi', '9876543211', '2023-02-20'),
('DR003', 'Rahul Singh', 'MS Orthopedics', 130000, '08:30:00', '16:30:00', '789 PQR Street, Bangalore', '9876543212', '2023-03-25'),
('DR004', 'Neha Gupta', 'MD Gynecology', 140000, '08:00:00', '16:00:00', '456 LMN Street, Kolkata', '9876543213', '2023-04-30'),
('DR005', 'Amit Patel', 'DM Neurology', 160000, '09:30:00', '17:30:00', '789 RST Street, Chennai', '9876543214', '2023-05-05');

-- Inserting data into DOC_ON_CALL table
INSERT INTO DOC_ON_CALL (DOC_NO, D_NAME, QUALIFICATION, FS_PR_CL, PYMT_DU, ADDRESS, PH_NO)
VALUES
('DR001', 'Gourav Upadhyay', 'MD Cardiology', 2000, 1500, '456 Sunrise Avenue, Mumbai', '9876543210'),
('DR002', 'Priya Sharma', 'MD Pediatrics', 2500, 1800, '789 Maple Street, Delhi', '9876543211'),
('DR003', 'Rahul Singh', 'MS Orthopedics', 1800, 1200, '123 Oak Lane, Bangalore', '9876543212'),
('DR004', 'Neha Gupta', 'MD Gynecology', 2200, 1600, '345 Elm Street, Kolkata', '9876543213'),
('DR005', 'Amit Patel', 'DM Neurology', 2400, 1700, '678 Pine Avenue, Chennai', '9876543214');


-- Inserting data into PAT_ENTRY table
INSERT INTO PAT_ENTRY (PAT_NO, PAT_NAME, CHKUP_DT, PT_AGE, SEX, RFRG_CSTNT, DIAGNOSIS, ADDRESS, CITY, PH_NO, DEPARTMENT) VALUES
('PT001', 'Ramesh Kumar', '2023-06-01', 35, 'M', 'Fever', 'Common Cold', '123 ABC Street', 'Mumbai', '9876543200', 'Cardiology'),
('PT002', 'Sunita Devi', '2023-06-05', 28, 'F', 'Headache', 'Migraine', '456 XYZ Street', 'Delhi', '9876543201', 'Pediatrics'),
('PT003', 'Amit Sharma', '2023-06-10', 45, 'M', 'Stomach Pain', 'Gastritis', '789 PQR Street', 'Bangalore', '9876543202', 'Orthopedics'),
('PT004', 'Priya Singh', '2023-06-15', 30, 'F', 'Cough', 'Bronchitis', '456 LMN Street', 'Kolkata', '9876543203', 'Gynecology'),
('PT005', 'Gopal Verma', '2023-06-20', 55, 'M', 'Chest Pain', 'Angina', '789 RST Street', 'Chennai', '9876543204', 'Neurology');

-- Inserting data into PAT_CHKUP table
INSERT INTO PAT_CHKUP (PAT_NO, DOC_NO, DIAGNOSIS, STATUS, TREATMENT) VALUES
('PT001', 'DR001', 'Common Cold', 'Admitted', 'Antibiotics'),
('PT002', 'DR002', 'Migraine', 'Regular', 'Painkillers'),
('PT003', 'DR003', 'Gastritis', 'Referred for Operation', 'Surgery'),
('PT004', 'DR004', 'Bronchitis', 'Admitted', 'Bronchodilators'),
('PT005', 'DR005', 'Angina', 'Regular', 'Cardiac Medications');

-- Inserting data into PAT_ADMIT table
INSERT INTO PAT_ADMIT (PAT_NO, ADV_PYMT, MODE_PYMT, ROOM_NO, DEPTNAME, ADMTD_ON, COND_ON, INVSTGTN_DN, TRMT_SDT, ATTDNT_NM) VALUES
('PT001', 5000, 'Cash', 101, 'Cardiology', '2023-06-01', 'Stable', 'ECG, Blood Test', '2023-06-02', 'Rita Sharma'),
('PT004', 7000, 'Card', 201, 'Gynecology', '2023-06-15', 'Critical', 'X-Ray, Ultrasound', '2023-06-16', 'Amit Singh');

-- Inserting data into PAT_DIS table
INSERT INTO PAT_DIS (PAT_NO, TR_ADVS, TR_GVN, MEDICINES, PYMT_GV, DIS_ON) VALUES
('PT001', 'Complete bed rest', 'Antibiotics', 'Paracetamol, Vitamin C', 5000, '2023-06-05'),
('PT004', 'Avoid cold food', 'Bronchodilators', 'Cough Syrup, Inhaler', 7000, '2023-06-20');

-- Inserting data into PAT_REG table
INSERT INTO PAT_REG (PAT_NO, DATE_VIS, CONDITIONN, TREATMENT, MEDICINES, DOC_NO, PAYMT) VALUES
('PT002', '2023-06-05', 'Migraine', 'Painkillers', 'Ibuprofen', 'DR002', 300),
('PT003', '2023-06-10', 'Gastritis', 'Surgery', 'Omeprazole', 'DR003', 500),
('PT005', '2023-06-20', 'Angina', 'Cardiac Medications', 'Aspirin', 'DR005', 400);

-- Inserting data into PAT_OPR table
INSERT INTO PAT_OPR (PAT_NO, DATE_OPR, IN_COND, AFOP_COND, TY_OPERATION, MEDICINES, DOC_NO, OPTH_NO, OTHER_SUG) VALUES
('PT003', '2023-06-11', 'Pre-Operative', 'Post-Operative', 'Appendectomy', 'Antibiotics', 'DR003', 'OT01', 'Avoid oily food');

-- Inserting data into ROOM_DETAILS table
INSERT INTO ROOM_DETAILS (ROOM_NO, TYPE, STATUS, RM_DL_CRG, OTHER_CRG) VALUES
(101, 'P', 'Y', 2000, 500),
(102, 'G', 'N', 1000, 300),
(201, 'P', 'Y', 2500, 600),
(202, 'G', 'Y', 1500, 400),
(203, 'P', 'N', 2200, 550);


























-- ********************************************************************************************************

use gourav_ass_2;
call NumberTimes('C0003');