API tools faq
paste
sidrs

Constraint Types (Half Assed)

sidrs
Jul 14th, 2025 (edited)
30
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 9.13 KB | None | 0 0
raw download clone embed print report
  1. mysql> CREATE TABLE Employees(ID int(5), Name varchar(50), City varchar(50), Post varchar(40), Salary int(5), DeptID int(5));
  2. Query OK, 0 rows affected, 3 warnings (0.61 sec)
  3.  
  4. mysql> DESC Employees
  5.     -> ;
  6. +--------+-------------+------+-----+---------+-------+
  7. | Field  | Type        | Null | Key | Default | Extra |
  8. +--------+-------------+------+-----+---------+-------+
  9. | ID     | int         | YES  |     | NULL    |       |
  10. | Name   | varchar(50) | YES  |     | NULL    |       |
  11. | City   | varchar(50) | YES  |     | NULL    |       |
  12. | Post   | varchar(40) | YES  |     | NULL    |       |
  13. | Salary | int         | YES  |     | NULL    |       |
  14. | DeptID | int         | YES  |     | NULL    |       |
  15. +--------+-------------+------+-----+---------+-------+
  16. 6 rows in set (0.01 sec)
  17.  
  18. mysql> INSERT INTO Employees VALUES(1, 'Arjun', 'Pune', 'Developer', 50000, 2);
  19. Query OK, 1 row affected (0.06 sec)
  20.  
  21. mysql> SELECT * FROM Employees;
  22. +------+-------+------+-----------+--------+--------+
  23. | ID   | Name  | City | Post      | Salary | DeptID |
  24. +------+-------+------+-----------+--------+--------+
  25. |    1 | Arjun | Pune | Developer |  50000 |      2 |
  26. +------+-------+------+-----------+--------+--------+
  27. 1 row in set (0.00 sec)
  28.  
  29. mysql> INSERT INTO Employees VALUES(2, 'Brajesh', 'Noida', 'Developer', 60000, 2);
  30. Query OK, 1 row affected (0.09 sec)
  31.  
  32. mysql> INSERT INTO Employees VALUES(2, 'Brajesh', 'Noida', 'Developer', 45000, 3);
  33. Query OK, 1 row affected (0.08 sec)
  34.  
  35. mysql> SELECT * FROM Employees;
  36. +------+---------+-------+-----------+--------+--------+
  37. | ID   | Name    | City  | Post      | Salary | DeptID |
  38. +------+---------+-------+-----------+--------+--------+
  39. |    1 | Arjun   | Pune  | Developer |  50000 |      2 |
  40. |    2 | Brajesh | Noida | Developer |  60000 |      2 |
  41. |    2 | Brajesh | Noida | Developer |  45000 |      3 |
  42. +------+---------+-------+-----------+--------+--------+
  43. 3 rows in set (0.00 sec)
  44.  
  45. mysql> DELETE FROM Employees WHERE DeptID = 3;
  46. Query OK, 1 row affected (0.07 sec)
  47.  
  48. mysql> SELECT * FROM Employees;
  49. +------+---------+-------+-----------+--------+--------+
  50. | ID   | Name    | City  | Post      | Salary | DeptID |
  51. +------+---------+-------+-----------+--------+--------+
  52. |    1 | Arjun   | Pune  | Developer |  50000 |      2 |
  53. |    2 | Brajesh | Noida | Developer |  60000 |      2 |
  54. +------+---------+-------+-----------+--------+--------+
  55. 2 rows in set (0.00 sec)
  56.  
  57. mysql> INSERT INTO Employees VALUES(3, 'Cheta', 'Pune', 'HR Manager', 45000, 3);
  58. Query OK, 1 row affected (0.07 sec)
  59.  
  60. mysql> SELECT * FROM Employees;
  61. +------+---------+-------+------------+--------+--------+
  62. | ID   | Name    | City  | Post       | Salary | DeptID |
  63. +------+---------+-------+------------+--------+--------+
  64. |    1 | Arjun   | Pune  | Developer  |  50000 |      2 |
  65. |    2 | Brajesh | Noida | Developer  |  60000 |      2 |
  66. |    3 | Cheta   | Pune  | HR Manager |  45000 |      3 |
  67. +------+---------+-------+------------+--------+--------+
  68. 3 rows in set (0.00 sec)
  69.  
  70. mysql> INSERT INTO Employees VALUES(4, 'Dhruv', 'Pune', 'Tester', 30000, 2);
  71. Query OK, 1 row affected (0.10 sec)
  72.  
  73. mysql> INSERT INTO Employees VALUES(5, 'Kalyan', 'Mumbai', 'Vice President', 110000, 1);
  74. Query OK, 1 row affected (0.10 sec)
  75.  
  76. mysql> INSERT INTO Employees VALUES(6, 'Pooja', 'Mumbai', 'Developer', 50000, 2);
  77. Query OK, 1 row affected (0.14 sec)
  78.  
  79. mysql> INSERT INTO Employees VALUES(7, 'Sumit', 'Pune', 'Sr. Developer', 70000, 2);
  80. Query OK, 1 row affected (0.10 sec)
  81.  
  82. mysql> INSERT INTO Employees VALUES(8, 'Niharika', 'Mumbai', 'Sr. Developer', 85000, 2);
  83. Query OK, 1 row affected (0.10 sec)
  84.  
  85. mysql> INSERT INTO Employees VALUES(9, 'Rakesh', 'Noida', 'Manager', 80000, 3);
  86. Query OK, 1 row affected (0.07 sec)
  87.  
  88. mysql> INSERT INTO Employees VALUES(10, 'Varun', 'Pune', 'Security Head', 40000, 4);
  89. Query OK, 1 row affected (0.05 sec)
  90.  
  91. mysql> SELECT * FROM Employees
  92.     -> ;
  93. +------+----------+--------+----------------+--------+--------+
  94. | ID   | Name     | City   | Post           | Salary | DeptID |
  95. +------+----------+--------+----------------+--------+--------+
  96. |    1 | Arjun    | Pune   | Developer      |  50000 |      2 |
  97. |    2 | Brajesh  | Noida  | Developer      |  60000 |      2 |
  98. |    3 | Cheta    | Pune   | HR Manager     |  45000 |      3 |
  99. |    4 | Dhruv    | Pune   | Tester         |  30000 |      2 |
  100. |    5 | Kalyan   | Mumbai | Vice President | 110000 |      1 |
  101. |    6 | Pooja    | Mumbai | Developer      |  50000 |      2 |
  102. |    7 | Sumit    | Pune   | Sr. Developer  |  70000 |      2 |
  103. |    8 | Niharika | Mumbai | Sr. Developer  |  85000 |      2 |
  104. |    9 | Rakesh   | Noida  | Manager        |  80000 |      3 |
  105. |   10 | Varun    | Pune   | Security Head  |  40000 |      4 |
  106. +------+----------+--------+----------------+--------+--------+
  107. 10 rows in set (0.01 sec)
  108.  
  109. mysql> SELECT * FROM Employees;
  110. +------+----------+--------+----------------+--------+--------+
  111. | ID   | Name     | City   | Post           | Salary | DeptID |
  112. +------+----------+--------+----------------+--------+--------+
  113. |    1 | Arjun    | Pune   | Developer      |  50000 |      2 |
  114. |    2 | Brajesh  | Noida  | Developer      |  60000 |      2 |
  115. |    3 | Cheta    | Pune   | HR Manager     |  45000 |      3 |
  116. |    4 | Dhruv    | Pune   | Tester         |  30000 |      2 |
  117. |    5 | Kalyan   | Mumbai | Vice President | 110000 |      1 |
  118. |    6 | Pooja    | Mumbai | Developer      |  50000 |      2 |
  119. |    7 | Sumit    | Pune   | Sr. Developer  |  70000 |      2 |
  120. |    8 | Niharika | Mumbai | Sr. Developer  |  85000 |      2 |
  121. |    9 | Rakesh   | Noida  | Manager        |  80000 |      3 |
  122. |   10 | Varun    | Pune   | Security Head  |  40000 |      4 |
  123. +------+----------+--------+----------------+--------+--------+
  124. 10 rows in set (0.00 sec)
  125.  
  126. mysql> SELECT *
  127.     -> FROM Employees
  128.     -> WHERE Salary > (
  129.     -> SELECT Salary
  130.     -> FROM Employees
  131.     -> WHERE Name = 'Pooja');
  132. +------+----------+--------+----------------+--------+--------+
  133. | ID   | Name     | City   | Post           | Salary | DeptID |
  134. +------+----------+--------+----------------+--------+--------+
  135. |    2 | Brajesh  | Noida  | Developer      |  60000 |      2 |
  136. |    5 | Kalyan   | Mumbai | Vice President | 110000 |      1 |
  137. |    7 | Sumit    | Pune   | Sr. Developer  |  70000 |      2 |
  138. |    8 | Niharika | Mumbai | Sr. Developer  |  85000 |      2 |
  139. |    9 | Rakesh   | Noida  | Manager        |  80000 |      3 |
  140. +------+----------+--------+----------------+--------+--------+
  141. 5 rows in set (0.00 sec)
  142.  
  143. mysql> SELECT Name FROM Employees WHERE City = (SELECT City FROM Employees WHERE Name = 'Rakesh');
  144. +---------+
  145. | Name    |
  146. +---------+
  147. | Brajesh |
  148. | Rakesh  |
  149. +---------+
  150. 2 rows in set (0.00 sec)
  151.  
  152. mysql> SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
  153. +------+----------+--------+----------------+--------+--------+
  154. | ID   | Name     | City   | Post           | Salary | DeptID |
  155. +------+----------+--------+----------------+--------+--------+
  156. |    5 | Kalyan   | Mumbai | Vice President | 110000 |      1 |
  157. |    7 | Sumit    | Pune   | Sr. Developer  |  70000 |      2 |
  158. |    8 | Niharika | Mumbai | Sr. Developer  |  85000 |      2 |
  159. |    9 | Rakesh   | Noida  | Manager        |  80000 |      3 |
  160. +------+----------+--------+----------------+--------+--------+
  161. 4 rows in set (0.00 sec)
  162.  
  163. mysql> SELECT * FROM Employees WHERE Salary < (SELECT AVG(Salary) FROM Employees);
  164. +------+---------+--------+---------------+--------+--------+
  165. | ID   | Name    | City   | Post          | Salary | DeptID |
  166. +------+---------+--------+---------------+--------+--------+
  167. |    1 | Arjun   | Pune   | Developer     |  50000 |      2 |
  168. |    2 | Brajesh | Noida  | Developer     |  60000 |      2 |
  169. |    3 | Cheta   | Pune   | HR Manager    |  45000 |      3 |
  170. |    4 | Dhruv   | Pune   | Tester        |  30000 |      2 |
  171. |    6 | Pooja   | Mumbai | Developer     |  50000 |      2 |
  172. |   10 | Varun   | Pune   | Security Head |  40000 |      4 |
  173. +------+---------+--------+---------------+--------+--------+
  174. 6 rows in set (0.00 sec)
  175.  
  176. mysql> SELECT * FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees);
  177. +------+--------+--------+----------------+--------+--------+
  178. | ID   | Name   | City   | Post           | Salary | DeptID |
  179. +------+--------+--------+----------------+--------+--------+
  180. |    5 | Kalyan | Mumbai | Vice President | 110000 |      1 |
  181. +------+--------+--------+----------------+--------+--------+
  182. 1 row in set (0.00 sec)
  183.  
  184. mysql> SELECT * FROM Employees WHERE Salary = (SELECT MIN(Salary) FROM Employees);
  185. +------+-------+------+--------+--------+--------+
  186. | ID   | Name  | City | Post   | Salary | DeptID |
  187. +------+-------+------+--------+--------+--------+
  188. |    4 | Dhruv | Pune | Tester |  30000 |      2 |
  189. +------+-------+------+--------+--------+--------+
  190. 1 row in set (0.00 sec)
  191.  
  192. mysql> SELECT Name, Salary, DeptID  FROM Employees WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Post = 'Developer' OR Post = 'Sr. Developer');
  193. +--------+--------+--------+
  194. | Name   | Salary | DeptID |
  195. +--------+--------+--------+
  196. | Kalyan | 110000 |      1 |
  197. +--------+--------+--------+
  198. 1 row in set (0.01 sec)
  199.  
Advertisement
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!