Question

The database for a pet supply company includes the following table, named tblCollar, that contains product data for pet collars. The ItemDesc and Color fields contain text. The ItemNum, Price, and Quantity fields contain numbers.

ItemNum	ItemDesc	Color	Price	Quantity
2358	leather studded collar	black	30.00	35
2693	leather collar	brown	25.00	25
3547	striped collar	red	20.00	75
3855	striped collar	blue	15.00	42
3764	striped collar	green	15.00	48
5782	solid collar	pink	12.00	36
5785	solid collar	red	12.00	10
5787	solid collar	blue	10.00	15

Which of the following clauses would allow a user to select the records for collars with a cost of between 10 and 20?

	a.	WHERE Price >= @Price1 AND Price <= @Price2
	b.	WHERE Price LIKE 10 AND Price LIKE 20
	c.	WHERE Price <= 10 AND Price >= 20
	d.	WHERE @Price1 < Query  < @Price2

Answer 1

Option a is correct answer

Reason:- In where clause the price is is checkd between @price1 and @price2. So, first weset the value in variable

and run the select statement to display . Check below code:-

CREATE TABLE tblCollar(itemno integer PRIMARY KEY, itemdes text,color text, price integer,quqntity integer);

/* insert record */
INSERT INTO tblCollar VALUES(2358,'lether studded collar','black',30.00,35);
INSERT INTO tblCollar VALUES(2693,'lether collar','brown',25.00,25);
INSERT INTO tblCollar VALUES(3547,'stripped collar','red',20.00,75);
INSERT INTO tblCollar VALUES(3855,'stripped collar','blue',15.00,42);
INSERT INTO tblCollar VALUES(3764,'stripped collar','green',15.00,48);
INSERT INTO tblCollar VALUES(5782,'solid collar','pink',12.00,36);
INSERT INTO tblCollar VALUES(5785,'solid collar','red',12.00,10);
INSERT INTO tblCollar VALUES(5787,'solid collar','blue',10.00,15);

SET @price1 = 10, @price2 = 20; /* set variable to 10 and 20*/
SELECT * FROM tblCollar WHERE price>=@price1 and price<=@price2; /* it will display record between price 10 and 20*/

Output:-

Reason for Incorrect Answer:-

b. WHERE Price LIKE 10 AND Price LIKE 20

Like operater is used in where clouse to search specific pattern . So it will not show record between price 10 and 20. It's incorrect .

see below code as record is zero.

SELECT * FROM tblCollar WHERE Price LIKE 10 AND Price LIKE 20;

Output:-

C. WHERE Price <= 10 AND Price >= 20

As according to above statement it will display record not in between 10 and 20. so, It's incorrect.

D. WHERE @Price1 < Query  < @Price2

The above query will not display record between 10 and 20 because if we use select price query at place of Query it will give error. So, It's incorrect.