Student database using MySQL Part 4

-

EXAMPLE FOR MYSQL OPERATORS

        Will have an example to see how the operators work with the table records.

 

AND, OR, NOT OPERATORS:

    SELECT * FROM Students WHERE department=2 OR district='Madurai';

    It returns the Student records if the student department is 2 or the student has a district in Madurai. It returns True if any of the conditions are satisfied.


    SELECT * FROM Students WHERE department= 2 AND district='Madurai';

     It returns the Student records if the student department is 2 and the student has a district in Madurai. It returns True if both conditions are satisfied.


    SELECT * FROM Students WHERE NOT district='Madurai' ORDER BY district DESC;

     It returns all the Student records except the student who has a district in Madurai.


    The queries filter the records from the Student table based on the conditions given.

  • The AND operator displays a record if all the conditions separated by AND are TRUE.
  • The OR operator displays a record if any of the conditions separated by OR is TRUE.
  • The NOT operator displays a record if the condition(s) is NOT TRUE.




LIKE:

    Like is used to filter the records within the specific pattern.

    SELECT * FROM Students WHERE district LIKE 'Ch%';  

Examples:

        SELECT * FROM Students WHERE district LIKE 'Ch%';  - It finds the district starts with the letter Ch

        SELECT * FROM Students WHERE district LIKE '%ai';  - It finds the district ends with the letter ai

        SELECT * FROM Students WHERE district LIKE '%an%';  - It finds the district with the letter 'an' in any position

        SELECT * FROM Students WHERE district LIKE 'C%i';  - It finds the district starts with the letter C and ends with the letter i

        SELECT * FROM Students WHERE district LIKE 'a_';  - It finds the district starts with the letter a and it has at least 2 characters in length

        SELECT * FROM Students WHERE district LIKE '_h';  - It finds the district with the letter h in the second position



IN:

    SELECT * FROM Students WHERE district IN ('Madurai', 'Trichy', 'Chennai');

    The query filters the record from the Student table with multiple conditions.

     It returns the Student records if the student who has a district in Madurai, Trichy, and Chennai.




BETWEEN:

    SELECT * FROM Students WHERE fees BETWEEN 60000 AND 80000;

    Query filter the records within the fee range given.

    It returns the Student records if the student pays fees within the range of 60000 to 80000.

    



EXISTS:

    SELECT * FROM Department WHERE EXISTS (SELECT department FROM Students WHERE Students.department = Department.id AND fees < 70000);

    The EXISTS operator is used to test for the existence of any record in a subquery.


ANY and ALL:

    SELECT name, department, district FROM Students WHERE department = ANY (SELECT id FROM Department WHERE location = 'Block1');

    SELECT name, department, district FROM Students WHERE department = ALL (SELECT id FROM Department WHERE location = 'Block1');

    It returns the data from the Students table if the subquery satisfies the condition.

    The ANY operator:

  • returns a boolean value as a result
  • returns TRUE if ANY of the subquery values meet the condition
    
    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




 Thanks for choosing our blog 😊. 
Will discuss an example of MySQL joins in the next part. -  Part-5


Tutorial Topics for examples:
Database Creation -  Part-1
Table Modifications  -  Part-2
Clauses   - Part-3
Operators  - Part-4
Joins  - Part-5

Comments

Post a Comment

Popular posts from this blog

MySQL Tutorial Part 1

-
Image
MYSQL: MySQL is a very popular open-source relational database management system. It is very easy to use. MySQL is one of the most straightforward database technology to learn and use. To add, access, and process data stored in a database. SQL: SQL stands for Structured Query Language SQL is used to communicate with the database. SQL lets you access and manipulate databases SQL keywords are NOT case sensitive: select is the same as SELECT SQL VS MYSQL: SQL MYSQL SQL is a query programming language that manages RDBMS. MySQL is a relational database management system that uses SQL. SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way. RDBMS: RDBMS stands for Relational Database Management System. RDBMS is a program used to maintain a relational ...
Read more

Pandas DataFrame empty

-
Image
HOW TO CHECK IF THE DATAFRAME IS EMPTY OR NOT? Check if the DataFrame is empty or not by using the  DataFrame.empty .  It returns the Boolean value. If the DataFrame is empty, then it returns True . If the DataFrame is not empty, then it returns False . CREATE A EMPTY DATAFRAME: Create an empty dataframe and will check if it is empty or not by using the property DataFrame.empty If it is empty, then it returns True. import  pandas as pd  empty_dataframe = pd.DataFrame() if empty_dataframe.empty:       print ('True') CREATE A NON-EMPTY DATAFRAME: Create a non-empty dataframe and will check if the dataframe is empty or not.  If it is not empty, then it returns False. import  pandas as pd  pd_dataframe= pd.DataFrame( [['Vetri',20],['Vishal',21],['Jack',18]],   columns=['Name', 'Age'] ) if  pd_dataframe .empty is False:      print ('False') Thanks for choosing our blog 😊. 
Read more

Introduction to Java

-
Image
JAVA      James Gosling Invented Java in 1991 and it is developed by Sun Microsystems. But the first version was released in 1995. The current version of Java is Java 17.  Java is an Object Oriented Programming language and it is platform-independent ( Write once and run anywhere ).  Java is easy to compile, and debug and it provides reusability of code that makes development faster.  The following four are important to run, compile and debug the Java application   JDK - Java Development Kit   JRE - Java Runtime Environment  JVM - Java Virtual Machine  Compiler - Java Compiler.    JDK           It is used to provide tools to develop Java applications it comes with Compiler, JRE, and JVM.  COMPILER  Java is the compiler used for compilation.  The compiler will check the program syntax and if it is wrong then throw the error at compile time.   If we don't hav...
Read more