Sunday, 19 March 2017

JAVA VIVA QUESTIONS WITH ANSWERTS


Yes, a double value can be cast to a byte.



Java is a high-level programming language originally developed by Sun Microsystems and released in 1995. Java runs on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX.
Java runs on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX/Linux like HP-Unix, Sun Solaris, Redhat Linux, Ubuntu, CentOS, etc.
Some features include Object Oriented, Platform Independent, Robust, Interpreted, Multi-threaded
When Java is compiled, it is not compiled into platform specific machine, rather into platform independent byte code. This byte code is distributed over the web and interpreted by virtual Machine (JVM) on whichever platform it is being run.
Netbeans, Eclipse, etc.
Some Java keywords are import, super, finally, etc.
Object is a runtime entity and it’s state is stored in fields and behavior is shown via methods. Methods operate on an object's internal state and serve as the primary mechanism for object-to-object communication.
A class is a blue print from which individual objects are created. A class can contain fields and methods to describe the behavior of an object.
Variables defined inside methods, constructors or blocks are called local variables. The variable will be declared and initialized within the method and it will be destroyed when the method has completed.
Default value of byte datatype is 0.
Default value of float and double datatype in different as compared to C/C++. For float its 0.0f and for double it’s 0.0d
This data type is used to save space in large arrays, mainly in place of integers, since a byte is four times smaller than an int.
Java provides access modifiers to set access levels for classes, variables, methods and constructors. A member has package or default accessibility when no accessibility modifier is specified.
Postfix operators i.e () [] . is at the highest precedence.
Variables used in a switch statement can only be a string, enum, byte, short, int, or char.
This method is used to get the primitive data type of a certain String.
It is the process where one object acquires the properties of another. With the use of inheritance the information is made manageable in a hierarchical order.
Polymorphism is the ability of an object to take on many forms. The most common use of polymorphism in OOP occurs when a parent class reference is used to refer to a child class object.
It refers to the ability to make a class abstract in OOP. It helps to reduce the complexity and also improves the maintainability of the system.
These classes cannot be instantiated and are either partially implemented or not at all implemented. This class contains one or more abstract methods which are simply method declarations without a body.
If you want a class to contain a particular method but you want the actual implementation of that method to be determined by child classes, you can declare the method in the parent class as abstract.
It is the technique of making the fields in a class private and providing access to the fields via public methods. If a field is declared private, it cannot be accessed by anyone outside the class, thereby hiding the fields within the class. Therefore encapsulation is also referred to as data hiding.
The main benefit of encapsulation is the ability to modify our implemented code without breaking the code of others who use our code. With this Encapsulation gives maintainability, flexibility and extensibility to our code.
Java Runtime Environment is an implementation of the Java Virtual Machine which executes Java programs. It provides the minimum requirements for executing a Java application;
JAR files is Java Archive fles and it aggregates many files into one. It holds Java classes in a library. JAR files are built on ZIP file format and have .jar file extension.
It improves the runtime performance of computer programs based on bytecode.
Object based programming languages follow all the features of OOPs except Inheritance. JavaScript is an example of object based programming languages.
If a class has multiple functions by same name but different parameters, it is known as Method Overloading.
If a subclass provides a specific implementation of a method that is already provided by its parent class, it is known as Method Overriding.
Method overloading increases the readability of the program. Method overriding provides the specific implementation of the method that is already provided by its super class parameter must be different in case of overloading, parameter must be same in case of overriding.
Unicode requires 16 bits and ASCII require 7 bits. Although the ASCII character set uses only 7 bits, it is usually represented as 8 bits. UTF-8 represents characters using 8, 16, and 18 bit patterns. UTF-16 uses 16-bit and larger bit patterns.
These are classes that allow primitive types to be accessed as objects. Example: Integer, Character, Double, Boolean etc.
AWT components are heavy-weight, whereas Swing components are lightweight. Heavy weight components depend on the local windowing toolkit. For example, java.awt.Button is a heavy weight component, when it is running on the Java platform for Unix platform, it maps to a real Motif button.
A break statement results in the termination of the statement to which it applies (switch, for, do, or while). A continue statement is used to end the current loop iteration and return control to the loop statement.
The eight primitive types are byte, char, short, int, long, float, double, and boolean.

Advantage of OOPs over Procedure-oriented programming language

