Last minute Preparation

Unit II: Computer Networks    Marks:10  

●Evolution of networking: introduction to computer networks, evolution of networking (ARPANET, NSFNET, INTERNET) ● Data communication terminologies: concept of communication, components of data communication (sender, receiver, message, communication media, protocols), measuring capacity of communication media (bandwidth, data transfer rate), IP address, switching techniques (Circuit switching, Packet switching) ● Transmission media: Wired communication media (Twisted pair cable, Co-axial cable, Fiber-optic cable), Wireless media (Radio waves, Micro waves, Infrared waves) ● Network devices (Modem, Ethernet card, RJ45, Repeater, Hub, Switch, Router, Gateway, WIFI card) ● Network topologies and Network types: types of networks (PAN, LAN, MAN, WAN), networking topologies (Bus, Star, Tree) ● Network protocol: HTTP, FTP, PPP, SMTP, TCP/IP, POP3, HTTPS, TELNET, VoIP ● Introduction to web services: WWW, Hyper Text Markup Language (HTML), Extensible Markup Language (XML), domain names, URL, website, web browser, web servers, web hosting

Network : A network is an interconnected collection of autonomous computer that can share and exchange information./ Applications of networks:  information sharing and exchange, peripheral resources sharing , software sharing, Access and share remote database , provide communication media/Advantage of networking (network goals): resource sharing , reliability ,reduce cost, fast communication  Disadvantage:· Server faults stop applications from being available.· Network faults can cause loss of data./history or evolution of network :first network ARPANET((Advanced Research Project Agency Network) is a project sponsored by U. S. Department of Defense  in 1969 to connect computers at different universities and U.S defense. Then NSFnet was developed by the National Science Foundation in 1980 which was high capacity network and strictly used for academic and engineering research.  In 1990s the internetworking of ARPANET, NSFNET and other private network combined and named Internet/Internet: worldwide network of computer networks/ Protocol: Set of rules for communication over internet is called protocol./ Node: a computer attached to a network is called node /

Data Communication: is an act of sending or receiving data over network between two or more network devices/Components: sender, receiver, message, communication media, protocols )/ Data transfer rate (DTR): is the amount of digital data that's moved from one place to another in a given time. Data transfer rate is actually a measure of the speed at which network components can exchange data(send or receive). It is measured in either bits per second or bytes per second.  / Bandwidth: A measure of the data transfer rate or capacity of a given network.  Maximum data transfer rate of a network or Internet connection./ IP Address: Unique numeric address of each resource available on the internet is called IP address. It is written in dotted decimal form as 216.27.61.157. There are two type of IP address IPV4 and IPV6 / IPV4: 32 BIT long/Format: X.X.X.X Where X called octet and must be decimal value between 0 TO 255. IPV4 Has 4 octets./IPV6: 128 BIT long/Format: X:X:X:X:X:X:X:X Where X is called octet and must be hexadecimal value between  0 TO FFFF. IPV6 has 8 octets./ DNS: stands for domain name system :character based naming system  by which servers are identified on  internet .some most common domains are  .com for commercial firms , .gov for government organizations, .edu for educational firms, .net  for network resources ISPs/networks,org for non profit organizations like NGOs, .co for listed companes/In addition to it two letter abbreviation indicating country name like www.microsoft.co.in  in stands for india, .au stands for Australia , .fr stands for france,.uk stands for united kingdom./

Switching techniques: Switching is the process of transferring data packets from one device to another in a network. Three switching methods are using during data transmission over network 1.circuit switching: A complete  physical path is established between sender(source) computer and receiver(destination)computer on network  before data transmission. It involves three phase’s connection establishment, data transfer and disconnection. A Telephone System uses this system to connect to subscribers for telephone conversation. 2. Message Switching: A message is a logical unit of information. Sender computer attach address of destination computer to the message and transmit it to its destination computer by store and forward or broadcast method. Mobile devices use this system for sending messages   3.Packet Switching: Similar to message switching but data are divided in fix size of packets and transmit independently over the network. Internet use packet switching .Advantage: packets are small and fix size and stored in main memory while in message switching complete message stored in disk. Transmission is fast because intermediate nodes do not have to wait for entire message.

