COMPUTER NETWORKING
Network: A computer network is a collection of interconnected computers. Two computers are
said to be interconnected if they are capable of sharing hardware, software and exchanging
information.
Need or advantages for Networking :
1. Resource sharing
2. Reliability
3. Cost factor
4. Communication medium.
5. Central Storage of data
Application of Network:
1. Sharing
2. Access to remote database
3. Communication facilities
Terminology of Network:
Nodes: Nodes refer to the computers that are attached to a network that can share resources of
the network.
Server: A computer that facilitates the sharing of data, hardware and software on the network is
known as server. Each server has unique name on the network. A server can be of two types:
(i) Dedicated server (ii) Non dedicated server
Domain Name Vs URL: Domain name is unique name assigned to a web site. The URL is the
complete address of web page on the website. e. g. URL: http:// www.cbse.nic.in/index.html Here
http is protocol and www.cbse.nic.in is domain name.
Domain Name Resolution: Domain Name resolution is the process of getting corresponding IP
address from a domain name.
Network Interface Unit (NIU): A network interface unit is an interpreter that helps to establish a
communication between the server and the client.
MAC Address: It refers to the physical address assigned by the NIC manufacturer. A MAC address is a 6 byte address with each byte separated by a colon. For Example: 20:B5:03:63:2E: FC (The first three bytes refer to manufacturer ID and last three card no.)
IP address: Every machine on a TCP/IP network has a unique identifying number, called IP address: For Example: 202.27.94.137 (also known as Internet Protocol address)
Transmission/ Communication Media
The communication channels which are used for actual physical transmission of data are known
as transmission media. The transmission media is of two types: wired and wireless.
Wired Media (Guided Media):
It consists of physical wired medium to carry signals.
Twisted pair cable: A twisted pair cable consists of minimum two insulated copper wires of
about 1 mm thickness which are twisted together in a helical form. e.g. Telephone wire. LAN
Cable.
Advantages:
1. It is easy to install and maintain
2. It is very cheap (Inexpensive)
Disadvantages:
1. It is not efficient in transmitting data for very long distance without a repeater.
2. Low bandwidth.
Co-Axial Cable: It is a stiff copper wire, surrounded by insulating material. E.g. cable TV wire
Advantages:
1. High band width (data transmission is better than twisted pair cable)
2. Can be used for transmitting data over long distance.
Disadvantages:
1. Expensive than twisted pair cable
2. It is not compatible with Twister pair cable.
Optical Fiber: Optical fiber cables are made up of a glass core through which data is
transmitted in the form of light signals.
Advantages:
1. Transmit data over long distance with security and high bandwidth.
2. Immune to noise/external interference.
Disadvantages:
1. Very expensive and quite fragile (breakable).
2. Connecting two optical fibers is a difficult process.
Wireless Media (Unguided Media)
It consists of electromagnetic waves which carry signals
a) Blue Tooth: Bluetooth is a wireless technology for creating personal networks operating
within a range of 10 meters.
b) Infrared: The type of transmission that uses infrared light to send the data is known as
infrared transmission. e.g.: Remote control of TV and AC.
Advantages:
1. It is a secure medium of transmitting data.
2. It is a cheap mode of transmission.
Disadvantages:
1. It can work for short distance.
2. It cannot go through walls and affected by distance, noise and heat.
c) Microwave: Microwaves are high energy radio waves that are used for line of sight
communication.
Advantages:
1. Suitable for high speed, long distance and overseas communication.
2. No need for laying cable.
Disadvantages:
1. Micro wave communication is an insecure communication.
2. Microwave propagation of waves is susceptible to weather effects like Rain & thunder
3. Only straight line transmission is possible.
d) Radio wave/Radio Link
The transmission making use of radio frequencies is termed as radio wave transmission.
Advantages:
1. It is suitable for long distance and can be used for indoors and outdoors.
2. Radio waves can travel in any direction (omnidirectional).
Disadvantages:
1. Radio waves communication is insecure communication.
2. Radio waves propagation is susceptible to weather effects like rain, thunderstorm.
e) Satellite Link: The satellite transmission is also a kind of line of sight transmission that is
used to transmit signals throughout the world. Services like DTH cable TV, VSAT, GPS and
Satellite phones etc. are offered by the satellite.
Advantages:
1. It can cover large area of earth.
2. Satellite proves best alternate where the laying out of cable is difficult and
expensive.
Disadvantages:
1. High cost (very expensive) and complex Installation.
2. Signals sent to the earth can be tampered by external interference.
Network devices
Modem: MOdulator-DEModulator is a device that connects telephone line to computer. It
converts digital signal to analog signal and vice versa.
Hub: Hub is a hardware device used to connect several computers together to form a Local Area
Network. Hub broadcasts the message to the entire network.
Switch: Switch is an intelligent Hub, which is used to segment networks into different sub network
called subnets. Switch is faster and efficient over Hub due to good traffic management capability.
Repeater: A repeater is a device that amplifies signals transmitted on the network. It is used to
extend a network beyond the segment length of the wire used.
Router: The device which connects two similar networks and can handle different protocols.
Gateway: It is a device that connects dissimilar networks.
Bridge: A device that connects two similar networks.
Types of Network
There are mainly four types of network:
Local area Network (LAN): Small computer network that is confined to a local area, office or within a building are known as LAN. E.g. network within school campus.
Metropolitan Area Network (MAN): Metropolitan area network is the network spread over a city or nearby corporate offices. For example, a cable TV network.
Wide Area Network (WAN): This type of network spreads over large geographical area across
countries and continents. WANs are generally used to interconnect several other types of
networks such as LANs, MANs etc.
Personal Area Network (PAN): The PANs are small network, used to establish communication
between computer and other hand-held devices in small proximity up to 10 meters using wired
USB connectivity or wireless system like Bluetooth or Infrared.
Difference between LAN and WAN
LAN WAN
1. Diameter is in a few kilometers
2. Very low error rate.
3. Complete ownership by a single
organization
4. Speed in MBPS (10-100 Mbps)
1. Span across countries
2. High error rate as compare to LAN, MAN, PAN
3. Run by multiple organization
4. Normal speed 1-2 Mbps
Network Topologies
Network topologies refer to the way in which the nodes are interconnected in a network.
Star Topology: In star topology each node is directly connected to the central node (server/hub) by
a single path.
Advantages:
1. Easy to install and Low cost.
2. A single node failure does not affect the entire network.
3. Problem diagnosis is easy due to central control.
Disadvantages:
1. Central node dependency, i.e. in case central node fails, the entire network fails.
2. Long cable is required because each node is directly attached to the server/central
node.
BUS or Linear Topology: In this topology, a single length of the transmission medium is used on
which various nodes are attached. It can transmit data in both directions.
Advantages:
1. Short cable length required
2. Easy to extend the network
Disadvantages:
1. Nodes must be intelligent.
2. Fault diagnosis is very difficult
Tree Topology: It is a variation of bus topology and has the shape of an inverted tree.
Advantages:
1. It is most suitable in networking multiple departments of a university where each unit
works separately.
2. Fault identification is easy.
Disadvantages
1. As multiple segments are connected to a central hub, if central hub fails, it affects the
entire network
2. Maintenance cost is high
Network Protocol
1. Hypertext Transfer Protocol (HTTP): HTTP is a communication protocol that is used to transfer
information on the internet and WWW. HTTP is a request/response standard between a client
and a server. A client is the end user and the server is the web site.
2. Transmission Control Protocol (TCP): TCP is responsible for verifying the correct delivery of data
from client to server. Data can be lost in intermediate network. TCP adds support to detect
errors or loss of data.
3. Internet Protocol (IP): IP is responsible for assigning 4 byte IP address to each packet.
4. Point to Point Protocol (PPP): It is a protocol used to establish a direct connection between
two computers using Telephone lines.
Remote Access Software:
Today there are some softwares that provide you facility of accessing the computer remotely.
Remote desktop software lets you access your computer and all its application over the Internet.
Example: Team Viewer, Window Remote Desktop, Ammyy Admin etc.
Team Viewer: TeamViewer is a popular piece of software used for Internet-based remote
access and support. Team Viewer Software can connect to any PC or server, so you can remotely
control your partner's PC as if you were sitting right in front of it.
Network Security Concept:
Cyber Law: Cyber law is a generic terms which refers to all the legal and regulatory aspects of
internet and World Wide Web.
Firewall: It prevents unauthorized access to or from a private network. A firewall is used to control
the traffic between computer networks.
Cookies: Cookies are the text messages sent by a web server to the web browser so that web server can keep track of the user’s activity on a specific web site.
Hackers: Gaining knowledge about someone’s private and sensitive information by getting
accessibility of his computer system illegally. Hackers are more interested in gaining knowledge
about computer system and possibly using this knowledge for playful planks.
Crackers: Crackers are the malicious programs who break into the secure system for some illegal or destructive purpose.
Network security threats:
Denial of Services (DoS): DoS attacks are those attacks that prevent the legitimate users from
accessing or using the resources and information. These types of attacks may eat up all the
resources of the system and computer become to a halt state.
Intrusion problem:
Snooping: It refers to unauthorized access of someone else data, e-mail, computer activity or
data communication. It may comprise monitoring of Keystrokes pressed, Capturing of passwords
and login information and interception of e-mails and other private information.
Eavesdropping: It the act of secretly listening / interpreting someone else’s private
communication or information while data is on its way on the network.
Spyware: Spy ware is a program designed to spy on your activities and reports this data to people
willing to pay it either for legal or illegal purposes. It is getting installed in your system without
your consent as a file or gets downloaded from websites on Internet.
Spamming: Spamming refers to the sending of bulk-mail (junk-mail) by identified or unidentified
sources.
Phishing: Phishing is a process of attempting to acquire sensitive information such as user name,
passwords, credit card number, bank account details etc. using a trap-mail in which user himself
discloses their private details.
Malicious Programs
Virus: Computer viruses are malicious and self-replicating codes/programs that cause damage to
data and files on the computer system.
Worm: It is also a self-replicating program which eats entire disk space or memory. It copies itself
until all the disk space or memory is filled.
Trojan Horse: It is a program that appears harmless (like utility program) but actually performs
malicious functions such as deleting or damaging files.
Internet application:
SMS (Short Message Services): SMS is the transmission of short text message from one mobile to other mobile phone.
Voice Mail: Transfer of recorded message is known as voice mail.
E-Mail: E-mail allows a user to send or receive message electronically across the world on the
computer.
Chat: Online textual talk in real time is called chatting.
Video Conferencing: A two way videophone conversation among multiple participants is called
video conferencing
Wireless/Mobile Communication:
GSM: It is a wireless communication medium that provides the user with roaming facility, good
voice quality, SMS etc. through digital signals.
CDMA: It is a digital cellular technology that uses spread spectrum technique where the entire
bandwidth is divided among several users for transmission of data. This allows multiple people
on multiple cell phones over the same channel to share a bandwidth of frequencies.
WLL: Wireless local loop (WLL) technology simply means that the subscriber is connected to the
nearest telephone exchange through a radio link instead of copper wires.
3G: It is the third generation of Wireless & Mobile technology. It is high-speed transmission with
advanced multimedia access and global roaming. 3G is mostly used with mobile phones and
handsets as a means to connect the phone to the Internet.
4G: It is fourth-generation of wireless service, which refers to the next wave of high-speed mobile
technologies that will be used to replace current 3G networks.
Cloud Computing: The practice of using a network of remote servers hosted on the Internet to
store, manage, and process data, rather than a local server or a personal computer.
Characteristics: (i) On demand Self-service, (ii) Broad network access. (iii) Resource Pooling (iv)
Measured service.
OPEN SOURCE CONCEPTS
Open Source Software: Open Source Software can be freely used, changed, improved, copied and redistributed but it may have some cost for support and further development. Source Code is also available with OSS. It can be modified and redistributed with some guidelines of License.
FOSS/FLOSS: An OSS may come free of cost or with a payment of nominal charges that its
developers may charge in the name of development and support of software. FLOSS (Free Livre and
OSS) is both free software as well as OSS.
Proprietary software: Proprietary software is the software that is neither open nor free available. Its use requires special permission by the supplier or vendor. They must have some cost and Source code is also not given. It is the property of the developer organization. No change, copy and distribution is allowed.
Free software: Free Software are those which are freely accessible, freely used, changed,
improved, copied and distributed. It provides all types of freedom. It has no cost and Source Code is
also available with Free Software.
Free ware: Free ware is software which is available freely and can be copied and distributed but no modification is allowed as source code is not available.
Shareware: These software are freely used, copied and distributed for a certain period of time.
After expiry, you have to purchase or uninstall them. Modification is not possible due to non availability of the source code. These are the Demo version and freely distributed for trial purpose.
Free Software Foundation (FSF): FSF is a non-profit organization established to support free
software movement. It has funded many software developers to develop free software under GNU
GPL. It also works on legal and structural issues for the free software community.
Open Source Initiative (OSI): It specifies the criteria and defines the terms and specifications for Open Software.
Free & Open Source Products:
GNU: GNU refer to GNU’s not UNIX. It offers a wide range of free and open source software
including application apart from operating system.
LINUX: Linux is a popular Open source operating system and can be downloaded from
www.linux.org.
Mozilla Firefox: Freeware, free web browser, it can be download from www.mozilla.org.
Open Office: It is an office applications suite. It is intended to be compatible with most Operating
Systems like Windows, Mac OS, UNIX and Linux and directly compete with Microsoft Office. It
includes Write (Word Processor), Calc (Spread Sheets), Draw (Graphics program), Impress
(Presentation), Math (Mathematical Formula editor) etc.
MYSQL: MySQL is most popular open source database system. MYSQL is a multi-user. It can work
on many different platforms like Linux, Microsoft Windows, SUN OS etc. It can be downloaded from
www.mysql.com.
JAVA NetBeans: Java NetBeans is a free open source, cross platform IDE with built in support for
Java programming language. In fact Net beans run anywhere where there is JVM. It offers many
features for application development.
APACHE: Apache web server (or HTTP server) is an open source web server available for many
platform such ad Linux, Microsoft windows and other platform. Apache is designed as a set of
modules, enabling administrators to choose which features they wish to use and making it easy to
add features to meet specific needs including handling protocols other than the web-standard HTTP. It is component of LAMP (Linux, apache, MySQL and PHP).
PHP: It is known as Hypertext Preprocessor. It is an OSS used for server side applications and
developing dynamic web content. PHP allows easy interaction with large number of relational
database systems such MySQL, Oracle, DB2, PostgreSQL etc. PHP runs on most major OS including
windows, Mac OS X, UNIX, Linux
W3C: The World Wide Web consortium exists to realize the full potential of the web.
Advantages of Open Source Software
• Application and platform independence.
• Making the data accessible to all.
Disadvantages of Open Source Software
• Open source development process may not be well defined and the stages in the development
process, such as system testing and documentation may be ignored.
• Not all OSS initiatives have been successful.
Types of Standards:
Proprietary standards: it is closed standard and users have to buy license to use them.
Open standards: Open standards are internationally accepted technical standards that guarantee that data can be exchanged or accessible across platforms and applications, even as technologies changes, It is freely available to all.
Some common open standard formats are:
HTML: HTML format is the standard language for the web.
XML: XML is open standard which is used to describe the data. It is text based markup language
that allows storing data in structured format
Open document Format (ODF): Open document is an open, XML based file format. It is an open standard, supported by the IBM, Sun etc.
Ogg Vorbis: It is a new audio compression format. It is roughly comparable to other formats used
to store and play digital music such as MP3 and mpeg-4 format. It is open, free, unpatented and
loosely compression project from Xiph.org foundation.
Indian Language Computing
ASCII: American Standard Code for Information Interchange is widely used alphanumeric code in
most microcomputers, minicomputers and in many mainframes. It is 7 bit code hence it can
represent 27 =128 characters.
ISCII: Indian Standard Code for Information Interchange (ISCII) is an 8 bit code capable of coding
256 characters. It retains all ASCII characters and also offers coding for Indian Scripts.
Unicode: Unicode is a 16 bit code used to incorporate almost all the language of the world
including Indian language. It provides a unique number for every character. Indian scripts included
in Unicode are Devnagari, Bengali, Gurumukhi, Gujarati, Oriya, Tamil, Telegu, Kannada, and
Malayalam.
Font: A Font refers to a set of displayable text characters called glyphs, having specific style and size. There are two categories of font: True Type Font and Open Type Font.
Types of Fonts:
True Type Font: It is developed by Apple and licensed to Microsoft. It is 8 bit font which is
compatible with Microsoft Windows and MAC OS.
Open Type Font: It is the extension of the True Type Font Format which is 16 bits font and support Unicode characters.
Static Vs Dynamic Font:
Static Font: In static fonts, the character are designed and digitized and then stored in font files.
Every time printing take place, same character will appear with same shape for example Times
New Roman, Arial etc.
Dynamic Font: In Dynamic fonts, the characters are redefined at each occurrence. Dynamic fonts
generate different font shape using certain parameter-values. All hand written fonts such as
handwritten alphabets are dynamic fonts because of individual variations.
Indian Language Text Entry: There are two types of text entries:
1) Phonetic text entry: In this type of text entry, traditional keyboards with English keys are used. But while typing, the Indian alphabets are written phonetically i.e. the way they sound. So, for
phonetic text entry, a combination of keys can be used to represent one Indian language
character. English script and then converted to corresponding language word.
2) Key map Based Entry In this method the keyboard keys are mapped to specific character using
a key map. The whole arrangement of mapping the keyboard keys to specific language
character is known as key map. A key map is internally stored as a table.
JAVA GUI PROGRAMMING REVISION TOUR-I
Rapid Application Development: It describes a method of developing software through the
use of pre-programmed tools or wizards. The pre-programmed tools or controls are simply dropped
on a screen to visually design the interface of application. It enables program development in
shorter time.
NetBeans Java IDE: It is a free, open-source, cross-platform IDE with built-in support for Java
programming language. It has more advanced GUI building tools available in any open-source Java
IDE.
Event: Occurrence of an activity.
Message: Information sent to the application or received from the application.
Types of Swing Components:
(a) Component: It is a self-contained graphic entity like JLabel, JTextField etc.
(b) Container: It can hold other components. It is of two types:
(i) Top Level Container: Can be displayed directly on a desktop. Every swing application
must have at least one top level container, i.e. JApplet, JDialog, JFrame.
(ii) Non Top Level Container: Can be displayed within another top level container, i.e.
JPanel, JScrollPane, JInternalFrame, JLayeredPane etc.
Child controls are controls inside a container control.
Java Character Set: It is the set of valid character set that a language can recognise. Java uses
Unicode character set.
Data Types: Data Types are means to identify the type of data and associated operations of
handling it.
Variables: It is a named memory location which holds a data value of a particular data type.
Variable declaration & Initialisation:
A variable with declared first value is said to be initialized variable. e.g. int rollno = 1;
Text Interaction:
(i) getText() Method : String name = nametf.getText();
(ii) setText() method: Stores or changes text in text based controls e.g. ranktf.setText(“1”);
(iii) Parse() Methods: Used to convert a string value into numeric value.
(iv) JOptionPane.showMessageDialog (): Displays a Message Box.
System.out.print (): It prints the desired output on output window.
System.out.println():It prints the desired output on output window and also changes the line.
Variable Scope: Program region within which a variable is accessible. It is accessible within the set
of braces it is declared in.
if (..)
{
int x = 5;
….
}
System.out.println(“The result is :” + x); //error
Constant: Named memory locations whose value can’t be changed in the program. It makes
program easy to read, check & modify. e.g. final int rateofinterest = 10;
Operator: It represents particular task and the objects of the operation is called operands. It may
be Unary (working on one operand, eg. unary -), binary (working on two operands, e.g. subtract -)
or ternary (working on three operands, e.g. ?:).
Type Conversion: It is the process of converting one predefined data type into another. It has two
forms:
• Implicit (Coercion): Compiler automatically performs conversion to the type of the largest
operand in mixed mode expression.
• Explicit (Type casting): Done by programmer. Converting to or from Boolean is not
permitted. : (type) expression e.g. (float) (x/5 * y + 5)
Programming Constructs: Flow of Control
Selection:
If statement;
if (expression)
statement ;
if . . . else statement
if (expression) statement 1 ;
else statement 2;
Nested if:
if (expression) {
if (expression)
statement 1 ;
[else
statement 2;] }
else
body of else;
if else if
if (expression)
body ;
else if (expression)
body;
.
else body;
Dangling else Problem:
if (expression)
if (expression) statement 1 ;
else statement 2;
(Indentation indicates else for outer if but it actually
matches with preceding If.)
The Switch Statement:
switch (expression)
{ case constant 1 : statement sequence 1;
break; // value of expression must be byte, short, int or a
char.
case constant 2 : statement sequence 2;
break;
.
[default : statement sequence n]; //default can be anywhere need not to be in
last.
}
In absence of break in switch statement, Java will start executing all statements after the correct
case. This phenomenon is called Fall-Through.
Iteration (Looping) statements:
(i) for loop (ii) while loop (iii) do-while loop
Parts of loop:
Initialisation Test Expression Update Expression Loop Body
for loop:
for (int i = 1; i <= 10; ++i)
System.out.print( i + “ ”);
Program to find factorial using for loop:
int fact = 1, a;
int num = Integer.parseInt(numtf.getText());
for (a = 1; a <= num; a++) {
fact = fact*a; }
System.out.println(“The factorial of ” + num + “ is ” + fact);
19
The While Loop: It is an Entry-controlled/Top-tested/Pre-Tested loop.
Program to find factorial using while loop:
int num = Integer.parseInt(numtf.getText());
long i = num, fact = 1;
while (num != 0) {
fact = fact *num;
-- num; }
System.out.println(“The factorial of ” + i + “ is ” + fact);
The do-while Loop: It is an Exit-controlled/Bottom-Tested/Post-Tested loop. It executes at least
once.
Syntax: do {statement ;
} while (test-expression);
Example:
char ch = ‘A’;
do {System.out.printIn(ch);
ch++;
} while (ch <= ‘Z’);
Jump statement
Return: To return from a function.
Break: It is used to terminate the body of while, do-while, for or switch statement and
transfers control to the statement following the body.
Continue: It skips the rest of the loop (while, do-while or for) and starts the next iteration
INTRODUCING CLASSES AND OBJECTS
Object Oriented Programming: The object oriented programming style emphasizes upon classes and objects. It incorporates the features of Data Abstraction, Encapsulation, inheritance and
Polymorphism. Java is a pure object oriented language.
Class is a blue print that represents a set of similar objects e.g. vehicle. Class is User
Defined/Reference Data Types.
Polymorphism: Polymorphism is the ability for a message or data to be processed in more than one form. It is the ability to behave differently in different circumstances.
Object is an entity with unique identity, characteristics and behaviour.
Method is the action defined in the class which can be carried out on the data.
Purpose of using method:
(i) Cope with complexity (ii) Hide details (iii) Reuse.
Definition:
[access specifier] [modifier] return type method-name (parameter list)
{ body of the method}
Access specifiers can be private, protected or public. Default is friendly.
Modifiers can be final, static etc.
Return type may be of any data type or void.
Parameter list is a comma-separated list of variables also known as arguments.
Access Specifiers
It controls access to members of a class. It may be
private: Can’t be accessed from outside the class.
protected: Public to subclasses in any package and classes of same package but private to
all other class.
public : Directly accessible from all other classes.
default access : If no specifier is used then the class has default access which is friendly or
package access. Members with package access are not available to the classes or subclasses
of other package.
Types of member in a class:
Instance member: Defined without static keyword. Instance variables and instance
methods are collectively called instance members. Each object created from such class will have its
own copy of instance member. It is called as <object name>.method().
Static member: It is declared using static keyword. It belongs to class as a whole and not to
a particular object. It is called as <class name>.method().
float x = 25;
float z = Math.sqrt(x); // class method.
String s = “KVS”;
String t = s.substring(0, 4); //instance method as it is called through object s
Creating objects:
city metro1, metro2;
metro1 = new city();
Or city metro1 = new city();
COMMONLY USED LIBRARIES
Libraries: It is a set of ready-made software routines that can be reused in new programs.
Libraries are made available in a program using import statement e.g. import java.io.*;
String Library: It is a part of java language library java.lang, which is by default imported to the
program. There are 3 classes to work with characters:
(i) Character Class whose instances can hold single character data.
(ii) String Class whose instances can hold unchanging string. Every time we change, a new
object is created automatically.
String s = “Excellent” ; or String s = new String(“Excellent”);
(iii) StringBuffer Class whose instances can hold mutable strings.
StringBuffer sb = new StringBuffer(); or StringBuffer sb = new StringBuffer(“First”); or
StringBuffer sb = new StringBuffer(10); // to hold 10 characters
String s = "crack", r = "rack";
Method Prototype Description
String toString() r = s.toString(); Returns the string itself.
String concat(String) Concats two strings. s = s.concat(r); -> crackrack
str1 + str2 Concatenation operator. s = s + r ;
int length() n = s.length() -> 5
String toLower Case() r = s.toLowerCase();
String toUpperCase() r = s.toUpperCase();
String trim() r = s.trim(); Removes white spaces from both ends.
String substring(int
beginindex, int endindex)
s.substring(1,3); =>> ra.
Inclusive of start index and exclusive of end index. Index starts at 0.
boolean equals(str) b = s.equals(r);
boolean
equalsIgnoreCase(str)
b = s.equalsIgnoreCase(r);
String valueOf(all types) r = s.valueOf(r); Returns string representation of passed argument
i.e. 30 is returned as "30".
valueOf() int i = Integer.valueOf(k); Returns Integer representation of passed
argument i.e. “30” is returned as 30. double x =
Double.valueOf(k); //x becomes 30.0
String s = "art", r = "science";
StringBuffer sb = new Stringbuffer(s);
Additional stringBuffer Methods:
Method Prototype Description
append(x) Adds x char at the end. sb.append(r); =>> artscience.
reverse() sb.reverse(); =>> ecneicstra
Math Functions: It is found in Math library and used as Math.sqrt(a*a + b*c)
Function Action Function Action Function Action
pow(x,y) Xy ceil(x) Round up floor(x) Round down
sqrt(x) Root abs(a) Absolute max(a,b), min(a,b) Max, min
round(x): Rounds off a number to its nearest integer. It argument is double then it returns long
and for float it returns int. If the argument is NaN, then the result is zero. Round(-4.5) is -4.
CONCEPT OF INHERITANCE
Inheritance: It is the capability of one class to derive properties from another class.
Need for Inheritance:
1. It ensures closeness with real-world models.
2. Reusability
3. Transitive nature of inheritance
Subclass & Super Class: The class being inherited is called super class or base class and the
inheriting class is called sub class or inherited class. Thus subclass derives some features (data
members and methods) from its super class.
Forms of Inheritance
Single Inheritance: A Subclass inherits from only one base class.
Multiple Inheritance: A subclass inherits from multiple base classes (not supported by Java).
Hierarchical Inheritance: Many subclasses inherit from a single base class.
Multilevel Inheritance: A subclass inherits from a class that itself inherits from another class.
This shows transitive nature of inheritance.
Hybrid Inheritance: Here a sub class inherits from multiple base classes and all of its base
classes inherit from a single base class.
Defining Derived class:
Class <sub class name> extends <super class name> {
: // members of sub class}
Function Overloading: A function name having several definitions in the same scope that are
differentiable by the number or types of their arguments (i.e. same name but different signature),
is said to be an overloaded function. Functions with same name and same signature but different
return type are not allowed. Functions with same name and signature are treated as re-declaration
of first.
Need for Function Overloading: To cope with the changing behavior in different situations.
Declaration and Definition:
double a =0.0, b = 5.4, c = 8.9;
int d = 0, e = 5, f = 8;
a = sum(b,c);
d = sum(e,f);
System.out.println(a + "," + d);
int sum( int a, int b){ //func 1
return (a+b); }
double sum( double x, double y){ //func 2
return (x+y); }
Example of Inheritance and constructors
package a;
class person {
int j = 4;
private int i = 5;
28
protected String name;
public String address;
person (String name, String address) {
this.name = name ;
this.address = address ; }
}
class student extends person { //can’t access i
int rolno ; int j = 1;
student (String name, String address, int rolno){
super(name, address);
this. rolno = rolno ; }
public void display () {System.out.println(j + “, ” + super.j);} }
// j of class person is hidden
class employee extends person{
String dept;
employee (String name, String address, String dept) {
super(name, address);
this.dept = dept; }}
class professor extends employee{
String inst;
professor(String name, String address, String dept, String inst) {
super(name, address, dept);
this. institute = inst; }}
class p { } // can’t access i,
package b;
class q { }
DATABASE CONNECTIVITY TO MYSQL
MYSQL provides connectivity for client applications developed in the Java programming language
via JDBC driver, which is called “MYSQL Connector/J”.
There are four main classes in the JDBC API for database connectivity:
(i) Driver Manager Class: It locates and logs on to the database and returns a connection object.
(ii) Connection Class: It manages the communication between Java & MySQL.
(iii) Statement Class: It contains SQL string that is submitted to the database. An SQL Select
statement returns a ResultSet object that contains the data retrieved as the result of SQL
statement.
(iv) ResultSet Class: A result set is the logical set of records that are fetched from the database by executing a query and made available to the application program. It accesses, analyzes and
converts data values returned by the SQL select statement.
Steps for Creating Database Connectivity Application:
(i) Import the package required for database programming:
import java.sql.Connection;
import java.sql.DriverManager; or import java.sql.*
import java.sql.Statement;
import java.sql.ResultSet;
(ii) Register the JDBC driver with Driver Manager:
Class.forName(“java.sql.Driver”); or Class.forName(“com.mysql.jdbc.Driver”);
(iii) Open a connection:
Connection conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test”, “root”,
“tiger”);
Test is the name of SQL database, root is user id and tiger is MySQL password.
(iv) Execute a query: Create an object of type Statement using createStatement() method. Then
execute the SQL statement using executeQuery( ) method, in case of SELECT query, or
executeUpdate() method, in case of UPDATE, INSERT or DELETE or Create Table query. It returns an
object of resultSet type.
Statement stmt = conn.createStatement();
String sql= “Select id, name from employee”;
ResultSet rs = stmt.executeQuery(sql);
OR
30
sql = “delete from employee”;
ResultSet rs = stmt.executeUpdate(sql);
ResultSet Cursor: When a ResultSet object is created, the cursor is placed just before the first row.
To move the cursor to first row use rs.next() or rs.first(). rs.next() forwards the cursor by one row –
since Initially cursor is before the first row, first rs.next() will move the cursor to first row. Any
following rs.next() commands forward the cursor by one row.
(v) Extract data from result set: This step is required if data is fetched from the database i.e., in
case of SELECT query. To retrieve the data ResultSet.get<Type>() method is used. i.e., getInt(),
getLong(), getString(), getFloat(), getDate() etc. All these method takes parameter as Column Name
or Column Index. Column Index is the order of the column.
int id = rs.getInt(“id”); // if more than one column exists in result set with same
Column Name then the first one is returned.
or int id = rs.getInt(1); // If id is first field of table.
String name = rs.getString(“name”);
Retrieving data from result set if it contains multiple rows:
Use rs.next() method. In addition to moving a result-set by one row, it also returns true if cursor is positioned on a row and false if cursor is positioned after the last row.
int id; String name;
while (rs.next()){ id = rs.getInt(1);
name = rs.getString(2); // display or process here.}
(v) Clean up the environment: Close all database resources using
WEB APPLICATION DEVELOPMENT
Web Application: A web application refers to an application which sends and receives information through WWW over internet. It may include hypertext, multimedia, GUI etc.
Web Browser: A client program that enables the user to browse, search and collect information
from the web is known as a web browser. It sends the request from the client/user to server for a
specific resource and the server then responds with the content of that resource. e.g. Internet
explorer, Mozilla Firefox, Google chrome, Opera.
Web server: Web servers are computers on internet on which web pages are stored. Web server
stores the web pages and responds to the request made by web browser. e.g.: Apache web server,
Microsoft Internet Information Server (IIS), Netscape Enterprise Web server.
URL: The unique address of the website is known as uniform Resource Locator (URL). E.g. the web
site of KVS has an address or URL called http://www.kvsangathan.nic.in. It has following parts:-
Protocol: It specifies the type of protocol (set of rules) to be followed by server. Exam: http,
https etc.
Domain Name: It specifies the name of the web server on the internet including domain
name like .com, .org, .edu or country domain like .in, .ca etc.
Directory path: It specifies Location of file/web page on the server.
Object Name: It specifies the name of specific web page or file like index.html.
Example: http://cbse.nic.in/welcome.aspx
Communication with Web server: The internet on WWW works on client server computing model. In this model client has web browser which works as web client and other machine (remote
machine) which request the pages from web server. A web server follows some protocols like HTTP,
HTTPS, FTP etc.
Client Server Computing: Client server computing refer to a network setup in which program,
documents and information reside on the server and client can connect with the server using
network to access the information. It is of two types:
1. Static Web Pages: Static Web pages display the exact information whenever anyone visits it,
until you alter that page's source code.
2. Dynamic Web pages: Dynamic Web pages are capable of producing different content for
different visitors from the same source code file. The website can display different content
based on what operating system or browser the visitor is using, whether user is using a PC
or a mobile device, or even the source that referred the visitor.
Examples:
Client side computing: Java script, VB Script.
Server side computing: ASP (Active Server Page), PHP (Personal Home Page), JSP (Java
Server Page.
Some commonly used internet protocols:
Protocol: A protocol is a set of rules to govern communication between two computers in a
network.
HTTP (Hyper Text Protocol): It is used on WWW for transferring web pages and files
contained in web pages such as images.
FTP (File Transfer Protocol): It is used for transferring files from one machine to the other.
SMTP (Simple Mail Transfer Protocol): It is used for email.
Telnet: It is used to open remote machine access (telnet) session.
HTML-1 BASIC HTML ELEMENTS
HTML
Hyper Text markup language (HTML) is used to create web pages or web documents. It tells the
web browser how to display text, pictures and links on the screen. HTML provides various markup
elements. Using these elements we can specify various parts of a web page and formatting of web
page. Basically HTML is used to design the layout of a document with hyperlink.
Elements of HTML:
HTML is made up of elements called Tags and Attributes.
Tags: A tag is coded HTML command within angular <> bracket. It is not case sensitive. There are
two types of tags Empty tags and Container tags.
Attribute: An Attribute is a special word used inside tag to specify additional information of tag
such as color, alignment etc.
Container and Empty Tags
There are two types of Tags – one that requires a starting as well as ending tag and another that
requires just a starting tag and not an ending tag.
Container Tag: A container tags requires starting tag and as well as ending tag. Example:
<Title>…..</Title>, <HEAD>…….</HEAD>
Empty Tag: Empty Elements have only a starting tag and no ending tag. e.g. <HR> inserts a
horizontal rule and <BR> breaks a line.
Structure of HTML Document:
<HTML> // It identifies that the document is an HTML document.
<HEAD> // Head tag makes the header for document.
<TITLE> KVS </TITLE></HEAD> //contains the title which appears in the title bar of the web browser
<BODY> Kendriya Vidyalaya Sangathan </BODY> // The content written in BODY tag appears in
the windows. It contains many other tags.
</HTML>
Commonly used Empty tags:
<!...Comment..>
It is used to define comments in HTML. This can be viewed in the HTML but is not displayed in web
browser.
Example: <!.... This text to display in window....>
<BR> Break Line
BR tag is used to break line. It displays remaining text in the next line.
Example: Kendriya Vidyalaya Sangathan <BR> New Delhi
<HR> Horizontal Ruler
HR tag is used to insert a Horizontal line across the browser window. The attributes of HR tag
are size, width and color.
Example: <HR Size=10, Color= Red, Width 50%>
<BASEFONT>
This tag is used to define the default size of the font, where font is not defined. Attributes of
<Basefont>
tags are size, face and color.
Example: <BASEFONT size=5 color= Green Face=”Arial”>
Commonly used Container tags
<HTML>….</HTML>
This tag is used to mark begin and end of HTML document. It may contain <Head> and
<Body tag>.
Example: <HTML><HEAD> <TITLE> KVS </TITLE> </HEAD></HTML>
<TITLE>…….</TITLE>
It specifies the title and is displayed in the title bar of the web browser.
Example: <TITLE>Title of your webpage</TITLE>
<HEAD>…….</HEAD>
It is used to specify the document header. It contains information like title, style etc.
Example: <HEAD>Contains elements describing the document</HEAD>
<BODY>…………..</BODY>
This tag is used to define the content of the documents. It may contain text, images,
multimedia, tables, list, form etc. Attributes of <Body> tags are bgcolor, background, link, Vlink,
Alink, text, leftmargin, topmargin.
Example: <BODY>The content of your page</BODY>
<BODY bgcolor=Red” Text=”green” link=”red”>
<BODY Background=”picture path” Topmargin=”70” Leftmargin=10 vlink=”lime”
alink=”red”>
<H1>…….</H1> to <H6>…….</H6>
It is used to specify the headings in a web page. In HTML there are six levels of heading (1 to
6).
<H1> is the largest and </H6> is the smallest size. Attribute of <H1> tags is align.
Example: <TITLE>Title of your webpage</TITLE>
< H1>Central Board of secondary Education</H1>
<H3 Align=”Center”>New Delhi</H3>
< P>…….</P>
To start a new paragraph. It specifies begin and end of the paragraph.
Example: This is an example displaying the use of the paragraph tag. <P> It will create a line
break and a space between lines.
< IMG>…………..</IMG>
This tag is used to display images (.jpg, bmp, .gif etc) in web pages. Attributes of <img > tag are
src,
width, height, align, border.
Example: <IMG SRC=” Location”, BORDER=”5” Align=”Left” width =“40”, Height=”50”>
<A>…….</A>
It specifies Active link of the other web page. When mouse is roll over the text, the mouser
pointer is automatically converted into the hand shaped cursor.
Example: <A HREF="http://www.yourdomain.com/">Visit Our Site</A>
Other tags:
<B>………..</B> : To make text bold
<I>….…..</I> : To make text Italic
<U>……...</U> : To make text underline
<Sup>…..<Sup> : To make text in the form of superscript like 54
<Sub>….</Sub> : To make text in the form of subscript like co2
<CENTER>…</CENTER>: To make text centralized.
Logical and Physical Text Styles
HTML has two types of styles for individual word or sentences:
1. Logical Styles 2. Physical Styles
Logical Text Styles
Logical Text: Logical text Styles are general descriptions. Each browser handles a logical style in its
own way.
<DFN> : For a word being defined. Typically displayed in italics.
<EM> : For emphasis. Typically displayed in italics.
<CITE> : For titles of books, films etc. Typically displayed in italics
<STRONG> : For strong emphasis. Typically displayed in bold.
<CODE> : For computer code. Displayed in a fixed width font.
<KBD> : For user keyboard entry. Typically displayed in plain fixed- width font.
<VAR> : For a variable, where you will replace the variable with specific
Information typically displayed in italics.
Physical Text Styles
Physical Text styles indicate the specific type of appearance for a section e.g. , bold, italics etc.
<B> : Bold text
<I> : Italic text
<U> : underline text
<TT> : type writer text (Fixed-width font)
XML (eXTENSIBLE MARKUP LANGUAGE)
Introduction to XML: XML (eXtensible markup Language) is a markup language like HTML. But XML
is different from HTML in the sense that HTML describe how to display and format the data text
and images in the browser whereas XML is used to describe the data. It is text based markup
language that allows storing data in structured format. A system that allows new markup languages
is known as Meta language. A Meta Language is a language that is used to define other language.
XML VS HTML
HTML XML
HTML document formats and displays
web pages data.
XML documents carry data along with their description.
HTML tags are predefined. XML tags are not predefined. You can create and define
new tags as per your needs.
HTML tags may not have closing tag. XML tags must have a closing tag.
HTML tags are not case-sensitive. XML tags are case – sensitive.
HTML documents are directly
viewable in a browser.
XML documents can be viewed only if proper style sheet
file is also available along with XML file.
Features of XML:
1. XML is Free.
2. XML can be used to store data. Application can be written to store and retrieve information.
3. XML is extensible. XML allows the user to create his own tags and documents structure.
4. XML is W3C recommendation.
5. XML is platform independent.
Cascading Style Sheet (CSS): Cascading is a collection of formatting rules that control the
appearance of content (Font, color, size, alignment & appearance etc) in a web page. A stylesheet is
made up of style rules that tell a browser how to present a document. There are two types of CSS.
1. Internal Cascading Sheet 2. External Cascading Sheet.
Advantages of XML:
1. It is easy as HTML.
2. It is fully compatible with application like Java.
3. It is portable language. It can be used on network with multiple platforms.
4. It is platform independent.
5. It is extensible language. You can create your own tags and document structure.
Lists in HTML
There are three basic types of lists in HTML. The lists may be nested as well.
i) Unnumbered or Unordered List
ii) Numbered List or Ordered List
iii) Definition List
Unordered lists <UL> - are indented lists with a special bullet symbol in front of each item.
<LI> - denotes the beginning of each new list item.
<UL>
<LI> Apples
<LI> Bananas
<LI> Grapefruit
</UL>
TYPE Attributes V a lue Description EXAMPLE OUTPUT
Container
element
TYPE disc Specifies the
type of bullet
symbol. Default
is disk type.
<UL type = “square”>
<LI> Tea
<LI> Coffee
</UL>
Tea
Coffee
circle
square
An ordered numbered list are indented list with numbers in front of each item.<LI> denotes the
beginning of each new list item.
<OL>
<LI> Apples
<LI> Bananas
<LI> Grapefruit
</OL>
TYPE Attributes V a l u e Description Sample sequence
/example
CODE & OUTPUT
Container
element
TYPE A Capital letters A, B, C, D <OL start = 6 type =
“i”>
<LI> JAVA
<LI> SQL
<LI> C++
</OL>
a Lowercase letters a, b, c, d
I Capital roman
numerals
I, II, III, IV
i Lowercase roman
numerals
i, ii, iii, iv
1 Arabic numerals 1, 2, 3, 4
START value defines starting
value of list
<OL start = 5>
Output –
Apples
Bananas
Grapefruit
Output –
1. Apples
2. Bananas
3. Grapefruit
Output –
vi. JAVA
53
Definition Lists <DL> - A definition Lists are indented lists without any bullet symbol or any
number in front of each item.
<DT> stands for definition term.
<DD> stands for definition description.
TYPE Attributes D e s cription CODE OUTPUT
Container
element
compact Used in case
definition terms
are very short.
<DL>
<DT> Computer
<DD> It is an electronic
device which processes
data into information.
</DL>
Computer
It is an electronic
device which processes
data into information.
Tables in HTML :-
The HTML table model allows you to arrange data – text, preformatted text, images, links, forms,
form fields, other tables etc. into rows and columns of cells.
<Table> ……..</Table> defines a table object.
<TR> ….….</TR> defines a table row.
<THEAD>…..</THEAD> defines header section of table.
<TFOOT>…..</TFOOT> defines footer section of table.
<TBODY>…..</TBODY> defines body section of table.
<TD>……..</TD> defines a table data (cell value).
<TH>……..</TH> defines column header.
<CAPTION>……..</CAPTION> defines caption of table.
Cell spacing & cell padding: Space between cells is cellspacing & space between cell border & cell
data is cell pading
Row span vs cols span: Span of a cell in respect of columns is row span . Colspan is span of a cell in
respect of columns
HTML Forms
HTML forms are means to collect information / data from the site-visitor or client. It is done with
the help of controls that collect the information and send it over. Forms contain some GUI controls
to interact with users.
Some of the important controls are:-
Button
Submit button
Reset button
Push button
Checkboxes
Radio Button
Combo boxes
Password
54
Text Input (Text field, Text area etc.)
CREATING FORM:-
Attributes Value Description
Name String Specifies the name of the form
Action Script or URL It specifies the script or email-ID or URL which will receive
data.
Method Get
Post
Form
Specifies how the form-data is submitted.
Get – form data is submitted as URL variables.
Post – form data is submitted as HTTP post.
Form – opens a new form as per specified URL.
Example : <Form Method = Get Action = “www.yahoo.com”>
Commonly used method is:-
<Form Method = Post Action = “mailto:kvs@gmail.com”>
Adding Input Controls on the Form :-
Example : <INPUT TYPE = “Text” Name =” Vidyalaya Name”>
<INPUT TYPE = “Submit” Name = “Button1” Value = “Submit”>
DATABASE FUNDAMENTALS - MYSQL REVISION TOUR
Database: Collection of logically related data stored in a structure format.
DBMS: Software used to manage databases is called Data Base Management System (DBMS).
RDBMS: A DBMS used to manage Relational Databases is called an RDBMS (Relational Data
Base Management System). Some popular RDBMS software available are: Oracle, MySQL,
Sybase, and Ingress.
Benefits of using a DBMS are:
a. Redundancy can be controlled b. Inconsistency can be avoided
c. Data can be shared d. Security restrictions can be applied.
MySQL: It is an Open Source RDBMS Software. It is available free of cost.
Relation/Table: A table refers to a two dimensional representation of data arranged in columns
(also called fields or attributes) and rows (also called records or tuples).
Key: A column or a combination of columns which have some specific characteristics in a relation
e.g. are Primary Key, Candidate Key and Foreign Key etc.
Primary Key: The group of one or more attribute(s) used to uniquely identify each row/tuple of a
relation/table is called its Primary Key.
Candidate Key: A group of columns which can be set as the primary key of a relation is called a
candidate key because it is one of the candidates available to be the primary key of the relation.
Alternate Key: A candidate key of a table which is not set as primary key is called its Alternate Key.
Degree is the number of columns/attributes in the table.
Cardinality is the number of rows/tuples in a table.
SQL (Structured Query Language): It is the language used to manipulate and manage databases
and tables within them using an RDBMS. There are following four types of SQL commands:
1. DDL (Data Definition Language): Deals with the Structure (create, remove, or modify) of
databases and tables e.g. CREATE, DROP, ALTER.
2. DML (Data Manipulation Language): Used to manipulate data/ values within tables e.g.
INSERT, UPDATE, DELETE.
3. DCL (Data Control Language): Used to control the access to the databases and tables e.g.
GRANT, REVOKE.
4. TCL(Transaction Control Language): used to manage and control the transaction e.g.
COMMIT , ROLLBACK, SAVEPOINT
Some Commonly used DDL Command are as follows:-
SNo Command, Syntax and Purpose
1 Command : Create Database
Syntax: Create database <Database Name>;
Purpose: Creates a database with specified name
2 Command : Create Table
Syntax: Create Table <Table Name>
( <Column Name1>, Data Type1,<Colomn Name2>, Data Type2);
Purpose: Creates a table with specified name
3 Command : Alter Table
Syntax: Alter Table <Table Name> Add <Column Name> Data Type1;
Alter Table <Table Name> Drop <Column Name>;
Alter Table <Table Name> Modify <Column Name> <new_defintion>;
Purpose: Modify the strcture of a table
4 Command : Use
Syntax: Use <Database Name>;
Purpose: Open the specified database for use
5 Command : Select Database( )
Syntax: : Select Database( );
Purpose: Show the name of current Database
6 Command : Show tables;
Syntax: : Show tables ;
Purpose: Show a list of tables in the current database
7 Command : Show databases;
Syntax: : Show databases;
Purpose: Show a list of databases
8 Command : Insert
Syntax : Insert Into <Table Name> ( <Column Name1>,<Column Name2>….,values
(<value1, value 2….);
Purpose: Insert Data into the table
9 Command : Select
Syntax: Select * Column name, Expression,Column name From table Name Where
Condition Order by Column Name Asc/Desc ;
Purpose: To reterive selected data from the table
10 Command : Describe
Syntax: : Desc <Table Name> ;
Purpose: Show structure of table
11 Command : Update
Syntax: : Update <table name>
Set <Column name >=Value
Where <condition>;
Purpose: Update or Modify the data in tables
Following are the clauses which can be used with select command
SNo CLAUSE EXPLANATION
1 DISTINCT Used to display distinct values from a column of a table
2 WHERE Used to specify the condition based on which rows of a table are dispalyed
3 BETWEEN Used to define the range of values within which the column values must fall to
make a condition true. It include both upper and lower values.
4 IN Used to select values that natch any values in a lsit of specified values
5 LIKE Used for pattern matching of string data using wildcard characters % and _
6 IS NULL/ NOT Used to select rows in which the specified column is NULL (or is NOT NULL)
NULL
7 ORDER BY Used to display the selected rows in ascending or desending order of the
specified column
String Functions:
SN Name & syntax Description
1 LENGTH() Returns the length of a column or a string in bytes
2 CONCAT(Str1, Str2) Returns a string that result from concatenating the arguments.
3 INSTR(Str, Substr) Returns the position of the first occurance of substring (substr) in the
string(str)
4 Lower(str) or
LCASE(Str)
Returns the argument <str> in lowercase
5 Upper(str) UCASE(Str) Returns the argument <str> in uppercase
6 LEFT(Str, n) Returns the first n character from the string
7 RIGHT(Str,n) Returns the last n character from the string
Numeric Functions:
SN Name & syntax Description
1 POWER(X,Y) or POW(X,Y) Returns the values raised to the power of y
2 ROUND(X) Rounds the argument X to the nearest Integer
3 ROUND(x,d) Rounds the argument X to d decimal places
4 Truncate(x,d) Truncate the argunment x to d decimal places
59
CALCULATOR FUNCTIONALITY USING SELECT STATEMENT
SELECT 5+68;
SELECT 5+68 FROM DUAL;
SELECT ECODE, SAL_AMT*12 FROM SALARY;
TO KNOW CURRENT DATE
SELECT CURDATE();
DISPLAY DATA WITH OTHER HEADING
SELECT SAL_AMT*12 AS “ANNUAL SALARY” FROM SALARY
SELECT 22/7 AS PI;
HANDLING NULLS
SELECT NAME, BIRTH, DEATH FROM ABC;
SELECT NAME, BIRTH, IFNULL(DEATH, ”ALIVE”) FROM ABC;
PUTTING TEXT IN SELECT QUERY
SELECT EMPNAME, ‘GETS THE SALARY PER MONTH’ SAL_AMT FROM SALARY;
USE OF RELATIONAL OPERATORS: = , > , < , >= , <= , <>
SELECT * FROM EMPLOYEE WHERE ECODE<>2001;
USE OF LOGICAL OPERATORS: OR / || , AND / && , NOT / !
SELECT * FROM EMPLOYEE WHERE ECODE=2001 OR EMPNAME=’RAVI KUMAR’;
SELECT * FROM EMPLOYEE WHERE ECODE=2001 AND EMPNAME=’RAVI KUMAR’;
SELECT * FROM EMPLOYEE WHERE (NOT ECODE=2001);
BETWEEN
SELECT ECODE, EMPNAME FROM SALARY WHERE SAL_AMT BETWEEN 20000 AND 50000;
SELECT ECODE, EMPNAME FROM SALARY WHERE SAL_AMT NOT BETWEEN 20000 AND 50000;
Date and Time Functions:
SN Name & syntax Description
1 CURDATE() Returns the current date in YYYY-MM-DD format YYYYMMDD format,
depending on whether the function is used in a string or numeric
context
2 NOW () Returns the current date and time in YYYY-MM-DD HH:MM:SS
3 SYSDATE() Returns the current date and time in YYYYMMDD HHMMSS.uuuuuuu
format
4 DATE(expr) Extracts the date part of date or datetime <exp>
5 MONTH(date) Returns the numeric month from the specified date in the range 0 to
12. it returns 0 for dates such as ‘0000-00-00’ or ‘2010-00-00’ that have
a zero month part
6 YEAR(date) Returns the year for specified date in the range 0 to 9999. it returns 0
for the ‘Zero’ date. Returns values like 1998, 2016 etc.
7 DAYNAME(date) Returns the name of the weekday for specified date
8 DAYOFMONTH(date) Returns the day of month in the range 0-31
9 DAYOFWEEK(date) Returns the day of week in number as 1 for Sunday, 2 for Monday and
so on
10 DAYOFYEAR(date) Returns the day of the year for given date in numeric format in the
range 1 to 366.
60
IN
SELECT * FROM EMPLOYEE WHERE CITY IN (‘DELHI’,’MUMBAI’,’BANGALORE’);
LIKE
SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE ‘A%’; -- Starts with A.
SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE ‘----‘; -- Exact four characters.
SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE ‘---%‘; -- At least three characters.
NULL
SELECT * FROM EMPLOYEE WHERE EMPNAME IS NULL;
ORDER BY
SELECT * FROM EMPLOYEE ORDER BY EMPNAME;
SELECT * FROM SALARY WHERE SAL_AMT >50000 ORDER BY ECODE DESC;
USING ALIAS
SELECT ECODE SAL_AMT*12 “ANNUAL SALARY” FROM SALARY ORDER BY “ANNUAL SALARY”
STRING FUNCTION
SELECT CHAR(65); -- To display the character against ASCII CODE 65.
SELECT CONCAT(ECODE, EMPNAME) AS “CODENAME” FROM EMPLOYEE; -- To merge two
columns.
SELECT RTRIM(EMPNAME) FROM EMPLOYEE; -- Removes space from right side of empname.
SELECT LTRIM(EMPNAME) FROM EMPLOYEE; -- Removes space from left side of empname.
SELECT TRIM(EMPNAME) FROM EMPLOYEE; -- Removes space from both sides of empname.
NUMERIC FUNCTION
SELECT ECODE, MOD(15,6) FROM DUAL; -- display remainder 3.
SELECT MOD(SAL_AMT,1000) FROM SALARY;
SELECT POWER(5,3) FROM DUAL; -- Calculate 5 raise to power 3. i.e.125.
SELECT ROUND(15.193, 1) FROM DUAL; --- Display 15.2 (Round off)
DATE/TIME FUNCTION
CURDATE()/CURRENT)DATE()
SELECT CURDATE(); -- Display system date.
SELECT DATE(‘2010-04-13 01:02:33’); -- Display 2010-04-13 (date only).
SELECT MONTH(‘2010-04-13’); --- Display month only i.e. 04.
SELECT YEAR(‘2010-04-13’); --- Display year only i.e. 2010.
DATABASE TRANSACTIONS
Database transaction - A database transaction is a logical unit of work that must either succeed
or fail in its entirety.
ACID properties of database transaction –
Atomicity- Atomicity ensures either all-or-none operations of a transaction are carried out.
Consistency-This property ensures that database remains in a consistent state before the start
of transaction and after the transaction is over.
Isolation-Isolation ensures that executing transaction execution in isolation i.e. is unaware of
other 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 persist i.e. remain in the
database irrespective of other failures.
TCL commands supported by SQL:-
SQL supports following TCL commands
BEGIN |START TRANSACTION-Marks the beginning of a transaction
COMMIT - Ends the current transaction by saving database changes and starts a new transaction.
Syntax : COMMIT ;
COMMIT WORK;
ROLLBACK - Ends the current transaction by discarding changes and starts a new transaction.
Syntax : ROLLBACK ;
ROLLBACK WORK;
SAVEPOINT - Defines breakpoints for the transactions to allow partial rollbacks.
Syntax : SAVEPOINT <savepoint-name> ; e.g. – SAVEPOINT MARK1;
To rollback the transaction till the bookmark named MARK1, we can write the following:-
ROLLBACK TO SAVEPOINT MARK1;
SET AUTOCOMMIT-Enables or disable the default auto commit mode. By default the autocommit
mode is true or 1.
@@autocommit- This command is used to check the status of autocommit.
MORE ON SQL-GROUPING RECORDS AND TABLE JOINS
SQL Aggregate (Group) Function: These function operates on group of rows instead of single
row that is why these functions are known as group or aggregate functions. Some important group
functions are as follows:
TABLE:Student
Rollno Sname Subject Marks grade
001 SUMIT MATHS 95 A
002 SHERRY IP 96 A
003 SUMAN IP 75
004 LALIT HINDI 84 B
005 RAHUL MATHS 88 B
Name of
the
Purpose/Use Syntax or Example OUTPUT
MAX() Returns the MAXIMUM values in a
specified column
Mysql> select max(marks)
from student;
96
MIN() Returns the MINIMUM values in a
specified column
Mysql> select min(marks)
from student;
75
SUM() Returns the SUM of values in given
column/expression.
Mysql> select sum(marks)
from student;
438
AVG() Returns the AVERAGE value in the
specified column/expression.
Mysql> select avg(marks)
from student;
87.6
COUNT() Returns the total number of non null
values in a column.
Mysql> select count(grade)
from student;
4
COUNT(*) Returns the total no of rows Select Count(*) from student; 5
TYPES OF FUNCTIONS:
Single Row Function: It works with a single row at a time. A single row function returns a result for
every row of the queried table
Multiple Row or Group Function: It works with data of multiple rows at a time and returns
aggregate values.
The main difference between these two functions is in the number of rows they act upon.
Grouping Result by using Group By: The GROUP BY clause is used in a select statement in
conjunction with aggregate functions to group the result based on DISTINCT or ALL values in a
column. Grouping can be done by column name, or with aggregate functions in which case the
aggregate produces a value for each group. Example:
Mysql> select count (marks) from student Group By subject;
OUTPUT:
HINDI 1
MATHS 2
IP 2
Mysql> select SUM (marks) from student Group by Subject;
OUTPUT:
HINDI 84
65
MATHS 183
IP 171
Conditions on Group-Having Clause: We can use any condition on group, if required. HAVING
clause is used to apply a condition on a group.
Mysql> select SUM (marks) from student Group By subject having MAX(marks)>80;
OUTPUT:
HINDI 84
MATHS 183
IP 96
Mysql> Select SUM (marks) from student Group By subject having COUNT (*)>1;
OUTPUT:
MATHS 2
IP 2
JOINS: A join is a query that combines rows from two or more tables. In a query involving join,
more than one table is listed in FROM clause separated by comma (,).
EXAMPLE: SELECT * FROM EMP1, DEPT;
CROSS JOIN (Cartesian product): Cartesian product of two tables is a table obtained by pairing each
row of one table with each row of the other. It contains all the columns of both tables.
e.g. mysql > SELECT * FROM Order, product ;
Note:-In final table cardinality will be multiplication of rows of both tables and degree will be
addition of columns of both tables.
Order Product
(After Cross Join following output will be produced)
SN Pcode scode code Name qty
1 P101 S002 P101 SOAP 20
2 P102 S003 P101 SOAP 20
1 P101 S002 P102 OIL 10
2 P102 S003 P102 OIL 10
This table will contain (2*2=4) rows and (3+3=6) Columns.
EQUI JOINS:
The Joins, in which a column is compared for equality, is called Equi-Join. The Join Column is a
column which is common in both tables.
SQL Statement: mysql> SELECT * FROM Order, product where order.pcode=product.pcode;
(After Equi Join following output will be produced)
SN Pcode scode Pcode Name qty
1 P101 S002 P101 SOAP 20
2 P102 S003 P102 OIL 10
MySQL creates a Cartesian product of the tables; from this Cartesian product MySQL select only
those records for which Pcode of order table matches with code of product table.
SN Pcode scode
1 P101 S002
2 P102 S003
code Name qty
P101 SOAP 20
P102 OIL 10
66
NON EQUI JOINS:
Non equi join is a query that specifies some relationship other than equality between the columns.
It means that records are joined on the condition other than Equal operator (<,>,<>,>=,<=) for
joining column.
NATURAL JOINS: The join in which one of the identical columns exist, is called Natural Join. The
natural join is much similar to Equi-Join, records are joined on the equality condition of joining
column except that the common column appears one time.
(After Natural Join following output will be produced)
Joining Tables Using Join Clause of SQL Statement: MySQL offer two ways by which you may join
two or more tables. One is using multiple tables with FROM clause and using JOIN keyword with
FROM clause.
Using multiple tables with FROM clause
Select * from Student, Stream where student.scode= stream.scode;
Table: Student Table: Stream
RNO Name Subject Fee Scode
101 RAM MATHS 1000 S101
102 SHAM ECO 800 C102
103 RITU ENG 500 H103
104 SHERRY PHY 1200 S101
OUTPUT
RNO Name Subject Fee Scode Stream
101 RAM MATHS 1000 S101 Science
102 SHAM ECO 800 C102 Commerce
103 RITU ENG 500 H103 Humanities
104 SHERRY PHY 1200 S101 Science
Using multiple tables with FROM clause
Select RNO, Name from student join streams on student.scode= streams.scode where
stream=”Science”
OUTPUT
RNO Name Stream
101 RAM Science
104 SHERRY Science
SN Pcode scode Name qty
1 P101 S002 SOAP 20
2 P102 S003 SOAP 20
Scode Stream
S101 Science
C102 Commerce
H103 Humanities
67
UNION:-The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The
columns must also have similar data types. Also, the columns in each SELECT statement must be in
the same order.
Syntax : UNION
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the
ALL keyword with UNION.
Syntax :- UNION ALL
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Note: The column names in the result-set of a UNION are usually equal to the column names in the
first SELECT statement in the UNION. The number of Columns must be same In both the tables.
SELECT * FROM a;
+------+------+
| x | y |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+------+------+
SELECT * FROM b;
+------+------+
| x | y |
+------+------+
| 1 | A |
| 3 | C |
+------+------+
Union:
SELECT * FROM a UNION SELECT * FROM b;
+------+------+
| x | y |
+------+------+
| 1 | A |
68
| 2 | B |
| 3 | C |
| 4 | D |
+------+------+
SELECT * FROM a UNION ALL SELECT * FROM b;
+------+------+
| x | y |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 1 | A |
| 3 | C |
+------+------+
INTERSECTION
First, let's explain what an INTERSECT query is. An INTERSECT query returns the intersection of 2 or
more data sets. If a record exists in both data sets, it will be included in the INTERSECT results.
However, if a record exists in one data set and not in the other, it will be omitted from the
INTERSECT results.
Intersect Query
Explanation: The INTERSECT query will return the records in the shaded area. These are the records
that exist in both Dataset1 and Dataset2.
Syntax The syntax for the INTERSECT operator in MySQL is:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
69
Although there is no INTERSECT operator in MySQL, you can easily simulate this type of query using
Table and Integrity Constraints
Integrity Constraints are the rules that a database must follow at all times. Various Integrity
constraints are as follows:-
1. Not Null: It ensures that we cannot leave a column as null i.e. a value has to be supplied for
that column.
2. Unique: Ensures that each row for a column must have a unique value. A column(s) can have
null value but the values cannot be duplicated.
3. Primary key: - Primary key is used to identify the record uniquely in the table. A combination
of a NOT NULL and UNIQUE means that a column cannot have duplicated values and not even
a null value.
4. Default: Specifies a default value for a column. If no value is specified while inserting records
then default value will be inserted.
5. Check: Ensures that the value in a column meets a specific condition. It is used to limit the range
of values that can be inputted in to a column
6. Foreign Key: Ensure the referential integrity of the data in one table to match values in another
table
Create Table with Constraints: You can create table with constraints using create table.
Constraints can be added at the time of table creation or can be added later on. For example:
CREATE TABLE Student
(
RollNo Integer Primary Key,
Regno Integer Unique,
NAME Char(20), Not Null,
Gender Char(1),
Age Integer Check age>=5,
Admfee integer Default 5000
);
Viewing Constraints and their Columns:
Syntax:
DESC <TABLENAME>;
DESC STUDENT//Shows detailed information about the table STUDENT
Alter Table
The purpose of alter table are as follows:-
DEFAULT
73
1. Adding Column to a table: To add a new column to a table, you can use ALTER TABLE
command as per syntax given below:-
ALTER TABLE <Table Name>
ADD [COLUMN] <Column data Type> ;
ALTER TABLE EMPLOYEE
ADD MOBILE_NO INTEGER;
A new column by the name Mobile_No will be added to the table
2. Deleting Column: To delete a column from the table, the DROP command as per syntax
given below:-
ALTER TABLE <Table Name>
DROP [COLUMN]<Column Name> ;
ALTER TABLE EMPLOYEE
DROP [COLUMN] MOBILE_NO;
It will delete the column MOBILE_NO from the table EMPLOYEE
3. Modifying Data Type(s) of a column(s): To modify a column of a table, you can use MODIFY
command as per syntax given below:-
ALTER TABLE <Table Name>
MODIFY <Column Name> <Column Definition>;
ALTER TABLE EMPLOYEE
MODIFY SALARY INTEGER(8);
It will change the width of a salary field from 5 to 8
To modify a column of a table with constraints:
ALTER TABLE <Table Name>
MODIFY <Column Name>
<Column Definition> constraints
ALTER TABLE EMPLOYEE
MODIFY EMP_NO INTEGER (10) PRIMARY KEY;
It will modify the EMP_NO and EMP_NO is now working as primary key
4. Adding Constraints: You can also use ALTER TABLE command to add constraints to your
existing table.
Syntax:
ALTER TABLE <Table Name>
ADD <Constraint name>(column name)
ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (EMP_ID);
It will define the EMP_ID as primary key
Removing Constraints: You can also use ALTER TABLE command to delete or remove constraints
from your existing table
Syntax:
ALTER TABLE <Table Name>
DROP <Constraint Name>
ALTER TABLE EMPLOYEE
DROP PRIMARY KEY;
It will delete the primary key constraint of a table
74
Enabling/Disabling Constraints: You can enable or disable constraints, but only primary key
constraint cannot be disabled, it can only be deleted. Other constraints like Foreign Key, Not Null
etc. can de enabled or disabled.
Syntax TO ENABLE:
SET FOREIGN_KEY_CHECKS=1;
Syntax TO DISABLE:
SET FOREIGN_KEY_CHECKS=0;
Dropping Tables: To delete or drop tables from database, you can use DROP TABLE COMMAND.
With Drop Table command, My SQL also removes the description of the table along with all
constraints.
Syntax:
DROP TABLE TABLE NAME
DROP TABLE EMPLOYEE;
It will DROP table employee from the database
IT APPLICATIONS
Front End: The front end application interacts with the user and collects input from the user. For
example: E-mail sign up form or any application where a user enters data.
Back End: The backend is an application or program that is not visible to the user but that processes the user requests as received from the front end. The back end handles all database accesses through one or more servers. For Example: MYSQL, Oracle, MS-Access etc.
Impact of ICT on Society:
ICT stands for (Information and communication Technology). ICT has impacted the society in a
much wider way than any other technologies. The social and economic impacts are as follows:-
Social Impact of ICT on Society:
Social Benefits:
1. Employment Generation
2. Social Networking
3. Education Sector
Economic Benefits:
1. Faster Growth of Economy
2. Global Market
3. Net Banking
4. Ease and Availability
E-Governance: E-governance refers to the application of electronic means in governance with an
aim of fulfilling the requirements of common man at affordable costs and in fastest possible time.
All government services are accessible to common man in his/her locality and ensure efficiency,
transparency and reliability of such services at affordable cost to realize the basic needs of the
common man.
Some popular E-Governance sites in India:-
http:// www. Incometaxindia.gov.in
http:// www.passport.gov.in
http:// www. India.gov.in
Social impact of E-Governance:
1. E-governance improves the efficiency and transparency of administration and service.
2. E-governance reduces waiting time before the work is done.
3. E-governance controls the corruption to some extent.
Some issues that have impacted the Society in a not so positive manner:
1. People living in rural area could not benefit from the E-Governance because of lack of
computerization in these areas.
2. Lack of awareness about E-Governance program among the people.
3. All government services are not online. So manual method cannot be avoided.
E-Commerce/E-Business: E-Commerce describes the process of buying, selling of products,
information and services via computer network using internet
Some popular E-Business sites in India:-
http:// www.irctc.co.in
http:// www.licindia.in
http:// www.Amazon.com
Advantages of E-Business to customers:
1. It reduces the cost of the products.
2. Customers can access international market.
3. Improves the customer service by offering 24X7 access to market
Social impact of E- Business:
1. Online availability of various options at remote places also.
2. Increase in online shopping habits.
3. Increase in online payments.
Some issues that have impacted the Society in a not so positive manner
1. Poor telecom, Internet services and infrastructure for reliable connectivity.
2. Lack of trust. Sometimes customers do not receive goods even after online payments up to
their expectations.
E-learning: E-Learning describes as a means of teaching and learning through computer or etechnology
like Internet, CD-ROM, DVD, Audio etc. It provides an opportunity to learn almost
anytime, anywhere.
Some popular E-Learning sites:-
http:// www.w3schools.com
http:// www.exelearning.org
http:// www.cbsecsnip.in
Advantages:
1. Study can be done anytime (24 X 7 hours available) and anywhere
2. It reduces the cost of study.
3. Self-paced and self-motivational learning
Social impact:
1. Boon for working class.
2. It helps the students to continue their studies, who has left out the school for any reason.
3. Availability of the same course to millions.