Relational Database Design and SQL Using Microsoft SQL Server, write the SQL sta

Question

Relational Database Design and SQL

Using Microsoft SQL Server, write the SQL statements for each question necessary to generate the required result set.

Answer the following three questions

Entity Relationship Diagram Orders PK OrderlD PK,FK2 Productio CompanyName ContactTitle City FK1 CustomerlD FK2 EmployeelD UnitPrice Quantity Discount -has--H ShipAddress ShipPostalCode Country Phone has FK3 ShipperiD RequiredDate Freight FK1 SuppierlD PK EmployeelD LastName FirstName MiddleName EnglishName UnitPrice City UnitsOnOrder ReorderLevel Discontinued PK ShippertlD Phone BirthDate CompanyName has PK SupplierlD Name City

Explanation / Answer

Hi,

Please find below the answer-

Ans 1- create view order_shipped_vw as select orderid, format(orderdate,'YYYY-MM-DD') AS OrderDate, format(shippeddate,'YYYY-MM-DD') as ShippedDate,Shipname,concat(firstname,' ',lastname) as EmployeeName,shippername,datediff(day,shippeddate,orderdate) as days from orders o

join employees e

on o.employeeid=e.employeeid

join shippers s

on o.shipperid=s.shipperid

and datepart(YYYY,orderdate)>=1993

and days>10;

Ans 2- DELETE from EMPLOYEES where phone is null;

Ans 3- select orderid, companyname,contactname,phone, format(orderdate,'YYYY.MM.DD') as orderdate , quantity from order o join customer c on o.customerid=c.customerid

join orderdetails od

on o.orderid=od.orderid

and od.productid not in( select productid from products where discontinued=1)

and orderdate>='1994-03-01'

and shippeddate is null;

Thanks and Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---