Network devices: Components used to connect computers in a network  Modem: hardware device that allow your computer to connect and communicate with other computers via analog telephone lines is called modem/. Modem converts digital signals into analog and vice versa This process in called modulation and demodulation. /RJ-45: Register jack-45,8 wired connector, used to connect computers in Ethernet LANs/Ethernet LAN is specially designed by Xerox Corp with DEC and Intel. Ethernet uses either a bus or star topology. /Hub: a Networking device to connect multiple computers together in LAN . Hub broadcast data. It share and distributes bandwidth with all connected computer. Two types : active hub and passive hub. Active hubs: amplify the signals before sending to another node / Passive hub  pass the signal from on computer to another without any change. Hub broadcast the broadcast the message without any filtration/Switch: networking device to connect multiple computers in a LAN /It  is smart than a hub because a switch will only send a message to the device that needs or request it, rather than broadcasting the same message to all its ports /  Switch does not share bandwidth , rather each computer gets full bandwidth./ Bridge : A network device that connect two local  networks with same protocols  but with different types of cables. Bridge works on MAC address./Router : It is similar to bridge but can handle different protocols. Select the best path among multiple paths for data transfer on network./Repeater : network device that amplifies and restores signals for long distance transmission./ GateWay: network device that connects two dissimilar networks/NIC: Network adapter is a network card installed in your computer so that your computer can connect with internet. A NIC is also known as a network interface controller (NIC), network interface controller card, expansion card, computer circuit board, network card, LAN card, network adapter or network adapter card (NAC).NIC manufacturer assigns a unique physical address to each NIC card. This address called MAC Address. It is 6 byte address with each byte separated by a colon 10:85:03:63:2E:FC where first three bytes are manufacturer ID and last three represent card no/Wi-Fi: Wi-Fi stands wireless fidelity. Commonly used, for connecting devices in wireless mode. /

Wireless or unguided media(Devices connected through electromagnetic waves): 1. Microwaves(Line of sight transmission): transmit signal such as mobile phone calls 2,Radio waves: (Omni directional )Used to transmit radio and TV signals. Transmit information (audio , video and text), Wi-Fi also use Radio waves

