Wednesday, 21 November 2018

 Java 7 features

1)Strings in Switch:

public void testStringInSwitch(String param){
       final String JAVA5 = "Java 5";
       final String JAVA6 = "Java 6";
       final String JAVA7 = "Java 7";
       switch (param) {
           case JAVA5:
               System.out.println(JAVA5);
               break;
           case JAVA6:
               System.out.println(JAVA6);
               break;
           case JAVA7:
               System.out.println(JAVA7);
               break;
       }
   }

2)Binary Literals:

public void testBinaryIntegralLiterals(){
        int binary = 0b1000; //2^3 = 8
        if (binary == 8){
            System.out.println(true);
        } else{
            System.out.println(false);
        }
}

3)Underscore Between Literals:

public void testUnderscoresNumericLiterals() {
    int oneMillion_ = 1_000_000; //new
    int oneMillion = 1000000;
    if (oneMillion_ == oneMillion){
        System.out.println(true);
    } else{
        System.out.println(false);
    }
}

4)Type Inference for Generic Instance:

List<String> l = new ArrayList<>();
l.add("A");
l.addAll(new ArrayList<>());

5)Multiple exception catching:

public void testMultiCatch(){
    try {
        throw new FileNotFoundException("FileNotFoundException");
    } catch (FileNotFoundException | IOException fnfo) {
        fnfo.printStackTrace();
    }
}

Java 8 features

1)forEach() method in Iterable interface

Whenever we need to traverse through a Collection, we need to create an Iterator whose whole purpose is to iterate over and then we have business logic in a loop for each of the elements in the Collection. We might get ConcurrentModificationException if iterator is not used properly.

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.function.Consumer;
import java.lang.Integer;

public class Java8ForEachExample {

public static void main(String[] args) {

//creating sample Collection
List<Integer> myList = new ArrayList<Integer>();
for(int i=0; i<10; i++) myList.add(i);

//traversing using Iterator
Iterator<Integer> it = myList.iterator();
while(it.hasNext()){
Integer i = it.next();
System.out.println("Iterator Value::"+i);
}

//traversing through forEach method of Iterable with anonymous class
myList.forEach(new Consumer<Integer>() {

public void accept(Integer t) {
System.out.println("forEach anonymous class Value::"+t);
}

});

//traversing with Consumer interface implementation
MyConsumer action = new MyConsumer();
myList.forEach(action);

}

}

//Consumer implementation that can be reused
class MyConsumer implements Consumer<Integer>{

public void accept(Integer t) {
System.out.println("Consumer impl Value::"+t);
}


}

2)default and static methods in Interfaces
3)Functional Interfaces and Lambda Expressions

Functional interfaces are new concept introduced in Java 8. An interface with exactly one abstract method becomes Functional Interface. We don’t need to use @FunctionalInterface annotation to mark an interface as Functional Interface.
One of the major benefits of functional interface is the possibility to use lambda expressions to instantiate them. We can instantiate an interface with anonymous class but the code looks bulky.
Runnable r = new Runnable(){
@Override
public void run() {
System.out.println("My Runnable");
}};
(or)
Runnable r1 = () -> {
System.out.println("My Runnable");
};

4)Java Stream API for Bulk Data Operations on Collections

A new java.util.stream has been added in Java 8 to perform filter/map/reduce like operations with the collection. Stream API will allow sequential as well as parallel execution.

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Stream;

public class StreamExample {

public static void main(String[] args) {

List<Integer> myList = new ArrayList<>();
for(int i=0; i<100; i++) myList.add(i);

//sequential stream
Stream<Integer> sequentialStream = myList.stream();

//parallel stream
Stream<Integer> parallelStream = myList.parallelStream();

//using lambda with Stream API, filter example
Stream<Integer> highNums = parallelStream.filter(p -> p > 90);
//using lambda in forEach
highNums.forEach(p -> System.out.println("High Nums parallel="+p));

Stream<Integer> highNumsSeq = sequentialStream.filter(p -> p > 90);
highNumsSeq.forEach(p -> System.out.println("High Nums sequential="+p));

}

}

