CODEWITHSUNDEEP
sqloracle-database
Tenkin
Tenkin

Reputation: 25

Order of Procedure for Select statement with Where Clause and other operators

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:

  1. Parentheses
  2. */
  3. +-
  4. = <= >= < >
  5. IS
  6. Between
  7. NOT
  8. AND
  9. 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:

  1. QtyA+QtyB
  2. QtyA+QtyB < 5
  3. gender = 'F'
  4. gender = 'M'
  5. gender = 'M' AND QtyA+QtyB < 5
  6. gender = 'F' OR gender = 'M'

My logic that is probably wrong is:

  1. Since no parentheses it does the addition first - QtyA+QtyB
  2. Then it does < operator as it's the next one on that list given to me < 5.
  3. Then it does the = which is gender = 'F'
  4. Then gender = 'M' as it's the next equal
  5. Then it does the AND operator so gender = 'M' AND QtyA+QtyB
  6. Finally it does the OR operator then gender = '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.

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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.

Upvotes: 1

Related Questions