API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 6th, 2016
88
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.20 KB | None | 0 0
raw download clone embed print report
  1. Table1: FLIGHT_DETAILS
  2. Create table FLIGHT_DETAILS(F_NO varchar2(30) primary key,Airlines varchar2(15),Capacity number(4),F_From varchar2(30),F_To varchar2(30),F_Class_Type varchar2(30),F_Price number(10,3));
  3. Table2: Reservation
  4. Create table RESERVATION(Res_No number(10) primary key,Res_From varchar2(30),Res_To varchar2(30),Res_date date,Res_Dep_Time date,Res_Arrival_Time date, Res_Price number(10,3),F_NO varchar2(30), foreign key(F_NO) references FLIGHT_DETAILS(F_NO) )
  5. Table3:
  6. Create table PASSENGER(P_Id varchar2(30),P_name varchar2(30),P_Gender varchar2(20),P_Age number(3),P_Address varchar2(300),P_Phone_No number(20),P_Email_Id varchar2(45), Res_No number(10), foreign key(Res_No) references RESERVATION(Res_No) )
  7.  
  8. Insertion
  9. Insert into flight_details(‘abc123’,’airindia’,300,’hyderabad’, ’newyork’,’economy’,100)
  10. Insert into flight_details(‘xyz890’,’usairways’,500,’newyork’, ’london’,’premium’,750)
  11. Insert into flight_details(‘mno3456’,’lufthansagroup’,500,’newyork’, ’paris’,’business’,750)
  12. insert into reservation values(1,'hyderabad','newyork','01-may-16','08-may-16','09-may-16',100,'ab123')
  13. insert into reservation values(111,'newyork','london','02-may-16','18-may-16','19-may-16',750,'xyz890')
  14. insert into reservation values(222,'newyork','paris','03-may-16','1-june-16','2-june-16',750,'mno3456')
  15.  
  16. insert into passenger values('aa11','robert','male',35,'hyderabad', 1234567890,'robert123@gmail.com',1)
  17. insert into passenger values('aa22','smoth','male',40,'newyork', 1234567890,'smith123@gmail.com',111)
  18. insert into passenger values('aa3','dayana','female',30,'newyork', 1234567890,'dayana123@gmail.com',222)
  19.  
  20. Delete:
  21. delete from flight_details where F_No='ab123' and F_Price>750
  22. delete from passenger where Res_No=(select Res_No from reservation where Res_Date='01-may-16')
  23. update:
  24. update flight_details set F_Price=500 where F_No='ab123';
  25. update flight_details set F_From='Delhi' where F_NO='ab123';
  26. update passenger set P_Name='smith' where P_Name='smoth'
  27.  
  28. Select (Retrive)
  29.  
  30. select * from flight_details;
  31. select * from reservation;
  32. select * from passenger;
  33.  
  34. select * from reservation where Res_Date='01-may-16';
  35. select * from passenger where Res_No in(1,111);
  36. select * from passenger where Res_No not in(1,111);
  37. select * from reservation r,passenger p where exists(select * from passenger where p.Res_No=r.Res_No)
  38. select * from flight_details where F_Price=500 union select * from flight_details where F_No='xyz890'
  39. select * from flight_details where F_Price=500 intersect select * from flight_details where F_No='ab123'
  40. select * from flight_details where F_Price > all(200,500) (works only in SQL SERVER)
  41. select * from passenger where p_Gender='male' except select * from passenger where P_Id='aa11'
  42. join
  43. ==================
  44. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r,flight_details f where r.F_No=f.F_No
  45.  
  46. LEFT JOIN
  47. ==============================
  48. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r LEFT JOIN flight_details f ON r.F_No=f.F_No
  49.  
  50. Right Join
  51. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r RIGHT JOIN flight_details f ON r.F_No=f.F_No
  52.  
  53. FULL OUTER JOIN
  54. Table1: FLIGHT_DETAILS
  55. Create table FLIGHT_DETAILS(F_NO varchar2(30) primary key,Airlines varchar2(15),Capacity number(4),F_From varchar2(30),F_To varchar2(30),F_Class_Type varchar2(30),F_Price number(10,3));
  56. Table2: Reservation
  57. Create table RESERVATION(Res_No number(10) primary key,Res_From varchar2(30),Res_To varchar2(30),Res_date date,Res_Dep_Time date,Res_Arrival_Time date, Res_Price number(10,3),F_NO varchar2(30), foreign key(F_NO) references FLIGHT_DETAILS(F_NO) )
  58. Table3:
  59. Create table PASSENGER(P_Id varchar2(30),P_name varchar2(30),P_Gender varchar2(20),P_Age number(3),P_Address varchar2(300),P_Phone_No number(20),P_Email_Id varchar2(45), Res_No number(10), foreign key(Res_No) references RESERVATION(Res_No) )
  60.  
  61. Insertion
  62. Insert into flight_details(‘abc123’,’airindia’,300,’hyderabad’, ’newyork’,’economy’,100)
  63. Insert into flight_details(‘xyz890’,’usairways’,500,’newyork’, ’london’,’premium’,750)
  64. Insert into flight_details(‘mno3456’,’lufthansagroup’,500,’newyork’, ’paris’,’business’,750)
  65. insert into reservation values(1,'hyderabad','newyork','01-may-16','08-may-16','09-may-16',100,'ab123')
  66. insert into reservation values(111,'newyork','london','02-may-16','18-may-16','19-may-16',750,'xyz890')
  67. insert into reservation values(222,'newyork','paris','03-may-16','1-june-16','2-june-16',750,'mno3456')
  68.  
  69. insert into passenger values('aa11','robert','male',35,'hyderabad', 1234567890,'robert123@gmail.com',1)
  70. insert into passenger values('aa22','smoth','male',40,'newyork', 1234567890,'smith123@gmail.com',111)
  71. insert into passenger values('aa3','dayana','female',30,'newyork', 1234567890,'dayana123@gmail.com',222)
  72.  
  73. Delete:
  74. delete from flight_details where F_No='ab123' and F_Price>750
  75. delete from passenger where Res_No=(select Res_No from reservation where Res_Date='01-may-16')
  76. update:
  77. update flight_details set F_Price=500 where F_No='ab123';
  78. update flight_details set F_From='Delhi' where F_NO='ab123';
  79. update passenger set P_Name='smith' where P_Name='smoth'
  80.  
  81. Select (Retrive)
  82.  
  83. select * from flight_details;
  84. select * from reservation;
  85. select * from passenger;
  86.  
  87. select * from reservation where Res_Date='01-may-16';
  88. select * from passenger where Res_No in(1,111);
  89. select * from passenger where Res_No not in(1,111);
  90. select * from reservation r,passenger p where exists(select * from passenger where p.Res_No=r.Res_No)
  91. select * from flight_details where F_Price=500 union select * from flight_details where F_No='xyz890'
  92. select * from flight_details where F_Price=500 intersect select * from flight_details where F_No='ab123'
  93. select * from flight_details where F_Price > all(200,500) (works only in SQL SERVER)
  94. select * from passenger where p_Gender='male' except select * from passenger where P_Id='aa11'
  95. join
  96. ==================
  97. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r,flight_details f where r.F_No=f.F_No
  98.  
  99. LEFT JOIN
  100. ==============================
  101. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r LEFT JOIN flight_details f ON r.F_No=f.F_No
  102.  
  103. Right Join
  104. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r RIGHT JOIN flight_details f ON r.F_No=f.F_No
  105.  
  106. FULL OUTER JOIN
  107. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r FULL OUTER JOIN flight_details f ON r.F_No=f.F_No
  108.  
  109. Table1: FLIGHT_DETAILS
  110. Create table FLIGHT_DETAILS(F_NO varchar2(30) primary key,Airlines varchar2(15),Capacity number(4),F_From varchar2(30),F_To varchar2(30),F_Class_Type varchar2(30),F_Price number(10,3));
  111. Table2: Reservation
  112. Create table RESERVATION(Res_No number(10) primary key,Res_From varchar2(30),Res_To varchar2(30),Res_date date,Res_Dep_Time date,Res_Arrival_Time date, Res_Price number(10,3),F_NO varchar2(30), foreign key(F_NO) references FLIGHT_DETAILS(F_NO) )
  113. Table3:
  114. Create table PASSENGER(P_Id varchar2(30),P_name varchar2(30),P_Gender varchar2(20),P_Age number(3),P_Address varchar2(300),P_Phone_No number(20),P_Email_Id varchar2(45), Res_No number(10), foreign key(Res_No) references RESERVATION(Res_No) )
  115.  
  116. Insertion
  117. Insert into flight_details(‘abc123’,’airindia’,300,’hyderabad’, ’newyork’,’economy’,100)
  118. Insert into flight_details(‘xyz890’,’usairways’,500,’newyork’, ’london’,’premium’,750)
  119. Insert into flight_details(‘mno3456’,’lufthansagroup’,500,’newyork’, ’paris’,’business’,750)
  120. insert into reservation values(1,'hyderabad','newyork','01-may-16','08-may-16','09-may-16',100,'ab123')
  121. insert into reservation values(111,'newyork','london','02-may-16','18-may-16','19-may-16',750,'xyz890')
  122. insert into reservation values(222,'newyork','paris','03-may-16','1-june-16','2-june-16',750,'mno3456')
  123.  
  124. insert into passenger values('aa11','robert','male',35,'hyderabad', 1234567890,'robert123@gmail.com',1)
  125. insert into passenger values('aa22','smoth','male',40,'newyork', 1234567890,'smith123@gmail.com',111)
  126. insert into passenger values('aa3','dayana','female',30,'newyork', 1234567890,'dayana123@gmail.com',222)
  127.  
  128. Delete:
  129. delete from flight_details where F_No='ab123' and F_Price>750
  130. delete from passenger where Res_No=(select Res_No from reservation where Res_Date='01-may-16')
  131. update:
  132. update flight_details set F_Price=500 where F_No='ab123';
  133. update flight_details set F_From='Delhi' where F_NO='ab123';
  134. update passenger set P_Name='smith' where P_Name='smoth'
  135.  
  136. Select (Retrive)
  137.  
  138. select * from flight_details;
  139. select * from reservation;
  140. select * from passenger;
  141.  
  142. select * from reservation where Res_Date='01-may-16';
  143. select * from passenger where Res_No in(1,111);
  144. select * from passenger where Res_No not in(1,111);
  145. select * from reservation r,passenger p where exists(select * from passenger where p.Res_No=r.Res_No)
  146. select * from flight_details where F_Price=500 union select * from flight_details where F_No='xyz890'
  147. select * from flight_details where F_Price=500 intersect select * from flight_details where F_No='ab123'
  148. select * from flight_details where F_Price > all(200,500) (works only in SQL SERVER)
  149. select * from passenger where p_Gender='male' except select * from passenger where P_Id='aa11'
  150. join
  151. ==================
  152. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r,flight_details f where r.F_No=f.F_No
  153.  
  154. LEFT JOIN
  155. ==============================
  156. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r LEFT JOIN flight_details f ON r.F_No=f.F_No
  157.  
  158. Right Join
  159. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r RIGHT JOIN flight_details f ON r.F_No=f.F_No
  160.  
  161. FULL OUTER JOIN
  162. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r FULL OUTER JOIN flight_details f ON r.F_No=f.F_No
  163.  
  164. Table1: FLIGHT_DETAILS
  165. Create table FLIGHT_DETAILS(F_NO varchar2(30) primary key,Airlines varchar2(15),Capacity number(4),F_From varchar2(30),F_To varchar2(30),F_Class_Type varchar2(30),F_Price number(10,3));
  166. Table2: Reservation
  167. Create table RESERVATION(Res_No number(10) primary key,Res_From varchar2(30),Res_To varchar2(30),Res_date date,Res_Dep_Time date,Res_Arrival_Time date, Res_Price number(10,3),F_NO varchar2(30), foreign key(F_NO) references FLIGHT_DETAILS(F_NO) )
  168. Table3:
  169. Create table PASSENGER(P_Id varchar2(30),P_name varchar2(30),P_Gender varchar2(20),P_Age number(3),P_Address varchar2(300),P_Phone_No number(20),P_Email_Id varchar2(45), Res_No number(10), foreign key(Res_No) references RESERVATION(Res_No) )
  170.  
  171. Insertion
  172. Insert into flight_details(‘abc123’,’airindia’,300,’hyderabad’, ’newyork’,’economy’,100)
  173. Insert into flight_details(‘xyz890’,’usairways’,500,’newyork’, ’london’,’premium’,750)
  174. Insert into flight_details(‘mno3456’,’lufthansagroup’,500,’newyork’, ’paris’,’business’,750)
  175. insert into reservation values(1,'hyderabad','newyork','01-may-16','08-may-16','09-may-16',100,'ab123')
  176. insert into reservation values(111,'newyork','london','02-may-16','18-may-16','19-may-16',750,'xyz890')
  177. insert into reservation values(222,'newyork','paris','03-may-16','1-june-16','2-june-16',750,'mno3456')
  178.  
  179. insert into passenger values('aa11','robert','male',35,'hyderabad', 1234567890,'robert123@gmail.com',1)
  180. insert into passenger values('aa22','smoth','male',40,'newyork', 1234567890,'smith123@gmail.com',111)
  181. insert into passenger values('aa3','dayana','female',30,'newyork', 1234567890,'dayana123@gmail.com',222)
  182.  
  183. Delete:
  184. delete from flight_details where F_No='ab123' and F_Price>750
  185. delete from passenger where Res_No=(select Res_No from reservation where Res_Date='01-may-16')
  186. update:
  187. update flight_details set F_Price=500 where F_No='ab123';
  188. update flight_details set F_From='Delhi' where F_NO='ab123';
  189. update passenger set P_Name='smith' where P_Name='smoth'
  190.  
  191. Select (Retrive)
  192.  
  193. select * from flight_details;
  194. select * from reservation;
  195. select * from passenger;
  196.  
  197. select * from reservation where Res_Date='01-may-16';
  198. select * from passenger where Res_No in(1,111);
  199. select * from passenger where Res_No not in(1,111);
  200. select * from reservation r,passenger p where exists(select * from passenger where p.Res_No=r.Res_No)
  201. select * from flight_details where F_Price=500 union select * from flight_details where F_No='xyz890'
  202. select * from flight_details where F_Price=500 intersect select * from flight_details where F_No='ab123'
  203. select * from flight_details where F_Price > all(200,500) (works only in SQL SERVER)
  204. select * from passenger where p_Gender='male' except select * from passenger where P_Id='aa11'
  205. join
  206. ==================
  207. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r,flight_details f where r.F_No=f.F_No
  208.  
  209. LEFT JOIN
  210. ==============================
  211. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r LEFT JOIN flight_details f ON r.F_No=f.F_No
  212.  
  213. Right Join
  214. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r RIGHT JOIN flight_details f ON r.F_No=f.F_No
  215.  
  216. FULL OUTER JOIN
  217. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r FULL OUTER JOIN flight_details f ON r.F_No=f.F_No
  218.  
  219. Table1: FLIGHT_DETAILS
  220. Create table FLIGHT_DETAILS(F_NO varchar2(30) primary key,Airlines varchar2(15),Capacity number(4),F_From varchar2(30),F_To varchar2(30),F_Class_Type varchar2(30),F_Price number(10,3));
  221. Table2: Reservation
  222. Create table RESERVATION(Res_No number(10) primary key,Res_From varchar2(30),Res_To varchar2(30),Res_date date,Res_Dep_Time date,Res_Arrival_Time date, Res_Price number(10,3),F_NO varchar2(30), foreign key(F_NO) references FLIGHT_DETAILS(F_NO) )
  223. Table3:
  224. Create table PASSENGER(P_Id varchar2(30),P_name varchar2(30),P_Gender varchar2(20),P_Age number(3),P_Address varchar2(300),P_Phone_No number(20),P_Email_Id varchar2(45), Res_No number(10), foreign key(Res_No) references RESERVATION(Res_No) )
  225.  
  226. Insertion
  227. Insert into flight_details(‘abc123’,’airindia’,300,’hyderabad’, ’newyork’,’economy’,100)
  228. Insert into flight_details(‘xyz890’,’usairways’,500,’newyork’, ’london’,’premium’,750)
  229. Insert into flight_details(‘mno3456’,’lufthansagroup’,500,’newyork’, ’paris’,’business’,750)
  230. insert into reservation values(1,'hyderabad','newyork','01-may-16','08-may-16','09-may-16',100,'ab123')
  231. insert into reservation values(111,'newyork','london','02-may-16','18-may-16','19-may-16',750,'xyz890')
  232. insert into reservation values(222,'newyork','paris','03-may-16','1-june-16','2-june-16',750,'mno3456')
  233.  
  234. insert into passenger values('aa11','robert','male',35,'hyderabad', 1234567890,'robert123@gmail.com',1)
  235. insert into passenger values('aa22','smoth','male',40,'newyork', 1234567890,'smith123@gmail.com',111)
  236. insert into passenger values('aa3','dayana','female',30,'newyork', 1234567890,'dayana123@gmail.com',222)
  237.  
  238. Delete:
  239. delete from flight_details where F_No='ab123' and F_Price>750
  240. delete from passenger where Res_No=(select Res_No from reservation where Res_Date='01-may-16')
  241. update:
  242. update flight_details set F_Price=500 where F_No='ab123';
  243. update flight_details set F_From='Delhi' where F_NO='ab123';
  244. update passenger set P_Name='smith' where P_Name='smoth'
  245.  
  246. Select (Retrive)
  247.  
  248. select * from flight_details;
  249. select * from reservation;
  250. select * from passenger;
  251.  
  252. select * from reservation where Res_Date='01-may-16';
  253. select * from passenger where Res_No in(1,111);
  254. select * from passenger where Res_No not in(1,111);
  255. select * from reservation r,passenger p where exists(select * from passenger where p.Res_No=r.Res_No)
  256. select * from flight_details where F_Price=500 union select * from flight_details where F_No='xyz890'
  257. select * from flight_details where F_Price=500 intersect select * from flight_details where F_No='ab123'
  258. select * from flight_details where F_Price > all(200,500) (works only in SQL SERVER)
  259. select * from passenger where p_Gender='male' except select * from passenger where P_Id='aa11'
  260. join
  261. ==================
  262. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r,flight_details f where r.F_No=f.F_No
  263.  
  264. LEFT JOIN
  265. ==============================
  266. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r LEFT JOIN flight_details f ON r.F_No=f.F_No
  267.  
  268. Right Join
  269. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r RIGHT JOIN flight_details f ON r.F_No=f.F_No
  270.  
  271. FULL OUTER JOIN
  272. select r.Res_No,r.Res_From,r.Res_To,r.Res_Date,f.F_No,f.Airlines,f.F_Class_Type,f.F_Price from reservation r FULL OUTER JOIN flight_details f ON r.F_No=f.F_No
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!