Scribd
Upload
122 views5 pages

AI Powered Job Portal Project

Ai powered job portal

Uploaded by

Anushree Verma
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
122 views5 pages

AI Powered Job Portal Project

Ai powered job portal

Uploaded by

Anushree Verma
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

AI-Powered Job Portal - Investigatory Project

Objective:

The objective of this project is to create a Job Portal using SQL that simulates job listings, applicant

details, job applications,

and company information. We will also implement SQL queries to extract useful information, such as

matching job recommendations based on skills.

Step 1: Database Design

We will design four main tables:

- Users: Stores applicant details such as name, email, and skills.

- Companies: Stores company details like name and industry.

- Jobs: Stores job listings with job title, description, required skills, etc.

- Applications: Stores applications submitted by users for different jobs.

Step 2: Database Schema

1. Users Table:

- user_id: Primary key (Unique identifier)

- name: Name of the user

- email: Email of the user

- skills: User's skills, stored as a comma-separated list

2. Companies Table:

- company_id: Primary key (Unique identifier)

- company_name: Name of the company

- industry: Industry the company belongs to


3. Jobs Table:

- job_id: Primary key (Unique identifier)

- company_id: Foreign key (Links to Companies table)

- job_title: Title of the job

- job_description: Description of the job

- required_skills: Skills required for the job

4. Applications Table:

- application_id: Primary key (Unique identifier)

- job_id: Foreign key (Links to Jobs table)

- user_id: Foreign key (Links to Users table)

- application_date: Date when the application was submitted

SQL Code for Creating Tables

CREATE TABLE Users (

user_id INT PRIMARY KEY,

name VARCHAR(100),

email VARCHAR(100) UNIQUE,

skills VARCHAR(255)

);

CREATE TABLE Companies (

company_id INT PRIMARY KEY,

company_name VARCHAR(100),

industry VARCHAR(100)
);

CREATE TABLE Jobs (

job_id INT PRIMARY KEY,

company_id INT,

job_title VARCHAR(100),

job_description TEXT,

required_skills VARCHAR(255),

FOREIGN KEY (company_id) REFERENCES Companies(company_id)

);

CREATE TABLE Applications (

application_id INT PRIMARY KEY,

job_id INT,

user_id INT,

application_date DATE,

FOREIGN KEY (job_id) REFERENCES Jobs(job_id),

FOREIGN KEY (user_id) REFERENCES Users(user_id)

);

Inserting Sample Data

-- Insert Users

INSERT INTO Users (user_id, name, email, skills)

VALUES

(1, 'Alice Johnson', 'alice@example.com', 'Python, SQL, Data Analysis'),

(2, 'Bob Smith', 'bob@example.com', 'Java, SQL, Web Development'),


(3, 'Charlie Davis', 'charlie@example.com', 'Python, Machine Learning, AI');

-- Insert Companies

INSERT INTO Companies (company_id, company_name, industry)

VALUES

(101, 'Tech Solutions', 'IT'),

(102, 'Data Insights', 'Analytics'),

(103, 'AI Innovators', 'Artificial Intelligence');

-- Insert Jobs

INSERT INTO Jobs (job_id, company_id, job_title, job_description, required_skills)

VALUES

(201, 101, 'Software Developer', 'Develop and maintain software applications.', 'Java, SQL'),

(202, 102, 'Data Analyst', 'Analyze and interpret data.', 'Python, Data Analysis'),

(203, 103, 'AI Engineer', 'Develop AI models and algorithms.', 'Python, Machine Learning, AI');

-- Insert Applications

INSERT INTO Applications (application_id, job_id, user_id, application_date)

VALUES

(301, 201, 2, '2024-09-18'),

(302, 202, 1, '2024-09-18'),

(303, 203, 3, '2024-09-18');

Queries for Extracting Information

1. View All Users with Their Skills

SELECT * FROM Users;


2. List All Jobs and Their Requirements

SELECT J.job_id, C.company_name, J.job_title, J.required_skills

FROM Jobs J

JOIN Companies C ON J.company_id = C.company_id;

3. Find Job Applications Submitted by Each User

SELECT U.name AS applicant_name, J.job_title, C.company_name, A.application_date

FROM Applications A

JOIN Users U ON A.user_id = U.user_id

JOIN Jobs J ON A.job_id = J.job_id

JOIN Companies C ON J.company_id = C.company_id;

4. Find All Jobs Matching a User's Skills

SELECT U.name AS applicant_name, J.job_title, J.required_skills

FROM Users U

JOIN Jobs J ON J.required_skills LIKE CONCAT('%', U.skills, '%')

WHERE U.user_id = 1;

5. List Jobs and Their Matching Applicants Based on Skills

SELECT U.name AS applicant_name, J.job_title, J.required_skills

FROM Users U

JOIN Jobs J ON J.required_skills LIKE CONCAT('%', U.skills, '%');

You might also like