API tools faq
paste
tampurus

Practical 4

tampurus
May 18th, 2022 (edited)
75
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 5.38 KB | None | 0 0
raw download clone embed print report
  1. 1 SELECT c.cname,s.sname FROM salesman_aj s INNER JOIN customer_aj c ON c.snum=s.snum;
  2. -- select CNAME,SNAME from customer_aj c,salesman_aj s where c.SNUM=s.SNUM;
  3.  
  4. CNAME   SNAME
  5. Harsh   Piyush
  6. Chirag  Piyush
  7. Govind  Sejal
  8. Lalit   Sejal
  9. Gita    Anand
  10. Pratik  Miti
  11. Chinmay Rajesh
  12.  
  13.  
  14. 2 SELECT s.sname,c.cname FROM salesman_aj s INNER JOIN customer_aj c ON s.city=c.city;
  15.  
  16. SNAME   CNAME   CITY
  17. Miti    Harsh   London
  18. Piyush  Harsh   London
  19. Sejal   Lalit   Surat
  20. Miti    Chirag  London
  21. Piyush  Chirag  London
  22. Sejal   Chinmay Surat
  23.  
  24.  
  25. 3 SELECT o.ONUM,c.CNAME,s.SNAME FROM Orders_aj o
  26. INNER JOIN Customer_aj c ON o.CNUM=c.CNUM
  27. INNER JOIN Salesman_aj s ON s.SNUM=o.SNUM;
  28.  
  29. -- select o.onum,c.cname,s.sname from orders_aj o inner join customer_aj c on o.snum=c.snum inner join salesman_aj s on s.snum=c.snum;
  30. -- select o.ONUM,c.CNAME,s.SNAME from Orders_aj o inner join Customer_aj c on o.CNUM=c.CNUM inner join Salesman_aj s on s.SNUM=o.SNUM;
  31.  
  32. ONUM    SNAME   CNAME
  33. 3002    Miti    Pratik
  34. 3001    Rajesh  Chinmay
  35. 3006    Rajesh  Chinmay
  36. 3011    Piyush  Chirag
  37. 3008    Piyush  Chirag
  38. 3010    Sejal   Govind
  39. 3007    Sejal   Govind
  40. 3005    Sejal   Lalit
  41. 3009    Anand   Gita
  42. 3003    Piyush  Harsh
  43.  
  44. 4 SELECT o.ONUM,o.AMOUNT,c.CNAME,s.SNAME,s.CITY,c.CITY FROM Orders_aj o
  45. INNER JOIN Customer_aj c ON o.CNUM=c.CNUM
  46. INNER JOIN Salesman_aj s ON s.SNUM=o.SNUM AND s.CITY<>c.CITY;
  47.  
  48. ONUM    AMOUNT  CNAME   SNAME   CITY      CITY
  49. 3002    1900.1  Pratik  Miti    London    Rome
  50. 3001    18.69   Chinmay Rajesh  Baroda    Surat
  51. 3006    1098.16 Chinmay Rajesh  Baroda    Surat
  52. 3010    1309.95 Govind  Sejal   Surat     Bombay
  53. 3007    75.75   Govind  Sejal   Surat     Bombay
  54. 3009    1713.23 Gita    Anand   NEW Delhi Rome
  55.  
  56.  
  57. 5 SELECT c.CNAME,s.SNAME,s.COMMISION FROM Salesman_aj s INNER JOIN Customer_aj c ON COMMISION >12 AND s.SNUM=c.SNUM;
  58.  
  59. CNAME   SNAME   COMMISION
  60. Govind  Sejal   13
  61. Lalit   Sejal   13
  62. Chinmay Rajesh  15
  63.  
  64. 6 SELECT s.SNAME,((AMOUNT*COMMISION)/100) AS COMMISSION ,c.CNAME,c.RATING,s.COMMISION FROM orders_aj o INNER JOIN Salesman_aj s ON s.SNUM=o.SNUM INNER JOIN Customer_aj c ON c.SNUM=s.SNUM AND RATING>100;
  65.  
  66. SNAME   COMMISSION  CNAME   RATING  COMMISION
  67. Sejal   170.2935    Govind  300     13
  68. Sejal   170.2935    Lalit   200     13
  69. Sejal   9.8475      Govind  300     13
  70. Sejal   9.8475      Lalit   200     13
  71. Anand   171.323     Gita    200     10
  72.  
  73. 7  SELECT  c1.cname,c2.cname,c1.rating FROM customer_aj c1 JOIN customer_aj c2 ON c1.cnum>c2.cnum AND c1.rating=c2.rating
  74.  
  75. CNAME   CNAME   RATING
  76. Pratik  Chirag  100
  77. Chinmay Govind  300
  78. Lalit   Gita    200
  79. Pratik  Harsh   100
  80. Chirag  Harsh   100
  81.  
  82.  
  83.  
  84. 10 SELECT  c1.cname,c2.cname,s.sname,s.sname FROM customer_aj c1 JOIN customer_aj c2 ON c1.cnum>c2.cnum INNER JOIN salesman_aj s ON c1.snum=s.snum AND c2.snum=s.snum
  85.  
  86. CNAME   CNAME   SNAME   SNAME
  87. Chirag  Harsh   Piyush  Piyush
  88. Govind  Lalit   Sejal   Sejal
  89.  
  90. 11 SELECT s1.sname,s2.sname,s1.city FROM salesman_aj s1 JOIN salesman_aj s2 ON s1.snum>s2.snum AND s1.city = s2.city;
  91.  
  92. SNAME   SNAME   CITY
  93. Miti    Piyush  London
  94.  
  95. 12 SELECT c.cname ,o1.onum,o2.onum FROM customer_aj c JOIN orders_aj o1 ON o1.cnum = c.cnum JOIN orders_aj o2 ON o2.cnum =  c.cnum AND o1.onum>o2.onum;
  96.  
  97. CNAME   ONUM    ONUM
  98. Chirag  3011    3008
  99. Govind  3010    3007
  100. Chinmay 3006    3001
  101.  
  102.  
  103. 14 SELECT s.SNAME, o.ONUM,o.AMOUNT,o.ODATE FROM Orders_aj o INNER JOIN Salesman_aj s ON s.SNAME='Miti'AND o.SNUM=s.SNUM;
  104.  
  105. SNAME   ONUM    AMOUNT  ODATE
  106. Miti    3002    1900.1  10-MAR-97
  107.  
  108. 15 SELECT s.SNAME,s.CITY,o.ONUM,o.ODATE,o.AMOUNT FROM Orders_aj o INNER JOIN Salesman_aj s ON s.CITY='Baroda' AND o.SNUM=s.SNUM;
  109.  
  110. SNAME   CITY    ONUM    ODATE   AMOUNT
  111. Rajesh  Baroda  3001    10-MAR-97   18.69
  112. Rajesh  Baroda  3006    10-MAR-97   1098.16
  113.  
  114. 16 SELECT c.CNAME,o.ONUM,o.AMOUNT,o.ODATE FROM Orders_aj o INNER JOIN Customer_aj c ON c.CNAME='Harsh' AND o.CNUM=c.CNUM;
  115.  
  116. CNAME   ONUM    AMOUNT  ODATE
  117. Harsh   3003    767.19  10-MAR-97
  118.  
  119.  
  120.  
  121.  
  122.  
  123.  
  124.  
  125.  
  126. #Practical List 4
  127. 1.SELECT CNAME,SNAME FROM Customers c,Salesman s WHERE c.SNUM=s.SNUM;
  128. 2.SELECT CNAME,SNAME,s.CITY FROM Customers c, Salesman s WHERE c.CITY=s.CITY;
  129. 3.SELECT SNAME,CNAME,ONUM FROM Salesman s,Customers c,Orders_anisha o WHERE o.SNUM=s.SNUM AND o.CNUM=c.CNUM;
  130. 3.SELECT o.ONUM,o.AMOUNT,c.CNAME,s.SNAME FROM Orders_anisha o INNER JOIN Customers c
  131.    ON o.CNUM=c.CNUM INNER JOIN Salesman s ON s.SNUM=o.SNUM;
  132. 4.SELECT o.ONUM,o.AMOUNT,c.CNAME,s.SNAME,s.CITY,c.CITY FROM Orders_anisha o INNER    JOIN Customers c  ON o.CNUM=c.CNUM INNER JOIN Salesman s ON s.SNUM=o.SNUM AND    s.CITY<>c.CITY;
  133. 5.SELECT c.CNAME,s.SNAME,s.COMMISSION FROM Salesman s INNER JOIN Customers c ON COMMISSION >12 AND s.SNUM=c.SNUM;
  134. 6.SELECT s.SNAME,((AMOUNT*COMMISSION)/100) AS COMMISSION ,c.CNAME,c.RATING,s.COMMISSION FROM Orders_anisha o INNER JOIN Salesman s ON s.SNUM=o.SNUM INNER JOIN Customers c ON c.SNUM=s.SNUM AND RATING>100;
  135. 14.SELECT s.SNAME, o.ONUM,o.AMOUNT,o.ODATE FROM Orders_anisha o INNER JOIN Salesman s ON s.SNAME='Miti'AND o.SNUM=s.SNUM;
  136. 15. SELECT s.SNAME,s.CITY,o.ONUM,o.ODATE,o.AMOUNT FROM Orders_anisha o INNER JOIN Salesman s ON s.CITY='Baroda' AND o.SNUM=s.SNUM;
  137. 16. SELECT c.CNAME,o.ONUM,o.AMOUNT,o.ODATE FROM Orders_anisha o INNER JOIN Customers c ON c.CNAME='Harsh' AND o.CNUM=c.CNUM;
  138.  
  139. # Nhi aaye
  140. 7. SELECT c1.CNAME,c2.CNAME,c1.RATING FROM Customers AS c1,Customers AS c2 ON c1.RATING=c2.RATING AND c1.CNUM>c2.CNUM;
  141. 18. SELECT SNAME,avg(COMMISSION),CITY FROM Salesman WHERE CITY='London';
  142. 11.SELECT s1.SNUM,s1.SNAME,s1.CITY
  143. FROM Salesman AS s1 JOIN Salesman AS s2 WHERE s1.CITY=s2.CITY;
  144. 10.SELECT s.SNAME,s.SNUM,c1.CNAME FROM Customers AS c1 , Customers AS c2
  145. ON c1.SNUM=c2.SNUM
  146. INNER JOIN Salesman s ON c1.SNUM=s.SNUM;
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!