Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ISM 6215 - Business Database Systems I

Spring 2019

 

HW 2

 

Question 1 –

 

The diagram below shows the ERD of a reality company database. Write the SQL queries that accomplish the following

 

 

 

 

1. Display the SMemberID and SMemberName for all staff members

2. Display the CCID, CCName, and CCIndustry for all corporate clients

3. Display the BuildingID, BNoOfFloors, and the manager’s MFName and MLName for all buildings

4. Display the MFName, MLName, MSalary, MBDate, and number of buildings that the manager manages for all managers with a salary less than $55,000

5. Display the BuildingID and AptNo, for all apartments leased by the corporate client WindyCT

6. Display the InsID and InsName for all inspectors whose next inspection is scheduled after 1-JAN-2014. Do not display the same information more than once.

7. Display the SMemberID and SMemberName of staff members cleaning apartments rented by corporate clients whose corporate location is Chicago. Do not display the same information more than once.

8. Display the CCName of the client and the CCName of the client who referred it, for every client referred by a client in the music industry

9. Display the BuildingID, AptNo, and ANoOfBedrooms for all apartments that are not leased.

 

 

 

Answer 1 –

 

- Display the SMemberID and SMemberName for all staff members.

 

SELECT *

FROM staffmember;

 

 

- Display the CCID, CCName, and CCIndustry for all corporate clients.

 

SELECT ccid, ccname, ccindustry

FROM corpclient;

 

 

- Display the BuildingID, BNoOfFloors, and the manager’s MFName and MLName for all buildings.

 

SELECT b.buildingid, b.bnooffloors, m.mfname, m.mlname

FROM building b, manager m

WHERE b.bmanagerid = m.managerid;

 

 

- Display the MFName, MLName, MSalary, MBDate, and number of buildings that the manager manages for all managers with a salary less than $55,000.

 

SELECT mfname, mlname, msalary, mbdate, count(*)

FROM building b, manager m

WHERE b.bmanagerid = m.managerid AND

m.msalary < 55000

GROUP BY m.managerid;

 

 

- Display the BuildingID and AptNo, for all apartments leased by the corporate client WindyCT.

 

SELECT a.buildingid, aptno

FROM apartment a, corpclient c

WHERE a.ccid = c.ccid AND

c.ccname = 'WindyCT';

 

 

- Display the InsID and InsName for all inspectors whose next inspection is scheduled after 1-JAN-2014. Do not display the same information more than once.

 

SELECT DISTINCT ir.insid, ir.insname

FROM inspector ir, inspecting ig

WHERE ir.insid = ig.insid AND

ig.datenext > '1-JAN-2014’;

 

 

- Display the SMemberID and SMemberName of staff members cleaning apartments rented by corporate clients whose corporate location is Chicago. Do not display the same information more than once.

 

SELECT DISTINCT s.smemberid, s.smembername

FROM staffmember s, cleaning cl, apartment a, corpclient cc

WHERE s.smemberid = cl.smemberid AND

a.aptno = cl.aptno AND

a.buildingid = cl.buildingid AND

a.ccid = cc.ccid AND

cc.cclocation = 'Chicago'

 

- Display the CCName of the client and the CCName of the client who referred it, for every client referred by a client in the music industry.

 

SELECT c1.ccname client, c2.ccname referredby

FROM corpclient c1, corpclient c2

WHERE c2.ccid = c1.ccidreferredby AND

c2.ccindustry = 'Music'

 

 

- Display the BuildingID, AptNo, and ANoOfBedrooms for all apartments that are not leased.

 

SELECT buildingid, aptno, anoofbedrooms

FROM apartment

WHERE ccid IS NULL

 

 

 

Questions 2(a) – A fixed disk has 80 cylinders.  The tracks in each cylinder are numbered 0-11.  The upper surface of the top-most platter and the lower surface of the bottom-most platter are not used for recording data.  How many of each of the following are there in the disk:

