Practice Question ( 5,6,7 ) for Board Exam on 22 March 2013

Q No - 5 

a. What is the purpose of ALTER TABLE command in MYSQL? How is it different from UPDATE command?

b. Table Traveldetails has 15 records and table Passenger has 5 records in it. Ms. Nidhi wants to display all information stored in both of these related tables . She forget to specify the equi-join condition in the query. How many rows will get displayed on execution of this query?

c. Consider the Relation Doctor given below, write command in SQL for (i) to (iv) and output for (v) to (viii)

Relation: Doctor

ID	Name	Dept	Gender	Experience	ConstFee
201	R.K. Nath	ENT	M	12	300
457	Mahavir Singh	SKIN	M		500
365	M. Asthana	MEDICINE	F	9	250
221	V. S. Nag	ENT	M	3	150
122	S. P. Sinha	NEPHRO	F		200
110	J. P. Pandey	CARDIOLOGY	M	9	500

     i. To display name of all doctors who are in Medicine having more than 10 years of experience.

     ii. To display the count of the different departments.

     iii. To display minimum consultation fee of female doctors

     iv. To display name and department of male doctors who has no experience.

     v. SELECT AVG(ConstFee) FROM Doctor  WHERE NOT Gender=’F’;

     vi. SELECT Count(Experience) FROM Doctor;

     vii. SELECT Name, Experience FROM Doctor WHERE id BETWEEN  100 AND 200;

     viii. SELECT SUM(ConstFee),MAX( Experience)  FROM Doctor;

Q No - 6 

Answer the following question.

   a.       Write an SQL query to create the  table  “peripheral“ with the following structure -

Field	Type	Constraint
P_Id	Varchar(5)	Primary Key
P_Name	Varchar(25)
Manufacturer	Varchar(15)
Price	Integer	Must between 399 and 4999
Quantity	Integer	Not Null

   b.      In a database there are two Relations ‘Dress’ and ‘Material’ shown below - 

                        Table/Relation : Dress

Dcode	Description	Price	MCode	LaunchDate
1002	Trouser	999	M002	15-12-2010
2006	Coat	1599	M001	27-09-2009
1036	Sweater	540	M001	11-01-2008
1789	Frock	1950	M003	31-12-2010

                      Table / Relation: Material

MCode	Type
M001	Woolen
M002	Cotton
M003	Polyester
M004	Silk
M005	Denim

i) Name the column  which can be made ‘Foreign Key’ from both the tables

ii) To display the dress details which are made up of woolen?

  c.       Consider the tables    given below:

                        Table : STOCK

Itcode	Itname	Dcode	Qty	unitpr	Stkdate
444	Drawing Copy	101	10	21	31-June-2009
445	Sharpener Camlin	102	25	13	21-Apr-2010
450	Eraser Natraj	101	40	6	11-Dec-2010
452	Gel Pen Montex	103	80	10	03-Jan-2010
457	Geometry Box	101	65	65	15-Nov-2009
467	Parker Premium	102	40	109	27-Oct-2009
469	Office File	103	27	34	13-Sep-2010

                       Table : DEALERS

Dcode	Dname	Location
101	Vikash Stationers	Lanka Varanasi
102	Bharat Drawing Emporium	Luxa Varanasi
103	Banaras Books Corporation	Bansphatak Varanasi

   With reference to these tables, write commands in SQL for (i) and (ii) and output for (iii) below-

(i) To display the amount that has been spent to purchase Office file & Sharpener camlin.

(ii) To display all the items of Vikash Stationers.

(iii) SELECT DCODE,COUNT(QTY),AVG(UNITPR) FROM STOCK GROUP BY DCODE;

Q No - 7 

a.  Name any two web portals involved in e-Business. And how is it benefited to the company.

b. How has e-governance benefited the common man?  Name any two sites.

c. Neha works for a Hotel. Sky warts to create controls on a form for the following function. Choose appropriate controls from TextField, Label , RadioButton , CheckBox , List , ComboBox , Button and write in the third column.

S.No.	Controls  use to	Control
1	Select room type
2	Enter customers name
3	Enter arrival date
4	To book room

Practice Questions ( 3,4) Board Exam on 22 Mar 2013

Q No -3  (1 + 2 + 2 +2 + 2 + 1 = 10 Marks)

a. Surendra wants to implement referential integrity in the tables being created. Which constraint should be used by Surendra while doing so?

b. Meena uses an EMP table with following columns:

   NAME, SAL, ID, DNAME 

   She needs to display names of employees who have not been assigned any department or have been assigned “Pathology” department. Pathology dept’s names end with “Pathology”. She wrote the following query:

SELECT NAME, SAL FROM EMP 

WHERE DNAME = NULL AND DNAME = “%Pathology”;

c. What are three transaction control statements n MySQL? Give example.

d. What do you mean by constraints? Explain ENUM and SET constraints.

e. What do you mean by UNION? Also write the syntax of UNION.

f. Which command is used in MySQL to make the changes in database permanent?

Q No - 4 (2 + 2 + 1 + 1 + 2 + 2 + 1 + 3 + 1 = 15 Marks)

a. Rewrite the correct code underlining all the corrections made:

int i; j=5;

i== j+5;

if(i=j)

  { jTextField1.setText(“i and j are unequal”);

    jTextField2.setText(“they are not equal”); break;

  }

else

  jTextField1.setText(“i and j are equal”)

b. Rewrite the following program code using for loop:

int num=6;

int temp=num;

while(num>=1)

{ temp=temp-1;

if (temp%2==0)

System.out.println(“Is even”);

else

System.out.println(“Is odd”);

num=num-2;

}

c. Write the output of the program:

int x = 10;

int y = 15;

jTextField1.setText(“”+(x>y)? 3.14: 3);

d. What will be the contents of jTextField after executing the following statement?

   jTextField.setText ( ‘B’  + ‘a’ );

e.  What will be the output of the program given below. Which number is printed twice? (Show the working also)

  int sum1=3;

  sum1++;

  jTextField1.setText(“”+sum);

  ++sum1;

  jTextField2.setText(“”+sum1);

  jTextField3.setText(“”+(++sum1));

  jTextField4.setText(“”+sum1++);

  1jTextField5.setText(“”+sum1);

f. What will be the value of M and N after execution of the following code:

   int M=150,N;

   for(N=10;N<=12;N++)

   M+=N;

   JOptionPane.showMessageDialog(this,”M : ” + M +”N: “+ N+””);

g. Mr. R C Joshi is working with railways and he has to design an application which can calculate the total fare. The following is the Fare Calculator along with details: (Use only defaults name for the controls i.e. Swing Components)

a.    Write the code for exit button so that when a user clicks on exit button Application will be closed. Also display a message “Thank you for your nice visit” before exiting the application.

b.    Write a code to calculate the total fare according to the given conditions:

i)                    The type of coach is selected by the user from  the jList1 and the charges are for 1 tier coach Rs. 2000 per person , 2 tier coach Rs 1500 per person  and for 3 tier coach 1000 per person.

ii)                   If the person is travelling in AC coach then increase the fare by 20%.

iii)                 If the person is senior citizen the fare will be 50%.

iv)                 The total fare will be number of passenger travelling multiply by fare calculated per seat.

  c.    Write the property which is perquisite for radio button.