Wednesday, October 26, 2022

How Components Communicate With Each Other In Angular

Components are the building blocks of Angular. So we need to understand how components communicate with each other.

There are three ways:

parent to child - sharing data via input
child to parent - sharing data via viewChild with AfterViewInit
child to parent - sharing data via output and EventEmitter

How Components Communicate With Each Other In Angular

Parent to child - sharing data via input

To share data from parent component to child component we use@input decorator.

Let’s configure the parent component and child component,

import { Component } from '@angular/core';

@Component({

selector: 'app-parent',

styleUrls: ['./parent.component.css']

})

export class ParentComponent {

message = “I am a parent"

constructor() { }

}

If you see the above code, <app-child> is used as a directive in the parent component. we are using property binding to bind the properties between child and parent, Binding child’s childMessage property with message property from the parent component.

import { Component, Input } from '@angular/core';

@Component({

selector: 'app-child',

styleUrls: ['./child.component.css']

})

export class ChildComponent {

@Input() childMessage: string;

constructor() { }

}

To use@input decorative, we have to first import it from @angular/core . Next will decorate childMessage with @input decorative with type string. Lastly, display the message from the parent into the child template using string interpolation.

Child to parent - sharing data via ViewChild and AfterViewInit

It allows the child component to be injected into parent competent so that the parent component gets access to its child’s attributes and functions. We must note that parents will get access only after the view is initialized.

Let’s configure the parent component and child component

import { Component, ViewChild, AfterViewInit } from '@angular/core';

import { ChildComponent } from "../child/child.component";

@Component({

selector: 'app-parent',

Message: {{ message }}

<app-child></app-child>

styleUrls: ['./parent.component.css']

})

