-- Temporal.
CREATE TABLE User (
Id INTEGER PRIMARY KEY
);
CREATE TABLE Employer (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Title TEXT,
Phone TEXT,
PhotoUrl TEXT
);
-- Companies must have a name.
CREATE TABLE Company (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
EmployerId INTEGER,
Name TEXT NOT NULL,
LogoUrl TEXT,
FOREIGN KEY('EmployerId') REFERENCES 'Employer'('Id')
);
-- Job Ads must have at least a job title.
CREATE TABLE Job (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
CompanyId INTEGER,
Title TEXT NOT NULL,
Description TEXT,
JobType TEXT,
Region TEXT,
FOREIGN KEY('CompanyId') REFERENCES 'Company'('Id')
);
-- MANY-TO-MANY relationship between Users & Jobs.
CREATE TABLE Application (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
UserId INTEGER,
JobId INTEGER,
FOREIGN KEY('UserId') REFERENCES 'User'('Id'),
FOREIGN KEY('JobId') REFERENCES 'Job'('Id')
);
-- Category tags for job ads, must have at least a name.
CREATE TABLE Category (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL UNIQUE
);
-- MANY-TO-MANY relationship between Jobs & Category tags.
CREATE TABLE JobCategory (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
JobId INTEGER,
CategoryId INTEGER,
FOREIGN KEY('JobId') REFERENCES 'Job'('Id'),
FOREIGN KEY('CategoryId') REFERENCES 'Category'('Id')
);
-- SQLite cannot make a FK col with the ALTER command.
-- So, drop User and make a new one.
DROP TABLE User;
-- Actual User table.
CREATE TABLE User (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
EmployerId INTEGER,
Email TEXT NOT NULL UNIQUE,
FirstName TEXT NOT NULL,
LastName TEXT,
FOREIGN KEY('EmployerId') REFERENCES 'Employer'('Id')
);
-- Add users.
INSERT INTO User (FirstName, LastName, Email, EmployerId)
VALUES ('Alicia', 'Wonderland', 'alicia@example.com', NULL),
('Bob', 'Esponja', 'bob@example.com', NULL),
('Charlie', 'Brown', 'charly@example.com', NULL);
-- Add employers.
INSERT INTO Employer (Title, Phone, PhotoUrl)
VALUES ('CEO', '+34666777888', 'https://uinames.com/api/photos/male/1.jpg'),
('Director RRHH', '+34666555444', 'https://uinames.com/api/photos/male/2.jpg'),
('Head of HR', '+4470000000000', 'https://uinames.com/api/photos/male/3.jpg');
-- Add users who are employers.
INSERT INTO User (FirstName, LastName, Email, EmployerId)
VALUES ('Daniel', 'Travieso', 'dany_xulo69@example.com', 1),
('Emilio', 'Electrónico', 'email_xulo69@example.com', 2),
('Félix', 'Rodríguez', 'thefbomb_xulo69@example.com', 3);
-- Add companies.
INSERT INTO Company (Name, LogoUrl, EmployerId)
VALUES ('Naughty Daniel Inc.', 'https://upload.wikimedia.org/wikipedia/commons/thumb/6/63/Signature_of_Prince_Daniel%2C_Duke_of_V%C3%A4sterg%C3%B6tland.PNG/320px-Signature_of_Prince_Daniel%2C_Duke_of_V%C3%A4sterg%C3%B6tland.PNG', 1),
('Unicorn Analytics SA', 'https://upload.wikimedia.org/wikipedia/commons/9/97/Logo_of_Unicorn_Analytics_company.jpg', 2),
('F-BOMB S.L.', 'https://s1.piq.land/2013/06/19/hskihtNhb0PcpSZTF2ImZ82N_400x400.png', 3);
-- Add job ads.
INSERT INTO Job ('CompanyId', 'Title', 'Description', 'JobType', 'Region')
VALUES (1, 'Janitor', 'We need a full-time janitor to take care of our giant reception hall.', 'Full-time', 'Murcia'),
(2, 'FORTRAN & COBOL Developer', 'Unicorn Analytics is looking for a resilient genius who can code in both Fortran and Cobol, Delphy and Assembly experience is also mandatory. NOOBS NEED NOT APPLY!!', 'Full-time', 'World'),
(3, 'Copywriter', 'F-BOMB SL necesita un copy que produzca insultos a cascoporro para nuestra app. Se valorará pertenencia a la RAE. Requisito imprescindible que el candidato/a tenga buenos modales y saber estar.', 'Student-job', 'Coín'),
(3, 'Accountant', 'F-BOMB SL necesita un contable que cuente los insultos que recibimos en Twitter. Secundariamente hará la contabilidad de la empresa y el inventario anual.', 'Part-time', 'Coín');
-- Create categories.
INSERT INTO Category ('Name')
VALUES ('IT'), ('Advertising'), ('Maintenance'), ('Management'), ('Hardcore'),
('Spanish'), ('English'), ('Remote'), ('Journalism'), ('Accounting');
-- Tag job ads under categories.
INSERT INTO JobCategory ('JobId', 'CategoryId')
-- Janitor: #Maintenance.
VALUES (1, 3),
-- Developer: #IT, #Hardcore, #Spanish, #English, #Remote.
(2, 1), (2, 5), (2, 6), (2, 7), (2, 8),
-- Copywriter: #Advertising, #Hardcore, #Spanish, #Journalism.
(3, 2), (3, 5), (3, 6), (3, 9),
-- Accountant: #Accounting.
(4, 10);
-- Make job applications, 9 combinations plus accountant.
INSERT INTO Application ('UserId', 'JobId')
VALUES (1, 1), (1, 2), (1, 3),
(2, 1), (2, 2), (2, 3),
(3, 1), (3, 2), (3, 3),
-- Bob applies to be an Accountant.
(2, 4);-- Return jobs grouped by region.
SELECT Job.Region, COUNT(Job.Id) Jobs
FROM Job
GROUP BY Job.Region;Output:
| Region | Jobs |
|---|---|
| "Coín" | "2" |
| "Murcia" | "1" |
| "World" | "1" |
-- Return companies offering IT jobs.
SELECT Company.Name, Jobs.CategoryName, Jobs.JobTitle
FROM (
SELECT Job.CompanyId, Job.Title JobTitle, Category.Name CategoryName
FROM Job
INNER JOIN JobCategory ON Job.Id=JobCategory.JobId
INNER JOIN Category ON Category.Id=JobCategory.CategoryId
) Jobs
INNER JOIN Company ON Company.Id=Jobs.CompanyId
WHERE Jobs.CategoryName='IT';Output:
| Company.Name | Jobs.CategoryName | Jobs.JobTitle |
|---|---|---|
| "Unicorn Analytics SA" | "IT" | "FORTRAN & COBOL Developer" |
-- Return users who applied for accountancy jobs.
SELECT User.FirstName FirstName, User.LastName LastName, Jobs.CategoryName
FROM (
SELECT Job.Id JobId, Category.Name CategoryName
FROM Job
INNER JOIN JobCategory ON Job.Id=JobCategory.JobId
INNER JOIN Category ON Category.Id=JobCategory.CategoryId
) Jobs
INNER JOIN User ON User.Id=Application.UserId
INNER JOIN Application ON Application.JobId=Jobs.JobId
WHERE Jobs.CategoryName='Accounting';Output:
| FirstName | LastName | Jobs.CategoryName |
|---|---|---|
| "Bob" | "Esponja" | "Accounting" |
-- Return full-time jobs.
SELECT Job.Title, Job.JobType
FROM Job
WHERE Job.JobType='Full-time';Output:
| Title | JobType |
|---|---|
| "Janitor" | "Full-time" |
| "FORTRAN & COBOL Developer" | "Full-time" |
-- List employers.
SELECT User.FirstName, User.LastName, Employer.Title, Employer.Phone
FROM User
INNER JOIN Employer ON User.EmployerId=Employer.Id;
How can I get this project diagram, please