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. (Use the AdventureWorks database)

select productID,sum(UnitPrice) as TotalUnitPrice,sum (LineTotal) as TotalAmount from sales.salesOrderDetail
where productID in (777,774) group by cube(ProductID)

17. Write a query to display a report that contains the Product ID and its availability status in the following format.

The values in the Availability of Product column can be Available or Not Available. (Use the
AdventureWorks database)



select productID,iif(makeflag=1,'Available','NotAvailable') 
as 
AvailabilityOfProduct from Production.product

16. Display the sum of sales amount earned by each sales person and the sum of sales amount earned by the all the salespersons. (Use the AdventureWorks database)

select salesPersonID,sum(salesQuota)
as salesQuota from sales.salespersonQuotaHistory
group by rollup(salesPersonID)

15. Write a query to retrieve the list price of the products where the product price is between $ 360.00 and $ 499.00 and display the price in the following format: The list price of "Product Name" is "Price". (Use the AdventureWorks database)

select 'The list price of'+Name+'is'+ cast(ListPrice as varchar(20)) as ListPrice from Production.Product
where ListPrice BETWEEN 360.00 and 499.00;

14. 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 $ 5,000. (Use the AdventureWorks database)

select salesorderID,MIN(LineTotal) as 'Minimum',MAX(LineTotal) as Maxinum
from sales.salesorderDeteil
where LineTotal>5000
group by salesorderID

13. Display the total amount collected from the orders for each order date. (Use the AdventureWorks database)

select * from sales.salesOrderDitail
select sum (amount) as 'total' from sales.salesorderdilail
group by date
having date='2001-07-12'

12. Consider the following SQL query containing the ROLLUP operator: SELECT ProductID, LineTotal AS 'Total' FROM Sales.SalesOrderDetail GROUP BY ROLLUP (ProductID) The preceding query generates errors during execution. Identify the possible causes of such errors and rectify? (Use the AdventureWorks database)

select ProductID,sum(LineTotal) as 'Total' from sales.salesorderDetail
Group by productID(productID)

11. 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. (Use the AdventureWorks database)

select ProductID,sum(LineTotal) as Total from sales.salesorderDetail
Group by productID
Having sum(LineTotal)>10000

10. What will be the output of the following code written to display the total order value for each order? (Use the AdventureWorks database) SELECT SalesOrderID,ProductID,sum(LineTotal) FROM Sales.SalesOrderDetail GROUP BY SalesOrderID

select salesorderID,ProductID,sum(LineTotal) as Total from sales.salesorderDetail
Group by salesorderID,productID

9.Display the maximum, minimum, and the average rate of sales orders. (Use the AdventureWorks database)

select 'Maximum'=MAX(TotalDue),'Mininum'=MIN(TotalDue),'Average'=AVG(TotalDue) from sales.salesorderHeader

8. Display the total value of all the orders put together. (Use the AdventureWorks database)

select 'Total value of the Order'=sum(TotalDue)
from sales.salesOrderHeader

7. Display the Order ID of the top five orders based on the total amount due in the year 2001. (Use the AdventureWorks database)

select Top 5 salesorderId from sales.salesorderHeader
where Datepart(yyyy,orderDate)=2001 order by totalDue desc

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

select customerID, Name=Left(name,15),salesPersonId from sales.store

5.Display a report containing the sales order ID and the average value of the total amount greater than $ 5,000 in the following format. (Use the AdventureWorks database)

select * from Sales.SalesOrderDetail
select 'sales order ID'= SalesorderID,'Average value'=Avg(lineTotal) from Sales.SalesOrderDetail
Group by SalesOrderID

4. Display the details of all orders in the following format. (Use the AdventureWorks database)

select * from Sales.SalesOrderHeader
select 'Order Number'= salesOrderID,'Total Due'=TotalDue,'Day of order'=DATEPART(DD,OrderDate),'Week day'=DATEPART(DW,orderDate) from Sales.SalesOrderHeader

3. Write a query to display the full name of a person in a column named Person Name. (Use the AdventureWorks database)

select * from Person.Contact
select concat(FirstName,MiddleName,LastName) AS person_Name from Person.Contact

2.Display EmployeeID and HireDate of the employees from the Employee table. The month and the year need to be displayed. (Use the AdventureWorks database)

select * from HumanResources.Employee
select EmployeeID, MONTH=DATENAME(MM,HireDate),YEAR=DATENAME(YY,HireDate)
from HumanResources.Employee

1.Consider the following SQL query: SELECT ProductID, LineTotal AS 'Total' FROM Sales.SalesOrderDetail Group By Cube(LineTotal) Once executed, the preceding query generates errors. Identify the possible causes of such errors and rectify the same. (Use the AdventureWorks database)

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

6. 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. The following tables are used for solving the preceding query.

11. Consider the following Purchase_Details table.

Cust_ID and StoreID make the composite primary key in the table. Identify the partial dependency
in the table, if any. How can you remove the partial dependency to attain the next normal form?

10. Consider the following Student table.

The preceding table is in the first normal form. How can this table be converted into the second
normal form?

9. Consider the following Product table.

The preceding table is not normalized. How can this table be converted into the first normal form?

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, batches, and modules covered in the batches: STUD-ID: Student's id (unique) NAME: Name of student BATCH-NO: Batch number (one student can belong to only one batch) SLOT: Time and day when the batch of 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

Q.5.

select * from [Sales].[SalesOrderDetail]
where [ModifiedDate]='2004-06-06'

Q.4. NIIT SQL LAB@ HOME 2,

select * from [Sales].[SalesOrderDetail]
where [UnitPrice]>2000

Q.3. NIIT SQL LAB@ HOME 2,

select [CustomerID],[AccountNumber] from [Sales].[Customer]
where [TerritoryID]=4

Q.2 .NIIT SQL LAB@ HOME 2,

select * from [Sales].[Customer]

Q.1

select [CreditCardID] as 'Credit CardID',[CardType] as 'Credit Card Type',[CardNumber] as 'Credit Card Number',[ExpYear] as'Expiry Year' from [Sales].[CreditCard]

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. (Use the AdventureWorks database)

select top 10 EmployeeID,LoginID,Title from HumanResources.Employee
where EmployeeID not between 1 and 5

Display the different types of credit cards used for purchasing products. (Use the AdventureWorks database)

select distinct CardType from Sales.CreditCard

Display the top three sales persons based on the bonus. (Use the AdventureWorks database)

select top(3) Bonus,SalesPersonID from Sales.SalesPerson

Display the SalesPerson ID, the Territory ID, and the Sales Quota for those sales persons who have been assigned a sales quota. The data should be displayed in the following format. (Use the AdventureWorks database)

select SalesPersonID as 'Sales Person ID',
TerritoryID as 'Territory ID',
SalesQuota as 'Sales Quota' from Sales.SalesPerson

Display all territories whose names begin with 'N'. (Use the AdventureWorks database)

select * from Sales.SalesTerritory
where Name like 'N%'

Display the details of those stores that have Bike in their name. (Use the AdventureWorks database)

select * from Sales.Store
where Name like '%Bike%'

pmr 6

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("I am a line");
            Console.WriteLine("I am a circle");
            Console.WriteLine("I am a square");
            Console.ReadLine();
        }
    }
}

John is a software developer, who works for Mega Technologies. He is currently under the project of geometric calculations. The program that John has to develop should: Calculate the area of circle. Calculate the volume of the cube.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApplication7

{



class geo

{

int a, r,area,volume;

public void ca()

{

Console.WriteLine("Enter the radius of circle:");

r = Convert.ToInt32(Console.ReadLine());

area = 22/7 * r * r;

Console.WriteLine("the area of circle:" + area);

}

public void cv()

{

Console.WriteLine("Enter the side of cone:");

a = Convert.ToInt32(Console.ReadLine());

volume = a*a*a;

Console.WriteLine("the volume of cone:" + volume);

}

}

class Program

{

static void Main(string[] args)

{

int s;

Console.WriteLine("Enter 1 for area of circle and 2 for Volume of cone:");

s=Convert.ToInt32(Console.ReadLine());

geo g = new geo();

switch (s)

{

case 1:

g.ca();

break;

case 2:

g.cv();

break;

default:

Console.WriteLine("invalid selection");

break;

}

Console.ReadLine();

}

}

}

Sam is working on an application wherein he needs to accept the size of a one dimensional array and its values. He needs to identify the largest and smallest number in the array and based on that he needs to display the result. Help him to create the application.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication6
{
    class Program
    {
        static void Main(string[] args)
        {
            int[] io=new int[3]{5,4,6};
            Console.WriteLine("Sam is working on an application wherein he needs to accept the size of a one dimensional array and its values. He needs to identify the largest and smallest number in the array and based on that he needs to display the result. Help him to create the application.  ");
            if(io[0]>io[1] && io[0]>io[2])
            {
                Console.WriteLine("the largest number is:" + io[0]);
            }
            if (io[1] > io[0] && io[1] > io[2])
            {
                Console.WriteLine("the largest number is:" + io[1]);
            }
            if (io[2] > io[0] && io[2] > io[1])
            {
                Console.WriteLine("the largest number is:" + io[2]);
            }
            if (io[0] < io[1] && io[0] < io[2])
            {
                Console.WriteLine("the smallest number is:" + io[0]);
            }
            if (io[1] < io[0] && io[1] < io[2])
            {
                Console.WriteLine("the smallest number is:" + io[1]);
            }
            if (io[2] < io[0] && io[2] < io[1])
            {
                Console.WriteLine("the smallest number is:" + io[2]);
            }
            Console.ReadLine();
        }
    }
}

Create or delete a macro

To automate a repetitive task, you can quickly record a macro in Microsoft Excel. You can also create a macro by using the Visual Basic Editor in Microsoft Visual Basic for Applications (VBA) to write your own macro script, or to copy all or part of a macro to a new macro. After you create a macro, you can assign it to an object (such as a toolbar button, graphic, or control) so that you can run it by clicking the object. If you no longer use a macro, you can delete it.

What do you want to do?

• Record a macro
• Create a macro by using VBA
• Copy part of a macro to create another macro
• Assign a macro to an object, graphic, or control
• Delete a macro

Record a macro

When you record a macro, the macro recorder records all the steps required to complete the actions that you want your macro to perform. Navigation on the Ribbon is not included in the recorded steps.

1. If the Developer tab is not available, do the following to display it:
1. Click the File tab.

2. Click Options, and then click Customize Ribbon.
3. In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK.

2. To set the security level temporarily to enable all macros, do the following:

1. On the Developer tab, in the Code group, click Macro Security.

2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

Note    To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

For more information about how to change the settings, see Change macro security settings in Excel.

1. On the Developer tab, in the Code group, click Record Macro.
2. In the Macro name box, enter a name for the macro.

Note    The first character of the macro name must be a letter. Subsequent characters can be letters, numbers, or underscore characters. Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.

3. To assign a CTRL combination shortcut key to run the macro, in the Shortcut key box, type any lowercase letter or uppercase letter that you want to use.

Note    The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open.

For a list of CTRL combination shortcut keys that are already assigned in Excel, see Excel shortcut and function keys.

4. In the Store macro in list, select the workbook where you want to store the macro.

Tip    If you want a macro to be available whenever you use Excel, select Personal Macro Workbook. When you select Personal Macro Workbook, Excel creates a hidden personal macro workbook (Personal.xlsb) if it does not already exist, and saves the macro in this workbook. In Windows Vista, this workbook is saved in the C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart folder. In Microsoft Windows XP, this workbook is saved in the C:\Documents and Settings\user name\Application Data\Microsoft\Excel\XLStart folder. Workbooks in the XLStart folder are opened automatically whenever Excel starts. If you want a macro in the personal macro workbook to be run automatically in another workbook, you must also save that workbook in the XLStart folder so that both workbooks are opened when Excel starts.

5. In the Description box, type a description of the macro.
6. Click OK to start recording.
7. Perform the actions that you want to record.
8. On the Developer tab, in the Code group, click Stop Recording .

Tip    You can also click Stop Recording  on the left side of the status bar.

 TOP OF PAGE

Create a macro by using VBA

1. If the Developer tab is not available, do the following to display it:
1. Click the File tab.

