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 )
)

What is an inner join in SQL?

What is an inner join in SQL?

Inner Join:-

It returns only matching rows from both the tables.

Syntax:-

Select a.column1,a.column2,b.column1,b.column2

from table1 as a INNER JOIN table2 as b

on a.Common_field = b.Common_field

Example:-

SELECT a.OrderID, b.CustomerName
FROM Orders as a
INNER JOIN Customers as b

ON a.CustomerID = b.CustomerID;

What are the functions of the MS SQL Server database?

What are the functions of the MS SQL Server database?

->Function is a database object in the SQL server. Basically, it is a set of sql Settlement that accepts only input parameter, performs and returns the result.

->Function can return only a single value or label.

->We can’t use the function to insert, update, delete records in the database table.

Function Types:-

1)Scalar Function:-

The function perform calculation on a input value and return a single value.

example:-

Round(),MID(),LCASE(),UCASE() etc.

2)Aggregate Function:-

Aggregate function operator on a collection of values and returns single value.

i)MAX():-

Select Max(Couloumn_name) as Alias name from table

ii)MIN():-

Select MIN(Couloumn_name) as Alias name from table

iii)AVG():-

Select AVG(Couloumn_name) as Alias name from table

iv)COUNT:-

Select Count(Couloumn_name) as Alias name from table

v)SUM:-

Select SUM(Couloumn_name) as Alias name from table

vi)LOWER():-

Select LOWER(Couloumn_name) as Alias name from table

vii)UPPER:-

Select UPPER(Couloumn_name) as Alias name from table

viii)LTRIM():-

Select LTRIM(‘ vijay’)

ix)RTRIM():-

Select RTRIM(‘ vijay’)

x)Length Aggregate Fucnction:-

Select Coloumn_name, LEN(Couloumn_name) as Alias name from table where id=’1′

How do I create a new table with another column from existing 2 tables where values be filled according to certain conditions?

How do I create a new table with another column from existing 2 tables where values be filled according to certain conditions?

The syntax for creating a new table with another column

1)create table by copy all column from antother table

syntax:-

create Table New_tableName

as

select column1,column2,column3 from existing table.

2)create table by copy selected column from another table

syntax:-

create Table New_tableName as

select column1,column2,column3 from oldtable1,oldtable2,oldtable3

example:-

create Table Employee as

select Company.CID,,Company.Address,Department.Depat from Company,Department

more

What is the SQL left join keyword?

LEFT JOIN IN SQL SERVER

Left Join:- It returns all records from the left table and matching records from the right table.

Syntax:-

Select a.Coloumn1,a.Coloumn2,b.Coloumn1,b.Coloumn2

from Table1 as a LEFT JOIN Table2 as b

Where a.CommonField= b.CommonField

Example:-

Select a.Name,a.Department,b.CompanyName,b.Degination

from Employee as a LEFT JOIN Company as b

Where a.EmplyeeId= b.CompanyID

What is the max size of varchar in SQL Server?

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

Varchar(n):-

1)Non-Unicode Variable Length character data type.

2)It can store a maximum of 8000 Non-Unicode characters (i.e. maximum storage capacity is 8000 bytes of storage). Optional Parameter n value can be from 1 to 8000.

3)It takes 1 byte per character.

Varchar(max):-

1)Non-Unicode large Variable Length character data type.

2)It can store a maximum of 2 147 483 647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB).

3)It takes 1 byte per character

What is Semijoin in SQL?

What IS Semi Join IN SQL SERVER?

A semi-join between two tables returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

Example:-

Let assume we have two table table1(Employee) and table2 (Department).

now table 1 has 3 fields Empid, EmpName, Department and table 2 has 2 field DepartmentName, Manager.

SELECT * FROM Employee WHERE EXISTS (

SELECT 1

FROM Dept

WHERE Employee.DeptName = Dept.DeptName

)




more