CLASS XI SQL TEST

Answer the following questions:
(a)	Compare DDL and DML statements of SQL.
(b)	Swati needs to remove all the rows from SALES table to release the storage space. But she does not want to remove the table structure. Which statement should she use?
(c)	Meena uses a 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 course’s names end with “Pathology”. She wrote the following query: SELECT NAME,SAL FROM EMP,COURSE WHERE DNAME = NULL OR DNAME = “%pathology”; But the query is not producing result. Identify the problem.
(d)	What is the importance of primary key in a table? Explain with example.
A table ACCOUNT in a database has 6 columns and 60 rows. The DBA has added 3 more columns and 50 more rows to the table. But the table has about 15 records where balance is null. What is the degree and cardinality of this table now ?

·         Consider the following tables PRODUCT and answer (b) and (c) part of this question
Relation :PRODUCT
PCODE PNAME  COMPANY    PRICE    STOCK    MANUFACTURE    WARRANTY
P001  TV      BPL       10000     200     12-JAN-2008         3
P002  TV      SONY      12000     150     23-MAR-2007         4
P003  PC      LENOVO    39000     100     09-APR-2008         2
P004  PC      COMPAQ    38000     120     20-JUN-2009         2
P005  HANDYCAM  SONY    18000     250     23-MAR-2007         3 


Write MySQL commands for following Statements
i.To show details of all the PC with stock more than 110.       
ii.To list the company which gives warranty for more than 2 years


iii.To show the PRODUCT name which are within warranty as on date.
iv) Show the products whose warranty is over

Q1)Consider the table Hospital given below.

No	Name	Age	DEPARTMENT	DateOfAdm	Charges	Sex
1	Sandeep	64	Surgery	23/02/98	300	M
2	Ravina	24	Orthopedic	20/01/98	200	F
3	Karan	45	Orthopedic	10/02/98	200	M
4	Tarun	12	Surgery	01/01/98	300	F
5	Zubin	36	ENT	12/01/98	250	M
6	Ketaki	16	ENT	12/02/98	300	F
7	Ankita	29	Cardiology	20/02/98	800	F
8	Zareen	45	Gynecology	22/02/98	Null	F
9	Ankit	19	Cardiology	13/01/98	800	M
10	Shailya	31	Medicine	19/02/97	400	F

—  To show all information about the patients of cardiology department.

—  To list the names of female patients who are in orthopaedic department.

—  To display Patient’s name, charges, Age for  male and female patients.

—  Increase the charges of male patient in ENT department by 3%.

—  Add another column email_id with suitable data type.

—   Delete the records of all female patients in Surgery department.

—  Display a report listing name, age, charges and amount of charges including VAT as 2%  on charges name the column as total charges and keep the data in ascending order of name.

—    Display department name where charges are null.

Insert new row with following values dept="ENT"  patient name="mohit"

—   Display the details of all the patients who are hospitalised in 1998.

—   Display the charges of various departments .A charge amount should appear only once.  

QGive output:

SELECT ASCII('Q')
FROM DUAL;

SELECT CONCAT(name, ' is a good boy') "Result"
FROM hospital
WHERE dept=’ENT’;

SELECT SUBSTR('SPIDERMAN',7,3);

SELECT instr('oralce apps','app');

SELECT round(123.67,-1) FROM DUAL;

SELECT trunc(123.67) FROM DUAL;

Q) Extract ‘my’ from ‘Mysql’ string.

q) Extract month from date(12-03-2012’);

q) select lower(substr(“MYSQLCLASSXI”,3,4));

Q)Select length(lower(substr(“CLASS XI”,-2,2)));

test1

1)Design a GUI application to calculate the monthly telephone bills as per the following rule:
Minimum Rs. 200 for upto 100 calls.
Plus Rs. 0.60 per call for next 50 calls.
Plus Rs. 0.50 per call for next 50 calls.
Plus Rs. 0.40 per call for any call beyond 200 calls.

2) Design a gui application that repeatdely accepts number in a an option pane and once the number typed is 0, the maximum and minimum number is displayed

3)Convert the following ‘while loop’ into its equivalent ‘for loop without altering the output

int x = 100, a = 30;

while ( x>= 10)

{ System.out.println(”New Amount =”+(x + a));

a++;

x - = a;}

4)Write a program to swap value of two variables .

 5)ava code that takes a number from jTextField1 and displayed its table in jTextArea1.                    (e.g 6*1 . 6*2=12..uptil 6*10

6)What is the difference between setEnabled and setVisible methods of a control?

7)Predict Output:for a=15 and a=20

if(a%4==0)

if(a%5==0)

System.out.println(a);

else

System.out.print(a++);

else

 System.out.print(a+2);

8)char ch;Short sh;Int intval;long longval; float fl;

identify the datatype of the following expressions:

‘a’-3;

Intval*longval-ch;