5)Java Time API

Java Time API has some sub-packages java.time.format that provides classes to print and parse dates and times and
java.time.zone provides support for time-zones and their rules.

package com.shris.java8.time;

import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class DateParseFormatExample {

public static void main(String[] args) {

//Format examples
LocalDate date = LocalDate.now();
//default format
System.out.println("Default format of LocalDate="+date);
//specific format
System.out.println(date.format(DateTimeFormatter.ofPattern("d::MMM::uuuu")));
System.out.println(date.format(DateTimeFormatter.BASIC_ISO_DATE));


LocalDateTime dateTime = LocalDateTime.now();
//default format
System.out.println("Default format of LocalDateTime="+dateTime);
//specific format
System.out.println(dateTime.format(DateTimeFormatter.ofPattern("d::MMM::uuuu                                                                   HH::mm::ss")));
System.out.println(dateTime.format(DateTimeFormatter.BASIC_ISO_DATE));

Instant timestamp = Instant.now();
//default format
System.out.println("Default format of Instant="+timestamp);

//Parse examples
LocalDateTime dt = LocalDateTime.parse("27::Apr::2014 21::39::48",
DateTimeFormatter.ofPattern("d::MMM::uuuu HH::mm::ss"));
System.out.println("Default format after parsing = "+dt);
}

}

The new Time API prefers enums over integer constants for months and days of the week. One of the useful class is DateTimeFormatter for converting datetime objects to strings.

6)Collection API improvements

We have already seen forEach() method and Stream API for collections. Some new methods added in Collection API are:

Iterator default method forEachRemaining(Consumer action) to perform the given action for each remaining element until all elements have been processed or the action throws an exception.
Collection default method removeIf(Predicate filter) to remove all of the elements of this collection that satisfy the given predicate.
Collection spliterator() method returning Spliterator instance that can be used to traverse elements sequentially or parallel.
Map replaceAll(), compute(), merge() methods.
Performance Improvement for HashMap class with Key Collisions

7)Concurrency API improvements

Some important concurrent API enhancements are:

ConcurrentHashMap compute(), forEach(), forEachEntry(), forEachKey(), forEachValue(), merge(), reduce() and search() methods.
CompletableFuture that may be explicitly completed (setting its value and status).
Executors newWorkStealingPool() method to create a work-stealing thread pool using all available processors as its target parallelism level.

8)Java IO improvements

Some IO improvements known to me are:

Files.list(Path dir) that returns a lazily populated Stream, the elements of which are the entries in the directory.
Files.lines(Path path) that reads all lines from a file as a Stream.
Files.find() that returns a Stream that is lazily populated with Path by searching for files in a file tree rooted at a given starting file.
BufferedReader.lines() that return a Stream, the elements of which are lines read from this BufferedReader.

Sunday, 11 November 2018

Angular Framework 20

1. Angular Framework

Angular is a TypeScript-based open-source front-end framework maintained by Google. It helps developers build scalable single-page applications (SPAs) with two-way data binding, dependency injection, routing, and modular architecture. Angular follows MVC-like patterns and provides reusable components and services.


2. Angular Modules

Definition:
Modules are containers that group related components, directives, services, and pipes. Every Angular app has a root module (AppModule) and can have multiple feature modules for modularity. They improve maintainability and lazy loading.

Example (app.module.ts):

import { NgModule } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { AppComponent } from './app.component'; import { CourseCardComponent } from './course-card/course-card.component'; @NgModule({ declarations: [AppComponent, CourseCardComponent], imports: [BrowserModule], bootstrap: [AppComponent] }) export class AppModule {}

3. Components

Definition:
Components are building blocks of Angular apps. They control a part of the UI using HTML templates, CSS styles, and TypeScript logic. Each component is associated with a selector used in HTML.

Example (course-card.component.ts):

