Wednesday, 16 October 2013

01. The management of AdventureWorks Incorporation wants that whenever the pay rate of an employee is modified, its effect on the monthly salary of that employee should be displayed. John, the Database Developer at AdventureWorks, has been asked to resolve this problem. Help John to find an appropriate solution.

create Trigger uptrigger
on HumanResources.EmployeePayHistory for UPdate
as
Begin
declare @rate as money
Declare @frq as int
select @rate=Rate,
@frq=payfrequency from Inserted
select @rate*@frq*30 as 'Monthly salary'
end

Update HumanResources.EmployeePayHistory
set rate=rate+5
Where EmployeeID=160

02. The management of AdventureWorks Incorporation has decided that no user should be able to change the prices of products. In addition, the management wants that all the attempts to change the price should be saved in a temporary table, Temp. John, the Database Developer, has been asked to make significant changes in the database to implement this policy. What should John do to achieve the same?

create Table Temp
(
ProductID int,
AttChangeCost money,
AttTime datetima
)


create trigger updtrigger on
Production.ProductCostHistory insert of UPdate
as
Begin
declare @Pid as int
Declare @cost as money
select @pid=ProductID,
@cost=standardCost from Deletedinsert into Temp values(@Pid,@cost,getdate())
select 'sorry you can not change the price of a product'
end


update Production.ProductCostHistory
set standardCost=55
where productID=707

03. Create a trigger on the Product table to ensure that if the value for the SafetyStockLevel column becomes less than or equal to the specified ReorderPoint column for a product because of an update operation, a message informing that the reorder level has been attained should be displayed.

create Trigger OrderStock
on Production.Product
for update
as
declare @PID varchar
select @PID=ProductID from inserted
if((select safetyStockLevel from INserted)<=
(select ReorderPoint from inserted))
Begin
print'The stock of'+@PID+
'is unber the reorder point'
end

04. Create a trigger to ensure that the average of the values in the Rate column of the EmployeePayHistory table should not be more than 20 when the value of the rate is increased.

create trigger UpdatetriggerEPayHistory
on HumanResources.EmployeePayHistory
for update
as
if Update (rate)
begin
declare @AvgRate float
select @avgRate=Avg (Rate)
from HumanResources.EmployeePayHistory
if (@avgRate>20)
Begin
Print 'The averge value of rate connot be more than  20'
Rollback Transaction
end
end04.

05. The management of AdventureWorks wants that whenever the account number of a user is modified, the old account number, as well as the new account number, should be instantaneously displayed to the user. How can you accomplish the desired task?

create Trigger Vendoraccountnum
on Purchasing.Vendor
for Update as
Begin
Declare @oldvalue nvarchar(15)
Declare @newvalue nvarchar(15)
select @oldvalue=Accountnumber from deleted
select @Newvalue=Accountnumber from inserted
print 'The old account number is'+@oldvalue
Print 'The New account number is'+Newvalue
end

06. Create a trigger named Emp_Update on the EmployeeDetail table. This trigger should restrict a user from performing any kind of DML operation on the table before 9 A.M and after 5 P.M.

create trigger emp_UPdate
on employeeDetail After insert, UpDATE, Delete
as
Begin
Declare @Time INT
set @Time=DateName (HH,GETDATE())
if @Time not Between 9 and 17
Begin
Rollback
print ('The operation cannot be performed')
end
end

07. Create a trigger named Emp_Update_Trg that will restrict the updation of salary of an employee if the new salary is less than the previous salary in the EmployeeDetails table.

create trigger emp_UPdate_Trg
on employeeDetail After UpDATE
as
Begin
Declare @oldSal money
Declare @NewSal money
select @oldSal=salary from deleted
select @NewSal=Salary from Insertd
if @OldSal>@NewSal
begin
Rollback
print ('New salary cannot be less than the old salary')
end
end

08. Create a trigger named Emp_Delete_Trg on the EmployeeDetails table. This trigger should restrict the deletion of records from the table if the designation of an employee is Manager.

create trigger emp_Delete_Trg
on employeeDetails After Delete
as
Begin
Declare @job varchar(50)
select @job=Designation from deleted
if @job='Manager'
begin
Rollback
print ('Cannot Delete Manager from The Table')
end
end

09. Consider the following statement: CREATE VIEW Emp_Dept AS SELECT E.EmployeeID, E.EmpName, E.Designation, E.Salary, D.DeptNo, D.DeptName FROM EmployeeDetails E INNER JOIN DeptDetails D ON E.DeptNo=D.DeptNo A view named Emp_Dept has been created on the base tables, EmployeeDetails and DeptDetails, by using the preceding statement. John is the Database Developer with AdventureWorks Incorporation. He wants that the users should be able to insert data in the underlying base tables by using the view. He can implement the desired functionality if the insert operation affects a single table. However, the same cannot be done if multiple tables are involved. He wants to be able to insert data in both the tables with the help of a single insert statement. Help John in resolving the problem.

create trigger View_Insert_Trg
on Emp_Dept Instead of Insert
as
Begin
Insert into EmployeeDetails(EmployeeID,EMPName,
Designation, Salary,DeptNo)
select EmployeeID,EmpName,Designation,salary,DeptNo from Inserted
Insert into DeptDetails(DeptNo,DeptName)
select DeptNo,DeptName from Inserted
end

10. Create a trigger named Emp_Nested_Trg on the EmployeeDetails table for an insert operation. This trigger should ensure that a new record being inserted into the EmployeeDetails table has a matching record in the DeptDetails table. Otherwise, the insert operation is rolled back.

create trigger Emp_Nestes_Trg
on EmployeeDetails After Insert
as
Begin
Declare @OptNo int
select @OptNo=OptNo from Inserted
If not Exists(select * from DeptDetails
Where DeptNo=@deptNo)
Print 'The specified DeptNo does not exist.'
Rollback
end

11. John is a Database Developer. He is not able to delete the unwanted records from the base tables by using the view, Emp_Dept. Help John in resolving the problem.

create trigger view_delete_trg
on Emp_Dept Instead of delete
as
Begin
Declare @DeptNo int
declare @Count int
select @DeptNo=DeptNo from deleted
select @Count=Count(*) from EmployeeDetails
Where DeptNo=@DeptNo
delete from EmployeeDetails Where DeptNo=@DeptNo
if @Count=1
Delete from deptdetails where DeptNo=@deptNo
end

Friday, 11 October 2013

01Create a batch that finds the average pay rate of the employees and then lists the details of the employees who have a pay rate less than the average pay rate.

declare @avg_rate int
select @avg_rate=AVG (rate)
from humanResources.EmployeePayHistory
select * from humanResources.EmployeePayHistory
Where rate< @avg_rate
go

02. Create a function that returns the shipment date of a particular order.

create Function sales.calshipdate(@SalesOrderID int)Returns date
as
begin
Declare @shipDate Datetime
select @Shipdate=shipdate
from sales.salesOrderHeader
Where salesOrderID=@salesOrderID
If (@shipdate is Null)
set @shipdate=0
Return @shipdate
end


select salesOrderID,Sales.calshipdate(salesOrderID)
as Shipdate
from Sales.SalesOrderHeader

03. Create a stored procedure that accepts the name of a product and display its ID, number, and availability.

create Proc ProductList @name Varchar(50)
as
begin
Print 'Product details'
select ProductID,ProductNumber,MakeFlag as Availability
from Production.Product
Where Name=@name
end

Execute ProductList 'Keyed Washer'

04. Create a function that returns the credit card number for a particular order.

create Function sales.DisplayCardNumber(@salesOrderID int)
Returns nvarchar(25)
as
begin
Declare @ret nvarchar(25)
select @ret=CardNumber
from sales.salesOrderHeader s join sales.CraditCard c
on s.CreditCardID=c.CreditCardID
Where salesOrderID=@salesOrderID
If (@ret is Null)
set @ret=0
Return @ret
end


select SalesOrderID,'Credit Card Number'=Sales.DisplayCardNumber
(SalesOrderID) from Sales.SalesOrderHeader

05. Create a function that returns a table containing the ID and the name of the customers who are categorized as individual customers (CustomerType = 'I'). The function should take one parameter. The parameter value can be either Shortname or Longname. If the parameter value is Shortname, only the last name of the customer will be retrieved. If the parameter value is Longname, then the full name will be retrieved.

create Function sales.IndividualDetails(@format nvarchar(9))
Returns @tbl_individual table(CustomerID int Primary key, Name Nvarchar(100))
as
begin if (@format='LongName')
insert @tbl_Individual
select Cu.CustomerID,FirstName+''+LastName
from Person.Contact as c join sales.Individual as l
on c.ContactID=l.ContactID join sales.Customer as Cu
on l.CustomerID=cu.CustomerID
Where cu.CustomerType='l'Order by LastName,FirstName
Else
If (@Format='ShortName')
insert @tbl_individual
select cu.customerID,LastName from Person.Contact as c join
sales.Individual as l on c.ContactID=l.ContactID join sales.Customer as Cu
on l.CustomerID=cu.CustomerID
Where cu.CustomerType='l'
Order by lastName
return
end


select * from Sales.IndividualDetails('LongName')
select * from Sales.IndividualDetails('ShortName')

06. Create a user-defined function that accepts the account number of a customer and returns the customer's name from the Depositor table. Further, ensure that after creating the function, user is not able to alter or drop the Depositor table.

create Function fx_disp_Accdet(@accnum int)
Returns Table with Schemabinding
as
Return
(
select Customer_name, Acc_num
from dbo.Depositor
Where Acc_num=@accNum
)


select * from Fx_Disp_AccDet(101)

07. Create a batch to check the availability of the product, Chaining Bolts, in the stock. If the product is available, display a message, 'The Stock is available'. If the product is not available, display a message, 'The Stock is not available'.

Declare @MakeFlag bit
select @MakeFlag=MakeFlag from Production.Product
Where Name ='Chaining Bolts'
If @MakeFlag=1
Print 'The Stock is available'
else
Print 'The stock is not available'

08. Create a stored procedure that returns the standard cost of a given product.

create Procedure PrcGetCostDetail2 @ProductID int,
@standardCost money Output
as
Begin
If Exists (select * from Production.ProductCostHistory
Where ProductID=@ProductID)
Begin
select @standardCost=StandardCost from
Production.ProductCostHistory
Return 0
end
else
return 1
end

09. Create a stored procedure that accepts two numbers, num1 and num2, and displays the result after dividing these two numbers. In addition, num1 should always be greater than num2. If num1 is less than num2, generate a user-defined error message, 'You have entered your numbers in the wrong way'.

create Procedure vx_div @Num1 int, @num2 int
as
Begin
Declare @div int
if @Num1<@num2
Raiserror('You have entered your number in the wrong way',16,1)
else
set @div=@Num1/@num2
print @div
end

Wednesday, 9 October 2013

01The SalesOrderDetail and SalesOrderHeader tables store the details of the sales orders. You have created a backup of the SalesOrderDetail and SalesOrderHeader tables in the SalesOrderDetail_Backup and SalesOrderHeader_Backup tables. To generate a report displaying the sales order ID and the total amount of all the products purchased against an order, you are using the following query: SELECT sd.SalesOrderID, SUM(LineTotal) AS [Total Amount] FROM Sales.SalesOrderDetail_Backup sd JOIN Sales.SalesOrderHeader_Backup sh ON sd.SalesOrderID = sh.SalesOrderID GROUP BY sd.SalesOrderID The table contains a large amount of data. Suggest a solution to optimize the execution of this query.


02. The Store table is often queried. The queries are based on the CustomerID attribute and take a long time to execute. Optimize the execution of the queries. In addition, ensure that the CustomerID attribute does not contain duplicate values.

select * from Sales.Store
create Unique nonclustered index
IDx_CustomerID on sales.Stare(CustomerID)

03. A view has been defined as shown in the following statement: CREATE VIEW vwSalesOrderDetail AS SELECT oh.SalesOrderID, TerritoryID, TotalDue, OrderQty, ProductID FROM Sales.SalesOrderHeader oh JOIN Sales.SalesOrderDetail od ON oh.SalesOrderID = od.SalesOrderID The following UPDATE statement gives an error when you update the OrderQty and TerritoryID attributes: UPDATE vwSalesOrderDetail SET OrderQty = 2, TerritoryID = 4 FROM vwSalesOrderDetail WHERE SalesOrderID = 43659 Identify the problem and provide the solution.

update vwSalesOrderDetail
set OrderQty = 2
from vwSalesOrderDetail
Where SalesOrderID=43659

Update vwSalesOrderDetail
SET TerritoryID=4
from vwSalesOrderDetail
Where SalesOrderID=43659

04. The SalesOrderDetail table is often queried. The queries are based on the SalesOrderDetailID and SalesOrderID attributes. The execution of the queries takes a long time. Optimize the execution of the queries. In addition, check the performance of the query before and after optimizing the execution of the queries.

create nonclustered Index
 Idx_salesOrderID_salesOrderDetailID
 on sales.salesorderdetail(salesorderid,salesorderdetailid)

05. The Store table contains the details of all the stores. The HR Manager of AdventureWorks, Inc. frequently queries the Store table based on the names of the stores. He wants to create the following reports: A report containing the details of all the stores that contain the word 'bike' in their names. A report displaying the names of all the stores containing the phrase 'Bike Store'. Write the query so that the result set is retrieved quickly.

select * from Sales.Store
where contains (Name,'"Bike store"')

06. Display the details of all the credit cards that are of type SuperiorCard. The CreditCard table contains a large amount of data. Therefore, the query takes a long time to retrieve the details of the credit card. You need to optimize the execution of the query so that the result set does not take too much time to be retrieved.

select * from sales.CreditCard
Where Contains(CardType,'SuperiorCard')

07. Display the details of all the currencies that contain the words New and Dollar in their names. These words can be included in any order. In addition, you need to make sure that the query does not take too much time to execute.

select * from Sales.Currency
Where FreeText (Name,'"New" and "Dollar"')

08. The manager of the production department wants to analyze the products that contain the exact word 'road' in their description. Write a query so that the result set does not take a long time to execute.

select * from Production.ProductDescription
Where Contains(Description,'Road')

09. You need to create a report displaying the details of all the products that contain the word 'top' near the word 'line' in their description. Write a query to retrieve the desired output. Write the query such that it does not take a long time to execute.

select Description, ProductDescriptionID
from Production.ProductDescription
Where Contains (Description,'"Top" near "Line"')

10. The Store table is often queried. The queries are based on the Demographics attribute and take a long time to execute. Optimize the execution of these queries.

create XML Index Sdx_Sales_Demographics_path
on Sales.Store(Demographics)
Using XML Index PXML_Store_Demographics
for path

11. The DepartmentHistory table of employees is often queried. The queries are based on the EmployeeID attribute and take a long time to execute. Optimize the execution of these queries.

create Nonclustered index Idx_EmployeeID
on HumanResources.EmployeeDepartmentHistory(EmployeeID)

12. Display the details of all the stores having the word 'bike' in their name. In addition, ensure that the report contains the details of those stores that have the sales person ID as 277. You need to write a query so that the result set does not take a long time to be retrieved.

select * from Sales.Store
Where SalesPersonID=277
and Contains(Name,'Bike')

13. Create a view to retrieve SalesOrderId and Price of the Product along with the corresponding Product Name.

create View vwsales
as
select P.Name, S.SalesOrderID,S.UnitPrice
from Production.Product P inner join
Sales.SalesOrderDetail S
on P.ProductID = ProductID
go

14. Create a view to retrieve the employee IDs along with the corresponding Group Name.

create View Disp_Detail
as
select E.EmployeeID,D.GroupName
from HumanResources.Department d
Inner join
HumanResources.EmployeePayHistory E
on D.DepartmentID=E.EmployeeID

15. Create a view to display SalesOrderId, CustomerId, OrderQty, UnitPrice, SubTotal, and TotalDue of the orders placed.

create view vwsale as
select H.CustomerID, H.SubTotal, H.TotalDue,
D.SalesOrderID,D.OrderQty,D.UnitPrice
from Sales.SalesOrderHeader as H
join sales.salesOrderDetail as D on
H.salesOrderID=d.salesOrderID
select * from Sales.SalesOrderDetail

01. Delete all the records from the ProductBrand table in the NarrowFabrics database. Ensure that you do not delete the table.

Truncate table ProductBrand

02. Write an SQL statement to insert a record into the PictureDetails table, which is created in the PictureLibrary database. The following table shows the data to be inserted into the PictureDetails table.


03. Write an SQL statement to insert a record into the OfficeLocation table, which is created in the PictureLibrary database. The following table shows the data to be inserted into the OfficeLocation table.





Use PictureLibrary
go
Insert into OfficeLocation(Office_ID,
Office_Manager,Office_Location)
Values(1001,'Max',Geography: : Parse('Point(-83.0086 39.95954)'))

04. The production of a bicycle at AdventureWorks involves a number of phases. In each phase, the bicycle is moved to a different work center. The details of all the work centers are stored in the Production.ProductModel table. Bicycles of different types pass through different work centers, depending on the components that need to be fitted. The management wants a list of all the types of bicycles that go through work center 10. How will you generate this list?

With
XMLNamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
ProductModelManulnstructions'as pd)
select ProductModelID from Production.ProductModel
Where Instructions.exist('pd:root/pd:Location[@LocationID=10]')=1

05. The EmployeeDetails table contains the records of the employees. Write a query to delete the records of those employees who are designated as clerk. In addition, ensure that all the deleted rows are displayed after the execution of the query.

Delete EmployeeDetails output
deleted.*Where Designation='Clerk'

06. The users of AdventureWorks need to publish the details of all the customers and their address on the organization's website. To perform this task, you need to retrieve the data in the XML format.

select c.CustomerID,TerritoryID,AccountNumber,CustomerType,
 City, StateprovinceID,PostalCode from Sales.Customer c
Join Sales.CustomerAddress ca on c.CustomerID=ca.CustomerID
join Person.Address a on ca.AddressID=a.AddressID for XML Path('Customer')

07.Delete all the records from the Employee table in the AdventureWorks database where the department ID is 5.

select * from HumanResources.EmployeeDepartmentHistory
delete from HumanResources.EmployeeDepartmentHistory
where DepartmentID=3

08. The management of AdventureWorks requires a list containing the skills of all the candidates who have applied for a vacancy. The details of all the candidates are stored in the XML format in the HumanResources.JobCandidate table. How will you display the list?

select jobCandidateID,Resume.value('declare namesPace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:name/ns:name.First)[1]','nverchar(20)')
as [first name], Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-work/Resume";
(/ns:Resume/ns:name/ns:name.Last)[1]','nvarchar(20)')
as [Last name],Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-work/Resume";
(/ns:Resume/ns:Skills[1]','nvarchar(max)')
as [Skills]from HumanResources.JobCandidate

09. The details of the two employees designated as clerk have been removed from the EmployeeDetails table. Write the SQL statement to reflect these changes made in the EmployeeDetails_Backup table.

Merge EmployeeDetail_Backup
as Target using EmployeeDetail
as Source on (Target.EmployeeID= Source.EmployeeID)
When Matched and
Target.Designation <> Source.Designation
Then Update set Target.Designation= source.Designation
when Not Matched then
Insert Values(Source.EmployeeID,
Source.EmpName,source.designation,
Source.salary,Source.DeptNo)
When Not Matched by Source Then Delete;

10. There is a change in the production process of the bicycle with the product model ID 7. Due to this change, the bicycle will not be going to work center 10. You need to update this change in the database. How will you perform this task?

With
XMLNamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-work/productModelManulnstructions'
as pd)
Update Production.ProductModel SET
Instructions.modify('delete (/pd:root/pd:Location)[1]')
where ProductModelID = 7

11. Write a query to copy the records of sales with unit price greater than $ 2024.994 in a new table named Sales_od.

select * from Sales.SalesOrderDetail
select * into sales_od from Sales.SalesOrderDetail
Where UnitPrice> 2024.994

12. Insert a record with the following address in the Address table of the AdventureWorks database: 2125 Yan Street, Bothell-79, Postal Code-98011

Insert Person.Address (AddressLine1,City,StateProvinceID,
PostalCode) Values('2135 yan street',79,'98011')

13.You need to create a table named Student_Details to store the details of students in the database. The structure is shown in the following table.






create XML Schema Collection StudentInfo as
'<schema xmlns="http://www.w3.org/201/xmlschema">
<Element name ="StudentName"Type="string"/>
<Element name ="Address" type="string"/>
<Element name ="ContactNumber"type="int"/>
</schema>'

Monday, 7 October 2013


internet ASP and .NET


internet asp


The ASP model



The application software resides on the vendor's system and is accessed by users through a web browser using HTML or by special purpose client software provided by the vendor. Custom client software can also interface to these systems through XML APIs. These APIs can also be used where integration with in-house systems is required. ASPs may or may not use multi-tenancy in the deployment of software to clients; some ASPs offer an instance or license to each customer (for example using Virtualization), some deploy in a single instance multi-tenant access mode, now more frequently referred to as "SaaS".
Common features associated with ASPs include:
  • ASP fully owns and operates the software application(s) - ALJO-IN
  • ASP owns, operates and maintains the servers that support the software
  • ASP makes information available to customers via the Internet or a "thin client"
  • ASP bills on a "per-use" basis or on a monthly/annual fee
The advantages to this approach include:
  • Software integration issues are eliminated from the client site
  • Software costs for the application are spread over a number of clients
  • Vendors can build more application experience than the in-house staff
  • Key software systems are kept up to date, available, and managed for performance by experts
  • Improved reliability, availability, scalability and security of internal IT systems
  • A provider's service level agreement guarantees a certain level of service
  • Access to product and technology experts dedicated to available products
  • Reduction of internal IT costs to a predictable monthly fee
  • Redeploying IT staff and tools to focus on strategic technology projects that impact the enterprise's bottom line
Some inherent disadvantages include:
  • The client must generally accept the application as provided since ASPs can only afford a customized solution for the largest clients
  • The client may rely on the provider to provide a critical business function, thus limiting their control of that function and instead relying on the provider
  • Changes in the ASP market may result in changes in the type or level of service available to clients
  • Integration with the client's non-ASP systems may be problematic
Evaluating an Application Service Provider security when moving to an ASP infrastructure can come at a high cost, as such a firm must assess the level of risk associated with the ASP itself. Failure to properly account for such risk can lead to:
  • Loss of control of corporate data
  • Loss of control of corporate image
  • Insufficient ASP security to counter risks
  • Exposure of corporate data to other ASP customers
  • Compromise of corporate data
Some other risks include failure to account for the financial future of the ASP in general, i.e. how stable a company is and if it has the resources to continue business into the foreseeable future. For these reasons Cisco Systems has developed a comprehensive evaluation guideline. This guideline includes evaluating the scope of the ASP's service, the security of the program and the ASP's maturity with regard to security awareness. Finally the guidelines indicate the importance of performing audits on the ASP with respect to:
  • Port/Network service
  • Application vulnerability
  • ASP Personnel
Physical visits to the ASP to assess the formality of the organization will provide invaluable insight into the awareness of the firm.

01. Write a query to retrieve the details of the product locations where cost rate is greater than 12. In addition, the locations need to be grouped into three groups, and then ranked based on the cost rate in descending order.

select LocationID, name,CostRate,
Availability, ntile(3) over(order by CostRate desc)
as
Rank from Production.Location
Where CostRate>=12

02. Display SalesOrderID, OrderQty, and UnitPrice from the SalesOrderDetail table where a similar unit price needs to be marked with an identical value.

select salesOrderID,OrderQty,UnitPrice,Dense_Rank()
Over(Order by UnitPrice ASC)
as
Rank from sales.salesOrderDetail

03. Write a query to return the current year price and the previous year price of a product with the Product ID, 715, over subsequent years.

select ProductID, Year(StarDate)
as
salesYear, ListPrice as CurrentPrice, LAG(ListPrice,1,0)
Over(Order by year(startDate))
as PreviousPrice from Production.ProductListPriceHistory
where ProductID=715

04. The Recipient table and the Country table in the NarrowFabrics database do not have the same data type for the CountryId attribute. The following tables show the sample structure of the two tables.

























Alter Table Country
Alter Column Country char(2)

05. Delete the Recipient table from the NarrowFabrics database.

drop table Employee

06. The following statement was used to remove the Category table: DELETE TABLE Category The preceding statement displays an error and aborts. Identify the error and rectify it.

Use NarrowFabrice
go
Drop table NewProduct
Drop table Category

07. Consider the following table structures.


  Refer the preceding table structures for the following requirements:
1. Create the Category table in the NarrowFabrics database. Enforce the following data integrity
rules while creating the table:
The CategoryId should be the primary key.
The Category attribute should be unique but not the primary key.
The Description attribute can allow storage of NULL values.
2. Create the ProductBrand table in the NarrowFabrics database. Enforce the following data
integrity rules while creating the table:
The BrandId should be the primary key.
The BrandName should be unique but not the primary key.
3. Create the NewProduct table with the following data integrity rules in the NarrowFabrics
database:
The ProductId should be the primary key.
The Qoh of the product should be between 0 and 200.
The Photo and ProductImgPath attributes can allow storage of NULL values.
The ProductName and ProductDescription attributes should not allow NULL values.
The values of the CategoryId attribute should be present in the Category table.
4. Modify the NewProduct table to enforce the following data integrity rule in the NarrowFabrics
database:
The values entered in the BrandId attribute should be present in the ProductBrand table.





















 
 create table category
(
categoryID char(3) constraint pkategoryID primary key,
category char(20) constraint unqCategory unique,
Description varchar(100)Null
)






create table ProductBrand
(
BrandID char (3) constraint pkBrandID primary key,
BrandName char(20) constraint unqBrandName Unique,
)






create table New Product
(
ProductID char(6)Constraint pkProductID primary key,
ProductName varchar(20) not null,
ProductDescription varchar(200) not null,
categoryID char(3)constraint fkCategoryID
foreign key references Category(categoryID),
ProductRate money,
BrandID char(3),
photo image Null,
Qoh smallint constraint chkQoh check (Qoh Between 0 and 200),
productImgPath vatchar(40)Null
)

08. Write the SQL statement to create a table named PictureDetails in the PictureLibrary database. The following table shows the structure of the PictureDetails table.









create table PictureDetail
(
EventPicID Uniqueidentifier Rowguidcol Not Null
Event Name varchar(30)not null,
Pic carbinary(Max) not null,
)

09. Create a table named LeaveDetail in the AdventureWorks database. The following table shows the structure of the LeaveDetail table.









create table LeaveDetail
(
Name varchar(50)not null,
Reason varchar(100)not null,
Number_of_days int constraint chknoday
check(Number_of_days <3)
)

10. Write a query to display the structure of the Department table.

sp_Help 'HumanResources.department'

11. The check constraint is applied on the Number_of_days column of the LeaveDetail table. This check constraint ensures that the value inserted for the Number_of_days column is less than three. However, the leave of four days has been approved for John based on some valid reasons. The HR Manager wants to update the leave details for John but fails to assign the value, 4, in the Number_of_days column. As a database developer, what will you do to perform the preceding task?

Alter table LeaveDetail
Nocheck constraint chknoday

Insert into LeaveDetail valus('Tom','fever',4)

Alter table LeaveDetail
check constraint chknoday

12. Write a query to display the details of all the salespersons by using synonyms.

create Synonym spersonDetail

for Adventureworks.sales.salesperson




selrct * from spersonDetail

13. Write a query to create a rule on the GroupName column of the Department table to accept only three values: Research and Development, Sales and Marketing, and Manufacturing.

create Rule groupType
as
@gType in ('Research and Development',
'sales and Marketing',
'Manufacturing')
go
sp_bindrule 'groupType', 'HumanResorces.Department.
GroupName'

14. Write a statement to create a user-defined data type named quality. This data type should be varchar(100) and should not contain NULL values.

create type quality
from varchar(50) not null;

15. AdventureWorks, Inc. has set up a new store. Insert the data into the database as shown in the following table.





















Insert into sales.Customervalus(7,'s',Default, Default)



Insert into sales.store valus(29484,'car store',285,
'<StoreSurvey XMLnas="htty://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey">'
<AnnualSales>350000</AnnualSales><AnnualRevenue>35000</AnnualRevenue>
<BankName>International Bank</BankName><BusinessType>BM</BusinessType>
<YearOpened>1980</YearOpened><Specialty>Road</Specialty>
<SquareFeet>7500</SquareFeet><Brands>AW</Brands><Internet>T1</Internet>
<NumberEmployees>7</NumberEmployees></storeSurvey>',Default,Default)

16. Insert the data shown in the following table into the ProductBrand table of the NarrowFabrics database.








Inser into ProductBrand valus('B01','Lee')
Inser into ProductBrand valus('B02','Wike')
Inser into ProductBrand valus('B03','Reebok')

17. The address of a vendor, Comfort Road Bicycles, has changed. You need to update the data, as shown in the following table, in the AdventureWorks database.






Update purchasing.VendorAddress
set AddressID=(select Address from Person,Address where AddressLinel='4151 Olivera'
and City='Atlanta')
from Purchasing.VendorAddress va,Purchasing.Vendor v
where va.VendorID=v.VendorID and
v.Name='Confort Road Bicycles'

Sunday, 6 October 2013

Internet Secrets (PI)

Online Money Making and PayPal Payment Technique.

Online Money Making and PayPal Payment Technique.
You can earn the money online. There are several things which you will have to keep in mind. But you need to know one thing quite deeply that how you will get the payment. you might be in Brazil and the company for whom you are working might be in San Francisco. The question arises that how will you be able to receive the payment. One solution is the PayPal and this article is about it.

As far as the PayPal is concerned, it is the electronic business. It allows the fees and the money payments through the internet. It can be considered as the electronic different to the traditional paper methods like the money orders and the checks.

The PayPal can be funded as an electronic debit from the bank account or you might also get it cashed from the credit cards. it is in fact a payment intermediary service which provides the world wide online marketing facility. 
You can request a check from the PayPal or you can establish your own PayPal account. You can also request the PayPal to transfer the money to your bank account.

The PayPal provides the payment options to the vendors who work online, commercial users as well as the auction sites. all of the freelancing sites too depend upon the PayPal. actually the PayPal is the most important and well known agency which provides the online money transaction.

They charge some fee for this. The charge amount depends upon the currency used as well as the payment option. The country of the sender as well as the recipient is also quite important. The recipient account type along with the recipient's accounts type is also quite important. The PayPal is the wholly owned subsidiary of the E-bay. This company has its offices all over the world. Its head office is at San Jose, California. Some of the other substantial offices are at Omaha, Arizona, Nebraska and the Texas. All these offices are located in USA. Some of the other important offices outside the US are at Chennai, Berlin, Dublin and Tel Aviv.

This is all about the Pay Pal. Let me tell you one thing that if you will do the freelancing and try to make the fast cash online then you will definitely has to go for the PayPal. Believe it or not the PayPal is one of the most important medium for online money transaction.

01. Write a query to display EmployeeID of those employees who belong to the department, Tool Design.

select EmployeeID from HumanResources.EmployeeDepartmetHistory
where DepartmantID in (select DepartmentID from HumanResources.Department
where name ='Tool Design')

02. Write a query to display the credit card number of Catherine Abel.

select CaedNumber from sales.creditCard
where creditCardID=(select creditCardID from sales.contactCreditCard where contactID=
(select contactID from Person.Contact where FirstName='Catherine' and LastName='Abe1'))

03. Write a query to display the sales order IDs and the order detail IDs along with the total value of those orders that have a total value greater than the average of the total value for the order ID.

select salesOrderDetailID,salesOrderID,LineTotal from sales.salesOrderdetail s1
where s1.lineTotal > (select AVG(s2.Linetotal) from
sales.salesOrderDetail s2
where s1.SalesOrderID=s2.salesOrderID)

04. Write a query to display the first name and the last name of the customers along with their credit card identification number.

select firstName, Lastname, B.CreditCardID
from Person.contact A Cross apply
(select * from sales.contactcreditcard B Where B.contactID=A,contactID)B

05. Write a query to display CreditCardID, CardNumber, and ExpYear of the credit cards that do not expire in the year 2005 and 2007.

select creditCardID, cardnumber, ExpYear
from sales.creditcard Expcept (select creditcardID,cardNumber,Expyear
from sales.creditcard
where Expyear=2005 or Expyear=2007)

06. Write a query to display the sales person ID and the total number of sales orders processed by each sales person. In addition, display the commision earned by each sales person. Commision is given to each sales person at the rate of $ 10 per order. Exclude the orders where sales person ID is null. The details of the sales person who has got the maximum commision should be displayed first in the result set.

With sales_Cte(salesPersonID, totalsalesOrder)
as
(
select salesPersonID, Count(salesorderID)
from sales.salesOrderHeader
where salesPersonID is not Null
Group by salesPersonID
)
selectr salespersonID,TotalSalesOrder,Commission= TotalSalesOrder*10
from sales_Cte
OrderBy TotalsalesOrder desc

07. Create a table named Recipient in the NarrowFabrics database to store the details of the recipients to whom the orders are dispatched. The following table provides the structure of the Recipient table.
















create table Recipient
(
Ordernumber char(6),
FirstName varchar(20),
lastname varchar(20),
Address varchar(50),
City char(15),
state char(15),
CountryCode char(3),
ZipCode char(10),
phone char(15)
)

08. NarrowFabrics, Inc. is a leading cloth manufacturing organization that manufactures and delivers clothes to the distributors. Being a database developer at NarrowFabrics, you need to create a database that will store the various database objects, such as tables, views, and indexes, used by the organization. Write the SQL statements that you can use to create a database named NarrowFabrics.

create Database NarrowFabrics

09. LiveChain, Inc. is a leading media company that organizes events and provides photography services. After each event, the company stores the pictures taken in the event and delivers the pictures to the client on request. As a database developer, you have been assigned the task to create a database named PictureLibrary that the company can use to store the pictures.

create Database PictureLibrary
on
Primary (Name =PictureLibrary,FileName= 'c:\Data\PictureLibrary.mdf'),
FileGroup fileStreamGroup Contains fileStram(name = videoLibrary_Data,
fileName= 'c:\Data\pictureLibrary_Data')
Log on (Name=Log1,
fileName='C:\data\pictureLibrary.ldf')

10. Create a table named Country in the NarrowFabrics database to store the country details. The following table provides the structure of the Country table.








Create table Conutry
(
CountryID varchar(2),
Country char(25)
)

11. You need to store the details of the offices in the central database, PictureLibrary. Write the SQL statement to create the OfficeLocation table in the PictureLibrary database. The following table shows the structure of the OfficeLocation table.









Use PictureLibrary
Create table OfficeLocation
(
Office_ID int not Null,
Office_Manger varchar(30) not Null,
Office_Lication Geography not Null
)

12. John is working with Client Network Ltd as the Database Administrator. He wants to create a database to store the details of all the employees working in the organization. He needs to ensure that while saving, the data is placed in multiple file groups so that he is able to effectively manage the backup and restore operations. What should John do to perform this task?

Create table Empdetail
on Primary
(name ='EmpDetail_Primary',
fileName='D:\Data\EmpDetail_Prm.mdf',
Size=5MB,
Maxsize= 10MB,
fileGrowth =1MB),
Filegroup Empdetail_Fg

(Name ='EmpDetail_Dat1',
file= 'D:\Data\EmpDetail_1.ndf',
size =5MB, Maxsize=10MB,
FileGrowth=1MB),
(Name = 'EmpDetail_Dat2',
fileName = 'D:\Data\EmpDetail_2.ndf',
Size =5MB,
Maxsize=10MB,
FileGrowth =1MB)
Log on
(Name ='EmpDetail_Log',
FileName='D:\Data\EmpDetaiL_ldf',
Size=5MB, Maxsize=10MB,
FileGrowth=1MB)
Go

13. Write a query to make EmpDetail_FG as the default filegroup for the EmpDetail database.

Alte Database EmpDetail
Modify FileGroup empDetail_FG Default
go

01. Write a query to display the sales person ID, territory ID, and territory name of all the sales persons in the following format.








select 'person Id'=salesPersonID,
'territory ID'=s.TerritoryID,Name
from sales.salesperson s join
sales.salesterritory t
on s.territoryID = t.territoryID

02. Write a query to display the sales person ID of all the sales persons and the name of the territory to which they belong.

select salespersonID, Name
from sales.salesPerson
join sales.salesterritory
on salesperson.territoryID = salesTeriitory.TerritoryID

04. Write a query to display the sales order ID, the product ID, and the order date for all the products in the following format.







select 'Order ID'=  h.salesOrderID,
'Product ID'= d.productID,
'Order Date'= OrderDate
from sales.salesOrderHeader h
join sales.salesOrderDetail d
on h.salesOrderID= d.salesOrderID

05. Write a query to display the order number, territory name, order date, and the quarter in which each order was placed in the following format.






select salesOrderID,'territory name'= name
OrderDate, Quarter = detepart (qq,OrderDate)
from sales.salesOrderHeader s
join sales.salesTerritory t
on s.TerritoryID =t.TeriitoryID

06. Write a query to display the sales order ID, territory name, month, and year of all the sales orders in the following format.






select salesOrderID 'Territory name'= name,
month = datename(mm,OrderDate),
Year = Datename(yy,orderdate)
from sales.salesOrderHeader s
join sales.salesTerritorey t
on s.TerritoryID =t.TerritoryID

07. Write a query to display the total amount due for all the sales orders rounded off to a whole number. In addition, display the sales order ID and the type of credit card through which the payment was made.

select salesOrderID, CardType,
'Total Due'= Round(TotalDue,0)
from sales.salesOrderHeader s
join sales.CreditCard c
on s.CreditCardID= c.CreditCardID

08. Write a query to display all the country region codes along with their corresponding territory IDs.

select c.CountryReglonCode, territoryID
from sales.salesTerritory s
Right Outer join sales.CountryReglonCurrecy c
on s.CountryReglonCode = c.CountryReglonCode

09. Write a query to display the total amount due for all the orders in the AdventureWorks database in the following format:







select 'Order value'=
'The total amount due for the sales Order ID:'+
Convert(varchar(10),salesOrderID)+
'is $'+ Convert(varchar(10),TotalDue)
from sales.salesOrderHeadre

10. Write a query to display the order date along with the sales order ID and the territory name. The order date should be displayed in the dd/mm/yyyy format.

select salesOrderid,Name,
Convert(Char(10),OrderDate,103)
as
'Order Date' from sales.salesOrderHerder s join sales.salesTerritory t
on t.TerritoryID= s.TerritoryID

11. Write a query to display the sales order ID and the territory name of the orders where the month of the order is May and the year is 2004.

select salesOrderID,Name from  sales.salesOrderHeader
s join sales.salesTerritory t on s.TerritoryID =t.TerritoryID
where DateName(mm, OrderDate)= 'May'and DatePart(yy,OrderDate)=2004

12. Write a query to display the contact ID of the customers that have the 'Vista' credit card.

select ContactID,CardType
from sales.ContactCreditCard
join sales.CreditCard no ContactCreditCardID=
CreditCard.CreditcardID
where CardType='Vista'

13. Write a query to display the sales order IDs of the orders received from the Northeast territory.

select salesOrderID from sales.salesOrderHeader
where territoryID=(select TerritoryID
from sales.salesTerritory
Where Name='Nottheast')

14. Create a report containing the sales order ID of those orders where the total value is greater than the average of the total value of all the orders.

select salesOrderID, TotalDue
from sales.salesorderHeader
where totalDue > (select avg(TotalDue)
from sales.salesOrderheader)

15. Write a query to display the sales order IDs of the orders that have been paid through a SuperiorCard.

select salesOrderID from sales.OrderHeader
where CreditCardID in (select CreditCardID
from sales.CreditCard Where CardType='SuperiorCard')

16. Write a query to display the sales order ID, the order detail ID, and the total value of those orders where the total value is greater than the maximum of the total value of order ID 43662.

select salesOrderID, salesOrderDetailID,LineTotal
from sales.salesOrderDetail
where LineTotal >All (select Linetotal from sales.salesOrderDetail
Where salesOrderID=43662)

17. Write a query to display the details of those orders for which no discount was offered

select* from sales.salesOrderDetail
Where specialofferID=(select spcialofferID
from sales.specialoffer where type= 'No Discont')

18. Write a query to display the sales order IDs and the credit card IDs of those cards which are expiring in the year 2007.

select salesOrderIds, CreditcardID
from sales.salesOrderHeader
where CreditCardID in (select CreditcardID
from sales.CreditCard where ExpYear=2007)