Delete In SQL Server

What is Delete Statement in SQL Server?

Delete :-

Delete statement is used for Delets row in a table.

Syntax:-

Delete from Table_name 

Where some_column = some_value

Example:-

Delete from Student

where StudentId ='2'

Note :-

If we cannot used "where" clause in delete statement then it can delete all the records from table.

SQL SELECT Statment

What is SELECT Query ?


SELECT:-
The SELECT statement is probably the most used statement n SQL command.

Syntax:-

Select * from Table_name

Example:-

Select * from Student

Case:-  If we want to show only few column then we write this query.

Syntax:-

Select  columnname1,columnname2,columnname3 from table_name

Example:-

Select StudentId,StudentName,RollNumber From Student

Order by keyword in SQl SERVER

What Is Order By Keyword in SQl SERVER ? How To use?

Order by :-

 

-The Order by keyword is used to sort the result according to user choice ascending order or descending order.

-By default order by show ascending order  result.

Syntax:-

Select  * from Table_name

order by columnname ASC/DESC

CASE:

1) By Ascending Order:- The following SQL statement selects all Student from the "student" table, sorted ASCENDING by the "Name" column:

Example:-

Select * from Student

Order by Name ASC

2)By Descending Order:- The following SQL statement selects all Student from the "student" table, sorted DESCENDING by the "Name" column

Example:

Select * from Student

Order by Name DESC

3)Show Some Column:- The following SQL statement selects some column from the "student" table, sorted ASCENDING by the "Name" column:

Example:-

Select Name, City From Student 

Order by Name ASC

4)Order by Mutiple Column:- The following SQL statement selects all Student from the "student" table, sorted ASCENDING by the "Name" column and DESCENDING by the "City" Column:

Example:-

Select * From Student 

Order by Name ASC , City DESC

List of 90 Sql Server Interview Question and Answer

List of 90 SQL Server Interview Questions and Answers

Q #1) What is SQL?

Answer: Structured Query Language SQL is a database tool that is used to create and access the database to support software applications.

Q #2) What are tables in SQL?

Answer: The table is a collection of records and information in a single view.

Q #3) What are the different types of statements supported by SQL?

Answer:

statements supported by SQL

There are 3 types of SQL statements:

a) DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as CREATE, ALTER, and DROP.

Some of the DDL Commands are listed below:

CREATE: It is used for creating the table.

CREATE TABLE table_name

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size),

ALTER: The ALTER table is used for modifying the existing table object in the database.

ALTER TABLE table_name

 ADD column_name datatype

OR

ALTER TABLE table_name

DROP COLUMN column_name

b) DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are INSERT, UPDATE, and DELETE.

The SELECT statement is used as a partial DML statement, used to select all or relevant records in the table.

c) DCL (Data Control Language): These statements are used to set privileges such as GRANT and REVOKE database access permission to the specific user.

Q #4) How do we use the DISTINCT statement? What is its use?

Answer: The DISTINCT statement is used with the SELECT statement. If the record contains duplicate values then the DISTINCT statement is used to select different values among duplicate records.

Syntax:

SELECT DISTINCT column_name(s)

 FROM table_name;

Q #5) What are different Clauses used in SQL?

Answer:

Clauses used in SQL

WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill the given condition.

Syntax:

SELECT column_name(s)

 FROM table_name

 WHERE condition;

GROUP BY Clause: It is used with a SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.

Syntax:

SELECT column_name(s)

 FROM table_name

 GROUP BY column_name;

HAVING clause: This clause is used in association with the GROUP BY clause. It is applied to each group of results or the entire result as a single group. It is much similar to the WHERE clause but the only difference is you cannot use it without the GROUP BY clause

Syntax:

SELECT column_name(s)

 FROM table_name

 GROUP BY column_name

 HAVING condition;

ORDER BY clause: This clause is used to define the order of the query output either in ascending (ASC) or descending (DESC). Ascending (ASC) is set as the default one but descending (DESC) is set explicitly.

