Convert the following Oracle database script to SQl server: --select table_name
ID: 3879681 • Letter: C
Question
Convert the following Oracle database script to SQl server:
--select table_name from all_tables where owner='PROP2'
drop table branch cascade constraint;
drop table client cascade constraint;
drop table staff cascade constraint;
drop table viewing cascade constraint;
drop table property_for_rent cascade constraint;
drop table registration cascade constraint;
drop table private_owner cascade constraint;
drop table lease cascade constraint;
CREATE TABLE branch
(branchno VARCHAR2(5)
,street VARCHAR(100)
,city VARCHAR2(20)
,postcode varchar2(10)
,PRIMARY KEY (branchno)
)
/
CREATE TABLE client
(clientno VARCHAR2(5)
,fname VARCHAR(8)
,lname VARCHAR(8)
,address varchar2(35)
,tel_no varchar2(14)
,pref_type char(6)
,max_rent number(4)
,PRIMARY KEY (clientno)
)
/
create table viewing
( clientno VARCHAR2(5)
,propertyno varchar2(5)
,viewdate date
,comments varchar2(200)
,PRIMARY KEY (clientno, propertyno)
)
/
CREATE TABLE private_owner
(ownerno VARCHAR2(4)
,fname VARCHAR2(20)
,lname VARCHAR(20)
,address varchar2(50)
,tel_no varchar2(14)
,PRIMARY KEY (ownerno)
)
/
create table registration
( clientno VARCHAR2(5)
,branchno varchar2(5)
,staffno varchar2(5)
,datejoined date
,PRIMARY KEY (clientno, branchno, staffno)
)
/
CREATE TABLE staff
(staffno VARCHAR2(5)
,fname VARCHAR(15)
,lname VARCHAR(15)
,position varchar2(15)
,sex char(1)
,dob date
,salary number(7,2)
,branchno varchar2(5)
,FOREIGN KEY(branchno) REFERENCES branch(branchno)
,PRIMARY KEY (staffno)
)
/
CREATE TABLE property_for_rent
(propertyno VARCHAR2(5)
,street VARCHAR(15)
,city VARCHAR(10)
,postcode varchar2(6)
,type char(7)
,rooms number(2)
,rent number(4)
,ownerno varchar2(4)
,branchno varchar2(5)
,staffno varchar(4)
,FOREIGN KEY(branchno) REFERENCES branch(branchno)
,PRIMARY KEY (propertyno)
)
/
create table lease
( lease_no varchar2(6)
,property_no varchar2(4)
,client_no varchar2(4)
,rent number(4)
,payment_method char(7)
,deposit number(6)
,paid char(1)
,rent_start date
,rent_finish date
,duratn number)
/
grant select on branch to public;
grant select on client to public;
grant select on staff to public;
grant select on viewing to public;
grant select on property_for_rent to public;
grant select on registration to public;
grant select on private_owner to public;
grant select on lease to public;
Explanation / Answer
--we need to alter statement if table contain foreign key
DROP TABLE branch;
DROP TABLE client;
ALTER TABLE staff DROP CONSTRAINT FK_branchno;--check table properties and please provide foreign key name correctly
DROP TABLE staff;
ALTER TABLE viewing DROP CONSTRAINT FK_propertyno;--check table properties and please provide foreign key name correctly
DROP TABLE viewing;
ALTER TABLE property_for_rent DROP CONSTRAINT FK_branchno;--check table properties and please provide foreign key name correctly
DROP TABLE property_for_rent;
DROP TABLE registration;
DROP TABLE private_owner;
DROP TABLE lease;
CREATE TABLE branch
(branchno VARCHAR2(5) PRIMARY KEY
,street VARCHAR(100)
,city VARCHAR2(20)
,postcode varchar2(10)
);
CREATE TABLE client
(clientno VARCHAR2(5) PRIMARY KEY
,fname VARCHAR(8)
,lname VARCHAR(8)
,address varchar2(35)
,tel_no varchar2(14)
,pref_type char(6)
,max_rent number(4)
);
create table viewing
( clientno VARCHAR2(5)
,propertyno varchar2(5)
,viewdate date
,comments varchar2(200)
,CONSTRAINT viewing_pk PRIMARY KEY (clientno, propertyno)
);
CREATE TABLE private_owner
(ownerno VARCHAR2(4) PRIMARY KEY
,fname VARCHAR2(20)
,lname VARCHAR(20)
,address varchar2(50)
,tel_no varchar2(14)
);
create table registration
( clientno VARCHAR2(5)
,branchno varchar2(5)
,staffno varchar2(5)
,datejoined date
,CONSTRAINT registration_pk PRIMARY KEY(clientno, branchno, staffno)
);
CREATE TABLE staff
(staffno VARCHAR2(5) PRIMARY KEY
,fname VARCHAR(15)
,lname VARCHAR(15)
,position varchar2(15)
,sex char(1)
,dob date
,salary number(7,2)
,branchno varchar2(5)
,CONSTRAINT fk_staff_branchno FOREIGN KEY(branchno) REFERENCES branch(branchno)
);
CREATE TABLE property_for_rent
(propertyno VARCHAR2(5) PRIMARY KEY
,street VARCHAR(15)
,city VARCHAR(10)
,postcode varchar2(6)
,type char(7)
,rooms number(2)
,rent number(4)
,ownerno varchar2(4)
,branchno varchar2(5)
,staffno varchar(4)
,CONSTRAINT fk_property_branchno FOREIGN KEY(branchno) REFERENCES branch(branchno)
);
create table lease
( lease_no varchar2(6)
,property_no varchar2(4)
,client_no varchar2(4)
,rent number(4)
,payment_method char(7)
,deposit number(6)
,paid char(1)
,rent_start date
,rent_finish date
,duratn number);
grant select on branch to public;
grant select on client to public;
grant select on staff to public;
grant select on viewing to public;
grant select on property_for_rent to public;
grant select on registration to public;
grant select on private_owner to public;
grant select on lease to public;