API tools faq
paste
tampurus

GOURAV UPADHYAY MCA ALL ASSIGNMENT

tampurus
Jun 13th, 2024 (edited)
187
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 45.43 KB | None | 0 0
raw download clone embed print report
  1. create database gourav;
  2. use gourav;
  3.  
  4. CREATE TABLE department(dno int primary key,dname varchar(30));
  5. insert into department(dno,dname)
  6. values(1,"Mathematics"),(2,"Physics"),(3,"Computer Science"),(4,"MCA"),(5,"Management");
  7.  
  8. create table branch( bcode int primary key, bname varchar(30), dno int);
  9. alter table branch add foreign key (DNO) references department(DNO);
  10.  
  11. insert into branch (bcode,bname,dno)
  12. 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);
  13.  
  14. create table branch_course( bcode int, Ccode int, semester varchar(50));
  15. ALTER TABLE branch_course Modify COLUMN semester INT;
  16. desc branch_course;
  17. alter table branch_course add primary key (bcode,Ccode);
  18. alter table branch_course add foreign key (bcode) references branch (bcode);
  19. alter table branch_course add foreign key (ccode) references course (Ccode);
  20.  
  21. create table course( Ccode int primary key, Cname varchar(30), Credits int , dno int);
  22. alter table course add foreign key (dno) references department (dno);
  23.  
  24. alter table branch_course add foreign key (ccode) references course (Ccode);
  25.  
  26. INSERT INTO course (Ccode, Cname, Credits, dno) VALUES
  27. (1011, 'Programming in Python', 3, 2),
  28. (1012, 'Database Management Systems', 4, 3),
  29. (1013, 'Introduction to Statistics', 3, 2),
  30. (1014, 'Software Engineering', 4, 4),
  31. (1015, 'Digital Marketing', 3, 5),
  32. (1016, 'Microeconomics', 3, 1),
  33. (1017, 'Computer Graphics', 4, 3),
  34. (1018, 'Leadership and Management', 3, 5),
  35. (1019, 'Web Development', 4, 2),
  36. (1020, 'Organizational Behavior', 3, 5),
  37. (1021, 'Machine Learning', 5, 4),
  38. (1022, 'Operating Systems', 4, 3),
  39. (1023, 'Corporate Finance', 4, 5),
  40. (1024, 'Network Security', 4, 2),
  41. (1025, 'Introduction to Psychology', 3, 1),
  42. (1026, 'Ethical Hacking', 4, 3),
  43. (1027, 'Human-Computer Interaction', 4, 4),
  44. (1028, 'Strategic Management', 4, 5),
  45. (1029, 'Mobile Application Development', 4, 4),
  46. (1030, 'Artificial Intelligence Ethics', 3, 4),
  47. (1031, 'Data Mining', 4, 3),
  48. (1032, 'Macroeconomics', 3, 1),
  49. (1033, 'Database Design', 4, 3),
  50. (1034, 'Project Management', 4, 4);
  51.  
  52.  
  53. INSERT INTO branch_course (bcode, Ccode, semester) VALUES
  54. (101, 1011, 1),
  55. (102, 1012, 2),
  56. (103, 1013, 3),
  57. (104, 1014, 4),
  58. (105, 1015, 5),
  59. (106, 1016, 6),
  60. (107, 1017, 1),
  61. (108, 1018, 2),
  62. (109, 1019, 3),
  63. (110, 1020, 4),
  64. (101, 1021, 5),
  65. (102, 1022, 6),
  66. (103, 1023, 1),
  67. (104, 1024, 2),
  68. (105, 1025, 3),
  69. (106, 1026, 4),
  70. (107, 1027, 5),
  71. (108, 1028, 6),
  72. (109, 1029, 1),
  73. (110, 1030, 2),
  74. (101, 1031, 3),
  75. (102, 1032, 4),
  76. (103, 1033, 5),
  77. (104, 1034, 6);
  78. INSERT INTO branch_course (bcode, Ccode, semester) VALUES
  79. (102, 1011, 1),
  80. (103, 1011, 2),
  81. (104, 1011, 3),
  82. (105, 1011, 4);
  83.  
  84.  
  85.  
  86. CREATE TABLE STUDENT (ROLLNO int(5),NAME VARCHAR(20),DOB DATE,GENDER CHAR(2),DOA DATE,BCODE int);
  87. alter table student add primary key (rollno);
  88. alter table student add foreign key (bcode) references branch (bcode);
  89. ALTER TABLE STUDENT ADD CONSTRAINT CHECK (GENDER IN ('M','F'));
  90. ALTER TABLE STUDENT ADD CONSTRAINT CHECK ( DOA between DATE '2023-01-01' and '2025-11-06');
  91.  
  92.  
  93.  
  94. INSERT INTO student (rollno, name, dob, gender, doa, bcode) VALUES
  95. (12001, 'Gourav Upadhyay', '2002-10-07', 'M', '2023-10-10', 101),
  96. (12002, 'Priya Sharma', '2003-05-15', 'F', '2024-02-21', 102),
  97. (12003, 'Rahul Patel', '2002-07-25', 'M', '2023-12-05', 103),
  98. (12004, 'Sneha Gupta', '2003-01-12', 'F', '2024-01-08', 104),
  99. (12005, 'Amit Singh', '2002-11-30', 'M', '2023-11-25', 105),
  100. (12006, 'Ananya Reddy', '2003-09-18', 'F', '2024-05-30', 106),
  101. (12007, 'Rajesh Kumar', '2002-04-05', 'M', '2023-09-15', 107),
  102. (12008, 'Pooja Mishra', '2003-02-20', 'F', '2024-03-12', 108),
  103. (12009, 'Nitin Sharma', '2002-08-10', 'M', '2023-12-20', 109),
  104. (12010, 'Shreya Das', '2003-06-28', 'F', '2024-04-03', 110),
  105. (12011, 'Vikram Tiwari', '2002-03-14', 'M', '2023-08-05', 101),
  106. (12012, 'Divya Singh', '2003-11-09', 'F', '2024-06-15', 102),
  107. (12013, 'Rohan Gupta', '2002-01-25', 'M', '2023-11-30', 103),
  108. (12014, 'Aishwarya Patel', '2003-04-18', 'F', '2024-02-28', 104),
  109. (12015, 'Ankur Dubey', '2002-10-02', 'M', '2023-10-18', 105),
  110. (12016, 'Sneha Rao', '2003-08-16', 'F', '2024-05-25', 106),
  111. (12017, 'Rajat Sharma', '2002-05-30', 'M', '2023-09-05', 107),
  112. (12018, 'Anjali Gupta', '2003-03-25', 'F', '2024-03-20', 108),
  113. (12019, 'Aryan Kumar', '2002-09-10', 'M', '2023-12-25', 109),
  114. (12020, 'Sakshi Singh', '2003-07-28', 'F', '2024-04-10', 110),
  115. (12021, 'Varun Mishra', '2002-04-14', 'M', '2023-08-15', 101),
  116. (12022, 'Aditi Reddy', '2003-12-09', 'F', '2024-06-25', 102),
  117. (12023, 'Kunal Gupta', '2002-02-25', 'M', '2023-12-02', 103),
  118. (12024, 'Meera Patel', '2003-05-18', 'F', '2024-03-02', 104),
  119. (12025, 'Shivam Dubey', '2002-11-02', 'M', '2023-11-20', 105),
  120. (12026, 'Preeti Rao', '2003-09-16', 'F', '2024-05-28', 106),
  121. (12027, 'Vivek Sharma', '2002-06-30', 'M', '2023-09-10', 107),
  122. (12028, 'Neha Gupta', '2003-04-25', 'F', '2024-03-18', 108),
  123. (12029, 'Rahul Kumar', '2002-08-12', 'M', '2023-12-22', 109),
  124. (12030, 'Simran Das', '2003-07-02', 'F', '2024-04-05', 110),
  125. (12031, 'Aditya Tiwari', '2002-03-18', 'M', '2023-08-08', 101),
  126. (12032, 'Ananya Singh', '2003-11-14', 'F', '2024-06-18', 102),
  127. (12033, 'Akash Gupta', '2002-01-28', 'M', '2023-12-05', 103),
  128. (12034, 'Riya Patel', '2003-04-22', 'F', '2024-02-25', 104);
  129.  
  130.  
  131.  
  132. create table enrolls( rollno INT, Ccode INT, sess varchar(15) , grade char(2));
  133. alter table enrolls add primary key (rollno,Ccode,sess);
  134. alter table enrolls add foreign key (rollno) references student (rollno);
  135. alter table enrolls add foreign key (Ccode) references course (Ccode);
  136. ALTER TABLE enrolls ADD CONSTRAINT CHECK (grade IN ('A','B','C','D','E','F','S'));
  137. -- if you want remove any constraint
  138. -- ALTER TABLE enrolls DROP CONSTRAINT enrolls_chk_1;
  139.  
  140. INSERT INTO enrolls (rollno, Ccode, sess, grade) VALUES
  141. (12001, 1011, 'dec 2023', 'A'),
  142. (12002, 1012, 'jan 2024', 'B'),
  143. (12003, 1013, 'feb 2024', 'C'),
  144. (12004, 1014, 'mar 2024', 'D'),
  145. (12005, 1015, 'apr 2024', 'E'),
  146. (12006, 1016, 'may 2024', 'F'),
  147. (12007, 1017, 'jun 2024', 'A'),
  148. (12008, 1018, 'jul 2024', 'B'),
  149. (12009, 1019, 'aug 2024', 'C'),
  150. (12010, 1020, 'sep 2024', 'D'),
  151. (12011, 1021, 'oct 2024', 'E'),
  152. (12012, 1022, 'nov 2024', 'F'),
  153. (12013, 1023, 'dec 2024', 'A'),
  154. (12014, 1024, 'jan 2025', 'B'),
  155. (12015, 1025, 'feb 2025', 'C'),
  156. (12016, 1026, 'mar 2025', 'D'),
  157. (12017, 1027, 'apr 2025', 'E'),
  158. (12018, 1028, 'may 2025', 'F'),
  159. (12019, 1029, 'jun 2025', 'A'),
  160. (12020, 1030, 'jul 2025', 'B'),
  161. (12021, 1031, 'aug 2025', 'C'),
  162. (12022, 1032, 'sep 2025', 'D'),
  163. (12023, 1033, 'oct 2025', 'E'),
  164. (12024, 1034, 'nov 2025', 'F'),
  165. (12001, 1012, 'dec 2023', 'A'),
  166. (12002, 1013, 'jan 2024', 'B'),
  167. (12003, 1014, 'feb 2024', 'C'),
  168. (12004, 1015, 'mar 2024', 'D'),
  169. (12005, 1016, 'apr 2024', 'E'),
  170. (12006, 1017, 'may 2024', 'F'),
  171. (12007, 1018, 'jun 2024', 'A'),
  172. (12008, 1019, 'jul 2024', 'B'),
  173. (12009, 1020, 'aug 2024', 'C'),
  174. (12010, 1021, 'sep 2024', 'D'),
  175. (12011, 1022, 'oct 2024', 'E'),
  176. (12012, 1023, 'nov 2024', 'F'),
  177. (12013, 1024, 'dec 2024', 'A'),
  178. (12014, 1025, 'jan 2025', 'B'),
  179. (12015, 1026, 'feb 2025', 'C'),
  180. (12016, 1027, 'mar 2025', 'D'),
  181. (12017, 1028, 'apr 2025', 'E'),
  182. (12018, 1029, 'may 2025', 'F'),
  183. (12019, 1030, 'jun 2025', 'A'),
  184. (12020, 1031, 'jul 2025', 'B'),
  185. (12021, 1032, 'aug 2025', 'C'),
  186. (12022, 1033, 'sep 2025', 'D'),
  187. (12023, 1034, 'oct 2025', 'E'),
  188. (12002, 1011, 'nov 2023', 'A'),
  189. (12003, 1012, 'dec 2023', 'B'),
  190. (12004, 1013, 'jan 2024', 'C'),
  191. (12005, 1014, 'feb 2024', 'D'),
  192. (12006, 1015, 'mar 2024', 'E'),
  193. (12007, 1016, 'apr 2024', 'F'),
  194. (12008, 1017, 'may 2024', 'A'),
  195. (12009, 1018, 'jun 2024', 'B'),
  196. (12010, 1019, 'jul 2024', 'C'),
  197. (12011, 1020, 'aug 2024', 'D'),
  198. (12012, 1021, 'sep 2024', 'E'),
  199. (12013, 1022, 'oct 2024', 'F');
  200. INSERT INTO enrolls (rollno, Ccode, sess, grade) VALUES
  201. (12002, 1011, 'jan 2023', 'A'),  
  202. (12002, 1012, 'oct 2023', 'A'),  
  203. (12002, 1013, 'feb 2024', 'A');  
  204. INSERT INTO enrolls (rollno, Ccode, sess, grade) VALUES
  205. (12003, 1011, 'sep 2023', 'A'),  
  206. (12003, 1012, 'feb 2024', 'B'),  
  207. (12003, 1013, 'mar 2024', 'A');
  208. UPDATE enrolls
  209. SET grade = 'S'
  210. WHERE rollno IN (12003, 12002);
  211.  
  212.  
  213.  SELECT *from department As d where d.dno IN
  214.  (SELECT b.dno FROM branch AS b Group BY b.dno HAVING COUNT(b.dno) >3 );
  215.  
  216.   SELECT *from department As d where d.dno IN
  217.  (SELECT c.dno FROM course AS c Group BY c.dno HAVING COUNT(c.dno) >5 );
  218.  
  219.  
  220. SELECT * FROM course AS c WHERE c.Ccode IN
  221. (SELECT bc.Ccode FROM branch_course AS bc
  222. GROUP BY bc.Ccode HAVING COUNT(bc.Ccode) > 3);
  223.  
  224.  
  225. SELECT * FROM STUDENT S WHERE S.ROLLNO IN (SELECT E.ROLLNO FROM ENROLLS
  226. E WHERE E.GRADE ='S' GROUP BY E.ROLLNO HAVING count(E.GRADE) > 2);
  227.  
  228.  
  229. CREATE view stu_cour_comp AS SELECT S.ROLLNO, S.NAME,COUNT(E.CCODE) AS course_completed
  230. FROM STUDENT as S,ENROLLS as E
  231. WHERE S.ROLLNO = E.ROLLNO AND E.GRADE != 'E'
  232. GROUP BY E.ROLLNO;
  233.  
  234. select *from stu_cour_comp order by course_completed;
  235.  
  236.  
  237.  
  238.  
  239.  
  240.  
  241.  
  242.  
  243.  
  244.  
  245.  
  246.  
  247.  
  248.  
  249.  
  250.  
  251.  
  252. create database gourav_ass_2;
  253. use gourav_ass_2;
  254.  
  255. create table customer(Customerno varchar(5),cname varchar(25));
  256.  alter table customer ADD primary key(customerno);
  257.  alter table customer ADD constraint chk3 CHECK (customerno LIKE 'C%');
  258.  
  259.  create table cust_order(orderno varchar(5), odate date, customerno varchar(5),
  260.  ord_amt numeric(6) default 0);
  261.   alter table cust_order ADD primary key (orderno);
  262.   alter table cust_order ADD foreign key(customerno) references customer(customerno);
  263.   alter table cust_order ADD constraint chk2 CHECK (orderno LIKE 'O%');
  264.   -- ALTER TABLE cust_order DROP CONSTRAINT chk2;  if you want to delete constraint
  265.  
  266. create table item(
  267.     itemno varchar(5),
  268.     intem_name varchar(30),
  269.     unit_price numeric(5)
  270. );
  271. alter table item add primary key(itemno);
  272. alter table item ADD constraint chk1 CHECK(itemno LIKE 'I%');
  273.  
  274. create table order_item(
  275.     orderno varchar(5),
  276.     itemno varchar(5),
  277.     qty numeric(3)
  278. );
  279. alter table order_item ADD primary key (orderno,itemno);
  280. alter table order_item ADD foreign key (orderno) references cust_order (orderno);
  281. alter table order_item ADD foreign key (itemno) references item (itemno);
  282.  
  283.  
  284. INSERT INTO customer (Customerno, cname) VALUES
  285.     ('C0001', 'Gourav Upadhyay'),
  286.     ('C0002', 'Priya Patel'),
  287.     ('C0003', 'Rahul Sharma'),
  288.     ('C0004', 'Neha Singh'),
  289.     ('C0005', 'Anjali Gupta'),
  290.     ('C0006', 'Manish Verma'),
  291.     ('C0007', 'Deepika Reddy'),
  292.     ('C0008', 'Rohit Kumar'),
  293.     ('C0009', 'Pooja Choudhary'),
  294.     ('C0010', 'Alok Mishra');
  295.    
  296. INSERT INTO item (itemno,intem_name, unit_price) values
  297.     ('I0002', 'Mouse',150),
  298.     ('I0002', 'Mouse',150),
  299.     ('I0003', 'Keyboard', 200),
  300.     ('I0004', 'Hard Drive', 500),
  301.     ('I0005', 'RAM', 1200),
  302.     ('I0006', 'Webcam', 1500),
  303.     ('I0007', 'Ethernet Cable', 300);
  304.  
  305. INSERT INTO cust_order (orderno, odate, customerno, ord_amt) VALUES
  306.     ('O0001', STR_TO_DATE('01,01,2022', '%d,%m,%Y'), 'C0005', 0),
  307.     ('O0002', STR_TO_DATE('15,04,2022', '%d,%m,%Y'), 'C0003', 0),
  308.     ('O0003', STR_TO_DATE('22,07,2022', '%d,%m,%Y'), 'C0007', 0),
  309.     ('O0004', STR_TO_DATE('10,10,2022', '%d,%m,%Y'), 'C0002', 0),
  310.     ('O0005', STR_TO_DATE('05,03,2023', '%d,%m,%Y'), 'C0004', 0),
  311.     ('O0006', STR_TO_DATE('20,06,2023', '%d,%m,%Y'), 'C0001', 0),
  312.     ('O0007', STR_TO_DATE('12,09,2023', '%d,%m,%Y'), 'C0006', 0),
  313.     ('O0008', STR_TO_DATE('03,12,2022', '%d,%m,%Y'), 'C0007', 0),
  314.     ('O0009', STR_TO_DATE('18,05,2022', '%d,%m,%Y'), 'C0010', 0),
  315.     ('O0010', STR_TO_DATE('26,08,2022', '%d,%m,%Y'), 'C0001', 0),
  316.     ('O0011', STR_TO_DATE('14,11,2022', '%d,%m,%Y'), 'C0003', 0),
  317.     ('O0012', STR_TO_DATE('09,02,2023', '%d,%m,%Y'), 'C0006', 0),
  318.     ('O0013', STR_TO_DATE('24,04,2023', '%d,%m,%Y'), 'C0005', 0),
  319.     ('O0014', STR_TO_DATE('21,07,2023', '%d,%m,%Y'), 'C0004', 0),
  320.     ('O0015', STR_TO_DATE('04,10,2023', '%d,%m,%Y'), 'C0002', 0),
  321.     ('O0016', STR_TO_DATE('10,01,2022', '%d,%m,%Y'), 'C0006', 0),
  322.     ('O0017', STR_TO_DATE('13,06,2022', '%d,%m,%Y'), 'C0005', 0),
  323.     ('O0018', STR_TO_DATE('27,09,2022', '%d,%m,%Y'), 'C0003', 0),
  324.     ('O0019', STR_TO_DATE('08,12,2022', '%d,%m,%Y'), 'C0007', 0),
  325.     ('O0020', STR_TO_DATE('16,03,2023', '%d,%m,%Y'), 'C0004', 0),
  326.     ('O0021', STR_TO_DATE('18,04,2022', '%d,%m,%Y'), 'C0008', 0);
  327.  
  328. INSERT INTO order_item (orderno, itemno, qty) VALUES
  329.     ('O0001', 'I0007', 1),
  330.     ('O0001', 'I0002', 3),
  331.     ('O0002', 'I0003', 4),
  332.     ('O0002', 'I0004', 2),
  333.     ('O0003', 'I0005', 6),
  334.     ('O0003', 'I0002', 5),
  335.     ('O0004', 'I0003', 2),
  336.     ('O0004', 'I0004', 1),
  337.     ('O0005', 'I0007', 3),
  338.     ('O0005', 'I0002', 4),
  339.     ('O0006', 'I0003', 1),
  340.     ('O0006', 'I0004', 2),
  341.     ('O0007', 'I0005', 3),
  342.     ('O0007', 'I0007', 4),
  343.     ('O0008', 'I0002', 1),
  344.     ('O0008', 'I0003', 2),
  345.     ('O0009', 'I0004', 1),
  346.     ('O0009', 'I0005', 4),
  347.     ('O0010', 'I0006', 2),
  348.     ('O0010', 'I0005', 4),
  349.     ('O0011', 'I0002', 3),
  350.     ('O0011', 'I0006', 1),
  351.     ('O0012', 'I0007', 1),
  352.     ('O0012', 'I0003', 1),
  353.     ('O0013', 'I0004', 4),
  354.     ('O0013', 'I0005', 2),
  355.     ('O0014', 'I0005', 3),
  356.     ('O0014', 'I0004', 4),
  357.     ('O0015', 'I0003', 3),
  358.     ('O0015', 'I0004', 1),
  359.     ('O0016', 'I0006', 1),
  360.     ('O0016', 'I0005', 2),
  361.     ('O0017', 'I0003', 2),
  362.     ('O0017', 'I0002', 5),
  363.     ('O0018', 'I0006', 5),
  364.     ('O0018', 'I0004', 2),
  365.     ('O0019', 'I0006', 1),
  366.     ('O0019', 'I0002', 2),
  367.     ('O0020', 'I0003', 1),
  368.     ('O0021', 'I0005', 2);
  369.  
  370.  
  371. select *from customer AS c
  372. where c.customerno IN(select o.customerno
  373. from cust_order AS o GROUP BY o.customerno
  374. having count(o.orderno)>2);
  375.  
  376. select *from item AS k
  377. where k.unit_price < (select avg
  378. (unit_price) from item);
  379.  
  380. SELECT orderno, SUM(qty)
  381. FROM order_item
  382. GROUP BY orderno;
  383.  
  384. select *from item where itemno
  385. IN (select itemno from order_item
  386. group by itemno having
  387. count(itemno) >= (select (count(*)/4)
  388. from cust_order));
  389.  
  390. SELECT * FROM item
  391. WHERE itemno IN (
  392.     SELECT itemno
  393.     FROM order_item
  394.     GROUP BY itemno
  395.     HAVING COUNT(itemno) >= (SELECT (COUNT(*) / 4) FROM cust_order)
  396. );
  397.  
  398. update cust_order set ord_amt = (select sum(o.qty * i.unit_price)
  399. from order_item AS o, item AS i where
  400. cust_order.orderno = o.orderno AND o.itemno = i.itemno);
  401.  
  402. CREATE VIEW customer_details AS
  403. SELECT c.Customerno, c.cname, COUNT(orderno) AS order_count
  404. FROM customer AS c , cust_order AS o
  405. where c.customerno = o.customerno  
  406. GROUP BY c.Customerno, c.cname;
  407.  
  408.  
  409.  
  410.  
  411.  
  412.  
  413.  
  414.  
  415.  
  416.  
  417.  
  418. create database gourav_ass_3;
  419. use gourav_ass_3;
  420. create table depts(deptno varchar(5),dname varchar(30));
  421. alter table depts add primary key(deptno);
  422. alter table depts add constraint chk1 check(deptno like 'D%'); 
  423.  
  424. create table skill(skill_code varchar(5),description varchar(30),charge_outrage numeric(3));
  425. alter table skill add primary key(skill_code);
  426. alter table skill add constraint chk2 check(skill_code like 'S%');
  427.  
  428. 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));
  429. ALTER TABLE STAFF ADD PRIMARY KEY(STAFFNO);
  430. ALTER TABLE STAFF ADD FOREIGN KEY(DEPTNO) REFERENCES DEPTS (DEPTNO);
  431. ALTER TABLE STAFF ADD CONSTRAINT CHK3 CHECK(GENDER IN('M','F'));
  432.  
  433. CREATE TABLE STAFF_SKILL (STAFFNO NUMERIC(5), SKILL_CODE VARCHAR(5));
  434. ALTER TABLE STAFF_SKILL ADD PRIMARY KEY (STAFFNO, SKILL_CODE);
  435. ALTER TABLE STAFF_SKILL ADD FOREIGN KEY(STAFFNO) REFERENCES STAFF(STAFFNO);
  436. ALTER TABLE STAFF_SKILL ADD FOREIGN KEY(SKILL_CODE) REFERENCES SKILL(SKILL_CODE);
  437.  
  438. CREATE TABLE PROJECT(PROJECTNO VARCHAR(5), PNAME VARCHAR(30), START_DATE DATE, END_DATE DATE, PROJECT_MANAGER_STAFFNO numeric(5));
  439. ALTER TABLE PROJECT ADD PRIMARY KEY (PROJECTNO);
  440. ALTER TABLE PROJECT ADD CONSTRAINT CHK4 CHECK(PROJECTNO LIKE 'P%');
  441. ALTER TABLE PROJECT ADD FOREIGN KEY (PROJECT_MANAGER_STAFFNO) REFERENCES STAFF(STAFFNO);
  442.  
  443. CREATE TABLE WORKS (STAFFNO NUMERIC(5), PROJECTNO VARCHAR(5),DATE_WORKED_ON DATE, INTIME TIMESTAMP, OUTTIME TIMESTAMP);
  444. ALTER TABLE WORKS ADD PRIMARY KEY (STAFFNO,PROJECTNO);
  445. ALTER TABLE WORKS ADD FOREIGN KEY(STAFFNO) REFERENCES STAFF(STAFFNO);
  446. ALTER TABLE WORKS ADD FOREIGN KEY(PROJECTNO) REFERENCES PROJECT(PROJECTNO);
  447.  
  448. INSERT INTO depts VALUES('D0001','Accounts');
  449. INSERT INTO depts VALUES('D0002','Human Resources');
  450. INSERT INTO depts VALUES('D0003','Marketing');
  451. INSERT INTO depts VALUES('D0004','Information Technology');
  452. INSERT INTO depts VALUES('D0005','Operations');
  453. INSERT INTO depts VALUES('D0006','Sales');
  454. INSERT INTO depts VALUES('D0007','Research and Development');
  455. INSERT INTO depts VALUES('D0008','Customer Service');
  456.  
  457.  
  458. INSERT INTO STAFF VALUES (10001, 'Gourav Upadhyay', '1990-05-15', 'M', '2015-03-20', 'Manager', 50000, 'D0001');
  459. INSERT INTO STAFF VALUES (10002, 'Rahul Sharma', '1992-07-10', 'M', '2016-05-25', 'Assistant Manager', 45000, 'D0002');
  460. INSERT INTO STAFF VALUES (10003, 'Priya Patel', '1988-11-30', 'F', '2014-02-15', 'Senior Developer', 55000, 'D0003');
  461. INSERT INTO STAFF VALUES (10004, 'Amit Kumar', '1995-04-20', 'M', '2017-09-10', 'HR Specialist', 48000, 'D0004');
  462. INSERT INTO STAFF VALUES (10005, 'Neha Gupta', '1993-08-05', 'M', '2016-12-12', 'Sales Executive', 42000, 'D0005');
  463. INSERT INTO STAFF VALUES (10006, 'Anjali Singh', '1990-01-25', 'F', '2015-08-20', 'Financial Analyst', 49000, 'D0006');
  464. INSERT INTO STAFF VALUES (10007, 'Rajesh Sharma', '1987-03-18', 'M', '2013-04-30', 'Marketing Coordinator', 47000, 'D0007');
  465. INSERT INTO STAFF VALUES (10008, 'Sneha Kapoor', '1991-06-12', 'F', '2016-10-08', 'Operations Manager', 52000, 'D0008');
  466. INSERT INTO STAFF VALUES (10009, 'Ravi Singh', '1989-09-08', 'M', '2015-07-05', 'Customer Service', 43000, 'D0001');
  467. INSERT INTO STAFF VALUES (10010, 'Pooja Sharma', '1994-02-28', 'F', '2017-11-15', 'Finance Manager', 56000, 'D0002');
  468. INSERT INTO STAFF VALUES (10011, 'Manoj Patel', '1996-06-20', 'M', '2018-03-03', 'Research Analyst', 46000, 'D0001');
  469. INSERT INTO STAFF VALUES (10012, 'Deepak Gupta', '1992-10-15', 'F', '2016-06-18', 'Sales Manager', 53000, 'D0004');
  470. INSERT INTO STAFF VALUES (10013, 'Smita Sharma', '1986-12-05', 'M', '2012-09-22', 'IT Specialist', 48000, 'D0005');
  471. INSERT INTO STAFF VALUES (10014, 'Vikram Singh', '1990-04-30', 'F', '2015-02-10', 'HR Assistant', 44000, 'D0006');
  472. INSERT INTO STAFF VALUES (10015, 'Nisha Kapoor', '1988-08-15', 'M', '2013-11-05', 'Operations Coordinator', 51000, 'D0001');
  473. INSERT INTO STAFF VALUES (10016, 'Rahul Gupta', '1993-03-10', 'F', '2016-08-28', 'Marketing Manager', 54000, 'D0008');
  474. INSERT INTO STAFF VALUES (10017, 'Suresh Singh', '1987-07-20', 'M', '2014-04-15', 'Customer Service Supervisor', 47000, 'D0001');
  475. INSERT INTO STAFF VALUES (10018, 'Preeti Patel', '1991-11-12', 'F', '2017-10-08', 'Finance Analyst', 49000, 'D0002');
  476. INSERT INTO STAFF VALUES (10019, 'Ajay Kumar', '1995-01-25', 'M', '2018-06-20', 'Research Coordinator', 45000, 'D0003');
  477. INSERT INTO STAFF VALUES (10020, 'Kavita Sharma', '1990-05-08', 'F', '2015-09-05', 'Sales Associate', 42000, 'D0004');
  478. INSERT INTO STAFF VALUES (10021, 'Prakash Singh', '1986-09-30', 'M', '2012-12-20', 'IT Manager', 55000, 'D0001');
  479. INSERT INTO STAFF VALUES (10022, 'Anita Gupta', '1994-01-15', 'F', '2017-02-10', 'HR Coordinator', 46000, 'D0006');
  480. INSERT INTO STAFF VALUES (10023, 'Rajiv Kapoor', '1989-05-05', 'M', '2014-08-18', 'Operations Supervisor', 51000, 'D0007');
  481. INSERT INTO STAFF VALUES (10024, 'Sunita Sharma', '1992-07-10', 'F', '2016-10-25', 'Marketing Assistant', 48000, 'D0008');
  482. INSERT INTO STAFF VALUES (10025, 'Vijay Singh', '1988-11-15', 'M', '2013-03-03', 'Customer Service Manager', 53000, 'D0001');
  483.  
  484. INSERT INTO SKILL VALUES ('S0001', 'Programming', 100);
  485. INSERT INTO SKILL VALUES ('S0002', 'Database Management', 120);
  486. INSERT INTO SKILL VALUES ('S0003', 'Web Development', 110);
  487. INSERT INTO SKILL VALUES ('S0004', 'Network Administration', 130);
  488. INSERT INTO SKILL VALUES ('S0005', 'Cybersecurity', 260);
  489. INSERT INTO SKILL VALUES ('S0006', 'Data Analysis', 150);
  490. INSERT INTO SKILL VALUES ('S0007', 'Project Management', 110);
  491. INSERT INTO SKILL VALUES ('S0008', 'UI/UX Design', 120);
  492. INSERT INTO SKILL VALUES ('S0009', 'Mobile App Development', 180);
  493. INSERT INTO SKILL VALUES ('S0010', 'Cloud Computing', 100);
  494. INSERT INTO SKILL VALUES ('S0011', 'Machine Learning', 200);
  495. INSERT INTO SKILL VALUES ('S0012', 'Artificial Intelligence', 210);
  496. INSERT INTO SKILL VALUES ('S0013', 'Digital Marketing', 110);
  497. INSERT INTO SKILL VALUES ('S0014', 'Business Intelligence', 230);
  498. INSERT INTO SKILL VALUES ('S0015', 'Quality Assurance', 200);
  499. INSERT INTO SKILL VALUES ('S0016', 'Accounting', 120);
  500. INSERT INTO SKILL VALUES ('S0017', 'Finance', 260);
  501. INSERT INTO SKILL VALUES ('S0018', 'Human Resources', 100);
  502. INSERT INTO SKILL VALUES ('S0019', 'Sales', 110);
  503. INSERT INTO SKILL VALUES ('S0020', 'Marketing', 120);
  504.  
  505. INSERT INTO STAFF_SKILL VALUES
  506. (10001, 'S0001'), (10001, 'S0002'), (10001, 'S0003'), (10001, 'S0004'),
  507. (10001, 'S0005'), (10001, 'S0006'), (10004, 'S0007'), (10004, 'S0008'),
  508. (10005, 'S0009'), (10005, 'S0010'), (10006, 'S0011'), (10006, 'S0012'),
  509. (10007, 'S0013'), (10007, 'S0014'), (10008, 'S0015'), (10008, 'S0001'),
  510. (10009, 'S0002'), (10009, 'S0003'), (10010, 'S0004'), (10010, 'S0005'),
  511. (10011, 'S0004'), (10011, 'S0007'), (10012, 'S0004'), (10012, 'S0009'),
  512. (10013, 'S0004'), (10013, 'S0011'), (10014, 'S0004'), (10014, 'S0013'),
  513. (10015, 'S0014'), (10015, 'S0015'), (10016, 'S0001'), (10016, 'S0002'),
  514. (10017, 'S0003'), (10017, 'S0004'), (10018, 'S0005'), (10018, 'S0006'),
  515. (10019, 'S0007'), (10019, 'S0008'), (10020, 'S0009'), (10020, 'S0010');
  516.  
  517. INSERT INTO PROJECT (PROJECTNO, PNAME, START_DATE, END_DATE, PROJECT_MANAGER_STAFFNO) VALUES
  518. ('P0001', 'Project Alpha', '2023-01-15', '2023-07-15', 10001),
  519. ('P0002', 'Project Beta', '2023-03-20', '2023-09-20', 10002),
  520. ('P0003', 'Project Gamma', '2023-05-10', '2024-01-10', 10003),
  521. ('P0004', 'Project Delta', '2023-07-25', '2024-02-25', 10004),
  522. ('P0005', 'Project Epsilon', '2023-09-05', '2024-03-05', 10005),
  523. ('P0006', 'Project Zeta', '2023-11-15', '2024-05-15', 10006),
  524. ('P0007', 'Project Eta', '2024-01-20', '2024-07-20', 10007);
  525.  
  526. INSERT INTO WORKS (STAFFNO, PROJECTNO, DATE_WORKED_ON, INTIME, OUTTIME) VALUES
  527. (10001, 'P0001', '2023-01-15', '2023-01-15 09:00:00', '2023-01-15 17:00:00'),
  528. (10001, 'P0002', '2023-03-20', '2023-03-20 08:30:00', '2023-03-20 16:30:00'),
  529. (10002, 'P0001', '2023-01-16', '2023-01-16 09:15:00', '2023-01-16 17:15:00'),
  530. (10002, 'P0003', '2023-05-11', '2023-05-11 08:45:00', '2023-05-11 16:45:00'),
  531. (10003, 'P0004', '2023-07-26', '2023-07-26 09:30:00', '2023-07-26 17:30:00'),
  532. (10003, 'P0002', '2023-03-21', '2023-03-21 08:00:00', '2023-03-21 16:00:00'),
  533. (10004, 'P0005', '2023-09-06', '2023-09-06 09:45:00', '2023-09-06 17:45:00'),
  534. (10004, 'P0006', '2023-11-16', '2023-11-16 08:30:00', '2023-11-16 16:30:00'),
  535. (10005, 'P0003', '2023-05-12', '2023-05-12 10:00:00', '2023-05-12 18:00:00'),
  536. (10005, 'P0007', '2024-01-21', '2024-01-21 08:15:00', '2024-01-21 16:15:00'),
  537. (10006, 'P0001', '2023-01-17', '2023-01-17 10:15:00', '2023-01-17 18:15:00'),
  538. (10006, 'P0004', '2023-07-27', '2023-07-27 08:45:00', '2023-07-27 16:45:00'),
  539. (10007, 'P0002', '2023-03-22', '2023-03-22 10:30:00', '2023-03-22 18:30:00'),
  540. (10007, 'P0005', '2023-09-07', '2023-09-07 08:00:00', '2023-09-07 16:00:00'),
  541. (10008, 'P0006', '2023-11-17', '2023-11-17 10:45:00', '2023-11-17 18:45:00'),
  542. (10008, 'P0003', '2023-05-13', '2023-05-13 08:30:00', '2023-05-13 16:30:00'),
  543. (10009, 'P0001', '2023-01-18', '2023-01-18 11:00:00', '2023-01-18 19:00:00'),
  544. (10009, 'P0004', '2023-07-28', '2023-07-28 09:00:00', '2023-07-28 17:00:00'),
  545. (10010, 'P0002', '2023-03-23', '2023-03-23 11:15:00', '2023-03-23 19:15:00'),
  546. (10010, 'P0005', '2023-09-08', '2023-09-08 08:45:00', '2023-09-08 16:45:00'),
  547. (10011, 'P0006', '2023-11-18', '2023-11-18 11:30:00', '2023-11-18 19:30:00'),
  548. (10011, 'P0003', '2023-05-14', '2023-05-14 09:30:00', '2023-05-14 17:30:00'),
  549. (10012, 'P0001', '2023-01-19', '2023-01-19 11:45:00', '2023-01-19 19:45:00'),
  550. (10012, 'P0004', '2023-07-29', '2023-07-29 10:00:00', '2023-07-29 18:00:00');
  551.  
  552. SELECT deptno, COUNT(staffno) FROM staff GROUP BY deptno;
  553.  
  554. SELECT *FROM staff WHERE basic_pay IN (SELECT AVG(basic_pay) from staff);
  555.  
  556. SELECT *FROM staff WHERE staffno IN(SELECT staffno FROM staff_skill GROUP BY staffno HAVING COUNT(staffno) > 3);
  557.  
  558. select *from staff where staffno IN
  559. (select staffno from staff_skill where skill_code IN
  560. (select skill_code from skill where charge_outrage > 200));
  561.  
  562. CREATE VIEW staffcount AS SELECT d.deptno,d.dname,COUNT(s.staffno)
  563. AS STAFF_COUNT,SUM(S.BASIC_PAY) AS Depeartment_expense FROM
  564. DEPTS D,STAFF S WHERE D.DEPTNO = S.DEPTNO GROUP BY D.DEPTNO,D.DNAME;
  565.  
  566. SELECT D.deptno,D.dname ,COUNT(S.staffno) AS Number_Of_Staff
  567. FROM depts D, STAFF S WHERE D.deptno = S.deptno
  568. GROUP BY D.deptno, D.dname HAVING COUNT(S.staffno) > 5;
  569.  
  570.  
  571.  
  572. drop table ACCOUNT;
  573. TRUNCATE TABLE branch;
  574. DELETE FROM branch WHERE BNAME = 'Surat';
  575.  
  576. -- SET SQL_SAFE_UPDATES = 0;
  577.  
  578.  
  579.  
  580. -- ASSIGNMENT 4
  581. create database gourav_ass_4;
  582. use gourav_ass_4;
  583. CREATE TABLE customer(CID INT PRIMARY KEY,CNAME VARCHAR(30));
  584.  
  585. CREATE TABLE BRANCH( BCODE VARCHAR(5) PRIMARY KEY, BNAME VARCHAR(30) );
  586.  
  587. ALTER TABLE BRANCH ADD CONSTRAINT CPK CHECK (BCODE LIKE 'B');
  588. CREATE TABLE ACCOUNT(ANO VARCHAR(5) PRIMARY KEY, ATYPE CHAR(2), BALANCE numeric(7), CID int, BCODE VARCHAR(5));
  589. ALTER TABLE ACCOUNT ADD FOREIGN KEY(CID) REFERENCES CUSTOMER(CID);
  590. ALTER TABLE ACCOUNT ADD FOREIGN KEY(BCODE) REFERENCES BRANCH(BCODE);
  591. ALTER TABLE ACCOUNT ADD CONSTRAINT CPK2 CHECK (atype IN('S','C'));
  592. ALTER TABLE ACCOUNT ADD CONSTRAINT CPK3 CHECK (ano LIKE 'A%');
  593.  
  594. CREATE TABLE TRANSACTION( TID VARCHAR(5) PRIMARY KEY,
  595. ANO VARCHAR(5), TTYPE CHAR(2), TDATE DATE, TAMOUNT INTEGER );
  596. ALTER TABLE TRANSACTION ADD FOREIGN KEY(ANO) REFERENCES ACCOUNT(ANO);
  597. ALTER TABLE TRANSACTION ADD CONSTRAINT CPK4 CHECK(TTYPE IN('W','D'));
  598. ALTER TABLE TRANSACTION ADD CONSTRAINT CPK5 CHECK (tid LIKE 'T%');
  599.  
  600. INSERT INTO customer (CID, CNAME)
  601. VALUES
  602. (10001, 'Gourav Upadhyay'),
  603. (10002, 'Priya Patel'),
  604. (10003, 'Rahul Singh'),
  605. (10004, 'Ananya Sharma'),
  606. (10005, 'Aarav Gupta'),
  607. (10006, 'Neha Verma'),
  608. (10007, 'Vivek Mishra'),
  609. (10008, 'Kritika Sharma'),
  610. (10009, 'Sandeep Yadav'),
  611. (10010, 'Shreya Joshi');
  612.  
  613. INSERT INTO branch (BCODE, BNAME)
  614. VALUES
  615. ('B0001', 'Ahmedabad'),
  616. ('B0002', 'Surat'),
  617. ('B0003', 'Vadodara'),
  618. ('B0004', 'Rajkot');
  619.  
  620. INSERT INTO account (ANO, ATYPE, BALANCE, CID, BCODE)
  621. VALUES
  622. ('A0001', 'S', 5000, 10001, 'B0001'),
  623. ('A0002', 'S', 7000, 10002, 'B0004'),
  624. ('A0003', 'S', 3000, 10002, 'B0001'),
  625. ('A0004', 'C', 9000, 10004, 'B0001'),
  626. ('A0005', 'S', 6000, 10001, 'B0002'),
  627. ('A0006', 'C', 2000, 10002, 'B0004'),
  628. ('A0007', 'S', 4000, 10007, 'B0004'),
  629. ('A0008', 'C', 8000, 10002, 'B0001'),
  630. ('A0009', 'S', 10000, 10009, 'B0002'),
  631. ('A0010', 'C', 12000, 10010, 'B0004'),
  632. ('A0011', 'S', 15000, 10001, 'B0001'),
  633. ('A0012', 'C', 17000, 10002, 'B0001'),
  634. ('A0013', 'S', 20000, 10003, 'B0002'),
  635. ('A0014', 'S', 22000, 10001, 'B0001'),
  636. ('A0015', 'S', 25000, 10002, 'B0004');
  637.  
  638. INSERT INTO account (ANO, ATYPE, BALANCE, CID, BCODE)
  639. VALUES
  640. ('A0016', 'C', 1000, 10001, 'B0001');
  641.  
  642.  
  643. INSERT INTO transaction (TID, ANO, TTYPE, TDATE, TAMOUNT)
  644. VALUES
  645. ('T0001', 'A0001', 'W', '2024-03-30', 1000),
  646. ('T0002', 'A0001', 'D', '2024-03-30', 2000),
  647. ('T0003', 'A0001', 'W', '2024-03-30', 1500),
  648. ('T0004', 'A0001', 'D', '2024-03-30', 3000),
  649. ('T0005', 'A0001', 'W', '2024-03-30', 2500),
  650. ('T0006', 'A0001', 'D', '2024-03-30', 4000),
  651. ('T0007', 'A0007', 'W', '2024-03-10', 2000),
  652. ('T0008', 'A0008', 'D', '2024-03-30', 3500),
  653. ('T0009', 'A0009', 'W', '2024-03-30', 3000),
  654. ('T0010', 'A0010', 'D', '2024-03-30', 5000),
  655. ('T0011', 'A0011', 'W', '2024-03-20', 4000),
  656. ('T0012', 'A0012', 'D', '2024-04-30', 6000),
  657. ('T0013', 'A0003', 'W', '2024-04-30', 4500),
  658. ('T0014', 'A0014', 'D', '2024-03-30', 7000),
  659. ('T0015', 'A0015', 'W', '2024-03-30', 5500),
  660. ('T0016', 'A0001', 'D', '2024-06-30', 8000),
  661. ('T0017', 'A0002', 'W', '2024-03-10', 1000),
  662. ('T0018', 'A0003', 'D', '2024-07-20', 2000),
  663. ('T0019', 'A0002', 'W', '2024-03-30', 1500),
  664. ('T0020', 'A0002', 'D', '2024-01-30', 3000),
  665. ('T0021', 'A0002', 'W', '2024-03-30', 2500),
  666. ('T0022', 'A0002', 'D', '2024-01-30', 4000),
  667. ('T0023', 'A0008', 'W', '2024-03-30', 2000),
  668. ('T0024', 'A0009', 'D', '2024-03-30', 3500),
  669. ('T0025', 'A0003', 'W', '2024-03-30', 3000);
  670.  
  671. -- C
  672. SELECT *FROM CUSTOMER WHERE CID IN
  673. (SELECT A1.CID FROM ACCOUNT A1, ACCOUNT A2 WHERE A1.ATYPE = 'S' AND A2.ATYPE = 'C' AND A1.CID = A2.CID);
  674.  
  675. SELECT DISTINCT C.Cid, C.Cname  FROM Customer C
  676. INNER JOIN Account A ON C.Cid = A.Cid
  677. INNER JOIN Account A1 ON C.Cid = A1.Cid
  678. WHERE A.Atype = 'S' AND A1.Atype = 'C';
  679.  
  680. -- D
  681. SELECT B.BCODE, B.BNAME, COUNT(A.ANO) as No_of_accounts FROM BRANCH B, ACCOUNT A
  682. WHERE B.BCODE=A.BCODE GROUP BY B.BCODE;
  683.  
  684.  
  685. SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_accounts
  686. FROM Branch B
  687. JOIN Account A ON B. Bcode=A. Bcode
  688. GROUP BY B. Bcode, B. Bname;
  689.  
  690. -- E
  691.  
  692. SELECT B.BCODE, B. BNAME, COUNT(A.bcode) AS No_of_Accounts
  693. FROM BRANCH B, ACCOUNT A WHERE B.BCODE = A.BCODE
  694. GROUP BY B.BCODE, B.BNAME
  695. HAVING COUNT(A.bcode) <
  696. (SELECT COUNT(a1.ano)/4 FROM account AS a1);
  697.  
  698.  
  699. SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_Accounts
  700. FROM Branch B, Account A WHERE B.Bcode = A.Bcode
  701. GROUP BY B.Bcode, B. Bname
  702. HAVING COUNT(A. Ano)<
  703. (SELECT COUNT(Ano)/(SELECT COUNT(Bcode) FROM Branch) FROM Account);
  704.  
  705. SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS NoOfAccount
  706. FROM Branch B JOIN Account A ON B.Bcode=A.Bcode
  707. GROUP BY B. Bcode, B. Bname
  708. HAVING COUNT(A. Ano) <
  709. (SELECT COUNT(Ano)/ (SELECT COUNT(Bcode) FROM Branch) FROM Account);
  710.  
  711. -- F
  712. SELECT c.cid, c.cname
  713. FROM customer c, account a, transaction t
  714. WHERE c.cid = a.cid AND a.ano = t.ano
  715. group by t.tdate
  716. having COUNT(t.tdate) = 3;
  717.  
  718. SELECT c.cid, c.cname
  719. FROM customer c
  720. JOIN account a ON c.cid = a.cid
  721. JOIN transaction t ON a.ano = t.ano
  722. GROUP BY c.cid, c.cname
  723. HAVING COUNT(t.tdate) = 3;
  724.  
  725.  
  726. SELECT c.cid, c.cname FROM customer c, account a, transaction t
  727. WHERE c.cid = a.cid AND a.ano = t.ano
  728. GROUP BY c.cid, c.cname, t.tdate
  729. HAVING COUNT(t.tdate) = 3;
  730.  
  731.  
  732. -- G
  733. CREATE VIEW BRANCH_DATA AS SELECT B.BCODE, B.BNAME, COUNT(A.ANO) as No_of_accounts FROM BRANCH B, ACCOUNT A
  734. WHERE B.BCODE=A.BCODE GROUP BY B.BCODE;
  735.  
  736. create view BRANCH_DATA2 AS
  737. SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_accounts
  738. FROM Branch B
  739. JOIN Account A ON B. Bcode=A. Bcode
  740. GROUP BY B. Bcode, B. Bname;
  741.  
  742. select *from branch_data;
  743.  
  744.  
  745.  
  746.  
  747.  
  748.                 -- &^^^^^^^^^^^^^^case study 1st  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
  749. create database CS1;
  750. use CS1;
  751.  
  752. CREATE TABLE DEPARTMENT (
  753.     D_NAME VARCHAR(30) PRIMARY KEY,
  754.     D_LOCATION VARCHAR(50),
  755.     FACILITIES INT
  756. );
  757.  
  758. CREATE TABLE ALL_DOCTORS (
  759.     DOC_NO VARCHAR(30) PRIMARY KEY,
  760.     DEPARTMENT VARCHAR(30),
  761.     FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT(D_NAME)
  762. );
  763.  
  764. CREATE TABLE DOC_REG (
  765.     DOC_NO VARCHAR(30) PRIMARY KEY,
  766.     D_NAME VARCHAR(30),
  767.     QUALIFICATION VARCHAR(30),
  768.     SALARY DECIMAL(10, 2),
  769.     EN_TIME TIME,
  770.     EX_TIME TIME,
  771.     ADDRESS VARCHAR(50),
  772.     PH_NO VARCHAR(10),
  773.     DOJ DATE,
  774.     FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
  775. );
  776.  
  777. CREATE TABLE DOC_ON_CALL (
  778.     DOC_NO VARCHAR(30),
  779.     D_NAME VARCHAR(50),
  780.     QUALIFICATION VARCHAR(30),
  781.     FS_PR_CL DECIMAL(10, 2),
  782.     PYMT_DU DECIMAL(10, 2),
  783.     ADDRESS VARCHAR(100),
  784.     PH_NO VARCHAR(10),
  785.     FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
  786. );
  787.  
  788. CREATE TABLE PAT_ENTRY (
  789.     PAT_NO VARCHAR(30) PRIMARY KEY,
  790.     PAT_NAME VARCHAR(30),
  791.     CHKUP_DT DATE,
  792.     PT_AGE INT,
  793.     SEX CHAR(1),
  794.     RFRG_CSTNT VARCHAR(50),
  795.     DIAGNOSIS VARCHAR(30),
  796.     ADDRESS VARCHAR(100),
  797.     CITY VARCHAR(30),
  798.     PH_NO VARCHAR(10),
  799.     DEPARTMENT VARCHAR(30),
  800.     FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT(D_NAME)
  801. );
  802.  
  803. CREATE TABLE PAT_CHKUP (
  804.     PAT_NO VARCHAR(30),
  805.     DOC_NO VARCHAR(30),
  806.     DIAGNOSIS VARCHAR(30),
  807.     STATUS VARCHAR(30),
  808.     TREATMENT VARCHAR(30),
  809.     FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
  810.     FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
  811. );
  812.  
  813. CREATE TABLE PAT_ADMIT (
  814.     PAT_NO VARCHAR(30) PRIMARY KEY,
  815.     ADV_PYMT DECIMAL(10, 2),
  816.     MODE_PYMT VARCHAR(10),
  817.     ROOM_NO INT,
  818.     DEPTNAME VARCHAR(50),
  819.     ADMTD_ON DATE,
  820.     COND_ON VARCHAR(50),
  821.     INVSTGTN_DN VARCHAR(30),
  822.     TRMT_SDT VARCHAR(50),
  823.     ATTDNT_NM VARCHAR(50),
  824.     FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO)
  825. );
  826.  
  827. CREATE TABLE PAT_DIS (
  828.     PAT_NO VARCHAR(30),
  829.     TR_ADVS VARCHAR(100),
  830.     TR_GVN VARCHAR(100),
  831.     MEDICINES VARCHAR(100),
  832.     PYMT_GV DECIMAL(10, 2),
  833.     DIS_ON DATE,
  834.     FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO)
  835. );
  836.  
  837. CREATE TABLE PAT_REG (
  838.     PAT_NO VARCHAR(30) PRIMARY KEY,
  839.     DATE_VIS DATE,
  840.     CONDITIONN VARCHAR(30),
  841.     TREATMENT VARCHAR(30),
  842.     MEDICINES VARCHAR(30),
  843.     DOC_NO VARCHAR(30),
  844.     PAYMT DECIMAL(10, 2),
  845.     FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
  846.     FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
  847. );
  848.  
  849. CREATE TABLE PAT_OPR (
  850.     PAT_NO VARCHAR(30) PRIMARY KEY,
  851.     DATE_OPR DATE,
  852.     IN_COND VARCHAR(100),
  853.     AFOP_COND VARCHAR(100),
  854.     TY_OPERATION VARCHAR(100),
  855.     MEDICINES VARCHAR(100),
  856.     DOC_NO VARCHAR(30),
  857.     OPTH_NO VARCHAR(20),
  858.     OTHER_SUG VARCHAR(100),
  859.     FOREIGN KEY (PAT_NO) REFERENCES PAT_ENTRY(PAT_NO),
  860.     FOREIGN KEY (DOC_NO) REFERENCES ALL_DOCTORS(DOC_NO)
  861. );
  862.  
  863. CREATE TABLE ROOM_DETAILS (
  864.     ROOM_NO VARCHAR(30) PRIMARY KEY,
  865.     TYPE VARCHAR(50),
  866.     STATUS VARCHAR(20),
  867.     RM_DL_CRG DECIMAL(10, 2),
  868.     OTHER_CRG DECIMAL(10, 2)
  869. );
  870.  
  871.  
  872. ALTER TABLE DOC_REG
  873. ADD CONSTRAINT fk_doctor_number_prefix CHECK (DOC_NO LIKE 'DR%');
  874.  
  875. ALTER TABLE DOC_ON_CALL
  876. ADD CONSTRAINT fk_doctor_number_prefix_call CHECK (DOC_NO LIKE 'DR%');
  877.  
  878. ALTER TABLE PAT_ENTRY
  879. ADD CONSTRAINT pk_patient_number_prefix_entry CHECK (PAT_NO LIKE 'PT%'),
  880. ADD CONSTRAINT valid_sex_entry CHECK (SEX IN ('M', 'F'));
  881.  
  882. ALTER TABLE ROOM_DETAILS
  883. ADD CONSTRAINT valid_room_type CHECK (TYPE IN ('G', 'P')),
  884. ADD CONSTRAINT valid_room_status CHECK (STATUS IN ('Y', 'N'));
  885.  
  886.  
  887. -- Inserting data into DEPARTMENT table
  888. INSERT INTO DEPARTMENT (D_NAME, D_LOCATION, FACILITIES) VALUES
  889. ('Cardiology', 'Mumbai', 5),
  890. ('Pediatrics', 'Delhi', 3),
  891. ('Orthopedics', 'Bangalore', 4),
  892. ('Gynecology', 'Kolkata', 3),
  893. ('Neurology', 'Chennai', 5);
  894.  
  895. -- Inserting data into ALL_DOCTORS table
  896. INSERT INTO ALL_DOCTORS (DOC_NO, DEPARTMENT) VALUES
  897. ('DR001', 'Cardiology'),
  898. ('DR002', 'Pediatrics'),
  899. ('DR003', 'Orthopedics'),
  900. ('DR004', 'Gynecology'),
  901. ('DR005', 'Neurology');
  902.  
  903. -- Inserting data into DOC_REG table
  904. INSERT INTO DOC_REG (DOC_NO, D_NAME, QUALIFICATION, SALARY, EN_TIME, EX_TIME, ADDRESS, PH_NO, DOJ) VALUES
  905. ('DR001', 'Gourav Upadhyay', 'MD Cardiology', 150000, '08:00:00', '16:00:00', '123 ABC Street, Mumbai', '9876543210', '2023-01-15'),
  906. ('DR002', 'Priya Sharma', 'MD Pediatrics', 120000, '09:00:00', '17:00:00', '456 XYZ Street, Delhi', '9876543211', '2023-02-20'),
  907. ('DR003', 'Rahul Singh', 'MS Orthopedics', 130000, '08:30:00', '16:30:00', '789 PQR Street, Bangalore', '9876543212', '2023-03-25'),
  908. ('DR004', 'Neha Gupta', 'MD Gynecology', 140000, '08:00:00', '16:00:00', '456 LMN Street, Kolkata', '9876543213', '2023-04-30'),
  909. ('DR005', 'Amit Patel', 'DM Neurology', 160000, '09:30:00', '17:30:00', '789 RST Street, Chennai', '9876543214', '2023-05-05');
  910.  
  911. -- Inserting data into DOC_ON_CALL table
  912. INSERT INTO DOC_ON_CALL (DOC_NO, D_NAME, QUALIFICATION, FS_PR_CL, PYMT_DU, ADDRESS, PH_NO)
  913. VALUES
  914. ('DR001', 'Gourav Upadhyay', 'MD Cardiology', 2000, 1500, '456 Sunrise Avenue, Mumbai', '9876543210'),
  915. ('DR002', 'Priya Sharma', 'MD Pediatrics', 2500, 1800, '789 Maple Street, Delhi', '9876543211'),
  916. ('DR003', 'Rahul Singh', 'MS Orthopedics', 1800, 1200, '123 Oak Lane, Bangalore', '9876543212'),
  917. ('DR004', 'Neha Gupta', 'MD Gynecology', 2200, 1600, '345 Elm Street, Kolkata', '9876543213'),
  918. ('DR005', 'Amit Patel', 'DM Neurology', 2400, 1700, '678 Pine Avenue, Chennai', '9876543214');
  919.  
  920.  
  921. -- Inserting data into PAT_ENTRY table
  922. INSERT INTO PAT_ENTRY (PAT_NO, PAT_NAME, CHKUP_DT, PT_AGE, SEX, RFRG_CSTNT, DIAGNOSIS, ADDRESS, CITY, PH_NO, DEPARTMENT) VALUES
  923. ('PT001', 'Ramesh Kumar', '2023-06-01', 35, 'M', 'Fever', 'Common Cold', '123 ABC Street', 'Mumbai', '9876543200', 'Cardiology'),
  924. ('PT002', 'Sunita Devi', '2023-06-05', 28, 'F', 'Headache', 'Migraine', '456 XYZ Street', 'Delhi', '9876543201', 'Pediatrics'),
  925. ('PT003', 'Amit Sharma', '2023-06-10', 45, 'M', 'Stomach Pain', 'Gastritis', '789 PQR Street', 'Bangalore', '9876543202', 'Orthopedics'),
  926. ('PT004', 'Priya Singh', '2023-06-15', 30, 'F', 'Cough', 'Bronchitis', '456 LMN Street', 'Kolkata', '9876543203', 'Gynecology'),
  927. ('PT005', 'Gopal Verma', '2023-06-20', 55, 'M', 'Chest Pain', 'Angina', '789 RST Street', 'Chennai', '9876543204', 'Neurology');
  928.  
  929. -- Inserting data into PAT_CHKUP table
  930. INSERT INTO PAT_CHKUP (PAT_NO, DOC_NO, DIAGNOSIS, STATUS, TREATMENT) VALUES
  931. ('PT001', 'DR001', 'Common Cold', 'Admitted', 'Antibiotics'),
  932. ('PT002', 'DR002', 'Migraine', 'Regular', 'Painkillers'),
  933. ('PT003', 'DR003', 'Gastritis', 'Referred for Operation', 'Surgery'),
  934. ('PT004', 'DR004', 'Bronchitis', 'Admitted', 'Bronchodilators'),
  935. ('PT005', 'DR005', 'Angina', 'Regular', 'Cardiac Medications');
  936.  
  937. -- Inserting data into PAT_ADMIT table
  938. INSERT INTO PAT_ADMIT (PAT_NO, ADV_PYMT, MODE_PYMT, ROOM_NO, DEPTNAME, ADMTD_ON, COND_ON, INVSTGTN_DN, TRMT_SDT, ATTDNT_NM) VALUES
  939. ('PT001', 5000, 'Cash', 101, 'Cardiology', '2023-06-01', 'Stable', 'ECG, Blood Test', '2023-06-02', 'Rita Sharma'),
  940. ('PT004', 7000, 'Card', 201, 'Gynecology', '2023-06-15', 'Critical', 'X-Ray, Ultrasound', '2023-06-16', 'Amit Singh');
  941.  
  942. -- Inserting data into PAT_DIS table
  943. INSERT INTO PAT_DIS (PAT_NO, TR_ADVS, TR_GVN, MEDICINES, PYMT_GV, DIS_ON) VALUES
  944. ('PT001', 'Complete bed rest', 'Antibiotics', 'Paracetamol, Vitamin C', 5000, '2023-06-05'),
  945. ('PT004', 'Avoid cold food', 'Bronchodilators', 'Cough Syrup, Inhaler', 7000, '2023-06-20');
  946.  
  947. -- Inserting data into PAT_REG table
  948. INSERT INTO PAT_REG (PAT_NO, DATE_VIS, CONDITIONN, TREATMENT, MEDICINES, DOC_NO, PAYMT) VALUES
  949. ('PT002', '2023-06-05', 'Migraine', 'Painkillers', 'Ibuprofen', 'DR002', 300),
  950. ('PT003', '2023-06-10', 'Gastritis', 'Surgery', 'Omeprazole', 'DR003', 500),
  951. ('PT005', '2023-06-20', 'Angina', 'Cardiac Medications', 'Aspirin', 'DR005', 400);
  952.  
  953. -- Inserting data into PAT_OPR table
  954. INSERT INTO PAT_OPR (PAT_NO, DATE_OPR, IN_COND, AFOP_COND, TY_OPERATION, MEDICINES, DOC_NO, OPTH_NO, OTHER_SUG) VALUES
  955. ('PT003', '2023-06-11', 'Pre-Operative', 'Post-Operative', 'Appendectomy', 'Antibiotics', 'DR003', 'OT01', 'Avoid oily food');
  956.  
  957. -- Inserting data into ROOM_DETAILS table
  958. INSERT INTO ROOM_DETAILS (ROOM_NO, TYPE, STATUS, RM_DL_CRG, OTHER_CRG) VALUES
  959. (101, 'P', 'Y', 2000, 500),
  960. (102, 'G', 'N', 1000, 300),
  961. (201, 'P', 'Y', 2500, 600),
  962. (202, 'G', 'Y', 1500, 400),
  963. (203, 'P', 'N', 2200, 550);
  964.  
  965.  
  966.  
  967.  
  968.  
  969.  
  970.  
  971.  
  972.  
  973.  
  974.  
  975.  
  976.  
  977.  
  978. -- ********************************************************************************************************
  979.  
  980.  
  981. use cs2;
  982. show tables;
  983.  
  984. CREATE TABLE trainlist (
  985.     train_number INT NOT NULL PRIMARY KEY,
  986.     train_name VARCHAR(255) NOT NULL,
  987.     source VARCHAR(255) NOT NULL,
  988.     destination VARCHAR(255) NOT NULL,
  989.     ac_fair DECIMAL(10,2) NOT NULL,
  990.     general_fair DECIMAL(10,2) NOT NULL,
  991.     weekdays VARCHAR(255)
  992. );
  993.  
  994. CREATE TABLE train_status (
  995.     train_number INT NOT NULL,
  996.     train_date DATE NOT NULL,
  997.     total_ac_seats INT NOT NULL,
  998.     total_general_seats INT NOT NULL,
  999.     ac_seats_booked INT NOT NULL DEFAULT 0,
  1000.     general_seats_booked INT NOT NULL DEFAULT 0,
  1001.     PRIMARY KEY (train_number),
  1002.     FOREIGN KEY (train_number) REFERENCES trainlist(train_number)
  1003. );
  1004.  
  1005. CREATE TABLE passenger (
  1006.     ticket_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  1007.     train_number INT NOT NULL,
  1008.     booking_date DATE NOT NULL,
  1009.     name VARCHAR(255) NOT NULL,
  1010.     age INT,
  1011.     sex VARCHAR(10),
  1012.     address TEXT,
  1013.     status VARCHAR(20) NOT NULL,
  1014.     ticket_category VARCHAR(10) NOT NULL,
  1015.     FOREIGN KEY (train_number) REFERENCES trainlist(train_number)
  1016. );
  1017.  
  1018.  
  1019.  
  1020. INSERT INTO trainlist (train_number, train_name, source, destination, ac_fair, general_fair, weekdays)
  1021. VALUES
  1022.     (1001, 'Gourav U Exp', 'Indore', 'Delhi', 1500.00, 1000.00, 'Monday, Wednesday, Friday'),
  1023.     (1002, 'Gourav U Ps', 'Delhi', 'Prayagraj', 2000.00, 1200.00, 'Tuesday, Thursday, Saturday'),
  1024.     (1003, 'Rajdhani Express', 'Kolkata', 'Chennai', 1800.00, 1100.00, 'Daily'),
  1025.     (1004, 'Kalyan Exp', 'Chennai', 'Bangalore', 1700.00, 1050.00, 'Monday, Friday'),
  1026.     (1005, 'Maharaja exp', 'Jaipur', 'Jodhpur', 50000.00, 20000.00, 'Wednesday, Saturday');
  1027.  
  1028. INSERT INTO train_status (train_number, train_date, total_ac_seats,
  1029. total_general_seats, ac_seats_booked, general_seats_booked)
  1030. VALUES
  1031.     (1001, '2024-05-05', 50, 100, 10, 20),
  1032.     (1001, '2024-05-06', 50, 100, 15, 30),
  1033.     (1001, '2024-05-07', 50, 100, 20, 40),
  1034.     (1002, '2024-05-05', 60, 120, 5, 10),
  1035.     (1002, '2024-05-06', 60, 120, 10, 20),
  1036.     (1003, '2024-05-05', 40, 80, 8, 15),
  1037.     (1003, '2024-05-06', 40, 80, 12, 25),
  1038.     (1004, '2024-05-05', 45, 90, 8, 13),
  1039.     (1004, '2024-05-06', 45, 90, 8, 16),
  1040.     (1005, '2024-05-05', 100, 40, 90, 23),
  1041.     (1005, '2024-05-06', 101, 40, 95, 35);
  1042.  
  1043.  
  1044. INSERT INTO passenger (ticket_id, train_number, booking_date, name, age, sex, address, status, ticket_category)
  1045. VALUES
  1046.     (1, 1001, '2024-05-05', 'Gourav Upadhyay', 21, 'Male', '123 Bandra, Mumbai', 'Confirmed', 'AC'),
  1047.     (2, 1001, '2024-05-05', 'Priya Singh', 25, 'Female', '456 Kirti Nagar, Delhi', 'Confirmed', 'General'),
  1048.     (3, 1002, '2024-05-05', 'Ajay Kumar', 40, 'Male', '789 New Friends Colony, Delhi', 'Confirmed', 'AC'),
  1049.     (4, 1002, '2024-05-05', 'Neha Gupta', 35, 'Female', '987 Ballygunge, Kolkata', 'Confirmed', 'General'),
  1050.     (6, 1003, '2024-05-05', 'Riya Jain', 22, 'Female', '321 Anna Nagar, Chennai', 'Confirmed', 'General'),
  1051.     (7, 1004, '2024-05-05', 'Vivek Reddy', 45, 'Male', '234 Madhavaram, Chennai', 'Confirmed', 'AC'),
  1052.     (8, 1004, '2024-05-05', 'Shreya Mishra', 20, 'Female', '567 Walnut Street, Bangalore', 'Confirmed', 'General'),
  1053.     (9, 1005, '2024-05-05', 'Amit Kumar', 33, 'Male', '345 Jayanagar, Bangalore', 'Confirmed', 'AC'),
  1054.     (10, 1005, '2024-05-05', 'Ramesh sharma', 27, 'Male', '876 Banjara Hills, Hyderabad', 'Confirmed', 'General'),
  1055.     (17, 1004, '2024-05-05', 'Suresh Singh', 21, 'Male', 'Indore', 'Confirmed', 'AC'),
  1056.     (18, 1004, '2024-05-05', 'Suresh Singh', 21, 'Male', 'Indore', 'Confirmed', 'General');
  1057.  
  1058. CALL BookTicket(
  1059.     1002,'2024-05-05','AC',
  1060.     'Kamlesh Mishra,22','Male',                    
  1061.     '56, bablu vilas',    
  1062.     @ticket_id,                
  1063.     @booking_status            
  1064. );
  1065.  
  1066. SELECT @ticket_id AS TicketID, @booking_status AS BookingStatus;
  1067. select *from passenger;
  1068. select *from train_status;
  1069.  
  1070. -- Booking procedure
  1071. DELIMITER //
  1072.  
  1073. CREATE PROCEDURE BookTicket(
  1074.     IN P_Train_No VARCHAR(10),
  1075.     IN P_Train_Date DATE,
  1076.     IN P_Category VARCHAR(20),
  1077.     IN P_Name VARCHAR(30),
  1078.     IN P_Age INT,
  1079.     IN P_Sex CHAR(1),
  1080.     IN P_Address VARCHAR(30)
  1081. )
  1082. BEGIN
  1083.     DECLARE Total_seats INT;
  1084.     DECLARE Booked_seats INT;
  1085.  
  1086.     IF P_Category = 'AC' THEN
  1087.         SELECT total_ac_seats, ac_seats_booked INTO Total_seats, Booked_seats
  1088.         FROM train_status
  1089.         WHERE train_number = P_Train_No AND train_date = P_Train_Date;
  1090.     ELSE
  1091.         SELECT total_general_seats, general_seats_booked INTO Total_seats, Booked_seats
  1092.         FROM train_status
  1093.         WHERE train_number = P_Train_No AND train_date = P_Train_Date;
  1094.     END IF;
  1095.  
  1096.     IF Total_seats > Booked_seats THEN
  1097.         INSERT INTO passenger (train_number, booking_date, name, age, sex, address, status, ticket_category)
  1098.         VALUES (P_Train_No, P_Train_Date, P_Name, P_Age, P_Sex, P_Address, 'Confirmed', P_Category);
  1099.  
  1100.         SELECT 'Ticket Confirmed' AS message;
  1101.  
  1102.         IF P_Category = 'AC' THEN
  1103.             UPDATE train_status SET ac_seats_booked = ac_seats_booked + 1
  1104.             WHERE train_number = P_Train_No AND train_date = P_Train_Date;
  1105.         ELSE
  1106.             UPDATE train_status SET general_seats_booked = general_seats_booked + 1
  1107.             WHERE train_number = P_Train_No AND train_date = P_Train_Date;
  1108.         END IF;
  1109.     ELSE
  1110.         INSERT INTO passenger (train_number, booking_date, name, age, sex, address, status, ticket_category)
  1111.         VALUES (P_Train_No, P_Train_Date, P_Name, P_Age, P_Sex, P_Address, 'Waiting', P_Category);
  1112.  
  1113.         SELECT 'Sorry, Ticket not confirmed' AS message;
  1114.     END IF;
  1115. END //
  1116.  
  1117. DELIMITER ;
  1118.  
  1119.  
  1120. -- CACELLING PROCEDURE
  1121. DELIMITER //
  1122. CREATE PROCEDURE CancelTicket4(IN P_Ticket_ID INT)
  1123. BEGIN
  1124.     DECLARE C_Train_No INT;
  1125.     DECLARE C_Train_Date DATE;
  1126.     DECLARE C_Category VARCHAR(20);
  1127.    
  1128.     SELECT train_number, booking_date, ticket_category INTO C_Train_No, C_Train_Date, C_Category
  1129.     FROM passenger WHERE ticket_id = P_Ticket_ID;
  1130.    
  1131.     DELETE FROM passenger WHERE ticket_id = P_Ticket_ID;
  1132.    
  1133.     IF EXISTS (
  1134.         SELECT 1 FROM passenger
  1135.         WHERE train_number = C_Train_No AND booking_date = C_Train_Date AND ticket_category = C_Category AND status = 'Waiting'
  1136.     ) THEN
  1137.         UPDATE passenger SET status = 'Confirmed'
  1138.         WHERE train_number = C_Train_No AND booking_date = C_Train_Date AND ticket_category = C_Category AND status = 'Waiting' LIMIT 1;
  1139.     ELSE
  1140.         IF C_Category = 'AC' THEN
  1141.             UPDATE train_status
  1142.             SET ac_seats_booked = ac_seats_booked - 1
  1143.             WHERE train_number = C_Train_No AND train_date = C_Train_Date;
  1144.         ELSE
  1145.             UPDATE train_status
  1146.             SET general_seats_booked = general_seats_booked - 1
  1147.             WHERE train_number = C_Train_No AND train_date = C_Train_Date;
  1148.         END IF;
  1149.     END IF;
  1150.    
  1151.     SELECT 'Ticket Deleted Successfully' AS delete_status;
  1152. END //
  1153.  
  1154. DELIMITER ;
  1155.  
  1156. use cs2;
  1157. SHOW CREATE PROCEDURE BookTicket;
  1158.  
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!