API tools faq
paste
Advertisement
GamerBhai02

DBMS EXP 4

GamerBhai02
Mar 24th, 2025 (edited)
243
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 5.29 KB | Source Code | 0 0
raw download clone embed print report
  1. create database flight;
  2. use flight;
  3. create table flight(flno integer,frm varchar(20),too varchar(20),distance integer,departs varchar(20),arrives varchar(20),price integer,PRIMARY KEY(flno));
  4. create table aircraft(aid integer,aname varchar(20),cruisingrange integer,PRIMARY KEY(aid));
  5. create table employees(eid integer,ename varchar(20),salary integer,PRIMARY KEY(eid));
  6. create table certified(eid integer,aid integer,PRIMARY KEY(eid,aid),FOREIGN KEY(eid) REFERENCES employees(eid),FOREIGN KEY(aid) REFERENCES aircraft(aid));
  7. insert into flight(flno, frm, too, distance, departs, arrives,price) values
  8. (1, 'Bangalore', 'Mangalore', 360, '10:45:00', '12:00:00',10000),
  9. (2,'Bangalore', 'Delhi', 5000,'12:15:00','04:30:00',25000),
  10. (3,'Bangalore', 'Mumbai', 3500,'02:15:00','05:25:00',30000),
  11. (4,'Delhi', 'Mumbai', 4500,'10:15:00', '12:05:00',35000),
  12. (5,'Delhi', 'Frankfurt', 18000,'07:15:00','05:30:00',90000),
  13. (6,'Bangalore', 'Frankfurt', 19500,'10:00:00','07:45:00',95000),
  14. (7,'Bangalore', 'Frankfurt', 17000,'12:00:00','06:30:00',99000);
  15. insert into aircraft(aid,aname,cruisingrange) values
  16. (123,'Airbus', 1000),(302,'Boeing', 5000),(306,'Jet01',5000),(378,'Airbus380',8000),
  17. (456,'Aircraft',500),(789,'Aircraft02',800),(951,'Aircraft03',1000);
  18. insert into employees(eid,ename,salary) values
  19. (1,'Ajay',30000),(2,'Ajith',85000),(3,'Arnab', 50000),(4,'Harry', 45000),
  20. (5,'Ron',90000),(6,'Josh', 75000),(7,'Ram', 100000);
  21. insert into certified(eid,aid) values
  22. (1,123),(2,123),(1,302),(5,302),(7,302),(1,306),(2,306),(1,378),(2,378),(4,378),(6,456),(3,456),(5,789),(6,789),(3,951),(1,951),(1,789);
  23. select * from employees;
  24. select * from aircraft;
  25. select * from flight;
  26. select * from certified;
  27. select distinct a.aname from aircraft a, certified c, employees e where a.aid=c.aid and c.eid=e.eid and e.salary>80000;
  28. SELECT DISTINCT a.aname FROM aircraft a, certified c, employees e WHERE a.aid=c.aid AND c.eid=e.eid AND NOT EXISTS (SELECT * FROM employees e1 WHERE e1.eid=e.eid AND e1.salary<80000);
  29. SELECT c.eid,MAX(cruisingrange) FROM certified c,aircraft a WHERE c.aid=a.aid GROUP BY c.eid HAVING COUNT(*)>3;
  30. SELECT DISTINCT e.ename FROM employees e WHERE e.salary< (SELECT MIN(f.price) FROM flight f WHERE f.frm='Bangalore' AND f.too='Frankfurt');
  31. SELECT a.aname, AVG(e.salary) FROM aircraft a,certified c,employees e WHERE a.aid=c.aid AND c.eid=e.eid AND a.cruisingrange>1000 GROUP BY a.aname;
  32. SELECT distinct e.ename FROM employees e,aircraft a,certified c WHERE e.eid=c.eid AND c.aid=a.aid AND a.aname='Boeing';
  33. SELECT a.aid FROM aircraft a WHERE a.cruisingrange>(SELECT MIN(f.distance) FROM flight f WHERE f.frm='Bangalore' AND f.too='Delhi');
  34.  
  35. Output:
  36.  
  37. +-----+-------+--------+
  38. | eid | ename | salary |
  39. +-----+-------+--------+
  40. |   1 | Ajay  |  30000 |
  41. |   2 | Ajith |  85000 |
  42. |   3 | Arnab |  50000 |
  43. |   4 | Harry |  45000 |
  44. |   5 | Ron   |  90000 |
  45. |   6 | Josh  |  75000 |
  46. |   7 | Ram   | 100000 |
  47. +-----+-------+--------+
  48. +-----+------------+---------------+
  49. | aid | aname      | cruisingrange |
  50. +-----+------------+---------------+
  51. | 123 | Airbus     |          1000 |
  52. | 302 | Boeing     |          5000 |
  53. | 306 | Jet01      |          5000 |
  54. | 378 | Airbus380  |          8000 |
  55. | 456 | Aircraft   |           500 |
  56. | 789 | Aircraft02 |           800 |
  57. | 951 | Aircraft03 |          1000 |
  58. +-----+------------+---------------+
  59. +------+-----------+-----------+----------+----------+----------+-------+
  60. | flno | frm       | too       | distance | departs  | arrives  | price |
  61. +------+-----------+-----------+----------+----------+----------+-------+
  62. |    1 | Bangalore | Mangalore |      360 | 10:45:00 | 12:00:00 | 10000 |
  63. |    2 | Bangalore | Delhi     |     5000 | 12:15:00 | 04:30:00 | 25000 |
  64. |    3 | Bangalore | Mumbai    |     3500 | 02:15:00 | 05:25:00 | 30000 |
  65. |    4 | Delhi     | Mumbai    |     4500 | 10:15:00 | 12:05:00 | 35000 |
  66. |    5 | Delhi     | Frankfurt |    18000 | 07:15:00 | 05:30:00 | 90000 |
  67. |    6 | Bangalore | Frankfurt |    19500 | 10:00:00 | 07:45:00 | 95000 |
  68. |    7 | Bangalore | Frankfurt |    17000 | 12:00:00 | 06:30:00 | 99000 |
  69. +------+-----------+-----------+----------+----------+----------+-------+
  70. +-----+-----+
  71. | eid | aid |
  72. +-----+-----+
  73. |   1 | 123 |
  74. |   2 | 123 |
  75. |   1 | 302 |
  76. |   5 | 302 |
  77. |   7 | 302 |
  78. |   1 | 306 |
  79. |   2 | 306 |
  80. |   1 | 378 |
  81. |   2 | 378 |
  82. |   4 | 378 |
  83. |   3 | 456 |
  84. |   6 | 456 |
  85. |   1 | 789 |
  86. |   5 | 789 |
  87. |   6 | 789 |
  88. |   1 | 951 |
  89. |   3 | 951 |
  90. +-----+-----+
  91. +------------+
  92. | aname      |
  93. +------------+
  94. | Airbus     |
  95. | Jet01      |
  96. | Airbus380  |
  97. | Boeing     |
  98. | Aircraft02 |
  99. +------------+
  100. +------------+
  101. | aname      |
  102. +------------+
  103. | Airbus     |
  104. | Boeing     |
  105. | Jet01      |
  106. | Airbus380  |
  107. | Aircraft02 |
  108. +------------+
  109. +-----+--------------------+
  110. | eid | MAX(cruisingrange) |
  111. +-----+--------------------+
  112. |   1 |               8000 |
  113. +-----+--------------------+
  114. +-------+
  115. | ename |
  116. +-------+
  117. | Ajay  |
  118. | Ajith |
  119. | Arnab |
  120. | Harry |
  121. | Ron   |
  122. | Josh  |
  123. +-------+
  124. +-----------+---------------+
  125. | aname     | AVG(e.salary) |
  126. +-----------+---------------+
  127. | Boeing    |    73333.3333 |
  128. | Jet01     |    57500.0000 |
  129. | Airbus380 |    53333.3333 |
  130. +-----------+---------------+
  131. +-------+
  132. | ename |
  133. +-------+
  134. | Ajay  |
  135. | Ron   |
  136. | Ram   |
  137. +-------+
  138. +-----+
  139. | aid |
  140. +-----+
  141. | 378 |
  142. +-----+
Tags: exp 4
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!