Agri-Energy Connect Dbas.sql

CREATE DATABASE AGRIENERGY;
USE AGRIENERGY;

CREATE TABLE USERS (
    USER_ID INT IDENTITY(1, 1) PRIMARY KEY,
    FIREBASE_UID VARCHAR(100) UNIQUE, 
    ROLE VARCHAR(10), 
    NAME VARCHAR(100),
    DOB DATE,
    BIO TEXT
);

CREATE TABLE CATEGORIES (
    CATEGORY_ID INT IDENTITY(1, 1) PRIMARY KEY,
    CATEGORY_NAME VARCHAR(50) UNIQUE
);


INSERT INTO CATEGORIES (CATEGORY_NAME, IMAGE_URL)
VALUES ('Fruits', 'https://www.icegif.com/wp-content/uploads/icegif-5974.gif');

INSERT INTO CATEGORIES (CATEGORY_NAME, IMAGE_URL)
VALUES ('Vegtables', 'https://i.pinimg.com/originals/9d/b7/3e/9db73e5a5a980c09e86c80674828bd68.gif');

INSERT INTO CATEGORIES (CATEGORY_NAME, IMAGE_URL)
VALUES ('Green Solutions', 'https://th.bing.com/th/id/R.0889c0719d43301fa77544d9268e9b96?rik=DBXMaJgv4WE6XA&pid=ImgRaw&r=0');

INSERT INTO CATEGORIES (CATEGORY_NAME, IMAGE_URL)
VALUES ('Automated Solutions', 'https://th.bing.com/th/id/R.2e74e551b9624170d0cc054ea8601190?rik=o6t%2bQU5Iu5aF%2bg&pid=ImgRaw&r=0');

INSERT INTO CATEGORIES (CATEGORY_NAME, IMAGE_URL)
VALUES ('Semi-Auto Solutions', 'https://cdn.dribbble.com/users/2388528/screenshots/4761507/media/7fddac0a532c79ce5a7cdd5df2b2cbf9.gif');

INSERT INTO CATEGORIES (CATEGORY_NAME, IMAGE_URL)
VALUES ('Fertilizer', 'https://th.bing.com/th/id/OIP.WI7uix1QAEUVOo0Ae0wumwHaFj?rs=1&pid=ImgDetMain');

CREATE TABLE PRODUCTS (
    PRODUCT_ID INT IDENTITY(1, 1) PRIMARY KEY,
    PRODUCT_NAME VARCHAR(50),
    PRODUCT_DESCRIPTION VARCHAR(8000),
    PRICE FLOAT,
    QUANTITY INT,
    AVAILABILITY VARCHAR(3), 
    PRODUCTION_DATE DATE,
    FARMER_ID INT, 
    CATEGORY_ID INT, 
    FOREIGN KEY (FARMER_ID) REFERENCES USERS(USER_ID),
    FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORIES(CATEGORY_ID)
);




SELECT * FROM PRODUCTS
SELECT * FROM CATEGORIES
SELECT * FROM USERS