Syntax:

SELECT column_name(s)

 FROM table_name

 WHERE condition

 ORDER BY column_name ASC|DESC;

USING clause: USING clause comes in use while working with SQL JOIN. It is used to check equality based on columns when tables are joined. It can be used instead of the ON clause in JOIN.

Syntax:

SELECT column_name(s)

 FROM table_name

 JOIN table_name

 USING (column_name);

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

Foreign key in SQL server

What is foreign key SQL SERVER constraint?

->A Foreign key is used to link two tables.

->Foreign key is a one table point to another table.

->Foreign key can accept multiple NULL values.

->We can have more than one Foreign key in a table.

How to create Foreign Key in SQL SERVER by Query?

 CREATE TABLE Employee(

    EmpId int NOT NULL PRIMARY KEY identity(1,1),

    EmpName Varchar(200),

    Department varchar(200),

    CompanyId int FOREIGN KEY REFERENCES Company (CId)

);

where:-

 Employee - Table name 2

Company - Table Name 1

CId - Table 1 Column name(Primary key)

CompanyId - table 2 Column name(Foreign Key)

How to set Foreign key in SQL SERVER For Existing Table Made Simple.

Foreign Key

For More Sql Server Constraints

Click Here

Primary Key

What is a Primary Key?

->The Primary key constraints uniquely identify each record in a database.

->It contains a unique value.

->A Primary key column cannot be a NULL value.

Syntax

create table table_name(Id int identifier Primary Key, Name varchar(200), Roll_number int)

Types of SQL Constraints

How many types of SQL Constraints?

There are basically 6 types of SQL Constraints.

1)PRIMARY KEY

2)FOREIGN KEY

3)NOT NULL

4)UNIQUE

5)CHECK

6)DEFAULT

Constraints in SQL Server?

What Are SQL constraints?

->SQL constraints are the rule which is applied

 to the table column to store valid data and 

prevents the user from storing invalid data 

into table columns.

->SQL Constraints are part of a database 

schema definition.

->We can create constraints on single or 

multiple columns of any table.

Command in SQL Server?

Types of command in SQL Server?

1) Data Definition Language(DDL)

Data definition statements are used to define the database structure or table.

(i)CREATE :- Create new database/table
(ii)ALTER :- Modifies the structure of database / table.
(iii)DROP:- Deletes a database / table.
(iv) TRUNCATE:- Remove all the records from the table.
(v)RENAME:-Rename the database/table.

2) Data Manipulating Language(DML)

Data manipulating statements are used for managing data within the table.

(i)SELECT:- Retrieve data from a table.
(ii)INSERT: -Insert data into table.
(iii)UPDATE:-Update/Modify existing records in a table.
(iv)DELETE:- Delete the records from the table.

3)Data Control Language.


(i)GRANT:- Giver user access privileges database.
(ii)REVOKE: -Withdraw user access privileges given by using the GRANT command.


4)Transaction Control Language(TCL)

TCL commands deal with the transaction within the database.

(i)COMMIT: -commits a transaction.
(ii)ROLLBACK:-rollbacks a transaction in case of any error occurs.
(iii)SAVE-POINT:- sets a savepoint within a transaction.
(iv)SET TRANSACTION:- specify characteristics for the transaction.

5)Data Query Language

(i)SELECT:- It is used to retrieve data from the database.



Image Source: Google

How to Create Table in SQL SERVER?

How to Create a Table?


There are two ways to create Table

(i) With SQL Command

(ii)Without SQL Command

So, let's go with First way

(i) Create Table using SQL Command:-

Syntax:- 

Create Table table_name(Column_name1 datatype,column_name2 datatype,....)

Example:-

Create table Employee(Employee_Id Int,Employee_Name varchar(100), Employee_Department varchar(200) )

(ii) Without SQL Command:-

Step 1:-Right click on the table. Select New then select Table.

Step 2:- Enter Column name and datatype

Step 3:- Then Save the table.

Step 4:- Click OK.