Societal Impacts

Unit-4-Societal Impacts

Digital Footprint

A digital footprint, sometimes called digital dossier is a body of data that you create while using the Internet. It includes the websites you visit, emails you send, and information you submit to online services and can be traced back by an individual. 

It is of two types: Passive digital footprint & Active digital footprints

• A passive digital footprint is created when data is collected without the owner knowing. A more personal aspect of your passive digital footprint is your search history, which is saved by some search engines while you are logged in. 
• Active digital footprints are created when a user, for the purpose of sharing information about oneself by means of websites or social media, deliberately. An "active digital footprint" includes data that you intentionally submit online. Sending an email contributes to your active digital footprint, since you expect the data be seen and/or saved by another person. The more email you send, the more your digital footprint grows. 

Publishing a blog and posting social media updates are another popular ways to expand your digital footprint. Every tweet you post on Twitter, every status update you publish on Facebook, and every photo you share on Instagram contributes to your digital footprint.  

Net and Communication Etiquettes

Netiquette is short for "Internet etiquette." Just like etiquette is a code of polite behavior in society, netiquette is a code of good behavior on the Internet. This includes several aspects of the Internet, such as email, social media, online chat, web forums, website comments, multiplayer gaming, and other types of online communication. While there is no official list of netiquette rules or guidelines, the general idea is to respect others online. 

Below are some examples of rules to follow for good netiquette:

• Avoid posting inflammatory or offensive comments online. 
• Respect others' privacy by not sharing personal information, photos, or videos that another person may not want published online.
• Never spam others by sending large amounts of unsolicited email. 
• Show good sportsmanship when playing online games, whether you win or lose. 
• Don't troll people in web forums or website comments by repeatedly nagging or annoying them. 
• Stick to the topic when posting in online forums or when commenting on photos or videos, such as YouTube or Facebook comments. 
• Don't swear or use offensive language. 
• Avoid replying to negative comments with more negative comments. Instead, break the cycle with a positive post.
• If someone asks a question and you know the answer, offer to help. 
• Thank others who help you online.

Data Protection

Data protection refers to the practices, safeguards, and binding rules put in place to protect your personal information and ensure that you remain in control of it. 

In short, you should be able to decide whether you want to share some information or not, who has access to it, for how long, for what reason, and who be able to modify some of this information Personal data is any information relating to you, whether it relates to your private, professional, or public life. 

In the online environment, where vast amounts of personal data are shared and transferred around the globe instantaneously,

It is increasingly difficult for people to maintain control of their personal information. This is where data protection comes in.

Intellectuals Property Rights (IPR)

Intellectual property refers to intangible property that has been created by individuals and corporations for their benefit or usage such as copyright, trademark, patent and digital data. It is therefore unethical to copy or steal the creativity and efforts of someone else. Intellectual property is divided into categories which are-

• Industrial property which majorly speaks about protecting inventions on the other hand.
• Copyright majorly protects literary and artistic works.

INDUSTRIAL PROPERTY Which include inventions (patents), commercial names, industrial designs, trademarks, geographic indications and designations etc.

Plagiarism

Plagiarism pronounced as   plei ·juh·ri·zm

Plagiarism means not giving authors credit after copying that author’s work. 

It involves lying, cheating, theft and dishonesty. 

For example, copying papers written by other people and professional and claims it as written by you can be an example of plagiarism. 

It can be classified as 

1. Accidental/unintentional 2. Deliberate/intentional

Accidental/unintentional Plagiarism: . Involves careless paraphrasing (changing the words or sentence construction of a copied document), quoting text excessively along with poor documentation. Accidental Plagiarism cases are less serious

whereas

Deliberate/intentional Plagiarism : Includes copying someone else’s work, cutting and passing blocks of text or any kind of information from electronic sources without the permission of the original author.Deliberate plagiarism that may result in serious implications.

HOW TO AVOID PLAGIARISM? 

Plagiarism should be avoided by the following simple measures: 

• Use your own ideas and words. 
• Always provide a reference or give credit to the source from where you have received information. 
• Cite the name of the website, a URL or the name of authors, and acknowledge them if you have used their work after rearranging the order of a sentence and changing some of the work. 
• Take the information in the form of bulleted notes in your words. 
• Use online tools to check for plagiarism. 
• Develop your writing skills. 

Licensing and copyright

A Software license is a legal permission or right to use or redistribution of that software. The software can run on a certain number of computers as per license agreement.  

The software license usually answers questions such as 

1. Can you copy, modify or redistribute it? 

2. Where and how many times can you install the software? 

3. Can you look at the underlying source code?

PROPRIETARY LICENSES

Exclusive rights in the software are retained with the owner /developer /publisher. They reserve all the freedom and rights to use and distribute this proprietary software. 

FREE AND OPEN SOURCE SOFTWARE 

Refers to software that users can safely run, adapt and redistribute without legal restraint, and which emphasizes freedom. 