export class ParentComponent implements AfterViewInit {

@ViewChild(ChildComponent) child;

constructor() { }

Message:string;

ngAfterViewInit() {

this.message = this.child.message

}

JavaScript

import { Component} from '@angular/core';

@Component({

selector: 'app-child',

styleUrls: ['./child.component.css']

})

export class ChildComponent {

message = 'Hello Angular!';

constructor() { }

}

We have to import ViewChild,AfterViewInit from angular/core, and the child component and use AfterViewInit life cycle hook so that we get access to the attributes in the child component. In the child component, we are passing the value for the message.

Child to parent - sharing data via output and EventEmitter

You use this approach when you want to share the data on button clicks or other events. The child component uses @output to send the data to the parent by creating an event with the help of an EventEmitter which is imported from @angular/core.

Let’s configure the parent component and child component.

import { Component, Output, EventEmitter } from '@angular/core';

@Component({

selector: 'app-child',

<button (click)="message()">Send Message</button>

styleUrls: ['./child.component.css']

})

export class ChildComponent {

message: string = "Hey I am child !"

@Output() messageEvent = new EventEmitter<string>();

constructor() { }

sendMessage() {

this.messageEvent.emit(this.message)

}

import { Component } from '@angular/core';

@Component({

selector: 'app-parent',

Message: {{message}}

<app-child (messageEvent)="receiveMessage($event)"></app-child>

styleUrls: ['./parent.component.css']

})

export class ParentComponent {

constructor() { }

Message:string;

receiveMessage($event) {

this.message = $event

}

JavaScript

First, we have to import output and EventEmitter from the @anguar/core , then we are creating an event messageEvent using EventEmitter . when the button is clicked it calls the function sendMessage and calls the emit with the message. we are using event binding property with the child selector . The parent can now subscribe to this messageEvent that’s outputted by the child component, then run the receive message function whenever this event occurs.

Wednesday, October 19, 2022

Interview questions 2022 angular

If Record Exists, Update Else Insert

MERGE
INTO    table2 t2
USING   table1 t1
ON      t2.email = t1.email
WHEN MATCHED THEN
UPDATE
SET     t2.col1 = t1.col1,
        t2.col2 = t1.col2
WHEN NOT MATCHED THEN
INSERT  (col1, col2)
VALUES  (t1.col1, t1.col2)

JSON_QUERY (Transact-SQL)

https://www.google.com/search?q=communicatingdata+in+angular&rlz=1C1RXQR_enIN1016IN1016&oq=communicatingdata+in++angular&aqs=chrome..69i57j0i546l2j0i30i546.21656j0j7&sourceid=chrome&ie=UTF-8

How Components Communicate With Each Other In Angular

https://www.google.com/search?rlz=1C1RXQR_enIN1016IN1016&sxsrf=ALiCzsaMQQtDQWVTu16g8FhyLGFO2KBDJA:1666160359941&q=communicating+data+in+angular&spell=1&sa=X&ved=2ahUKEwjclJKD0-v6AhXwSmwGHatdDfIQkeECKAB6BAgFEAE

https://www.thirdrocktechkno.com/blog/how-angular-components-communicate/

https://www.dotnettricks.com/learn/angular/top-20-angular-interview-questions-and-answers

https://www.thirdrocktechkno.com/blog/how-angular-components-communicate/

https://www.sqlshack.com/understanding-the-sql-merge-statement/

https://www.google.com/search?q=check+if+record+exists+in+temp+table+update+else+insert+sql+server&rlz=1C1RXQR_enIN1016IN1016&oq=check+if+record+exists+in+temp+table+update+else+insert+sql+server&aqs=chrome.0.69i59.10573j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=how+to+read+json+output+from+sql+server&rlz=1C1RXQR_enIN1016IN1016&sxsrf=ALiCzsZk2vN_38ZOMDL0an4CNUzES5CNiA%3A1666162997803&ei=NaFPY4HSMO_gseMPnM68WA&ved=0ahUKEwjBwPzs3Ov6AhVvcGwGHRwnDwsQ4dUDCA8&uact=5&oq=how+to+read+json+output+from+sql+server&gs_lcp=Cgdnd3Mtd2l6EAMyBQgAEKIEMgcIABAeEKIEMgUIABCiBDIFCAAQogQyBwgAEB4QogQ6CggAEEcQ1gQQsAM6CgghEMMEEAoQoAFKBAhNGAFKBAhBGABKBAhGGABQ5gpYsB1gmSloAnABeACAAdUCiAG0CJIBBzAuMy4xLjGYAQCgAQHIAQjAAQE&sclient=gws-wiz

https://www.google.com/search?q=outer+apply+in+sql&rlz=1C1RXQR_enIN1016IN1016&oq=outer+ap&aqs=chrome.0.0i20i263i512j0i512l9.4755j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=coalesce+in+sql&rlz=1C1RXQR_enIN1016IN1016&oq=collase&aqs=chrome.2.69i57j0i10i131i433i512l2j0i10i512l6j0i10i433i512.5622j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=check+if+record+exists+in+temp+table+update+else+insert+sql+server&rlz=1C1RXQR_enIN1016IN1016&sxsrf=ALiCzsb5J4ANp-dMWmBK1g31YUioHii-Gw%3A1666162689925&ei=AaBPY-2AOMa7seMPz6S10Ak&ved=0ahUKEwithZXa2-v6AhXGXWwGHU9SDZoQ4dUDCA8&uact=5&oq=check+if+record+exists+in+temp+table+update+else+insert+sql+server&gs_lcp=Cgdnd3Mtd2l6EAM6CggAEEcQ1gQQsAM6BwgjELACECc6BQgAEIYDOgQIIxAnOgUIABCiBDoHCAAQHhCiBDoECCEQCkoECE0YAUoECEEYAEoECEYYAFCXJliAemDegQFoAXABeACAAe4BiAHwGZIBBjAuMjAuMZgBAKABAcgBCMABAQ&sclient=gws-wiz

https://www.google.com/search?q=register+filter+in+web+api&rlz=1C1RXQR_enIN1016IN1016&oq=register+filter&aqs=chrome.1.69i57j0i20i263i512j0i512l8.12903j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=get+type+of+input+c%23&rlz=1C1RXQR_enIN1016IN1016&oq=get+type+of+input+c%23&aqs=chrome..69i57j33i22i29i30l4.11392j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=genec+method+c%23&rlz=1C1RXQR_enIN1016IN1016&oq=genec+method+c%23&aqs=chrome..69i57j0i13i512l7j0i13i30l2.9752j1j7&sourceid=chrome&ie=UTF-8

generic methods with example

https://www.google.com/search?q=check+if+string+is+valid+json+webabi+c%23&rlz=1C1RXQR_enIN1016IN1016&sxsrf=ALiCzsbGPwwZAkAwxiue9qOP5J0ZFZU9fQ%3A1666162262959&ei=Vp5PY-CMOsaaseMPoai-4AU&ved=0ahUKEwjgj8mO2uv6AhVGTWwGHSGUD1wQ4dUDCA8&uact=5&oq=check+if+string+is+valid+json+webabi+c%23&gs_lcp=Cgdnd3Mtd2l6EAMyBQgAEKIEMgUIABCiBDIHCAAQHhCiBDoKCAAQRxDWBBCwA0oECE0YAUoECEEYAEoECEYYAFC0BFj2RGDgSmgCcAF4AIABnwKIAY8MkgEFMC41LjOYAQCgAQHIAQjAAQE&sclient=gws-wiz

https://www.google.com/search?q=provide+action+verb+c%23&rlz=1C1RXQR_enIN1016IN1016&sxsrf=ALiCzsapZkev1U4CxHSxsDfZBcV0iFqWRw%3A1666162169916&ei=-Z1PY6bCN6mfseMP982DqA0&ved=0ahUKEwjmopri2ev6AhWpT2wGHffmANUQ4dUDCA8&uact=5&oq=provide+action+verb+c%23&gs_lcp=Cgdnd3Mtd2l6EAMyBQghEKABMgUIIRCgAToKCAAQRxDWBBCwA0oECEEYAEoECEYYAFD-AVjnGmD9IWgCcAF4AYAB4ASIAdQIkgEHMC4zLjUtMZgBAKABAcgBCMABAQ&sclient=gws-wiz

https://www.google.com/search?q=http+request+angular+header&rlz=1C1RXQR_enIN1016IN1016&oq=http+request+angular+header&aqs=chrome..69i57j0i22i30l9.10903j1j7&sourceid=chrome&ie=UTF-8

https://stackoverflow.com/questions/41133705/how-to-correctly-set-http-request-header-in-angular-2

https://www.google.com/search?q=parent+to+child+data+transfer+in+angular&rlz=1C1RXQR_enIN1016IN1016&oq=parent+to&aqs=chrome.1.69i57j0i20i263i512j0i512l8.6322j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?rlz=1C1RXQR_enIN1016IN1016&sxsrf=ALiCzsZ1rDo_cMXDQzP74kAbQmwgPummYw:1666161767774&q=web+organs+angular&spell=1&sa=X&ved=2ahUKEwimuLmi2Ov6AhVoTGwGHR-VAucQBSgAegQIBxAB&biw=1280&bih=569&dpr=1.5

https://www.google.com/search?rlz=1C1RXQR_enIN1016IN1016&sxsrf=ALiCzsZ2c_b9mUp2AjjVpeZ_BAGfEWoaNQ:1666161594154&q=to+navigate+from+admin+to+another+page+typescript&spell=1&sa=X&ved=2ahUKEwiB0dTP1-v6AhXNcGwGHfJfA3kQBSgAegQIBhAB&biw=1280&bih=569&dpr=1.5

https://www.google.com/search?q=initialize+reactive+form+angular&rlz=1C1RXQR_enIN1016IN1016&oq=initialize+rea&aqs=chrome.2.0i512l4j69i57j0i512l5.10054j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=subject+vs+behaviorsubject&rlz=1C1RXQR_enIN1016IN1016&oq=subject+&aqs=chrome.2.69i57j0i131i433i512j0i433j0i433i512j0i512j0i20i263i512j0i433i512j69i65.7153j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=how+to+pass+data+between+two+independent+components+in+angular&rlz=1C1RXQR_enIN1016IN1016&oq=data+between+two+independent+c&aqs=chrome.1.69i57j0i22i30l2.17941j0j7&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=%40output+in+angular&rlz=1C1RXQR_enIN1016IN1016&oq=%40output&aqs=chrome.1.69i57j0i512l6j69i65.9822j0j7&sourceid=chrome&ie=UTF-8

Tuesday, October 18, 2022

environment variable on windows 10

Modifying PATH on Windows 10:

In the Start Menu or taskbar search, search for "environment variable".
Select "Edit the system environment variables".
Click the "Environment Variables" button at the bottom.
Double-click the "Path" entry under "System variables".
With the "New" button in the PATH editor, add C:\Program Files\Git\bin\ and C:\Program Files\Git\cmd\ to the end of the list.
Close and re-open your console.

Sunday, October 16, 2022

Angular Interview questions

Fundamentals of Angular

Component

Module

Data bindings

Services

Dependency Injection

Directives

Templates

Application Bootstrapping

Navigation or Routing

Native mobile development

Angular Elements

Angular Universal

What are the features of Angular CLI?

Angular CLI is a powerful command-line tool by which we can create new files, update the file, build and deploy the angular application and other features are available so that we can get started with Angular development within a few minutes.

Packaging application and release for the deployment
Testing an Angular app
Bootstrapping Angular app
Various code generation options for Component, Module, Class, Pipes, Services, Enums, and many other types are also supported.
Running the unit test cases
Build and deployment our Angular application

What are the advantages of the Angular Console?

There are a few advantages and features provided by the Angular Console.

Build CLI Commands visually
Internal terminal for the output
Import the existing Angular project
Trivial code generation
Run the custom NPM scripts
Install various extensions

What is a Component in Angular?

Components are the basic building block of an Angular application. An Angular component mainly contains a template, class, and metadata. It is used to represent the data visually. A component can be thought of as a web page. An Angular component is exported as a custom HTML tag like as: <my-app></my-app>.

For creating a component, we can use decorator @component

ng generate component democomponent

What is the Template Expression?

Template expressions are the same expression that we use with JavaScript. But the difference is that we can use it within our Html template, so it looks like we are using JavaScript along with Html

What is Data Binding in Angular?

Data binding is one of the important core concepts which is used to do the communicate between the component and DOM. In other words, we can say that Data binding is a way to add/push elements into HTML Dom or pop the different elements based on the instructions given by the Component. There are mainly three types of data binding support in order to achieve the data bindings in Angular.

One-way Binding (Interpolation, Attribute, Property, Class, Style)

Event Binding

Two-way Binding

Using these different binding mechanisms, we can easily communicate the component with the DOM element and perform various operations.

What is Interpolation?

Interpolation in Angular is used to show the property value from the component to the template or used to execute the expressions. Generally, interpolation can be used to execute the JavaScript expressions and append the result to the Html DOM. Another use of Interpolation is to print the data or we can say one-way data, which is coming from the component i.e. from a variable or the item of an array. And interpolation can be used to achieve Property binding as well by using curly braces {{ }}, let’s see a simple example.

App.component.ts

myMessage: string = "Hello World";

App.component.html

<p>{{ myMessage }}</p>

<p>Total : {{ 25 + 50 + 75 + 100 }}</p>

Directives in angular?

directives are angular sytaxces which go and change the behaviour of html dom

Monday, September 26, 2022

Using temp tables in telerik in SP

IF 1=0 BEGIN

SET FMTONLY OFF

END

Tuesday, August 2, 2022

Using Rank in Linq

//IEnumerable<SelectListItem> _invoiceList = from r in

// (Context.FUE_FUEL_DETAIL

// .GroupBy(c => c.INVOICE_NUMBER)

// .OrderBy(x => x.Count())

// .Select((g, rank) => new { INVOICE_NUMBER = g.Key, Count = g.Count(), INVOICE_ID = rank }).ToList())

// select new SelectListItem

// {

// Text = r.INVOICE_NUMBER.ToString().TrimEnd(),

// Value = r.INVOICE_ID.ToString()

// };

Thursday, June 23, 2022

Jquery

get live config file and change below value

debug is false

</customHeaders>

</httpProtocol>

remove NeoDynamic comment

Sunday, May 22, 2022

Senior developer Interview Questions

What are the advantaged of using interfaces
Difference between abstract class and encapsulation
Can we have strings in enum? what is the default value and type, Can enum be long and short?
What is managed, unmanaged code? give examples
What is sealed class? Can I have abstract sealed class?-
static const? Can I apply static to const?
Can I use new modifier on static
Sealed, Virtual, overide
Difference between new and overrride
this keyword on static class
Can a virtual method have static
Difference between dispose, finalize ( Used by Garbage collector)
What are Anoymous method
Difference between IQueryable and Ienumarable? HAve ou used Entity Framework
linq - iqueryable-code side filter - performance, ienumerable, filter
Difference between Thread and Task

Task is a background task.

Use Task for quick operations

Execute task asynchronously and in parallel(multiple cores)

Task by default, uses CLR threadpool

Tasks support cancellation

Task supports: async,await keywords

Threads are expensive

Thread can be a foreground or background

difference between Dependency Injection and IOC
What is Transient, Scoped, singleton
How is DI managed in .NET core

builder.Services.AddScoped<IMyDependency, MyDependency>()

asp.net core injects the instances of dependency classes by using builtin IOC container

Routing is he process of mapping an incoming http request to a specific method in our app code.

Attribute routing is used to create REST API endpoints for web services

It uses a set of attributes to map action methods to route templates

Action filters are used to implement logic that gets executed before and after a controller action executes.

What is common table expression

Common Table expression is a temporary named result set that you can reference with a SELECT, INSERT, UPDATE OR DELETE statement

WITH expression_name ([column_names..).]

cte query definition

Two types of CTE:

1. Recursive: Use recursive procedural loops

2. Non-recursive: CTE ddoesn;t use recursion

Difference between Temp table and table variable:Temp table: supports transactions and error handling, DDL statements, table variable do not support transaction, error handling ddl statements, Tmp table is for big datasets, table variable is for small datasets

TABLE VARIABLE:

DECLARE @T ABLEVARIABLE TABLE(COLUMN1 DATATYPE, COLUMN2 DATATYPE)

TEMP TABLE:

CREATE TABLE {# | ##} TEMP_TABLE_NAME(DOLUMN DATATYPE OCNSTRAINT)

Triggers and types of triggers. Trigger is a piece of code executed automatically in response to a specific event occurrent on a table in a database, it is invoked before or after Insert, update delete, there are two types of triggers: row and statement level triggers. row level trigger executes each time a row is affected by an update statement. statement trigger is called once regardless of how many rows affected by the UPDATE statement

What did you work on AWS? Have you worked with AWS lambda
What is self join? join in which a table is joined with itself means each row of the table is combined with itself and with every other row of the table,
What PMP tools have you used? I have used Trello, Asana, Jira, Google sheets
var result = employees.Where(x1 => x1.Salary == employees.OrderByDescending(x => x.Salary) .Select(x => x.Salary).Distinct().Take(NthNumber) .Skip(NthNumber - 1).FirstOrDefault()).ToArray();

Sunday, May 1, 2022

ASP.NET Web API

1. What is ASP.NET Web API?

The term API stands for Application Programming Interface. ASP.NET Web API is a framework that makes it easy to build Web APIs, i.e. HTTP based services on top of the .NET Framework. ASP.NET Web API is an ideal platform for building Restful services. These services can then be consumed by a broad range of clients like

· Browsers

· Mobile applications

· Desktop applications

· IOTs

2. What is the Rest?

REST stands for Representational State Transfer. This is an architectural pattern used for exchanging data over a distributed environment. In Rest, there is something called Client and Server, and the data can be exchanged between the client and server over a distributed environment. Distributed environment means the client can be on any platform like Java, .NET, PHP, etc. and the server can also be on any platform like Java, .NET, PHP, etc. The REST architectural pattern treats each service as a resource and a client can access those resources by using HTTP Protocol methods such as GET, POST, PUT, PATCH, and DELETE.

3. What are the RESTful services?

REST stands for Representational State Transfer.. REST is an architectural pattern for exchanging data over a distributed environment. REST architectural pattern treats each service as a resource and a client can access these resources by using HTTP protocol methods such as GET, POST, PUT, PATCH, and DELETE. The REST architectural pattern specifies a set of constraints that a system should be adhered to. Here are the REST constraints.

4. What are the differences between REST and SOAP?

The difference between REST and SOAP is given below:

· SOAP stands for Simple Object Access Protocol whereas REST stands for Representational State Transfer.

· The SOAP is an XML-based protocol whereas REST is not a protocol rather it is an architectural pattern i.e. resource-based architecture.

· SOAP has specifications for both stateless and state-full implementation whereas REST is completely stateless.

· SOAP enforces message format as XML whereas REST does not enforce message format as XML or JSON.

· The SOAP message consists of an envelope that includes SOAP headers and body to store the actual information we want to send whereas REST uses the HTTP build-in headers (with a variety of media-types) to store the information and uses the HTTP Methods such as GET, POST, PUT, PATCH, and DELETE to perform CRUD operations.

· SOAP uses interfaces and named operations (i.e. Service Contract and Operation Contract) to expose the service whereas to expose resources (service) REST uses URI and methods like (GET, PUT, POST, PATCH, and DELETE).

· SOAP Performance is slow as compared to REST.

difference between put post and patch in rest api

The POST method is used to submit an entity to a resource. It is used for creating a new resource or adding a new entity to an existing resource. When a POST request is sent, the server creates a new resource and assigns a new URL to it.

PATCH

The PATCH method is used to partially update a resource. It is useful when you want to update only a few fields of a resource without replacing the entire resource. In a PATCH request, you specify the fields that need to be updated in the request body.

The PUT method is used to update a resource or create a new resource if it does not exist. When a PUT request is sent, the entire resource is replaced with the new data. If the resource does not exist, it will be created.

Authentication and Authorization in ASP.NET Web API

https://learn.microsoft.com/en-us/aspnet/web-api/overview/security/authentication-and-authorization-in-aspnet-web-api

Features	PUT	PATCH
Purpose	Used to update or replace an entire resource.	Used to apply partial modifications to a resource.
Data Handling	Requires the client to send the complete resource representation.	Requires only the changes (delta) to be sent, not the entire resource.
Error Handling	If the resource doesn’t exist, it may create a new one (depending on implementation).	Typically used only for existing resources; may fail if the resource doesn’t exist.
Performance	It can be less efficient for large resources, as the entire resource is sent.	More efficient for small changes, as only the necessary data is sent.
Request Body	Contains the full resource representation.	Contains only the fields or data to be updated.
Use Case	Best for replacing a resource entirely (e.g., updating a user profile).	Best for making small updates (e.g., changing a user’s email address).
Example	PUT /users/1 with full user data updates the entire user resource.	PATCH /users/1 with { “email”: “new@example.com” } updates only the email.

5. State management

SQL top 5

1. Index ?

Index is a lookup table associated with actual table or view that is used by the database to improve the data retrieval performance timing. In index, keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. Index gets automatically created if primary key and unique constraint is defined on the table

2. Differences between clustered and non-clustered indexes.

There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.

Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.

Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step

3. What do you understand by query optimization?

The phase that identifies a plan for evaluation query which has the least estimated cost is known as query optimization.

The advantages of query optimization are as follows:

· The output is provided faster

A larger number of queries can be executed in less time

· Reduces time and space complexity

4. Explain different types of Normalization.

There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one.The first three normal forms are usually adequate.

· First Normal Form (1NF) – No repeating groups within rows

· Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.

· Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.

5. What is the ACID property in a database?

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.

· Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.

· Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.

· Isolation: The main goal of isolation is concurrency control.

· Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error

6. What do you mean by “Trigger” in SQL?

A SQL trigger is a database object which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when a change occurs on a database table such as a record is inserted or updated or deleted. For example, a trigger can be set on a record insert in a database table

7. List the ways to get the count of records in a table?

To count the number of records in a table in SQL, you can use the below commands:

· SELECT COUNT(*) FROM table1

· SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

8. Write a SQL query to get the third-highest salary of an employee from employee_table?

TOP keyword

SELECT TOP 1 salary

FROM(

SELECT TOP 3 salary

FROM employee_table

ORDER BY salary DESC) AS emp

ORDER BY salary ASC;

Using Limit :

SELECT salary

FROM employee_table

ORDER BY salary DESC

LIMIT 2, 1

Sub Query

SELECT salary

FROM

(SELECT salary

FROM employee_table

ORDER BY salary DESC

LIMIT 3) AS Comp

ORDER BY salary

LIMIT 1;

Rank:

select * from(

select ename, sal, dense_rank()

over(order by sal desc)r from Employee)

where r=&n;

To find to the 2nd highest sal set n = 2

To find 3rd highest sal set n = 3 and so on.

SELECT EmpName , Salary FROM(

SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS SNo , EmpName, Salary

FROM Employee

)Sal

WHERE SNo = 3

9. How can you fetch alternate records from a table?

You can fetch alternate records i.e both odd and even row numbers. For example- To display even numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

Now, to display odd numbers:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1

10. What is a View?

A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship

11.

1. An organization is having multiple departments

2. Organization is having multiple employees

3. An employee can play one or multiple roles in same or various departments

employee(eid, ename, salary)

department(did, dname)

empdept(eid, did, role)

Write down the query to select employee count of the department along with the department name. It should select those department as well which are having no employees.

SELECT department_name AS 'Department Name',

COUNT(*) AS 'No of Employees'

FROM departments

INNER JOIN employees

ON employees.department_id = departments.department_id

GROUP BY departments.department_id, department_name

ORDER BY department_name;

1212.

The nth highest salary in SQL SERVER using TOP keyword

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM #Employee ORDER BY salary DESC ) AS temp ORDER BY salary

using row num

SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM Employee e ) WHERE rn = N

Difference Between SCOPE_IDENTITY() and @@IDENTITY

@@IDENTITY - Returns the last identity values that were generated in any table in the current session. @@IDENTITY is not limited to a specific scope.

SCOPE_IDENTITY() - Return the last identity values that are generated in any table in the current session. SCOPE_IDENTITY returns values inserted only within the current scope.

SQL

What is SQL and what it is used for?

What is Primary Key?

What is Foreign Key?

What is an index and how to create one?

Name types of indexes

How to select all columns from a table?

How to select given columns from a table?

How to select a column with duplicated values?

What is JOIN used for?

Name types of JOINs

What is the difference between LEFT and RIGHT joins?

What is a self join?

How does CROSS join work?

How does UNION ALL work?

How to select data from 2 tables without a JOIN?

What are subqueries?

How to skip NULLs when using LEFT or RIGHT join?

When to use each type of join?

What is database transaction?

Explain transaction isolation levels

When to use each transaction isolation level

When can deadlocks occur in a SQL statement?

Explain how the query execution plan works

What is a database view?

What is a materialized view?

What is the difference between a stored procedure and a function?

What is the purpose of triggers?

What database normalization forms do you know?

In what cases would you consider database denormalization?

What is GROUP BY used for?

How to add condition to the GROUP BY ?

Write a SQL query to select top 5 products with highest sales

Write a SQL query to select top 5 products with the highest price

How to filter a single column by multiple values?

Name the scalar functions

Difference between a subquery and join

Difference between a correlated subquery from a regular subquery

How to ensure the uniqueness of the column?

How to add columns to an index?

What is SQL injection, and how can it be prevented?

Explain the ACID

What is a common table expression (CTE)?

How do you write a recursive CTE?

What is a database sequence?

What is ROW_NUMBER ?

How do you handle errors in SQL?

What is a composite key?

What are default constraints?

How does the CASE statement work in SQL ?

What are the diff between SQL and NoSQL databases

----------------------

What is SQL, and what are its different types?
Explain the difference between SQL and MySQL.
What are the different types of joins in SQL? Provide examples.
What is normalization, and how does it differ from denormalization?
Explain the different types of normal forms.
What is the difference between DELETE, TRUNCATE, and DROP commands?
What are primary keys and foreign keys?
How do INNER JOIN and OUTER JOIN differ?
What is a subquery? Explain with an example.
What are indexes in SQL, and why are they important?
What are aggregate functions in SQL? Provide some examples.
What is the difference between GROUP BY and HAVING clauses?
What is the purpose of the WHERE clause?
What are the different types of constraints in SQL?
What is a stored procedure, and how does it differ from a function?
What is a trigger in SQL? Provide an example.
Explain ACID properties in the context of SQL.
How do VARCHAR and CHAR differ?
What are the different types of SQL data types?
What is a self-join? Provide an example.
What is a view in SQL, and how does it differ from a table?
How does the EXISTS clause work?
What is a Common Table Expression (CTE)?
What is the purpose of INDEX in SQL?
What are transactions in SQL, and how can you manage them?
Explain the RANK() and DENSE_RANK() functions in SQL.
How can you optimize SQL queries to improve performance?
What is the difference between UNION and UNION ALL?
How is the LIKE operator used in SQL?
Explain the COALESCE function with an example.