Step 2: System Catalog Scripts Using the System Catalog Views https://docs.micro

ID: 3859498 • Letter: S

Question

Step 2: System Catalog Scripts

Using the System Catalog Views

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql

create the SQL Scripts for the following views:

1. vw_TableNoIndexes: User tables with Indexes

2. vw_ProjectIdTables: All the tables which contain the column “projectId”

3. vw_Last7Obj: All the objects that have been modified in the last 7 days

4. vw_ProjectProcs: The SQL logic from the stored procedures which have “Project” in their name

Not sure if the code I provided below is needed for this, I think its from the link provided. I don't really understand the question, was hoping someone could help me with this.

HERE IS SOME OF THE CODE ALREADY WRITTEN:

create table Project
(
projectId char(4),
primary key(projectId),
projectName varchar(50),
firmFedID char(9),
fundedbudget decimal(16,2),
projectStartDate date,
projectstatus varchar(25),
projectTypeCode char(5),
projectedEndDate date,
projectManager char(8),
activityID char(4)
);
go

create table Activity
(
activityId char(4),
projectId char(4),
activityName varchar(50),
costToDate decimal(16,2),
activityStatus varchar(25),
startDate date,
endDate date
primary key(projectId,activityId)
);
go

create procedure sp_addUpdateProject
(
@projectId char(4),
@projectName varchar(50),
@firmFedID char(9),
@fundedbudget decimal(16,2),
@projectStartDate date,
@projectstatus varchar(25),
@projectTypeCode char(5),
@projectedEndDate date,
@projectManager char(8),
@activityId char(4)
)
as
begin

if exists(select * from Project where @projectId = projectId)
begin
update Project
set
projectName = @projectName,
firmFedID = @firmFedID,
fundedbudget = @fundedbudget,
projectStartDate = @projectStartDate,
projectstatus = @projectstatus,
projectTypeCode = @projectTypeCode,
projectedEndDate = @projectedEndDate,
projectManager = @projectManager,
activityId = @activityId
where @projectId = projectId;
end
else
begin
insert into Project(projectId, projectName, firmFedID, fundedBudget, projectStartDate,
projectStatus, projectTypeCode, projectedEndDate, projectManager, activityID)
values (
@projectId,
@projectName,
@firmFedID,
@fundedbudget,
@projectStartDate,
@projectstatus,
@projectTypeCode,
@projectedEndDate,
@projectManager,
@activityId);
end
end

create procedure SP_DeleteProject
(
@projectId char(4)
)
as
begin
delete from Project where projectId = @projectId;
end
go

create procedure sp_addUpdateActivity
(
@activityId char(4),
@activityName varchar(50),
@costToDate decimal(16,2),
@activityStatus varchar(25),
@startDate date,
@endDate date
)
as
begin

if exists(select * from Activity where activityId = @activityId)
begin
update Activity
set
activityName = @activityName,
costToDate = @costToDate,
activityStatus = @activityStatus,
startDate = @startDate,
endDate = @endDate
where @activityId = activityId;
end
else
begin
insert into Activity(activityId, activityName, costToDate, activityStatus, startDate, endDate)
values(
@activityId,
@activityName,
@costToDate,
@activityStatus,
@startDate,
@endDate)
end
end
go

create procedure SP_DeleteActivity
(
@projectId char(4),
@activityId char(4)
)
as
begin

delete from Activity where projectId = @projectId and activityId = @activityId;
end
go

create procedure SP_ProcessProjectDelay
(
@projectId char(4),
@DateDiff Int,
@projectedEndDate date,
@actualEndDate date
)
as
begin
select @projectedEndDate = projectedEndDate from Projects where projectId = @projectId
select @actualEndDate = endDate from Activity where projectId = @projectId order by endDate

@DateDiff = Datediff(@projectedenddate, @actualenddate)
if(@DateDiff < 0)
begin
update Projects set endDate = @actualenddate where projectId = @projectId
end
end

Explanation / Answer

create procedure SP_DeleteProject
(
@projectId char(4)
)
as
begin
delete from Project where projectId = @projectId;
end
go

create procedure sp_addUpdateActivity
(
@activityId char(4),
@activityName varchar(50),
@costToDate decimal(16,2),
@activityStatus varchar(25),
@startDate date,
@endDate date
)
as
begin

create procedure SP_DeleteActivity
(
@projectId char(4),
@activityId char(4)
)
as
begin

delete from Activity where projectId = @projectId and activityId = @activityId;
end
go

Navigate

Step 2: Summarize the following passage. Write a one-paragraph summary of the fo

Step 2: What is the height of the density curve between 0 and 2? Draw a graph of

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Step 2: System Catalog Scripts Using the System Catalog Views https://docs.micro

Question

Explanation / Answer

Related Questions

Navigate