2. Click Options, and then click Customize Ribbon.
3. In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK.

2. To set the security level temporarily to enable all macros, do the following:

1. On the Developer tab, in the Code group, click Macro Security.

2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

Note    To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

For more information about how to change the settings, see Change macro security settings in Excel.

1. On the Developer tab, in the Code group, click Visual Basic.

2. If needed, in the Visual Basic Editor, on the Insert menu, click Module.

Note    Modules are automatically created for all sheets in the workbook.

3. In the code window of the module, type or copy the macro code that you want to use.
4. To run the macro from the module window, press F5.
5. In the Visual Basic Editor, on the File menu, click Close and Return to Microsoft Excel when you finish writing the macro.

 TOP OF PAGE

Copy part of a macro to create another macro

1. If the Developer tab is not available, do the following to display it:
1. Click the File tab.

2. Click Options, and then click Customize Ribbon.
3. In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK.

2. To set the security level temporarily to enable all macros, do the following:

1. On the Developer tab, in the Code group, click Macro Security.

2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

Note    To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

For more information about how to change the settings, see Change macro security settings in Excel.

1. Open the workbook that contains the macro that you want to copy.
2. On the Developer tab, in the Code group, click Macros.
3. In the Macro name box, click the name of the macro that you want to copy.
4. Click Edit.
5. In the code window of the Visual Basic Editor, select the lines of the macro that you want to copy.

Tip    To copy the whole macro, include the Sub and End Sub lines in the selection.

6. On the Edit menu, click Copy.

Tip    You can also right-click and then click Copy, or you can press CTRL+C.

7. In the Procedure box of the code window, click the module where you want to place the code.
8. On the Edit menu, click Paste.

Tip    You can also right-click and then click Paste, or you can press CTRL+V.

Tip    You cannot edit your Personal Macro Workbook file (Personal.xlsb) because it is a hidden workbook that is always open. You must first unhide it by using the Unhide command. You can also open it in the Visual Basic Editor by pressing ALT+F11.

 TOP OF PAGE

Assign a macro to an object, graphic, or control

1. On a worksheet, right-click the object, graphic, or control to which you want to assign an existing macro, and then click Assign Macro.
2. In the Macro name box, click the macro that you want to assign.

 TOP OF PAGE

Delete a macro

1. Do one of the following:
• Open the workbook that contains the macro that you want to delete.
• If the macro that you want to delete is stored in the personal macro workbook (Personal.xlsb), and this workbook is hidden, do the following to unhide the workbook:
1. On the View tab, in the Window group, click Unhide.
2. Under Unhide workbooks, click PERSONAL, and then click OK.
2. If the Developer tab is not available, do the following to display it:

1. Click the File tab.

2. Click Options, and then click Customize Ribbon.
3. In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK.

1. On the Developer tab, in the Code group, click Macros.

2. In the Macros in list, select the workbook that contains the macro that you want to delete. For example, clickThis Workbook.
3. In the Macro name box, click the name of the macro that you want to delete.
4. Click Delete.

jhon wants to enter player name (range int 1 to 4)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication20
{
    class Program
    {
        static void Main(string[] args)
        {
            int a,i;
            Console.WriteLine("enter the number of players");
            a=Convert.ToInt32(Console.ReadLine());

            string[] arr= new string [a];
            for(i=0;i<a;i++)
            {
                arr[i]=Console.ReadLine();
            }
            for (i = 0; i < a; i++)
            {
                Console.WriteLine("name of player:" + arr[i]);
            }
            Console.ReadLine();
        }
    }
}

lab @ home 10

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApplication4



{
class duplicate



{
static void Main(string[] args)



{
char[] ch = new char[5] { 'a', 'b', 'a', 'c', 'd' };

int i, count = 0;

Console.WriteLine("enter a character:");

char input = Convert.ToChar(Console.ReadLine());

for (i = 0; i < 5; i++)



{
if (ch[i] == input)



{

count++;

}

}


Console.WriteLine("{0} appears {1} times in the character array", input, count);

Console.ReadLine();





}

}

}

File handling....in C#

File handling in C#

 

File handling is an unmanaged resource in your application system. It is outside your application domain (unmanaged resource). It is not managed by CLR.

 

