Q1. List the total number of tasks for a particular project in a given date range. (SQL)

Code:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY';

DROP TABLE REGION;

DROP TABLE EMPLOYEE;

DROP TABLE CUSTOMER;

DROP TABLE PROJECT;

DROP TABLE SKILL;

DROP TABLE EMPSKILL;

DROP TABLE BILL;

DROP TABLE TASK_ID;

DROP TABLE TS;

DROP TABLE ASSIGN;

DROP TABLE WORKLOG;

CREATE TABLE REGION( REGION_ID CHAR(2) PRIMARY KEY, REGION_NAME VARCHAR(20) NOT NULL);

CREATE TABLE EMPLOYEE( EMP_ID VARCHAR(6) PRIMARY KEY, REGION_ID CHAR(2), EMP_LNAME VARCHAR(15) NOT NULL, EMP_MI VARCHAR(2) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_HIREDATE DATE, FOREIGN KEY (REGION_ID) REFERENCES REGION(REGION_ID));

CREATE TABLE CUSTOMER( CUS_ID VARCHAR(6) PRIMARY KEY, REGION_ID CHAR(2), CUS_NAME VARCHAR(35) NOT NULL, CUS_PHONE VARCHAR(12) NOT NULL, FOREIGN KEY (REGION_ID) REFERENCES REGION(REGION_ID));

CREATE TABLE WORK_PROJECT( PROJECT_ID VARCHAR(6) PRIMARY KEY, CUS_ID VARCHAR(6), EMP_ID VARCHAR(6), PROJ_DATE DATE NOT NULL, DESCRIPTION VARCHAR(300) NOT NULL, ES_START DATE NOT NULL, ES_END DATE NOT NULL, ES_BUDGET VARCHAR(20) NOT NULL, AC_START DATE NOT NULL, AC_END DATE NOT NULL, AC_COST VARCHAR(20), FOREIGN KEY (CUS_ID) REFERENCES CUSTOMER(CUS_ID), FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID));

CREATE TABLE SKILL( SKILL_ID VARCHAR(6) PRIMARY KEY, DESCRIPTION VARCHAR(40) NOT NULL, RATE VARCHAR(20) NOT NULL);

CREATE TABLE EMPSKILL( EMP_ID VARCHAR(6), SKILL_ID VARCHAR(6), FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID), FOREIGN KEY (SKILL_ID) REFERENCES SKILL(SKILL_ID), PRIMARY KEY (EMP_ID, SKILL_ID));

CREATE TABLE BILL( BILL_ID VARCHAR(5) PRIMARY KEY, BILL_DATE DATE NOT NULL, PROJECT_ID VARCHAR(6), FOREIGN KEY (PROJECT_ID) REFERENCES WORK_PROJECT(PROJECT_ID));

CREATE TABLE TASK( TASK_ID VARCHAR(6) PRIMARY KEY, PROJECT_ID VARCHAR(6), DESCRIPTION VARCHAR(40) NOT NULL, BEGINDATE DATE NOT NULL, ENDDATE DATE NOT NULL, FOREIGN KEY (PROJECT_ID) REFERENCES WORK_PROJECT(PROJECT_ID));

CREATE TABLE TS( TS_ID VARCHAR(6) PRIMARY KEY, TASK_ID VARCHAR(6), SKILL_ID VARCHAR(6), TS_QTY NUMBER(10) NOT NULL, FOREIGN KEY (TASK_ID) REFERENCES TASK(TASK_ID), FOREIGN KEY (SKILL_ID) REFERENCES SKILL(SKILL_ID));

CREATE TABLE ASSIGN( ASSIGN_ID VARCHAR(6) PRIMARY KEY, EMP_ID VARCHAR(6), BEGIN DATE NOT NULL, END DATE NOT NULL, TS_ID VARCHAR(6), FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID), FOREIGN KEY (TS_ID) REFERENCES TS(TS_ID));