Open source software (OSS) is software with a source code that is publically available under general public licenses that give users the right to study, modify and distribute that software and emphasizes security, cost saving, and transparency. 

Hence FOSS (free and open source software) allows using, copying, studying and modifying the software and the source code to be openly shared and allow copyrights to other users. 

PERMISSIVE LICENSES 

Permissive licenses provide a royalty-free license to do virtually anything with the source code.  

They permit using, copying, modifying, merging, publishing, distributing, sublicense and/or selling ,but distribution can only be made without the source code as source code modifications can lead to permissive license violation. 

COPYLEFT LICENSE

  In the case of copyleft licenses, source code has to be provided. 

Distribution and modification of source code is permitted. 

Example 

General Public License (GPL), 

Creative Commons License (CC), 

Lesser General Public License (LGPL), 

Mozilla public License (MPL) etc. 

copyright

It is a form of protection given to the authors of “original works of authorship”. This is given in the field of literature, dramatics, music, software, art etc. This protection applies to published as well as unpublished work. 

Software copyright is used by software developers and proprietary software companies to prevent the unauthorized copying of their software. 

Free and open source licenses also rely on copyright law to enforce their terms. Copyright protects your software from someone else copying it and using it without your permission. When you hold the copyright to software, you can- 

• Make copies of it. 
• Distribute it. 
• Modify it

Cyber-crime

Cybercrime is defined as “cybercrime (computer crime) is an illegal behavior, done through electronic operations, that targets the security of computer systems and the data processed by them”.

In other words cyber crime is a crime in which the offense is done using a computer or we can say a computer is the object of the crime. 

Common types of cybercrime include identity theft, unauthorized computer access, online bank information that and online predatory crimes. 

Cybercrime includes a large range of activities but it can be classified into 2 categories :

• Crimes that target computer networks or devices. These types of crimes include viruses and denial of service (DoS) attacks.
• Crimes that use computer networks to advance other criminal activities. These types of crimes include cyber-stalking, phishing and fraud or identity theft.

Cyber law

Cyber law is the part of overall legal system that deals with the Internet, cyberspace, and their respective legal issue. Cyber law covers broad area including freedom of expression, access to and usage of the Internet, and online privacy. Generally cyber law is known as “Law of the Internet”.

Importance of Cyber Law: 

1. It covers all transaction over internet. 

2. It keeps eyes on all activities over internet. 

3. It touches every action and every reaction in cyberspace.

Hacking

Hacking is identifying weakness in computer systems or networks to exploit its weaknesses to gain access. Example of Hacking, Using password cracking algorithm to gain access to a system ,

Computers have become mandatory to run successful businesses. It is not enough to have isolated computers systems, they need to be networked to communicate with external businesses. This exposes them to the outside world and hacking. Hacking means using computers to commit fraudulent acts such as fraud, privacy attack, stealing corporate/personal data, etc.

Phishing

Phishing is an attempt to capture a user’s login password and credit card details by including a URL in a spam e-mail that links to a fake website controlled by the attacker as a trustworthy entity.

Cyber bullying

Cyber bullying is the harassment or bullying executed through digital devices like computers, laptops, smart phones, and tablets. The platforms where cyber bullying can occur include social media, chat rooms, and gaming platforms where people can view and participate in the sharing of content. 

The different types of cyber bullying involve causing humiliation through hateful comments on online platforms/apps, or through SMS or messaging. 

It comprises posting, sending or sharing negative, nasty or false information about another individual for causing humiliation and character assassination.

Indian -IT Act

Information technology act 2000/ITA-2000/IT act is an act of the Indian parliament notified on 17 oct 2000.

• This primary law deals with cybercrimes and electronic commerce in India.
• It consists of 94 sections that are divided into 13 chapters and 4 schedules
• A person of others nationalities can also be indicated under the law if the crime involves a computer or network located in India, which means the law applies to the whole of India.
• The IT Act,2000 has provisions that permits the interception, monitoring of traffic data

The cyber laws in India and the provision for legal action and punishment have been explained -

Section :   67A    

Offense :    Publishing images containing sexual acts 

Penalty :   Imprisonment up to seven years and/or fine up to 10 lakh

Section :    67B

Offence :   Publishing child porn or predating children online 

Penalty : imprisonment Up to five years and/or fine up to 10 lakh On 1st                          conviction.

Section :    67C

Offence :   Failure to maintain records

Penalty :    Imprisonment up to three years and/or fine up to 2 lakh

Section :   68

Offence :  Failure/refusal to comply with orders

Penalty :   Imprisonment up to seven years, and/or possible fine

Section :  69

Offence : Failure/refusal to decrypt data

Penalty : Imprisonment up to three years, and/or fine up to 1 lakh

Section : 70

Offence : Securing access or attempting to secure access to protected system

Penalty : Imprisonment up to ten years, and/or fine

Section : 71

Offence : Misrepresentation

Penalty : Imprisonment up to three years, and/or fine up to 1 lakh

IT Act, 2000 Amendment

