SQL SELECT with WHERE, AND and OR: ISC Class 12 Accounts DBMS

Understand SQL: SELECT, WHERE, AND and OR

SELECT column1, column2 FROM table_name WHERE condition;

SQL: SELECT, WHERE, AND and OR: key facts

Basic query

• •SELECT columns
• •FROM table
• •WHERE condition;

Order of clauses is fixed.

Comparison operators

• •= equal
• •<> not equal
• •< less
• •> greater
• •<= at most
• •>= at least

Text and dates in single quotes.

Logical operators

• •AND: both true
• •OR: at least one true
• •AND before OR

Brackets override the order.

Calculated column

An expression in the SELECT list, such as quantity multiplied by rate | AS gives the result a heading

The multiplication sign in SQL is the star.

How to answer SQL: SELECT, WHERE, AND and OR questions

1. 1Identify the columns the question wants to see: that is the SELECT list.
2. 2Name the table in FROM.
3. 3Write each condition on its own and join them with AND or OR.
4. 4Add brackets wherever AND and OR are mixed.
5. 5Check the query row by row against the sample data to find the output.

SQL: SELECT, WHERE, AND and OR in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

SELECT ItemName FROM Stock WHERE Category = 'Stationery' AND Rate > 40;

Tricks to remember SQL: SELECT, WHERE, AND and OR

Short memory aids. Say each one aloud once, then use it on a question the same day.

• S-F-W

  SELECT what, FROM where, WHERE which rows.

  Use it when: Clause order.

• AND narrows, OR widens

  AND gives fewer rows; OR gives at least as many.

  Use it when: Predicting results.

• AND first, then OR

  Like multiplication before addition, AND binds tighter than OR.

  Use it when: Precedence.

SQL: SELECT, WHERE, AND and OR: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

SELECT ItemName, Rate FROM Stock WHERE Category = 'Furniture';

SELECT Name, Salary FROM Staff WHERE (Dept = 'Accounts' OR Dept = 'Sales') AND Salary > 40000;

Where students lose marks in SQL: SELECT, WHERE, AND and OR

The slips examiners see most often, and what to write instead.

• ✗Mistake: Missing quotes around text

  Treating text as a column name.

  Wrong working

  WHERE Category = Furniture

  Right working

  WHERE Category = 'Furniture'

  ✓Do this: Single quotes around text and dates.

  Usually costs: 1 mark.

• ✗Mistake: Mixing AND and OR without brackets

  Reading the query left to right.

  Wrong working

  Category = 'Furniture' OR Category = 'Electronics' AND Rate > 5000 meant as one group

  Right working

  (Category = 'Furniture' OR Category = 'Electronics') AND Rate > 5000

  ✓Do this: Bracket the OR group.

  Usually costs: 1-2 marks.

• ✗Mistake: Using < instead of <=

  Careless with the boundary.

  Wrong working

  Qty < 30 expected to include Mouse

  Right working

  Qty <= 30 includes Mouse (Qty is exactly 30)

  ✓Do this: Check the boundary rows.

  Usually costs: 1 mark.

• ✗Mistake: Writing columns in a wrong order in the output

  Copying the table instead of the SELECT list.

  Wrong working

  Output ItemName, Qty, Rate for SELECT ItemName, Qty * Rate

  Right working

  Output columns exactly as listed after SELECT

  ✓Do this: Follow the SELECT list.

  Usually costs: 1 mark.

Practice questions on SQL: SELECT, WHERE, AND and OR

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   The query SELECT * FROM Staff; returns:
   (a) only the first row
   (b) all columns of all rows
   (c) only the column names
   (d) an error

   Hint

   The star means all columns.

   Answer

   (b) all columns of all rows

2. Q2 · 3 marks · Short answerBoard level

   Using the Staff table, write a query to display the names of staff in the Sales department, and give the output. (3 marks)

   Hint

   Text is in single quotes.

   Answer

   SELECT Name FROM Staff WHERE Dept = 'Sales';

   Output: Beena, Eshan.

3. Q3 · 3 marks · Short answerHarder board level

   Write a query to display the name and salary of staff whose salary is at least ₹36,000 and at most ₹45,000, and give the output. (3 marks)

   Hint

   Two conditions joined with AND.

   Answer

   SELECT Name, Salary FROM Staff WHERE Salary >= 36000 AND Salary <= 45000;

   Name	Salary
   Anil	42000
   Beena	38000
   Fiza	36000

4. Q4 · 6 marks · Long answerHarder board level

   For the Staff table, write each query and give its output: (a) names of Accounts staff earning less than ₹40,000; (b) name and salary of staff in Stores or earning more than ₹50,000; (c) name and yearly salary (salary × 12) of Sales staff; (d) names of staff not in Accounts. (6 marks)

   Hint

   <> means not equal.

   Answer

   (a) SELECT Name FROM Staff WHERE Dept = 'Accounts' AND Salary < 40000; Output: Fiza.

   (b) SELECT Name, Salary FROM Staff WHERE Dept = 'Stores' OR Salary > 50000;

   Name	Salary
   Chetan	55000
   Deepa	30000

   (c) SELECT Name, Salary * 12 AS Annual FROM Staff WHERE Dept = 'Sales';

   Name	Annual
   Beena	456000
   Eshan	564000

   (d) SELECT Name FROM Staff WHERE Dept <> 'Accounts'; Output: Beena, Deepa, Eshan.

5. Q5 · 3 marks · Short answerHOTS / competency

   Predict the output of (i) SELECT Name FROM Staff WHERE Dept = 'Sales' OR Dept = 'Stores' AND Salary > 40000; and (ii) SELECT Name FROM Staff WHERE (Dept = 'Sales' OR Dept = 'Stores') AND Salary > 40000; and explain why they differ. (3 marks)

   Hint

   Beena earns 38,000.

   Answer

   (i) AND first: Stores staff over 40000 (none) OR all Sales staff: Beena, Eshan. (ii) Brackets: only Sales or Stores staff earning above 40000: Eshan. The brackets apply the salary test to both departments, so Beena (38,000) is dropped.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.Which clause filters rows?Show answer

   WHERE.

2. 2.What does SELECT * mean?Show answer

   Select all columns.

3. 3.Which is evaluated first, AND or OR?Show answer

   AND.

4. 4.How are text values written in SQL?Show answer

   In single quotes.

5. 5.Which operator means not equal?Show answer

   <>.

SQL: SELECT, WHERE, AND and OR: frequently asked questions