This is an advanced business management question.

using SQL Server Management Studio, Write SQL queries for the following three questions using joins. You will need to execute the script provided below to answer these questions.

1)

Display the ManagerID, MFName, MLName, and buildindid and the building nuumber of floors of the buildings managed, for all managers. Write SQL queries using different types of joins

1. inner join,

2.right outer join,

3.left outer join ,

4.full join and

5.cross join

2) Display the MFName, MLName, MSalary, MBDate, and and buildindid that the manager manages for all managers with a salary less than $55,000. Use an appropriate join.

3) Display the buildingid, Apartment number, number of bedrooms, managerfirst name and manager last name of the buildings. Use an appropriate join.

/*Execute the following script to be able to work on the assignment on joins*/

CREATE TABLE manager

( managerid CHAR(4) NOT NULL,

mfname VARCHAR(15) NOT NULL,

mlname VARCHAR(15) NOT NULL,

mbdate DATE NOT NULL,

msalary NUMERIC(9,2) NOT NULL,

mbonus NUMERIC(9,2),

mresbuildingid CHAR(3),

PRIMARY KEY (managerid)

);

CREATE TABLE building

( buildingid CHAR(3) NOT NULL,

bnooffloors INT NOT NULL,

bmanagerid CHAR(4) NOT NULL,

PRIMARY KEY (buildingid),

FOREIGN KEY (bmanagerid) REFERENCES manager(managerid)

);

CREATE TABLE apartment

( buildingid CHAR(3) NOT NULL,

aptno CHAR(5) NOT NULL,

anoofbedrooms INT NOT NULL,

PRIMARY KEY (buildingid, aptno),

FOREIGN KEY (buildingid) REFERENCES building(buildingid),

);

INSERT INTO manager VALUES ('M12 ','Boris','Grant','20-Jun-1980',60000,null,null);

INSERT INTO manager VALUES ('M23','Austin','Lee','30-Oct-1975',50000,5000,null);

INSERT INTO manager VALUES ('M34','George','Sherman','11-Jan-1976',52000,2000,null);

INSERT INTO manager VALUES ('M45','Mariana','Gonzalez','27-Dec-1980',54000,null,null);

INSERT INTO manager VALUES ('M56','Fiona', 'Keane','4-Oct-1977',57000,2000,null);

INSERT INTO manager VALUES ('M67','Alexander','Sanborn','17-Aug-1953',62000,3000,null);

INSERT INTO building VALUES ('B1','5','M12');

INSERT INTO building VALUES ('B2','6','M23');

INSERT INTO building VALUES ('B3','4','M23');

INSERT INTO building VALUES ('B4','4','M34');

INSERT INTO building VALUES ('B5','3','M45');

INSERT INTO building VALUES ('B6','3','M45');

INSERT INTO building VALUES ('B7','2','M56');

INSERT INTO building VALUES ('B8','4','M67');

INSERT INTO building VALUES ('B9','3','M67');

INSERT INTO apartment VALUES ('B1','11',1);

INSERT INTO apartment VALUES ('B1','21',1);

INSERT INTO apartment VALUES ('B1','31',1);

INSERT INTO apartment VALUES ('B1','41',1);

INSERT INTO apartment VALUES ('B1','51',1);

INSERT INTO apartment VALUES ('B2','11',2);

INSERT INTO apartment VALUES ('B2','21',2);

INSERT INTO apartment VALUES ('B2','31',2);

INSERT INTO apartment VALUES ('B2','41',2);

INSERT INTO apartment VALUES ('B2','51',2);

INSERT INTO apartment VALUES ('B2','61',2);

INSERT INTO apartment VALUES ('B3','11',2);

INSERT INTO apartment VALUES ('B3','21',2);

INSERT INTO apartment VALUES ('B3','31',2);

INSERT INTO apartment VALUES ('B3','41',2);

INSERT INTO apartment VALUES ('B4','11',2);

INSERT INTO apartment VALUES ('B4','21',2);

INSERT INTO apartment VALUES ('B4','31',2);

INSERT INTO apartment VALUES ('B4','41',2);

INSERT INTO apartment VALUES ('B5','11',3);

INSERT INTO apartment VALUES ('B5','21',3);

INSERT INTO apartment VALUES ('B5','31',3);

INSERT INTO apartment VALUES ('B6','11',1);

INSERT INTO apartment VALUES ('B6','12',1);

INSERT INTO apartment VALUES ('B6','21',1);

INSERT INTO apartment VALUES ('B6','22',1);

INSERT INTO apartment VALUES ('B6','31',1);

INSERT INTO apartment VALUES ('B6','32',1);

INSERT INTO apartment VALUES ('B7','11',3);

INSERT INTO apartment VALUES ('B7','12',3);

INSERT INTO apartment VALUES ('B7','13',3);

INSERT INTO apartment VALUES ('B7','21',3);

INSERT INTO apartment VALUES ('B7','22',3);

INSERT INTO apartment VALUES ('B7','23',3);

INSERT INTO apartment VALUES ('B8','11',2);

INSERT INTO apartment VALUES ('B8','12',2);

INSERT INTO apartment VALUES ('B8','21',2);

INSERT INTO apartment VALUES ('B8','22',2);

INSERT INTO apartment VALUES ('B8','31',2);

INSERT INTO apartment VALUES ('B8','32',2);

INSERT INTO apartment VALUES ('B8','41',2);

INSERT INTO apartment VALUES ('B8','42',2);

INSERT INTO apartment VALUES ('B9','11',2);

INSERT INTO apartment VALUES ('B9','21',2);

INSERT INTO apartment VALUES ('B9','31',2);

SELECT * from manager

SELECT * from building

SELECT * from apartment