SQL remember points

 

1. If we give an after where condition, right (city, 2) search will provide an answer according to the last two characters.

 Example:  Select distinct CITY from STATION where left(CITY, 1) not in ('a','e','i','o','u',' A','E','I','O','U') and right(CITY, 1) not in ('a','e','i','o','u','A','E','I','O','U');

Here it will search and give an answer, is the city name from the left first character not starting with following and city name right means from last 1 means first character is not starting with following.

2. If we want to search for one character within the keyword, like with _ and % can be used if it is more than one character, go for In keyword 

3. If there are more than 3 tables and you don't know how to join or perform a query, remember the ER relationship. Try to visualize each table use case, relate them to each other, and practice accordingly.

4. Go to dbdiagram.io - Database Relationship Diagrams Design Tool website to get the relationship between the tables.

5. To make the advanced joins, if we want to join more than three tables. We have to think like submission_stats joined on view_stats.  view_stats joined on challenges. challenges joined on the college. But in the query it looks like bottom to top approach.

SELECT

    con.contest_id,

    con.hacker_id, 

    con.name, 

    SUM(total_submissions) AS total_submissions, 

    SUM(total_accepted_submissions) AS total_accepted_submissions, 

    SUM(total_views) AS total_views, 

    SUM(total_unique_views) AS total_unique_views

FROM

    contests con 

JOIN

    colleges col ON con.contest_id = col.contest_id 

JOIN

    challenges cha ON col.college_id = cha.college_id 

LEFT JOIN

    (SELECT challenge_id, 

            SUM(total_views) AS total_views, 

            SUM(total_unique_views) AS total_unique_views

     FROM view_stats 

     GROUP BY challenge_id) vs 

    ON cha.challenge_id = vs.challenge_id 

LEFT JOIN

    (SELECT challenge_id, 

            SUM(total_submissions) AS total_submissions, 

            SUM(total_accepted_submissions) AS total_accepted_submissions 

     FROM submission_stats 

     GROUP BY challenge_id) ss 

    ON cha.challenge_id = ss.challenge_id

GROUP BY

    con.contest_id, con.hacker_id, con.name

HAVING

    SUM(total_submissions) != 0 OR 

    SUM(total_accepted_submissions) != 0 OR

    SUM(total_views) != 0 OR

    SUM(total_unique_views) != 0

ORDER BY

    contest_id;

6. Subqueries can be used within SQL joins to filter, aggregate, or manipulate data before joining. For example, you can use a subquery to find customers who have spent the most in each product category before joining them with other tables.