• A major amendment was made in 2008.
• It introduced section 69, which gave authorities the power of “interception/monitoring/decryption” of any information through any computer resource.
• It also introduced 66A which penalized sending of “offensive messages”.
• Amendments also contained penalties for child pornography, cyber terrorism, and surveillance.
• The act was passed in December 2008 and came into force in October 2009.

E-Waste:  Hazards and Waste

E-waste broadly covers waste from all electronic and electrical appliances and comprises of items such as computers, mobile phones, digital music recorders/players, refrigerators, washing machines, televisions (TVs) and many other household consumer items.

E-Waste Hazards : 

Mostly all electronic waste comprises of toxic chemicals such as lead, beryllium, mercury etc. Improper disposing of gadgets and devices increases the amount of these toxic chemicals thus contaminated the soil, causing air and water pollution.

⚫ The contaminated water which is highly polluted it thus making it harmful for drinking purposes.

⚫ Improper e-waste recycling, such as by open burning and acid baths creates hazardous and toxic compounds like- dioxins, furans and acids.

⚫ Damage to the immune system

⚫ Skin disease.

⚫ Multi ailments.

⚫ Skin problems

E-Waste Management 

E-waste management requires proper recycling and recovery of the disposed material. The recycling and recovery process includes following steps-

1. Dismantling:- removal of parts containing valuable items such as- copper,          silver, gold, steel and removal of parts containing dangerous substance like-       mercury, lead, Beryllium etc.

2. Separation metal and plastic

3. Refurbishment and reuse:- it means used electrical and electronic items that      can be easily remodel to make it’s to reuse.

4. Recovery of valuable materials

5. Disposal of dangerous materials like- mercury, lead, Beryllium etc and                disposed off in underground landfill sites.

Awareness about health concerns related to the use of Technology 

Today, computer technologies provide people with many benefits, educational activities can be designed, online shopping is available, it is possible to get in touch with people overseas and to chat with them. 

It is possible to search for anything and sometimes. 

It is even possible to do one’s job at home without going to his or her office.

If these technologies, which dominate our lives more each passing day, are not used carefully.

Then it is inevitable for people to end up with certain illnesses like-

1. Neck strain

2. Vision Problem

3. Sense of isolation

4. Sleeping disorder

5. Stress

6. Loss of attention

7. Problem in social relationships of individuals.

8. Computer anxiety

9. Internet addiction etc.

In order to avoid these problems-

• One should learn how to use these technologies without experiencing any problem rather than avoiding using them.
• Some of the users of computer technologies are not even aware of their health-related problems that they have.
• Some of those who are aware of their illnesses see a doctor for various reasons. Users of computer technologies should immediately take education on the healthy use of these technologies. 
• If the necessary precautions are not taken about this issue, individuals may have serious health problems, and the institutions may face a serious decrease in work force as well as financial losses.

***********

Introduction to Computer Networks

 unit-III 

Introduction to Computer Networks

Introduction:

A computer network is a set of nodes like computers and networking devices that are connected through communication for the purpose of communication and sharing resources(hardware/software) among the users.

Networks are used to: (Benefits of computer network) 

• Facilitate communication through email / video conferencing / instant messaging or any other mode. 
• Share hardware devices like a printer or scanner 
• Enable file sharing 
• Share software or operating programs 
• Share information 

Disadvantages of computer network 

• Lack of robustness, 
• security issue, 
• cost of network

Need of Computer Network

• Resource Sharing :- Resource Sharing means to make all programs, data and peripherals available to anyone on the network irrespective of the physical location of the resources and the user. 
• Reliability :- Reliability means to keep the copy of a file on two or more different machines, so if one of them is unavailable (due to some hardware crash or any other) them its other copy can be used. 
• Cost Factor :- Cost factor means it greatly reduces the cost since the resources can be shared.

Evolution of networking

ARPANET (Advanced Research Projects Agency NETwork): In 1969, The US govt. formed an agency named ARPANET to connect computers at various universities and defense agencies. The main objective of ARPANET was to develop a network that could continue to function efficiently even in the event of a nuclear attack.

Internet (INTERconnection NETwork): The Internet is a worldwide network of computer networks. It is not owned by anybody. The internet has evolved from ARPANET. The internet is a globally connected network system that utilizes TCP/IP to transmit information. following services are instantly available through internet : Email, Web-enabled audio/video conferencing services, Online movies and gaming , Data transfer/file-sharing, Instant messaging , Internet forums , Social networking , Online shopping ,Financial services.

Interspace: is a client/server software program that allows multiple users to communicate online with real –time audio, video and text chat in dynamic 3D environments. 

Elementary Terminology of Networks :-

1. Nodes (Workstations):- The term nodes refer to the computers that are attached to a network and are seeking to share the resources.

2. Server:- A computer that facilitates the sharing of data, software and hardware resources on the network.

Computer Network: Types

1. Personal Area Network (PAN)

2. Local Area Network (LAN) 

3. Metropolitan Area Network (MAN)  

4. Wide Area Network (WAN) 

