harish-bendale
commented
10 months ago What's Your Isssue ? I am interested to resolve your issue..
Open Dhruv3013 opened 10 months ago
harish-bendale
commented
10 months ago What's Your Isssue ? I am interested to resolve your issue..
USE SECTION6;
--LOCATION_INFO TABLE CREATION CREATE TABLE LOCATIONS_INFO( ADDRESS VARCHAR(50) PRIMARY KEY NOT NULL, --DONE TELEPHONE VARCHAR(20) NOT NULL, EMAIL VARCHAR(50) NOT NULL );
--AMENITIES TABLE CREATION CREATE TABLE AMENITIES( ADDRESS VARCHAR(50) PRIMARY KEY NOT NULL, AMENITY_NAME CHAR(50) NOT NULL, --DONE FOREIGN KEY (ADDRESS) REFERENCES LOCATIONS_INFO(ADDRESS) );
--LOCATION TABLE CREATION CREATE TABLE LOCATIONS( LOCATION_ID INTEGER PRIMARY KEY NOT NULL, NAME CHAR(50) NOT NULL, TIMES_VISITED INTEGER, ADDRESS VARCHAR(50) NOT NULL, --DONE FOREIGN KEY (ADDRESS) REFERENCES LOCATIONS_INFO(ADDRESS) );
--COACH TABLE CREATION CREATE TABLE COACH ( COACH_ID INTEGER PRIMARY KEY NOT NULL, COACH_NAME CHAR(50) NOT NULL, ADDRESS VARCHAR(50) NOT NULL, LEVEL INTEGER NOT NULL, RATING INTEGER NOT NULL, --DONE BIODATA VARCHAR(500), FOREIGN KEY(ADDRESS) REFERENCES LOCATIONS_INFO(ADDRESS) );
--COACH_CERTIFICATE TABLE CREATION CREATE TABLE COACH_CERTIFICATE( CERTIFICATE_ID INTEGER PRIMARY KEY NOT NULL, --DONE COACH_ID INTEGER NOT NULL, CERTIFICATES CHAR(50) NOT NULL, FOREIGN KEY (COACH_ID) REFERENCES COACH(COACH_ID) );
--REFRENCES TABLE CREATION CREATE TABLE REFRENCES( COACH_ID INTEGER PRIMARY KEY NOT NULL, CLIENT CHAR(50) NOT NULL, --DONE CLIENT_EMAIL VARCHAR(50), PHONE_NO VARCHAR(20), FOREIGN KEY (COACH_ID) REFERENCES COACH(COACH_ID) );
--REVIEWS TABLE CREATION CREATE TABLE REVIEWS( COACH_ID INTEGER PRIMARY KEY NOT NULL, REVIEW_DATE DATE NOT NULL, COMMUNICATION INTEGER NOT NULL, ENTHUSIASM INTEGER NOT NULL, PUNCTUALITY INTEGER NOT NULL, DESCRIPTION VARCHAR(500), FOREIGN KEY(COACH_ID) REFERENCES COACH(COACH_ID) );
--COACH_LOCATION TABLE CREATION CREATE TABLE COACH_LOCATION( COACH_ID INTEGER NOT NULL, LOCATION_ID INTEGER NOT NULL, PRIMARY KEY(COACH_ID, LOCATION_ID), FOREIGN KEY(COACH_ID) REFERENCES COACH(COACH_ID), FOREIGN KEY (LOCATION_ID) REFERENCES LOCATIONS(LOCATION_ID) );
--CLASSES TABLE CREATION CREATE TABLE CLASSES( DAY CHAR(10) NOT NULL, TIME VARCHAR(10) NOT NULL, CLASS_NAME CHAR(20), PRICE INTEGER NOT NULL, ROOM VARCHAR(20) NOT NULL, GYMID INTEGER NOT NULL, PRIMARY KEY (DAY, TIME), FOREIGN KEY (GYMID) REFERENCES LOCATIONS(LOCATION_ID) );
--LOCATIONS_INFO INSERTION INSERT INTO LOCATIONS_INFO (ADDRESS, TELEPHONE, EMAIL) VALUES ('150 CLEMENTINE DRIVE', '905.487.1254', 'MIDTOWN123'); INSERT INTO LOCATIONS_INFO (ADDRESS, TELEPHONE, EMAIL) VALUES ('190 MCLAUGHLIN ROAD', '905.485.4582', 'CABBAGEETOWN');
--AMENITIES INSERTION INSERT INTO AMENITIES (ADDRESS, AMENITY_NAME) VALUES ('150 CLEMENTINE DRIVE', 'SAUNA, POOL, GREEN ZONE');
--LOCATIONS INSERTION INSERT INTO LOCATIONS (LOCATION_ID, NAME, TIMES_VISITED, ADDRESS) VALUES(142, 'MIDTOWN', 15, '150 CLEMENTINE DRIVE'); INSERT INTO LOCATIONS (LOCATION_ID, NAME, TIMES_VISITED, ADDRESS) VALUES(157, 'CABAGETOWN', 8, '190 MCLAUGHLIN ROAD');
--COACH INSERTION INSERT INTO COACH (COACH_ID, COACH_NAME, ADDRESS, LEVEL, RATING, BIODATA) VALUES(1591, 'SALLY', '150 CLEMENTINE DRIVE', 3, 5, 'MY EXPERTISE IS IN PSYCHOLOGY AND PHYSIOLOGY'); INSERT INTO COACH (COACH_ID, COACH_NAME, ADDRESS, LEVEL, RATING, BIODATA) VALUES(1881, 'ADAM', '190 MCLAUGHLIN ROAD', 2, 4, 'INTERESTED IN GETTING FIT? COME JOIN ME');
--REVIEWS INSERTION INSERT INTO REVIEWS (COACH_ID, REVIEW_DATE, COMMUNICATION, ENTHUSIASM, PUNCTUALITY) VALUES (1591, '2021-10-15', 3, 4, 3); INSERT INTO REVIEWS (COACH_ID, REVIEW_DATE, COMMUNICATION, ENTHUSIASM, PUNCTUALITY, DESCRIPTION) VALUES (1881, '2021-11-25', 5, 4, 4, 'GOOD AND VALUBLE TEACHINGS');
--REFRENCES INSERTION INSERT INTO REFRENCES (COACH_ID, CLIENT, CLIENT_EMAIL, PHONE_NO) VALUES (1591, 'MARK''BARRACK', 'MARKB', '468.255.4874');
--COACH_CERTIFICATE INSERTION INSERT INTO COACH_CERTIFICATE (COACH_ID, CERTIFICATE_ID, CERTIFICATES) VALUES (1591, 12, 'MASSAGE III'); INSERT INTO COACH_CERTIFICATE (COACH_ID, CERTIFICATE_ID, CERTIFICATES) VALUES (1591, 15, 'PHYSIOLOGY MASTERS');
--COACH_LOCATIION INSERTION INSERT INTO COACH_LOCATION (COACH_ID, LOCATION_ID) VALUES (1591, 142); INSERT INTO COACH_LOCATION (COACH_ID, LOCATION_ID) VALUES (1881, 157);
--CLASSES INSERTION INSERT INTO CLASSES (DAY, TIME, CLASS_NAME, PRICE, ROOM, GYMID) VALUES ('MONDAY', '9:50AM', 'BODY PUMP', 10, 'STUDIO1', 142) INSERT INTO CLASSES (DAY, TIME, CLASS_NAME, PRICE, ROOM, GYMID) VALUES ('MONDAY', '10:50AM', 'HIIT', 5, 'STUDIO1', 157) INSERT INTO CLASSES (DAY, TIME, CLASS_NAME, PRICE, ROOM, GYMID) VALUES ('THURSDAY', '9:50AM', 'BODY PUMP', 10, 'STUDIO1', 142) INSERT INTO CLASSES (DAY, TIME, CLASS_NAME, PRICE, ROOM, GYMID) VALUES ('FRIDAY', '10:50AM', 'YOGA', 8, 'SMALL STUDIO', 157)