Data is stored in two ways, persistent and non-persistent manner.

 

When you open a file for reading or writing, it becomes stream.

 

Stream: Stream is a sequence of bytes traveling from a source to a destination over a communication path.

 

The two basic streams are input and output streams. Input stream is used to read and output stream is used to write.

 

The System.IO namespace includes various classes for file handling.

 

The parent class of file processing is stream. Stream is an abstract class, which is used as the parent of the classes that actually implement the necessary operations.

 

The primary support of a file as an object is provided by a .NET Framework class called File. This static class is equipped with various types of (static) methods to create, save, open, copy, move, delete, or check the existence of a file.

 

Diagram to represent file-handling class hierarchy 
 

 

 

Note: FileIno, DirectoryInfo and DriveInfo classes have instance methods. File, Directory, Path classes have static methods.

 

The following table describes some commonly used classes in the System.IO namespace.

 

Class Name	Description
FileStream	It is used to read from and write to any location within a file
BinaryReader	It is used to read primitive data types from a binary stream
BinaryWriter	It is used to write primitive data types in binary format
StreamReader	It is used to read characters from a byte Stream
StreamWriter	It is used to write characters to a stream.
StringReader	It is used to read from a string buffer
StringWriter	It is used to write into a string buffer
DirectoryInfo	It is used to perform operations on directories
FileInfo	It is used to perform operations on files

 

Reading and writing in the text file

 

StreamWriter Class

 

The StreamWriter class in inherited from the abstract class TextWriter. The TextWriter class represents a writer, which can write a series of characters.

 

The following table describes some of the methods used by StreamWriter class.

 

Methods	Description
Close	Closes the current StreamWriter object and the underlying stream
Flush	Clears all buffers for the current writer and causes any buffered data to be written to the underlying stream
Write	Writes to the stream
WriteLine	Writes data specified by the overloaded parameters, followed by end of line

 

Program to write user input to a file using StreamWriter Class

 

using System;

using System.Text;

using System.IO;

 

namespace FileWriting_SW

{

    class Program

    {

        class FileWrite

        {

            public void WriteData()

            {

                FileStream fs = new FileStream("c:\\test.txt", FileMode.Append, FileAccess.Write);

                StreamWriter sw = new StreamWriter(fs);

                Console.WriteLine("Enter the text which you want to write to the file");

                string str = Console.ReadLine();

                sw.WriteLine(str);

                sw.Flush();

                sw.Close();

                fs.Close();

            }

        }

        static void Main(string[] args)

        {

            FileWrite wr = new FileWrite();

            wr.WriteData();

        }

    }

}

 

StreamReader Class

 

The StreamReader class is inherited from the abstract class TextReader. The TextReader class represents a reader, which can read series of characters.

 

The following table describes some methods of the StreamReader class.

 

Methods	Description
Close	Closes the object of StreamReader class and the underlying stream, and release any system resources associated with the reader
Peek	Returns the next available character but doesn't consume it
Read	Reads the next character or the next set of characters from the stream
ReadLine	Reads a line of characters from the current stream and returns data as a string
Seek	Allows the read/write position to be moved to any position with the file

 

Program to read from a file using StreamReader Class

 

using System;

using System.IO;

 

namespace FileReading_SR

{

    class Program

    {

        class FileRead

        {

            public void ReadData()

            {

                FileStream fs = new FileStream("c:\\test.txt", FileMode.Open, FileAccess.Read);

                StreamReader sr = new StreamReader(fs);

                Console.WriteLine("Program to show content of test file");

                sr.BaseStream.Seek(0, SeekOrigin.Begin);

                string str = sr.ReadLine();

                while (str != null)

                {

                    Console.WriteLine(str);

                    str = sr.ReadLine();

                }

                Console.ReadLine();

                sr.Close();

                fs.Close();

            }

        }

        static void Main(string[] args)

        {

            FileRead wr = new FileRead();

            wr.ReadData();

 

        }

    }

}

 

I hope that this article would have helped you in understanding file handling.

 

Your feedback and constructive contributions are welcome. Please feel free to contact me for feedback or comments you may have about this article.