Assume a table, task, with the following columns:
§ task_id (int, Not NULL)
§ task_description (text, Not NULL)
§ parent_task_id (int, NULL)

Write a stored procedure and program (in any language of your choosing) to produce a text based hierarchical tree of the tasks rooted at a given task, using tabs to show the hierarchy. For example, if these rows exist in the task table:

task_idtask_descriptionparent_task_id
1Eat LunchNULL
2Choose restaurant1
3Order food1
4Call restaurant3
5Have staff meetingNULL
6Send agenda5

And your program in invoked as follows:
tasktree –t 1

It will produce this output:
Eat Lunch
Choose restaurant
Order food
Call restaurant

Question

Assume a table, task, with the following columns:
§  task_id (int, Not NULL) 
§  task_description (text, Not NULL) 
§  parent_task_id (int, NULL) 
 
Write a stored procedure and program (in any language of your choosing) to produce a text based hierarchical tree of the tasks rooted at a given task, using tabs to show the hierarchy.  For example, if these rows exist in the task table:
 
task_idtask_descriptionparent_task_id
1Eat LunchNULL
2Choose restaurant1
3Order food1
4Call restaurant3
5Have staff meetingNULL
6Send agenda5

 
And your program in invoked as follows:
tasktree –t 1
 
It will produce this output:
Eat Lunch
      Choose restaurant
      Order food
            Call restaurant

Sigiloso · Accepted Answer

/* Create stored procedure */
USE [Test]
GO

/* Step 0 Create Table and Insert Data*/
CREATETABLE [dbo].[task](
	[task_id] [int] NULL,
	[task_description] [text] NOTNULL,
	[parent_task_id] [int] NULL
)ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERTINTO [Test].[dbo].[task] VALUES (1,'Eat Lunch',NULL)
INSERTINTO [Test].[dbo].[task] VALUES (2,'Choose restaurant', 1)
INSERTINTO [Test].[dbo].[task] VALUES (3,'Order food', 1)
INSERTINTO [Test].[dbo].[task] VALUES (4,'Call restaurant', 3)
INSERTINTO [Test].[dbo].[task] VALUES (5,'Have staff meeting',NULL)
INSERTINTO [Test].[dbo].[task] VALUES (6,'Send agenda',NULL)
GO

/*Step 1 */
CREATENONCLUSTEREDINDEX NC_NU_task_parenttaskid ON [Test].[dbo].[task]([parent_task_id])

/*Step 2 */
CREATEPROCEDURE [dbo].[tasktree]
(
	@Root int
)
AS
BEGIN
	SETNOCOUNTON
	DECLARE @task_id int, @task_description varchar(30)

	SET @task_description =(SELECT task_description 
FROM [Test].[dbo].[task] 
WHERE task_id = @Root)
	PRINTREPLICATE(char(9),@@NESTLEVEL* 1)+ @task_description

SET @task_id =(SELECTMIN(task_id)
FROM [Test].[dbo].[task] 
WHERE parent_task_id = @Root)

	WHILE @task_id ISNOTNULL
	BEGIN
		EXEC dbo.tasktree@task_id
		SET @task_id =(SELECTMIN(task_id)
FROM [Test].[dbo].[task]   
WHERE parent_task_id = @Root AND task_id > @task_id)
	END
END

/*Step 3 Test store proc */
exec dbo.tasktree1


/* Write Java Program. Create tasktree.java file */
/* User should invoke program by typing "java tasktree -t 1" where 1 is the Root
   for the program. Users may enter Roots 1 thru 6 and yield similar results
   like the stored procedure. */


import java.sql.*;

public class tasktree {

	public static void main(String[] args) {

		// Create a variable for the connection string.
		String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=Test;integratedSecurity=true;";

		// Declare the JDBC objects.
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;


        	try {
        		// Establish the connection.
        		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

                con = DriverManager.getConnection(connectionUrl);


               /* for (int i = 0; i = " + Root;
                    System.out.println(SQL);
                    System.out.println("Root = " + Root);
                    System.out.println("          ");
            		stmt = con.createStatement();
            		rs = stmt.executeQuery(SQL);

					ResultSetMetaData metadata = rs.getMetaData();
					int parent_task_id2=0;
                    int Node=0;
            		// Iterate through the data in the result set and display it.
					while (rs.next()) {

                          if(Integer.parseInt(rs.getString(1)) > Root && rs.getString(3)==null){
					      }else{

							 int parent_task_id1=0;
                           if(rs.getString(3) == null){
						         parent_task_id1=0;
						   }else{
                              parent_task_id1=Integer.parseInt(rs.getString(3));
					       }


                           if(Integer.parseInt(rs.getString(1)) > Root && parent_task_id1 > parent_task_id2){


                                Node=Node+1;
						          for (int y=0; y

Two Sigma

Pergunta de entrevista da empresa Two Sigma

Resposta da entrevista

Empresas seguidas

Buscas de vagas