import { Component } from '@angular/core'; @Component({ selector: 'app-course-card', templateUrl: './course-card.component.html', styleUrls: ['./course-card.component.css'] }) export class CourseCardComponent { title = "Angular Basics"; }

4. Services

Definition:
Services hold business logic and data retrieval logic. They are reusable across components and injected via Angular’s Dependency Injection (DI) system.

Example (courses.service.ts):

import { Injectable } from '@angular/core'; @Injectable({ providedIn: 'root' }) export class CoursesService { getCourses() { return ['Angular', 'React', 'Vue']; } }

5. Templates

Definition:
Templates define the view (UI) of a component using HTML and Angular syntax like interpolation ({{ }}), property binding, event binding, and structural directives.

Example (course-card.component.html):

<h2>{{ title }}</h2> <button (click)="enroll()">Enroll</button>

6. Pipes

Definition:
Pipes transform data before displaying it in templates. Angular provides built-in pipes like date, currency, uppercase, and we can create custom pipes.

Example:

<p>{{ today | date:'fullDate' }}</p>

Custom Pipe (truncate.pipe.ts):

import { Pipe, PipeTransform } from '@angular/core'; @Pipe({ name: 'truncate' }) export class TruncatePipe implements PipeTransform { transform(value: string, limit = 10): string { return value.length > limit ? value.substring(0, limit) + '...' : value; } }

7. Routing

Definition:
Routing allows navigation between different views (components) without reloading the page. It defines paths mapped to components.

Example (app-routing.module.ts):

import { NgModule } from '@angular/core'; import { RouterModule, Routes } from '@angular/router'; import { CourseCardComponent } from './course-card/course-card.component'; const routes: Routes = [ { path: 'courses', component: CourseCardComponent } ]; @NgModule({ imports: [RouterModule.forRoot(routes)], exports: [RouterModule] }) export class AppRoutingModule {}

8. Directives

Definition:
Directives are instructions in the DOM that add behavior to elements.

  • Structural Directives: Change DOM structure (*ngIf, *ngFor).

  • Attribute Directives: Change DOM appearance/behavior (ngStyle, ngClass).

  • Custom Directives: Developer-defined logic.

Example – Built-in:

<p *ngIf="isVisible">Visible Content</p> <li *ngFor="let course of courses">{{ course }}</li>
Structural Directives (change DOM layout)
  1. *ngIf – Conditionally include/exclude elements.

  2. *ngFor – Loop over arrays.

  3. *ngSwitch, *ngSwitchCase, *ngSwitchDefault – Switch-case rendering.

<div *ngIf="isLoggedIn">Welcome!</div> <ul> <li *ngFor="let course of courses">{{ course }}</li> </ul> <div [ngSwitch]="role"> <p *ngSwitchCase="'admin'">Admin Panel</p> <p *ngSwitchDefault>User Panel</p> </div>

Attribute Directives (modify appearance/behavior)

  1. ngClass – Apply classes dynamically.

  2. ngStyle – Apply styles dynamically.

  3. [(ngModel)] – Two-way data binding.

<p [ngClass]="{active: isActive}">Dynamic Class</p> <p [ngStyle]="{'color': isActive ? 'green' : 'red'}">Dynamic Style</p> <input [(ngModel)]="username">

Custom Directive Example

@Directive({ selector: '[appHighlight]' }) export class HighlightDirective { constructor(private el: ElementRef, private renderer: Renderer2) {} @HostListener('mouseenter') onEnter() { this.renderer.setStyle(this.el.nativeElement, 'backgroundColor', 'lightblue'); } @HostListener('mouseleave') onLeave() { this.renderer.removeStyle(this.el.nativeElement, 'backgroundColor'); } }

9. Decorators in Angular

Definition:
Decorators add metadata to classes, properties, and methods. They tell Angular how to process a class or element.

1. @NgModule

  • Defines a module that bundles components, directives, pipes, and services.

  • Every Angular app has a root module (AppModule).

  • Helps organize the application into functional blocks.

