RDBMS Lab@Home 5

1. Write a query to display the sales person ID, territory ID, and territory name of all the sales persons in the following format. (Use the AdventureWorks database)

Ans.

2. Write a query to display the sales person ID of all the sales persons and the name of the territory to which they belong. (Use the AdventureWorks database)

Ans.  

3.Write a query to display the sales person ID and territory names for all the sales persons. If a sales person does not belong to any territory, NULL should be displayed as shown in the following format. (Use the AdventureWorks database)

Ans, 

Ans. 

4. Write a query to display the sales order ID, the product ID, and the order date for all the products in the following format. (Use the AdventureWorks database).

Ans. 

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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8eWg1NnQ1cmxoVDA

6. Write a query to display the sales order ID, territory name, month, and year of all the sales orders in the following format. (Use the AdventureWorks database)

Ans.https://drive.google.com/open?id=0B08RKBSts8D8RDNQQnRidUEyX2s

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

Ans. https://drive.google.com/open?id=0B08RKBSts8D8XzgyWEV4aV9XRmc

8.  Write a query to display all the country region codes along with their corresponding territory IDs. (Use the AdventureWorks database)

Ans https://drive.google.com/open?id=0B08RKBSts8D8ci1kY3ZmT1JwbTA

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

Ans  https://drive.google.com/open?id=0B08RKBSts8D8VzJwdzJtbXZhREE

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

Ans. https://drive.google.com/open?id=0B08RKBSts8D8ZmVaV282SEJ5X1k

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

Ans. https://drive.google.com/open?id=0B08RKBSts8D8cHJJenN4SDZ1d3c

12. Write a query to display the contact ID of the customers that have the 'Vista' credit card. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8dGFoZ3hDR0VpRFU

13. Write a query to display the sales order IDs of the orders received from the Northeast territory. (Use the AdventureWorks database)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8VVdKNXFxdTlrZ0k

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

Ans  https://drive.google.com/open?id=0B08RKBSts8D8T1lQRk5VSG51bGs

15. Write a query to display the sales order IDs of the orders that have been paid through a SuperiorCard. (Use the AdventureWorks database)

Ans  https://drive.google.com/open?id=0B08RKBSts8D8NXMzVlpJcDVXNmc

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

Ans  https://drive.google.com/open?id=0B08RKBSts8D8LVdoYkkxeUVfcHc

17. Write a query to display the details of those orders for which no discount was offered. (Use the AdventureWorks database)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8MWRqYVg4VDNBZkU

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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8aFpiMEtGLTU5VE0

19. Write a query to display the average rate of the Australian Dollar, where the CurrencyRateDate is July 1, 2004. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8YUdmSlZYQjRwRDA

RDBMS Lab@Home 4

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

Ans.

2. 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)

Ans.

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

Ans. 

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

Ans

.

5. 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)

Ans.

6.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)

Ans.

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

Ans.

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

Ans.

9.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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8SHNKTXJHR2NVZ0U

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

Ans.

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

Ans.   https://drive.google.com/open?id=0B08RKBSts8D8OTJiRTBoenBQMjg

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)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8S2stN2Z1QnZjTWc

13.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)

Ans.https://drive.google.com/open?id=0B08RKBSts8D8UHMyc3RjR2tSX0U

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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8My0yVUUtX0s4QXM

15. 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)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8ejQtQWtnbWJvaUU

16. 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)

Ans.   https://drive.google.com/open?id=0B08RKBSts8D8eWFJVmFfY1FNYlU

17. 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)

Ans  https://drive.google.com/open?id=0B08RKBSts8D8YzkzLVpmemg0R1E

RDBMS Lab@Home 3

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

Ans.

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

Ans .

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

Ans. 

4.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)

Ans 

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

Ans https://drive.google.com/open?id=0B08RKBSts8D8RV9wVGVMUFJrTWs

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

Ans. 

7. 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.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8RW1aLVVSSWpnTkk

8. 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?

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8V2dVOXNDLVg5R28

9. Consider the following 

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

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8ek5tbEtkVW1lY1E

10. 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?

Ans. https://drive.google.com/open?id=0B08RKBSts8D8NklSYUsyckFVTE0

RDBMS Lab@Home 2

Q.1. Display the ID, type, number, and expiry year of all the credit cards in the following format. (Use the AdventureWorks database)

Ans  https://drive.google.com/open?id=0B08RKBSts8D8UGFrMVkxQmZhMTA


Q.2.  Display the details of all the customers. (Use the AdventureWorks database)


Ans. https://drive.google.com/open?id=0B08RKBSts8D8ajlSU1B3clVicUU


Q.3.  Display all the details of the sales orders that have a cost exceeding $ 2,000. (Use the AdventureWorks database)


Ans.  https://drive.google.com/open?id=0B08RKBSts8D8SFRnUjB5eDhQMnc


Q.4.  Display the customer ID and the account number of all the customers who live in the TerritoryID 4. (Use the AdventureWorks database)


Ans.  https://drive.google.com/open?id=0B08RKBSts8D8SDg4OE8yVExRbTg


Q.5.  Display the list of all the orders placed on June 06, 2004. (Use the AdventureWorks database)


Ans   https://drive.google.com/open?id=0B08RKBSts8D8QWxGSnBZbko5ZTQ


Q. 6  Display the sales order details of the product named 'Cable Lock' for which the ProductId is 843. (Use the AdventureWorks database)

Ans   https://drive.google.com/open?id=0B08RKBSts8D8a0VkclEwRUpzc0k


Q.7.  Display the name, country region code, and sales year to date for the territory with Territory ID as 1. (Use the AdventureWorks database)


Ans  https://drive.google.com/open?id=0B08RKBSts8D8YWpnMWJLOFh1eUU


Q.8. Display a list of all the sales orders in the price range of $ 2,000 to $ 2,100. (Use the AdventureWorks database)


Ans   https://drive.google.com/open?id=0B08RKBSts8D8RngtTzlHbTd4VU0


Q.9.   Display the sales territory details of Canada, France, and Germany. (Use the AdventureWorks database)


Ans   https://drive.google.com/open?id=0B08RKBSts8D8WTVROWQxZ2M4Ymc


Q.10.   Display the details of the orders that have a tax amount of more than $ 10,000. (Use the AdventureWorks database)


Ans    https://drive.google.com/open?id=0B08RKBSts8D8akdpSDVTaDhIOGM


Q.11.  Generate a report that contains the IDs of sales persons living in the territory with TerritoryID as 2 or 4. The report is required in the following format. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8YktWTVhFQlJZcXc

Q.12.  Display the details of the Vista credit cards that are expiring in the year 2006. (Use the AdventureWorks database)

Ans.   https://drive.google.com/open?id=0B08RKBSts8D8MnFaSXdvQzcwc2c

Q.13.   Display the orders placed on July 01, 2001 that have a total cost of more than $ 10,000 in the following format. (Use the AdventureWorks database)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8XzFLaXYwaFZ0LXc

Q.14.  Display the details of all the orders that were shipped after July 12, 2004. (Use the AdventureWorks database)

Ans   https://drive.google.com/open?id=0B08RKBSts8D8SEk4QVJ6UEdsa0U

Q.15.  Display a report of all the orders in the following format. (Use the AdventureWorks database)

Ans  https://drive.google.com/open?id=0B08RKBSts8D8enhibURjZ1JQQmM

Q.16.  Display the order ID and the tax amount for the sales orders that are less than $ 2,000. The data should be displayed in ascending order. (Use the AdventureWorks database)

Ans   https://drive.google.com/open?id=0B08RKBSts8D8d1ZGNU5CeVdpMEE

Q.17.  Display the order ID and the total amount due of all the sales orders in the following format. Ensure that the order with the highest price is at the top of the list. (Use the AdventureWorks database)

Ans  https://drive.google.com/open?id=0B08RKBSts8D8T2JpRjVZZVVvbTA

Q. 18.  Display the order number and the total value of the order in ascending order of the total value. (Use the AdventureWorks database)

Ans  https://drive.google.com/open?id=0B08RKBSts8D8X19vMzIwWEJRcWs

Q.19..  Display the details of all the currencies that have the word 'Dollar' in their name. (Use the AdventureWorks database)

Ans  https://drive.google.com/open?id=0B08RKBSts8D8V0ZwYjNHSGJ1UDA

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 the ER diagram to represent the preceding situation, and then to map the ER diagram to the corresponding tables. Help Chen to do the same.

Ans

2. Design an ER diagram for the following situation:
An organization has two types of employees, salaried and wage earning. Both the types of employees have 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.

Ans

3.  Consider the following Doctor-details table.

 In the preceding table, you have to identify primary key, candidate key, and alternate key and give the reason.

Ans 

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 data easily accessible. The following ER diagram represents the relationship between the patient and the doctor. 

In the preceding diagram, you have to identify the entities, their attributes, and the type of relationship between the entities.

Ans  

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. The following ER diagram represents the relationship between the departments and the employees.

 In the preceding diagram, you have to identify entities, their attributes, and type of relationship between the entities.

Ans 

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. The following ER diagram represents the relationship between a customer and the accounts owned by a particular customer. 

In the preceding diagram, you have to identify entities, their attributes, and type of relationship between the entities.

Ans. 

7.  Consider the following scenario: 

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

Ans  

8.  Consider the following scenario: 

In a college, there are multiple departments. Each department has various faculty members. Only one of these faculty members holds the position of the Head of the Department. Identify the relationship between the faculty and the department and represent it with an ER Diagram.

Ans  

Java _Network Files

Programmers Files  :-

1. SimpleClient

Ans

public class TestThread {
  public static void main(String args[]) {
    Xyz r = new Xyz();
    Thread t = new Thread(r);
    t.start();
  }
}

class Xyz implements Runnable {
  int i;

  public void run() {
    i = 0;

    while (true) {
      System.out.println("Hello " + i++);
      if ( i == 50 ) {
break;
      }
    }
  }
}

  
2.  SimpleServer

Ans

import java.net.*;
import java.io.*;

public class SimpleServer {
  public static void main(String args[]) {
    ServerSocket s = null;

    // Register your service on port 5432
    try {
      s = new ServerSocket(5432);
    } catch (IOException e) {
      e.printStackTrace();
    }

   // Run the listen/accept loop forever
    while (true) {
      try {
        // Wait here and listen for a connection
        Socket s1 = s.accept();
System.out.println("Connection accepted: port=" + s1.getPort());

        // Get output stream associated with the socket
        OutputStream s1out = s1.getOutputStream();
        BufferedWriter bw = new BufferedWriter(
          new OutputStreamWriter(s1out));

        // Send your string!
        bw.write("Hello Net World!\n");

        // Close the connection, but not the server socket
        bw.close();
        s1.close();
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
  }
}

Code Files :-  https://drive.google.com/open?id=0B08RKBSts8D8eFh6aVdkS0o4NzQ

Java _ threads Files

Programmers Files :-


1. Sync File :-https://drive.google.com/open?id=0B08RKBSts8D8aFB2cVVlVFIzTlk




*1 .ControlledThread

Ans

public class ControlledThread extends Thread {
  static final int SUSP = 1;
  static final int STOP = 2;
  static final int RUN = 0;
  private int state = RUN;

  public synchronized void setState(int s) {
    state = s;
    if ( s == RUN ) {
      notify();
    }
  }

  public synchronized boolean checkState() {
    while ( state == SUSP ) {
      try {
        wait();
      } catch (InterruptedException e) {
        // ignore
      }
    }
    if ( state == STOP ) {
      return false;
    }
    return true;
  }

  public void run() {
    while ( true ) {
      //doSomething();

      // Be sure shared data is in consistent state in
      // case the thread is waited or marked for exiting
      // from run()
      if ( !checkState() ) {
        break;
      }
    }
  }
}

*2.  MyStack

Ans

public class MyStack {
  int idx = 0;
  char [] data = new char[6];

  public void push(char c) {
    data[idx] = c;
    idx++;
  }

  public char pop() {
    idx--;
    return data[idx];
  }
}

*3. TestMyStack

Ans

public class TestMyStack {

   public static void main(String args[]) {

     MyStack stack = new MyStack();

     stack.push('H');

     stack.push('i');

     System.out.println("Stack is: " + String.copyValueOf(stack.data));

     stack.pop();

     stack.push('o');

     System.out.println("After one pop and another push, Stack is: " + 

String.copyValueOf(stack.data));

   }

}

     

*4. TestThread

Ans

public class TestThread {

  public static void main(String args[]) {

    Xyz r = new Xyz();

    Thread t = new Thread(r);

    t.start();

  }

}

class Xyz implements Runnable {

  int i;

  public void run() {

    i = 0;

    while (true) {

      System.out.println("Hello " + i++);

      if ( i == 50 ) {

break;

      }

    }

  }

}

Code Files :- https://drive.google.com/open?id=0B08RKBSts8D8LVZyY3hsU25XZUE