QUESTION ANSWERS ON IT APPLICATIONS

Q1. What is e-Governance? Ans: E-Governance is the use of a range of modern information and communication technologies such as internet, local area network, mobiles etc. by government to improve effectiveness of theirservices. Q2. What is e-Learning? Ans: E-Learning is a delivery of learning, training or education program by electronic means. Q3. What do you mean by E-Business? Ans: E-business is a term used to describe business run on the computer using modern IT practices. Q4. What are objectives of E- Governance? Ans: Objectives of E- Governance are:- • ImprovesGovernment processes. • Increases the efficiency and speed in a transparent manner. • Simplify administrative transactions. • Citizen can participate in decision making process. Q5. List the advantages of E Governance. Ans: Advantages are :- • Improved quality of information and information supply. • Reduction of process time. • Cost reduction. • Improved service level. • Increased efficiency. Q6. How E-learning is useful to learner. Ans: • It enables students to complete training conveniently at off-hours or from home. • Self pacing for slow and quick learners reduces stress and increased satisfaction. • Interactivity engage users, pushing them rather than pulling them through training etc. Q7. Why E-learning is preferred? Ans: E-learning is preferred because it provides faster learning at reduced cost, increased accessed to learning and clear accountability for all participants in the learning process. Q8. What are the importance of E-business? Ans: • Reductions in transactions and other cost. • Un-shortened supply chain. • Improved customer service. • Increased productivity/efficiency. • Access to international markets. Q9. What do you think e-learning courses have high dropout rate? Ans: the possible reasons for this are • Lack of interaction. • Lack of proper follow-up. Q10. Write three web portals of (1) e-commerce (2) e-Governance (3) e-Learning? Ans: e-Commerce e-Governance e-Learning www.irctc.co.in www.indiancourts.nic.in www.moodle.org www.ebay.in www.rti.gov.in www.w3schools.com www.amazon.com www.india.gov.in www.exelearning.org Q11. What is front end application? Ans: A "front-end" application interacts with the user and collects inputs from the user. Q12. What is back- end application? :: 95 :: Ans: A "back-end" application or program is not directly visible to the user but that processes the user requests as received by the front –end. For example databases like MySQL, Oracle, OOo Base, MS-Access etc. Q13 List the advantages of E Governance. Ans: Advantages are :-  Improved quality of information and information supply.  Reduction of process time.  Cost reduction  Improved service level  Increased efficiency. Q14. How E-learning is useful to learner. Ans: a. It enables students to complete training conveniently at off-hours or from home. b. Self pacing for slow and quick learners reduces stress and increased satisfaction. c. Interactivity engage users, pushing them rather than pulling them through training etc Q15. Why E-learning is preferred? Ans: E-learning is preferred because it provides faster learning at reduced cost, increased accessed to learning and clear accountability for all participants in the learning process. Q16.Mr. Kartik is working as a Manager in Flash Telecomm. He wants to create the forms with the following functions. Choose appropriate controls from Text Field, Label, Radio Button, Check Box, List Box, Combo Box, Command Button and write in the third column. SNo Control Used to Control 1 Enter Name of Customer 2 Enter Mobile No. 3 Select Connection Type 4 Display total Amount of Bill Ans: 1. Text Field 2. Text Field 3. Radio Button 4. Label Q17. Amarworks for the school. He wishes to create controls on a form for the following functions. Choose appropriate controls from Text Field, Text Area, Label, Option Button, Check Box, List Box, Combo Box, and Button. S.No Control used to Control 1 Enter Admission No 2 Enter Gender 3 Choose subjects from list of subjects 4 Clear the form Ans: 1. Text Field 2. Radio Button 3. Combo Box 4.Button Q 18. Anu works for a Hotel Sky wants to create controls on a form for the following function. Choose appropriate controls from TextField, Label, RadioButton, CheckBox, ListBox, ComboBox, Button and write in the third column. S.No. Controls use to Control 1 Select room type 2 Enter customers name 3 Select arrival date 4 To book a room Ans: 1.Radio Button 2. Text Field 3. Text Field 4.Button

IT APPLICATIONS

: IT Applications The modern e-technology changed our lives and facilitated to all the area of applications like Banking, Education, Entertainment and Government etc. Now days, Information & Communication Technology (ICT) enabled services are essential requirement of every walk of life. Some application area of modern IT practices are described belowE-GOVERNANCE: E-Governance refers to the application of e-Technologies in governance to provide effective, efficient, faster and transparent services to citizens. Example of such applications are Birth/Death Registration, issuing Passport, making of Voter ID Card, Filing Income Tax returns, Railway Enquiry & Ticket booking, On-line delivery of Land records etc Objective of E-Governance Applications  To provide information and knowledge about political system and services to the citizens.  To fulfill the requirement of citizens by providing services at minimal cost and at doorsteps.  To facilitate faster, transparent, accountable, efficient and reliable system for various administrative and social activities of the Government Social impacts of E-Governance:  Improvedtheefficiencyofadministrationandservicedelivery  Reducedwaitingtime  ReducedCost  Increasedpublicparticipation  Increasedtransparency Limitations of e-Governance:  People in rural and remote area could not get benefit from these E-Governance projects due to poor ICT infrastructure and lack of Awareness.  Unavailability of computers, Internet and lack of knowledge of computer operation has also prevented people to get benefit from E-Governance Applications.  Not all the services can be computerized and offered by the E-Governance Some E-Governance websites are: NameofWebsite Purpose www.incometaxindia.gov.in It Provides all the services of Income Tax department www.indiancourts.nic.in It provides information related to Supreme Court and High Courts of India. www.rti.gov.in Right to information Act 2005 mandates timely response to citizen requests for government information www.india.gov.in This portal givesthe information about Government of India and allows the users to apply online for various Government services. www.drdo.nic.in Defense Research and Development organization. E-Commerce: E-Commerce or E-Business refers to any form of business or trade that uses e-Technologies to facilitate the selling or buying of products and services, transactions of money and goods etc. Social impacts of E- Commerce:  Reductions in transactions and production costs.  Increase in the internet users.  Improved customer service (24x7 services).  Increased productivity/efficiency.  Access to international (Global) markets. :: 91 :: Limitations of e-Commerce:  Lack of knowledge and poor Telecommunication Infrastructure and unavailability of Internet connectivity in Rural areas are major issues for limited use of e-Business.  People are still unaware about legal remedies. There are high chances for fraud, theft of private information due to lack of effective legal framework. Some E-Business websites are: NameofWebsite Purpose www.irctc.co.in It provides online railway ticket reservation in India. www.ebay.in On line shopping mall providing sales and online auctions. www.amazon.com On line store for Books, CD’s, DVD’s, MP3’setc. www.yatra.com Online flight ticket booking service. E-LEARNING: E-Learning describe as a means of teaching and learning through e-technology like Internet and Multimedia enriched contents on CDROM or DVD. Social impacts of E- Learning:  Cost effective, Interactive and Multimedia enriched Material for effective and long-lasting learning.  No physical Interaction among Teacher and Learner is required i.e. most suited for working people.  Easily available and facilitates Any-time and Any-Where Learning. Limitations of e-Learning:  High dropout rate due to lack of interaction among Trainer and Trainee.  Sometimes less effective due to inappropriate learning contents.  It requires prior knowledge of Computer and other Technologies. Some E-learning websites are: NameofWebsite Purpose www.moodle.org It is Open source Course Management System(CMS) www.w3schools.com Online web tutorial. www.exelearning.org Open source application useful in publishing of web content. www.ncert.nic.in Interactive module for students to learn various topics. Development of IT Applications. The development of an IT application involves various activities. The following steps may be followed while development of IT Applications.  Study of Problem:The problem should be studied in terms of its best technical and economical feasible solution before development.  Design of Back-end:Decision to be made regarding database tables and their structure (data type and size etc.) to meet the requirement.  Design of Front-end:Decision to be made regarding Input to be taken from the user through Forms containing various controls like TextBox, RadioButtons etc. as per requirement and ease of use.  Establishment of Connection: Connection between front-end and Back-end is established.  Testing and Implementation:Finally, testing of Front-end and Back-end and their connectivity with sample data is carried out and Application is implemented, if testing is successful. Design of front-End:  The front-end is a face of any application which collects input or responses from the user and provides information to users using various Graphical components like Window, Text Boxes and Buttons etc.  Goodness of Front-end depends on its design, placement and appearance of contents, and connectivity to Back-end. :: 92 ::  Desirable Properties of a Good Front-End:  Consistency and Standard :Front-End should be consistent in look and operation. It should follow a standard design and convention for Window (frame), Menus, Dialog Boxes and Labels etc.  User Familiar and Good looking Design:Front-end should be user-familiar and always keeps user informed about what is going on. It should contain relevant information in visible font, size and color etc.  Error Prevention and Recovery:Front-end should be to identify and prevent data-entry errors and supports recovery in case of failure.  Flexibility and efficiency of use: Front-end should facilitate to Expert users as well as New users and supports customization as per user need.  Help Support and Documentation : A good front-end provide Help documentation for its operation and explains itself.  Commonly Used GUI Controls: The following controls or Window Gadget (Widget) are commonly used during design of front-end.  Frame : Used as a Basic Window or form.  Label: Allows Non-editable text or icon to displayed.  TextField: Allows user to input single-line editable text.  PasswordField: It is used to get some secret/encrypted text.  Button: An action is generated when pushed.  TextArea: Allows user to input multi-line editable text.  CheckBox: Allow user to select multiple choices.  RadioButton: They are option button which can be turned on or off. These are suitable for single selection.  List: Gives a list of items or choices from which user can select one or more items.  ComboBox: gives dropdown list of items or new item cab be added. It is combination of List and TextBox.  Guidelines regarding Selection and use of GUI Controls:  Make sure that the user provides appropriate information with minimum efforts. Maximize use of radio button, checkbox, combo box, and list.  Radio Button should be used wherever one of the option out of limited number of known set of options are required to be taken from the user. For example, for accepting gender (Male or Female), marital status (Single or Married), for accepting membership type (Monthly, Annual or Lifetime) etc.  Checkbox should be used wherever multiple options are required to be selected from a limited number of known set of options. For example, for accepting multiple hobbies (Swimming, Singing, Dancing, Debating), for accepting food order in a restaurant (Pizza, Burger, Channa Kulcha, Pao Bhaji, Chowmein) etc.  Combo box should be used wherever only one of the options from a large number of known set of options is required to be taken from the user. For example, selecting state, selecting marital status, selecting schools and selecting city etc.  List should be used wherever multiple options are required to be selected from a large number of known set of options. For example, selecting multiple food items from a menu containing five or more number of items.  Text Area should be used for multi-line text entries like Comment and hobbies etc. whereas Text Fields are suitable for single text.  Button should be placed at proper place with proper label. Button should be used for performing some action only.  Items which are used for similar purpose or objective should be visibly grouped used panels. :: 93 :: Design of Back-End:  Any IT Application usually stores a lot of data in the form of Database also called Back-end, that responds to the request of the front-end or user.  A database can be defined as a collection of interrelated data stored together to serve the applications. Basically it works as a computer based record keeping system.  A Database is managed by Database Management System (DBMS) which is responsible to maintain the database and facilitate users to insert, modify, delete and search (query) information stored in the database in the form of records.  A database may contain several tables to store records and avoids data redundancy.  A table may hold some constraints to ensure validity of data and Keys like Primary key and Foreign Keys etc.  Each table should contain proper names columns, data types and suitable size, so that data can be properly stored in the table. Relation between front-End and Back-end:

MYSQL SINGLE TABLE BASED QUERIES

Consider a database LOANS with the following table: Table: LOANS AccNo Cust_Name Loan_Amount Instalments Int_Rate Start_Date Interest 1 R.K.Gupta 300000 36 12.00 19-07-2009 1200 2 S.P.Sharma 500000 48 10.00 22-03-2008 1800 3 K.P.Jain 300000 36 NULL 08-03-2007 1600 4 M.P.Yadav 800000 60 10.00 06-12-2008 2250 5 S.P.Sinha 200000 36 12.50 03-01-2010 4500 6 P.Sharma 700000 60 12.50 05-06-2008 3500 7 K.S.Dhall 500000 48 NULL 05-03-2008 3800 Answer the following questions. Create Database anduse it- 1. Create the database LOANS. Mysql> Create Database LOANS; 2. Use the database LOANS. Mysql> Use LOANS; Create Table and Insert a record- 3. Create the table Loans and insert tuples in it. Mysql>Create Table Loans (AccNo int primary key, Cust_Name varchar(30), Loan_Amount int, Installment int, Int_Rate number(5,3), Start_Date date, Interest number(7,2)); Mysql> Insert into Loans values (1,'R.K. GUPTA',300000,36,12.0.'2009-07-19'); Simple Select 4. Display the details of all the loans. Mysql> Select * from Loans; 5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans. Mysql> Select AccNo,Cust_Name,Loan_Amount from Loan_Acc; Conditional Select using Where Clause 6. Display the details of all the loans with less than 40 instalments. Mysql> Select * from Loans where Instalment <40; 7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009. Mysql> Select AccNo, Loan_Amount from Loans where Start_Date <'2009-04-01'; 8. Display the Int_Rate of all the loans started after 01-04-2009. Mysql> Select Int_Rate from Loans where Start_date>'2009-04-01'; Using NULL 9. Display the details of all the loans whose rate of interest is NULL. Mysql> Select * from Loans where Int_rate is NULL; 10. Display the details of all the loans whose rate of interest is not NULL. Mysql> Select * from LOANS where Int_rate is not NULL; Using DISTINCT Clause 11. Display the amounts of various loans from the table LOANS. A loan amount should appear only once. Mysql> Select DISTINCT Loan_Amount from LOANS; 12. Display the number of installments of various loans from the table LOANS. An instalment :: 87 :: should appear only once.. Mysql> Select DISTINCT Instalment from LOANS; Using Logical Operators (NOT, AND, OR) 13. Display the details of all the loans started after 31-12-2008 for which the number of instalments are more than 36. Mysql> Select * from LOANS where Start_Date>'2008-12-31' and Instalment>36; 14. Display the Cust_Name and Loan_Amount for all the loans which do not have number of instalments 36. Mysql> Select Cust_Name, Loan_Amount from LOANS where Instalment <>36; 15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount is less than 500000 or int_rate is more than 12. Mysql> Select Cust_Name, Loan_Amount from LOANS where Loan_Amount <500000 or Int_rate>12; 16. Display the details of all the loans which started in the year 2009. Mysql> Select * from LOANS where Year(Start_Date)=2009; 17. Display the details of all the loans whose Loan_Amount is in the range 400000 to 500000. Mysql> Select * from LOANS where Loan_Amount between 400000 and 50000; 18. Display the details of all the loans whose rate of interest is in the range 11% to 12%. Mysql> Select * from LOANS where Int_Rate between 11 and 12; Using IN Operator 19. Display the Cust_Name and Loan_Amount for all the loans for which the number of installments are 24, 36, or 48.(Using IN operator) Mysql> Select Cust_Name, Loan_Amount from LOANS where Instalment IN(24,36,48); Using LIKE Operator 20. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name ends with 'Sharma'. Mysql> Select AccNo, Cust_name from LOANS where Cust_Name like '%Sharma'; 21. Display the AccNo, Cust_Name, and Loan_Amount for allthe loans for which the Cust_Name ends with 'a'. Mysql> Select AccNo, Cust_name,Loan_Amount from LOANS where Cust_Name like '%a'; 22. Display the AccNo, Cust_Name, and Loan_Amount for allthe loans for which the Cust_Namecontains 'a'. Mysql> Select AccNo, Cust_name,Loan_Amount from LOANS where Cust_Name like '%a%'; 23. Display the AccNo, Cust_Name, and Loan_Amount for allthe loans for which the Cust_Name does not contain 'P'. Mysql> Select AccNo, Cust_name,Loan_Amount from LOANS where NOT (Cust_Name like '%P%'); 24. Display the AccNo, Cust_Name, and Loan_Amount for allthe loans for which the Cust_Name contains 'a' as the second last character. Mysql> Select AccNo, Cust_name,Loan_Amount from LOANS where Cust_Name like '%a_'; :: 88 :: Using ORDER BY clause 25. Display the details of all the loans in the ascending order of their Loan_Amount. Mysql> Select * from LOANS ORDER BY Loan_Amount; 26. Display the details of all the loans in the descending order of their Start_Date. Mysql> Select * from LOANS ORDER BY Start_date DESC; 27. Display the details of all the loans in the ascending order of their Loan_Amount and within Loan_Amount in the descending order of their Start_Date. Mysql> Select * from LOANS ORDER BY Loan_Amount, Start_Date DESC; Using UPDATE, DELETE, ALTER TABLE 28. Put the interest rate 11.50% for all the loans for which interest rate is NULL. Mysql> Update LOANS SET Int_Rate =11.50 Where Int_Rate IS NULL: 29. Increase the interest rate by 0.5% for all the loans for which the loan amount is more than 400000. Mysql> Update LOANS SET Int_Rate= Int_Rate+0.5 Where Loan_Amount >400000; 30. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100. Mysql> Update LOANS SET Interest=(Loan_Amount*Int_Rate*Instalments) /12*100; 31. Delete the records of all the loans whose start date is before 2007. Mysql> Delete From LOANS Where Year(Start_Date)<2007; 32. Delete the records of all the loans of 'K.P. Jain' Mysql> Delete From LOANS Where Cust_Name='K.P.Jain'; 33. Add another column Category of type CHAR(1) in the Loan table. Mysql> Alter Table LOANS ADD (Category CHAR(1)); Using Aggregate Functions 34. Display the sum of all Loan Amount for whose Interest rate is greater than 10. Mysql> Select sum(Loan_Amount) from LOANS Where Interest >10; 35. Display the Maximum Interest from Loans table. Mysql> Select Max(Interest) from LOANS; 36. Display the count of all loan holders whose name is ending with ‘Sharma’. Mysql> Select Count(*) from LOANS Where Cust_Name Like ‘%Sharma’; 37. Display the count of all loan holders whose Interest is Null. Mysql> Select Count(*) from LOANS Where Interest Is NULL; Using Group By Clause 38. Display the Interest wise details of Loan Account Holders. Mysql> Select * from LOANS Group By Interest; 39. Display the Interest wise details of Loan Account Holders with at least 10 installments remaining. Mysql> Select * from LOANS Group By Interest Having Instalment>=10; 40. Display the Interest wise count of all loan holders whose Installment due is more than 5 in each group. Mysql> Select Count(*) from LOANS Group By Interest Having Instalment>5; Q23. Consider the following tables Item and Customer. Write SQL commands for the statement (1) to (4) and give outputs for SQL queries (5) to (6) :: 89 :: Table: ITEM ItemCode ItemName Price PC01 PC HP 35000 LC01 Laptop Sony 55000 PC02 PC ASUS 32000 PC03 PC HCL 37000 LC02 Laptop Toshiba 57000 Table: CUSTOMER CustCode CustName City ItemCode C01 N.Roy Delhi LC02 C06 H.Singh Mumbai PC03 C12 R.Pandey Delhi PC02 C15 C.Sharma Delhi LC01 C16 K.Agrawal Bangalore PC01 1. To display the details of those Customers whose city is Delhi and purchased ‘PC HP’ . 2. To display the details of Item which is purchased by custCode C15. 3. To display the Customer Code Customer Name, City and Item Name and Price for all sales. 4. To display the details of customer who purchased PC of any company. 5. SELECT CustName, City from Customer, Item Where Customer.ItemCode= Item.ItemCode And ItemName=”PC HP”; 6. SELECT CustName, City FROM Customer, Item Where Item.ItemCode= Customer.ItemCode And City=”Delhi”; Ans- 1. SELECT CustCode, CustName, City FROM Customer, Item Where Customer.ItemCode= Item.ItemCode And City=”Delhi” And ItemName=”PC HP”; 2. SELECT ItemCode, ItemName, Price FROM Customer, Item Where Customer.ItemCode= Item.ItemCode And CustCode=’C15’; 3. SELECT CustCode, CustName, City, ItemName, Price FROM Customer, Item Where Customer.ItemCode= Item.ItemCode ; 4. SELECT CustCode, CustName, City FROM Customer, Item Where Customer.ItemCode= Item.ItemCode And And ItemName LIKE ”PC%”; 5. CustName City K.Agrawal Bangalore 6. CustName City N.Roy Delhi R.Pandey Delhi C.Sharma Delhi

Create table “EMP” as per the following specification:

COLUMN NAME	DATATYPE	SIZE	CONSTRAINTS
Emp_No	Integer	4	Primary Key
Emp_Name	Varchar2	20	Not Null
Job	Varchar2	10
Hiredate	Date	dd-mm-yyyy
Salary	Float	10,2	Salary>10000
Commission	Integer	4
DeptNo	Integer	2	10,20 or 30

Job Types are:

·         President

·         Manager

·         Clerk

·         Salesman

Commission given only to ‘salesman’.

QUESTIONS

1)      Create the above table including its constraints.

2)      Insert 10 records into it.