import { NgModule } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { AppComponent } from './app.component'; @NgModule({ declarations: [AppComponent], // components, directives, pipes imports: [BrowserModule], // other modules providers: [], // services bootstrap: [AppComponent] // root component }) export class AppModule {}

2. @Component

  • Declares a component (UI + logic).

  • Connects HTML template, CSS styles, and TypeScript logic.

import { Component } from '@angular/core'; @Component({ selector: 'app-hello', template: `<h1>Hello {{name}}</h1>`, styles: ['h1 { color: blue; }'] }) export class HelloComponent { name = 'Angular'; }

3. @Injectable

  • Declares a service available for Dependency Injection.

  • Ensures the class can be injected into components or other services.

import { Injectable } from '@angular/core'; @Injectable({ providedIn: 'root' // singleton service }) export class CourseService { getCourses() { return ['Angular', 'Spring Boot', 'AWS']; } }

4. @Directive

  • Creates a custom directive (structural or attribute).

import { Directive, ElementRef } from '@angular/core'; @Directive({ selector: '[appRedText]' }) export class RedTextDirective { constructor(el: ElementRef) { el.nativeElement.style.color = 'red'; } }

5. @Pipe

  • Defines a custom pipe to transform data.

import { Pipe, PipeTransform } from '@angular/core'; @Pipe({ name: 'capitalize' }) export class CapitalizePipe implements PipeTransform { transform(value: string): string { return value.charAt(0).toUpperCase() + value.slice(1); } }

6. @Input / @Output

  • @Input → Pass data from parent to child component.

  • @Output → Send events from child to parent.

// child.component.ts @Input() title!: string; @Output() clicked = new EventEmitter<string>(); onClick() { this.clicked.emit(this.title); }

7. @HostListener / @HostBinding

  • @HostListener → Listen to DOM events.

  • @HostBinding → Bind properties to host element.

@Directive({ selector: '[appHover]' }) export class HoverDirective { @HostBinding('style.backgroundColor') bgColor = 'transparent'; @HostListener('mouseenter') onMouseEnter() { this.bgColor = 'yellow'; } @HostListener('mouseleave') onMouseLeave() { this.bgColor = 'transparent'; } }

8. @ViewChild / @ViewChildren

  • Access template DOM elements or child components.

@ViewChild('myInput') inputRef!: ElementRef; ngAfterViewInit() { console.log(this.inputRef.nativeElement.value); }

9. @ContentChild / @ContentChildren

  • Access projected content inside <ng-content>.

@ContentChild('projected') projected!: ElementRef;

10. Dependency Injection Decorators

  • @Optional → Service may or may not exist.

  • @Self → Look only in current injector.

  • @SkipSelf → Look in parent injector.

  • @Host → Look in host component injector.

Common Angular Decorators:

  • @Component → Defines a component.

  • @NgModule → Defines a module.

  • @Injectable → Marks a service for DI.

  • @Directive → Defines a custom directive.

  • @Pipe → Defines a custom pipe.

  • @Input → Pass data from parent → child.

  • @Output → Emit events child → parent.

  • @HostListener → Listen to DOM events.

  • @ViewChild / @ViewChildren → Access child elements/components.

Example (Input/Output):

@Component({ selector: 'app-child', template: `<button (click)="notify()">Notify Parent</button>` }) export class ChildComponent { @Output() notifyEvent = new EventEmitter<string>(); notify() { this.notifyEvent.emit("Hello Parent!"); } }

10. Project Architecture (src/app folder)

src/ └── app/ ├── app.module.ts ├── app.component.ts / .html / .css ├── course-card/ │ ├── course-card.component.ts / .html / .css ├── services/ │ └── courses.service.ts ├── directives/ │ └── highlighted.directive.ts ├── pipes/ │ └── truncate.pipe.ts ├── models/ │ └── course.ts └── app-routing.module.ts

11. Angular Architecture Flow (Diagram – textual form)

Modules → Components → Templates ↘ Services → Models Templates → Directives & Pipes → DOM

12. MVC Mapping in Angular

  • Model: Data models (Course class), services (CourseService).

  • View: Templates (.html), directives, and pipes controlling UI.

  • Controller: Components (.ts) act as controllers connecting Model & View.

MVC Diagram (textual form):

Model (Services + Data) ↔ Component (Controller) ↔ View (Templates + Directives + Pipes)

Some Angular CLI Commands

# Install Angular CLI npm install -g @angular/cli # Create new Angular project ng new angular-course

# To start application npm start (or) ng serve # Generate a component ng generate component course-card # Generate Angular control flow (structural directive block) ng generate @angular/core:control-flow # Generate custom directives ng g directive directives/highlighted ng g directive directives/ngx-unless # Generate a service ng g service services/courses # Generate standalone component ng generate @angular/core:standalone

Wednesday, 19 September 2018

MongoDB Qqueries


 What is MongoDB?

1) MongoDB is an open-source, non-relational database developed by MongoDB, Inc.
2) MongoDB stores data as documents in a binary representation called BSON (Binary JSON).
3) Related information is stored together for fast query access through the MongoDB query language. 4) Fields can vary from document to document; there is no need to declare the structure of documents to the system – documents are self-describing. If a new field needs to be added to a document, then the field can be created without affecting all other documents in the collection, without updating a central system catalog, and without taking the system offline. Optionally, schema validation can be used to enforce data governance controls over each collection.
5) MongoDB’s document data model maps naturally to objects in application code, making it simple for developers to learn and use. Documents give you the ability to represent hierarchical relationships to store arrays and other more complex structures easily.
Native, idiomatic drivers are provided for 10+ languages – and the community has built dozens more – enabling ad-hoc queries, real-time aggregation and rich indexing to provide powerful programmatic ways to access and analyze data of any structure.
Because documents can bring together related data that would otherwise be modeled across separate parent-child tables in a relational schema, MongoDB’s atomic single-document operations already provide transaction semantics that meet the data integrity needs of the majority of applications.
6) In MongoDB one or more fields may be written in a single operation, including updates to multiple sub-documents and elements of an array. The guarantees provided by MongoDB ensure complete isolation as a document is updated; any errors cause the operation to roll back so that clients receive a consistent view of the document.
7) MongoDB 4.0 added support for multi-document transactions, making it the only open source database to combine the ACID guarantees of traditional relational databases, the speed, flexibility, and power of the document model, with the intelligent distributed systems design to scale-out and place data where you need it. Through snapshot isolation, transactions provide a consistent view of data, and enforce all-or-nothing execution to maintain data integrity. Transactions in MongoDB feel just like transactions developers are familiar with in MySQL. They are multi-statement, with similar syntax (e.g. start_transaction and commit_transaction), and therefore easy for anyone with prior transaction experience to add to any application.
8) Unlike MySQL and other relational databases, MongoDB is built on a distributed systems architecture, rather than a monolithic, single node design. As a result, MongoDB offers out-of-the-box scale-out and data localization with automatic sharding, and replica sets to maintain always-on availability.

 Terminology and Concepts

