Exam 2 Review SQL
- Physical Database Design - ANS-• specification storage era necessities
• specification/ advent of appropriate report structures
• Multivalued attribute: - ANS-characteristic that could have more than one
cost for an entity-example.
• What is a database? - ANS-a shared series of logically associated continual facts, designed
to satisfy the needs of multiple users generally inside an
business enterprise.
24. List, in alphabetical order, the names of all personnel (managers)
who are now managing human beings with talent ID BS12; list
each manager's call handiest once, despite the fact that that supervisor manages
several human beings with this ability. - ANS-pick distinct b.EmployeeName as 'Manager'
from employee_t a, employee_t b, employeeskills_t
wherein a.EmployeeId = b.EmployeeSupervisor and employeeskills_t.Skillid= 'BS12' and
a.Employeename<b.Employeename order by using b.Employeename desc
25. Display the salesclerk call, product finish, and total
amount offered (label as TotSales) for each finish by way of every
salesclerk. - ANS-choose salespersonname, productfinish, Sum(orderedquantity) as 'TotSales'
from salesperson_t, product_t, orderline_t, territory_t, doesbusinessIin_t, customer_t, order_t
where product_t.Productid= orderline_t.Productid and
salesperson_t.Salesterritoryid=territory_t.Territoryid and
territory_t.Territoryid=doesbusinessIin_t.Territoryid and
doesbusinessIin_t.Customerid=customer_t.Customerid and
customer_t.Customerid=order_t.Customerid and order_t.Orderid=orderline_t.Orderid
institution via productfinish, salespersonname
26. Write a query to listing the variety of merchandise produced in
every paintings center (label as TotalProducts). If a piece middle
does not produce any merchandise, display the end result with a
total of 0. - ANS-choose WorkCenterId, Count (ProducedIn_t.ProductID) as 'TotalProducts' from
ProducedIn_t left outer be a part of Product_t on Product_t.ProductID=Product_t.ProductID
organization with the aid of WorkCenterId
27. The manufacturing supervisor at PVFC is concerned approximately aid
for purchased parts in products owned through customers.
A easy analysis he wants executed is to decide for every
, purchaser how many vendors are in the identical nation as that
patron. Develop a listing of all the PVFC clients with the aid of call
with the variety of companies inside the equal kingdom as that purchaser.
(Label this computed end result NumVendors.) - ANS-pick CustomerName, depend (VendorId)
from Vendor_t, Customer_t where Customer_t.CustomerState=Vendor_t.VendorState group
with the aid of CustomerName
28. Display the order IDs for customers who have now not made
any charge, yet, on that order. Use the set command
UNION, INTERSECT, or MINUS for your question. - ANS-pick OrderID from Order_t
besides
pick OrderID from Payment_t
29. Display the names of the states in which customers are living
however for which there is no shop clerk living in that country.
There are several ways to write this query. Try to write down it
without any WHERE clause. Write this query two methods,
the use of the set command UNION, INTERSECT, or MINUS
and now not the use of any of these instructions. Which changed into the maximum
herbal approach for you, and why? - ANS-choose customerstate
from customer_t
Except
pick SalespersonState
from Salesperson_t
choose awesome customerstate
from customer_t, salesperson_t
wherein SalespersonState < > customerstate
30. Write an SQL query to produce a list of all the products (i.E.,
product description) and the wide variety of instances every product
has been ordered. - ANS-choose ProductDescription, sum (OrderedQuantity) from Product_t,
OrderLine_t where Product_t.ProductID=Orderline_t.ProductID institution by way of
ProductDescription
31. Display the customer ID, name, and order ID for all consumer
orders. For the ones customers who do now not have any
orders, include them inside the show once. - ANS-pick Customer_t.CustomerID,
CustomerName, OrderID from Customer_t left outer be a part of Order_t on
Customer_t.CustomerID=Order_t.CustomerID
32. Display the EmployeeID and EmployeeName for those
personnel who do no longer possess the talent Router. Display the
- Physical Database Design - ANS-• specification storage era necessities
• specification/ advent of appropriate report structures
• Multivalued attribute: - ANS-characteristic that could have more than one
cost for an entity-example.
• What is a database? - ANS-a shared series of logically associated continual facts, designed
to satisfy the needs of multiple users generally inside an
business enterprise.
24. List, in alphabetical order, the names of all personnel (managers)
who are now managing human beings with talent ID BS12; list
each manager's call handiest once, despite the fact that that supervisor manages
several human beings with this ability. - ANS-pick distinct b.EmployeeName as 'Manager'
from employee_t a, employee_t b, employeeskills_t
wherein a.EmployeeId = b.EmployeeSupervisor and employeeskills_t.Skillid= 'BS12' and
a.Employeename<b.Employeename order by using b.Employeename desc
25. Display the salesclerk call, product finish, and total
amount offered (label as TotSales) for each finish by way of every
salesclerk. - ANS-choose salespersonname, productfinish, Sum(orderedquantity) as 'TotSales'
from salesperson_t, product_t, orderline_t, territory_t, doesbusinessIin_t, customer_t, order_t
where product_t.Productid= orderline_t.Productid and
salesperson_t.Salesterritoryid=territory_t.Territoryid and
territory_t.Territoryid=doesbusinessIin_t.Territoryid and
doesbusinessIin_t.Customerid=customer_t.Customerid and
customer_t.Customerid=order_t.Customerid and order_t.Orderid=orderline_t.Orderid
institution via productfinish, salespersonname
26. Write a query to listing the variety of merchandise produced in
every paintings center (label as TotalProducts). If a piece middle
does not produce any merchandise, display the end result with a
total of 0. - ANS-choose WorkCenterId, Count (ProducedIn_t.ProductID) as 'TotalProducts' from
ProducedIn_t left outer be a part of Product_t on Product_t.ProductID=Product_t.ProductID
organization with the aid of WorkCenterId
27. The manufacturing supervisor at PVFC is concerned approximately aid
for purchased parts in products owned through customers.
A easy analysis he wants executed is to decide for every
, purchaser how many vendors are in the identical nation as that
patron. Develop a listing of all the PVFC clients with the aid of call
with the variety of companies inside the equal kingdom as that purchaser.
(Label this computed end result NumVendors.) - ANS-pick CustomerName, depend (VendorId)
from Vendor_t, Customer_t where Customer_t.CustomerState=Vendor_t.VendorState group
with the aid of CustomerName
28. Display the order IDs for customers who have now not made
any charge, yet, on that order. Use the set command
UNION, INTERSECT, or MINUS for your question. - ANS-pick OrderID from Order_t
besides
pick OrderID from Payment_t
29. Display the names of the states in which customers are living
however for which there is no shop clerk living in that country.
There are several ways to write this query. Try to write down it
without any WHERE clause. Write this query two methods,
the use of the set command UNION, INTERSECT, or MINUS
and now not the use of any of these instructions. Which changed into the maximum
herbal approach for you, and why? - ANS-choose customerstate
from customer_t
Except
pick SalespersonState
from Salesperson_t
choose awesome customerstate
from customer_t, salesperson_t
wherein SalespersonState < > customerstate
30. Write an SQL query to produce a list of all the products (i.E.,
product description) and the wide variety of instances every product
has been ordered. - ANS-choose ProductDescription, sum (OrderedQuantity) from Product_t,
OrderLine_t where Product_t.ProductID=Orderline_t.ProductID institution by way of
ProductDescription
31. Display the customer ID, name, and order ID for all consumer
orders. For the ones customers who do now not have any
orders, include them inside the show once. - ANS-pick Customer_t.CustomerID,
CustomerName, OrderID from Customer_t left outer be a part of Order_t on
Customer_t.CustomerID=Order_t.CustomerID
32. Display the EmployeeID and EmployeeName for those
personnel who do no longer possess the talent Router. Display the
