sql server

CREATE TABLE DEPARTMENT (
      DepartmentName char(35)       NOT NULL,
      BudgetCode     char(30)      NOT NULL,
      OfficeNumber   char(15)      NOT NULL,
      Phone             char(10)       NOT NULL,      
      CONSTRAINT             DEPARTMENT_PK PRIMARY KEY(DepartmentNumber)
      );

CREATE TABLE EMPLOYEE (
      EmployeeNumber   Integer       NOT NULL,
      LastName         Char(25)       NOT NULL,
      FirstName             Char(25)        NOT NULL,
      Department         Char(35)        NOT NULL,
      Phone            char(12)   NULL,
      Email            char(100)      NOT Null,
      CONSTRAINT             EMPLOYEE_PK       PRIMARY KEY (EmployeeNumber)
      );

CREATE TABLE PROJECT (
      ProjectID        Integer        NOT NULL,
      Name              char(50)       NOT NULL,
      Department  char(35)      NOT NULL,
      MaxHours        double      NOT NULL,
      StartDate        date            NOT NULL,
      EndDate     date        Null,
      CONSTRAINT             PROJECT_PK       PRIMARY KEY (ProjectID)
    CONSTRAINT      PROJECT_FK  FOREIGN KEY (Department)
      REFERENCES EMPLOYEE(Department)
      );
      
CREATE TABLE ASSIGNMENT (
      ProjectID            Integer            NOT NULL,
      EmployeeNumber      INTEGER        NOT NULL,
      HoursWorked          DOUBLE      NOT NULL,
      CONSTRAINT             ASSIGNMENT_PK  PRIMARY KEY (ProjectID,EmployeeNumber)
      CONSTRAINT      ASSIGNMENT_FK  FOREIGN KEY (ProjectID)
      REFERENCES PROJECT(ProjectID)
      CONSTRAINT      ASSIGNMENT_FK  FOREIGN KEY (EmployeeNumber)
      REFERENCES EMPLOYEE(EmployeeNumber)
      );



i'm getting this error!

Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'NOT'.
Msg 156, Level 15, State 1, Line 35
Incorrect syntax near the keyword 'NOT'.
glnjalsaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Change "double" to float:

Note you had some other errors.  more errors are still here but this will get you closer:

CREATE TABLE DEPARTMENT (
      DepartmentName char(35)       NOT NULL,
      BudgetCode     char(30)      NOT NULL,
      OfficeNumber   char(15)      NOT NULL,
      Phone             char(10)       NOT NULL,      
      CONSTRAINT             DEPARTMENT_PK PRIMARY KEY(DepartmentNumber)
      );

CREATE TABLE EMPLOYEE (
      EmployeeNumber   Integer       NOT NULL,
      LastName         Char(25)       NOT NULL,
      FirstName             Char(25)        NOT NULL,
      Department         Char(35)        NOT NULL,
      Phone            char(12)   NULL,
      Email            char(100)      NOT Null,
      CONSTRAINT             EMPLOYEE_PK       PRIMARY KEY (EmployeeNumber)
      );

CREATE TABLE PROJECT (
      ProjectID        Integer        NOT NULL,
      Name              char(50)       NOT NULL,
      Department  char(35)      NOT NULL,
      MaxHours        float      NOT NULL,
      StartDate        date            NOT NULL,
      EndDate     date        Null,
      CONSTRAINT             PROJECT_PK       PRIMARY KEY (ProjectID),
    CONSTRAINT      PROJECT_FK  FOREIGN KEY (Department)
      REFERENCES EMPLOYEE(Department)
      );
      
CREATE TABLE ASSIGNMENT (
      ProjectID            Integer            NOT NULL,
      EmployeeNumber      INTEGER        NOT NULL,
      HoursWorked          float      NOT NULL,
      CONSTRAINT             ASSIGNMENT_PK  PRIMARY KEY (ProjectID,EmployeeNumber) ,
      CONSTRAINT      ASSIGNMENT_FK  FOREIGN KEY (ProjectID)
      REFERENCES PROJECT(ProjectID),
      CONSTRAINT      ASSIGNMENT_FK  FOREIGN KEY (EmployeeNumber)
      REFERENCES EMPLOYEE(EmployeeNumber)
      );

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
Double is not a SQL datatype. You need to change it to real/float/....

once you fixed that, the script will complain again because you are missing some ,