1)OOPs makes development and maintenance easier where as in Procedure-oriented programming language it is not easy to manage if code grows as project size grows.
2)OOPs provides data hiding whereas in Procedure-oriented programming language a global data can be accessed from anywhere.
3)OOPs provides ability to simulate real-world event much more effectively. We can provide the solution of real word problem if we are using the Object-Oriented Programming language.



What is difference between object-oriented programming language and object-based programming language?

Object based programming language follows all the features of OOPs except Inheritance. JavaScript and VBScript are examples of object based programming languages.

Java Naming conventions

Java naming convention is a rule to follow as you decide what to name your identifiers such as class, package, variable, constant, method etc.
But, it is not forced to follow. So, it is known as convention not rule.
All the classes, interfaces, packages, methods and fields of java programming language are given according to java naming convention.

Advantage of naming conventions in java

By using standard Java naming conventions, you make your code easier to read for yourself and for other programmers. Readability of Java program is very important. It indicates that less time is spent to figure out what the code does.
NameConvention
class name should start with uppercase letter and be a noun e.g. String, Color, Button, System, Thread etc.
interface nameshould start with uppercase letter and be an adjective e.g. Runnable, Remote, ActionListener etc.
method nameshould start with lowercase letter and be a verb e.g. actionPerformed(), main(), print(), println() etc.
variable nameshould start with lowercase letter e.g. firstName, orderNumber etc.

What is the purpose of this?
this is a keyword -it refers to the current object.
1.this: to invoke current class method
You may invoke the method of the current class by using the this keyword. If you don't use the this keyword, compiler automatically adds this keyword while invoking the method. Let's see the example
 this.setVisible(true);
2. Used with JOptionPane.showMessageDialog(this,"to bring the frame in the center of the screen");

what is the purpose of extends keyword?
The extends keyword indicates that you are making a new class that derives from an existing class. The meaning of "extends" is to increase the functionality.
Explain what do you depict from this diagram?

Java Inheritance Example

This indicates  inheritance , Programmer is the subclass and Employee is the superclass. Relationship between two classes is Programmer IS-A Employee.It means that Programmer is a type of Employee.
What is Method Overloading?

If a class has multiple methods having same name but different in parameters, it is known as Method Overloading
What is its advantage?

Advantage of method overloading

Method overloading increases the readability of the program.

What are the Different ways to overload the method

There are two ways to overload the method in java
  1. By changing number of arguments
  2. By changing the data type
Explain the given figure?

In the above diagram, byte can be promoted to short, int, long, float or double. The short datatype can be promoted to int,long,float or double. The char datatype can be promoted to int,long,float or double and so on.

What is final keyword?

Java final variable

If you make any variable as final, you cannot change the value of final variable(It will be constant). 


final int speedlimit=90;//final variable    
  speedlimit=400

Output:Compile Time Error

Explain encapsulation.

Encapsulation in Java

Encapsulation in java is a process of wrapping code and data together into a single unit, for example capsule i.e. mixed of several medicines.
We can create a fully encapsulated class in java by making all the data members of the class private. Now we can use setter and getter methods to set and get the data in i

eg. JTextField.
What is the output?
1. String s1="javatpoint";  
System.out.println(s1.substring(2,4));//returns va  
System.out.println(s1.substring(2));//returns vatpoint 

2.
String s1="javatpoint";  
String s2="python";  
System.out.println("string length is: "+s1.length());//10 is the length of javatpoint string  
System.out.println("string length is: "+s2.length());//6 is the length of python string 



 3.
String name="javatpoint";  
char ch=name.charAt(4);//returns the char value at the 4th index  
System.out.println(ch);  
4.
String s1="javatpoint";  
String s2="javatpoint";  
String s3="JAVATPOINT";  
String s4="python";  
System.out.println(s1.equals(s2));//true because content and case is same  
System.out.println(s1.equals(s3));//false because case is not same  
System.out.println(s1.equals(s4));//false because content is not same
5.
String s1="java string";  
s1.concat("is immutable");  
System.out.println(s1);  
s1=s1.concat(" is immutable so assign it explicitly");  
System.out.println(s1);  
6.
String s1="javatpoint is a very good website";  
String replaceString=s1.replace('a','e');//replaces all occurrences of 'a' to 'e'  
System.out.println(replaceString);  
}}  

jevetpoint is e very good website

 
 

Sunday, 5 February 2017

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