CREATE TABLE WORKLOG( WL_ID VARCHAR(6) PRIMARY KEY, ASSIGN_ID VARCHAR(6), WL_DATE DATE NOT NULL, WL_HOURS NUMBER(3) NOT NULL, BILL_ID VARCHAR(5), FOREIGN KEY (ASSIGN_ID) REFERENCES ASSIGN(ASSIGN_ID), FOREIGN KEY (BILL_ID) REFERENCES BILL(BILL_ID));

insert into REGION values('NW','Northwest');

insert into REGION values('SW','Southwest');

insert into REGION values('MN','Midwest North');

insert into REGION values('MS','Midwest South');

insert into REGION values('NE','Northest');

insert into REGION values('SE','Southeast');

INSERT INTO EMPLOYEE VALUES ('E23148','NW','Johnson','M','Will','JUN-01-2003');

INSERT INTO EMPLOYEE VALUES ('E78315','SW','Holmes','G','Ashley','DEC-13-2008');

INSERT INTO EMPLOYEE VALUES ('E45781','MN','Gaston', 'A', 'Devon', 'MAY-25-2000');

INSERT INTO EMPLOYEE VALUES ('E23478','MS','Williams', 'S', 'Phil', 'AUG-03-2010');

INSERT INTO EMPLOYEE VALUES ('E96145','NE','Brady', 'E', 'Tom', 'FEB-21-2014');

INSERT INTO EMPLOYEE VALUES ('E46571','SE','Dorsett', 'B', 'Jason', 'JAN-28-2005');

INSERT INTO EMPLOYEE VALUES ('E58936','NW','Evans', 'Z', 'Jamie', 'NOV-08-2002');

INSERT INTO EMPLOYEE VALUES ('E56789','SW','Banks', 'Q', 'Patrick', 'MAR-31-2019');

INSERT INTO EMPLOYEE VALUES ('E23461','MN','Smith', 'J', 'Sean', 'JUL-14-2016');

INSERT INTO EMPLOYEE VALUES ('E31689','MS','Anderson', 'N', 'Nicole', 'APR-11-2018');

insert into CUSTOMER values ('C43801','NW','Carlos Machado','5054857894');

insert into CUSTOMER values ('C44454','SW','Marcio Santos','7893579510');

insert into CUSTOMER values ('C82272','MN','Garry Tonon','5546987852');

insert into CUSTOMER values ('C96015','MS','Gordon Ryan','2346524487');

insert into CUSTOMER values ('C13895','NE','John Danaher','28269741352');

insert into CUSTOMER values ('C15167','SE','Renzo Gracie','4527953724');

insert into CUSTOMER values ('C37167','NW','Eddie Bravo','7852150224');

insert into CUSTOMER values ('C08601','SW','Ryan Hall','9724765897');

insert into CUSTOMER values ('C33599','MN','Dillon Danis','5755720449');

insert into CUSTOMER values ('C52558','MS','Nate Diaz','51523698745');

INSERT INTO WORK_PROJECT VALUES ('903020','C43801','E23148','SEP-12-2004','System Refresh','AUG-28-2004','SEP-01-2004','$125,000','AUG-15-2004','AUG-20-2004','$115,000');

INSERT INTO WORK_PROJECT VALUES ('386215','C44454','E78315','JUN-25-2010','New tracking database ','APR-07-2009','JUN-22-2010','$75,250','APR-01,2010','MAY-01-2010','$100,00');

INSERT INTO WORK_PROJECT VALUES ('658152','C82272','E45781','JAN-31-2012','Hospital Patient system','DEC-01-2011','DEC-25-2011','$50,500','DEC-15,2011','DEC-25-2011','$99,900');

INSERT INTO WORK_PROJECT VALUES ('165890','C96015','E23478','MAR-31-2015','New Windows App','FEB-15-2015','MAR-15-2015','$2,500','MAR-01-2011','MAR-30-2011','$5,000');

