Tech Learn

Please Answer the all the questions in comments box Question 1: -- Is this Satement Excuted successfully? Please expalin the senario. CREATE TABLE EMP ( ID1 INT , ID2 INT , ID3 INT , ID4 INT , ID5 INT , ID6 INT , ID7 INT , ID8 INT , ID9 INT , ID10 INT , ID11 INT , ID12 INT , ID13 INT , ID14 INT , ID15 INT , ID16 INT , ID17 INT , CONSTRAINT PK_ID PRIMARY KEY ( ID1 , ID2 , ID3 , ID4 , ID5 , ID6 , ID7 , ID8 , ID9 , ID10 , ID11 , ID12 , ID13 , ID14 , ID15 , ID16 , ID17 ) ) Question 2: --Is this Satement Excuted successfully? Please expalin the senario. CREATE TABLE EMP ( NAME CHAR ( 2000 ) PRIMARY KEY ) Question 3: --Is this Satement Excuted successfully? Please expalin the senario. DROP TABLE EMP CREATE TABLE EMP ( NAME VARCHAR ( 2000 ) PRIMARY KEY ) Question 4: --Is this Satement Excuted successfully? Please expalin the senario. INSERT INTO EMP ( NAME ) VALUES ( REPLICATE ( ...

-- CREATE TRG_STUDENT TABLE CREATE TABLE TRG_STUDENT (                 Id INT IDENTITY ( 1 , 1 ),                 Name VARCHAR ( 50 ),                 Marks INT ) -- INSERT SAMPLE DATA TO TRG_STUDENT TABLE INSERT INTO TRG_STUDENT ( Name , Marks ) VALUES ( 'RAKESH' , 90 ) INSERT INTO TRG_STUDENT ( Name , Marks ) VALUES ( 'MADHU' , 70 ) INSERT INTO TRG_STUDENT ( Name , Marks ) VALUES ( 'NANI' , 60 ) -- CREATE SRC_STUDENT TABLE CREATE TABLE SRC_STUDENT (                 Id INT IDENTITY ( 1 , 1 ),                 Marks INT ) -- INSERT SAMPLE DATA TO SRC_STUDENT T...

-- HERE IS THE SIMPLE DEMONSTRATION CHECK CONSTRAINT WITH USER DEFINED FUNCTION. -- SENARIO : ONLY ONE STUDENT CAN JOIN THE AT A TIME . ALL STUDENTS CAN WAIT UNTILL THE JOINED STUDENT CAN INACTIVE. -- CREATE FUNCTION CREATE FUNCTION CHECK_StudentJoinSQLClass ( @JOINED BIT ) RETURNS BIT AS BEGIN IF ( @JOINED = 1 AND ( SELECT COUNT (*) FROM StudentJoinSQLClass WHERE   JOINED = @JOINED )> 1 ) BEGIN                   SET @JOINED = 0 --FALSE END ELSE BEGIN                 SET @JOINED = 1 --TRUE END RETURN @JOINED END -- CREATE TABLE   CREATE TABLE StudentJoinSQLClass (   ID INT IDENTITY ( 1 , 1 ),   NAME VARCHAR ( 10 ),   JOINED BIT   NOT NULL CHECK ( DBO . CHECK_StudentJoinSQLClass ( JOINED )= 1 ) ) --INSERT DATA INSE...

Concat function is used concatnating the values among the different arguments. Syntax: Concat([String1],[String2]..[StringN]);  DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT CONCAT ( @FName , ' ' , @LName ) as [FullName] ; Output: Rakesh Kalluri --Before SQL 2012 Versions we can use like this way DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT @FName + ' ' + @LName as [FullName] ; Output: Rakesh Kalluri --But Before SQL 2012 Versions there are 2 problems when we  contacting . --1.Need to replace NULL Value to Empty.if we directly concat NULL Value the  Entire  result also NULL. --Before 2012: DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SELECT @FName + ' ' + @LName a...

Step-1: Create Table CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) Step-2: Insert Some Sample Data INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' Step-3: If we need to add DOJ column to Student table with default constraint , But all the Existing rows want to update Default GETDATE() , if WITH VALUES are not used all existing rows DOJ column should be NULL. ALTER TABLE STUDENT ADD DOJ DATETIME NULL DEFAULT GETDATE () WITH VALUES Step-4: SELECT * FROM STUDENT

In Recent one of my project I need to change the column data type. At the same time I need to change the all object the table of column dependences. We find best solution best for SQL SERVER 2008 onwards this is known as View Dependences. Step -1: Create sample table. CREATE  TABLE  STUDENT ( ID  INT   IDENTITY (1,1)  PRIMARY   KEY , STUDENTNAME  VARCHAR (100) NOT NULL , LIVINGLOCATION  VARCHAR (100) NULL ) Step-2: Create some sample procedures (or) views (or) functions (or) triggers. CREATE  PROCEDURE  Get_Student AS BEGIN SELECT *  FROM  STUDENT END ----------------------------- CREATE  PROCEDURE  Get_StudentBy_ID (@ID  INT =NULL ) AS BEGIN SELECT *  FROM  STUDENT  WHERE  ID=@ID END ----------------------------- CREATE  VIEW  V_GetStudent AS SELECT *  FROM  ...

Stop the processing after the number of rows has been affected. Example: Step-1: Create the Table and insert some sample data. CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' In above Insert Statement we inserted 5 sample records. Step-2: Using SET ROWCOUT SET ROWCOUNT 3 ; SELECT * FROM STUDENT ORDER BY ID DESC It will affected 3 rows after that processing has been stopped. Step-2: Using SET ROWCOUT 0 SET ROWCOUNT 0 ; SELECT * FROM STUDENT ORDER BY ID DESC ...

Variables can be used to store the data in temporally based on data type. Variable name begin with @ symbol. There are two types of variables in T-SQL. 1.   Local Variables (It must begin @ symbol). 2.   Global Variables (it must begin @@ symbol) this variables also known as system variables. Syntax Declare a Variable DECLARE   @Variable-Name   DATATYPE EXAMPLE: DECLARE   @Name   VARCHAR ( 100 ) In before 2008 versions of SQL SERVER we declare a variable and assign a value in 2 lines.                 DECLARE   @Name   VARCHAR ( 100 ) SET   @Name = 'LeadFirstSQL' In 2008 and Later versions of SQL SERVER we declare a variable and assign a value in single line. DECLARE   @Name   VARCHAR ( 100 )   = 'LeadFirstSQL' Variables must be declare and used with the same batch. DECLARE   @Name   VARCHAR ( 100 ) ...