Question

USING SQL

Create a table named Zones with the attributes and assumptions indicated below.

• Attributes: the zone ID, the lowest and the highest accepted temperature.

Assumptions: the ID will be the primary key and have one or two digits, the temperatures (in Fahrenheit) will be at most two digits and a possible minus sign, none of the temperatures can be NULL.

Populate table Zones so that it has the following rows:

id	lowerTemp	higherTemp
2	-50	-40
3	-40	-30
4	-30	-20
5	-20	-10
6	-10	0
7	0	10
8	10	20
9	20	30
10	30	40

3. Deliveries

The same flower vendor wants to use a code to explain the type of delivery for each flower. Create a table named Deliveries with the attributes and assumptions indicated below.

• Attributes: the delivery ID, the category or type of delivery, and the size of the delivery.

• Assumptions: the ID will be the primary key and have one one digit, the category will be at most five characters (pot, plant, hedge, shrub, tree), and the delivery size will be up to five digits with three decimal spaces (possibly NULL).

Populate table Deliveries so that it has the following rows:

id	categ	delSize
1	pot	1.500
2	pot	2.250
3	pot	2.625
4	pot	4.250
5	plant	NULL
6	bulb	NULL
7	hedge	18.000
8	shrub	24.000
9	tree	36.000

3. FlowersInfo

Create a table named FlowersInfo with the attributes and assumptions indicated below. Choose the most appropriate data types.

Attributes: an ID with three characters, common name, Latin name, the coolest and hottest zones where it can be grown, the delivery category, and the sun needs.

Assumptions: The ID will be the primary key, the attribute common name may have up to thirty characters, and the Latin name up to thirty-five characters. The attributes coolest zone, hottest zone, and delivery category will match the IDs from other tables, and the sun needs will be up to five characters, S for Sun, SH for Shade, P for Partial sun and any combination (StoP, StoSH, etc.). Your table definition should implement referential integrity whenever possible.
Populate table FlowersInfo so that it has the following rows:

id	conName	latName	cZone	hZone	deliver	sunNeeds
101	Lady Fern	Atbyrium filix-femina	2	9	5	SH
102	Pink Caladiums	C.x bortulanum	10	10	6	PtoSH
103	Lily-of-the-Valley	Convallaria majalis	2	8	5	PtoSH
105	Purple Liatris	Liatris spicata	3	9	6	StoP
106	Black Eyed Susan	Rudbeckia fulgida var. specios	4	10	2	StoP
107	Nikko Blue Hydrangea	Hydrangea macrophylla	5	9	4	StoSH
108	Variegated Weigela	W. florida Variegata	4	9	8	StoP
110	Lombardy Poplar	Populus nigra Italica	3	9	9	S
111	Purple Leaf Plum Hedge	Prunus x cistena	2	8	7	S
114	Thorndale Ivy	Hedera belix Thorndale	3	9	1	StoSH

4. Queries

Write SQL statements to answer the following queries. Write a comment header right before each SQL statement with the letter of the query answered by the statement.

a) the total number of zones.

b) the number of flowers per cool zone.

c) common names of the plants that have delivery sizes less than 5.

d) common names of the plants that require full sun (i.e., sun needs contains ‘S’).

e) all delivery category names order alphabetically (without repetition).

f) the exact output (note that it is order by Name):

Answer 1

If you have any doubts, please give me comment...

--1

CREATE TABLE Zones(

    id INT(2) NOT NULL PRIMARY KEY,

    lowerTemp INT,

    higherTemp INT

);

INSERT INTO Zones VALUES(2,-50,-40);

INSERT INTO Zones VALUES(3, -40, -30);

INSERT INTO Zones VALUES(4, -30, -20);

INSERT INTO Zones VALUES(5, -20, -10);

INSERT INTO Zones VALUES(6, -10, 0);

INSERT INTO Zones VALUES(7, 0, 10);

INSERT INTO Zones VALUES(8, 10, 20);

INSERT INTO Zones VALUES(9, 20, 30);

INSERT INTO Zones VALUES(10, 30, 40);

-- 2

CREATE TABLE Deliveries(

    id INT(1) NOT NULL PRIMARY KEY,

    categ VARCHAR(5),

    delSize DECIMAL(9, 3)

);

INSERT INTO Deliveries VALUES(1, 'pot', 1.500);

INSERT INTO Deliveries VALUES(2, 'pot', 2.250);

INSERT INTO Deliveries VALUES(3, 'pot', 2.625);

INSERT INTO Deliveries VALUES(4, 'pot', 4.250);

INSERT INTO Deliveries VALUES(5, 'plant', NULL);

INSERT INTO Deliveries VALUES(6, 'bulb', NULL);

INSERT INTO Deliveries VALUES(7, 'hedge', 18.000);

INSERT INTO Deliveries VALUES(8, 'shrub', 24.000);

INSERT INTO Deliveries VALUES(9, 'tree', 36.000);

-- 3

CREATE TABLE FlowerInfo(

    id INT(3) NOT NULL PRIMARY KEY,

    conName VARCHAR(30),

    latName VARCHAR(35),

    cZone INT(2),

    hZone INT(2),

    deliver INT(1),

    sunNeeds  VARCHAR(5),

    FOREIGN KEY(cZone) REFERENCES Zones(id),

    FOREIGN KEY(hZone) REFERENCES Zones(id),

    FOREIGN KEY(deliver) REFERENCES Deliveries(id)

);

INSERT INTO FlowerInfo VALUES(101, 'Lady Fern', 'Atbyrium filix-femina', 2, 9, 5, 'SH');

INSERT INTO FlowerInfo VALUES(102, 'Pink Caladiums', 'C.x bortulanum', 10, 10, 6, 'PtoSH');

INSERT INTO FlowerInfo VALUES(103, 'Lily-of-the-Valley', 'Convallaria majalis', 2, 8, 5, 'PtoSH');

INSERT INTO FlowerInfo VALUES(105, 'Purple Liatris', 'Liatris spicata', 3, 9, 6, 'StoP');

INSERT INTO FlowerInfo VALUES(106, 'Black Eyed Susan', 'Rudbeckia fulgida var. specios', 4, 10, 2, 'StoP');

INSERT INTO FlowerInfo VALUES(107, 'Nikko Blue Hydrangea', 'Hydrangea macrophylla', 5, 9, 4, 'StoSH');

INSERT INTO FlowerInfo VALUES(108, 'Variegated Weigela', 'W. florida Variegata', 4, 9, 8, 'StoP');

INSERT INTO FlowerInfo VALUES(110, 'Lombardy Poplar', 'Populus nigra Italica', 3, 9, 9, 'S');

INSERT INTO FlowerInfo VALUES(111, 'Purple Leaf Plum Hedge', 'Prunus x cistena', 2, 8, 7, 'S');

INSERT INTO FlowerInfo VALUES(114, 'Thorndale Ivy', 'Hedera belix Thorndale', 3, 9, 1, 'StoSH');

-- 4a

SELECT COUNT(*) FROM Zones;

-- 4b

SELECT cZone, COUNT(*) AS "No of Flowers"

FROM FlowerInfo

GROUP BY cZone;

-- 4c

SELECT conName

FROM FlowerInfo, Deliveries

WHERE FlowerInfo.deliver = Deliveries.id AND delSize < 5;

-- 4d

SELECT conName

FROM FlowerInfo

WHERE sunNeeds = 'S';

-- 4e

SELECT DISTINCT categ

FROM Deliveries

ORDER BY categ;

-- 4f

SELECT categ

FROM Deliveries

ORDER BY categ;