Subquery

§  Query with in another Query is known as subquery.

§  If you want to fetch data from one table but based on condition of another table then we     should go for Subquery.

§  Types of Subquery: -

1.     Single row Subquery (getting single value) (using = operator)

2.     Multi Sub Query (if query generating more than output value is called [In operator       uses]

v A subquery may occur in:

  §  A SELECT clause

     §  A FROM clause

     §  A WHERE clause

§   The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

§   A subquery is usually added within the WHERE Clause of another SQL     SELECT statement.

 §   You can use the comparison operators, such as >, <, or =. The comparison operator           can also be a multiple-row operator, such as IN, ANY, or ALL.

Execution Process-

§  Inner most Query To outermost Query except in the case of co-related query.

§  The inner query executes first before its parent query so that the results of an        inner  query can be passed to the outer query.

Que-

 In which case joins required and In Which case Subquery is required?

Ans-

§  If you want to fetch data from multiple table get placed one column from table and another column from second table i.e. if you want to fetch two columns from two table definitely, we should use joins.

§  Subquery said if you are expecting the data from one table but based on condition of another table it is better to use subquery.

select DEPARTMENTNAME from tbldepartment where id=(select DEPARTMENTID from tblemployee where id=1 )

 

SELECT * FROM TBLEMPLOYEE

WHERE DEPARTMENTID IN (SELECT ID FROM TBLDEPARTMENT WHERE DEPARTMENTNAME='Payroll' OR DEPARTMENTNAME='IT')

Write a query to display senior most employee details from emp table.

select *
from emp

where hiredate=( select min(hiredate) from  emp)

Write an query to display junior most employee details from emp table.

select *
from emp

where hiredate=( select max(hiredate) from  emp)

Write a query to display highest sal details emp table
 
select *
from emp

where sal= (select max(sal) from emp)

Write a query to display Second highest sal details emp table.

select *
from emp

where sal= (select max(sal) from emp where sal< select max(sal) from emp))

Write a query to display highest paid deptno emp table.

select deptno from emp where sal=( select max(sal) from emp)

Write an query to display highest paid deptname dept emp table.

select dname from dept

where deptno = (select deptno from emp where sal= (select max(sal) from emp))

Write a query to display the employees who are working under 'BLAKE' from emp table

select * from emp

where mgr= (select empno from emp where ename='BLAKE')

Read more

Directives In Angular

  §  Directives help us is to enable us to give instructions to the Angular on how to render the templates to the DOM. So, they use directives to give instructions to Angular as it is rendering the templates on the screen.

    §  Angular templates are dynamic. When Angular renders them, it transforms the DOM according to the instructions given by directives. A directive is a class with a @Directive() decorator.

    §  There are two other kinds of directives in Angular, structural and attribute directives.

Structural Directives

§  structural directives are, they help us to alter the layout of our content by helping us to add and remove elements from the DOM.

§  So, using structural directives you add or remove HTML elements from the Dom.
§  There are three common built in structural directives namely
·        ngIf
·        ngSwitch

·        ngFor

ngIf

  §  Structural directives ngIf and ngSwitch which are used to conditionally render HTML elements

     §  Since ngIf is a structural directive it is prefixed with *.

    §  When you use ngIf in your template syntax you will write it as *ngIf

<div *ngIf="selectedItem">..</div>

§  For example, if you apply the *ngIf directive to a div like this in this example, what you're specifying where is that if the selectedItem is not null then this div will be added to the DOM. If that selectedItem is null, then this div, whatever is contained in this div will not be added to the DOM, so you are literally removing this from the DOM if the value turns out to be false. If it turns out to be true, then it is added into the DOM.

In our example we have <div> tag

open app.component.html file.

now we want to use ng-if directive on this div tag . so with-in opening tag use asterisk and then ngIf then we need to assign a true or false value .

let's begin with a straight forward value true this and take a look at the browser we can see gravity4Tech.blogspot.com

app.component.html

<mat-toolbar color="primary">

<div class="container" *ngIf="true">

<h1>gravity4Tech.blogspot.com</h1>

</div>

</mat-toolbar>

Here we are using angular material toolbar.

When we are working with angular material toolbar we need to import MatToolbarModule in app.module.ts file.

import { BrowserModule } from '@angular/platform-browser';

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

import { AppComponent } from './app.component';

import { BrowserAnimationsModule } from '@angular/platform-browser/animations';

import { MatToolbarModule } from '@angular/material/toolbar';

@NgModule({

declarations: [

AppComponent,

],

imports: [

BrowserModule,

BrowserAnimationsModule,

MatToolbarModule,

],

providers: [],

bootstrap: [AppComponent]

})

export class AppModule { }

Note:-

§  If angular material library is not added in your project then first add angular material library in your project by following commands.

ng add @angular/material

app.component.html

<mat-toolbar color="primary">

<div class="container" *ngIf="true">

<h1>gravity4Tech.blogspot.com</h1>

</div>

</mat-toolbar>

§  Save the project and run the project by the following command in terminal.

ng serve -o

Output

 

  §  let's back in our code I change true to false save this and take a look at the browser gravity4Tech.blogspot.com is not visible anymore.