3)      Display the name , job, salary of all employee having salary between 15000 and 25000

4)      Display the employee name , department and total salary calculated as (Salary +comm.) for all employees.

5)      Display the content of the table in the descending order of salary and ascending order of name.

6)      Display Emp_no , Emp_Name, annual salary under the heading “ANNUAL SALARY” where salary is calculated as (salary + commissim*12)

7)      Display the maximum salary from the table department wise.

8)      Display the name and job of all the employees whose name starts with ‘A’ and ends with ‘A’.

9)      Display the name i.e. Emp_Name, job and hiredate of the employee having job as manager, salesman or clerk.

10)   Count the no. of employee having salary greater than 15000.

11)   Create view DEPT20 with Emp_No, Emp_Name and salary of employee for department no. 20.

12)   To display the information of all the employees who were hired during year 1995.

13)   Show the average salary for all the departments with more than three people for a job.

14)   Write a SQL command to add the column named as remark with datatype variable length string (Varchar(20)).

15)   Increase the salary of all employees by 20% who were hired before 1^st Jan 1995 and belongs to job “ANALYST”.

MYSQL QUESTION ANSWERS

Very Short answers types questions 1. Which of the following will give the same answer irrespective of the NULL values in the specified column: a. MIN() b. MAX() c. SUM() d. None of the above 2. An aggregate function: a. Takes a column name as its arguments b. May take an expression as its argument c. Both (a) and (b) d. None of (a) and (b) 3. HAVING is used in conjunction with a. WHERE b. GROUP BY clause c. Aggregate functions d. None of the above 4. In the FROM clause of a SELECT statement a. Multiple Column Names are specified. b. Multiple table names are specified. c. Multiple Column Names may be specified. d. Multiple table names may be specified. 5. JOIN in RDBMS refers to a. Combination of multiple columns b. Combination of multiple rows c. Combination of multiple tables d. Combination of multiple databases 6. Equi-join is formed by equating a. Foreign key with Primary key b. Each row with all other rows c. Primary key with Primary key d. Two tables 7. Referential integrity a. Must be maintained b. Cannot be maintained c. Is automatically maintained by databases d. Should not be maintained 8. A Primary key column a. Can have NULL values b. Can have duplicate values c. Both (a) and (b) d. Neither (a) nor (b) 9. Primary Key of a table can be a. Defined at the time of table creation only. b. Defined after table creation only. c. Can be changed after table creation d. Cannot be changed after table creation 10. Two SELECT commands in a UNION a. Should select same number of columns. b. Should have different number of columns c. Both (a) and (b) d. Neither (a) nor (b) Answers : 1-c, 2-c, 3-b, 4-a, 5-c, 6-a, 7-a, 8-d, 9-a, 10-c :: 83 :: Q1. What do you mean by a Database Management System? Ans. Database Management is a collection of programs and files that allow a user to define structure of a database, store data into it, modify the structure and manipulate the data. Q2. What do you mean by Relational database? Ans. Relational Database is a type of database in which relation (table) is used to store data. Q3. What is a foreign key? Ans. A non-key attribute or column of a table whose value is derived from the primary key of another table. Q4. What is primary key? Ans. Primary key is a unique key in a relation which can uniquely identify a tuple (row) in a given relation. Q5. What is SQL? Ans. SQL is stands for structured query language. This language is used to create, manage table and manipulate stored records in a table. Q6. What is referential integrity? Ans. This is a rule which ensures that in DBMS relationships between records in related tables are valid. And that user don’t accidently delete or change related data. Q7. What is MySQL? Ans. MySQL is an open source RDBMS which uses SQL. Q8. What is DDL? Ans. DDL provides commands to define or redefine the schema of a table. Table is created, altered and dropped using DDL. Q9. What are DML commands? Ans- DML commands are used to manipulate data stored in a table.Insertion, deletion and modifications are possible using DML commands. Q11. What is null value in MySql? Ans-If a column in a row has no value, then column is said to be null. Q12. Which keyword eliminates redundant data in from a query result? Ans- DISTINCT Q13. How would you display system date as the result of a query? Ans- CURDATE() Q14. What is NOW() function in MySql? Ans- It returns the current date and time. Q15. What is NOT NULL constraint? Ans- NOT NULL constraints impose a condition that value of a row cannot be left blank. Q16. Identify the error? DELETE ALL FROM TABLE EMP; Ans:There is no need to write ALL and TABLE word in above query. Correct form is-DELETE FROM EMP; Q17. Differentiate WHERE and HAVING clause? Ans- Where clause is used to select particular rows that satisfy condition whereas having clause is used in connection with aggregate function, group by clause. Q 18. Why is it not allowed to give String and Date type arguments for SUM() and AVG() functions? Ans:String and dates are not real numbers that we calculate so sum() or avg() functions are not valid for them. Q 19. What is default, Autocommit mode in MySQL? Ans:By default, Autocommit mode is on in MySQL. :: 84 :: Q 20. Can where be added a savepoint in a transaction? Ans :We can add a savepoint anywhere in a transaction. Q 21. How are NULL values treated by aggregate functions? Ans:None of the aggregate functions takes NULL into consideration. NULL is simply ignored by all the aggregate functions. Q22. There is a column C1 in a table T1. The following two statements: SELECT COUNT(*) FROM T1; and SELECT COUNT(C1) from T1; are giving different outputs. What may be the possible reason? Ans :There may be a null value. Q23. What is the purpose of GROUP BY clause? Ans: GROUP BY: GROUP BY clause is used in a SELECT statement in conjunction with aggregate functions to group the result based on distinct values in a column. Q 24. What is the difference between HAVING and WHERE clauses? Explain with the help of an example. Ans:WHERE Vs HAVING: WHERE is used to put a condition on individual row of a table whereas HAVING is used to put condition on individual group formed by GROUP BY clause in a SELECT statement. Q24. What is a foreign key? What is its importance? Ans:Foreign Key: It is a column of a table which is the primary key of another table in the same database. It is used to enforce referential integrity of the data. Q 25. What are constraints? Are constraints useful or are they hindrances to effective management of databases? Ans:These are the rules which are applied on the columns of tables to ensure data integrity and consistency. These play very important role for tables so are not hindrances. Q26. In a database there is a table Cabinet. The data entry operator is not able to put NULL in a column of Cabinet? What may be the possible reason(s)? Ans :Not NULL or Primary key constraints used. Q 27. In a database there is a table Cabinet. The data entry operator is not able to put duplicate values in a column of Cabinet? What may be the possible reason(s)? Ans:Primary key constraint used. Q28. Do Primary Key column(s) of a table accept NULL values? Ans:No. Q29. What are the differences between DELETE and DROP commands of SQL? Ans:Delete is used for row removing while drop is used for removing complete table. Q 30. What is HAVING clause? Ans:HAVING clause is used in conjunction with GROUP BY clause in a SELECT statement to put condition on groups. Q31. What is Referential Integrity? Ans:The property of a relational database which ensures that no entry in a foreign key column of a table can be made unless it matches a primary key value in the corresponding column of the related table. Q32. What is Union used for? Ans:Union is an operation of combining the output of two SELECT statements. Q33.What is ALTER TABLE? Ans:ALTER TABLE command can be used to Add, Remove, and Modify columns of a table. It can also be used to add and Remove constraints. Q 34. What is DROP TABLE? Ans:DROP TABLE command is used to delete tables. Q35. What function is used whenever a condition involves an aggregate function? Ans:whenever a condition involves an aggregate function, then we use HAVING clause in conjunction with GROUP BY clause. :: 85 :: Short answers types questions Q1. How SQL commands are classified? Ans-SQL Commands are classified into three categories (i) Data Definition Language (DDL)-Commands that allow us to perform tasks related to data definition. E.g. creating, altering and dropping (ii) Data Manipulation Language (DML) - Commands that allows us to perform data manipulation e.g retrieval, insertion, and modification of data stored in a database. (iii) Transaction Control Language (TCL) - Commands that manages and controls the transactions. Q2. What is difference between char and varchar? Ans-The CHAR datatypes specifies a fixed length character string.When a column is given datatype as CHAR(n) then MySQL ensures that all values stored in that column have this length.But on other hand when a column is given datatype as VARCHAR(n) ,then the maximum size of a value in this column stores exactly what we specify. Q3. What do you understand by degree and cardinality of a relation in relational data base? Ans- The number of attributes in a relation is called Degree of arelation and number of records in a table is called cardinality in relational data base. Q4. What do you understand by the candidate key ? And- Candidate Key: All attribute combinations inside a relation that can serve as primary key(uniquely identifies a row in a relation) are Candidate Keys as they are candidates for the primary key position. Q 5. Why do understand by transaction COMMIT and ROLLBACK? Ans- COMMITing a transaction means all the steps of a transaction are carried out successfully and all data changes are made permanent in the database. Transaction ROLLBACK means transaction has not been finished completely and hence all data changes made by the transaction in the database if any, are undone and the database returns to the state as it was before this transaction execution started. Q 6. What do you understand by ACID properties of database transaction? Ans. -To ensure the data-integrity, the database system maintains the following properties of transaction. The properties given below are termed as ACID properties-an acronym derived from the first letter of each of the properties.  Atomicity - This property ensures that either all operations of the transactionsare reflected properly in the database, none are. Atomicity ensures either all-or- none operations of a transaction are carried out.  Consistency - This property ensures that database remains in a consistent state beforethe start of transaction and after the transaction is over.  Isolation - Isolation ensures that executing transaction execution in isolation i.e. is unaware ofother transactions executing concurrently in the system.  Durability - This property ensures that after the successful completion of a transaction i.e. when a transaction COMMITs, the changes made by it to the database are permanently persist. Q 7.What TCL commands are supported by SQL? Ans. -SQL supports following TCL commands • BEGIN Or START TRANSACTION-Marks the beginning of a transaction • COMMIT-Ends the current transaction by saving database changes and starts a newtransaction. • ROLLBACK-Ends the current transaction by discarding changes and starts a newtransaction. • SAVEPOINT-Defines breakpoints for the transactions to allow partial rollbacks. • SET AUTOCOMMIT-Enables or disable the default autocommit mode. Q8. What is transaction? How multiple transactions are executed? Ans. -A transaction is a logical unit of a work that must succeed or fail in its entirely. It is an atomic operation which can be divided unto smaller operations.Multiple transactions can be executed in one of the following two ways: Serial execution (one by one) or Concurrent execution.

TCL COMMANDS

