SHARE
TWEET

lab-sql-store-pro

a guest Jul 21st, 2019 80 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT *FROM employees
  2. DECLARE @i VARCHAR(35)=(SELECT avg(salary) FROM employees)
  3.  
  4. IF @i<30000
  5.     BEGIN
  6.         UPDATE employees SET salary=salary+5000
  7.        
  8.     END
  9. ELSE
  10.     BEGIN
  11.         UPDATE employees SET salary=salary-2000
  12.         SELECT *FROM employees
  13.     END
  14.  
  15.  
  16.  
  17.  
  18. /* Data Identification */
  19. IF EXISTS (SELECT *FROM employees)
  20.     BEGIN
  21.         print 'Record Available'
  22.     END
  23. ELSE
  24.     BEGIN
  25.         print 'Record Not Availble'
  26.     END
  27.  
  28. /*while*/
  29. DECLARE @a INT =1
  30. while @a<=10
  31. BEGIN
  32.     print @a
  33.     SET @a=@a+1
  34. END
  35.  
  36.  
  37.  
  38.  
  39. DECLARE @avgsalary VARCHAR(35)=(SELECT avg(salary) FROM employees)
  40.  
  41.         IF @avgsalary<30000
  42.             BEGIN
  43.                 while @avgsalary<=80000
  44.                 BEGIN
  45.                 UPDATE employees SET salary=salary*salary*1.5
  46.                 print 'updated'
  47.                 SET @avgsalary=(SELECT avg(salary) FROM employees)
  48.                 END
  49.             END
  50.         ELSE
  51.             BEGIN
  52.                 print 'not update'
  53.             END
  54.        
  55.  
  56.  
  57. SELECT *FROM employees
  58.  
  59.  
  60. /* Stored Procedure */
  61. CREATE PROCEDURE E_details
  62. @id VARCHAR (15),
  63. @dept VARCHAR (20)
  64. AS
  65. BEGIN
  66.     SELECT *FROM employees WHERE E_ID=@id OR
  67.     Department=@dept
  68. END
  69.  
  70. E_details @id ='E014', @dept='HR'
  71. SP_helptext E_details
  72. /* create a store procedure that will take empolyee dept as input and provides total number of employee output*/
  73. CREATE PROCEDURE Total_emp
  74. @dept VARCHAR(15),
  75. @SUM INT output
  76. AS
  77. BEGIN
  78.     SELECT @SUM=COUNT(E_ID) FROM employees
  79.     WHERE Department =@dept
  80. END
  81. /*sum dept connot be declared*/
  82. DECLARE @a INT
  83. EXECUTE Total_emp 'HR' , @a output
  84. SELECT @a AS Total_employee
  85.  
  86.  
  87. DECLARE @a INT
  88. EXECUTE Total_emp 'HR' , @a output
  89. print 'Total number of Employee is '+
  90. CAST(@a AS VARCHAR(25))
  91.  
  92. /**/
  93. CREATE PROCEDURE Emp_avg2
  94. @dept VARCHAR(15)AS
  95. BEGIN
  96.     DECLARE @avgsalary INT
  97.     SELECT @avgsalary=AVG(Salary) FROM employees
  98.     WHERE Department =@dept
  99.     IF @avgsalary<50000
  100.     BEGIN
  101.     UPDATE employees SET Salary=Salary+5000 WHERE Department =@dept
  102.     END
  103.     SELECT *FROM employees WHERE Department =@dept
  104. END
  105. SELECT *FROM employees WHERE Department ='IT'
  106.  
  107. Emp_avg2 'IT'
  108.  
  109.  
  110. CREATE PROCEDURE withReturn
  111. AS
  112. BEGIN
  113.     RETURN (SELECT COUNT(E_ID) FROM employees)
  114.  
  115.  
  116. END
  117.  
  118. DECLARE @i INT
  119. EXECUTE @i =withReturn
  120. SELECT @i AS Total
RAW Paste Data
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
 
Top