CREATE TABLE DEPARTMENT (
       DepartmentName char(35)       NOT NULL,
       BudgetCode     char(30)      NOT NULL,
       OfficeNumber   char(15)      NOT NULL,
       Phone             char(10)       NOT NULL,      
       CONSTRAINT             DEPARTMENT_PK PRIMARY KEY(DepartmentNumber)
       );

 CREATE TABLE EMPLOYEE (
       EmployeeNumber   Integer       NOT NULL,
       LastName         Char(25)       NOT NULL,
       FirstName             Char(25)        NOT NULL,
       Department         Char(35)        NOT NULL,
       Phone            char(12)   NULL,
       Email            char(100)      NOT Null,
       CONSTRAINT             EMPLOYEE_PK       PRIMARY KEY (EmployeeNumber)
       );

 CREATE TABLE PROJECT (
       ProjectID        Integer        NOT NULL,
       Name              char(50)       NOT NULL,
       Department  char(35)      NOT NULL,
       MaxHours        FLOAT      NOT NULL,
       StartDate        date            NOT NULL,
       EndDate     date        Null,
       CONSTRAINT             PROJECT_PK       PRIMARY KEY (ProjectID),
     CONSTRAINT      PROJECT_FK  FOREIGN KEY (Department)
       REFERENCES EMPLOYEE(Department)
       );
       
 CREATE TABLE ASSIGNMENT (
       ProjectID            Integer            NOT NULL,
       EmployeeNumber      INTEGER        NOT NULL,
       HoursWorked          float      NOT NULL,
       CONSTRAINT             ASSIGNMENT_PK  PRIMARY KEY (ProjectID,EmployeeNumber) ,
       CONSTRAINT      ASSIGNMENT_FK  FOREIGN KEY (ProjectID)
       REFERENCES PROJECT(ProjectID),
       CONSTRAINT      ASSIGNMENT_FK  FOREIGN KEY (EmployeeNumber)
       REFERENCES EMPLOYEE(EmployeeNumber)
       );

Open in new window

glnjalsaAuthor Commented:
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'CONSTRAINT'.
Msg 156, Level 15, State 1, Line 37
Incorrect syntax near the keyword 'CONSTRAINT'.
 i'm getting this error now
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

glnjalsaAuthor Commented:
can you people help me In solving this?
Éric MoreauSenior .Net ConsultantCommented:
like we both pointed you, you are missing some ,

try my script, it is compiling just fine
Kyle AbrahamsSenior .Net DeveloperCommented:
Next error after that is Primary Key on DepartmentNumber . . . cause you don't have that column in the Department table.
Scott PletcherSenior DBACommented:
Use decimal, not double.  Use varchar not char.  Use all lower case for type names (since under certain conditions SQL gets errors if data types are not in lower case).  Also, changed the duplicate constraint name on the last table.

Finally, as noted above, column "DepartmentNumber" does not exist in the DEPARTMENT table, so that will cause an error.

CREATE TABLE DEPARTMENT (
      DepartmentName varchar(35) NOT NULL,
      BudgetCode     varchar(30) NOT NULL,
      OfficeNumber   varchar(15) NOT NULL,
      Phone          char(10)    NOT NULL,
      CONSTRAINT             DEPARTMENT_PK  PRIMARY KEY(DepartmentNumber)
      );

CREATE TABLE EMPLOYEE (
      EmployeeNumber  integer      NOT NULL,
      LastName        varchar(25)  NOT NULL,
      FirstName       varchar(25)  NOT NULL,
      Department      varchar(35)  NOT NULL,
      Phone           char(10)     NULL,
      PhoneExtension  char(2)      NULL,
      Email           varchar(100) NOT NULL,
      CONSTRAINT             EMPLOYEE_PK  PRIMARY KEY (EmployeeNumber)
      );

CREATE TABLE PROJECT (
      ProjectID       integer       NOT NULL,
      Name            varchar(50)   NOT NULL,
      Department      varchar(35)   NOT NULL,
      MaxHours        decimal(9, 2) NOT NULL,
      StartDate       date          NOT NULL,
      EndDate         date          NULL,
      CONSTRAINT             PROJECT_PK  PRIMARY KEY (ProjectID),
      CONSTRAINT             PROJECT_FK  FOREIGN KEY (Department) REFERENCES EMPLOYEE(Department)
      );
      
CREATE TABLE ASSIGNMENT (
      ProjectID       integer       NOT NULL,
      EmployeeNumber  integer       NOT NULL,
      HoursWorked     decimal(9, 2) NOT NULL,
      CONSTRAINT            ASSIGNMENT_PK  PRIMARY KEY (ProjectID,EmployeeNumber),
      CONSTRAINT            ASSIGNMENT_FK  FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID),
      CONSTRAINT            ASSIGNMENT_FK2 FOREIGN KEY (EmployeeNumber) REFERENCES EMPLOYEE(EmployeeNumber)
      );

Open in new window

Kyle AbrahamsSenior .Net DeveloperCommented:
Scott just a note there's a potential overflow potential if you use anything but float when working with doubles in .Net.  I know that's a leap but based off of what the guy was posting it's better to go with the larger value then a smaller one.
Scott PletcherSenior DBACommented:
I get your point.  But double is only an approximate data type.  For hours worked, that's not a good idea, and may not even be legal in some areas.  It's very disturbing if hours worked can overflow the largest exact-value data type in .Net.  Yikes!
Vitor MontalvãoMSSQL Senior EngineerCommented:
glnjalsa, do you still need help with this question?
glnjalsaAuthor Commented:
i need a linked list program in vb.net which has count,add,remove,swap properties in it. pls can anyone post it here
Kyle AbrahamsSenior .Net DeveloperCommented:
I would recommend posting a different question . . . though that sounds dangerously like homework.  Your original question has been answered.
Scott PletcherSenior DBACommented:
I agree, that's a completely different q, and thus it should be asked separately.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.