lab-sql-store-pro

SELECT *FROM employees
DECLARE @i VARCHAR(35)=(SELECT avg(salary) FROM employees)

IF @i<30000
    BEGIN
        UPDATE employees SET salary=salary+5000

    END
ELSE
    BEGIN
        UPDATE employees SET salary=salary-2000
        SELECT *FROM employees
    END




/* Data Identification */
IF EXISTS (SELECT *FROM employees)
    BEGIN
        print 'Record Available'
    END
ELSE
    BEGIN
        print 'Record Not Availble'
    END

/*while*/
DECLARE @a INT =1
while @a<=10
BEGIN
    print @a
    SET @a=@a+1
END




DECLARE @avgsalary VARCHAR(35)=(SELECT avg(salary) FROM employees)

        IF @avgsalary<30000
            BEGIN
                while @avgsalary<=80000
                BEGIN
                UPDATE employees SET salary=salary*salary*1.5
                print 'updated'
                SET @avgsalary=(SELECT avg(salary) FROM employees)
                END
            END
        ELSE
            BEGIN
                print 'not update'
            END



SELECT *FROM employees


/* Stored Procedure */
CREATE PROCEDURE E_details
@id VARCHAR (15),
@dept VARCHAR (20)
AS
BEGIN
    SELECT *FROM employees WHERE E_ID=@id OR
    Department=@dept
END

E_details @id ='E014', @dept='HR'
SP_helptext E_details
/* create a store procedure that will take empolyee dept as input and provides total number of employee output*/
CREATE PROCEDURE Total_emp
@dept VARCHAR(15),
@SUM INT output
AS
BEGIN
    SELECT @SUM=COUNT(E_ID) FROM employees
    WHERE Department =@dept
END
/*sum dept connot be declared*/
DECLARE @a INT
EXECUTE Total_emp 'HR' , @a output
SELECT @a AS Total_employee


DECLARE @a INT
EXECUTE Total_emp 'HR' , @a output
print 'Total number of Employee is '+
CAST(@a AS VARCHAR(25))

/**/
CREATE PROCEDURE Emp_avg2
@dept VARCHAR(15)AS
BEGIN
    DECLARE @avgsalary INT
    SELECT @avgsalary=AVG(Salary) FROM employees
    WHERE Department =@dept
    IF @avgsalary<50000
    BEGIN
    UPDATE employees SET Salary=Salary+5000 WHERE Department =@dept
    END
    SELECT *FROM employees WHERE Department =@dept
END
SELECT *FROM employees WHERE Department ='IT'

Emp_avg2 'IT'


CREATE PROCEDURE withReturn
AS
BEGIN
    RETURN (SELECT COUNT(E_ID) FROM employees)


END

DECLARE @i INT
EXECUTE @i =withReturn
SELECT @i AS Total