Sql basic queries (Create table, relations ships, insert, update, and delete, select)

=>Create Table

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

CREATE TABLE Product

(

Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

ProductName VARCHAR(500)

)

CREATE TABLE Orders

(

Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

OrderName VARCHAR(100),

ProductId int,

FOREIGN KEY(ProductId) REFERENCES Product(Id)

)

=>Insert data into table

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

INSERT INTO Product(ProductName) VALUES('PRODUCT 1'),('PRODUCT 2')

INSERT INTO Orders(OrderName,ProductId) VALUES('ORDER 1',1)

INSERT INTO Orders(OrderName,ProductId) VALUES('ORDER 2',2)

=>Update Records

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

UPDATE Orders

SET OrderName='ORDER 3'

WHERE Id=2

=>Delete

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

DELETE FROM Orders

WHERE Id=1

=>Select

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

SELECT OrderName

FROM Orders

SELECT Id,OrderName

FROM Orders

SELECT *

FROM Orders

SELECT DISTINCT OrderName,Id

FROM Orders

SELECT TOP 2 OrderName

 

FROM Orders 

=>update table(Add new column)

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


ALTER TABLE HospitalInfo
ADD IsHealthCheckPaymentEnable bit NOT NULL
CONSTRAINT IsHealthCheckPaymentEnable_Default DEFAULT 0
 

How to get data between start from specific row and specific no of records in sql.

 =>Like MSSQL NO OFFSET and FETCH NEXT key words in SQL Server. So if we want to get data start from specific row we need to use Derived table.

Ex.

SELECT keyname

FROM

(

SELECT keyname,ROW_NUMBER() OVER (ORDER BY Id) AS rowno FROM CONFIG_ApplicationConfigValues

) AS T

WHERE T.rowno between 11 and 20

Read data in sql row by row and perform actions per each record using curssor or while

=>We can repeat data in sql server and perform actions records by records.

=> Two main ways we can do (1) using cursor (2) While

=> The best way is using while insted of cursor. if we use cursor it user some extra resources like closing the cursor and deallocate the  cursor so it increase the query execution plan.

Ex. Cursor
==============================================================
DECLARE @ItemId INT,@PanelId INT,@LabelText VARCHAR(1000),@MinId int

DECLARE MYCURSOR CURSOR
FOR SELECT  ItemId,PanelId,LabelText FROM config_panelxitems

OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @ItemId,@PanelId,@LabelText

WHILE @@FETCH_STATUS=0
BEGIN

PRINT @LabelText

FETCH NEXT FROM MYCURSOR INTO @ItemId,@PanelId,@LabelText
END

CLOSE MYCURSOR
DEALLOCATE MYCURSOR

Ex. While
==============================================================

DECLARE @ItemId INT,@PanelId INT,@LabelText VARCHAR(1000),@MinId int

SET @MinId=(select min(ItemId) from config_panelxitems)

WHILE @MinId IS NOT NULL
BEGIN

 SELECT @LabelText=LabelText FROM config_panelxitems WHERE ItemId=@MinId

 PRINT @LabelText
 SET @MinId=(select min(ItemId) from config_panelxitems WHERE ItemId>@MinId)
END