API tools faq
paste
Advertisement
GamerBhai02

DBMS Exp 3

GamerBhai02
Mar 24th, 2025 (edited)
262
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 4.30 KB | Source Code | 0 0
raw download clone embed print report
  1. create database student;
  2. use student;
  3. create table student(snum integer,sname varchar(10),major varchar(2),level varchar(2),age integer,PRIMARY KEY(snum));
  4. create table faculty(fid integer,fname varchar(20),deptid integer,PRIMARY KEY(fid));
  5. create table class(cname varchar(20),meets_at varchar(10),room varchar(10),fid integer,PRIMARY KEY(cname),FOREIGN KEY(fid) REFERENCES faculty(fid));
  6. create table enrolled(snum integer,cname varchar(20),PRIMARY KEY(snum,cname),FOREIGN KEY(snum) REFERENCES student(snum),FOREIGN KEY(cname) REFERENCES class(cname));
  7. insert into student(snum,sname,major,level,age) values
  8. (1,'jhon','CS','Sr',19),(2,'smith','CS','Jr',20),(3,'jacob','CV','Sr',20),(4,'tom','CS','Jr',20),(5,'sid','CS','Jr',20),(6,'harry','CS','Sr',21);
  9. insert into faculty(fid,fname,deptid) values(11,'Harshith',1000),(12,'Mohan',1000),(13,'Kumar',1001),(14,'Shobha',1002),(15,'Shan',1000);
  10. insert into student(snum,sname,major,level,age) values(7,'Kala','AI','Sr',21);
  11. insert into class(cname,meets_at,room,fid) values
  12. ('class1','noon','room1',14),('class10','morning','room128',14),
  13. ('class2','morning','room2',12),('class3','morning','room3',11),
  14. ('class4','evening','room4',14),('class5','night','room3',15),
  15. ('class6','morning','room2',14),('class7','morning','room3',14);
  16. insert into enrolled(snum,cname) values
  17. (1,'class1'),(2,'class1'),(3,'class3'),(4,'class3'),(3,'class4'),(5,'class4'),
  18. (1,'class5'),(2,'class5'),(3,'class5'),(4,'class5'),(5,'class5'),(6,'class5');
  19. select * from student;
  20. select * from class;
  21. select * from enrolled;
  22. select * from faculty;
  23. select distinct s.sname from student s,class c,faculty f,enrolled e where s.snum=e.snum and e.cname=c.cname and s.level='Jr' and f.fname='Harshith' and f.fid=c.fid;
  24. select distinct cname from class where room='room128' or cname in (select e.cname from enrolled e group by e.cname having COUNT(*)>=5);
  25. INSERT INTO enrolled (snum,cname) VALUES (3,'class2');
  26. SELECT DISTINCT s.sname FROM student s WHERE s.snum IN (SELECT el.snum FROM enrolled el,enrolled e2,class c1,class c2 WHERE el.snum=e2.snum AND el.cname=c1.cname AND e2.cname=c2.cname AND c1.meets_at=c2.meets_at);
  27. select f.fname, f.fid from faculty f where f.fid in (select fid from class group by fid having count(*)=(select count(distinct room) from class));
  28. select f.fname from faculty f WHERE 5>(select count(e.snum) from class c, enrolled e where c.cname=e.cname and c.fid=f.fid);
  29.  
  30. Output:
  31.  
  32. +------+-------+-------+-------+------+
  33. | snum | sname | major | level | age  |
  34. +------+-------+-------+-------+------+
  35. |    1 | jhon  | CS    | Sr    |   19 |
  36. |    2 | smith | CS    | Jr    |   20 |
  37. |    3 | jacob | CV    | Sr    |   20 |
  38. |    4 | tom   | CS    | Jr    |   20 |
  39. |    5 | sid   | CS    | Jr    |   20 |
  40. |    6 | harry | CS    | Sr    |   21 |
  41. |    7 | Kala  | AI    | Sr    |   21 |
  42. +------+-------+-------+-------+------+
  43. +---------+----------+---------+------+
  44. | cname   | meets_at | room    | fid  |
  45. +---------+----------+---------+------+
  46. | class1  | noon     | room1   |   14 |
  47. | class10 | morning  | room128 |   14 |
  48. | class2  | morning  | room2   |   12 |
  49. | class3  | morning  | room3   |   11 |
  50. | class4  | evening  | room4   |   14 |
  51. | class5  | night    | room3   |   15 |
  52. | class6  | morning  | room2   |   14 |
  53. | class7  | morning  | room3   |   14 |
  54. +---------+----------+---------+------+
  55. +------+--------+
  56. | snum | cname  |
  57. +------+--------+
  58. |    1 | class1 |
  59. |    2 | class1 |
  60. |    3 | class3 |
  61. |    4 | class3 |
  62. |    3 | class4 |
  63. |    5 | class4 |
  64. |    1 | class5 |
  65. |    2 | class5 |
  66. |    3 | class5 |
  67. |    4 | class5 |
  68. |    5 | class5 |
  69. |    6 | class5 |
  70. +------+--------+
  71. +-----+----------+--------+
  72. | fid | fname    | deptid |
  73. +-----+----------+--------+
  74. |  11 | Harshith |   1000 |
  75. |  12 | Mohan    |   1000 |
  76. |  13 | Kumar    |   1001 |
  77. |  14 | Shobha   |   1002 |
  78. |  15 | Shan     |   1000 |
  79. +-----+----------+--------+
  80. +-------+
  81. | sname |
  82. +-------+
  83. | tom   |
  84. +-------+
  85. +---------+
  86. | cname   |
  87. +---------+
  88. | class10 |
  89. | class5  |
  90. +---------+
  91. +-------+
  92. | sname |
  93. +-------+
  94. | jhon  |
  95. | smith |
  96. | jacob |
  97. | tom   |
  98. | sid   |
  99. | harry |
  100. +-------+
  101. +--------+-----+
  102. | fname  | fid |
  103. +--------+-----+
  104. | Shobha |  14 |
  105. +--------+-----+
  106. +----------+
  107. | fname    |
  108. +----------+
  109. | Harshith |
  110. | Mohan    |
  111. | Kumar    |
  112. | Shobha   |
  113. +----------+
Tags: Exp 3
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!