1. Personal Area Network(PAN) – Spread in the proximity of an individual. Cover an area of a few meters radius. 

• Set up using guided media(USB cable) or unguided media (Bluetooth, Infrared). Owned, controlled, and managed by a single person. 

Examples: 

• A network of devices such as computer, Phone, MP3/MP4 Player, Camera etc. 
• Transferring songs from one cell phone to another is a PAN of two phones. Transferring files from a PC to an MP3 playeris a PAN between the two.

2. Local Area Network (LAN) – LANs are the most frequently used/discussed networks.It is one of the most common one of the simplest types of network.It is designed for small physical areas such as an office, group of buildings.Any of different types of topologies can be used to design LAN like Star, Ring, Bus, Tree etc. 

Characteristics of LAN 

• private networks means no need of regulatory control. 
• Operate at relatively high speed. 
• Ethernet, Token ring etc type media access controls are used 
• Connects computers in a single building, block or campus.

Advantages of LAN 

• Resource Sharing 
• Software Applications Sharing
• Easy and Cheap Communication 
• Centralized Data 
• Data Security 
• Internet Sharing

 Disadvantages of LAN 

• High Setup Cost 
• Privacy Violations 
• Data Security Threat 
• LAN Maintenance Job 
• Covers Limited Area 

Examples: 

A networked office building, school or home. Sometimes one building can contain a few small LANs (Like some schools have independent LANs in each computer lab.).

3. Metropolitan Area Network(MAN):– 

Spread within a city . Cover an area of a few kilometres to a few hundred kilometres radius. Set up using all types of all guided and unguided media. Owned and operated by a government body or a large corporation. 

Examples: 

A network of schools, or banks, or Government offices etc. within a city. 

A MAN is usually formed by interconnecting a number of LANs and individual computers.

4. Wide Area Network (WAN) –Slightly more complex than a LAN, a WAN connects computers across longer physical distances. The Internet is the most basic example of a WAN, connecting all computers together around the world. Because of a WAN’s vast reach, it is typically owned and maintained by any single person or owner. 

Characteristics of WAN 

• Covers large distances(states, countries, continents). 

• Communication medium like satellite, public telephone networks etc and routers are used establish connection. 

Examples: 

A network of ATMs, BANKs, National Government Offices, International Organizations' Offices etc., spread over a country, continent, or covering many continents.

Advantages of WAN 

• Long distance business can connect on the one network. 
• Shares software and resources 
• Messages can be sent very quickly to wide range of nodes 
• Hardware devices can be shared. 

Disadvantages of WAN 

• Need a good firewall to restrict unauthorized access 
• Setting up a network can be an expensive, slow and complicated. 
• Maintaining a network is a full-time job 
• Security is a major issue when many different people have the ability to use information

Network Devices

1. Modem:-A modem (modulator/demodulator) is a hardware device that allows a computer to send and receive information over telephone lines. When sending a signal, the modem converts (modulates) digital data to an analog signal and transmit it over a telephone line. Similarly when an analog signal is received, the modem converts it back (demodulates) to a digital signal.

2. Hub :- A Hub is an electronic device that connects several nodes to form a network and redirects the received information to all the connected nodes in broadcast mode. The computer(s) for which the information is intended receive(s) this information and accept(s) it. Other computers on the network simply reject this information. 

Types of Hub :

• Passive Hub: This type of does not amplify or boost the signal. It does not manipulate or view the traffic that crosses it. 

• Active Hub: It amplifies the incoming signal before passing it to the other ports

3. Switch :- A switch is a network device which is used to interconnect computers or devices on a network. It filters and forwards data packets across a network. The main difference between hub and switch is that hub replicates what it receives on one port onto all the other ports while switch keeps a record of the MAC addresses of the devices attached to it and forwards data packets onto the ports for which it is addressed across a network, that’s why switch is intelligent Hub. 

4. Repeater :- A Repeater is a device that is used to amplify and regenerate a signal which is on its way through a communication channel. A repeater regenerates the received signal and re-transmits it to its destination.

5. Router :- A device that forwards data packets from one network to another by finding the shortest route, based on an internal routing table and the address of the destination network in the incoming packet, the router determines whether to send the packet out or keep it within the network. 

6. Gateway :- A gateway is a device that connects dissimilar networks. 

Network Topologies

The geometrical arrangement of computer resources, network devices along with communication channel is known as Network structure or Network topology.

Topology can be physical or logical.

• Physical Topology-physical layout of nodes and cables in the network. 
• Logical topology - the way information flows between different components.

Types of Physical Network Topologies 

• Bus Topology 
• Star Topology 
• Tree Topology
• Mesh Topology 

Bus Topology :-Bus topology is a topology for a Local Area Network (LAN) in which all the nodes are connected to a single cable. The starting and ending point of cable is called terminator. 

ADVANTAGES 

• It is easy to install. 
• It requires less cable length and hence it is cost effective. 
• Failure of a node does not affect the network.

DISADVANTAGES 

