Solution
Pashikanti Sneha answered on
Apr 01 2023
1. Create database and tables and insert records into tables (SQL Server)
CREATE DATABASE your_last_name_zoo;
GO
CREATE TABLE DEPARTMENTS
(
dept_id int NOT NULL PRIMARY KEY,
name varchar(50) NOT NULL
);
CREATE TABLE BUILDING
(
building_id int NOT NULL PRIMARY KEY,
name varchar(50) NOT NULL,
enclosure varchar(50),
sponsor varchar(100)
);
CREATE TABLE CONTRACTOR
(
contractor_id int NOT NULL PRIMARY KEY,
contractor_name varchar(50) NOT NULL,
contractor_address varchar(50),
contractor_city varchar(50),
contractor_state varchar(50),
contractor_zip varchar(10),
contractor_phone varchar(15)
);
CREATE TABLE EMPLOYEE
(
employee_id int NOT NULL PRIMARY KEY,
contractor_id int NOT NULL,
first_name varchar(50),
last_name varchar(50),
email varchar(50),
ssn varchar(15),
street_address varchar(50),
city varchar(10),
state varchar(50),
zip varchar(10),
department_id int NOT NULL,
building_assigned int NOT NULL
);
Insert into BUILDING (BUILDING_ID,NAME,ENCLOSURE,SPONSOR) values (1,'Building Four','Reptiles','The Hyogo University of Education');
Insert into BUILDING (BUILDING_ID,NAME,ENCLOSURE,SPONSOR) values (2,'Building Two','Rino','The University of Arkansas');
Insert into BUILDING (BUILDING_ID,NAME,ENCLOSURE,SPONSOR) values (3,'Building Three','Gorilla','The Australian College of Kuwait');
Insert into BUILDING (BUILDING_ID,NAME,ENCLOSURE,SPONSOR) values (4,'Building Five','Elephant','The Chung Shan Medical and Dental College');
Insert into BUILDING (BUILDING_ID,NAME,ENCLOSURE,SPONSOR) values (5,'Building Three','Gorilla','The Australian College of Kuwait');
Insert into BUILDING (BUILDING_ID,NAME,ENCLOSURE,SPONSOR) values (6,'Building Four','Reptiles','The Hyogo University of Education');
Insert into BUILDING (BUILDING_ID,NAME,ENCLOSURE,SPONSOR) values (7,'Building Five','Elephant','The Chung Shan Medical and Dental College');
Insert into BUILDING (BUILDING_ID,NAME,ENCLOSURE,SPONSOR) values (8,'Building Five','Elephant','The Chung Shan Medical and Dental College');
INSERT INTO DEPARTMENTS (dept_id,name) VALUES (1,'Animal Feeding');
INSERT INTO DEPARTMENTS (dept_id,name) VALUES (2,'Veterinarians');
INSERT INTO DEPARTMENTS (dept_id,name) VALUES (3,'Animal Feeding');
INSERT INTO DEPARTMENTS (dept_id,name) VALUES (4,'Animal Feeding');
INSERT INTO DEPARTMENTS (dept_id,name) VALUES (5,'Janitorial');
INSERT INTO DEPARTMENTS (dept_id,name) VALUES (6,'Janitorial');
INSERT INTO DEPARTMENTS (dept_id,name) VALUES (7,'Janitorial');
INSERT INTO DEPARTMENTS (dept_id,name) VALUES (8,'Maintenance');
Insert into CONTRACTOR...