Use jjjonas_Workspace

CREATE TABLE TRICK (
	TrickID       CHAR (4)    NOT NULL,
    TrickDescr VARCHAR (25)  NOT NULL,
    CONSTRAINT Trick_PK PRIMARY KEY  (TrickID)
    );

SHOW TABLES;

CREATE TABLE KENNEL ( 
	KennelCode      CHAR (5)   NOT NULL, 
    KennelName     VARCHAR  (25)  NOT NULL,
    Location     		  VARCHAR  (50)  NOT NULL,
    CONSTRAINT KennelCode_PK PRIMARY KEY  (KennelCode)
    );
    
SHOW TABLES;

CREATE TABLE PUPPY ( 
	PuppyNum        CHAR 		   (6)  NOT NULL, 
    PuppyName      VARCHAR  (25)  NOT NULL,
	Quality     		  CHAR           (4)  NOT NULL,
    Breed				  VARCHAR  (40)  NOT NULL,
    KennelCode      CHAR           (5)  NOT NULL,
    CONSTRAINT Puppy_PK PRIMARY KEY  (PuppyNum) ,
    CONSTRAINT Puppy_FK FOREIGN KEY  (KennelCode)
		REFERENCES KENNEL (KennelCode)
    );

DESCRIBE  KENNEL ;


CREATE TABLE PUPTRICK ( 
	PuppyNum        CHAR 		   (6)  NOT NULL, 
    TrickID     	      CHAR 	       (4)  NOT NULL,
	WhenLearned   DATE,
    CONSTRAINT Puptrick_PK PRIMARY KEY  (PuppyNum, TrickID) ,
    CONSTRAINT Puptrick_PuppyNum_FK FOREIGN KEY  (PuppyNum)
		REFERENCES PUPPY (PuppyNum) ,
	CONSTRAINT Puptrick_TrickID_FK FOREIGN KEY  (TrickID)
		REFERENCES TRICK (TrickID) 
    );


CREATE TABLE SHOWDOG ( 
	PuppyNum        CHAR 		   (6)  NOT NULL, 
    DateLast 	          DATE,
	FinishPlace       CHAR           (3),
    CONSTRAINT Showdog_PK PRIMARY KEY  (PuppyNum) ,
    CONSTRAINT Showdog_FK FOREIGN KEY  (PuppyNum)
		REFERENCES PUPPY (PuppyNum)  ON DELETE CASCADE
    ) ;    

INSERT INTO KENNEL
VALUES  ('C237', 'Cibola Corgis', 'Wilcox, AZ') ;

INSERT INTO TRICK
VALUES ( '8', 'LIE Down' ) ;

INSERT INTO PUPPY
VALUES  ( 'ER654',
				  'Cibola Sweet Jasmine',
                  'Show',
				  'Pembroke Welsh Corgi',
                  'C237') ;
                  
INSERT INTO PUPTRICK
VALUES  ('ER654', '8', '2016-10-12') ;

                  
INSERT INTO PUPTRICK
VALUES  ('ER654', '4', '2016-10-12') ;

INSERT  INTO TRICK
VALUES ('4', 'Fetch') ;
 
 Use Instruction ;
 
SELECT SID, Status
FROM SUPPLIER
WHERE Scity = 'Paris';

SELECT PID, PName
FROM PART
WHERE COLOR = 'Red' ;

SELECT SID, Status
FROM SUPPLIER
WHERE Scity = 'Paris' AND Status > 20;

SELECT PID, PName
FROM PART
WHERE COLOR = 'Red'  or  Weight = 17 ;

SELECT COUNT(PID)
FROM SUP_PART;

SELECT COUNT(DISTINCT PID)
FROM SUP_PART;

SELECT COUNT(DISTINCT SCity)
FROM SUPPLIER ;

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