1. Recording surfaces?

2. Platters?

3. Tracks per recording surface?

 

Question 2(b) – Describe (with definition) different types of times required in processing a book search query on Alibaba.com from your computer in Warrington College of business in Gainesville Florida. Assume that the business logic of search query is stored in the database server and data on all books are stored on hard disk in the data center of Alibaba.com in Beijing China.

 

Answer 2(a) –

a. The tracks in each cylinder are numbered 0-11. This means there are 12 recording surfaces on which data can be stored.

b. For 12 recording surfaces, with the upper surface of top-most platter and lower surface of bottom-most platter not being the recording surfaces, the disc should have 7 platters.

c. Number of tracks per recording surface must be same as the number of cylinders – so there are 80 tracks per recording surface.

Answer 2(b) –

 

The following are the different types of time required for processing of book search query.

- Query transfer time over the internet – time required to transfer book search query over the Internet from Gainesville Florida to the database server in Beijing China

- Seek time - time required to move the access arm mechanism to the correct cylinder where required book data is stored from whatever cylinder it’s currently positioned.

- Head switching time – time required in selecting the read/write head to access the required track of the cylinder.

- Rotational delay time – time required for the desired book data on the track to arrive under the read/write head as the disk is spinning.

- Transfer time - time required to move the book data from the disk to primary memory of the database server once the previous 3 steps have been completed.

- Query result transfer time over the internet - time required to transfer search query result over the internet from the database server in Beijing china to your computer in Gainesville Florida.

 

SQL COMMAND QUESTIONS’ ANSWERS

 

 

Add a new column CustomerType with datatype VARCHAR and size 10 and has a default value “Commercial’ in Cutomer_T

 

ALTER TABLE Customer_T

ADD COLUMN (CustomerType  VARCHAR(10) Default 'Commercial');

 

 

 

Now drop the constraint of default value for CustomerType.

 

ALTER TABLE Customer_T

Alter column CustomerType Drop default;

Now set it back to default value

 ALTER TABLE Customer_T

Alter column CustomerType set default ‘Commercial;

Inserting a full row into Customer_T

INSERT INTO Customer_T

Values (16,'American classics', '4000 Riverdrive', 'Milpitas', 'CA', 56005 );

 

Note that it will take a default value of Commercial in the CustomerType column when it is not passed on.

Drop column CustomerType from Customer_T.

 

ALTER TABLE Customer_T

DROP COLUMN CustomerType;

 

 

 

Add Customer’s e-mail address. Goal: Send coupons.

ALTER TABLE Customer_T

ADD COLUMN (Email VARCHAR(25));

 

 

Add Field Y/N field. Goal: Control whether or not a product is being sold or not.

 

Alter Table Product_T Add Column (Sold Char(1) Check(Sold IN ('Y','N')));

 

 

 

Display average ProductStandardPrice of each ProductLine  

Select ProductLineID, Avg(ProductStandardPrice) as AvgPrice

From Product_T

Group By (ProductLineID);

 

Display CustomerState and Number of customers from that state for states which have more than one customers

 

Select CustomerState, Count(CustomerState) as NCustomers

From Customer_T

Group By (CustomerState)

Having count(CustomerState)>1;

 

 

 

Display CustomerState and Number of customers from that state for states which have more than average number of customers from a state.

 

Select CustomerState, Count(CustomerState) as HighNCustomers

From Customer_T

Group By (CustomerState)

Having count(CustomerState)> (Select Avg(NCustomers)

From (Select CustomerState, Count(CustomerState) as NCustomers

                                       From Customer_T

Group By (CustomerState)) as PA);

 

 

 

 

 

Keep track of our silent clients, so that marketing department could figure out different strategies to boost sales:

 

SELECT CustomerID, CustomerName

FROM Customer_t

WHERE CustomerID NOT IN ( SELECT Distinct CustomerID

FROM Order_t);

 

 

 

