SQL -----------
========================================================

TSQL PROGRAMMING,,,

VARIABLE----VARIABLES ARE USED TO STORE THE DATA TEMPORARILY FOR LATER USE IN THE 
            SAME BATCH IN WHICH THEY ARE DECLARED.

           BATCH IS ONE OR MORE T-SQL STATEMENT  SENT BY A CLIENT APPLICATION
           TO SQL SERVER FOR EXECUTION AS ONE UNIT



java----backend sql
.net



 SYNTAX:
DECLARE @varname AS DATATYPE(SIZE) 
OR
DECLARE @varname DATATYPE(SIZE)          

CORRECT----WILL GIVE NULL VALUES WE HAVE NOT ASSIGN ANY VALUE TO VARIABLE
DECLARE @x as int;-----------------------
SET @X ='A'
SELECT @x as x;
GO

x
NULL

=============
BATCH WILL START
DECLARE @x as int;
GO   --------------WHENEVER YOU SPEFICIFY GO AT THAT TIME BATCH WILL END OVER HERE
SELECT @x as x;


Msg 137, Level 15, State 2, Line 38
Must declare the scalar variable "@x".


ITS SCOPE

DECLARING VARIABLES ONE AT A TIME:
DECLARE @x as int;
DECLARE @y as varchar(10);
DECLARE @z as date;

DECLARING MULTIPLE VARIABLES AT A TIME:

DECLARE @x as int, @y as varchar(10),@z as date;
SET @x = 23;
SET @y = 'hello'
SET @z = '2022-11-10'
SELECT @X,@Y,@Z

=================================
DECLARE @x as int =23 , @y as varchar(10) ='HARSHIT' ,@z as date ='2022-11-10';
SELECT @X,@Y,@Z



SET STATEMENT:
=> IT IS USED TO ASSIGN VALUE TO A VARIABLE
=> ONLY ONE VARIABLE IS ALLOWED AT A TIME

-- TO ASSIGN THE VALUES TO VARIABLES
SET @x = 23;
SET @y = 'hello'
SET @z = '2022-11-10'

=> INITIALIZING THE VARIABLE WHILE DECLARING THE VARIABLE

DECLARE @W AS INT = 10;

--==============================================
--WE CAN ALSO ASSIGN THE VALUES TO VARIABLES USING SELECT STATEMENT(ASSIGNMENT SELECT)

SELECT ENAME,JOB,SAL FROM EMP1 WHERE EMPNO = 7839;

DECLARE @EMPNAME AS VARCHAR(20);
DECLARE @JOB AS VARCHAR(20);
DECLARE @SALARY AS MONEY;

SELECT @EMPNAME = ENAME,@JOB = JOB,@SALARY=SAL FROM EMP1 WHERE EMPNO = 7839;
SELECT @EMPNAME,@JOB,@SALARY;
GO

===================================================================================================================
--IF MULTIPLE ROWS QUALIFY, THEN WHEN ASSIGNMENT SELECT IS FINISHED, THE VALUES IN THE 
--VARIABLES ARE THOSE FROM THE LAST ROW THE SQL SERVER HAPPENED TO ACCESS

SELECT ENAME,JOB,SAL FROM EMP1 WHERE DEPT =10;


DECLARE @EMPNAME AS VARCHAR(20);
DECLARE @JOB AS VARCHAR(20);
DECLARE @SALARY AS MONEY;

SELECT @EMPNAME = ENAME FROM EMP1 WHERE DEPT = 10;
SELECT @JOB = JOB FROM EMP1 WHERE DEPT = 10;
SELECT @SALARY=SAL FROM EMP1 WHERE DEPT = 10;
SELECT @EMPNAME,@JOB,@SALARY;
GO

-------------------------------------------------------------------------------------------------------------------
---------------------------BELOW BLOCK OF SQL STATEMENTS WILL GENERATES ERROR
-------------------------------------------------------------------------------------------------------------------
DECLARE @EMPNAME AS VARCHAR(20);
DECLARE @JOB AS VARCHAR(20);
DECLARE @SALARY AS MONEY;

SET @EMPNAME = (SELECT ENAME FROM EMP1 WHERE DEPT =10);
SET @JOB = (SELECT JOB FROM EMP1 WHERE DEPT =10);
SET @SALARY = (SELECT SAL FROM EMP1 WHERE DEPT =10);

SELECT @EMPNAME,@JOB,@SALARY;
GO

-------------------------------------------------------------------------------------------------------------------
Msg 512, Level 16, State 1, Line 116
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 117
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 118
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row affected)

Completion time: 2024-09-06T10:19:50.0251079+05:30

-------------------------------------------------------------------------------------------------------------------

===================================================================================================================
SELECT SAL FROM EMP1 WHERE EMPNO=7839


DECLARE @SAL MONEY;
SET @SAL = (SELECT SAL FROM EMP1 WHERE EMPNO=7839);
SELECT @SAL AS SALARY;

==================================

DECLARE @EMPNAME AS VARCHAR(20),
DECLARE @JOB AS VARCHAR(20)
DECLARE @SALARY AS MONEY


SET @EMPNAME = (SELECT ENAME FROM EMP1 WHERE EMPNO =7839)
SET @JOB = (SELECT JOB FROM EMP1 WHERE EMPNO =7839)
SET @SALARY = (SELECT SAL FROM EMP1 WHERE EMPNO =7839)
SELECT @EMPNAME,@JOB,@SALARY

===========================================

PRINT STATEMENT:
==============
IT IS USED TO RETURN A USER DEFINED MESSAGE TO THE CLIENT

PRINT 'HELLO'

HELLO

Completion time: 2024-09-06T10:25:09.0658577+05:30

SELECT GETDATE()
2024-09-06 10:25:39.043

PRINT GETDATE()
Sep  6 2024 10:25AM

Completion time: 2024-09-06T10:25:53.3712844+05:30


============================================================
1. WRITE A PROGRAM TO ADD TWO NUMBERS

DECLARE @a int, @b int, @c int;
set @a = 100;
set @b = 200;
set @c = @a+@b;
--print @c;
SELECT @C

--300

--Completion time: 2024-09-06T10:29:35.1547298+05:30



2. WRITE A PROGRAM TO TAKE INPUT DATE AND PRINT DAY OF THE WEEK
DECLARE @D DATE;---------------------
SET @D = '1947-08-15'
PRINT DATENAME(DW,@D);

--Friday
--Completion time: 2024-09-06T10:31:15.0897665+05:30

EMPNO
NAME
SALARY

3. WRITE A PROGRAM TO TAKE EMPNO AND PRINT CORRESPONDING NAME AND SALARY FROM EMP1

DECLARE @ENO INT, @ENME VARCHAR(20),@SAL INT;
SET @ENO = 7839;
SET @ENME = (SELECT ENAME FROM EMP1 WHERE EMPNO = @ENO);
SET @SAL = (SELECT SAL FROM EMP1 WHERE EMPNO = @ENO);
--PRINT @ENME + ' ' + @SAL----------------ERROR
PRINT @ENME + ' '+ CAST(@SAL AS VARCHAR);


KING 5000.00


===========================================================================
1.EMPNO,
2 HIREDATE FOR THAT PURPOSE ALSO WE NEED TO DECLARE ONE MORE VARIABLE
3. DISPLAY EXPERIENCE 

4 . WRITE A PROGRAM TO INPUT EMPNO AND PRINT EMPLOYEE EXPERIENCE IN EMP1 TABLE

DECLARE @EMPNO INT, @DOJ DATE, @EXP INT;
SET @EMPNO = 7839
SELECT @DOJ=HIREDATE FROM EMP1 WHERE EMPNO = @EMPNO
SET @EXP = DATEDIFF(YEAR,@DOJ,GETDATE())
PRINT 'EXPERIENCE = ' + CAST(@EXP AS VARCHAR) + ' YEARS';

EXPERIENCE = 43 YEARS

====================================================================================