Advanced Database Concepts What is Database Transaction ? In general, an event of access or modify the record from a database is called Database Transaction. A Transaction is a Logical Unit of Work (LUW) on the database that must succeed or fail entirely. A database transaction may contains several statement or commands but works as an atomic operation on the database. Properties of a Transaction (ACID Properties): A Transaction possesses the following ACID properties.  Atomicity: (All-or-None) A transaction is executed entirely or none. No any transaction is allowed to run partially.  Consistency: A transaction must leave the database in Consistent state after completion. No any transaction is allowed to leave the database in In-consistent state, because before execution it was in consistent state.  Integrity: Transaction is an atomic unit, it must be executed independently, no any other transaction should interfere during the execution of a transaction to avoid conflicts.  Durability: The changes made by the transaction are permanent in nature i.e. effect of a transaction is recorded permanently. Transaction: An ExampleA transaction may contain several commands like SELECT, DELETE and UPDATE etc. to perform a specific action (work) on the database. Suppose an amount of 1000/- is transferred from Ajay’ s account (AccountNo 1005) to Mohan’s Account (Account No 1102), in ACCOUNT table, then it can be represented asmysql> START TRANSACTION; mysql> UPDATE ACCOUNT SET Balance = Balance-1000 Where AccountNo=1005;// Ajay’s Balance is debited mysql> UPDATE ACCOUNT SET Balance = Balance+1000 Where AccountNo=1102;// Mohan’s Balance is credited Mysql> COMMIT; Transaction handling in MySQL: MySQL offers the following Transaction handling statements-  BEGIN / START TRANSACTION - Represents the start of a transaction.  COMMIT [Work] - Represents the end of a transaction. It saves all the changes on a database permanently.  SAVEPOINT -It creates a flag or mark during the execution of transaction which can be used to cancel the transaction partially, if required.  ROLLBACK [To SAVEPOINT ] - It cancels the effect of a transaction and restores the previous state of the database (works like UNDO operation). A partial rollback can be done using Save Points created in the transaction.  Set Autocommit -If Autocommit is Enabled , the changes are immediately saved after completion of the command, without using Commit command explicitly. mysql> Set Autocommit=1; (enables Autocommit feature) mysql> Set Autocommit=0; (disables Autocommit feature) :: 81 :: Save Points & Rollback: Save Points are flag or marker created during the execution of transaction which can be used to cancel the transaction partially. By using SAVEPOINTs you can devide the work of transaction into different segments. In case of failure, you can execute ROLLBACK to the save points only, leaving prior changes intact. Note that if ROLLBACKL is used without Savepoint then transaction is rollbacked upto the begin i.e no any changes will be made in database. You may discard the changes of all commands by using ROLLBACK. The Rollback with Savepoint facilitates partial rollback i.e. the changes up to defined Savepoint will be cancelled. Example : Autocommit & Rollback: By default, MySQL has autocommit on, which means if you do not start a transaction explicitly through a BEGIN or START TRANSACTION comm

MYSQL JOINING TABLES

Handling Two table Query- Join Query: Some times it is required to access the information from two or more tables, which requires the Joining of two or more tables. Such query is called Join Query. MySQL facilitates you to handle Join Queries. The major types of Join is as follows-  Cross Join (Cartesian Product)  Equi Join  Non-Equi Join  Natural Join :: 77 ::  Cross Join: Consider the two set A= {a,b} and B={1,2} The Cartesian Product i.e. AxB = {(a,1) (a,2) (b,1) (b,2)} Similarly, we may compute Cross Join of two tables by joining each record of first table with each record of second table.  Equvi Join: In Equvi Join, records are joined on the equality condition of Joining Column. Generally, the Join column is a column which is common in both tables. Consider the following table R and S having C as Join column.

Union of tables: Sometimes it is required to combine all records of two tables without having duplicate records. The combining records of two tables are called UNION of tables.UNION Operation is similar to UNION of Set Theory. E.g. If set A= {a,c,m,p,q} and Set B= {b,m,q,t,s} then AUB= {a,c,m,p,q,b,t,s} [All members of Set A and Set B are taken without repeating] The general form to write Union query isSelect …. From [Where ] UNION [ALL] Select …. From [Where ]; Example: Select Ename From PROJECT1 UNIONSelect Ename From PROJECT2 ;  Both tables or output of queries must be UNION compatible i.e. they must be same in column structure (number of columns and data types must be same). 

MYSQL FUNCTIONS

Aggregate Functions:  Some time it is required to apply a Select query in a group of records instead of whole table.  You can group records by using GROUP BY clause with Select command. A group column is chosen which have non-distinct (repeating) values like City, Job etc.  Generally, the following Aggregate Functions [MIN(), MAX(), SUM(), AVG(), COUNT()] etc. are applied on groups. SUM() Returns the sum of given column. MIN() Returns the minimum value in the given column. MAX() Returns the maximum value in the given column. AVG() Returns the Average value of the given column. COUNT() Returns the total number of values/ records as per given column. Example: mysql> Select Sum(Sal) from EMPLOYEE; mysql> Select Min(Sal) from EMPLOYEE; mysql> Select Max(Sal) from EMPLOYEE;  Aggregate Functions & NULL Values: Consider the following table EMP with some records. mysql> Select Sum(Sal) from EMP;  12000 mysql> Select Min(Sal) from EMP;  3500 mysql> Select Max(Sal) from EMP;  4500 mysql> Select Count(Sal) from EMP;  3 mysql> Select Avg(Sal) from EMP;  4000 mysql> Select Count(*) from EMP;  5 mysql> Select Count(Code) from EMP;  5 :: 76 ::  Aggregate Functions & Group (Group By Clause): An Aggregate function may applied on a column with DISTINCT or ALL keyword. If nothing is given, ALL scope is assumed.  Using SUM () This function returns the sum of values in given column or expression. mysql> Select Sum(Sal) from EMP; mysql> Select Sum(DISTINCT Sal) from EMP; mysql> Select Sum (Sal) from EMP where City=‘Kanpur’; mysql> Select Sum (Sal) from EMP Group By City; mysql> Select Job, Sum(Sal) from EMP Group By Job;  Using MIN () This function returns the Minimum value in the given column. mysql> Select Min(Sal) from EMP; mysql> Select Min(Sal) from EMP Group By City; mysql> Select Job, Min(Sal) from EMP Group By Job;  Using MAX () This function returns the Maximum value in given column. mysql> Select Max(Sal) from EMP; mysql> Select Max(Sal) from EMP where City=‘Kanpur’; mysql> Select Max(Sal) from EMP Group By City;  Using AVG () This function returns the Average value in the given column. mysql> Select AVG(Sal) from EMP; mysql> Select AVG(Sal) from EMP Group By City;  Using COUNT (<*|column>) This functions returns the number of rows in the given column. mysql> Select Count (*) from EMP; mysql> Select Count(Sal) from EMP Group By City; mysql> Select Count(*), Sum(Sal) from EMP Group By Job;  Aggregate Functions & Conditions on Groups (Having Clause): You may use any condition on group, if required. HAVING clause is used to apply a condition on a group. mysql> Select Job, Sum(Pay) from EMP Group By Job HAVING Sum(Pay)>=8000; mysql> Select Job, Sum(Pay) from EMP Group By Job HAVING Avg(Pay)>=7000; mysql> Select Job, Sum(Pay) from EMP Group By Job HAVING Count(*)>=5; mysql> Select Job, Min(Pay),Max(Pay), Avg(Pay) from EMP Group By Job HAVING Sum(Pay)>=8000; mysql> Select Job, Sum(Pay) from EMP Where City=‘Dehradun’ Group By Job HAVING Count(*)>=5; WHERE V/s HAVING: Where clause works in respect of whole table but Having works on Group only. If Where and Having both are used then Where will be executed first. WHERE is used to put a condition on individual row of a table whereas HAVING is used to put condition on individual group formed by GROUP BY clause in a SELECT statement.

CONSTRAINTS

Chapter 9: MySQL – Advanced  MySQL provides feature to maintain the validity and correctness of data being entered in the database using some predefined or user defined constraints. Later these constrains can be modified, if required.  Some time, it is required to make a query which retrieves records to form a group reports based on the given criteria and column.  You can also join two or more tables in a SELECT query using some joining criteria. In this chapter you will learn all these issues. Integrity Constraints: One of the major responsibility of a DBMS is to maintain the Integrity of the data i.e. Data being stored in the Database must be correct and valid.  An Integrity Constraints or Constraints are the rules, condition or checks applicable to a column or table which ensures the integrity or validity of data.  Most of the constraints are applied along with Column definition which are called Column-Level (inline Constraints),but some of them may be applied at column Level as well as Table-Level (Out-line constraints) i.e. after defining all the columns with CREATE TABLE command. Ex.- Primary Key & Foreign Key The following constraints are commonly used in MySQL. Constrains Role NOT NULL Ensures that a column cannot have NULL value. DEFAULT Provides a default value for a column, when nothing is given with INSERT command. UNIQUE Ensures that all values in a column are different. CHECK Ensures that all values in a column satisfy certain user defined condition. PRIMARY KEY Used to identify a row uniquely. FOREIGN KEY Used to ensure Referential Integrity of the data. UNIQUE v/s PRIMARY KEY  UNIQUE allows NULL values but PRIMERY KEY does not.  Multiple columns may have UNIQUE constraints, but there is only one PRIMERY KEY constraints in a table.  Implementing Primary Key Constraints:  Defining Primary Key at Column Level: mysql> CREATE TABLE Student ( StCode char(3) NOT NULL PRIMARY KEY, Stname char(20) NOT NULL, ………………………..);  Defining Primary Key at Table Level: mysql> CREATE TABLE Student ( StCode char(3) NOT NULL, Stname char(20) NOT NULL, ……………………….. PRIMARY KEY (StCode)); A Composite (multi-column) Primary key can be defined as only a Table level whereas Single-column Primary key can be defined in both way i.e. Column level or Table level. Example: mysql> CREATE TABLE Student (StCode char(3) NOT NULL PRIMARY KEY, Stname char(20) NOT NULL, StAdd varchar(40), AdmNo char(5) UNIQUE, StSex char(1) DEFAULT ‘M’, StAge integer CHECK (StAge>=5) ); :: 74 ::  Implementing Foreign Key Constraints:  A Foreign key is non-key column in a table whose value is derived from the Primary key of some other table.  Each time when record is inserted or updated in the table, the other table is referenced. This constraint is also called Referential Integrity Constraints.  This constraint requires two tables in which Reference table (having Primary key) called Parent table and table having Foreign key is called Child table. Let us take two table Employee and Department as per given columnsThe following command can be given to implement Foreign key constraintModifying Constraints: Some time it is required to modify the defined constraints after creating a table. ALTER command can be used to modify (adding/deleting of columns) Table structure as well as modifying constraints.  Adding new column and Constraints ALTER TABLE ADD[][] Example: mysql> ALTER TABLE Student ADD (TelNo Integer); mysql> ALTER TABLE Student ADD (Age Integer CHECK (Age>=5)); mysql> ALTER TABLE Emp ADD Sal Number(8,2) DEFAULT 5000 ; mysql> ALTER TABLE Emp ADD PRIMARY KEY (EmpID); mysql> ALTER TABLE Emp ADD PRIMARY KEY (Name,DOB);  Modifying Existing Column and Constraints ALTER TABLE
MODIFY[] [] Example: mysql> ALTER TABLE Student MODIFY Name VARCHAR(40); mysql> ALTER TABLE Emp MODIFY (Sal DEFAULT 4000 ); mysql> ALTER TABLE Emp MODIFY (EmpName NOT NULL);  Removing Column & Constraints ALTER TABLE
DROP | Example: mysql> ALTER TABLE Student DROP TelNo; mysql> ALTER TABLE Emp DROP JOB, DROP Pay; mysql> ALTER TABLE Student DROP PRIMARY KEY; :: 75 ::  Changing Column Name of Existing Column ALTER TABLE
CHANGE Example: mysql> ALTER TABLE Student CHANGE Name Stname Char(40);  To View the Constraints The following command will show all the details like columns definitions and constraints of EMP table. mysql> SHOW CREATE TABLE EMP; Alternatively you can use DESCribe command: mysql> DESC EMP;  Enabling / Disabling Foreign Key Constraint  You may enable or disable Foreign key constraints by setting the value of FOREIGN_KEY_CHECKS variable.  You can’t disable Primary key, however it can be dropped (deleted) by Alter Table… command.  To Disabling Foreign Key Constraint mysql> SET FOREIGN_KEY_CHECKS = 0;  To Enable Foreign Key Constraint mysql> SET FOREIGN_KEY_CHECKS = 1;

