How Can This SQL Be Wrong? What am I Not Seeing

What am I getting wrong in this SQL query?

add GROUP BY CAST(posted_at AS DATE) after WHERE clause and before HAVING clause. Also, check your WHERE clause. You are likely missing the last couple of days.

SELECT CAST(posted_at AS DATE) AS post_day
FROM posts p
WHERE posted_at > '2020-10-01' AND posted_at < '2020-11-01'
GROUP BY CAST(posted_at AS DATE)
HAVING COUNT(post_day)
ORDER BY COUNT(post_day) DESC

Query problem-getting wrong result when a condition or a set of data included

At the very least, you should avoid the way you update the table. You should be carefull with joins on update. If you happen to have more than one value for the same row, the result is not deterministic. Better use this form:

update table1
set table1.grade = (SELECT TOP 1 table3.grade FROM table3
                    WHERE table3.value < table1.max
                    ORDER BY table3.value DESC)

Something is Wrong with my SQL Query NOT EXISTS

You need a correlated subquery:

SELECT UserID
FROM @SelectedID si
WHERE NOT EXISTS (SELECT 1
                  FROM @SelectedIdValue siv
                  WHERE si.UserId = siv.UserId
                 );

Your version of the query simply returned false for all rows. The subquery returns a value, so something exists.

EDIT:

You can phrase this as a left outer join if you want:

SELECT si.UserID
FROM @SelectedID si LEFT OUTER JOIN
     @SelectedIdValue siv
     ON si.UserId = siv.UserId
WHERE siv.UserId IS NULL;

SQL NOT IN not working

SELECT foreignStockId
FROM   [Subset].[dbo].[Products]

Probably returns a NULL.

A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values. You can explicitly exclude them using IS NOT NULL as below.

SELECT stock.IdStock,
       stock.Descr
FROM   [Inventory].[dbo].[Stock] stock
WHERE  stock.IdStock NOT IN (SELECT foreignStockId
                             FROM   [Subset].[dbo].[Products]
                             WHERE  foreignStockId IS NOT NULL)

Or rewrite using NOT EXISTS instead.

SELECT stock.idstock,
       stock.descr
FROM   [Inventory].[dbo].[Stock] stock
WHERE  NOT EXISTS (SELECT *
                   FROM   [Subset].[dbo].[Products] p
                   WHERE  p.foreignstockid = stock.idstock)

As well as having the semantics that you want the execution plan for NOT EXISTS is often simpler as looked at here.

The reason for the difference in behaviour is down to the three valued logic used in SQL. Predicates can evaluate to True, False, or Unknown.

A WHERE clause must evaluate to True in order for the row to be returned but this is not possible with NOT IN when NULL is present as explained below.

'A' NOT IN ('X','Y',NULL) is equivalent to 'A' <> 'X' AND 'A' <> 'Y' AND 'A' <> NULL)

'A' <> 'X' = True
'A' <> 'Y' = True
'A' <> NULL = Unknown

True AND True AND Unknown evaluates to Unknown per the truth tables for three valued logic.

The following links have some additional discussion about performance of the various options.

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
Left outer join vs NOT EXISTS
NOT EXISTS vs NOT IN

SQL Query giving wrong output

You could use a UNION ALL with M_CAST where PID is NULL as per your requirement

                         WITH
                            k AS
                            (SELECT MC.MID a
                            FROM M_CAST MC
                            JOIN PERSON P ON TRIM(P.PID) = TRIM(MC.PID)
                            WHERE TRIM(P.GENDER) IN ('Male', 'None')
                            UNION ALL
                            SELECT MC.MID a
                            FROM M_CAST MC
                            WHERE MC.PID IS NULL 
                              )

                            SELECT CAST(SUBSTR(M.year,-4) AS UNASSIGNED) Year, 
                            COUNT(DISTINCT TRIM(MID)) number_of_movies
                            FROM MOVIE M
                            WHERE TRIM(MID) NOT IN (SELECT a FROM k) 
                            GROUP BY CAST(SUBSTR(M.year,-4) AS UNASSIGNED)
                            ORDER BY Year

Facing an error : table or view does not exist

Justin's answer is correct but let me expand a bit.

Everyone who said that the table doesn't exist didn't read your whole post. Since you are able to:

If I run the part of SP from query editor it works fine

Obviously the table is there.
Obviously you have some access to it. Otherwise this wouldn't work when it clearly does.

but when I execute the entire SP it throws an error.

This is because Oracle distinguishes between permissions granted directly and those granted via a role.

Say I do this:

Create Table TABLE_A
Create Role READ_ONLY
Grant Select on TABLE_A to READ_ONLY
Grant READ_ONLY to VIJAY

In a SQL Window/prompt you could query that table without issue. So now you need to create a view

Create VIJAY.VIEW_A as SELECT * FROM TABLE_A

You'll get the error that TABLE_A does exist. Because a view is compiled, like a procedure it runs without any roles. Since it runs without the READ_ONLY role, it's blind to the fact that TABLE_A exists. Now what I need to do is

Grant Select on TABLE_A to VIJAY.

Now that you have a direct permission, you can compile a view or procedure/package that uses that table.

Error: Could not use view or function because of binding errors

It sounds like the view was created and then one of the underlying tables was changed. I.e., ProductCategoryL2Name no longer exists or was renamed. You can try this to get the view's definition, but the sys tables might be locked down. Your best bet is to go talk to whoever owns the database and ask them to fix it (which can be quite a rabbit hole in large organizations or on consulting gigs).

SELECT sm.definition
FROM [YourDB].sys.sql_modules AS sm  
    JOIN [YourDB].sys.objects AS o 
        ON sm.object_id = o.object_id  
WHERE sm.object_id = OBJECT_ID('YourDB.dbo.ViewName')