• In case of cable or terminator fault, the entire network breaks down. 
• Not suitable for large number of computers. 
• At a time only one node can transmit data.

STAR Topology: -A STAR topology is based on a central node which acts as a hub. A STAR topology is common in homes networks where all the computers connect to the single central computer using it as a hub. 

Advantages: 

• Easy to troubleshoot 
• A single node failure does not affect the entire network. 
• Fault detection and removal of faulty parts is easier. 
• In case a workstation fails, the network is not affected. 

Disadvantages:- 

• Difficult to expand. 
• Longer cable is required.
• The cost of the hub and the longer cables makes it expensive over others.
• In case hub fails, the entire network 

TREE Topology : It has a root node and all other nodes are connected to it forming a hierarchy. It is also called hierarchical topology. It should at least have three levels to the hierarchy.

Features of Tree Topology 

• Ideal if workstations are located in groups. 

• Used in Wide Area Network. 

Advantages  

• Extension of bus and star topologies. 
• Expansion of nodes is possible and easy. 
• Easily managed and maintained. 
• Error detection is easily done

Disadvantages 

• Heavily cabled. 
• Costly. 
• If more nodes are added maintenance is difficult. 
• Central hub fails, network fails.

Mesh topology is a group of nodes which are all connected to each other and many types of connections are possible in a mesh topology.

Advantage 

• The arrangement of the network nodes is such that it is possible to transmit data from one node to many other nodes at the same time. 
• Fault is diagnosed easily

Disadvantage 

•  Installation and configuration is difficult.
•  Cabling cost is more. 
•  Bulk wiring is required. 

Introduction to Internet

The Internet is a massive network of networks, a networking infrastructure. It connects million of computers together globally, forming a network in which any computer can communicate with another computer as long as they are both connected to the Internet. The World Wide Web, or simply Web is a way or medium of accessing information over the Internet. It is an information sharing model that is built on top of the Internet.

” The Internet emerged in the United States in the 1970s but did not become visible to the general public until the early 1990s. By 2020, approximately 4.5 billion people, or more than half of the world’s population, were estimated to have access to the Internet. 

The Internet provides a capability so powerful and general that it can be used for almost any purpose that depends on information, and it is accessible by every individual who connects to one of its associated networks.

URL :- URL stands for Uniform Resource Locator. A URL is nothing more than the address of a given unique resource on the Web or address of a website. The URL is an address that matches users to a specific resource online, such as webpage. 

Example- https://www.bsfdabla.kvs.ac.in

WWW : The World Wide WEB (WWW), commonly known as the ‘Web’. It is an information system all the web resources are identified by Uniform Resource Locator (URL). Tim Berners-Lee invented the WWW in 1989. He wrote the first web browser in 1990. 

Application of WWW : 

• Publishing, Marketing and advertising 
• Research and Development  
• Communication 
• Collaboration 
• Industrial classification of the sample companies etc. 
• Gaming 

e-mail : email (or e-mail) is defined as the transmission of messages over communications networks. Typically the messages are notes entered from keyboard and sent over internet using computer or mobile.

Chat : Chat may refer to any kind of communication over the Internet that offers a real-time transmission of text messages from sender to receiver. Chat messages are generally short in order to enable other participants to respond quickly. 

Voip :- Voice over Internet Protocol (VoIP), is a technology that allows you to make voice calls using a broadband Internet connection instead of a regular (or analog) phone line. VoIP services convert your voice into a digital signal that travels over the Internet. If you are calling a regular phone number, the signal is converted to a regular telephone signal before it reaches the destination. VoIP can allow you to make a call directly from a computer.

Website :- a website is a group of web pages, containing text, images and all types of multi-media files. 

Difference between Website and Webpage 

Website : A collection of web pages which are grouped together and usually connected together in various ways. Often called a "web site" or simply a "site."

Webpage:  A document which can be displayed in a web browser such as Firefox, Google Chrome, Opera, Microsoft Internet Explorer etc.

 Static webpage  v/s  dynamic webpage

Static webpage

• The static web pages display the same content each time when someone visits it.
• It takes less time to load over internet.
• No Database used. 

dynamic webpage

• Dynamic web pages the page content changes according to the user.
• Dynamic web pages take more time while loading.
• A database is used in at the server end in a dynamic web page. 

Web Server :- a web server is a computer that stores web server software and a website's component files (e.g. HTML documents, images, CSS style sheets, and JavaScript files). 

When client sends request for a web page, the web server search for the requested page if requested page is found then it will send it to client with an HTTP response. 

If the requested web page is not found, web server will the send an HTTP response :Error 404 Not found.

Web Hosting :- Web hosting is an online service that enables you to publish your website or web application on the internet. When you sign up for a hosting service, you basically rent some space on a server on which you can store all the files and data necessary for your website to work properly. 

A server is a physical computer that runs without any interruption so that your website is available all the time for anyone who wants to see it.

Web Browser :- A web browser, or simply "browser," is an application used to access and view websites. Common web browsers include Microsoft Internet Explorer, Google Chrome, Mozilla Firefox, and Apple Safari.

