Basic Flashcards
How to declare SQL variable?
Declare @temp varchar(100) = ‘’;
How to get day name when date is passed as parameter
DateName(dw, Order date) => returns name of the day
Get number of days between two dates
Select Datediff(dd,OrderDate, ship date) +1 from table
What is normalization
Normalization is a design technique used to remove the redundant data
What is 1st normal form
The columns of the table should not have separated values. It should be atomic value
What is 2nd NF
1st NF is satisfied and all non primary key columns should be fully dependent on the primary key.
Why use views in SQL
Reusability of complex queries.
Security by exposing only required columns of sensitive table data
What stored procs can do
They can return results, manipulate data
Advantages of using stored procs
Reusability, improve performance by caching, improve security
What is CTE
CTE is a named query and to be used in subsequent select statement
What funtions in SQL return
They return a scalar value
What are SQL Triggers
These are special kinds of stored procedures, which react to certain actions we make in the database (perform actions when some event happens)
Where and having
Where introduces a condition on individual rows
Having introduces a condition on aggregation formed by group by
Temporary table
They are created in TempDB and are automatically deleted as soon as last connection is terminated.
Temporary table syntax
Create table #EmpDetails (id INT, name VARCHAR(25))
Insert into #EmpDetails values (01,’LALIT’)
Clustered index
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
What is order by 1 do?
It sorts the results based on the values in column1