Many concepts in MySQL have close analogs in MongoDB. The table below outlines the common concepts across MySQL and MongoDB.

My SQL
Mongo DB
ACID Transactions
ACID Transactions
Table
Collection
Row
Document
Column
Field
Secondary Index
Secondary Index
JOINs
Embedded documents, $lookup & $graphLookup
GROUP_BY
Aggregation Pipeline
Some of examples of users collection:

1) db.users.insertOne( { _id: 10, item: "box", qty: 20 } )
2) db.users.insertMany( [
{ item: "card", qty: 15 },
{ item: "envelope", qty: 20 },
{ item: "stamps" , qty: 30 }
   ] );
}
3) db.users.find( {} )
4) db.users.find( { “username”:"swamy" } )
5) db.users.find( { “username”: { $in: [ "swamy", "raju" ] } } )
SELECT *FROM users WHERE username in ("swamy ", "raju")
6) db.users.find( { “username”: "swamy", “sal”: { $lt: 30000 } } )
SELECT * FROM users WHERE username = "swamy" AND sal< 30000
7)  db.users.updateOne(
      { "username" : "swamy" },
      { $set: { "sal" : 40000 } }
   );
8)  db.users.updateMany(
      { “sal”: { $gt: 40000 } },
      { $set: { "Review" : true } }
   );
