And ,Or operators sql videos in telugu 57

 https://youtu.be/Du2ASxqCNUY

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

The SQL ANY Operator

The ANY operator:

returns a boolean value as a result

returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

ANY Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name operator ANY

  (SELECT column_name

  FROM table_name

  WHERE condition);

-----------

The SQL ALL Operator

The ALL operator:

returns a boolean value as a result

returns TRUE if ALL of the subquery values meet the condition

is used with SELECT, WHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all values in the range. 

ALL Syntax With SELECT

SELECT ALL column_name(s)

FROM table_name

WHERE condition;

ALL Syntax With WHERE or HAVING

SELECT column_name(s)

FROM table_name

WHERE column_name operator ALL

  (SELECT column_name

  FROM table_name

  WHERE condition);

create table student( sname varchar(20), sage int);

create table faculty( fname varchar(20), fage int);

insert into faculty values("Venkat",60),("ramesh",33)

,("lakshman",38) ,("praveen",40),("sreenu",42)

,("naveen",39);

insert into student values("salman",20),("tinku",33)

,("karthik",50) ,("rajesh",28),("mallik",22)

,("ravi",41);

---------

select sname from student where sage > any

(select fage from faculty);

insert into student values("salman1",62),("tinku1",62);

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

Warning: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe: ignoring option

'--no-beep' due to invalid value ''

Enter password: ********

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use vlrinst;

Database changed

mysql> create table student( sname varchar(20), sage int);

Query OK, 0 rows affected (0.24 sec)

mysql> create table faculty( fname varchar(20), fage int);

Query OK, 0 rows affected (0.48 sec)

mysql> insert into faculty values("Venkat",30),("ramesh",33)

    -> ,("lakshman",38) ,("praveen",40),("sreenu",42)

    -> ,("naveen",39);

Query OK, 6 rows affected (0.05 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from faculty;

+----------+------+

| fname    | fage |

+----------+------+

| Venkat   |   30 |

| ramesh   |   33 |

| lakshman |   38 |

| praveen  |   40 |

| sreenu   |   42 |

| naveen   |   39 |

+----------+------+

6 rows in set (0.00 sec)

mysql> insert into student values("salman",20),("tinku",33)

    -> ,("karthik",50) ,("rajesh",28),("mallik",22)

    -> ,("ravi",41);

Query OK, 6 rows affected (0.03 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student;

+---------+------+

| sname   | sage |

+---------+------+

| salman  |   20 |

| tinku   |   33 |

| karthik |   50 |

| rajesh  |   28 |

| mallik  |   22 |

| ravi    |   41 |

+---------+------+

6 rows in set (0.00 sec)

mysql> select sname from student where sage > any

    -> (select fage from faculty);

+---------+

| sname   |

+---------+

| tinku   |

| karthik |

| ravi    |

+---------+

3 rows in set (0.00 sec)

mysql> select sname from student where sage > all

    -> (select fage from faculty);

+---------+

| sname   |

+---------+

| karthik |

+---------+

1 row in set (0.00 sec)

mysql> select sname from student where sage >

    -> all (select fage from faculty);

+---------+

| sname   |

+---------+

| karthik |

+---------+

1 row in set (0.00 sec)

mysql> select sname from student where sage >

    -> any (select fage from faculty);

+---------+

| sname   |

+---------+

| tinku   |

| karthik |

| ravi    |

+---------+

3 rows in set (0.00 sec)

mysql> insert into faculty values("Venkat",60);

Query OK, 1 row affected (0.15 sec)

mysql> select sname from student where sage >

    -> all (select fage from faculty);

Empty set (0.00 sec)

mysql> insert into student values("salman1",62),("tinku1",62);

Query OK, 2 rows affected (0.07 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select sname from student where sage >

    -> any (select fage from faculty);

+---------+

| sname   |

+---------+

| tinku   |

| karthik |

| ravi    |

| salman1 |

| tinku1  |

+---------+

5 rows in set (0.00 sec)

mysql> select sname from student where sage >

    -> all (select fage from faculty);

+---------+

| sname   |

+---------+

| salman1 |

| tinku1  |

+---------+

2 rows in set (0.00 sec)

mysql>