How do different kind of brackets play role in SQL queries?

In this case, they are the same output, because there are only two expressions. Parentheses are used to group expressions for boolean and mathematical subexpressions. This is the same use as in mathematics and other programming languages.

When you mix AND and OR in boolean expressions, then you should use parentheses -- even if not necessary, they help readability.

The use of {, }, [, and ] is specific to the database. These are not standard parts of SQL. They do not group expressions. For instance, [ and ] is used to escape qualifiers (column and table names). This is included in SQLite for compatibility with SQL Server.

In this instance, yes they will give the same output There are technically two predicates in your where clause. Year between 1990 and 2000 is considered one predicate while the genere=comedy is considered another. When two predicates are usually involved, paranthesis may not matter.

However lets take an example of the following queries

select * from sometable where (somecolumn1=x or somecolumn2=y) and somecolumn3=zselect * from sometable where somecolumn1=x or (somecolumn2=y and somecolumn3=z)

The result will be different for the above case as they will be evaulated differently based on which condition is true or false.