9) db.users.updateMany(
   { },
   { $set: { "username" : “swamy” } }
)
10) db.users.replaceOne(
      { " username " : "swamy" },
      { " username " : " swamy ", "department" : "IT" }
   );
11) db.users.deleteOne( { "_id" : ObjectId("563237a41a4d68582c2509da") } );
12) db.orders.deleteOne( { "expiryts" : { $lt: ISODate("2015-11-01T12:40:15Z") } } );
13) db.orders.deleteMany( { "client" : "tcs" } );

CRUD operations create, read, update, and delete documents.
Create Operations
Create or insert operations add new documents to a collection. If the collection does not currently exist, insert operations will create the collection.

MongoDB provides the following methods to insert documents into a collection:
db.collection.insertOne() New in version 3.2
db.collection.insertMany() New in version 3.2

In MongoDB, insert operations target a single collection. All write operations in MongoDB are atomic on the level of a single document.
For examples, see Insert Documents.

Read Operations
Read operations retrieves documents from a collection; i.e. queries a collection for documents. MongoDB provides the following methods to read documents from a collection:
You can specify query filters or criteria that identify the documents to return.
For examples, see:

Update Operations
Update operations modify existing documents in a collection. MongoDB provides the following methods to update documents of a collection:
db.collection.updateOne() New in version 3.2
db.collection.updateMany() New in version 3.2
db.collection.replaceOne() New in version 3.2
In MongoDB, update operations target a single collection. All write operations in MongoDB are atomic on the level of a single document.
You can specify criteria, or filters, that identify the documents to update. These filters use the same syntax as read operations.
For examples, see Update Documents.

Delete Operations
Delete operations remove documents from a collection. MongoDB provides the following methods to delete documents of a collection:
db.collection.deleteOne() New in version 3.2
db.collection.deleteMany() New in version 3.2
In MongoDB, delete operations target a single collection. All write operations in MongoDB are atomic on the level of a single document.
You can specify criteria, or filters, that identify the documents to remove. These filters use the same syntax as read operations.
For examples, see Delete Documents


Monday, 30 April 2018

Some imporant oracle interview queries


1.Query to find Second Highest Salary of Employee?
Select distinct sal from emp e1 where 2=(Select count(distinct sal) from emp e2 where e1.sal<=e2.sal);
or
select min(sal) from (Select distinct(Sal) from Emp order by sal desc) where rownum<=2;
or
select * from (Select Dense_Rank() over (order by  sal desc) as Rnk,E.* from Emp E) where Rnk=2;

2.Query to find duplicate rows in table?

select *from emp where rowid not in(select min(rowid) from emp group by sal);
or
Select * from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

3.How to fetch  monthly Salary of Employee if annual salary is given?
Select ename,Sal/12 as "MonthlySalary" from emp;

4.What is the Query to fetch first record from Employee table?
Select * from Emp where Rownum =1;

5.What is the Query to fetch last record from the table?
Select * from Emp where Rowid =(select max(Rowid) from Emp);

6.What is Query to display first 5 Records from Employee table?
Select * from emp where Rownum <= 5;

7.What is Query to display last 5 Records from Employee table?

select * from (Select * from emp order by rowid desc) where rownum <=5;

8.What is Query to display Nth Record from Employee table?
Select * from emp where rownum = &5;

9.How to get 3 Highest salaries records from Employee table?
select distinct sal from emp a where 3 >= (select count(sal) from emp b where a.sal <= b.sal) order by a.sal desc;

10.How to Display Odd rows in Employee table?
Select * from(Select rownum as rno,E.* from Emp E) where Mod(rno,2)=1;