Keep track of our loyal customers (according to purchasing frequency) so that marketing department could figure out different strategies to boost sales:

 

 

 

Keep track of our major customers (according to total amount), so that marketing department could figure out different strategies to boost sales:

 

 

 

 

 

 

 

 

 

Display the order number and the total value of products purchased in all orders

 

Select OrderID, Sum(orderedQuantity*ProductStandardPrice) as TotalValue

From OrderLIne_T, Product_T

Where OrderLine_T.ProductID = Product_T.ProductID

Group By OrderID;

 

 

 

  

Display the order number and the total value of products purchased for the largest value order

 

Select OrderID, Sum(orderedQuantity*ProductStandardPrice) as TotalValue

From OrderLIne_T, Product_T

Where OrderLine_T.ProductID = Product_T.ProductID

Group By OrderID

Having Sum(orderedQuantity*ProductStandardPrice)=

(Select Max(TotalValue) From (Select OrderID, Sum(orderedQuantity*ProductStandardPrice) as TotalValue

From OrderLIne_T, Product_T

Where OrderLine_T.ProductID = Product_T.ProductID

Group By OrderID) as PA );

 

 

 

 

 

Display the name and address of the customer who place more than one order with the number of orders placed as Norders.

 

Select CustomerName, CustomerAddress, Count(*) as Norders

From Customer_T, Order_T

Where Customer_T.CustomerID = Order_T.CustomerID

Group By (Customer_T.CustomerID)

Having Count(*)>1;

 

 

 

 

Display the SalespersonID and the number of distinct customers’ orders they have handled

 

Select salespersonId, Count(Distinct CustomerID) As Nuniquecustomer

From Order_T

Group By SalespersonID;

 

 

 

 

Display total sales of products by productline

 

Select ProductlineID, sum(OrderedQuantity*ProductStandardPrice) AS TotalSales

From Product_T, Orderline_T

Where Product_T.ProductID = OrderLine_T.ProductID

Group By (ProductLineID);

 

 

 

Display total sales of those products by productline which have been ordered in atleast 4 distinct orders.

 

Select ProductlineID, Sum(OrderedQuantity*ProductStandardPrice) AS TotalSales

From Product_T, Orderline_T

Where Product_T.ProductID = OrderLine_T.ProductID

Group By (ProductLineID)

Having Count(Distinct OrderID)>4;

 

 

 

 

Display total sales and salespersonID in descending order (based on sales) for those salespersons sales value greater than 4000$

 

Select SalespersonID, Sum(OrderedQuantity*ProductStandardPrice) AS TotalSales

From Order_T, Product_T, Orderline_T

Where Order_T.OrderID = OrderLine_T.OrderID

And OrderLine_T.ProductID = Product_T.ProductID

Group By SalespersonID

Having TotalSales >4000

Order By TotalSales desc ;

 

 

 

 

Display total sales per productlinID for each salesperson

 

Select SalespersonID, Product_T.ProductLineID, Sum(OrderedQuantity*ProductStandardPrice) AS TotalSalesPrline

From Order_T, Product_T, Orderline_T

Where Order_T.OrderID = OrderLine_T.OrderID

And OrderLine_T.ProductID = Product_T.ProductID

Group By SalespersonID, Product_T.ProductLineID;

 

 

 

 

 

 

Display salespersonID of the salespersons who get more than average number of orders by salespersons

 

Select SalespersonID, Count(*) As Norder

From Order_T

Group By (SalespersonID)

Having Count(*)>(Select Avg(norder)

From (Select SalespersonID, Count(*) As norder From Order_T

Group By (SalespersonID)) AS AvgTable );

 

 

 

 

 

Display ProductLineID and the number of products ordered in it

 

Select ProductLineID, sum(orderedquantity)

From Product_T, Orderline_T

Where OrderLine_T.ProductID = Product_T.ProductID

Group By (ProductlineID);

 

 

·