Plug-ins:- a plug-in (or plugin, add-in, add-on) is a software component that adds a specific feature to an existing computer program. When a program supports plug-ins, it enables customization. Plug-ins are commonly used in Internet browsers but also can be utilized in numerous other types of applications. 

Add-ons : An Add-on is either a hardware unit that can be added to a computer to increase the capabilities or a program unit that enhances primary program. Some manufacturers and software developers use the term add-on. Examples of add-ons for a computer include card for sound, graphic acceleration, modem capability and memory. Software add-ons are common for games, word-processing and accounting programs.

Cookies :- cookies are small files which are stored on a user’s computer and contains information like which Web Pages visited in the past, logging details Password etc. They are designed to hold a modest amount of data specific to a particular client and website and can be accessed by the web server or the client computer.

Monthly Test Quiz Assignment

Unit-III Database Query using SQL

SQL FUNCTIONS

Math functions: POWER (), ROUND (), MOD (). 

Mathematical functions –Perform operation over numeric value.

POWER() – power() returns the value of a number raised to the power of another number. The synonym of power() is pow(). 

Syntax - pow(m,n)

where 'm' A number which is the base of the exponentiation and 'n' A number which is the exponent of the exponentiation. 

Example :-

Mysql> select pow(2,3); 

Mysql>8 

Mysql> select pow(2.37,3.45); 

Mysql>19.6282……

ROUND() – the round() function returns a number rounded to a certain number of decimal places. 

Syntax - ROUND(column_name,decimals) 

where column_name -Required the field to round. 

decimals -Required, Specifies the number of decimals to be returned.

Example :-

Mysql> select round(454.352,2); 
Mysql>454.35
Mysql> select round(454.352,0); 
Mysql>454

MOD() – The MOD() function returns the remainder of one number divided by another. The following shows the syntax of the MOD() function: 

Syntax - MOD(dividend,divisor) 

Dividend - is a literal number or a numeric expression to divide. 
Divisor- is a literal number or a numeric expression by which to divide the dividend. 

Example:-

Mysql> SELECT MOD(11,3); 
Mysql>2 
Mysql> SELECT MOD(10.5,3); 
Mysql>1.5

Text functions

Text functions- Perform operation over string values. 

UPPER() – UPPER(str) Returns the string str with all characters changed to uppercase. 

mysql> SELECT UPPER(‘india'); 
Mysql> ‘INDIA' 

Note: UCASE(str)-UCASE() is a synonym for UPPER(). 

LOWER(str)-Returns the string str with all characters changed to lowercase 

mysql> SELECT LOWER('JAIPUR'); 
Mysql> 'jaipur’

Note: LCASE(str) LCASE() is a synonym for LOWER()

SUBSTRING

Syntax 1:
SUBSTRING(str,pos) 
The forms without a len argument return a substring from string str starting at position pos. 

mysql> SELECT SUBSTRING(‘practically',5); 
-> 'tically' 

Syntax 2: 
SUBSTRING(str FROM pos), 
The forms with a len argument return a substring len characters long from string str, starting at position pos. 

mysql> SELECT SUBSTRING('informatics ' FROM 4); 

-> ‘ormatics' 


Syntax 3: 
SUBSTRING(str,pos,len)

mysql> SELECT SUBSTRING('Quadratically',5,6); 
-> 'ratica' 


The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. 
In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. 


mysql> SELECT SUBSTRING(‘Ankita', -3); 
-> 'ita' 


mysql> SELECT SUBSTRING(‘Ankita', -5, 3); 
-> 'nki'

LENGTH(str) - Returns the length of the string str 

mysql> SELECT LENGTH('text'); 
-> 4 

LEFT(str,len) - Returns the leftmost len characters from the string str, or NULL if any argument is NULL. 

mysql> SELECT LEFT(‘Toolbar',4); 
-> ‘Tool‘ 

RIGHT(str,len)-Returns the rightmost len characters from the string str, or NULL if any argument is NULL. 

mysql> SELECT RIGHT(‘Toolbar',3); 
-> 'bar'

INSTR(str,substr)-Returns the position of the first occurrence of substring substr in string str. 

mysql> SELECT INSTR(‘Toobarbar','bar'); 
-> 4
mysql> SELECT INSTR('xbar',‘ybar'); 
-> 0

LTRIM(str)-Returns the string str with leading space characters removed. 

mysql> SELECT LTRIM(' Toolbar'); 
-> ‘Toolbar‘ 

RTRIM(str)-Returns the string str with trailing space characters removed. 

mysql> SELECT RTRIM(‘Toolbar '); 
-> ‘Toolbar‘ 

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)- Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given , BOTH is assumed. 

mysql> SELECT TRIM(' tool '); 
-> 'tool' 
mysql> SELECT TRIM(LEADING 'x' FROM 'xxtoolxx'); 
-> ‘toolxx' 
mysql> SELECT TRIM(BOTH 'x' FROM 'xxtoolxx'); 
-> ‘tool' 
mysql> SELECT TRIM(TRAILING 'x' FROM ‘toolxx'); 
-> ‘tool'

Date functions

Date functions- Perform operation over date values. 

NOW()-Returns the current date and time as a value in 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context. 
mysql> SELECT NOW(); 
-> '2020-04-05 23:50:26' 
mysql> SELECT NOW() + 0; 
-> 20200415235026.000000 
Here +0 means +0 second 

DATE(expr)-Extracts the date part of the date or datetime expression expr. 

mysql> SELECT DATE('2003-12-31 01:02:03'); 
-> '2003-12-31'

MONTH(date)-Returns the month for date, in the range 1 to 12 for January to December, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero month part. 

mysql> SELECT MONTH('2008-02-03'); 
-> 2 

MONTHNAME (date)-Returns the full name of the month for date. 

mysql> SELECT MONTHNAME('2008-02-03'); 
-> 'February‘ 

YEAR(date)-Returns the year for date, in the range 1000 to 9999, or 0 for the “zero” date. 

mysql> SELECT YEAR('1987-01-01'); 
-> 1987 

DAY(date)-Returns the day of the month for date, in the range 1 to 31, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero day part. 

mysql> SELECT DAYOFMONTH('2007-02-03'); 
-> 3 

DAYNAME(date)-Returns the name of the weekday for date. 

mysql> SELECT DAYNAME('2007-02-03'); 
-> 'Saturday'

Aggregate Functions

Aggregate Functions & NULL- Perform operation over set of values. 

Sum()
Min()
Max()
AVG()
Count()

Consider a table Emp having following records as Null values are excluded while (avg)aggregate function is used .
              
              Table EMP
Emp Code         Name      Sal 
E1               Mohak     NULL 
E2               Anuj      4500 
E3               Vijay     NULL 
E4               Vishal    3500 
E5               Anil      4000 

SQL Queries 
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 

Querying and manipulating data 

using Group by, Having, Order by clause

Group by: 

Group by clause is used to divide the table into logical groups and we can perform aggregate functions in those groups. in this case aggregate function will return output for each group. 

Consider the following table student1

mysql>select sum(marks) from student1 group by age;

-> 195

   284

HAVING Clause

if we want to filter or restrict some rows from the output produced by GROUP BY then we use HAVING clause. it is used to put condition of group of rows. with HAVING clause we can use aggregate functions also.

WHERE clause is used before GROUP BY . with WHERE  we cannot use aggregate functions.

select max(marks) from student1 group by age having count(*)>1;

-> Max(marks)

   98

   98

ORDER BY 

by default records will come in the output in the same order in which it was entered. To see the output rows in sorted or arragned in ascending or descending order SQL provide order by clause. by default output will be ascending order(ASC) to see output in descending order we use DESC clause with order by.

select * from student1 order by name;

select * from student1 order by name  desc;

Assignment

Quiz1

Previous Years CBSE Board Examination Questions

2 Marks Questions

Question 1.

Write the output of the following SQL queries:
(i) SELECT RIGHT (‘software’, 2);
(ii) SELECT INSTR (‘twelve’, 'l');
(iii) SELECT DAYOFMONTH (‘2014-03-01’);
(iv) SELECT ROUND  (76.987,2); 

Answer:

(i) re
(ii) 4
(iii) 01
(iv) 76.99

Question 2.

There is a column Salary in a Table EMPLOYEE. The following two statements are giving different outputs. What may be the possible reason? 

SELECT COUNT(*) FROM EMPLOYEE;
SELECT C0UNT(Salary) FROM EMPLOYEE;

Answer:

SELECT COUNT (*) FROM EMPLOYEE:

This statement returns the number of records in the table.

SELECT COUNT(Salary) FROM EMPLOYEE;

This statement returns the number of values (NULL values will not be counted) of the specified column.

Question 3.

A table FLIGHT has 4 rows and 2 columns and another table AIRHOSTESS has 3 rows and 4 columns. How many rows and columns will be there if we obtain the cartesian product of these two tables? 

Answer:

Total number of rows will be 12 and total number of columns 6.

Question 4. 

What is the purpose of GROUP BY clause in MySQL? How is it different from ORDER BY clause? 

Answer:

The GROUP BY clause can be used to combine all those records that have identical value in a particular field or a group of fields. Whereas, ORDER BY clause is used to display the records either in ascending or descending order based on a particular field. For ascending order ASC is used and for descending order, DESC is used. The default order is ascending order.

Question 5.  

Shanya Khanna is using a table EMPLOYEE. It has the following columns:

Admno, Name, Agg, Stream [column Agg contains Aggregate marks]

She wants to display highest Agg obtained in each Stream.
She wrote the following statement:

SELECT Stream, MAX(Agg) FROM EMPLOYEE;

But she did not get the desired result. Rewrite the above query with necessary changes to help her get the desired output.

Answer:

SELECT Stream, MAX(Agg)
FROM EMPLOYEE
GROUP BY Stream;

Question 6.
State difference between date functions NOW() and SYSDATE() of MySql.
Answer:
Differences between Now() and SYSDATE() of MySql are as follows:

7 Marks Questions

Question 7.
Consider the following table named SBOP with details of account holders. Write commands of MySql for (i) to (iv) and output for (v) to (vii).

TABLE SBOP

(i) To display Accountno, Name and DateOfopen of account holders having transactions more than 8.
(ii) To display all information of account holders whose transaction value is not mentioned.
(iii) To add another column Address with datatype and size as VARCHAR(25).
(iv) To display the month day with reference to DateOfopen for all the account holders. 

(v) SELECT COUNT (*) FROM SBOP;
(vi) SELECT Name, Balance FROM SBOP WHERE Name LIKE “%i”;
(vii) SELECT ROUND (Balance,-3) FROM SBOP
       WHERE Accountno="SB-5” ;


Answer:
(i) SELECT Account no, Name, DateOfopen FROM SBOP WHERE Transaction > 8:
(ii) SELECT * FROM SBOP WHERE Transaction IS NULL;
(iii) ALTER TABLE SBOP ADD Address VARCHAR(25);
(iv) SELECT DAY0FM0NTH(DateOfopen), Name FROM SBOP:




Question 8.
Consider the following table named EXAM with details of marks. Write command of MySQL for (i) to (iv) and output for (v) to (vii).
Table EXAM

(i) To display all information of the students of humanities in descending order of percentage.
(ii) To display Adno, Name, Percentage and Stream of those students whose name is less than 6 characters long.
(iii) To add another column Bus_Fees with datatype and size as Decimal(8,2).
(iv) To increase percentage by 2% of all the Humanities students. (All India 2014)
(v) SELECT COUNT(*) FROM EXAM;
(vi) SELECT SName, Percentage FROM EXAM WHERE Name LIKE “N%”;
(vii) SELECT ROUND (Percentage ,0) FROM EXAM WHERE Adno=“R005”;



Answer:
(i) SELECT * FROM EXAM WHERE Stream = ‘Humanities’
    ORDER BY Percentage DESC;
(ii) SELECT Adno, SName, Percentage, Stream FROM EXAM
     WHERE LENGTH(SName)<6;
(iii) ALTER TABLE EXAM
      ADD Bus_Fees DECIMALS, 2) ;
(iv) UPDATE EXAM
     SET Percentage = Percentage + 2 
      WHERE Stream = ‘Humanities’;

(vi) The given query will result in an error as there is no column named         Name in table EXAM.



Question 9.
Consider the table SUPPLIER given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii). (i)

TABLE SUPPLIER

(iii) To count distinct City in the table.
(iv) To insert a new row in the table SUPPLIER.
       110, “Bournvita”,’ ABC’, 170, ‘Delhi’, 40.00 (Delhi 2012)

(v) SELECT Pname FROM SUPPLIER WHERE Supname IN ('Bread', 'Maggt');
(vi) SELECT COUNTCDISTINCT City) FROM SUPPLIER;
(vii) SELECT MAX(Price) FROM SUPPLIER WHERE City = ‘Kol kata’;



Answer:
(i) SELECT Pname FROM SUPPLIER WHERE Pname LIKE ‘B%’ ORDER BY Price;
(ii) SELECT Scode, Pname, City  FROM SUPPLIER WHERE Qty < 150;
(iii) SELECT COUNT(DISTINCT City) FROM SUPPLIER;
(iv) INSERT INTO SUPPLIER VALUES      (110,‘Bournvita’,‘ABC’,170,‘Delhi’,40.00);


Question 10.
Consider the table PERSONS given below. Write commands in SQL for (i) to (iv) and write output for (v) to (viii).

TABLE PERSONS

(i) Display the SurName, FirstName and City of people residing in Udhamwara city.
(ii) Display the Person IDs (PID), Cities and Pincode of persons in descending order of Pincode.
(iii) Display the FirstName and City of all the females getting Basic salaries above 40000.
(iv) Display FirstName and BasicSalaries of all the persons whose first name start with ‘G’. 

(v) SELECT SurName FROM PERSONS WHERE BasicSalary>= 50000;
(vi) SELECT SUM (BasicSalary) FROM PERSONS WHERE Gender = ‘F’;
(vii) SELECT Gender, MIN (BasicSalary) FROM PERSONS GROUP BY Gender;
(viii) SELECT Gender, COUNT (*) FROM PERSONS GROUP BY Gender;



Answer:
(i) SELECT SurName, FirstName, City FROM PERSONS WHERE City = ‘Udhamwara’;
(ii) SELECT PID, City. PinCode FROM PERSONS ORDER BY Pincode DESC;
(iii) SELECT FirstName, City FROM PERSONS WHERE Gender = ‘F’ AND
       BasicSalary > 40000;
(iv) SELECT FirstName, BasicSalary FROM PERSONS WHERE FirstName LIKE ‘G%’;