11.How to Display Even rows in Employee table?
Select * from(Select rownum as rno,E.* from Emp E) where Mod(rno,2)=0;

12.How Can i create table with same structure of Employee table?

Create table employee2 as Select * from emp where 1!=2;

13.How Can i create table with same structure with data of Employee table?
Create table Employee3 as select * from Employee1 where 1=2;

14.How do i fetch only common records between 2 tables.
Select * from Emp
intersect
Select * from Employee1

15.Find Query to get information of Employee where Employee is not assigned to the department.
Select * from Employee1 where Deptno Not in(Select Deptno from Employee1);

16.How to get distinct records from the table without using distinct keyword.
select * from Employee1 a where  rowid = (select max(rowid) from Employee1 b where  a.empno=b.Empno);

17.Select all records from Employee1 table whose name is 'SMITH' and 'SCOTT'
Select * from Employee1 where ename in('SMITH' ,'SCOTT');

18.Select all records from Employee1 table whose name not in 'SMITH' and 'SCOTT'


Select * from Employee1 where ename not in('SMITH' ,'SCOTT');

✅ 16) Left Outer Join vs Left Join

There is no difference between LEFT JOIN and LEFT OUTER JOIN — both are the same.
They return all records from the left table and matching rows from the right table.
If no match exists on the right side, NULL is returned.
The keyword OUTER is optional and usually omitted.


✅ 17) What is Stored Procedure

Stored Procedure is a precompiled block of SQL code stored in the database.
It can include logic like IFLOOP, and INSERT/UPDATE/SELECT.
It accepts input/output parameters and improves performance and security.
Stored procedures are often used for reusable and complex business logic in the DB.


✅18) Stored Procedure vs Function

FeatureStored ProcedureFunction
Return0 or many (via OUT/SELECT)Exactly 1 value (scalar/table)
Used in SELECT❌ No✅ Yes
DML Operations✅ Allowed⚠️ Limited/Not recommended
Transaction Control✅ Yes❌ No
Use CaseLogic + data operationsReusable expressions/calculations


19) When I update a record in the Employee (child) table, should something be updated in the Department (parent) table automatically in the MS SQL database?
employee table is having empId(primary key), empName,esal,departmet(foreign key) And department table is having departmentId(primary key),departmentName.

In MS SQL Server, just like in other relational databases, the default behavior is:

No — updating a record in the Employee (child) table does NOT automatically update the Department (parent) table.

๐Ÿง  Why?

  • Foreign key relationships in SQL Server only support automatic behavior from parent to child, like:

    • ON DELETE CASCADE

    • ON UPDATE CASCADE (on primary/foreign key values)

  • Child-to-parent automatic updates are never automatic.

    • SQL Server does not support “reverse cascading” or upward updates.

    • If you want this behavior, you must define it explicitly using a trigger.

✅ Solution: Use a Trigger in MS SQL Server

๐ŸŽฏ Example Scenario:

Whenever an employee is updated (e.g., name or salary), we want to update a field in the Department table — such as LastModified timestamp.


๐Ÿ›  Step-by-Step:

1. Add a LastModified column to the Department table

sql
ALTER TABLE Department ADD LastModified DATETIME;

2. Create a trigger on Employee

sql
CREATE TRIGGER trg_UpdateDepartmentOnEmployeeUpdate ON Employee AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE Department SET LastModified = GETDATE() WHERE DepartmentId IN ( SELECT DISTINCT department FROM Inserted ); END;

๐Ÿงพ Explanation:

  • Inserted is a special table in triggers in SQL Server containing the new rows.

  • This trigger:

    • Fires after an update on Employee

    • Updates LastModified of the related department(s) that the modified employees belong to

✅ Summary

QuestionAnswer
Does SQL Server auto-update parent when child is updated?❌ No
Can you make it happen?✅ Yes, using a trigger
Is it supported via foreign key directly?❌ No
Best method in SQL Server?AFTER UPDATE trigger