DDL COMMANDS

Creating and Opening Database: CREATE DATABASE ; Example: mysql> create database School; Now the database with the given name will be created. To open database give following command: mysql> use School;  Creating Tables Tables are defined with the CREATE TABLE command. When tables are created its columns are named,data types and sizes supplied for each column. At least one column must be specified. Syntax: CREATE TABLE (, ,…..….,); Example: mysql> CREATE TABLE Students(RollNo INTEGER(3), Name VARCHAR(25)); Once the table is created we can insert the record init, edit or delete existing records,and also we can search for desired record in a very comprehensive way using the SQL Select statement.  Creating tables with Constraints: A Constraint is a condition or check applicable to a column or table which ensures the integrity or validity of data. Constraints are also called Integrity constraints. The following constraints are commonly used in MySQL. Constraintsname Description PRIMARYKEY Used to create a primary key UNIQUE to create a unique key NOTNULL to define that column will not accept null values. CHECK to define the custom rule or condition. :: 69 ::  Not Null and Default constraints can be applied only at column level rest all constraints can be applied on both column level and table levels.  A table may have multiple UNIQUE constraints but only one PRIMARY KEY is allowed. Example: CREATE TABLE Student (StCode char(3) NOT NULL PRIMARY KEY, Stname char(20) NOT NULL, StAdd varchar(40), AdmNo char(5) UNIQUE, StSex char(1) DEFAULT ‘M’, StAge integer CHECK (StAge>=10), Stream char(1) CHECK Stream IN (‘S’, ‘C’, ‘A’) );

INTRODUCTION TO MYSQL

MySQL- MySQL is an Open Source RDBMS Software like Oracle, Sybase, DB2, MS SQL Server etc. that uses Structured Query Language. It was developed by Michael Widenius and AKA Monty. Features of MySQL :  Open Source and availableat freeofcost.  Fast and Reliable.  SupportsSQL (Structured Query Language), standardized by ANSI .  Portable and secure.  Can be used with various programming languages and platform.  Data Types in MySQL: -Every column in a table should belong to a unique domain (known as data type). These data types help to describe the kind of information a particular column holds. MySQL supports the ANSI SQL data types. Some of the commonly used data types along with their characteristics are as follows:  Numeric Data Types: INTEGER or INT – up to 11 digit number without decimal. SMALLINT – up to 5 digit number without decimal. DECIMAL(M,D) – Unpacked floating point up to M length and D decimal places.  Date & Time Data Types: DATE -A date in YYYY-MM-DD format. TIME - Stores time in HH:MM:SS format. DATETIME –A date and time format like YYYY-MM-DD HH:MM:SS  String or Text Data Type: CHAR(Size) - A fixed length string up to 255 characters.(default is 1) VARCHAR(Size) – A variable length string up to 255 characters. 

INTRODUCTION TO RDBMS

Chapter 8: RDBMS & MySQL – A Review  Database-A well organized collection of inter-related data that ensures safety, security and integrity of data is called Database. It works like is a container which contains the object like Tables, Queries, Reports, Procedures in organized way.  Database Management System (DBMS)-DBMS is aSoftware tool that provides the essential services to create, storing, maintain and utilize the databases in efficient way. They also provide security tools that ensure the safe accessibility of the data. Example of RDBMS software are- Oracle, MS SQL Server, MS Access, Paradox, DB2 and MySQL etc.  Advantages of Data Base Management System:  Database reduces Redundancy- Database removes duplication of data because data are kept at one place and all the application refers to the centrally maintained database.  Database controls Inconsistency- When two copies of the same data do not agree to each other, then it is called Inconsistency. By controlling redundancy, the inconsistency is also controlled.  Database facilitates Sharing of Data- Data stored in the database can be shared among several users.  Database ensures Security - Data are protected against accidental or intentional disclosure to unauthorized person or unauthorized modification.  Database maintains Integrity- It enforces certain integrity rules to insure the validity or correctness of data. For example a date can’t be entered like 25/25/2000.  Structure Query Language (SQL) - A non-procedural Query Language used for querying the database.  DDL (Data Definition Language) – These SQL command facilitates defining creation/modification etc. of database object such as tables and indexes etc. These commands are- CREATE, ALTER, DROP etc.  DML (Data Manipulation Language)-Type of SQL command, thatfacilitatesmanipulation of stored records like retrieval, additions, deletions and modification etc. Some commands are- SELECT, INSERT, DELETE & UPDATE etc.  TCL (Transaction Control Language)- A transaction is a one complete unit of work. To manage and control the transactions, the transaction control commands are used. e.g COMMIT, ROLLBACk etc.  Data Dictionary - A file containing facts/data about the data stored in table, generally it refers the structure of the table.  Data Model- Data model refers ‘ how data are organized and stored in the database’.Commonly used models are-  Relational Data Model - In this model data is organized into tables i.e. Rows and columns. These tables are called relations.  The Network Data Model - In this model data are represented by collection of records & relationships among data. The collections of records are connected to one another by means of links.  The Hierarchical Data Model - In this model records are organized as trees rather than arbitrary graphs.  Object Oriented Data Model - Data and associated operations are represented by objects. An object is an identifiable entity with some characteristics and behavior.  Relation –A table in Database, which contains data in the form of rows and columns, is called relation.  Domain - Pool of values from which the actual values are derived for a column in the table.  Tuple - A row of a relation/table is called record or Tuple.  Attribute - A column/Field of relation/table is called Attribute.  Degree - Number of columns in a table is called Degree of the table or relation.  Cardinality - Number of records/tuples stored in the table is called Cardinility.  View - Virtual table that does not really existin the database.  Primary Key - Set of one or more attributes/columns that can uniquely identify a record in the table.  Candidate Key - A Candidate Key is the one that is capable of becoming Primary key i.e., a field or :: 68 :: attribute that has unique value for each row in the relation and can act as a Primary key.  Alternate Key - A candidate key that is not primary key is called alternate key.  Foreign Key - A non-key attribute, whose values are derived from the primary key of some other table.  Integrity Constraints - Integrity Constraints are the rules that a database must comply all the times to ensure the correctness or validity of data.It determines what all changes are permissible to a database.

JAVA PROGRAMS