/3. Satellite Microwaves: The satellites accept data/signals transmitted from an earth station, amplify them, and retransmit them to another earth station(DTH,VSAT)/4.Infrared waves: (communicate device within short range) remote control, blue tooth devices / Twisted pair Cable: Two thin copper wires are twisted around each other to reduce cross talk. These type of cables are commonly used in local telephone communication and short distance digital communication. Two types UTP(Unshielded Twisted Pair ) Cable and STP(shielded twisted pair) Cable/UTP has 7 groups CAT 1 to CAT 6/  Advantage : simple and easy to install and  maintain, low weight ,low cost /Disadvantage : high attenuation(signal became weak on long distance ,low bandwidth capability / 2. Coaxial cable: consist of a solid copper wire a PVC insulation over which there is a sleeve of copper mesh. This is widely used for television signals. In form of CABLE TV cable, it provides a cheap mans of transporting multi-channel television signals around metropolitan areas. Advantage : Higher bandwidth can be used for broadband transmission, Disadvantage : expensive. Two types: Thicknet and Thinnet.  Thicknet: thicker then thinnet max segment size upto 500 meters long. Thinnet: max segment size 185meters/ 3.Optical fibers: It is made of glass or plastic and transmits signals in the form of light. The light source used are light emitting diodes (LEDs)  or laser Diodes(LDs). It can be used for broadband transmission. Advantage : higher bandwidth, light weight, secure transmission . Disadvantage : installation and maintenance , high cost, unidirectional light propagation

Network Topology: The physical arrangement of nodes in a network is called network topology. The factors to select a topology are cost, flexibility and reliability/Star topology :a central node to which all other nodes are directly connected via single path. Adv: ease of service, one device per connection, simple access protocol. Disadv: long cable length, central node dependency, difficult to extend/Bus or linear :It consist of  A single length coaxial cable onto which various nodes are directly connected. data can be transmitted in both direction and data is broadcasted to all nodes. data is transmitted in form of packets. Adv: short cable length, easy to extend.  Disadv: fault diagnosis is difficult, fault isolation is difficult, repeater configuration /Ring or circular topology: nodes are connected in ring form. Each node is connected only its two neighbor nodes. Adv: short cable length, no wiring closet space required, suitable for optical fibers. Disadv: Difficult to diagnose faults, network reconfiguration ts difficult, node failure causes network failure/Graph topology : nodes are connected in arbitrary fashion. A node may or not may connected two or more nodes.  It is not necessary that all the nodes are connected. Tree topology: nodes are connected in network in shape of inverted tree. Fully connected topology: each node in directly connected to another node in network./Mesh Topology: Each node is connected to more than one node to provide an alternative route in the case the host is either down or too busy. This is excellent for long distance networking/ Types of  network : PAN(personal area network),LAN(local area network),MAN(Metropolitan area network )and  WAN(Wide area network)/PAN(different digital devices are connected on  a single location or at home )LAN( up to 10 km) (spread in few kilometers, span in small area such as building, group of buildings ,a university campus, complete ownership by single  organization, very less error rates, high data rate in multiple mbps)/MAN(up to 10 km ) (spread over a city for example cable TV Network)/WAN(More than 100 km ) (spread across countries, it can be a group of LANs  spread across several locations, high error rates, low data rates less than 1Mbps,Owned by multiple organization, Internet is an example of WAN)/ Based on the role played by the network computers in the network operation , network can classified in two types : 1.Peer to Peer network: On small network , a workstation that can double up as a server. Computers are called non dedicated computers. All computers are equal. Each can play role of client as well as sever. There is no control over security. It is slower and require more memory. There is no central control over the network. Clients are not depend on central sever. 2. Client Server Network(Master-slave networks):On bigger network installation, there is a computer reserved for the server’s job. Its only job to help other computers (clients) to access data, software and hardware resources. Such server is called dedicated server. A client computer is a computer on network that request for services to server. A sever is a computer on network, dedicated to processing client requests./ Server is placed where maximum number of computers exist. It reduce network traffic /Hub or switch will be required in all blocks to form LAN/Radio wave transmission is the most economical way to connect with a reasonable high speed in hilly region where cable connection in not feasible/Very fast and effective wired internet connection in optical fiber cable//Firewall is used to save private company network from internet/ layout or structure may be bus/star/ring/Mesh as given distance/ type of wired internet connection : 1. Dial up(Slowest) 2. Broadband 3.Fiber optical (leased line connection)/Difference between HTML and XML:  HTML displays data and describes the structure of a webpage, whereas XML stores and transfers data. XML is a standard language which can define other computer languages, but HTML is a predefined language with its own implications. HTML has predefine tags but in xml we can design custom tags/ Expand the terms:FTP: File transfer protocol / SFTP: Secure File transfer protocol/IOT: Internet of things /VOIP: voice over internet protocol SCP: Secure Copy Protocol/SSL: Secure Socket Layer/Telnet: teletype network(remote login program): software used to access remote computer//SMTP: Simple Mail transfer protocol/POP: Post Office protocol/HTTP:Hyper text transfer protocol/TLS: Transport Layer Security/Wi-Fi: wireless fidelity/NIC: An NIC (network interface card)/DNS: Domain Name System/GPS: Global Positioning System/DTH: Direct to Home/DHCP: Dynamic host configuration protocol/CDMA: Code division Multiple Access/SSH: Secure Shell or Secure Socket Shell(remote login program)/HTTPS:Hyper text transfer protocol Secure//W3C: World Wide Consortium/IP : Internet Protocol/IPv6: Internet Protocol Version 6/IPv4: Internet Protocol Version 4/TCP: Transmission Control Protocol/UDP: User Datagram Protocol/NIU: Network interface Unit/STP: Shielded Twisted Pair/UTP: Unshielded Twisted Pair/RJ-45 : Registered jack connector//

TCP/IP : It is a set of rules (protocols) govern communication among all computers over the internet .It specify how data is exchanged over the internet /It  identify how it should be broken into packets, addressed, transmitted, routed and received at the destination. TCP: is responsible for the data delivery of packets and IP: is responsible for the logical addressing. IP obtain the address and TCP guarantees delivery of data to that address. /HTTPS stands for Hyper Text Transfer Protocol Secure. It is a protocol for securing the communication between two systems e.g. the browser and the web server./HTTPS established an encrypted link between the browser and the web server URL: Uniform Resource Locator/XML: Extensible Markup language/VoIP:Voice over Internet Protocol (VoIP), is a technology that allows you to make voice calls using Internet connection instead of a regular (or analog) phone line. /Simple Mail Transfer Protocol (SMTP) is an Internet protocol for sending e-mail across the internet/ POP3 (Post Office Protocol 3) Protocol used for receiving emails from a remote server to a local email client. POP3 is a client/server protocol in which e-mail is received and held for you by your Internet server. IMAP(Internet message Access Protocol) : is also e-mail receiving protocol. / The File Transfer Protocol (FTP) is a standard network protocol used to transfer computer files between a client and server on a computer network./

Remote Login: It is a network application that allows a user to work on remote computer. The access in granted only after valid login id and password. There are TELNET and SSH are two remote login programs/Web browser:  software used to explore or run web pages or web sites .They are used to make request for web pages.  for example internet explorer, Mozilla, Firefox,   opera, Google chrome etc./web server(web host): Software that respond to the request made by the web clients. Web server actually a collection of web sites. For example tomcat, web-logic, IIS server etc/Web hosting : hosting(placing) a web server software on a computer system and allow customers to place their web sites on this host computer. These websites always available for web clients  via internet/WWW: stands for World Wide Web. It is a set of protocols that allows us to access web page on net through a URL.WWW is a small part of internet.  www supports various attributes like user-friendly, multimedia documents, hypertext and hyperlinks, interactive, frames   /

Web site: collection of web pages. placed on a web server/web portal: a website which host other websites. A web portal has hyperlinks to many other web sites/Home page: first page of a web site  it open when we open the website/download : transfer files from server to local computer/upload : transfer file from local computer to server computer/The most suitable place to host server would be the block ( )  as  this block contain maximum number of computers. It decreases the cabling cost and increasing the efficiency of the maximum no of computers in the network. It also makes most of the traffic local, which is as required by 80-20 network design rule.  

80-20 rule of network design: 80% traffic on a given network segment should be local and not more than 20% of the network traffic should need to move across backbone. Therefore server is placed where large no of computer placed./Because the distance between given block is more than 70 m so repeater is needed to avoid loss of signals 

 It regenerate data and voice signals /Hub/Switch  each would be needed in all the blocks to form LAN /Modem is placed for internet connectivity where server is available. /

Wireless connectivity in hilly area : radio wave transmission because radio waves are Omni directional so that transmitter and receiver do not have to be carefully aligned physically / Fastest Wired connectivity in plain area: Optical Fiber/ low speed cheap wired connection : broadband connection/

Difference between: HTTP and HTTPS / IPV-4 and IPV-6 / HTML AND XML/Switch And Hub /Web Browser And Web Server/ Bus And Star Topology/Packet And Circuit Switching/Advantage And Disadvantages Of Star/Bus Topology/ URL and DOMAIN NAME(DNS)/HTTP and FTP

Explain the terms: 1. TCP/IP 2. WEB HOSTING 3. WEB SERVER 4. WEB BROWSER 5. CIRCUIT SWITCHING 6. PROTOCOL  7.FTP 8. modem  9. HUB 10.Firewall 11. HTML 12. WWW 13. Telnet 14.DNS 15.URL 16. IP Address

5 marks question ? 1. Where server will be placed and why 2. Where modem will be placed? 3. Where hub/switch will be placed? 4. What will be layout of network? 5. Where repeater should be placed? 6. What is the type of Network in given network? 7. Which type of internet connectivity can be used?

DATABASE is a collection of information in form of tables  it can easily be accessed, managed, and updated. /A database management system (DBMS) is system software  for creating and managing databases effectively and efficiently.. /A DBMS makes it possible for end users to create, read, update and delete data in a database./The database system stores data centrally at one place where all applications can get data required. Its advantages : 1. Reduce redundancy 2. Enhance data security 3.Reduce inconsistency 4. Sharing data.

Flat database: Data is stored in one file E.g Spreadsheet /Relational Database:  data is stored in multiple tables  are linked via common filed or column

RDBMS : Relational Database Management System. Software that  is used to manages  database E.g MS Access, Base. Oracle, MySQL./Relation: table is called Relation/  Attribute: Field /Column/ headers in table containing one type or peace of information/Record/Row/Tuple: Row in a table . Record contain a complete unit of information about a person or thing. /Redundancy : duplication  of data/Data Inconsistency : Multiple mismatch copy of same data/( .odb)  : the file extension of base database is .odb

Primary key:  Column that can uniquely identify  record in a table /This key can never be duplicated and NULL. /Composite Primary key: Primary key  that composite of   two or more columns/Alternative keys : All the candidate keys other than the primary key of a relation /Candidate keys: Primary keys and alternative keys together are called the candidate keys. Attributes of a table which can serve as a primary key are called candidate keys. /Foreign key : Non key attribute that is defined as primary key in another table called foreign key. This key is used to enforce referential integrity/

Referential integrity refers to the relationship between tables. Four features of referential integrity are: (a) It is used to maintain the accuracy and consistency of data in a relationship. (b) It saves time as there is no need to enter the same data in separate tables. (c) It reduces data-entry errors. (d) It summarizes data from related tables. 

SQL structured query language set of commands that is used to  create, manipulate and process the database. 

1. Data Definition Language (DDL) Commands: create, alter, drop, grant, revoke /2. Data Manipulation Language (DML)   Select, Insert, Delete, Update etc. /3. Transaction Control Language (TCL)

1. VIEW EXISTING DATABASE

To view existing database names, the command is : SHOW DATABASES ;

2. CREATING DATABASE IN MYSQL

For creating the database in MySQL, we write the following command :

CREATE DATABASE <databasename> ;

e.g. In order to create a database Student, command is :

CREATE DATABASE Student ;

3. ACCESSING DATABASE

For accessing already existing database , we write :

USE <databasename> ;

e.g. to access a database named Student , we write command as :

USE Student ;

4. DELETING DATABASE

For deleting any existing database , the command is :

DROP DATABASE <databasename> ;

e.g. to delete a database , say student, we write command as ;

DROP DATABASE Student ;

5. VIEWING TABLES IN DATABASE

In order to view tables present in currently accessed database , command is : SHOW TABLES ;

CREATE TABLE: 

CREATE TABLE employee 

(ECODE int , ENAME varchar(20) , GENDER char(1) , GRADE char(2) , GROSS int ) ;

Inserting record: 

INSERT INTO employee VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000); 

SELECT Records 

To select only ENAME, GRADE and GROSS column from student table 

SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ;

/Select all records from table with all columns :SELECT * FROM student ;

To display details of employee whose name ends with ‘y’. 

SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘%y’ ;

To display names of employee whose name starts with R in EMPLOYEE table, the command is : 

SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R%’ ;

To display details of employee whose second character in name is ‘e’

 SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%’ ; 

To display details of employee whose  grade value is A1 or A2 : 

SELECT * FROM EMPLOYEE  WHERE GRADE IN (‘A1’ , ‘A2’); 

To display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is:

SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ;

To display the details of employees in EMPLOYEE table in alphabetical order, we use command :

SELECT * FROM EMPLOYEE ORDER BY ENAME ;

Display list of employee in descending alphabetical order whose salary is greater than 40000.

SELECT ENAME FROM EMPLOYEE 

WHERE GROSS > 40000 ORDER BY ENAME desc ;

UPDATE Records

Change the salary of employee in EMPLOYEE table having employee code 1009 to 55000. 

UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009 ;

 Update the salary to 58000 and grade to B2 for those employee have employee code is 1001.

UPDATE EMPLOYEE SET GROSS = 58000, GRADE=’B2’ WHERE ECODE = 1001 ; 

Double the salary of employees having grade as ‘A1’ or ‘A2’ . 

UPDATE EMPLOYEE SET GROSS = GROSS * 2 WHERE GRADE=’A1’ OR GRADE=’A2’ ;

DELETE RECORDS

To remove the details of those employee from EMPLOYEE table whose grade is A1.  

DELETE FROM EMPLOYEE WHERE GRADE =’A1’ ;

To delete all records from employee table : 

DELETE FROM EMPLOYEE ;

ADD COLUMN

To add a new column ADDRESS to the EMPLOYEE table, we can write command as :
 ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);

In table EMPLOYEE , change the datatype of GRADE column from CHAR(2) to VARCHAR(2). 

ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);

In table EMPLOYEE , change the column ENAME to EM_NAME and data type from VARCHAR(20) to VARCHAR(30).

ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);

DELETE COLUMN

To delete column GRADE from table EMPLOYEE, we will write :

     ALTER TABLE EMPLOYEE DROP GRADE ;

GROUP BY :Clause is used with SELECT to divide the table into groups. Groups can be done by a column name. If you wish to perform calculations on that column. You can use AGGRIGATE FUNCTIONS SUCH AS COUNT, SUM, AVG, MAX, MIN on columns etc., functions on the grouped column. Aggregate function produces a value for each group.

Count no of students class wise

Select class, count(*) from student group by class;

The HAVING Clause: is used to give condition with GROUP BY clause. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Count no of students class wise where no of students greater then 5;

Select class, count(*) from student group by class having count(*)>5;

keywords or Operators are used to specify Criteria in SQL Query ?

LIKE, NOT LIKE, IN, NOT IN, AND,OR , NOT, BETWEEN, >,>=,<.<=,!= etc

What do you mean by Aggregate Functions ?

They produce aggregate values of records. Functions produce a single value from a set of values. They are also called summary functions. These functions are used in summary Queries to group data in Queries

Some Functions are :COUNT, SUM, MAX,MIN,AVG

Constraints: conditions that can be enforced on the attributes of a relation. The constraints come in play whenever we are trying to insert, delete or update a record in a relation// Unique :Means that the values under that column are always unique. E.g.Roll_no number (3) unique /Primary key :  means that a column cannot have duplicate values and not even a null value. e.g. Roll_no number (3) primary key / The main difference between unique and primary key constraint is that a column specified as unique may have null value but primary key constraint does not allow null values in the column. /Check constraint : limits the values that can be inserted into a column of a table. e.g. marks number(3) check(marks>=0) The above statement declares marks to be of type number and while inserting or updating the value in marks it is ensured that its value is always greater than or equal to zero. /Default constraint :Is used to specify a default value to a column of a table automatically. This default value will be used when user does not enter any value for that column. e.g. balance number(5)default = 0

JOIN ::Clause is used to combine rows from two or more tables, based on a related/common column between them.  The join clause is used to combine tables based on a common column and a join condition.

Cross join (UNRESTRICTED JOIN) OR CARTISIAN JOIN : This query will give you the Cartesian product i.e. all possible concatenations are formed of all row sof both the tables EMPL and DEPT. Such an operation is also known as Unrestricted Join. It returns

N1 x N2 rows where N1 is number of rows in first table and N2 is number of rows in second table.

It returns  c1+c2 columns where c1 is numbers of columns in first table and c2 is number of columns in second table  select * from emp,dept;

An equi join is a type of join that combines tables based on matching values in specified columns. The resultant table contains repeated columns. It is possible to perform an equi join on more than two tables.

The join in which columns are compared for equality, is called Equi - Join. In equi-join, all the columns from joining table appear in the output even if they are identical.

e.g. SELECT * FROM empl, dept

WHERE empl.deptno = dept.deptno ;

 The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only.

 /data type:INT: to store integer values/DECIMAL: to store  real or decimal values with  exact precision./CHAR: to store fixed length  string with a maximum size of 255./VARCHAR: to store variable length string a maximum size of 65,536./DATE: store date values in the format YYYY-MM-DD/DATETIME: store combined date/time values in the YYYY-MM-DD HH:MM:SS format/Binary type: used  to store binary data  files such as music,image and video files./  Sorting is a process of arranging records in ascending or descending order on the basis of one or more columns . Sorting makes data easier to understand, analyse and visualise /Relationship option in Tools menu is used to create relationship among tables. There are three type of relation one to one, one to many and many to many/Aggregate(summary) Functions : Functions produce a single value from a set of values.  Some Functions are :COUNT, SUM, MAX,MIN,AVG/ 

DISTINCT:  keyword is used to remove duplicate column values from table E.g SELECT DISTINCT city from student:/WHERE:  used with SELECT command to specify the condition  in  SQL Query  

BETWEEN:  The BETWEEN operator defines a range of values The range includes both lower value and upper value.

e.g. to display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is: 

SELECT ECODE , ENAME ,GRADE 

 FROM EMPLOYEE 

WHERE GROSS BETWEEN 40000 AND 50000 ; 

LIKE:

LIKE operator is used for pattern matching in SQL. Patterns are described using two special wildcard characters:

 1. percent(%) –The % character matches any substring.

 2. Underscore ( _ ) –The _ character matches any character. 

e.g. to display names of employee whose name starts with R in EMPLOYEE table, the command is :  SELECT ENAME  FROM EMPLOYEE  WHERE ENAME LIKE ‘R%’ ; 

IN : To specify a list of values, the IN operator is used. The IN operator selects values that match any value in a given list of values. E.g. 

SELECT * FROM EMPLOYEE 

     WHERE GRADE IN (‘A1’ , ‘A2’);

NULL:  column having no value is said to have NULL value.

ORDER BY : clause is used to order the records in Ascending or descending order. ASC keyword for ascending and DESC keyword is used for descending order

DESC OR describe: To show the structure of table DESC  STUDENT

Components of table:

Data Item/Field/Column/Attribute: Column headers containing one type of information /Record/Row/Tuple: Row in a table . Record contain a complete unit of information./Primary key:  Primary key is a key that can uniquely identifies the records /tuples in a relation. This key can never be duplicated and NULL. /Composite Primary key: Primary key  that consist  of combination of  two or more columns /Alternative keys : The columns that have the unique  values for each record but are not selected as the primary keys are called alternative keys .All the candidate keys other than the primary key of a relation 

Candidate keys: Primary keys and alternative keys together are called the candidate keys. In other words all attributes that are eligible to be a primary key , are called candidate keys. This key can never be duplicated and NULL. For example rollno field can be primary key for a table student. Attributes of a table which can serve as a primary key are called candidate keys. 

Basically the process of transfer data between python programs and MySQL database is known as Python Database Connectivity. There few steps you have to follow to perform Python Database Connectivity. These steps are as follow:

1.Import the required packages 2.Establish a connection 3.Execute SQL command

4.Perform operations as per the requirements

import mysql.connector as msql

cn=msql.connect(host='localhost',user='root',passwd=‘ ',database=‘jnvchandigarh')

cur=cn.cursor()

cur.execute("insert into students values(1111,'Asmita',78.50,'B1')

cn.commit()

Difference between fwtchone()/fetchmany() and fetchall():  They all are method of cursor object 1.fetchall(): It will retrieve all records from a database table as tuple .

2.fetchone(): It will retrieve one record from the resultset as a tuple or a list. It returns the records in a specific order like first record, the next time next record and so on. If records are not available then it will return None.

3.fetchmany(): It will retrieve n number of records from the database. If records are not available then it will return an empty tuple.

4.rowcount: It is one of the properties of cursor object that return number of rows fetched from the cursor object.

TABLE ALIAS- A table alias is a temporary label given along with table name in FROM clause. e.g.

SELECT E.DEPTNO, DNAME,EMPNO,ENAME,JOB,SAL

FROM EMPL E, DEPT D

WHERE E.DEPTNO = DEPT.DEPTNO

ORDER BY E.DEPTNO;

SELECT QUERY

qry="select * from student" mycursor.execute() records=mycursor.fetchall()

 for i in records:

print(i)

 INSER QUERY

qry="insert into student values(2,'VIJAY',17,'CHANDIGARH') "

 mycursor.execute(qry)

mydb.commit() 

print(mycursor.rowcount,"record inserted")

 UPDATE QUERY

roll=int(input("enter ROLLNO of student")) 

age=int(input("enter new age")) data=(age,roll)

qry="update student set age=%s where rollno=%s"

 mycursor.execute(qry,data)

mydb.commit() 

print(mycursor.rowcount,"Record updated")

UPDATE QUERY

qry="update student set age=25 where rollno=2 " 

mycursor.execute(qry)

mydb.commit() 

print(mycursor.rowcount,"Record updated")

DELETE QUERY

qry="delete from student where name='vinay' "

 mycursor.execute()

mydb.commit() 

print(mycursor.rowcount,"Record deleted")

Parametrize Queries

INSERT QUERY

rollno=int(input("enter your rollno")) 

nm=input("enter name of student") 

age=int(input("enter age")) 

cty=input("enter your city")

 data=(rollno,nm,age,cty)

qry="insert into student values(%s,%s,%s,%s)“

mycursor.execute(qrydata)

mydb.commit()

print(mycursor.rowcount,"Record updated")