Basic

Question 1

How to declare SQL variable?

Answer 1

Declare @temp varchar(100) = ‘’;

Question 2

How to get day name when date is passed as parameter

Answer 2

DateName(dw, Order date) => returns name of the day

Question 3

Get number of days between two dates

Answer 3

Select Datediff(dd,OrderDate, ship date) +1 from table

Question 4

What is normalization

Answer 4

Normalization is a design technique used to remove the redundant data

Question 5

What is 1st normal form

Answer 5

The columns of the table should not have separated values. It should be atomic value

Question 6

What is 2nd NF

Answer 6

1st NF is satisfied and all non primary key columns should be fully dependent on the primary key.

Question 7

Why use views in SQL

Answer 7

Reusability of complex queries.
Security by exposing only required columns of sensitive table data

Question 8

What stored procs can do

Answer 8

They can return results, manipulate data

Question 9

Advantages of using stored procs

Answer 9

Reusability, improve performance by caching, improve security

Question 10

What is CTE

Answer 10

CTE is a named query and to be used in subsequent select statement

Question 11

What funtions in SQL return

Answer 11

They return a scalar value

Question 12

What are SQL Triggers

Answer 12

These are special kinds of stored procedures, which react to certain actions we make in the database (perform actions when some event happens)

Question 13

Where and having

Answer 13

Where introduces a condition on individual rows
Having introduces a condition on aggregation formed by group by

Question 14

Temporary table

Answer 14

They are created in TempDB and are automatically deleted as soon as last connection is terminated.

Question 15

Temporary table syntax

Answer 15

Create table #EmpDetails (id INT, name VARCHAR(25))

Insert into #EmpDetails values (01,’LALIT’)

Question 16

Clustered index

Answer 16

Determines the physical order of the data in the table. Hence we have only one clustered index.by default this is enabled on primary key of table

Question 17

What is order by 1 do?

Answer 17

It sorts the results based on the values in column1

Question 18

What union do

Answer 18

Union operator combines result sets of two or more select queries into single result set. It returns only distinct rowa

Question 19

What is getdate() does?

Answer 19

It returns the current date and time

Question 20

What is convert() used ?

Answer 20

Convert() is used to convert from one data type to another type

Question 21

Convert(Date, getdate())

Answer 21

It is used to convert current date and time returned by getdate() to just DATE component

Question 22

Datediff(date part, start_date, end_date) does ?

Answer 22

It is used to calculate the difference
Based on the date part which can be years, months, days, hours, minutes,seconds

Question 23

Nolock

Answer 23

It is used to specify that a query should not acquire shared locks on the data it is reading

Question 24

Non correlated sub query

Answer 24

It may be executed alone and does not refer anything from the containing statement

Question 25

Correlated sub query

Answer 25

It is dependent on the containing statement from which it references one or more columns

Question 26

Top key word

Answer 26

Top clause is used to limit the number of rows returned by query. It is used in SELECT query

Question 27

In operator

Answer 27

It is used to check if a value exists in the set of values.

Question 28

Not in operator

Answer 28

It is used to check if value does not exist in a set of values

Question 29

All operator

Answer 29

It is used to check if the value is satisfied for every value in a set of values. It is generally used in conjunction with a comparison operator like <>all

Question 30

Any operator

Answer 30

It is like all operator but it evaluates to true even if a single value is satisfied in a set of values. It is used in conjunction with comparison operators

Question 31

Set Nocount On

Answer 31

This command is used to supress the "no of rows affected" message. It can be used in the scenarios to improve the performance of application

Question 32

Rtrim()

Answer 32

It is used to remove trailing spaces(spaces at the end) from a string.

Question 33

Isnull (expression, replacement_value)

Answer 33

This is used to replace NULL values(when expression is evaluated)with a specified replacement value.

Question 34

Object_id()

Answer 34

It is used to retrieve the database object identification number of a specified database object.it takes database object as input . If the object exists then it returns object_id or else it returns NULL

Question 35

rank()

Answer 35

this function assigns a unique value to each distinct row. but it leaves value gaps between groups

Question 36

dense_rank()

Answer 36

this is same as rank() but does not leave gaps.

Question 37

extract year,month,day seperately from Date

Answer 37

YEAR(date_column), MONTH(date_column),DAY(date_column)

Question 38

syntax to create a view

Answer 38

CREATE VIEW [Description] AS SELECT C.C_Name, P.P_Name, P.P_ID, P.P_Price, P.P_MAN_DATE, P.P_EXP_DATE FROM Customer C, Products P ;

Question 39

syntax to read from a view

Answer 39

SELECT * FROM [Description];

Question 40

syntax to update a view

Answer 40

CREATE OR REPLACE VIEW Description AS SELECT C.C_Name, P.P_Name, P.P_ID, P.Price, P.P_MAN_DATE, P.P_EXP_DATE, C_NO FROM Customer C, Products P;

Question 41

delete a view

Answer 41

DROP VIEW [Description];

Question 42

how to delete a column from a table

Answer 42

alter table [dbo].[halls]drop column seatId

Question 43

drop a constriant

Answer 43

ALTER TABLE DROP CONSTRAINT