<mat-toolbar color="primary">

<div class="container" *ngIf="false">

<h1>gravity4Tech.blogspot.com</h1>

</div>

</mat-toolbar>

§  Save the project and run the project by the following command in terminal.

ng serve -o

Output

  §  Setting true or false on the right-hand side is of not much let's assign a property  value now we create a new property call isDisplay name and set it true and assign it to the directive.

     app.component.ts

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

@Component({

selector: 'app-root',

templateUrl: './app.component.html',

styleUrls: ['./app.component.scss']

})

export class AppComponent {

isDisplay=true;

}

app.component.html

<mat-toolbar color="primary">

<div class="container" *ngIf="isDisplay">

<h1>gravity4Tech.blogspot.com</h1>

</div>

</mat-toolbar>

§  Save the project and check the output

*ngIf and Else: if statement with else

  §  When we are working with *ngIf directive can provide an expression which return boolean flag true of false to add or remove the element form DOM having *ngIf directive.if *nfIf directives returns false then we want to perfrom another task then you can use else also with *ngIf.

    §  *ngIf have the else statement with if to handle conditions

<ng-template>
§  The <ng-template> is an Angular element for rendering HTML.

§   It is never displayed directly. In fact, before rendering the view, Angular replaces the <ng-template> and its contents.

§  now save this and take a look at the browser you can see gravity4Tech.blogspot.com and if I change isDisplay to false gravity4Tech.blogspot.com is not displayed.

§  By making use of a property we can add or remove the element in the DOM by toggling property value between true and false.

Syntax:

<ng-container *ngIf="condition; else elseBlock">

Content to render when condition is true.

</ng-container>

<ng-template #elseBlock>

Content to render when condition is false.

</ng-template>

  §  let's take a look an example to see how to implement and else block for the ngIf directive.

<mat-toolbar color="primary">

<div class="container" *ngIf="isDisplay; else elseBlock">

<h1>gravity4Tech.blogspot.com</h1>

</div>

<ng-template #elseBlock>

<h1>Websited is Hidden</h1>

</ng-template>

</mat-toolbar>

 let's take a look at another Syntax that angular provide for the ng-if directive.

  §  In Angular 4.x.x You can use ngIf in four way to achieve simple if else procedure:

Just Use If

<div *ngIf="isValid">

If isValid is true

</div>

Using If with Else (Please notice to templateName)

<div *ngIf="isValid; else templateName">

If isValid is true

</div>

<ng-template #templateName>

If isValid is false

</ng-template>

Using If with Then (Please notice to templateName)

<div *ngIf="isValid; then templateName">

Here is never showing

</div>

<ng-template #templateName>

If isValid is true

</ng-template>

Using If with Then and Else

<div *ngIf="isValid; then thenTemplateName else elseTemplateName">

Here is never showing

</div>

<ng-template #thenTemplateName>

If isValid is true

</ng-template>

<ng-template #elseTemplateName>

If isValid is false

</ng-template>

Note:-

§  ngIf evaluates the expression and then renders the then or else template in its place when expression is truthy or falsy respectively

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

@Component({

selector: 'app-root',

templateUrl: './app.component.html',

styleUrls: ['./app.component.scss']

})

export class AppComponent {

isDone = true;

}

1) *ngIf

<div *ngIf="isDone">

It's Done!

</div>

<!-- Negation operator-->

<div *ngIf="!isDone">

It's Not Done!

</div>

2) *ngIf and Else

<ng-container *ngIf="isDone; else elseNotDone">

It's Done!

</ng-container>

<ng-template #elseNotDone>

It's Not Done!

</ng-template>

3) *ngIf, Then and Else

<ng-container *ngIf="isDone; then iAmDone; else iAmNotDone">

</ng-container>

<ng-template #iAmDone>

It's Done!

</ng-template>

<ng-template #iAmNotDone>

It's Not Done!

</ng-template>

  §  You can Use <ng-container> and <ng-template> for Achieve This

<ng-container *ngIf="isValid; then template1 else template2"></ng-container>

<ng-template #template1>

<div>Template 1 contains</div>

</ng-template>

<ng-template #template2>

<div>Template 2 contains </div>

</ng-template>

Just add new updates from Angular 8.

1.     For case if with else, we can use ngIf and ngIfElse.

<ng-template [ngIf]="condition" [ngIfElse]="elseBlock">

Content to render when condition is true.

</ng-template>

<ng-template #elseBlock>

Content to render when condition is false.

</ng-template>

2.     For case if with then, we can use ngIf and ngIfThen.

<ng-template [ngIf]="condition" [ngIfThen]="thenBlock">

This content is never showing

</ng-template>

<ng-template #thenBlock>

Content to render when condition is true.

</ng-template>

3.     For case if with then and else, we can use ngIf, ngIfThen, and ngIfElse.

<ng-template [ngIf]="condition" [ngIfThen]="thenBlock" [ngIfElse]="elseBlock">

This content is never showing

</ng-template>

<ng-template #thenBlock>

Content to render when condition is true.

</ng-template>

<ng-template #elseBlock>

Content to render when condition is false.

</ng-template>

Read more