64 701
Assignments Done
99,4%
Successfully Done
In September 2018

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

Question #48777
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.
Expert's answer
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



Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be first!

Leave a comment

Ask Your question

Submit
Privacy policy Terms and Conditions