Update in SQL Server

How to update in sql Server?

The update statement is used to update existing records in a table.

Syntax:-

UPDATE Table_name SET Column1=value1,column2=value2 WHERE someColumn=somevalue.

Example:-

UPDATE Employee SET Frst_name='Ram',Addres='Kolkata' WHERE EmpID ='1'

Note:-

If we cannot include the WHERE clause then all records will update.

Insert into Table

How to insert data in the sql server?

->Insert into statement is used to insert a new row in a table.

->It will write in two ways.

1)First Way Syntax:-

INSERT INTO Table_Name
VALUES(value1,value2,value3,...)

Example:-

INSERT INTO Table_Name
VALUES(1,'Er Vijay','Narayan Mishra','Kolkata')

2)Second Way Syntax:-

INSERT INTO Table_Name(column1,column2,column3,..)

VALUES(value1,value2,value3,...)

Example:-

INSERT INTO Table_Name(ID,First_name,Last_name)

VALUES(1,'Er Vijay','Narayan Mishra')

Unique Constraint in SQL Server

What are Unique constraints in SQL Server?

Unique constraints uniquely identifies each records in a database


Syntax

CREATE TABLE Table_name(
    column1 Datatype1 NOT NULL,
    column2 Datatype2  NOT NULL,
    column3 Datatype3  NOT NULL,
    column4 Datatype4 ,
UNIQUE(columnname));
Example

CREATE TABLE Table_name(
    EmpID Int NOT NULL,
    Emp_Name Varchar(200)  NOT NULL,
    Address Varchar(200)  NOT NULL,
    Age int,
UNIQUE(EmpId));

Not Null Constraint

What is Not Null constraints?

->In Sql Server by default column holds NULL values but when we add NOT NULL constraints It cannot hold NULL values.

->Not Null constraints enforce a column to not accept NULL values.

Syntax

CREATE TABLE Table_name(
    column1 Datatype1 NOT NULL,
    column2 Datatype2  NOT NULL,
    column3 Datatype3  NOT NULL,
    column4 Datatype4 
);

Example

CREATE TABLE Table_name(
    EmpID Int NOT NULL,
    Emp_Name Varchar(200)  NOT NULL,
    Address Varchar(200)  NOT NULL,
    Age int
);

How can I run a query in SQL?

How can I run a query in SQL?

Connecting to Your Database
Follow the procedure below to connect to your Vijay database.
1. Log in to a computer where SQL Server Management Studio is installed.
2. Launch SQL Management Studio from the Start menu.
3. Choose Database Engine as the Server type, choose the correct Server.

Step 1 Connecting to Your Database

 Name from the list or browse for the correct server.
If your user account has the rights to access the database, leave Authentication as Windows Authentication. Otherwise, switch it to SQL Server Authentication and enter the User name and Password for a SQL account with rights to access the database. Note: If you are not sure which account to use, you can confirm what’s being used by Vijay by opening the Vijay Control Panel.
4. Click Connect.
Running a Query
1)In the Object Explorer pane, expand the top-level Server node and then Databases.

Step 2 Select database

 2)Right-click your vCommander database and choose New Query.

3)Copy your query into the new query pane that opens.

Step 3 Select query

4)Click Execute

Step 4 Execute Query

What is the use of a schema in SQL Server?

What is the use of a schema in SQL Server?

Advantage of Schema:-

1. Act as object protection tool: A schema can be a very effective object projection tool combined with the appropriate level of user permissions. 
2. A DBA can maintain control access to an object that would be very crucial.
3. Easy to maintain the database.
4. A single schema can be shared among multiple databases and database users.
5. A database user can be dropped without dropping database objects.
6. Manipulation of and access to the object is now very complex and more secure. The schema acts as an additional layer of security.
7. Database objects can be moved among schemas.
8. The ownership of schemas is transferable

How do I display any string in SQL with a select query?

How do I Show any string in SQL with a pick query?


You can write a query in the double quotation.

Example:-

Select “ For additional concerning sql server visit SQL World here”

Output:-


For more about sql ser visit SQL World here

What does * mean in SQL?

What does * mean in SQL?


In Sql server * means it returns all the columns from the table.

Example:-

Let assume you have an Employee table and in this table, you have 1000 records. So, If you write query “SELECT * FROM EMPLOYEE “


It will return all the columns from the EMPLOYEE table.

What is the use of the MSDB database in SQL Server?

What is the use of the MSDB database in SQL Server?
The msdb database is used mainly by the SQL Server Agent to store system activities like sql server jobs, mail, service broker, maintenance plans, user and system database backup history, etc..It is also used by the database engine and management studio.

How can we write user-defined functions (UDF) in my SQL server?

----------------------------
How can we write user-defined functions (UDF) in my SQL server?

There are three types of user-defined functions(UDF) in SQL server
1)Scalar function
2)Inline function
3)Multi statement Table-valued function


1)Scalar function:-
->Scalar UDFs return a single value.
->Scalar UDFs built-in functions such as  GETDATE(),or OBJECT_NAME(), which returns single value,date or integer. The value returned by a Scalar UDF can be based on the parameters passed

Syntax:-

Create Functions  function-name(Parameters)
Returns returns-type
AS
BEGIN
      Statements 1
      Statements 2
      Statements 3
       .
       .
       Statements n
END

Example:-

Create Functions  GetEmployee(@DepartmentID int)
Returns varchar(50)
AS
BEGIN
     RETURN (SELECT Name FROM Employee WHERE       DepartmentID=@DepartmentID )
     END


2)Inline function:-
->Inline UDFs return single rows or multiple rows and can contain a single SELECT statement. Because in-line UDF is limited to a single SELECT statement.

Syntax:-

Create Functions  function-name(Parameters)
Returns returns-type
AS
BEGIN
     RETURN


Example:-

Create Functions  GetEmployee(@DepartmentID int)
Returns varchar(50)
AS
BEGIN
     RETURN 
SELECT Name FROM Employee WHERE  DepartmentID=@DepartmentID 

3)Multi statement Table-valued function:-
->Multi-statements UDF can contain any number of statements that populated the table variable to be returned.


Syntax:-

Create Functions  function-name(Parameters)
Returns @TableName TABLE
(
Column_1  Datatype,
Column_2  Datatype,
.
Column_n  Datatype
)
AS
BEGIN
Statements 1
                  Statements 2
                  Statements 3
                  .
                  .
                 Statements n
                 RETURN
END

Example:-


Create Functions  GETaverage(@Name varchar(50))
Returns  @Marks TABLE
(
Name varchar(50),
Class varchar(50),
Average Decimal(4,2)
)
AS
BEGIN
DECLARE @Avg DECIMAL(4,2)
DECLARE @Rno INT 
INSERT INTO @Marks (Name)VALUES(@Name)
SELECT @Rno=Rno FROM Student WHERE Name=@Name 
SELECT @Avg=(Subject1+Subject2+Subject3)/3 FROM Subjects WHERE Rno=@Rno

UPDATE @Marks SET
Subject1=(SELECT Subject1 FROM Subjects WHERE Rno=@Rno),
Subject2=(SELECT Subject2 FROM Subjects WHERE Rno=@Rno),
Subject3=(SELECT Subject3 FROM Subjects WHERE Rno=@Rno)
Average=@Avg
WHERE Name=@Name
                  
                 RETURN
END

What is Auto Increment in SQL?

What is an Auto Increment in SQL SERVER?

Auto Increment:-



SQL AUTO INCREMENT allows a unique number that is to be generated automatically whenever the new record is inserted into the table.

Syntax:-

Create Table Table_name
( 
EmpId int NOT NULL AUTO INCREMENT,
Employee_Name Varchar(200),
Employee_Address Varchar(200),
PRIMARY KEY (EmpId )
)