Write a java program to calculate the sum of all the no. divisible by 5 in the range 1 to 50. Ans: int sum=0; for(int i=1;i<=50;++i) { if(i%5==0) sum=sum+i; } jLabel1.setText(“”+sum); 2. What do you mean by infinite loop. Write one program that has infinite loop Ans: A loop that never terminates is called infinite loop. Example : for(;;) { jLabel1.setText(“Hello”);} 3. Write method in java that takes a number returns the sum of its digits. Ans int sumdig(int n) { int sum=0, r; while(n!=0) {int r=n%10; n=n/10; sum=sum+r; } return (sum); } 4. How many times, the following loop gets executed?
i=0; while(i>20) {//Statements } Ans: 0 times 5. How many times, the following loop gets executed? int i=0; do { //Statements }while(i>20); Ans: 1time Output Finding Questions 1. Write the output : (i) jTextField1.setText(“Hello”.charAt(3)); (ii) jTextField1.setText(“Good morning”.substring(4)); Ans: (i) l (ii) morning 2. Write the value stored on y variable after executing the following code : int x , y = 0; for(x=1;x<=5;++x) y=x++; Ans: 5 3. Find the output of the code: intf=1,i=2; do {f*=i; }while(++i<5); jTextField1.setText (“”+f); Ans: 24 4. What will be the value of j and k after execution of the following code: intj=10,k=12; if(k>=j) {k=j; J=k;} Ans: 10 10 5. What will be the contents of jTextield after executing the following statement: int num=4; num=num+1; if(num>5) jTextField1.setText(Integer.toString(num)); else jTextField1.setText(Integer.toString(num*4)); Ans: 20 6. Find the output of the following code: intFirst=7; intSecond=73; First++; if(First+Second>90) jlabel1.setText("valueis90"); else jlabel1.setText("valueisnot90"); Ans: value is not 90 :: 54 :: 7. Find the output int Number1=7,Number2=8; int Second=73; if(Number1>0||Number2>5) if(Number1>7) jTextField1.setText("CodeWorked"); else jTextField1.setText("CodeMightWork"); else jTextField1.setText("CodewillnotWork"); Ans: CodeMightWork 8. What will be the content of the jTextArea1 after executing the following code? intNum =1; do { jTextArea1.setText(Integer.toString(++Num)+"\n"); Num= Num + 1; }while(Num<=10); Ans: 10 9. What will be the contents of jTextfield1 and jTextfield2 after executing the following code: Strings=”KENDRIYAVIDYALAYA SANGATHAN” jTextField1.setText(s.length()+””); jTextField2.setText(Math.round(2.34)+“”); Ans: 282 10. Give the value of x after executing following Java code. int a=10,b=12,x=5,y=6; while(a<=b) { if(a%2==0) x=x+y; else x=x-y; a=a+1; } Ans: 11 11. What will be the output produced by following code fragment? flaot x=9, y=5; int z=(int)(x/y); switch(z) { case1: x=x+2; case2: x=x+3; default: x=x+1; } System.out.println(“valueof x:”+x); Ans: value of x: 15 12. What values will be assigned to the variable ua ,ub, uc and fail after execution of the following program segment: inti=0,ua=0,ub=0,uc=0,fail=0; while(i<=5){ switch(i++) { case1:++ua; case2:++ub; uc++; break; case3: case 4:++uc; :: 55 :: ua++; ub++; break; default:++fail; } Ans: ua=1 ub=1 uc=0 13. What will be the contents of jTextField1 and jTextField2 after executing the following code: Strings=“SunMicroSystems”; jTextField1.setText(s.length()+””); jTextField2.setText(s.toLowerCase()); Ans: jTextField1:17 jTextField2: sunmicrosystems 14. Give the output of the following code: int m=100; while(m>0) { if(m<10)break; m=m-10; } System.out.println(“mis”+m); Ans: m is 0 Errors finding and conversion questions: 1. Thefollowingcodehassomeerrors.Rewritethecorrectedcode. inti=2, j=5; whilej>i { jTextField1.getText(“jisgreater”); j--;++i; } JOptionPane.ShowMessageDialog(“Hello”); Ans: int i=2,j=5; while( j>i) { jTextField1.getText(“j is greater”); j--; ++i; } JOptionPane.showMessageDialog(“Hello”); 2. Rewrite the code after making correction. int sum; value; inct; intifor(i==0;i<=10;i++) sum=sum+i; inct++; Ans: int sum, value, inct; for(int i=0;i<=10;i++) sum=sum+i; inct++; 3. The following code has some error(s). Rewrite the correct code. inty=3; switch(y); {case1:System.out.print(“YesitsOne”); case>2:System.out.println(“YesitsmorethanTwo”); :: 56 :: break; case else: System.out.print(“InvalidNumber): } Ans: inty=3; switch(y) {case1:System.out.print(“YesitsOne”); break; case2:System.out.println(“YesitsmorethanTwo”); break; default: System.out.print(“InvalidNumber); } 4. Find out errors and rewrite the code: M=1;N=0; For(;m+n<19;++n) System.out.println(“hello”); M=m+10; Ans: m=1;n=0; for(;m+n<19;++n) System.out.println(“hello”); m=m+10; 5. Rewrite the following program code using a for loop: int i,sum=0; while(i<10) {sum+=i;i+=2; } Ans: inti, sum=0; for(i=0;i<10;i+=2) {sum+=i; } 6. Rewrite the following code using while loop : int i,j; for(i=1;i<=4;i++) {for(j=1;j<=i;++j) { System.out.print(j); } System.out.println(); } Ans: inti=1, j=0; while(i<=4) { j=1; while(j<=i) { System.out.print(j); ++j; } i++; System.out.println(); } :: 57 :: 7. Rewrite the following if-else segment using switch-case statement. charch='A'; if(ch=='A')System.out.println("Account"); if((ch=='C')||(ch=='G'))System.out.println("Admin"); if(ch=='F')System.out.println("Advisor"); Ans: charch='A';s witch(ch) { case‘A':System.out.println("Account");break; case'C': case'G’: System.out.println("Admin");break; case'F': System.out.println("Advisor"); } 8. Rewrite the following code using while loop: int i,j; for(i=1,j=2;i<=6;i++,j+=2) System.out.println(i++);System.out.println (“Finished!!!”); Ans: inti=1,j=2;whi le(i<=6) {System.out.println(i++); i++; j+=2;} System.out.println(“Finished!!!”); 9. Rewrite the following code using for loop. int i=0; while(++i<20) { if( i==8) break; System.out.println(i++); } Ans: inti; for (i=1;i<20;++i) { if( i==8)break; System.out.println(i++);} 10. Write the equivalent switch case for the following code : if(num1==1) jTextField1.setText(“Numberisone”); elseIf(num1==2) jTextField1.setText(“Numberistwo”); elseIf(num1==3) jTextField1.setText(“Numberisthree”); else jTextField1.setText(“Numberismorethanthree”); Ans: Switch(num1) { case1:jTextField1.setText(“Numberisone”);break; case2: jTextField1.setText(“Numberistwo”);break; :: 58 :: case3: jTextField1.setText(“Numberisthree”);break; default: jTextField1.setText(“Numberismorethanthree”); } 11. Write an alternative code(Using if)of given code that saves on number of comparisons. if(a==0) System.out.println(“zero”); if(a==1) System.out.println(“one”); if(a==2) System.out.println(“two”); if(a==3) System.out.println(“three”); Ans: if(a==0)System.out.println(“zero”); else if(a==1)System.out.println(“one”); else if(a==2)System.out.println(“two”); else if(a==3)System.out.println(“three”);

QUESTIN ANSWER ON WEB APPLICATIONS

Question Answers – Web Applications:
1. Identify the web browser software from the following options:
(a) Apache Web Server (b) MS Word (c) HTML (d) Mozilla Firefox
Ans. (d) Mozilla Firefox
2. A …………………… document is created by web server whenever a browser requests the documents.
(a) active (b) static (c) dynamic (d) none of the above
Ans. (c) Dynamic
3. A document is a fixed content document that is created by web server whenever a
browser requests the documents.
(a) active (b) static (c) dynamic (d) none of the above
Ans. (b) Static
4. Identify the web server software from the following options:
(a) Apache (b) MS Word (c) HTML (d) Mozilla Firefox
Ans. (a) Apache
5. The address of a resource on the net is known as:
(a) ISP (b) HTTP (c ) URL (d) WWW
Ans. (c) URL
6. A program that serves requested HTML files and pages.
(a) Web Address (b) Web Page (c ) Web Server (d) None of these
Ans. (c) Web Server
7. What is Uniform Resource Locator?
Ans: The uniform resource locator (URL) is the unique identifier of a web page. The address or URL of the
current page you are on appears in the "Address Bar" of the web browser. You can go directly to a web
page if you know its URL by simply typing the URL in the address bar. The most general form of a URL
:: 65 ::
syntax is as follows:
Protocol://domain name/<directory path>/<object name>
For example: http://www.openoffice.org/dev_docs/features/rc2.html
8. What is Web Server? Explain its main functions and also give examples.
Ans: Web server delivers (serves) content, such as web pages, using the Hypertext Transfer Protocol (HTTP),
over the World Wide Web. Web servers are computers on Internet on which Web pages are stored. It is
equipped with a program which listens request from the web client (Web Browser) and sends web
pages.The major functions of a web server are-
 Serving of Web pages on request of Browser.
 Controlling access and security of the server.
 Monitoring and logging server access statistics.
Some most popular Web Servers are Apache Web Server (Open Source software for Linux), MS
Internet Information Server (IIS) and Netscape Enterprise Web Server etc.
9. What is Web Browser? Explain its main functions and also give examples.
Ans: A web browser is a client that initiates communication by making a request for a specific resource. The
server then responds with the content of that resource, or an error message if unable to do provide the
contents due to any reason.
 It provides an interface to interact with Internet.
 It send request to Web Server for specified web page and displays at client machine.
 It also maintains History of visited Web Pages and may provide tools for easy web surfing.
Some commonly used Web Browsers are Internet Explorer, Mozilla FireFox, Google Chrome, Opera etc.
10. HTML tags must be written within:(a) <> (b) { } (c) [ ] (d) ( )
Ans: (a) <>
11. Explain the various elements of HTML?
HTML is made up of elements called Tags and Attributes, which specifies the format of the
documents.
 A Tag is a coded HTML command that indicates how parts of web page should be displayed.
 Tags are not case sensitive and contained within Angle Bracket <> i.e. <HTML> and <html> are same.
 Most of the Tags are used in pair i.e. begin and end of the Tag. End Tag are begins with /
character.
e.g. <Head> ………. </Head>
 An Attribute is a special word inside a Tag, which specifies additional information to Tags such as
colour, alignment etc.
 Most of the Attributes are followed by a Value (number or words).
e.g. <BODY BGColor = “RED”>
12. What are container and empty Tags?
Ans.These are two types of Tags are used in HTML.
Container Tags : These HTML Tag written in pair i.e. starting <..> as well as ending </…> .
Ex. <Title> My First Page < /Title>
Empty Tag : These Tags require just a starting tag and not ending tag.
Ex. <HR>, <BR><IMG > etc.
13. Give two differences between HTML and XML.
Ans: The three differences between HTML and XML are:
 HTML is designed to display data and hence, focused on the ‘look’ of the data, whereas XML is
designed to describe and carry dataandhence,focuses on‘whatdatais’.
 In HTML tags are predefined, while in XML, tags can be created as per needs.
 HTML tags are not case sensitive, whereas XML tags are case sensitive
14. What is an unordered list?
Ans: Bulleted/unordered list <UL> tag is used to indicate a list item as contained in an unordered or
bulleted form.
15. What is ordered list?
:: 66 ::
Ans: The numbered/ordered list <OL> tag is used to indicate a list item as contained in an ordered
or numbered form.
16. What is table? What are the basic commands for creating a table?
Ans: Table is a collection of rows and column.
Followings are important tags
<Table> :- used to give identification to a table
<TH> :- To provide headings in a table
<TR>:- (Table Row) to create Row in a table
<TD> :- (Table Data) to create columns in a row
17. What do you understand by ALINK? Explain with an example.
Ans: Links which are currently being visited in web page are known as Active Links (ALINK).
Example:
<BODY TEXT = “#FFFFFF” ALINK=”#FF0000”>
<A HREF=”www.kvsangathan.nic.in”> Kendriya Vidyalaya Sangathan </A><BR>
<A HREF = www.cbse.nic.in> Central Board of Secondary Education </A>
</BODY>
18. What is FORM tag? Explain with example.
Ans: To create or use forms in a web page <FORM> tag is used. Form is means to collect data from
the site visitor. It is done with the help of controls that collect data and send it over.
Example: <FORM method = “POST” action=submitform.asp>
19. What is INPUT tag? Explain with example.
Ans: Text boxes are single line text input controls that are created using <INPUT> tag whose TYPE
attribute has a value as “Text”. Example:
<FORM method = “POST” action=submitform.asp>
First Name:<INPUT TYPE=”text” NAME = “fname”/><BR>
Last Name:<INPUT TYPE=”text” NAME = “lname” />
</FORM>
20. What is the purpose of select tag?
Ans: <SELECT> tag is used to create a drop down box in which many options are available; user can make
selection from the list. Example:
<SELECT name = “stream”>
<OPTION value=”Science”> Science</OPTION>
<OPTION value=”Commerce”> Commerce </OPTION>
<option value=”Humanities”> Humanities </OPTION>
</SELECT>
21. What is XML?
Ans. - XML is eXtensible Markup Language which allows creating application specific structured document by
allowing creation of new tags. These structured documents can later be presented in humanunderstandable
manner in different ways.
22. Describe features of XML
Ans. - Features of XML:
 XML is designed to carry data not to display data.
 XML is self-descriptive. Tags are not pre-defined; rather they are created to describe the content in
appropriate manner.
 XML is free and extensible and XML is platform independent.
 XML can be used to create new languages, since it is a Meta language.
 XML is supported and recommended by World Wide Web Consortium (W3C)

QUESTION ANSWERS

Very Short answers types questions 1. Write command to display a message dialog to display prompt as “Hello World” , title as “My dialog” and icon as question icon. Ans: JOptionPane.showMessageDialog(null,”HelloWorld”,”My dialog”, ); 2. Name the different list type controls offered by Java Swing. Ans: (i) jListBox (ii) jComboBox 3. Name any two commonly used method of ListBox. Ans: getSelectedIndex() and getSelectedValue() 4. Write code to add an element (“New Course”) to a list (SubList) at the beginning of the list. Ans: SubList.add(0,”New Course”); 5. What is difference between ‘a’ and “a” ? Ans: ‘a’ is character and “a” is string. 6. How would you make a combo box editable? Ans: By setting its editable property to true. 7. Write the expression to print the value of a variable "x" of type int in a label. Ans: jLabel1.setText(“”+x); 8. In JDBC coding, what methods would be opted to move to last record of the recordSetrecSet? Ans: recSet.last(); 9. What is the name of event listener interface for action events ? Ans ActionPerformed 10. Name the inheritance type which is not supported by JAVA. Ans Multiple inheritance 11. What will be the value of jTextField1 after execution of following code : jTextField1.setText(“Informatics”.subString(2,6)); Ans: form 12. Name the character set supported by Java. Ans: Unicode. 16. What will be the value of y if x has 42 (1) y= ++x (2) y= x++ Ans: (1) 43 (2) 42 17. Name the 4 essential class libraries that we need to import for setting up the connection with the database and retrieve data from the database. Ans: DriverManager, Connection, Statement, ResultSet 18. What is Event? Ans. An Event refers to the occurrence of an activity. 19. What is Message? Ans. A Message is the information/request sent to the application. 20. Which Swing control is invisible on the Frame? And: ButtonGroup 21. Which property of list box is used to display values in the list? And: Model Property 22. How one can make a text field un-editable on a frame? And: jTextfield1.setEditable(false) 23. Which will be displayed in jTextArea after executing the following? jTextArea1.setText(“India \n is a great \t country”); And: India Is a great country 24. How one can make a text field un-editable on a frame? And: jTextfield1.setEditable(false) 25. Predefined classes are available in the form of …………. And: package :: 49 :: Short answers type questions Q1. How GUI application works? Ans. Graphical User Interface (GUI) based application contains Windows, Buttons, Text boxes, dialogue boxes and Menus etc. known as GUI components. While using a GUI application, when user performs an action, an Event is generated. Each time an Event occurs, it causes a Message which sent to OS to take action. Q2. What is a Method (Function)? Ans. A Method or function is sequence of statement which is written to perform a specific job in the application. In Object Oriented Programming, Method represents the behavior of the object. A message can also be thought as a call to an object’s method. Q3. What is Event? How the computer responds to an event? Ans. An event is occurrence of some activities either initiated by user or by the system. Hence Any user action related to the GUI is called an event, Almost all actions the user performs will ”trigger” an event for us to handle. For example:  Moving the mouse  Clicking on a button  Writing text in a text box etc. In order to react, you need to implement some Event handling system in your Application. Three things are important in Even HandlingEvent Source: It is the GUI component that generates the event, e.g. Button. Event Handler or Event Listener: It is implemented as in the form of code. It receives and handles events through Listener Interface. Event Object or Message: It is created when event occurs. It contains all the information about the event which includes Source of event and type of event Q4. How to use Event Handlers in NetBeans? Ans. As you attached an Event along with Listener, you will find a code window along with prototyped method to perform actions defined by you. You may write commands to be executed in //TODO section. You may define Action Event, Item Event, Mouse Event, Key Event and Mouse Motion Event to a button. Generally, ActionPerformed() Event is handled likejButton1.ActionPerformed( java.awt.event.ActionEvent evt) You can also rename the Event handler method. Q5. Explain Text Fields, List, Combo Box , check box and radio control in Java? Ans: 1. TextField Text Field allow the user to enter data as single line text or display text data to the user. Various things we can do with Text Field (Methods)  A text field can be ”enabled” or ”disabled” • Enabling a text field : (Enabled means - Data can be entered ) How to do that- jTextField1.setEditable(true); • Disabling a text field : (Disabled: Data can only be displayed) How to do that- jTextField1.setEditable(false);  Data can be written into or Read from Text Fields • Setting the text in a text field ( Show what you want to display) How to do that- jTextField1.setText("Welcome User!"); • Getting the text from a text field (Get values into some variable) How to do that- String s = theTextField.getText(); 2. List box / Combo box A list (or combo) box enables the user to choose an option between many alternatives • List box: User can only choose between specified alternatives :: 50 :: • Combo box: User can either choose between specified alternatives, or specify choice manually (by typing it in) Various things we can do with List/Combo Box (Methods)  A List/Combo box can be ”enabled” or ”disabled” like a Text Field • Enabling a List/Combo box How to do that - theBox.setEditable(true); • Disabling a List/Combo box How to do that - theBox.setEditable(false);  Items can be selected programatically or Selected items can be read from them • Setting the selection in a List/Combo box How to do that - theBox.setSelectedItem(”Three"); • Getting the selection from a List/Combo box How to do that - String s = (String)theBox.getSelectedItem(); 3. Check boxes/ Radio Buttons Unlike list/Check Boxes, in some cases, the set of possible choices is limited to two options, Often a case of either/or, or perhaps on/off – such case best suits check boxes or radio buttons A Check box can only be in two states; checked or unchecked, it is very Nice fit for binary choices Various things we can do with List/Combo Box (Methods)  A Check Box/Radio Button can be ”enabled” or ”disabled” like a Text Field • Enabling a Check box/Radio Button How to do that - theRCBox.setEnabled(true); • Disabling a Check box/Radio Button How to do that - theRCBox.setEnabled(false);  Items can be selected programatically or Selected items can be read from them • Setting the selection in a Check box/Radio Button How to do that - theRCBox.setSelected(isSelected); • Getting the selection from a Check box/Radio Button How to do that - boolean isSelected = theCBox.isSelected(); Q6. What is a variable? Explain with example. Ans. A variable is named memory location, which holds a data value of a particular data type. Declaration and Initialization of variable- ; Example: int age; double amount; double price=214.70, discount =0.12; String name=“Amitabh” long x=25L; byte a=3; float x= a+b; By default all Numeric variables initialized with 0, and character and reference variable with null, boolean with false, if it is not initialized. The keyword final can be used with variable declaration to indicate constant. E.g. final double SERVICE_TAX=0.020 Q7. What do you mean by parsing ? Ans: The Parsing refers to converts textual data from GUI component in to numeric type. Byte.parseByte(String s) – string into byte. Short.parseShort(String s) – string into short. Integer.parseInt(string s) – string into integer. Long.parseLong(string s) – string into long. Float.parseFloat(string s) – string into float. Double.parseDouble(string s) – string into double. e.g. int age=Integer.parseInt(jTextField1.getText());

Q8. How to Display Dialogue Boxes in JAVA GUIs (Netbeans)? Ans: In GUI application often we require to display a message in the Dialog Boxes containing OK button to close the Dialog Box. The following steps can be used to display a message in a dialog box. Firstly, you need to import jOptionPane swing control at the top of program code, by typing – import javax.swing.JOptionPane; When required you may display a message by following code in a methodJOptionPane.showMessageDialog(null, “Hello.. “); Q9. Explain the concept of Focus. Ans: Focus is the ability to receive user input/ response through Mouse or Keyboard. When object or control has focus, it can receive input from user.An object or control can receive focus only if its enabled and visible property is set to true. Most of the controls provides FOCUS_GAINED() and FOCUS_LOST() method in FocusEvent by the FocusListener. FOCUS_LOST() is generally used for validation of data. You can give focus to an object at run time by invoking the requestFocus() method in the code. Example : jTextField1.requestFocus(); - This method puts focus on jTextField1. Q10. What is an expression? What are the various types of expressions in Java? Ans: An expression is a valid combination of operators, constants and variable and keywords i.e. combination of Java tokens. In java, three types of expressions are used. Arithmetic Expression: Arithmetic expression may contain one or more numeric variables, literals and operators. Two operands or operators should not occur in continuation. e.g. x+*y and q(a+b-z/4) is invalid expressions. Compound Expression: It is combination of two or more simple expressions. e.g. (a+b)/(c+d) and (a>b)||(by (y+z)>=(x/z) x||y && z (x) (x-y) Q11. What is ‘Scope’ of a variable? Explain. Ans: In Java, a variable can be declared anywhere in the program but before using them. The area of program within which a variable is accessible, is known as its scope. A variable can be accessed within the block where it is declared. Q12. What are Access Specifiers ? How Access is controlled for members of Super class? Ans. Access specifier tells a complier about the usability of a data member of a class in a java program. Java supports basically three types of access specifier, however there are some others too, these are: Public, Private, Protected, and further - default and private protected. • Public: A Class member with public access specifier is usable outside the class. i.e. it can be used in any class in the program. • Protected: A class member with protected access specifier can be inherited by a child class but is not usable outside the parent class. • Private: Private members of a class can just be utilized inside the class and are hidden outside the class a private member cannot be used in any other class other than the class in which it is declared. • Default: These members are accessible only in the class that are in the same package class i.e., in their own classes. • Private Protected: These members are accessible only from subclasses whether in the same package or in the other package. Q13. What is casting? When do we need it? Ans: Casting is a conversion, which uses the cast operator to specify the type name in parenthesis and is placed in front of the value to be converted. For example: Result = (float) total / count ; They are helpful in situations where we temporarily need to treat a value as another type. Q14. What is the purpose of break statement in a loop? Ans: In a loop, the break statement terminates the loop when it gets executed. :: 52 :: Q15. How is the if…else if combination more general than a switch statement? Ans: The switch statement must be by a single integer control variable, and each case section must correspond to a single constant value for the variable. The if…else if combination allows any kind of condition after each if. Q16. What is a container component? Ans: A container is a special type of component that can hold other components. Some Swing Containers are jPanel, jFrame, jApplet, jWindow, jDialog and jInternalFrame. The components contained in a container are called child component. Q17. How are protected members different from public and private members of a class? Ans: Protected members can be directly accessed by all the classes in the same package, as that of the class in which the member is and sub classes of other package. Whereas private members cann ot be accessed outside the class, even in subclasses of the class and public members can be directly accessed by all other classes. Q18. What is an abstract class and abstract method? Ans: An Abstract Class is the one that simply represents a concept and whose objects can’t be created. It is created through the use of keyword abstract. Abstract methods are methods with no method statements. Subclasses must provide the method statements for the inherited abstract methods e.g. in the following code class. Q19. Differentiate between JDBC and ODBC? Ans: JDBC (Java Database Connectivity) is developed by Sun Java for the purpose of connecting java applications with a variety of relation database systems like MySQL or Oracle. On the other hand, ODBC (open database connectivity) is a system developed by Microsoft to connect Microsoft based programming application (like visual basic) with a variety of relation databases. Q20. What are the main tasks of JDBC? Ans: Mainly JDBC perform the following: a) Establishes a connection with a relation database b) Sends SQL queries/ statements to the database c) Process the results obtained from the database server. Programming Problems 1. Write a java program to calculate the sum of all the no. divisible by 5 in the range 1 to 50. Ans: int sum=0; for(int i=1;i<=50;++i) { if(i%5==0) sum=sum+i; } jLabel1.setText(“”+sum); 2. What do you mean by infinite loop. Write one program that has infinite loop Ans: A loop that never terminates is called infinite loop. Example : for(;;) { jLabel1.setText(“Hello”);} 3. Write method in java that takes a number returns the sum of its digits. Ans int sumdig(int n) { int sum=0, r; while(n!=0) {int r=n%10; n=n/10; sum=sum+r; } return (sum); } 4. How many times, the following loop gets executed? :: 53 :: i=0; while(i>20) {//Statements } Ans: 0 times 5. How many times, the following loop gets executed? int i=0; do { //Statements }while(i>20); Ans: 1time Output Finding Questions 1. Write the output : (i) jTextField1.setText(“Hello”.charAt(3)); (ii) jTextField1.setText(“Good morning”.substring(4)); Ans: (i) l (ii) morning 2. Write the value stored on y variable after executing the following code : int x , y = 0; for(x=1;x<=5;++x) y=x++; Ans: 5 3. Find the output of the code: intf=1,i=2; do {f*=i; }while(++i<5); jTextField1.setText (“”+f); Ans: 24 4. What will be the value of j and k after execution of the following code: intj=10,k=12; if(k>=j) {k=j; J=k;} Ans: 10 10 5. What will be the contents of jTextield after executing the following statement: int num=4; num=num+1; if(num>5) jTextField1.setText(Integer.toString(num)); else jTextField1.setText(Integer.toString(num*4)); Ans: 20 6. Find the output of the following code: intFirst=7; intSecond=73; First++; if(First+Second>90) jlabel1.setText("valueis90"); else jlabel1.setText("valueisnot90"); Ans: value is not 90 :: 54 :: 7. Find the output int Number1=7,Number2=8; int Second=73; if(Number1>0||Number2>5) if(Number1>7) jTextField1.setText("CodeWorked"); else jTextField1.setText("CodeMightWork"); else jTextField1.setText("CodewillnotWork"); Ans: CodeMightWork 8. What will be the content of the jTextArea1 after executing the following code? intNum =1; do { jTextArea1.setText(Integer.toString(++Num)+"\n"); Num= Num + 1; }while(Num<=10);

Ans: 10 9. What will be the contents of jTextfield1 and jTextfield2 after executing the following code: Strings=”KENDRIYAVIDYALAYA SANGATHAN” jTextField1.setText(s.length()+””); jTextField2.setText(Math.round(2.34)+“”); Ans: 282 10. Give the value of x after executing following Java code. int a=10,b=12,x=5,y=6; while(a<=b) { if(a%2==0) x=x+y; else x=x-y; a=a+1; } Ans: 11 11. What will be the output produced by following code fragment? flaot x=9, y=5; int z=(int)(x/y); switch(z) { case1: x=x+2; case2: x=x+3; default: x=x+1; } System.out.println(“valueof x:”+x); Ans: value of x: 15 12. What values will be assigned to the variable ua ,ub, uc and fail after execution of the following program segment: inti=0,ua=0,ub=0,uc=0,fail=0; while(i<=5){ switch(i++) { case1:++ua; case2:++ub; uc++; break; case3: case 4:++uc; :: 55 :: ua++; ub++; break; default:++fail; } Ans: ua=1 ub=1 uc=0 13. What will be the contents of jTextField1 and jTextField2 after executing the following code: Strings=“SunMicroSystems”; jTextField1.setText(s.length()+””); jTextField2.setText(s.toLowerCase()); Ans: jTextField1:17 jTextField2: sunmicrosystems 14. Give the output of the following code: int m=100; while(m>0) { if(m<10)break; m=m-10; } System.out.println(“mis”+m); Ans: m is 0 Errors finding and conversion questions: 1. Thefollowingcodehassomeerrors.Rewritethecorrectedcode. inti=2, j=5; whilej>i { jTextField1.getText(“jisgreater”); j--;++i; } JOptionPane.ShowMessageDialog(“Hello”); Ans: int i=2,j=5; while( j>i) { jTextField1.getText(“j is greater”); j--; ++i; } JOptionPane.showMessageDialog(“Hello”); 2. Rewrite the code after making correction. int sum; value; inct; intifor(i==0;i<=10;i++) sum=sum+i; inct++; Ans: int sum, value, inct; for(int i=0;i<=10;i++) sum=sum+i; inct++; 3. The following code has some error(s). Rewrite the correct code. inty=3; switch(y); {case1:System.out.print(“YesitsOne”); case>2:System.out.println(“YesitsmorethanTwo”);
break; case else: System.out.print(“InvalidNumber): } Ans: inty=3; switch(y) {case1:System.out.print(“YesitsOne”); break; case2:System.out.println(“YesitsmorethanTwo”); break; default: System.out.print(“InvalidNumber); } 4. Find out errors and rewrite the code: M=1;N=0; For(;m+n<19;++n) System.out.println(“hello”); M=m+10; Ans: m=1;n=0; for(;m+n<19;++n) System.out.println(“hello”); m=m+10; 5. Rewrite the following program code using a for loop: int i,sum=0; while(i<10) {sum+=i;i+=2; } Ans: inti, sum=0; for(i=0;i<10;i+=2) {sum+=i; } 6. Rewrite the following code using while loop : int i,j; for(i=1;i<=4;i++) {for(j=1;j<=i;++j) { System.out.print(j); } System.out.println(); } Ans: inti=1, j=0; while(i<=4) { j=1; while(j<=i) { System.out.print(j); ++j; } i++; System.out.println(); } :: 57 :: 7. Rewrite the following if-else segment using switch-case statement. charch='A'; if(ch=='A')System.out.println("Account"); if((ch=='C')||(ch=='G'))System.out.println("Admin"); if(ch=='F')System.out.println("Advisor"); Ans: charch='A';s witch(ch) { case‘A':System.out.println("Account");break; case'C': case'G’: System.out.println("Admin");break; case'F': System.out.println("Advisor"); } 8. Rewrite the following code using while loop: int i,j; for(i=1,j=2;i<=6;i++,j+=2) System.out.println(i++);System.out.println (“Finished!!!”); Ans: inti=1,j=2;whi le(i<=6) {System.out.println(i++); i++; j+=2;} System.out.println(“Finished!!!”); 9. Rewrite the following code using for loop. int i=0; while(++i<20) { if( i==8) break; System.out.println(i++); } Ans: inti; for (i=1;i<20;++i) { if( i==8)break; System.out.println(i++);} 10. Write the equivalent switch case for the following code : if(num1==1) jTextField1.setText(“Numberisone”); elseIf(num1==2) jTextField1.setText(“Numberistwo”); elseIf(num1==3) jTextField1.setText(“Numberisthree”); else jTextField1.setText(“Numberismorethanthree”); Ans: Switch(num1) { case1:jTextField1.setText(“Numberisone”);break; case2: jTextField1.setText(“Numberistwo”);break;

case3: jTextField1.setText(“Numberisthree”);break; default: jTextField1.setText(“Numberismorethanthree”); } 11. Write an alternative code(Using if)of given code that saves on number of comparisons. if(a==0) System.out.println(“zero”); if(a==1) System.out.println(“one”); if(a==2) System.out.println(“two”); if(a==3) System.out.println(“three”); Ans: if(a==0)System.out.println(“zero”); else if(a==1)System.out.println(“one”); else if(a==2)System.out.println(“two”); else if(a==3)System.out.println(“three”); Application Design Questions: Q1: Design an application for Theatre Booking system and answer the following questions?
What is the output
int a = 10; int b = 20; int c = 25; int d = 25; System.out.println("a + b = " + (a + b) ); System.out.println("a - b = " + (a - b) ); System.out.println("a * b = " + (a * b) ); System.out.println("b / a = " + (b / a) ); System.out.println("b % a = " + (b % a) ); System.out.println("c % a = " + (c % a) ); System.out.println("a++ = " + (a++) ); System.out.println("b-- = " + (a--) );

int a = 10; int b = 20; System.out.println("a == b = " + (a == b) ); System.out.println("a != b = " + (a != b) ); System.out.println("a > b = " + (a > b) ); System.out.println("a < b = " + (a < b) ); System.out.println("b >= a = " + (b >= a) ); System.out.println("b <= a = " + (b <= a) );

boolean a = true; boolean b = false; System.out.println("a && b = " + (a&&b)); System.out.println("a || b = " + (a||b) ); System.out.println("!(a && b) = " + !(a && b));

int a = 10; int b = 20; int c = 0; c = a + b; System.out.println("c = a + b = " + c ); c += a ; System.out.println("c += a = " + c ); c -= a ; System.out.println("c -= a = " + c ); c *= a ; System.out.println("c *= a = " + c );

a = 10; c = 15; c /= a ; System.out.println("c /= a = " + c ); a = 10; c = 15; c %= a ; System.out.println("c %= a = " + c ); c <<= 2 ; System.out.println("c <<= 2 = " + c );


int x = 30; int y = 10; if( x == 30 ){ if( y == 10 ){ System.out.print("X = 30 and Y = 10"); }