INSERT INTO WORK_PROJECT VALUES ('405462','C13895','E96145','NOV-10-2019','New Resturant POS system','JAN-15-2019','NOV-15-2019','$200,000','JUN-15-2019','NOV-01-2019','$250,000');

INSERT INTO WORK_PROJECT VALUES ('980466','C15167','E46571','AUG-18-2009','SYSTEM UPDATES','JUN-03-2009','JUL-04-2009','$22,000','APR-30-2009','MAY-28-2009','$35,000');

INSERT INTO WORK_PROJECT VALUES ('158230','C37167','E58936','FEB-16-2020','City marathon Tracking system','NOV-01-2019','DEC-31-2019','$47,275','NOV-01-2019','JAN-31-2020','$55,750');

INSERT INTO WORK_PROJECT VALUES ('586740','C08601','E56789','MAY-31-2011','Animal shelter adoption program','APR-01-2011','APR-30-2012','$6,335','APR-05-2019','MAY-05-2020','$10,500');

INSERT INTO WORK_PROJECT VALUES ('796362','C33599','E23461','DEC-31-2013','Art auction sales tracking system','JUL-04-2013','DEC-30-2013','$1,844','AUG-31-2013','NOV-01-2013','$4,500');

INSERT INTO WORK_PROJECT VALUES ('683489','C52558','E31689','JUL-31-2018','Server farm hardware upgrade','MAR-01-2018','JUL-30-2018','$350,000','JAN-01-2018','JUL-04-2018','$400,500');

insert into SKILL values ('001','Data Entry I','$25.00');

insert into SKILL values ('002','Data Entry II','$26.50');

insert into SKILL values ('003','Systems Analyst I','$27.00');

insert into SKILL values ('004','Systems Analyst II','$28.50');

insert into SKILL values ('005','Database Designer I','$32.00');

insert into SKILL values ('006','Database Designer II','$34.50');

insert into SKILL values ('007','Cobol I','$29.50');

insert into SKILL values ('008','Cobol II','$33.00');

insert into SKILL values ('009','C++ I','$35.00');

insert into SKILL values ('010','C++ II','$40.00');

insert into SKILL values ('011','VB I','$31.50');

insert into SKILL values ('012','VB II','$34.75');

insert into SKILL values ('013','Coldfusion I','$36.25');

insert into SKILL values ('014','Coldfusion II','$40.50');

insert into SKILL values ('015','ASP I','$39.45');

insert into SKILL values ('016','ASP II','$44.60');

insert into SKILL values ('017','Oracle DBA','$35.00');

insert into SKILL values ('018','MS SQL Server DBA','$35.00');

insert into SKILL values ('019','Network Engineer I','$45.00');

insert into SKILL values ('020','Network Engineer II','$49.00');

insert into SKILL values ('021','Web Administrator','$42.50');

insert into SKILL values ('022','Technical Writer','$47.50');

insert into SKILL values ('023','Project Manager','$52.00');

insert into EMPSKILL values ('E23148','004');

insert into EMPSKILL values ('E78315','001');

insert into EMPSKILL values ('E45781','002');

insert into EMPSKILL values ('E23478','009');

insert into EMPSKILL values ('E96145','001');

insert into EMPSKILL values ('E46571','016');

insert into EMPSKILL values ('E58936','005');

insert into EMPSKILL values ('E56789','018');

insert into EMPSKILL values ('E23461','002');

insert into EMPSKILL values ('E31689','023');

insert into BILL values ('B0001','sep-12-2004','903020');

insert into BILL values ('B0002','jun-25-2010','386215');

insert into BILL values ('B0003','jan-31-2012','658152');

insert into BILL values ('B0004','Mar-31-2015','165890');

insert into BILL values ('B0005','Nov-10-2019','405462');

insert into BILL values ('B0006','Aug-18-2009','980466');

insert into BILL values ('B0007','Feb-16-2020','158230');

insert into BILL values ('B0008','May-31-2011','586740');

insert into BILL values ('B0009','Dec-31-2013','796362');

insert into BILL values ('B0010','Jul-31-2018','683489');

insert into TASK values ('TA0001','903020','Define system faults','jul-14-2004','aug-01-2004');

insert into TASK values ('TA0002','386215','Enter new information into database','apr-01-2010','may-01-2010');

insert into TASK values ('TA0003','658152','Update patient information','Dec-15-2011','Dec-25-2011');

insert into TASK values ('TA0004','165890','Write program','MAR-01-2011','MAR-30-2011');

insert into TASK values ('TA0005','405462','Enter POS information','JUN-15-2019','NOV-01-2019');

insert into TASK values ('TA0006','980466','Update system','APR-30-2009','MAY-28-2009');

insert into TASK values ('TA0007','158230','Creat Runner tracking system','NOV-01-2019','JAN-31-2020');

insert into TASK values ('TA0008','586740','Design new adoption tracking system','APR-05-2019','MAY-05-2020');

insert into TASK values ('TA0009','796362','Update Art sales tracking system','AUG-31-2013','NOV-01-2013');

insert into TASK values ('TA0010','683489','Oversee hardware upgrade','JAN-01-2018','JUL-04-2018');

insert into TS values ('TS0101','TA0001','004','1');

insert into TS values ('TS0102','TA0002','001','1');

insert into TS values ('TS0103','TA0003','002','1');

insert into TS values ('TS0104','TA0004','009','1');

insert into TS values ('TS0105','TA0005','001','1');

insert into TS values ('TS0106','TA0006','016','1');

insert into TS values ('TS0107','TA0007','005','1');

insert into TS values ('TS0108','TA0008','018','1');

insert into TS values ('TS0109','TA0009','002','1');

insert into TS values ('TS0110','TA0010','023','1');

insert into ASSIGN values ('AS0201','E23148','jul-14-2004','aug-01-2004','TS0101');

insert into ASSIGN values ('AS0202','E78315','apr-01-2010','may-01-2010','TS0102');

insert into ASSIGN values ('AS0203','E45781','Dec-15-2011','Dec-25-2011','TS0103');

insert into ASSIGN values ('AS0204','E23478','MAR-01-2011','MAR-30-2011','TS0104');

insert into ASSIGN values ('AS0205','E96145','JUN-15-2019','NOV-01-2019','TS0105');

insert into ASSIGN values ('AS0206','E46571','APR-30-2009','MAY-28-2009','TS0106');

insert into ASSIGN values ('AS0207','E58936','NOV-01-2019','JAN-31-2020','TS0107');

insert into ASSIGN values ('AS0208','E56789','APR-05-2019','MAY-05-2020','TS0108');

insert into ASSIGN values ('AS0209','E23461','AUG-31-2013','NOV-01-2013','TS0109');

insert into ASSIGN values ('AS0210','E31689','JAN-01-2018','JUL-04-2018','TS0110');

insert into WORKLOG values ('WL0301','AS0201','aug-01-2004','32','B0001');

insert into WORKLOG values ('WL0302','AS0202','may-01-2010','25','B0002');

insert into WORKLOG values ('WL0303','AS0203','Dec-25-2011','17','B0003');

insert into WORKLOG values ('WL0304','AS0204','MAR-30-2011','40','B0004');

insert into WORKLOG values ('WL0305','AS0205','NOV-01-2019','27','B0005');

insert into WORKLOG values ('WL0306','AS0206','MAY-28-2009','16','B0006');

insert into WORKLOG values ('WL0307','AS0207','JAN-31-2020','10','B0007');

insert into WORKLOG values ('WL0308','AS0208','MAY-05-2020','52','B0008');

insert into WORKLOG values ('WL0309','AS0209','NOV-01-2013','08','B0009');

insert into WORKLOG values ('WL0310','AS0210','JUL-04-2018','22','B0010');