Student database using MySQL Part 5

EXAMPLE FOR JOINS

        Will have an example to combine one or more tables.

 

SELF JOIN:

        SELECT stud1.name AS Student1, stud2.name AS Student2, stud1.district FROM Students stud1, Students stud2 WHERE stud1.rollno <> stud2.rollno AND stud1.district = stud2.district ORDER BY stud1.district;

    Create two Alias for the Student table - stud1 and stud2.

    This query returns the name of the Student and district if the students are in the same district.

Note: [<> operator is used for not equal condition]

INNER JOIN:

    SELECT Students.name, Department.hod, Department.name FROM Students INNER JOIN Department ON Students.department=Department.id;

    Returns records that have matching values in both tables.

CASE:

• The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
• If there is no ELSE part and no conditions are true, it returns NULL.

        SELECT name, fees, 

        CASE 

        WHEN fees > 50000 THEN 'Fees greater than 50000' 

        WHEN fees = 50000 THEN 'Fees is 50000'

        ELSE 'Fees is less than 50000'

        END AS FeesQuote

        FROM Students;

COUNT():

    SELECT COUNT(district) FROM Student;

    SELECT COUNT(district) FROM Student WHERE district='Madurai';

    

    It returns the count of the district from the table.

DROP TABLE:

        DROP TABLE Students;

        This statement drops the Student table.

DROP DATABASE:

        DROP DATABASE Students;

        This statement drops the database.

 Thanks for choosing our blog 😊. 

Tutorial Topics for examples:

Database Creation -  Part-1

Table Modifications  -  Part-2

Clauses   - Part-3

Operators  - Part-4

Joins  - Part-5