Fl+longval/sh;

9)State the output of the following program:

public static void main(String args[ ])

{int x = 10;

double y = 10.0;

System.ou.println((x>y)? true: false);}

10 Give the output of the following statements.                                                                                        

jTextField1.setText(“CBSE\nFinal\tExam”);

11Differentiate between executeUpdate() and executeQuery();

12 What are the steps to connect to a database with a java application?
13. What is database connectivity? How is database connectivity useful?

14 Explain the purpose of following methods:
a) next() b)getModel() c)getRowcount() d)removeRow()

15What is the purpose of the class ResultSet?

16 Given a string object names salary having value as ‘55000” stored in it .,Obtain the output of the following:

a)JOptionPane.showMessageDialog(null,” “+salary.length()+Integer.parseInt(salary));

b) JOptionPane.showMessageDialog(null,salary.length()+Integer.parseInt(salary));

17Ms. Sunita has developed a Java application through which the students of her school can view their
marks by entering their admission number. The marks are displayed in various text fields. What should
she do so that the students are able to view but not change their marks in text fields?                        

19What will be values of x and y  after execution of the following code : int x, y = 0;
                 for( x= 1; x<=5; ++x)
                  y=x++;
                  --y;

20Which Command is used in MySQL to make the changes in database permanent?                                      

21While Creating a table ‘Customer’ Simrita forget the set of primary key for the table.  Give  the statement which she should write now to set the column ‘CustID’  as the primary key of the table?

22 Write two SQL statement to set Autocommit to off.

23 Mention two sql commands on which an implicit commit takes place even if the autocommit is off.

24 Rama is not able to change a value in a column to NULL. What constraint must have been thr in the table.

25 In a student table, out of RollNumber, Name, Address which column can be set as Primary key and Why?

26  Define Inheritance with reference to Object Oriented Programming.                                                  

 27  Read the following case study and answer the questions that follow :
 The  Shop n Store has developed the following data entry screen for its operations. The store offers three different types of membership discount schemes for its regular customers. Platinum members get a discount of 10% on all their purchases, Gold members get 5% and Silver members get 3% discount.
           

a)Write the code to disable the text fields AmountTF, DiscountTF and NetTF.                    
b)Write the code for CalcBTN to calculate the amount, discount and net amount as per given  descp

c)Write the code for ExitBTN to close the application, but before the application is closed it should check the net amount and if the net amount  > 10,000 the membership of the customer should be upgraded and displayed. For example, if the customer already has Silver membership  it should be upgraded Gold membership (similarly from gold to platinum);informed of the same using a message box.

28Study the following tables Doctor and Salary and write SQL Commands                                         
    Table  : DOCTOR                                                                                            Table: SALARY

ID	NAME	DEPT	SEX	EXPERIENCE	ID	BASIC	ALLOWANCE	CONSULT
101	John	ENT	M	12	101	12000	1000	300
104	Smith	ORRHPEDIC	M	5	104	23000	2300	500
107	George	CARDIOLOGY	M	10	107	32000	4000	500
114	Lara	SKIN	F	3	114	12000	5200	100
109	K George	MEDICINE	F	9	109	42000	1700	200
105	Johnson	ORRHPEDIC	M	10	105	18900	1690	300
117	Lucy	ENT	F	3	130	21700	2600	300
111	Bill	MEDICINE	F	12
130	Morphy	ORRHPEDIC	M	15

(a). Display NAME of all doctors who are in “MEDICINE”  having more than 10 years experience and basic more than 10000.

 (b). Display the average of all doctors working in “ENT” department using the DOCTOR and where as   salary=basic + allowance.

(c). Display the minimum ALLOWANCE of female doctors.

(d). Display the highest consultation fee among all male doctors for orthopedic dept

(e) Find out avg salary for each department.

f) Find out number of doctors in each department and display only those department where number of doctors are less than 2.

g)This table was created in the year 2010. Update the experience of all the doctors accordingly.

  29 Write the resulting output of the following :                                                                                          
   (a). Select  SUBSTR(‘NetBeans IDE Programmer’, 10,3);
   (b). select INSTR(TRIM(‘        ABS Public School                    ‘),’S’);
   (c).select ( ROUND ( 125.60,1) ;    d). select LEFT(‘RAMESH SHARMA’ , 5);   (e). select ROUND(1045.439 , 2) ;
   f) Select round(760,-2);  g)Select null +5;   (h). select Round(740,-2);

30 Create table “Employee” as per following table Instance Chart.                                                                                        

Column Name	EmpID	EmpName	EmpAddress	EmpPhone	EmpSal	DeptID
Key Type	Primary	Primary
Nulls/Unique		NOT NULL		unique
Data Typa	NUMBER	VARCHAR	VARCHAR	VARCHAR	VARCHAR	VARCHAR
Length	6	20	30	10	9,2	2