API tools faq
paste
Advertisement
tampurus

GSITS assignment 3

tampurus
Mar 10th, 2024 (edited)
696
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 10.27 KB | None | 0 0
raw download clone embed print report
  1. create database gourav_ass_3;
  2. use gourav_ass_3;
  3. create table depts(deptno varchar(5),dname varchar(30));
  4. alter table depts add primary key(deptno);
  5. alter table depts add constraint chk1 check(deptno like 'D%'); 
  6.  
  7. create table skill(skill_code varchar(5),description varchar(30),charge_outrage numeric(3));
  8. alter table skill add primary key(skill_code);
  9. alter table skill add constraint chk2 check(skill_code like 'S%');
  10.  
  11. 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));
  12. ALTER TABLE STAFF ADD PRIMARY KEY(STAFFNO);
  13. ALTER TABLE STAFF ADD FOREIGN KEY(DEPTNO) REFERENCES DEPTS (DEPTNO);
  14. ALTER TABLE STAFF ADD CONSTRAINT CHK3 CHECK(GENDER IN('M','F'));
  15.  
  16. CREATE TABLE STAFF_SKILL (STAFFNO NUMERIC(5), SKILL_CODE VARCHAR(5));
  17. ALTER TABLE STAFF_SKILL ADD PRIMARY KEY (STAFFNO, SKILL_CODE);
  18. ALTER TABLE STAFF_SKILL ADD FOREIGN KEY(STAFFNO) REFERENCES STAFF(STAFFNO);
  19. ALTER TABLE STAFF_SKILL ADD FOREIGN KEY(SKILL_CODE) REFERENCES SKILL(SKILL_CODE);
  20.  
  21. CREATE TABLE PROJECT(PROJECTNO VARCHAR(5), PNAME VARCHAR(30), START_DATE DATE, END_DATE DATE, PROJECT_MANAGER_STAFFNO numeric(5));
  22. ALTER TABLE PROJECT ADD PRIMARY KEY (PROJECTNO);
  23. ALTER TABLE PROJECT ADD CONSTRAINT CHK4 CHECK(PROJECTNO LIKE 'P%');
  24. ALTER TABLE PROJECT ADD FOREIGN KEY (PROJECT_MANAGER_STAFFNO) REFERENCES STAFF(STAFFNO);
  25.  
  26. CREATE TABLE WORKS (STAFFNO NUMERIC(5), PROJECTNO VARCHAR(5),DATE_WORKED_ON DATE, INTIME TIMESTAMP, OUTTIME TIMESTAMP);
  27. ALTER TABLE WORKS ADD PRIMARY KEY (STAFFNO,PROJECTNO);
  28. ALTER TABLE WORKS ADD FOREIGN KEY(STAFFNO) REFERENCES STAFF(STAFFNO);
  29. ALTER TABLE WORKS ADD FOREIGN KEY(PROJECTNO) REFERENCES PROJECT(PROJECTNO);
  30.  
  31. INSERT INTO depts VALUES('D0001','Accounts');
  32. INSERT INTO depts VALUES('D0002','Human Resources');
  33. INSERT INTO depts VALUES('D0003','Marketing');
  34. INSERT INTO depts VALUES('D0004','Information Technology');
  35. INSERT INTO depts VALUES('D0005','Operations');
  36. INSERT INTO depts VALUES('D0006','Sales');
  37. INSERT INTO depts VALUES('D0007','Research and Development');
  38. INSERT INTO depts VALUES('D0008','Customer Service');
  39.  
  40.  
  41. INSERT INTO STAFF VALUES (10001, 'Gourav Upadhyay', '1990-05-15', 'M', '2015-03-20', 'Manager', 50000, 'D0001');
  42. INSERT INTO STAFF VALUES (10002, 'Rahul Sharma', '1992-07-10', 'M', '2016-05-25', 'Assistant Manager', 45000, 'D0002');
  43. INSERT INTO STAFF VALUES (10003, 'Priya Patel', '1988-11-30', 'F', '2014-02-15', 'Senior Developer', 55000, 'D0003');
  44. INSERT INTO STAFF VALUES (10004, 'Amit Kumar', '1995-04-20', 'M', '2017-09-10', 'HR Specialist', 48000, 'D0004');
  45. INSERT INTO STAFF VALUES (10005, 'Neha Gupta', '1993-08-05', 'M', '2016-12-12', 'Sales Executive', 42000, 'D0005');
  46. INSERT INTO STAFF VALUES (10006, 'Anjali Singh', '1990-01-25', 'F', '2015-08-20', 'Financial Analyst', 49000, 'D0006');
  47. INSERT INTO STAFF VALUES (10007, 'Rajesh Sharma', '1987-03-18', 'M', '2013-04-30', 'Marketing Coordinator', 47000, 'D0007');
  48. INSERT INTO STAFF VALUES (10008, 'Sneha Kapoor', '1991-06-12', 'F', '2016-10-08', 'Operations Manager', 52000, 'D0008');
  49. INSERT INTO STAFF VALUES (10009, 'Ravi Singh', '1989-09-08', 'M', '2015-07-05', 'Customer Service', 43000, 'D0001');
  50. INSERT INTO STAFF VALUES (10010, 'Pooja Sharma', '1994-02-28', 'F', '2017-11-15', 'Finance Manager', 56000, 'D0002');
  51. INSERT INTO STAFF VALUES (10011, 'Manoj Patel', '1996-06-20', 'M', '2018-03-03', 'Research Analyst', 46000, 'D0001');
  52. INSERT INTO STAFF VALUES (10012, 'Deepak Gupta', '1992-10-15', 'F', '2016-06-18', 'Sales Manager', 53000, 'D0004');
  53. INSERT INTO STAFF VALUES (10013, 'Smita Sharma', '1986-12-05', 'M', '2012-09-22', 'IT Specialist', 48000, 'D0005');
  54. INSERT INTO STAFF VALUES (10014, 'Vikram Singh', '1990-04-30', 'F', '2015-02-10', 'HR Assistant', 44000, 'D0006');
  55. INSERT INTO STAFF VALUES (10015, 'Nisha Kapoor', '1988-08-15', 'M', '2013-11-05', 'Operations Coordinator', 51000, 'D0001');
  56. INSERT INTO STAFF VALUES (10016, 'Rahul Gupta', '1993-03-10', 'F', '2016-08-28', 'Marketing Manager', 54000, 'D0008');
  57. INSERT INTO STAFF VALUES (10017, 'Suresh Singh', '1987-07-20', 'M', '2014-04-15', 'Customer Service Supervisor', 47000, 'D0001');
  58. INSERT INTO STAFF VALUES (10018, 'Preeti Patel', '1991-11-12', 'F', '2017-10-08', 'Finance Analyst', 49000, 'D0002');
  59. INSERT INTO STAFF VALUES (10019, 'Ajay Kumar', '1995-01-25', 'M', '2018-06-20', 'Research Coordinator', 45000, 'D0003');
  60. INSERT INTO STAFF VALUES (10020, 'Kavita Sharma', '1990-05-08', 'F', '2015-09-05', 'Sales Associate', 42000, 'D0004');
  61. INSERT INTO STAFF VALUES (10021, 'Prakash Singh', '1986-09-30', 'M', '2012-12-20', 'IT Manager', 55000, 'D0001');
  62. INSERT INTO STAFF VALUES (10022, 'Anita Gupta', '1994-01-15', 'F', '2017-02-10', 'HR Coordinator', 46000, 'D0006');
  63. INSERT INTO STAFF VALUES (10023, 'Rajiv Kapoor', '1989-05-05', 'M', '2014-08-18', 'Operations Supervisor', 51000, 'D0007');
  64. INSERT INTO STAFF VALUES (10024, 'Sunita Sharma', '1992-07-10', 'F', '2016-10-25', 'Marketing Assistant', 48000, 'D0008');
  65. INSERT INTO STAFF VALUES (10025, 'Vijay Singh', '1988-11-15', 'M', '2013-03-03', 'Customer Service Manager', 53000, 'D0001');
  66.  
  67. INSERT INTO SKILL VALUES ('S0001', 'Programming', 100);
  68. INSERT INTO SKILL VALUES ('S0002', 'Database Management', 120);
  69. INSERT INTO SKILL VALUES ('S0003', 'Web Development', 110);
  70. INSERT INTO SKILL VALUES ('S0004', 'Network Administration', 130);
  71. INSERT INTO SKILL VALUES ('S0005', 'Cybersecurity', 260);
  72. INSERT INTO SKILL VALUES ('S0006', 'Data Analysis', 150);
  73. INSERT INTO SKILL VALUES ('S0007', 'Project Management', 110);
  74. INSERT INTO SKILL VALUES ('S0008', 'UI/UX Design', 120);
  75. INSERT INTO SKILL VALUES ('S0009', 'Mobile App Development', 180);
  76. INSERT INTO SKILL VALUES ('S0010', 'Cloud Computing', 100);
  77. INSERT INTO SKILL VALUES ('S0011', 'Machine Learning', 200);
  78. INSERT INTO SKILL VALUES ('S0012', 'Artificial Intelligence', 210);
  79. INSERT INTO SKILL VALUES ('S0013', 'Digital Marketing', 110);
  80. INSERT INTO SKILL VALUES ('S0014', 'Business Intelligence', 230);
  81. INSERT INTO SKILL VALUES ('S0015', 'Quality Assurance', 200);
  82. INSERT INTO SKILL VALUES ('S0016', 'Accounting', 120);
  83. INSERT INTO SKILL VALUES ('S0017', 'Finance', 260);
  84. INSERT INTO SKILL VALUES ('S0018', 'Human Resources', 100);
  85. INSERT INTO SKILL VALUES ('S0019', 'Sales', 110);
  86. INSERT INTO SKILL VALUES ('S0020', 'Marketing', 120);
  87.  
  88. INSERT INTO STAFF_SKILL VALUES
  89. (10001, 'S0001'), (10001, 'S0002'), (10001, 'S0003'), (10001, 'S0004'),
  90. (10001, 'S0005'), (10001, 'S0006'), (10004, 'S0007'), (10004, 'S0008'),
  91. (10005, 'S0009'), (10005, 'S0010'), (10006, 'S0011'), (10006, 'S0012'),
  92. (10007, 'S0013'), (10007, 'S0014'), (10008, 'S0015'), (10008, 'S0001'),
  93. (10009, 'S0002'), (10009, 'S0003'), (10010, 'S0004'), (10010, 'S0005'),
  94. (10011, 'S0004'), (10011, 'S0007'), (10012, 'S0004'), (10012, 'S0009'),
  95. (10013, 'S0004'), (10013, 'S0011'), (10014, 'S0004'), (10014, 'S0013'),
  96. (10015, 'S0014'), (10015, 'S0015'), (10016, 'S0001'), (10016, 'S0002'),
  97. (10017, 'S0003'), (10017, 'S0004'), (10018, 'S0005'), (10018, 'S0006'),
  98. (10019, 'S0007'), (10019, 'S0008'), (10020, 'S0009'), (10020, 'S0010');
  99.  
  100. INSERT INTO PROJECT (PROJECTNO, PNAME, START_DATE, END_DATE, PROJECT_MANAGER_STAFFNO) VALUES
  101. ('P0001', 'Project Alpha', '2023-01-15', '2023-07-15', 10001),
  102. ('P0002', 'Project Beta', '2023-03-20', '2023-09-20', 10002),
  103. ('P0003', 'Project Gamma', '2023-05-10', '2024-01-10', 10003),
  104. ('P0004', 'Project Delta', '2023-07-25', '2024-02-25', 10004),
  105. ('P0005', 'Project Epsilon', '2023-09-05', '2024-03-05', 10005),
  106. ('P0006', 'Project Zeta', '2023-11-15', '2024-05-15', 10006),
  107. ('P0007', 'Project Eta', '2024-01-20', '2024-07-20', 10007);
  108.  
  109. INSERT INTO WORKS (STAFFNO, PROJECTNO, DATE_WORKED_ON, INTIME, OUTTIME) VALUES
  110. (10001, 'P0001', '2023-01-15', '2023-01-15 09:00:00', '2023-01-15 17:00:00'),
  111. (10001, 'P0002', '2023-03-20', '2023-03-20 08:30:00', '2023-03-20 16:30:00'),
  112. (10002, 'P0001', '2023-01-16', '2023-01-16 09:15:00', '2023-01-16 17:15:00'),
  113. (10002, 'P0003', '2023-05-11', '2023-05-11 08:45:00', '2023-05-11 16:45:00'),
  114. (10003, 'P0004', '2023-07-26', '2023-07-26 09:30:00', '2023-07-26 17:30:00'),
  115. (10003, 'P0002', '2023-03-21', '2023-03-21 08:00:00', '2023-03-21 16:00:00'),
  116. (10004, 'P0005', '2023-09-06', '2023-09-06 09:45:00', '2023-09-06 17:45:00'),
  117. (10004, 'P0006', '2023-11-16', '2023-11-16 08:30:00', '2023-11-16 16:30:00'),
  118. (10005, 'P0003', '2023-05-12', '2023-05-12 10:00:00', '2023-05-12 18:00:00'),
  119. (10005, 'P0007', '2024-01-21', '2024-01-21 08:15:00', '2024-01-21 16:15:00'),
  120. (10006, 'P0001', '2023-01-17', '2023-01-17 10:15:00', '2023-01-17 18:15:00'),
  121. (10006, 'P0004', '2023-07-27', '2023-07-27 08:45:00', '2023-07-27 16:45:00'),
  122. (10007, 'P0002', '2023-03-22', '2023-03-22 10:30:00', '2023-03-22 18:30:00'),
  123. (10007, 'P0005', '2023-09-07', '2023-09-07 08:00:00', '2023-09-07 16:00:00'),
  124. (10008, 'P0006', '2023-11-17', '2023-11-17 10:45:00', '2023-11-17 18:45:00'),
  125. (10008, 'P0003', '2023-05-13', '2023-05-13 08:30:00', '2023-05-13 16:30:00'),
  126. (10009, 'P0001', '2023-01-18', '2023-01-18 11:00:00', '2023-01-18 19:00:00'),
  127. (10009, 'P0004', '2023-07-28', '2023-07-28 09:00:00', '2023-07-28 17:00:00'),
  128. (10010, 'P0002', '2023-03-23', '2023-03-23 11:15:00', '2023-03-23 19:15:00'),
  129. (10010, 'P0005', '2023-09-08', '2023-09-08 08:45:00', '2023-09-08 16:45:00'),
  130. (10011, 'P0006', '2023-11-18', '2023-11-18 11:30:00', '2023-11-18 19:30:00'),
  131. (10011, 'P0003', '2023-05-14', '2023-05-14 09:30:00', '2023-05-14 17:30:00'),
  132. (10012, 'P0001', '2023-01-19', '2023-01-19 11:45:00', '2023-01-19 19:45:00'),
  133. (10012, 'P0004', '2023-07-29', '2023-07-29 10:00:00', '2023-07-29 18:00:00');
  134.  
  135.  
  136.  
  137. ## new here ABDUL BOY
  138. SELECT deptno, COUNT(staffno) FROM staff GROUP BY deptno;
  139.  
  140. SELECT *FROM staff WHERE basic_pay IN (SELECT AVG(basic_pay) from staff);
  141.  
  142. SELECT *FROM staff WHERE staffno IN(SELECT staffno FROM staff_skill GROUP BY staffno HAVING COUNT(staffno) > 3);
  143.  
  144. select *from staff where staffno IN
  145. (select staffno from staff_skill where skill_code IN
  146. (select skill_code from skill where charge_outrage > 200));
  147.  
  148. CREATE VIEW staffcount AS SELECT d.deptno,d.dname,COUNT(s.staffno)
  149. AS STAFF_COUNT,SUM(S.BASIC_PAY) AS Depeartment_expense FROM
  150. DEPTS D,STAFF S WHERE D.DEPTNO = S.DEPTNO GROUP BY D.DEPTNO,D.DNAME;
  151.  
  152. SELECT D.deptno,D.dname ,COUNT(S.staffno) AS Number_Of_Staff
  153. FROM depts D, STAFF S WHERE D.deptno = S.deptno
  154. GROUP BY D.deptno, D.dname HAVING COUNT(S.staffno) > 5;
  155.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!