I am making a database and i am getting errors. Can you tell me what i can do to
ID: 3578397 • Letter: I
Question
I am making a database and i am getting errors. Can you tell me what i can do to fix the errors?
Msg 102, Level 15, State 1, Line 75
Incorrect syntax near '2'.
Msg 102, Level 15, State 1, Line 83
Incorrect syntax near '7'.
Msg 102, Level 15, State 1, Line 91
Incorrect syntax near '12'.
Msg 102, Level 15, State 1, Line 99
Incorrect syntax near 'system analyst'.
This is the database i made so far.
use [Human Resources (HR) Department]
create table employee(
emp_id int identity not null,
first_name varchar(30),
last_name varchar (30),
e_mail varchar (30),
hire_date varchar (30),
phone_number varchar (30),
constraint pk_employee
primary key(emp_id)
);
create table job(
job_id int identity not null,
emp_id int not null,
job_title varchar (30),
min_salary numeric (5,2),
max_salary numeric (5,2),
constraint pk_job
primary key (job_id),
constraint fk_job_employee
foreign key (emp_id)
references employee (emp_id)
on delete cascade on update cascade,
);
create table skill(
skill_id int identity not null,
skill_type varchar (30),
skill_description varchar (30),
emp_id int not null,
job_id int not null,
constraint pk_skill
primary key (skill_id),
constraint fk_ps_skill_employee
foreign key (emp_id)
references employee (emp_id)
on delete cascade on update cascade,
constraint fk_ps_skill_job
foreign key (job_id)
references job (job_id)
on delete cascade on update cascade,
);
create table position(
job_title varchar(30),
job_id int not null,
emp_id int not null,
job_grade varchar(30),
constraint fk_ps_position_job
foreign key (job_id)
references job (job_id)
on delete cascade on update cascade,
contraint fk_ps_position_employee
foreign key (emp_id)
references employee (emp_id)
on delete cascade on update cascade,
);
Use [Human Resources (HR) Department];
insert into employee (emp_id, first_name, last_name, e_mail, hire_date, phone_number)
values
(1, 'frank','douglas','frank@hotmail.com','2012-12-12','233-33-44')
(2, 'john','smith','john@hotmail.com','2014-10-10','224-49-82')
(3, 'brax','rodriguez','brax@hotmail.com','2015-10-10','224-49-99')
(4, 'damian','golec','damian@hotmail.com','2016-10-10','225-55-55')
(5, 'mariusz','chorazy','mariusz@hotmail.com','2015-10-11','223-66-66');
insert into job (job_id, emp_id, job_title, min_salary, max_salary)
values
(6,1,'business analyst','30000.00', '50000.00')
(7,2,'system analyst','40000.00','50000.00')
(8,3,'website designer','50000.00','60000.00')
(9,4,'database developer','50000.00','70000.00')
(10,5,'network administrator','50000.00','80000.00');
insert into skill (skill_id, skill_type, skill_description, emp_id, job_id)
values
(11,'c++','programming',1,6)
(12,'visual basic','programming',7,2)
(13,'sql','database',8,3)
(14,'html','web design',9,4)
(15,'links computers','networking',10,5);
insert into position (job_title,job_id,emp_id,job_grade)
values
('business analyst',6,1,'b')
('system analyst',7,2,'a')
('website designer',8,3,'c')
('database developer',9,4,'a')
('network administrator',10,5,'b');
This is in a new query(same database)
use [Human Resources (HR) Department];
(
select s.skill_id, s.skill_type, s.skill_description, e.emp_id, j.job_id
from skill s
inner join employee e
on e.emp_id =
e.emp_id
inner join job j
on j.job_id =
s.job_id
);
use [Human Resources (HR) Department];
(
select p.job_title, e.emp_id, j.job_id
from position p
inner join employee e
on e.emp_id =
p.emp_id
inner join job j
on j.job_id =
p.job_id
);
Explanation / Answer
insert into skill (skill_id, skill_type, skill_description, emp_id, job_id)
values
(11,'c++','programming',1,6)
(12,'visual basic','programming',7,2)
(13,'sql','database',8,3)
(14,'html','web design',9,4)
(15,'links computers','networking',10,5);
Alter the bold values....That means change the insert statement as
insert into skill (skill_id, skill_type, skill_description, emp_id, job_id)
values
(11,'c++','programming',1,6)
(12,'visual basic','programming',2,7)
(13,'sql','database',3,8)
(14,'html','web design',4,9)
(15,'links computers','networking',5,10);
as the emp_id values should be present to before selecting.Check the emp_id values in employee table which has only 5 values for emp_id...
Also for position tables values do the same
insert into position (job_title,job_id,emp_id,job_grade)
values
('business analyst',6,1,'b')
('system analyst',7,2,'a')
('website designer',8,3,'c')
('database developer',9,4,'a')
('network administrator',10,5,'b');
While creating tables also if u get any error Check the values in bold below and correct in ur database creation
create table employee(emp_id number not null,
first_name varchar(30),
last_name varchar (30),
e_mail varchar (30),
hire_date varchar (30),
phone_number varchar (30),
constraint pk_employee
primary key(emp_id)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.