This is the table I’m working off:
CREATE TABLE Person ( IdNo number (3), Name VARCHAR2(30), Gender VARCHAR2(3), QtyA number (3), QtyB number (3), Primary Key (IdNo) ); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (23,'Johnny Storm','M',10,10); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (46,'Eric Twinge ','M',7,4); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (17,'Diana Prince ','F',4,7); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (28,'Susan Storm ','F',1,3); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (134,'Dick Grayson ','m',6,2); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (5,'Barbara Gordon ','F',8,7); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (24,'Selina Kyle ','F',4,0); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (78,'Rick Hunt ','M',NULL,NULL); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (81,'Ted Howler ','M',6,6); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (89,'Sonita Marks ','F',3,6); INSERT INTO Person (IdNo, Name, Gender, QtyA, QtyB) VALUES (2,'Dave Orkle ','M',2,4);
From this order of procedure that was provided in class:
- Parentheses
- */
- +-
- = <= >= < >
- IS
- Between
- NOT
- AND
- OR
I have to work out how many rows of the following statement with show up:
SELECT Name FROM person WHERE gender = 'F' OR gender = 'M' AND QtyA+QtyB < 5
Is this statement read in this order:
- QtyA+QtyB
- QtyA+QtyB < 5
- gender = ‘F’
- gender = ‘M’
- gender = ‘M’ AND QtyA+QtyB < 5
- gender = ‘F’ OR gender = ‘M’
My logic that is probably wrong is:
- Since no parentheses it does the addition first –
QtyA+QtyB
- Then it does
<
operator as it’s the next one on that list given to me< 5
. - Then it does the
=
which is gender = ‘F’ - Then
gender = 'M'
as it’s the next equal - Then it does the
AND
operator sogender = 'M' AND QtyA+QtyB
- Finally it does the
OR
operator thengender = 'F' OR gender = 'M'
So 0 rows will show up after step 5
The next test apparently has a large section where you have to read Select statements and find how many rows will show from them without typing anything in and I feel like I’m doing this completely wrong, but I came up with 0 which is what the select statement gave me when I type it in.
Advertisement
Answer
You want to apply parentheses. The query as written is expressing:
WHERE (gender = 'F') OR (gender = 'M' AND (QtyA + QtyB) < 5)
That is because AND
has higher precedence than OR
. Then arithmetic operations are going to be evaluated before comparisons.