Source table

to select all:

= QUERY(A1:D5, "select *")

or

= QUERY(A1:D5, "select A, B, C, D")

or convert data range into array and use this formula:

= QUERY({A1:D5}, "select Col1, Col2, Col3, Col4")

To sort by column D with "order by":

=QUERY("A1:D6","select * order by D desc",1)

To only return "pencil" data:

=QUERY("A1:D6","select * where B='pencil' ",1)

To only return rows that contain "pen" (all rows):

=QUERY("A1:D6","select * where B contains 'pen' ",1)

To only return rows where the price is greater than 0:

=QUERY("A1:D6","select * where D>0 ",1)

Note that text strings require apostrophes while numerical values do not.

=QUERY(QUERY(A1:D6,"select C,SUM(D) group by C",1),"select Col2>0",1)