Answer to Question #48777 in Databases | SQL | Oracle | MS Access for jincyjayan

Question

A software company has the following information about its clients and the projects given by them:

Clients (client number, client name, address)
Projects(project number, project name, total investment, no. of people working on the project)
Client project(client number, project number, date of start of project)

Create a database with the tables given above. Give suitable field names, keys and validation checks. Create data entry forms and enter at least 10 rows.

Run the following queries on the above database:
a) List clients who have assigned more than 2 projects to the software company.
b) List clients in order of decreasing total project investment.
c) Show the details of the project with the maximum number of people working on the it.

Answer 1

Give suitable field names, keys and validation checks. Create data entry forms and enter at least 10 rows. - Insert.sql
Run the following queries on the above database:
a) List clients who have assigned more than 2 projects to the software company. - query_1.sql
b) List clients in order of decreasing total project investment. - query_2.sql
c) Show the details of the project with the maximum number of people working on the it. - query_3.sql
CREATE TABLE CLIENTS
(
CLIENT_NUMBER NUMBER,
CLIENT_NAME varchar2(60),
ADDRESS varchar2(500)
);
ALTER TABLE CLIENTS ADD CONSTRAINT PK_CLIENTS PRIMARY KEY (CLIENT_NUMBER);
CREATE TABLE PROJECTS
(
PROJECT_NUMBER NUMBER,
PROJECT_NAME varchar2(60),
TOTAL_INVESTMENT NUMBER,
COUNT_PEOPLE NUMBER
);
ALTER TABLE PROJECTS ADD CONSTRAINT PK_PROJECTS PRIMARY KEY (PROJECT_NUMBER);
CREATE TABLE CLIENT_PROJECT
(
CLIENT_NUMBER NUMBER,
PROJECT_NUMBER NUMBER,
START_PROJECT DATE
);
ALTER TABLE CLIENT_PROJECT
ADD CONSTRAINT FK_CL_PR_CLIENTS FOREIGN KEY (CLIENT_NUMBER)
REFERENCES CLIENTS (CLIENT_NUMBER);
ALTER TABLE CLIENT_PROJECT
ADD CONSTRAINT FK_CL_PR_PROJECTS FOREIGN KEY (PROJECT_NUMBER)
REFERENCES PROJECTS (PROJECT_NUMBER);
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (1, '&CLIENT_NAME_1', '&ADDRESS_1');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (2, '&CLIENT_NAME_2', '&ADDRESS_2');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (3, '&CLIENT_NAME_3', '&ADDRESS_3');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (4, '&CLIENT_NAME_4', '&ADDRESS_4');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (5, '&CLIENT_NAME_5', '&ADDRESS_5');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (6, '&CLIENT_NAME_6', '&ADDRESS_6');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (7, '&CLIENT_NAME_7', '&ADDRESS_7');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (8, '&CLIENT_NAME_8', '&ADDRESS_8');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (9, '&CLIENT_NAME_9', '&ADDRESS_9');
insert into CLIENTS (CLIENT_NUMBER, CLIENT_NAME, ADDRESS)
values (10, '&CLIENT_NAME_10', '&ADDRESS_10');
commit;
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (1, '&PROJECT_NAME_1', &TOTAL_INVESTMENT_1, &COUNT_PEOPLE_1);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (2, '&PROJECT_NAME_2', &TOTAL_INVESTMENT_2, &COUNT_PEOPLE_2);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (3, '&PROJECT_NAME_3', &TOTAL_INVESTMENT_3, &COUNT_PEOPLE_3);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (4, '&PROJECT_NAME_4', &TOTAL_INVESTMENT_4, &COUNT_PEOPLE_4);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (5, '&PROJECT_NAME_5', &TOTAL_INVESTMENT_5, &COUNT_PEOPLE_5);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (6, '&PROJECT_NAME_6', &TOTAL_INVESTMENT_6, &COUNT_PEOPLE_6);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (7, '&PROJECT_NAME_7', &TOTAL_INVESTMENT_7, &COUNT_PEOPLE_7);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (8, '&PROJECT_NAME_8', &TOTAL_INVESTMENT_8, &COUNT_PEOPLE_8);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (9, '&PROJECT_NAME_9', &TOTAL_INVESTMENT_9, &COUNT_PEOPLE_9);
insert into PROJECTS (PROJECT_NUMBER, PROJECT_NAME, TOTAL_INVESTMENT, COUNT_PEOPLE)
values (10, '&PROJECT_NAME_10', &TOTAL_INVESTMENT_10, &COUNT_PEOPLE_10);
commit;
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (1,1,sysdate-365*1-1);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (2,2,sysdate-365*2-2);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (3,3,sysdate-365*3-3);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (4,4,sysdate-365*4-4);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (5,5,sysdate-365*5-5);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (6,6,sysdate-365*6-6);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (7,7,sysdate-365*7-7);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (8,8,sysdate-365*8-8);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (9,9,sysdate-365*9-9);
insert into CLIENT_PROJECT (CLIENT_NUMBER, PROJECT_NUMBER, START_PROJECT)
values (10,10,sysdate-365*10-10);
commit;

QUERY_1.SQL

select c.client_number,c.client_name,c.address from CLIENT_PROJECT CP
inner join CLIENTS C
on cp.client_number=c.client_number
group by c.client_number,c.client_name,c.address
having count(cp.project_number)>2

QUERY_2.SQL

select c.client_number,c.client_name,c.address,p.project_name,p.total_investment from CLIENT_PROJECT CP
inner join CLIENTS C
on cp.client_number=c.client_number
inner join PROJECTS p
on cp.project_number=p.project_number
order by p.total_investment desc

QUERY_3.SQL

select
fin.project_number,fin.project_name,fin.total_investment,fin.count_people
from
(select p.project_number,p.project_name,p.total_investment,p.count_people, rank() OVER ( ORDER BY p.count_people desc) rn
from PROJECTS p) fin
where fin.rn=1