Question

-- Table construction (just 1 simplest possible way)
CREATE TABLE PetType (
petTypeId VARCHAR(10) PRIMARY KEY,
animalType VARCHAR(20),
breed VARCHAR(20)
);
CREATE TABLE Owner (
ownerId VARCHAR(10) PRIMARY KEY,
firstName VARCHAR(20),
lastName VARCHAR(20) NOT NULL,
homePhoneNumber VARCHAR(20),
streetAddress VARCHAR(80),
suburb VARCHAR(20),
postcode VARCHAR(10)
);
CREATE TABLE Pet (
petId VARCHAR(10) PRIMARY KEY,
petName VARCHAR(20),
sex CHAR(1)
CHECK (sex IN ('M', 'F')),
petTypeId VARCHAR(10)
FOREIGN KEY REFERENCES PetType
);
CREATE TABLE PetAndOwner (
ownerId VARCHAR(10),
petId VARCHAR(10),
PRIMARY KEY (ownerId, petId),
FOREIGN KEY (ownerId) REFERENCES Owner,
FOREIGN KEY (petId) REFERENCES Pet
);
-- Populate the tables with simple data
INSERT INTO PetType VALUES ('001', 'dog', 'Bulldog');
INSERT INTO PetType VALUES ('002', 'dog', 'Lhasa Apso');
INSERT INTO PetType VALUES ('003', 'dog', 'Maltese');
INSERT INTO PetType VALUES ('004', 'cat', 'Persian');
INSERT INTO PetType VALUES ('005', 'cat', 'Ragdoll');
INSERT INTO Owner VALUES ('001', 'David', 'Smith', '12345678',
'100 Victoria Road', 'Rydalmere', '2116');
INSERT INTO Owner VALUES ('002', 'Louise', 'Brown', '87654321',
'1 James Ruse Road', 'Rydalmere', '2116');
INSERT INTO Owner VALUES ('003', 'Robert', 'Brown', '11223344',
'2 Wentworth Street', 'Parramatta', '2150');
INSERT INTO Owner VALUES ('004', 'Avatar', 'Phantom', '',
'1 Pandora', 'Na''vi Land', '0000');
INSERT INTO Pet VALUES ('001', 'Mickey Mouse', 'M', '001');
INSERT INTO Pet VALUES ('002', 'Bugs Bunny', 'M', '001');
INSERT INTO Pet VALUES ('003', 'Betty Boop', 'F', '002');
INSERT INTO Pet VALUES ('004', 'Droopy', 'M', '003');
INSERT INTO Pet VALUES ('005', 'Penelope', 'F', '004');
INSERT INTO Pet VALUES ('006', 'Jerry', 'F', '005');
INSERT INTO PetAndOwner VALUES ('001', '001');
INSERT INTO PetAndOwner VALUES ('001', '004');
INSERT INTO PetAndOwner VALUES ('002', '001');
INSERT INTO PetAndOwner VALUES ('002', '005');
INSERT INTO PetAndOwner VALUES ('003', '002');

i.) For the Pet Datbase described in the previous practical, write an SQL statement with a subquery to list all the people in the Owner table who own more than one pet. 9. (Aggregate function) Continuing from the previous question, write an SQL statement to list the total number of pets for each pair of pet type and breed. Sort the listing first in the decreasing order of the total number, then in the order of animalType and then in the order of breed.

10. (Multi-table join) Continuing from the previous question, write an SQL statement to list all the pet details for all the owned pets.

Answer 1

Hi,

Please see the below Querries:

 
 --Querrry to fetch Owners Infor who is more than one Pet
 SELECT OwnerID,
                firstName,
                LastName,
                homephonenumber,
                streetaddress,
                suburb,
                postcode 
FROM ( 
                SELECT PO.OwnerID,
                                O.firstName,
                                O.LastName,
                                O.homephonenumber,
                                O.streetaddress,
                                O.suburb,
                                O.postcode,
                                ROW_NUMBER() OVER(Partition By PO.OwnerID order by PO.OwnerID) 'PetCount'
                   FROM PetAndOwner PO
                                INNER JOIN owner O
                                ON PO.OwnerID = O.OwnerID
        ) AS PetOwnerInfo
   WHERE PetCount > 1

OUTPUT:

 --Continuing from the previous question, write an SQL statement to list the total number of pets for each pair of pet type and breed. 
 --Sort the listing first in the decreasing order of the total number, then in the order of animalType and then in the order of breed 

 SELECT PT.petTypeID,
                PT.animalType,
                PT.breed, 
                count(P.petTypeID) 'TotalNumnerOfPet' 
   FROM PetType PT
                INNER JOIN Pet P 
                ON P.petTypeID = PT.petTypeID
        GROUP BY PT.petTypeID,
                         PT.animalType,
                                 PT.breed
        ORDER BY TotalNumnerOfPet DESC,
                         animaltype,
                                 breed

OUTPUT:

--(Multi-table join) Continuing from the previous question, write an SQL statement to list all the pet details for all the owned pets

SELECT P.PETID,
       P.PETNAME,
           P.SEX,
           O.OWNERID,
           O.FIRSTNAME,
           O.LASTNAME,
           O.HOMEPHONENUMBER,
           O.STREETADDRESS,
           O.SUBURB,
           O.POSTCODE
  FROM Pet P INNER JOIN PetAndOwner PO
           ON P.PETID = PO.PETID
           INNER JOIN Owner O
           ON PO.OWNERID = O.OWNERID

OUTPUT:

THANKS.