text zadatka:
There are 2 independent databases (D1, D2). Each database contains one stored procedure A, two views (B, C) and one table D. Objects from D1 are similar to the objects of D2 (the same objects with little differences). Please create (write DDL scripts) the pairs of these objects if:
* Stored Proc’s A use Tables D and Views C. These proc’s are different in a parameter set (parameters are used in the queries from D or C).
* Tables D are different in primary keys (column names and column types within the tables have to be equal). Table D from D2 has one additional character field.
* Views B use Tables D and Views C. These views are different in a column set
* Views C use Tables D. These views are different in WHERE clause
First part: write DDL for all the objects for database D1 (in proper order of creating), then for D2. There must be no any cross-database references (objects of D1 should not use objects of D2).
Second part: write all the DDL operations (full SQL statements are preferred, but if you cannot list full DDL operations because of time limitation – list just the operation name with the shortest description, see the example) in proper dependency order required for making database D2 to be equal with database D1 without the errors (all changes are made in the database D2). As result all D2 objects get altered.
You may use DROP, ALTER or CREATE statements where you want. Using of ALTER is preferred instead of object re-creation
MOJE RESENJE:
USE [D1]
GO
CREATE TABLE D
(
COL1 INT,
COL2 CHAR,
COL3 CHAR,
PRIMARY KEY (COL1)
)
CREATE VIEW C
(
COL1 INT,
COL2 CHAR
)
AS SELECT COL1, COL2
FROM D
WHERE COL1 > 1200
CREATE VIEW B
(
COL1 INT,
COL2 CHAR
)
AS SELECT C.COL1, D.COL2
FROM C INNER JOIN D ON C.COL1=D.COL1
CREATE PROCEDURE A
@COL1 INT,
@COL2 CHAR
AS
INSERT INTO D (COL1, COL2) VALUES (@COL1, @COL2)
WHERE @COL2=
(SELECT COL2 FROM C)
USE [D2]
GO
CREATE TABLE D
(
COL1 INT,
COL2 CHAR,
COL3 CHAR,
COL4 CHAR,
PRIMARY KEY (COL3)
)
CREATE VIEW C
(
COL1 INT,
COL2 CHAR
)
AS SELECT COL1, COL2
FROM D
WHERE COL1 < 100
CREATE VIEW B
(
COL1 INT,
COL3 CHAR
)
AS SELECT C.COL1, D.COL3
FROM C INNER JOIN D ON C.COL1=D.COL1
CREATE PROCEDURE A
@COL1 INT,
@COL3 CHAR
AS
INSERT INTO D (COL1, COL2) VALUES (@COL2, @COL3)
WHERE @COL3=
(SELECT COL2 FROM C)
______
USE [D2]
GO
ALTER TABLE D
DROP COLUMN COL4,
DROP CONSTRAINT PRIMARY KEY (COL3),
ADD CONSTRAINT PRIMARY KEY (COL1)
ALTER VIEW C
(
COL1 INT,
COL2 CHAR
)
AS SELECT COL1, COL2
FROM D
WHERE COL1 > 1200
ALTER VIEW B
(
COL1 INT,
COL2 CHAR
)
AS SELECT C.COL1, D.COL2
FROM C INNER JOIN D ON C.COL1=D.COL1
ALTER PROCEDURE A
@COL1 INT,
@COL2 CHAR
AS
INSERT INTO D (COL1, COL2) VALUES (@COL1, @COL2)
WHERE @COL2=
(SELECT COL2 FROM C)
hvala unapred!!!