Employee_available_date_slots

View All Public Scripts Login to Run Script

Script Name Employee_available_date_slots
Visibility Unlisted - anyone with the share link can access
Description SQL for creating sample data for finding available date slots.
Area SQL Analytics
Contributor Sample Tables script with data
Created Thursday May 11, 2023

Statement 1

CREATE TABLE PROJECTSM 
    (PROJECT_NUMBER VARCHAR2(30), 
    START_DATE DATE, 
    END_DATE DATE)

Table created.

Statement 2

INSERT INTO PROJECTSM VALUES('P1000','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 3

INSERT INTO PROJECTSM VALUES('P1001','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 4

CREATE TABLE EMPPROJECTS 
(EMPNO VARCHAR2(30), 
    PROJECT_NUMBER VARCHAR2(100), 
    PROJECT_IN_DATE DATE, 
    PROJECT_OUT_DATE DATE)

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 5

INSERT INTO EMPPROJECTS VALUES ('001','P1000','15-JAN-2023','06-JUN-2023')

1 row(s) inserted.

Statement 6

INSERT INTO EMPPROJECTS VALUES ('001','P1000','30-JAN-2023','17-JUL-2023')

1 row(s) inserted.

Statement 7
```
COMMIT
```
```
Statement processed.
```

Statement 8

SELECT * FROM EMPPROJECTS


EMPNO PROJECT_NUMBER PROJECT_IN_DATE PROJECT_OUT_DATE
001 P1000 15-JAN-23 06-JUN-23
001 P1000 30-JAN-23 17-JUL-23
001 P1000 15-JAN-23 06-JUN-23
001 P1000 30-JAN-23 17-JUL-23

4 rows selected.

Statement 9
```
   COMMIT
```
```
Statement processed.
```

EMPNO	PROJECT_NUMBER	PROJECT_IN_DATE	PROJECT_OUT_DATE
001	P1000	15-JAN-23	06-JUN-23
001	P1000	30-JAN-23	17-JUL-23
001	P1000	15-JAN-23	06-JUN-23
001	P1000	30-JAN-23	17-JUL-23

Statement 10

INSERT INTO PROJECTSM VALUES('P1001','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 11
```
    COMMIT
```
```
Statement processed.
```
Statement 12
```
SELECT * FROM PROJECTM
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942
Statement 13
```
SELECT * FROM PROJECTM
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 14

SELECT * FROM PROJECTSM


PROJECT_NUMBER START_DATE END_DATE
P1000 01-JAN-23 31-DEC-23
P1001 01-JAN-23 31-DEC-23
P1000 01-JAN-23 31-DEC-23
P1001 01-JAN-23 31-DEC-23

4 rows selected.

Statement 15
```
DROP TABLE PROJECTSM
```
```
Table dropped.
```

PROJECT_NUMBER	START_DATE	END_DATE
P1000	01-JAN-23	31-DEC-23
P1001	01-JAN-23	31-DEC-23
P1000	01-JAN-23	31-DEC-23
P1001	01-JAN-23	31-DEC-23

Statement 16

    DROP TABLE EMPPROJECTS

Table dropped.

Statement 17

CREATE TABLE PROJECTSM 
    (PROJECT_NUMBER VARCHAR2(30), 
    START_DATE DATE, 
    END_DATE DATE)

Table created.

Statement 18

INSERT INTO PROJECTSM VALUES('P1000','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 19

    INSERT INTO PROJECTSM VALUES('P1001','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 20
```
    COMMIT
```
```
Statement processed.
```

Statement 21

CREATE TABLE EMPPROJECTS 
(EMPNO VARCHAR2(30), 
    PROJECT_NUMBER VARCHAR2(100), 
    PROJECT_IN_DATE DATE, 
    PROJECT_OUT_DATE DATE)

Table created.

Statement 22

INSERT INTO EMPPROJECTS VALUES ('001','P1000','15-JAN-2023','06-JUN-2023')

1 row(s) inserted.

Statement 23

INSERT INTO EMPPROJECTS VALUES ('001','P1000','30-JAN-2023','17-JUL-2023')

1 row(s) inserted.

Statement 24
```
COMMIT
```
```
Statement processed.
```

Statement 25

SELECT * FROM EMPPROJECTS


EMPNO PROJECT_NUMBER PROJECT_IN_DATE PROJECT_OUT_DATE
001 P1000 15-JAN-23 06-JUN-23
001 P1000 30-JAN-23 17-JUL-23

2 rows selected.

EMPNO	PROJECT_NUMBER	PROJECT_IN_DATE	PROJECT_OUT_DATE
001	P1000	15-JAN-23	06-JUN-23
001	P1000	30-JAN-23	17-JUL-23

Statement 26

    SELECT * FROM PROJECTSM


PROJECT_NUMBER START_DATE END_DATE
P1000 01-JAN-23 31-DEC-23
P1001 01-JAN-23 31-DEC-23

2 rows selected.

PROJECT_NUMBER	START_DATE	END_DATE
P1000	01-JAN-23	31-DEC-23
P1001	01-JAN-23	31-DEC-23

Statement 27

SELECT * FROM EMPPROJECTS


EMPNO PROJECT_NUMBER PROJECT_IN_DATE PROJECT_OUT_DATE
001 P1000 15-JAN-23 06-JUN-23
001 P1000 30-JAN-23 17-JUL-23

2 rows selected.

EMPNO	PROJECT_NUMBER	PROJECT_IN_DATE	PROJECT_OUT_DATE
001	P1000	15-JAN-23	06-JUN-23
001	P1000	30-JAN-23	17-JUL-23

Statement 28

 SELECT * FROM PROJECTSM


PROJECT_NUMBER START_DATE END_DATE
P1000 01-JAN-23 31-DEC-23
P1001 01-JAN-23 31-DEC-23

2 rows selected.

Statement 29
```
DROP TABLE PROJECTSM
```
```
Table dropped.
```

PROJECT_NUMBER	START_DATE	END_DATE
P1000	01-JAN-23	31-DEC-23
P1001	01-JAN-23	31-DEC-23

Statement 30

    DROP TABLE EMPPROJECTS

Table dropped.

Statement 31

CREATE TABLE PROJECTSM 
    (PROJECT_NUMBER VARCHAR2(30), 
    START_DATE DATE, 
    END_DATE DATE)

Table created.

Statement 32

INSERT INTO PROJECTSM VALUES('P1000','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 33

    INSERT INTO PROJECTSM VALUES('P1001','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 34
```
    COMMIT
```
```
Statement processed.
```

Statement 35

CREATE TABLE EMPPROJECTS 
(EMPNO VARCHAR2(30), 
    PROJECT_NUMBER VARCHAR2(100), 
    PROJECT_IN_DATE DATE, 
    PROJECT_OUT_DATE DATE)

Table created.

Statement 36

INSERT INTO EMPPROJECTS VALUES ('001','P1000','15-JAN-2023','06-JUN-2023')

1 row(s) inserted.

Statement 37

INSERT INTO EMPPROJECTS VALUES ('001','P1000','30-JAN-2023','17-JUL-2023')

1 row(s) inserted.

Statement 38
```
COMMIT
```
```
Statement processed.
```

Statement 39

SELECT * FROM EMPPROJECTS


EMPNO PROJECT_NUMBER PROJECT_IN_DATE PROJECT_OUT_DATE
001 P1000 15-JAN-23 06-JUN-23
001 P1000 30-JAN-23 17-JUL-23

2 rows selected.

EMPNO	PROJECT_NUMBER	PROJECT_IN_DATE	PROJECT_OUT_DATE
001	P1000	15-JAN-23	06-JUN-23
001	P1000	30-JAN-23	17-JUL-23

Statement 40

    SELECT * FROM PROJECTSM


PROJECT_NUMBER START_DATE END_DATE
P1000 01-JAN-23 31-DEC-23
P1001 01-JAN-23 31-DEC-23

2 rows selected.

Statement 41
```
DROP TABLE PROJECTSM
```
```
Table dropped.
```

PROJECT_NUMBER	START_DATE	END_DATE
P1000	01-JAN-23	31-DEC-23
P1001	01-JAN-23	31-DEC-23

Statement 42

    DROP TABLE EMPPROJECTS

Table dropped.

Statement 43

CREATE TABLE PROJECTSM 
    (PROJECT_NUMBER VARCHAR2(30), 
    START_DATE DATE, 
    END_DATE DATE)

Table created.

Statement 44

INSERT INTO PROJECTSM VALUES('P1000','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 45

INSERT INTO PROJECTSM VALUES('P1001','01-JAN-2023','31-DEC-2023')

1 row(s) inserted.

Statement 46
```
    COMMIT
```
```
Statement processed.
```

Statement 47

CREATE TABLE EMPPROJECTS 
(EMPNO VARCHAR2(30), 
    PROJECT_NUMBER VARCHAR2(100), 
    PROJECT_IN_DATE DATE, 
    PROJECT_OUT_DATE DATE)

Table created.

Statement 48

INSERT INTO EMPPROJECTS VALUES ('001','P1000','15-JAN-2023','06-JUN-2023')

1 row(s) inserted.

Statement 49

INSERT INTO EMPPROJECTS VALUES ('001','P1000','30-JUN-2023','17-JUL-2023')

1 row(s) inserted.

Statement 50
```
COMMIT
```
```
Statement processed.
```

Statement 51

SELECT * FROM EMPPROJECTS


EMPNO PROJECT_NUMBER PROJECT_IN_DATE PROJECT_OUT_DATE
001 P1000 15-JAN-23 06-JUN-23
001 P1000 30-JUN-23 17-JUL-23

2 rows selected.

EMPNO	PROJECT_NUMBER	PROJECT_IN_DATE	PROJECT_OUT_DATE
001	P1000	15-JAN-23	06-JUN-23
001	P1000	30-JUN-23	17-JUL-23

Statement 52

    SELECT * FROM PROJECTSM


PROJECT_NUMBER START_DATE END_DATE
P1000 01-JAN-23 31-DEC-23
P1001 01-JAN-23 31-DEC-23

2 rows selected.

PROJECT_NUMBER	START_DATE	END_DATE
P1000	01-JAN-23	31-DEC-23
P1001	01-JAN-23	31-DEC-23

Statement 53

SELECT * FROM EMPPROJECTS


EMPNO PROJECT_NUMBER PROJECT_IN_DATE PROJECT_OUT_DATE
001 P1000 15-JAN-23 06-JUN-23
001 P1000 30-JUN-23 17-JUL-23

2 rows selected.

EMPNO	PROJECT_NUMBER	PROJECT_IN_DATE	PROJECT_OUT_DATE
001	P1000	15-JAN-23	06-JUN-23
001	P1000	30-JUN-23	17-JUL-23

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database