I am having issues with SQL code:

Question

I am having issues with my SQL here is my code:

CREATE TABLE Campuses
(CampusID int,
CampusName varchar(20),
CONSTRAINT pkCampuses PRIMARY KEY (CampusID))

select * from Campuses
exec sp_help Campuses

CREATE TABLE Courses
(CourseID int,
CoursePrefix char(4),
CourseNumber char(3),
CourseName varchar(50),
CONSTRAINT pkCourses PRIMARY KEY (CourseID))

select * from Courses
exec sp_help Courses

CREATE TABLE ZipCodes
(Zipcode char(10),
City varchar(20),
StateSbbr char(2),
CONSTRAINT pkZipCode PRIMARY KEY (ZipCode))

select * from ZipCodes
exec sp_help Zipcodes

CREATE TABLE Buildings
(BuildingID int,
BldAddress varchar (50),
BuildingName varchar (50),
ZipCode char (10),
CampusID int,
CONSTRAINT pkBuildingID PRIMARY KEY (BuildingID),
CONSTRAINT fkZipCode FOREIGN KEY (ZipCode) references ZipCodes,
CONSTRAINT fkCampusID FOREIGN KEY (CampusID) references Campuses)

select * from Buildings
exec sp_help Buildings

CREATE TABLE FacultyOffices
(OfficeNumber varchar (4),
BuildingID int,
CampusID int,
CONSTRAINT pkOfficeNumber PRIMARY KEY (OfficeNumber, BuildingID, CampusID))

select * from FacultyOffices
exec sp_help FacultyOffices

CREATE TABLE Faculty
(FacultyID int,
FirstName varchar (20),
Lastname varchar (20),
FacAddress varchar (50),
FacRank int,
Salary decimal (8,2),
Specially varchar (50),
OfficeNumber varchar (4),
BuildingID int,
CampusID int,
ZipCode char(10),
CONSTRAINT pkFacultyID PRIMARY KEY (FacultyID),
CONSTRAINT fkFacultyOfficesBuildingCampuses FOREIGN KEY (OfficeNumber, BuildingID, CampusID) references FacultyOffices,
CONSTRAINT fkZipCodeFac FOREIGN KEY (ZipCode) references ZipCodes)

select * from Faculty
exec sp_help Faculty

CREATE TABLE ClassRooms
(RoomNumber varchar(4),
Occupancy int,
BuildingID int,
CampusID int,
CONSTRAINT pkRoomNumber PRIMARY KEY (RoomNumber, BuildingID, CampusID))

select * from ClassRooms
exec sp_help ClassRooms

CREATE TABLE Students
(StudentID int,
FirstName varchar(15),
LastName varchar(15),
StreetAddress varchar(30),
PhoneNumber char(10),
BirthDate date,
ZipCode char(10),
FacultyID int not null,
MentorID int,
CONSTRAINT pkStudentID PRIMARY KEY (StudentID),
CONSTRAINT fkFacultyID FOREIGN KEY (FacultyID) references Faculty,
CONSTRAINT fkMentorID FOREIGN KEY (MentorID) references Students)

select * from Students
exec sp_help Students

CREATE TABLE Sections
(SectionID int,
SectionNumber char(3),
Term varchar(8),
TermYear char(4),
CourseID int,
RoomNumber varchar(4),
BuildingID int,
CampusID int,
FacultyID int not null,
CONSTRAINT pkSectionID PRIMARY KEY (SectionID),
CONSTRAINT fkSecRoomBuildingsCampuses FOREIGN KEY (RoomNumber, BuildingID, CampusID) references ClassRooms,
CONSTRAINT fkFacultyID_Sections FOREIGN KEY (FacultyID) references Faculty,
CONSTRAINT fkCourseID FOREIGN KEY (CourseID) references Courses)

select * from Sections
exec sp_help Sections
CREATE TABLE EnrollsIn
(StudentID int not null,
SectionID int not null,
StartDate date not null,
EndDate date,
Grade varchar(2),
CONSTRAINT pkEnrollsIn PRIMARY KEY (StudentID, SectionID),
CONSTRAINT fkStudentID FOREIGN KEY (StudentID) references Students,
CONSTRAINT fkSectionID FOREIGN KEY (SectionID) references Sections)

select * from EnrollsIn
exec sp_help EnrollsIn

THE ERROR I AM GETTING IS:
Msg 2714, Level 16, State 6, Line 2
There is already an object named ‘Campuses’ in the database.

My question is how do I fix this error?

Summary

Here in the given question, we have to solve the issues. Issues with the SQL code.

Explanation

To solve the issue first we should know that SQL never allows its user to create a table with the same name. So that if the user tries to make the table with the same name. SQL gives an error that “There is already an object”. Also to avoid this we have to check if there is already a table with the name that we are going to create. In order to avoid that we can delete the first table. So that we can create a new one with the name we want. And to delete the table there is one command in SQL. And that is the DROP command.

So in the above-given question if the user is willing to create a new table. Table with the name Campuses, then he must delete the first table. From the SQL code in the database. And he can do this with the below line of SQL code:

SQL code for this:

DROP TABLE Campuses;

CREATE TABLE Campuses
(CampusID int,
CampusName varchar(20),
CONSTRAINT pkCampuses PRIMARY KEY (CampusID));

 

Also read, Draw ER diagram, and elaborate database design outline.

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *