Monday, 21 March 2016

Creating Database In SQL


METHOD 1

Create database rose

OR 

Create database roses on primary
(name='roses',
filename='D:\database\roses.mdf',
size=5mb,
maxsize=unlimited,
filegrowth=1mb)
log on
(name='roses_log',
filename='D:\database\roses.ldf',
size=5mb,
maxsize=unlimited,
filegrowth=10%)

METHOD 2

Create database srose on primary
(name='srose',filename='D:\database\srose.mdf',
size=5mb,maxsize=unlimited,filegrowth=1mb)

METHOD 3

Create database rose2 on primary
(name='rose2',
filename='D:\database\rose2.mdf',-----(main extention)
size=5mb,
maxsize=unlimited,
filegrowth=1mb),
filegroup myfilegrp1
(name='rosefilegrp1',
filename='D:\database\rosefilegrp1.ndf',----(for saving the extention)
size=5mb,
maxsize=unlimited,
filegrowth=1mb),
filegroup myfilegrp2
(name='rosefilegrp2',
filename='D:\database\rosefilegrp2.ndf',
size=5mb,
maxsize=unlimited,
filegrowth=1mb)
log on
(name='rose2_log',
filename='D:\database\rose2.ldf',
size=5mb,
maxsize=unlimited,
filegrowth=10%)

ENABLING FILESTREAM

exec sp_configure filestream_access_level,3
reconfigure
create database teksoft
on
primary(name=teksoft,
filename='D:\data\teksoft.mdf'),
filegroup filestreamgroup contains filestream(name=teksoft_data,
filename='D:\data\teksoft_data')
log on(name=log1,filename='D:\data\teksoft\ldf')
go

RENAME A USER-DEFINED DATABASE

sp_renamedb db1, db2
Here,
db1 is the current name of the database
db2 is the new name of the database

DELETE A USER-DEFINED DATABASE

Drop database Employee
Here,
Employee is the name of database



Tuesday, 1 March 2016

NIIT SQL LAB@HOME 4


1.Display the Employee ID and the Hire Date of the Employees from the Employee table.The month and the year need to be displayed.

ANSWER: select Employee ID,                                                                            MONTH=DATENAME(mm,HireDate),YEAR =                              DATENAME(yy,Hiredate)
                   from HumanResources.Employee

2.Consider the following SQL query:
   Select ProductID, LineTotal as 'Total' from                  Sales.SalesOrderDetail 
  Group by Cube(Line Total)

ANSWER: The code will give following error message:
                  Msg 8120 ,Level 16 State 1 Line 1                                                     Column 'Sales.SalesOrderDetail.ProductID'
                  is invalid in the select list because it is not contained in
                  either an aggregate function or GROUP BY clause

                The correct code is: 

               select ProductID,SUM(LineTotal) as 'Total'
               from Sales.SalesOrderDetail
               Group by CUBE(ProductID)

3.Write a query to display the full name of a person in a column named Person Name.

ANSWER: select CONCAT(FirstName,MiddleName,LastName)as                    PersonName
                  from Person.Person

4.Display the details of all the orders

ANSWER: select 'Order No.'=SalesOrderID,TotalDue,'Day of                            Order'= DATEPART(dd,OrderDate),'Week                                        day'=DATENAME(dw,OrderDate)
                   from Sales.SalesOrderHeader

5.Display a report containing the sales order ID and the average value of the total amount greater than $5000.

ANSWER: select 'Average Value'=AVG(LineTotal),SalesOrderID
                   from Sales.SalesOrderDetail
                   group by SalesOrderID Having AVG(LineTotal)>5000

6.Display the customer ID, name,and sales person ID for all stores.According to the requirement, only first 15 letters of the customer name should be displayed.

ANSWER: selectCustomerID,Name=LEFT(Name,15),
                   SalesPersonID
                  from Sales.Store

7.Display the total value of all the orders put together.

ANSWER: select 'Total Value of all order'=SUM(TotalDue)
                   from Sales.SalesOrderHeader

8.What will be the output of the following code written to display the total order value for each order?
             Select SalesOrderID,ProductID,sum(LineTotal)
             From Sales.SalesOrderDetail
             Group by SalesOrderID

ANSWER: The code will give following error message:
                    Msg 8120 ,Level 16 State 1 Line 1                                                     Column 'Sales.SalesOrderDetail.ProductID'
                    is invalid in the select list because it is not contained in
                    either an aggregate function or GROUP BY clause

            The correct code is: 

               select SalesOrderID,ProductID,SUM(LineTotal) as 'Total'
               from Sales.SalesOrderDetail
               Group by SalesOrderID,ProductID

9.Display the Order ID of the top five orders based on the total amount due in the year 2001.

ANSWER: select top 5 SalesOrderID,OrderDate
                   from Sales.SalesOrderHeader
                   where DATEPART(yyyy,OrderDate)=2001
                   Order by TotalDue desc

10.Display the maximum,minimum,and the average rate of sales orders.

ANSWER: select 'Maximum'=MAX(TotalDue),                                                 'Minimum'=MIN(TotalDue),'Average'=AVG(TotalDue)
                   from Sales.SalesOrderHeader

11.Display the total amount collected from the orders for each order date.

ANSWER: select OrderDate,SUM(TotalDue)
                   from Sales.SalesOrderHeader
                   group by OrderDate

12.Display the sales order ID and the maximum and minimum values of the order based on the sales order ID. In addition, ensure that the order amount is greater than $5000.

ANSWER: select SalesOrderID, MIN(LineTotal)as Minimum,                            MAX(LineTotal)as Maximum
                   from Sales.SalesOrderDetail
                   where LineTotal>5000
                   group by SalesOrderID
                   
13.Consider the following SQL query containing the ROLLUP operator.
        select ProductID, LineTotal as 'Total'
        from Sales.SalesOrderDetail
        Group by ROLLUP(ProductID)    

ANSWER: The code will give following error message:
                   Msg 8120 ,Level 16 State 1 Line 1 Column                                        'Sales.SalesOrderDetail.ProductID'
                   is invalid in the select list because it is not contained in
                   either an aggregate function or GROUP BY clause

                 The correct code is: 

                 select ProductID,SUM(LineTotal) as 'Total'
                 from Sales.SalesOrderDetail
                 Group by ROLLUP(ProductID)


14.Display a report containing the product ID and the total cost of products for the product ID whose total cost is more than $10,000.

ANSWER: select ProductID,SUM(LineTotal) as Total
                   from Sales.SalesOrderDetail
                   group by ProductID Having SUM(LineTotal)>10000

15.Write a query to retrieve the list price of the products where the products price is between $360.00 and $499.00 and display the price in the following format:

The list price of "Product Name" is "Price" 

ANSWER: select 'list price of'+ Name + 'is'  + CAST(ListPrice as                      varchar(12))as PriceList
                   from Production.Product
                  where ListPrice BETWEEN 360.00 AND 490.00

16.Write a query to display a report that contains the Product ID and its availability status.

ANSWER: select ProductID,IIF(MakeFlag=1,'Available',
                   'NotAvailable')AS AvaibilityOfProduct
                   from Production.Product

17.Display the sum of sales amount earned by each sales person and the sum of sales amount earned by the all the sales persons.

ANSWER: select SalespersonID,SUM(SalesQuota)AS SalesQuota
                   from Sales.SalesPersonQuotaHistory
                   group by Rollup (SalesPersonID)

18.Display the total unit price and the total amount collected after selling the products, 774 and 777.In addition, calculate the total amount collected from these two products.

ANSWER: select ProductID,SUM(UnitPrice)as TotalUnitPrice,                          SUM(LineTotal)as TotalAmt
                   from Sales.SalesOrderDetail
                   where ProductID IN (777,774)
                   group by cube (ProductID)







NIIT  SQL LAB@HOME 3

1.Display all the territories whose name begin with 'N'.

ANSWER: Select *
                   From Sales.SalesTerritory
                   Where Name like 'N%'

2.Display the detail of those stores that have Bike in their name.

ANSWER: Select *
                   From Sales.Store
                   Where Name like '%Bike%'

3.Display the Salesperson Id, TerritoryID and Sales Quota for those sales persons who have been assigned a sales quota.

ANSWER: Select 'SalesPersonId' = SalesPersonID, 'TerritoryId' = Territory ID, 'Sales Quota' = Sales Quota
From Sales.SalesPerson
Where Sales Quota IS NOT NULL

4.Display the top three sales person based on bonus.

ANSWER: Select top 3*
                   From Sales.SalesPerson
                   Order by Bonus desc

5.Display a report that contains the employee ID, login ID, and the title of employees.The report should display the records for 10 employees after excluding the records of the first five employees.

ANSWER: Select Employee ID, Login ID,Title
                   From HumanResources.Employee
                   Order by Emp Id
                   OFFSET 5 Rows
                   Fetch next 10 Rows only 

6.Display the different types of the credit cards used for purchasing products.

ANSWER: Select distinct Cardtype
                   From Sales.CreditCard

7.Each time the Salary slip for an employee is generated, the referral bonus(if present) has to be calculated and printed in the salary slip.

ANSWER: 




8.New Heights is a training institute that provides courses on various nontechnical subjects, such as personality improvement and foreign languages. Xuan, the Database Designer, has made the following relations to represent the data about students, and modules covered in the batches:


  • STUD-ID: Student's id (unique)
  • NAME:Name of students
  • BATCH-NO: Batch number(one student can belong to only one batch)
  • SLOT: Time and day when the batch of the students attends the class
  • MODULE: Module or subject(one batch will do several modules)
  • MARKS: Marks obtained in a module test

Xuan now needs to simplify the above relations by normalizing them.

ANSWER:



9.
         

ANSWER:





10. Consider the following Student Table.




 ANSWER: 








11.Consider the following Purchase Details table.




ANSWER: 





Monday, 29 February 2016

NIIT SQL LAB@HOME 2


1. Display the details of all customers

ANSWER: Select *
                   From Sales.Customer


2.Display the ID,type,number,and expiry year of the credit cards

ANSWER:  Select 'CreditCardID=CreditCardId, 'CreditCardType' =                 Cardtype,'Credit Card Number' = CardNumber
                    'Expiry Year'= ExpYear
                    From Sales.CreditCard


3.Display the Customer ID and the account number of all the customer who live in the TerritoryID 4

ANSWER: Select Customer Id,account number
                   From Sales.Customer
                   Where TerritoryID = 4


4.Display all the details of the sales order that have a cost exceeding $2000.

ANSWER: Select *
                   From Sales.SalesOrderDetail
                   Where LineTotal > 2000.00


5.Display the sales order details of the product named 'Cable Lock' for which the product Id is 843.

ANSWER: Select *
                   From Sales.SalesOrderDetail
                   Where Product Id =843


6.Display the list of all the orders placed on June 06,2004.

ANSWER: Select *
                   From Sales.SalesOrderHeader
                   Where Order Date = '06-06-2004'

                   
7.Display the Name,Country region code,and sales year to date for territory with Territory ID as 1.

ANSWER: Select Country Region Code,Sales YTD
                   From Sales.SalesTerritory 
                   Where Territory ID = 1


8.Display lists of all the sales orders in the price range of $ 2000 to $ 2100.

ANSWER: Select *
                   From Sales.SalesOrderDetail
                   Where Line Total between 2000 and 2100


9.Display the Sales territory details of Canada,France and Germany.

ANSWER: Select *
                   From Sales.SalesTerritory 
                   Where name IN ('Canada','France','Germany')


10.Display the details of the orders that have tax amount of more than $10000.

ANSWER:  Select *
                   From Sales.SalesOrderHeader
                   Where Tax amt >=10000

11.Generate a report that contains the IDs of sales persons living in the territory ID as 2 or 4.

ANSWER: Select Sales Person ID ,Territory ID
                   From Sales.SalesTerritoryHistory
                   Where Territory ID =2 OR Territory ID = 4

12.Display the detail of the vista credit cards that are expiring in the year 2006.

ANSWER: Select *
                   From Sales.CreditCard
                   Where Card type = 'Vista' AND ExpYear = '2006' 

13.Display the Orders placed on July 01,2001 that have a total cost of more than $ 10000.

ANSWER: Select 'Order Number' = Sales Order ID ,'Order Date' =                    Order Date,Status,'Total Cost' = Total Due
                   From Sales.SalesOrderHeader
                   Where Order Date = '07-01-2001' 
                   AND Total Due >10000

14.Display the details of the all orders that were shipped after
  July 12,2004.

ANSWER: Select *
                   From Sales.SalesOrderHeader
                   Where Ship Date >'2004-07-12'

15.Display a report of all the Orders.

ANSWER: Select 'Order ID' = Sales Order ID, 'Order Quantity' =                      Order Qty,'Unit Price' = Unit Price, Total Cost = Order                    Qty * Unit Price 
                   From Sales.SalesOrderDetail

16.Display the details of the orders that have been placed by customers online.

ANSWER: Select *
                   From Sales.SalesOrderHeader
                   Where OnlineOrderFlag = 1

17.Display the Order ID and the Total amount due of all the sales orders.Ensure that the order with the highest price is at the top of the list.

ANSWER: Select 'Order ID' = Sales Order ID, 'Total Due' = Total                      Due
                   From Sales.SalesOrderHeader
                   Order by Total Due desc

18.Display the Order ID and Tax amount for the sales orders that are less than $2000. The data should be displayed in ascending order.

ANSWER: Select Sales Order ID, Tax Amt
                   From Sales.SalesOrderHeader
                   Where Total Due <2000
                   Order by Total Due asc

19.Display the Order number and the total value of the order in ascending order of the total value.

ANSWER: Select Sales Order ID, Total Due
                   From Sales.SalesOrderHeader
                   Order by Total Due asc

20.Display the details of all the currencies that have the word 'Dollar' in their name.

ANSWER: Select *
                   From Sales.Currency
                   Where Name like '%Dollar%'
                   
                                                NIIT RDBMS LAB@HOME 1


1.Shopping Spree is a leading departmental store in Shanghai.The store has a number of regular customers who purchase bulk items.The store also conducts regular feedback sessions to analyze customer satisfaction levels.Chen, the customer analyst of shopping spree, has to make ER diagram to represent the preceding situation, and then to map the ER diagram to the corresponding table.Help Chen to do the same.

ANSWER:





2.An organization has two types of employees,salaried and wage earning.Both the types of employees has some common properties,such as employee code,employee name, and employee address.However,the salaried employees have other additional properties:basic,allowance,and House Rent Allowance(HRA). The wage earning employees have distinct properties that are daily wage and overtime.

ANSWER:




3.


ANSWER:
Primary Key: DoctorID
Candidate Key: DoctorID,ShiftID
Alternate Key: Qualification

DoctorID and ShiftID are individually unique in every row.Therefore,the columns, DoctorID and ShiftID,are Candidate keys for primary key.However,the ShiftID column may contain duplicate values as two doctors may be on the same Shift.Therefore,DoctorID should be chosen as the primary key and ShiftID as the Alternate key.



4.You have been hired by a regional hospital to create a database that will be used to store the information of the patients.Implementing the database will make the date easily accessible.Identify the entities, their attributes,and the type of relationship between the entities.

ANSWER:
Entities: Patient,Doctor
Attributes of Patient: Patient_Name,Date_admitted,Patient_ID
Attributes of Doctor:
Doc_ID,Specialization,Doc_Name
Relationship :
Many to Many



5.Tom is working in an organization as a database administrator.Based on the hierarchy, the organization has multiple departments and each department has multiple employees.Identify entities,their attributes, and type of relationship between the entities.

ANSWER:
Entities: Employee,Department
Attributes of Employee:
EmpID,EmpName,DepID
Attributes of Department:
DepID,DepName,DepHead



6. Lee Wong is the newly appointed database administrator at Standard bank.The management of this bank wants to computerize the process of banking.According to the bank's policy,one customer can have many accounts but one account cannot be shared by many customers.Identify entities,their attributes,and type of relationship between the entities.

ANSWER:

Entities: Customer,Account
Attributes of Customer:
Customer Name,SSN Number ,Customer Address
Attributes of Account:
Account Number,Balance
Relationship: One to Many



7.An author can write multiple books and a book can be written by more than one author.Identify the relationship between the entity.Represent it with an ER Diagram.

ANSWER:


Relationship: Many to Many