CLASS-XII

COMPUTERSCIENCE(083)
UNIT-III DATABASEMANAGEMENTSYSTEM

S.No. Topic PageNo.

9.1 Introduction 2
9.2 DataModels 2
9.3 Keys 3
9.4 IntroductiontoMySQL 5
9.5 SQL 6
9.6 SQLElements 7
9.7 ClassificationofSQLcommands 10
9.8 SQLQueries 10
9.9 Constraints 13
9.10 Addingnewcolumninatable 17
9.11 Modifya table column 17
9.12 Droppinga table 18
9.13 Manipulationdataofa table 19
9.14 MakingSimpleQueries 20
9.15 Queriesforspecialoperators 23
9.16 ORDERBYclause 25
9.17 Aggregatefunctions 25
9.18 Joins 27
9.19 MySQLdatabaseconnectivitywithpython 29

Page1
 CHAPTER-9DATABASEMANAGEMENTSYSTEM(DBMS)

INTRODUCTION:
DBMSisacollectionofinterrelateddatainarrangedformandsetofprogramsusedto access
those data.

AdvantagesofDBMS:
 Controlofdata redundancy
 Data consistency
 Sharingofdata
 Data Integrity
 Data isolation
 Privacyand Security

DATAMODELS:

Datamodelsdescribethestructureofthedatabase.Therearefourdatamodelsin DBMS:
1. RelationalDataModel
2. HierarchicalDataModel
3. NetworkModel
4. ObjectOrientedDataModel

1. RelationalDataModel:Thisdatabaseconsistsofacollectionoftable.Thesetables
arecalledrelations.Arowinatablerepresentsarelationshipamongasetofvalues.

 Relation:Arelationisatablewithcolumnsandrows.
 Tuple:Arowofarelation ORarowofa table.
 Domain:Asetofvaluesforthe columns.
 Attribute:Columnnameofarelation.
 Degree:Numberofattributesinatable.
 Cardinality:NumberoftuplesORNumberofrowsinatable.
 View:Avirtualtablethatdoesnotexistsbutitisderivedfromother table.

Page2
 Roll_No. St_Name Fname Marks Percentage

2. HierarchicalDataModel:Inthismodelthedataarerepresentedbycollectionand
relationship between data are represented by links. In this model the collection of
data are organized as tree.

3. NetworkDataModel: Thisissameasthehierarchicalmodelbutinthis modelthe

collection of data is organized as arbitrary graphs.

4. Object Oriented Data Model: In this model the data and its operations are
represented by objects.

NOTE:Some Definitions:-

Record:Collectionofattributes
File:Collectionofrecords

KEYS:

A key allowsustoidentify asetofattributesthatdistinguish entitiesfrom each other.

Therearefourkeys:

1. PrimaryKey
2. CandidateKey
3. AlternateKey
4. ForeignKey

1. Primary Key:A primary key is a set of one or more attributes that can uniquely
identify tuples within the relation. This key does not have duplicate values in the
relation. There must be any value for this key, it cannot be NULL.

Page3
 PrimaryKey

Roll_no. Name F_Name Stream DOB

Table:STUDENT

2. CandidateKey:Allattributescombinationinsidearelationthatcanserveasa primary
key, is called candidate key.

{Name,F_Name} CandidateKey
{Name,DOB}

3. Alternate Key: A candidate key that is not the primary key is called an
alternatekey.

4. Foreign Key: A non-key attribute whose values are derived from the primary key
of some other table, is known as foreign key in its current table.

Primary Key TABLE:CUSTOMER(Parent Table)

Cust_ID First_Name Last_Name

Primary Key TABLE:ORDERS(ChildTable)ForeignKey

Order_No. Order_Date Cust_ID Amount

Primarykeyofparenttablebecomestheforeignkeyforthechild table.

Page4
INTRODUCTIONTOMySQL:

Introduction:
 MySQLstandsforMyStructuredQueryLanguage.
 MySQL is freely available, Open source Relational Database Management
System (RDBMS).

Featuresof MySQL:
a. Cost:Freelyavailable
b. Performance:MySQLis fast.
c. Simplicity:MySQLiseasytoinstall.
d. HighPerformance
e. DataSecurity:Inhaspowerfulmechanismfor ensuringonlyauthorizedusershave
access the data.
f. Flexibility

STARTINGMySQL:

Page5
FirstyouhavetoinstallMySQLonyoursystem.Thenclickon

Start AllPrograms MySQL MySQLCommandLineClient

SQL:-
 SQLstandsforStructuredQueryLanguage.
 Thisisnon-proceduralLanguage.
 Thisisthecommonlanguageforrelationaldatabase.Meansthislanguageisused in
MySQL.

DifferencebetweenSQLand MySQL:

SQL MySQL
Thisisthelanguagethatusedin database.
Thisistheopensourcedatabase.

ThislanguageisusedinMySQLto write Thisisthedatabase.ItusestheSQLto write

the commands in queries. the queries.

Page6
SQLElements:

TherearefourbasicelementsofSQL:

1. Literal
2. DataTypes
3. Nulls
4. Comments

1. Literal:Aliteralisafixeddatavalue.Thisfixvaluemaybenumericor character.

Integer Literal(75,+23,-765,34)

NumericLiteral DecimalLiteral(75,23.45, 0.375,-36)

Float,Real,doubleLiteral
(-2300, -23E2, 0.1256)

Character Literal: All character literals are enclosed in single quotation mark or double
quotation marks.

Forexample:-„a‟,“yogesh”,„5‟,“xyz5”

2. Data Types:- Data types are rules that define what data maybe stored in a column
andhowthatdataisactuallystored.DatatypesusedinMySQLcategorizedintofour
categories:
(i) Numeric
(ii) String
(iii) DateandTime
(iv) Binary

Page7
(i) Numeric:Thisdatatypestoresnumbers.
Int Normalsizeintegerthatcanbesignedorunsigned.Width
upto11 digits.
TinyInt Averysmallintegerthatcanbesignedorunsigned.Width
upto4digits.
SmallInt Asmallintegerthatcanbesignedorunsigned.Widthupto
5digits.
MediumInt Amediumsizeintegerthatcanbesignedorunsigned.
Widthupto9digits.
BigInt Alargeintegerthatcanbesignedorunsigned.
Float(M,D) Afloatingpointnumber.
M:-Lengthoftotalnumber D
:- Number of decimals Ex.-
Float(10,2)
10isthetotalnumberofdigitsand2isthenumberof decimals.

DOUBLE(M,D) Adoubleprecisionfloatingpointnumber.
Decimal(M,D) Floatingpointvalueswithvaryinglevelsofprecision.

(ii) String:
Char(M) Afixlengthstringbetween1to255characters.
M :-Length
Varchar(M) Avariablelengthstringbetween1to255characters.
Enum Acceptsoneofapredefinedsetofstrings.
Text Variablelengthtextwithamaximumsizeof64K.
TinyTEXT Sameastext,butwith amaximumsizeof255 bytes.
MediumTEXT Sameastext,butwith amaximumsizeof 16K.
LongTEXT Sameastext,butwith maximumsizeof 4GB.

(iii) DATEand TIME:

DATE YYYY-MM-DDformat.Example:1998-08-12
DATETIME YYYY-MM-DDHH:MM:SSformat.Example:1998-08-
1223:37:15
TIMESTAMP This is same as DATETIME data type, only without
hyphensbetweennumbers.YYYYMMDDHHMMSS format.
Example:19980812233715

TIME StoresthetimeinHH:MM:SSformat.
YEAR(M) Storesayearina2digitsor4-digits. M :-
Length
Example:YEAR(2)means98
YEAR(4)means1998

Page8
 (iv) Binary Data Type: Stores the data in binary objects, such as graphics, images,
multimedia etc.
BLOB StandsforBinaryLarge Objects.
MaximumLength=64K
TINYBLOB Maximumlength=255bytes.
MEDIUMBLOB MaximumLength=16MB
LONGBLOB Maximumlength= 4GB
3. NULL values: If a column in a row has no value, then column is said to be
null.NULLs can appear in a column if the column is not restricted by NOT NULL
orPrimary Key. You should use a null value when the actual value is not
known.Null and Zero is not equivalent. Any arithmetic expression containing a
nullalways evaluates to null.
Example:7+null=null
7+0=7 Differencebetweennullandzero.

4. Comments:Commentisatextthatisnotexecuted.Therearetwotypesofcomments that
we use in MySQL.
(i) Single line Comment: Beginning with two hyphens (--) OR beginning
with # symbol.
(ii) MultiLineComment: /*………………………………………..
…………………………………….*/

Differencebetweencharand varchar:

Char Varchar
1.Fixedlengthcharacterstring. Variablelengthcharacterstring.
2.Whenacolumnisgivendatatype as
Each value that is stored in this
char(N). If a value is shorter
columnstoresexactlyasyouspecify
thanthislengthNthenblanksare
it.Noblanksareadded,if thelength is
added,butthesizeofvalue
shorter than maximum length N.
remainsN byte.
3.Fastertoaccess. Takeslessdiskspace.

Page9
ClassificationofSQLcommands:

SQLcommandsarecategorizedintofoursublanguages:

(i) DataDefinitionLanguage(DDL)
(ii) DataManipulationLanguage(DML)
(iii) TransactionControlLanguage(TCL)
(iv) DataControlLanguage(DCL)

(i) DataDefinitionLanguage(DDL):Itconsistthecommandstocreateobjectssuch as
tables, views, indexes etc. in the database.
COMMANDS:CREATE,ALTER,DROP,RENAME,TRUNCATE

(ii) Data Manipulation Language (DML): It is used for queries. It allows you to
performdatamanipulatione.g.retrieval,insertion,deletion,modificationofdata
stored in database.
COMMANDS:SELECT,INSERT,UPDATE,DELETE

(iii) Transaction Control Language (TCL): This language allows you to manage
and control the transaction.
COMMANDS:COMMIT,ROLLBACK
 NOTE:-SavepointisalsousedinTCL.

(iv) DataControlLanguage(DCL):Thislanguageisusedtocontroldataandaccess to
the databases. It is used for protecting the data from unauthorized access.
COMMANDS: GRANT, REVOKE

SQLQUERIES:

In MySQL, to create new tables or query, the user must specify the database. This
database is called current database.

TochecktheavailabledatabaseinMySQL:-
SHOWdatabases;

To access the database:-

Syntax:- USEdatabase-name;

Example: USE school;

Page10
 Adatabasehastables.Tableshaverowsand columns.

Toshowtheavailabletablesinthedatabase:-
SHOWtables;

CREATINGDATABASE:InMySQLwecancreatethedatabasesusingCREATEDATABASE
statement.

Syntax:CREATEDATABASE<database-name>;

Example:CREATEDATABASEBank;

OPENINGDATABASE:
Syntax: USE<database-name>;

Example:USE Bank;

DROPPING DATABASES: To remove the entire database we use the DROP

DATABASE statement.

Syntax:DROPDATABASE<database-name>;

Example:DROPDATABASEBank;

CreatingTablesinMySQL:
TablesarecreatedusingCTREATETABLEcommand.

A table has rows and columns. The column name must be specified along the data type.
Each table must have at least one column.

Syntax:

CREATETABLE<table-name>(<column-name><data-type>(size),<column-name>
<data-type>(size),<column-name><data-type>(size) );

Example:

Page11
CREATE TABLE EMPLOYEE(Ecode int(6), Ename varchar(30), Dept varchar(30), city
varchar(25), sex char(1), DOB Date, salary float(12,2) );

Ecode Ename Dept City Sex Dob Salary

Fig.:EMPLOYEE
Viewing a table

structure:DESC <table-

name>;

Example:DESCEMPLOYEE;

Orwe canwrite
DESCRIBEEMPLOYEE;

Insertingdatainto table:
Therows(tuples)areaddedtorelations(tables)usingINSERTcommand.

Syntax:

INSERT INTO <table-name>[<column-name>] VALUES (<value1>, <value2>,

……….);

Example: INSERT INTO EMPLOYEE VALUES(1001, ‟Amit‟, ‟production‟,

‟Ahemedabad‟, ‟M‟, ‟1988-08-22‟, 20000.00);

The into clause specifies the target table and the value clause specifies the data to
be added to the new row of the table.

Whileinsertingdataintotables,followingpointsshouldbetakencareof:

 Characterdatashouldbeenclosedwithinsinglequotes.
 NULLvaluesaregivenasNULL,withoutanyquotes.

Page12
  If no data is available for all the columns then the column list must be included,
following the table name. Example: INSERT INTO EMPLOYEE(Ecode, Ename,
salary) VALUES(1001, „Amit‟, 20000.00);

Afterinsertingthedata,wecreatedthefollowing table:

Ecode Ename Dept City Sex DOB Salary

1001 Amit Production Ahemedabad M 1988-08-22 38000.00
1002 Sanjeev Marketing NewDelhi M 1990-09-05 32000.00
1003 Imran RND Surat M 1989-01-01 40000.00
1004 Harish RND Jaipur M 1988-01-20 40050.00
1005 Neha Marketing Guwahati F 1985-04-15 35000.00
1006 Dheeraj Production Mumbai M 1984-03-02 39000.00
1007 Vikram Marketing Shimla M 1990-10-10 31000.00
1008 Ashok Marketing Patna M 1980-09-11 40000.00
1009 Priyanka RND Gurgaon F 1990-07-23 40000.00
1010 Seema Production NewDelhi F 1989-05-16 37000.00
1011 Manish Marketing Guwahati M 1980-02-07 39050.00
Table:EMPLOYEE

CONSTRAINTS:
A constraint is a condition on a field or on set of fields. There are six types of constraints
that we use in MySQL:-

(i) NOTNULL
(ii)DEFAULT
(iii) UNIQUE
(iv) CHECK
(v) PRIMARYKEY
(vi) FOREIGNKEY

(i) NOTNULLConstraint:ThisconstraintensuresthatacolumncannothaveNULL
value.Bydefault,acolumncanholdNULL.IfyouaddaNOTNULLconstrainton a
column it cannot hold a NULL.

Page13
HowtoapplyNOTNULL constraint:-

(a) Whenwearecreating a table:-

Example:-

CREATETABLEEMPLOYEE(Ecodeint(6)NOTNULL,Ename
varchar(30)NOTNULL,Deptvarchar(25));

NowEcodeandEnamecolumnscannotincludeNULL.

(b) AddNOTNULLconstraintinaexistingtablecolumn:

Example:-

ALTER TABLE EMPLOYEE

MODIFYEcodeint(6)NOTNULL;

(ii) DEFAULT Constraint:- The DEFAULT constraint provides a default value to a

column.Whenauserdoesnotenterthevalueforacolumn,automaticallythedefined
default value is inserted into the field.

Howtoapplydefaultconstraint:-

(a) Whilewearecreatingatable:-

Example:-

CREATETABLEEMPLOYEE(Ecodeint(6),Enamevarchar(30),Salary float(12,2)
DEFAULT25000.00);

(b) AddDEFAULTconstraintinanexistingtablecolumn:-

Example:-

ALTERTABLEEMPLOYEE
MODIFYSalaryfloat(12,2)DEFAULT25000.00;

(iii) UNIQUEConstraint:-Itensuresthatallvaluesinacolumnaredifferent.

Howtoapplyuniqueconstraints:-

(a) Whilewearecreatingatable:-

Page14
 Example:-

CREATETABLEEMPLOYEE(Ecodeint(6)UNIQUE,Ename
varchar(30),Deptvarchar(25));

(b) Adduniqueconstraintinanexistingtablecolumn:
Example:-
ALTER TABLE EMPLOYEE
MODIFYEcodeint(6)UNIQUE;

(iv) CHECKconstraint:-Itmakesurethatallthevaluesinacolumnsatisfycertain
criteria.

Howtoapplycheckconstraint:-

(a) Whilewearecreatingatable:-
Example:-

CREATETABLEEMPLOYEE(Ecodeint(11)CHECK(Ecode>0),Ename
varchar(30));

(b) Addcheckconstraintonanexistingtablecolumn:-
Example:-

ALTERTABLEEMPLOYEE
ADD CHECK(Ecode>0);

(v) PrimaryKeyConstraint:-Thisisusedtouniquelyidentifyarowinatable.

Howtoapplyprimarykey constraint:-

(a) Whilecreatingatable:-
Example:-
CREATETABLEEMPLOYEE(Ecodeint(6)PRIMARYKEY,Ename
varchar(30),cityvarchar(30));
(b) Addprimarykeyconstraintonanexistingtablecolumn:-
Example:-
ALTER TABLE EMPLOYEE
ADDPRIMARYKEY(Ecode);

Page15
 (vi) Foreign Key Constraint:-Whenever two tables are related by a common
column, then the related column in the parent table should be either declared a
PRIMARY KEY or UNIQUE KEY and the related column in the child table
should have FOREIGN KEY constraint.

Syntax:-
Foreignkey(<column-to-be-designated-as-foreign-key>)referencesparent-table
(<primary-key-of parent table>);

Primary Key TABLE:CUSTOMER(Parent Table)

Cust_ID First_Name Last_Name

Primary Key TABLE:ORDERS(ChildTable)ForeignKey

Order_No. Order_Date Cust_ID Amount

Primarykeyofparenttablebecomestheforeignkeyforthechild table.

(a) Whilecreatingatable:-
Example:-
CREATETABLEORDERS(Order_no int PRIMARYKEY, Order_Date Date,
Cust_ID integer, Amount double, foreign key(cust_ID) references
CUSTOMER(cust_ID));

(b) AddforeignkeyconstraintthroughALTERtable:-
Example:-
ALTERTABLEORDERS
ADDforeignkey(cust_ID)referencesCUSTOMER(cust_ID);

Page16
Droppinga constraint:-

Thesyntaxis:-

ALTERTABLEtable-name

DROPconstraint<constraint-name>;

Example:
Toremoveprimarykeyconstraint:-

ALTERTABLEEMPLOYEE DROP

PRIMARY KEY;

Toremoveforeignkeyconstraint:-

ALTER TABLE table1

DROPFOREIGNKEYfk1;

Inthisquerytablenameistable1andforeignkeyconstraintisfk1.

ADDINGNEWCOLUMNINATABLE:-
Syntax:-

ALTERTABLEtable-name

ADDcol-namedata-type(size);

Example:Addanewcolumn addressinemployeetable.

ALTERTABLEEMPLOYEE

ADDaddressvarchar(50);

MODIFYINGATABLECOLUMN:

(i) Tochangethedata-typeandsizeofthecolumn,weusetheMODIFY
command.
(ii) Tochangethenameofthecolumn,weusetheCHANGEcommand.

Page17
(i) Tochangethedata-typeandsizeofthecolumn,weusetheMODIFY
command:-
Thesyntaxis:-

ALTERTABLEtable-name

MODIFY(Col_namenewdatatype(newsize));

Example:

ALTERTABLEEMPLOYEE

MODIFYcitychar(30);

(ii) Tochangethenameofthecolumn,weusetheCHANGE command:-

ALTERTABLE<table-name>

CHANGEoldcolumnnamenewcolumnnamecol-definition;

Example:

ALTERTABLEEMPLOYEE

CHANGEDeptDepartmentvarchar(30);

DROPPINGATABLE:
Toremovetheentirestructureofthetablecompletely,weusetheDROPTABLE command.

Syntax:
DROPTABLE<table-name>;

Example:

DROPTABLEEMPLOYEE;

Page18
 MANIPULATINGDATAOFATABLE:-

(i) Retrievingdata:SELECTcommand
(ii) Insertingdata:INSERTcommand
(iii) Deletingdata :DELETEcommand
(iv) Modification :UPDATEcommand

(i) SELECTCommand:-ASELECTcommandretrievesinformationfromthe database.

(ii) INSERTCommand:-Thiscommandisusedtoinsertthedatain table.

NOTE:-Wehavealreadydiscussedaboutthiscommand.

(iii) DELETECommand:-Itmeansdeletetheinformationfromthetable. Thiscommand is

used to remove the rows from the table.
 SpecificrowsaredeletedwhenyouspecifytheWHEREclause.
 AllrowsinthetablearedeletedifyouomittheWHEREclause. The

syntax is:

DELETEFROM<table-name>

WHERE<condition>;

Example:-Deletethoserowswhosedepartmentisproduction.

Solution: DELETEFROMEMPLOYEE

WHEREdept=‟production‟;

Example:-DeletealltherecordsofEMPLOYEEtablehavingsalarylessthan 35000.

Solution: DELETEFROMEMPLOYEE

WHERE Salary<35000;

(iv) UPDATE command:- Valuesofasinglecolumnorgroupofcolumnscanbe

updated.
Thesyntaxis:- UPDATEtable-name
SETcolumn_name=value
WHERE condition;

Page19
Example:-ChangethesalaryofVikramto36000.

Solution:UPDATEEMPLOYEE

SET

salary=36000WHEREEna

me=‟Vikram‟;

Example:- Increaseeveryemployeesalaryby10%.

Solution:UPDATE EMPLOYEE

SETsalary=salary+(salary*0.1);

MAKINGSIMPLEQUERIES:-

InSQLqueries,weusethreeclausesmostly:-

(i) SELECT:- Whattoselect

(ii) FROM:- WhichTable
(iii) WHERE:- Conditiontosatisfy

(i) SELECT:-
ASELECTcommandisusedtoretrieveinformationfromatable.
 Ifyouwanttoselecttheallcolumnsfromatable,thenweusethe
asterisk(*) in SELECT clause.
Example:- SELECT * FROMEMPLOYEE;
 Todisplayspecificcolumnsofthetablebyspecifyingthecolumnnames,
separated by commas.
Example: - SELECTEcode,Ename,salary
FROM EMPLOYEE;
(ii) FROM:-
AFROMclause,specifiesthetablenamethatcontainsthecolumns.

(iii) WHERE:-
AWHEREclause,specifiesthecondition.
Syntax:-SELECTcolumn_name
FROMtable_name
WHEREcondition;
Page20
SOMEIMPORTANATPOINTS:-

 SQLstatementsarenotcasesensitive.
 ToendtheSQLcommand,wewritethesemicolon(;)attheendofalinefollowed by
<ENTER>.

 SelectingAllColumns:-Toselectallthecolumns,weuseasterisk(*)inSELECT
statement.
Example:- SELECT *
FROM EMPLOYEE;

 Selecting Specific Columns:- To display the specific columns of the table,

writecolumns name, separated by commas.
Example:- SELECT Ecode,Ename,salary
FROM EMPLOYEE;

 Eliminatingredundantdata:-TheDISTINCTkeywordeliminatesduplicaterows
from the results. DISTINCT is used in SELECT statement.
Example:- SELECT DISTINCT(Dept)
FROM EMPLOYEE;

ALLkeyword:-
SQLallowsustousethekeywordALLtospecifyexplicitlythatduplicatesarenot removed.

Example: SELECT ALL Dept

FROM EMPLOYEE;

ArithmeticOperations:-
TheSELECTclausemayalsocontainarithmeticexpressionsinvolvingtheoperators+,-
,*and/operatingonconstantsor attributes.

Example:-Findthenewsalaryofeveryemployeeincreasedby25%.

SELECTEname,salary,salary*0.25

FROMEMPLOYEE;

Page21
COLUMN ALIAS:- Youcanchangeacolumnheadingbyusingacolumnalias.

Example:- SELECT Ename as Name

FROM EMPLOYEE;

ExamplesofQueries:-
1. Listthenameanddepartmentofthoseemployees wheredepartmentisproduction.
Solution:- SELECT Ename, Dept
FROM EMPLOYEE
WHERE Dept=‟production‟;
2. Findthenameandsalaryofthoseemployeeswhosesalaryismorethan20000.
Solution:- SELECT Ename, salary
FROM EMPLOYEE
WHERE salary> 20000;
3. DisplaythenameofthoseemployeeswholiveinNewDelhi.
Solution:- SELECT Ename, city
FROM EMPLOYEE
WHERE city=‟NewDelhi‟;
4. ListthenameoffemaleemployeesinEMPLOYEEtable.
Solution:- SELECT Ename
FROM EMPLOYEE
WHERE sex=‟F‟;
5. Displaythenameanddepartmentofthoseemployeeswhoworkinsuratandsalary is
greater than 25000.
Solution:- SELECT Ename,Dept
FROM EMPLOYEE
WHERE city=‟surat‟andsalary>25000;

Orwecanwritethisqueryinanotherway:
Solution:- SELECT Ename,Dept
FROM EMPLOYEE
WHERE city=‟surat‟&&salary>25000;

6. DisplaythenameofthosefemaleemployeeswhoworkinMumbai.
Solution:- SELECT Ename
FROM EMPLOYEE
WHERE sex=‟F‟andcity=‟Mumbai‟;
7. DisplaythenameofthoseemployeeswhosedepartmentismarketingorRND.

Page22
 Solution:- SELECT Ename
FROM EMPLOYEE
WHERE Dept=‟marketing‟ORDept=‟RND‟;
8. Listthenameofemployeeswhoarenot males.
Solution:- SELECT Ename,Sex
FROM EMPLOYEE
WHERE sex!=‟M‟;

QUERIESFORSPECIALOPERATORS:-

(i) BETWEEN :- Betweentwovalues

(ii) IN :- Matchavalueinthelist
(iii) LIKE :- Matchacharacterpattern
(iv) IS NULL :- Valueis null.

(i) BETWEEN:-

Example:-Findthenameandsalaryofthoseemployeeswhosesalaryis
between 35000 and 40000.
Solution:-
SELECT Ename,salary
FROM EMPLOYEE
WHERE salaryBETWEEN35000and40000;

Orwecanwritethisqueryinanotherway:
SELECT Ename,salary
FROM EMPLOYEE
WHERE salary>35000andsalary<40000;

(ii) IN :-
Example:-Findthenameofthoseemployeeswholiveinguwahati,surator jaipur
city.
Solution:-
SELECT Ename, city
FROM EMPLOYEE
WHERE cityIN(„Guwahati‟,‟Surat‟,‟Jaipur‟);

Page23
(iii) LIKE:-
%:-Itrepresentsanysequenceofzeroormore characters.
_ :-Representsanysinglecharacter.

Example:- Displaythenameofthoseemployeeswhosenamestartswith„M‟.

Solution:-
SELECT Ename
FROM EMPLOYEE
WHERE EnameLIKE „M%‟;

Example:-Displaythenameofthoseemployeeswhosedepartmentnameendswith „a‟.

Solution:-
SELECT Ename
FROM EMPLOYEE
WHERE DeptLIKE„%a‟;

Example:- Listthenameofemployeeswhosenamehaving„e‟asthesecond character.

Solution:-
SELECT Ename
FROM EMPLOYEE
WHERE EnameLIKE„_e%‟;
(iv) ISNULL:-

Example:- Listthenameofemployeesnotassignedtoanydepartment.

Solution:-
SELECT Ename
FROM EMPLOYEE
WHERE DeptISNULL;

IFNULL()function:-
Ifyouwanttosubstitutenullwithavalueintheoutput,youcanuseIFNULL()function.

Syntax:-
IFNULL(<column-name>,valuetobesubstitute)

Page24
Example:-

SELECT Ename,IFNULL(salary,“5000”)

FROM EMPLOYEE;

ORDERBYclause:-
You can sort theresult in aspecificorderusing ORDERBYclause.Thesorting can
be done either in ascending or descending order. The default order is ascending.

Example:- Displaythelistofemployeesindescendingorderofemployeecode.

Solution:-
SELECT *
FROM EMPLOYEE
ORDERBY ecodeDESC;
Example:- Displaytheemployeecode,nameinascendingorderofsalary.

Solution:-
SELECT Ecode,Ename,salary
FROM EMPLOYEE
ORDERBY salaryasc;
SupposethatwewishtolisttheentireEMPLOYEErelationindescendingorderofsalary.
Ifseveralemployeeshavethesamesalary,weordertheminascendingorderbyemployee code.
We express this query in SQL as follows:-

SELECT *
FROM EMPLOYEE
ORDERBY salarydesc,Ecodeasc;

AGGREGATEFUNCTIONS:

Aggregatefunctionsarefunctionsthattakeacollectionofvaluesasinputandreturna single
value. SQL offers five types of aggregate functions:-

(i) Avg() :-Tofindoutthe average

(ii) Min() :-Minimumvalue
(iii) Max() :-Maximumvalue
(iv) Sum() :-Tocalculatethetotal
(v) Count() :- For counting

Page25
NOTE:-Theinputtosum()andavg()mustbeacollectionofnumbers,buttheother functions can
operate on non-numeric data types e.g.string.

Findtheaveragesalaryoftheemployeesinemployee table.
SELECT avg(salary)
FROM EMPLOYEE;

Insomecircumstance,wewouldliketoapplytheaggregatefunctionnotonlytoasingle set of
tuples, but also to a group of sets of tuples. We specify this wish in SQL using the
group by clause.
Theattributesgiveninthegroupbyclauseareusedtoformgroups.

Example:-Findtheaveragesalaryateach department.
Solution: - SELECT Dept,avg(salary)
FROM EMPLOYEE
group by Dept;

Output for this query

Dept Avg(salary)
Production 38000.00
Marketing 35410.00
RND 40016.66

FindtheminimumsalaryinEMPLOYEEtable.
Solution:-
SELECT min(salary)
FROM EMPLOYEE;

FindtheminimumsalaryofafemaleemployeeinEMPLOYEEtable.
Solution:-
SELECT Ename,min(salary)
FROM EMPLOYEE
WHERE sex=‟F‟;

FindthemaximumsalaryofamaleemployeeinEMPLOYEEtable.
Solution:-
SELECT Ename,max(salary)
FROM EMPLOYEE
WHERE sex=‟M‟;

Page26
FindthetotalsalaryofthoseemployeeswhoworkinGuwahati city.
Solution:-
SELECT sum(salary)
FROM EMPLOYEE
WHERE city=‟Guwahati‟;
FindthetotalsalaryofallemployeesinEMPLOYEErelation.
Solution:-
SELECT sum(salary)
FROM EMPLOYEE;

FindthenumberoftuplesintheEMPLOYEE relation.
Solution:-
SELECT count(*)
FROM EMPLOYEE;

CountthenumberofemployeeswhoworkinRNDdepartment.
Solution:-
SELECT count(*)
FROM EMPLOYEE
WHERE Dept=‟RND‟;

JOINS:

AnSQLJOINclauseisusedtocombinerowsfromtwoormoretables,basedona common field

between them.

Considertwotables,CUSTOMERandORDERS:

TABLE: CUSTOMER
CUST_ID CUST_NAME GENDER CITY
101 Rahul M DELHI
105 Sushil M GOA
206 Sunita F MUMBAI
517 Gaurav M VADODARA

TABLE: ORDERS
ORDER_ID CUST_ID ORDER_DATE AMOUNT
14578 105 10/04/2016 1850.50
25685 517 15/03/2015 8569.00
89632 222 01/01/2016 5362.20
78451 105 30/05/2015 450.00

Page27
TypesofJoins:
1. CartesianProduct
2. EquiJoin
3. NaturalJoin

1. Cartesianproduct:Itisknownascrossjoin.Thenumberoftuplesinnewrelation
isequaltoproductofnumberoftuplesofthetwotablesonwhichCartesianproduct is
performed.

SELECTName,Amount

FROMCUSTOMER,BORROWER;

**Cartesianproductisformedwhennojoinconditionsexist.

2. EquiJoin:Columnsarecomparedforequality.

There are 4 types of equi join:

a. INNERJOIN
b. LEFTJOIN
c. RIGHTJOIN
d. FULLJOIN

a. INNER JOIN: Returns all rows when there is at least one match in BOTH tables.
This joining is known as simple join. You can use JOIN keyword also in the place
of INNER JOIN. Both will give same result.

Syntax:

SELECTcolumn_name(s)
FROMtable1
INNERJOINtable2
ONtable1.column_name=table2.column_name;

Example: OUTPUT
SELECTCUST_NAME,ORDER_ID
CUST_NAME ORDER_ID
FROM CUSTOMER
SHUSHIL 14578
INNER JOIN ORDERS
SHUSHIL 78451
ON
GAURAV 25685
CUSTOMER.CUST_ID=ORDER.CUST_ID;

Page28
 b. LEFT JOIN: Return all rows from the left table, and the matched rowsfrom
theright table

c. RIGHTJOIN:Returnallrowsfromtherighttable,andthematchedrowsfromthe left
table

d. FULLJOIN:ReturnallrowswhenthereisamatchinONEofthe tables

3. NaturalJoin:Onlyoneoftheidenticalcolumnsexists.

**TheequijoinandNaturalJoinareequivalentexceptthatduplicatecolumnsareeliminated in the
Natural Join.

MySQLdatabaseconnectivitywithPython:

 Installpython
 InstallMySQL
 InstallMySQLDriverusingfollowingcommand:(InCommandPrompt):
pipinstallmysql-connector
Note:Makesureyourcomputerisconnectedwithinternet.

 To verify, whetherthe connectorisproperlyinstalled ornot, open python shell and

type the following command:
>>>importmysql.connector
>>>

Ifthecommandsuccessfullyruns(withoutanyerror),thentheMySQLconnectoris successfully
installed.

 Now, open MySQL and check the current user, by typing the following command
in MySQL:

SELECTcurrent_user();

Page29
 Connect MySQL database with python. For this, open Python IDLE and write the
following code in python file.

CODE:

import mysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",passwd="computer")
print(demodb)

Ifyougetthefollowingoutput,thentheconnectionmadesuccessfully.

OUTPUT:

 AftermakingsuccessfulconnectionbetweenpythonandMySQL,nowcreatea database
in MySQL through python. For that, write the following code in python:
importmysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",passwd="computer")
democursor=demodb.cursor( )
democursor.execute("CREATEDATABASEEDUCATION")

Page30
  After successful execution of the following code, check in MySQL, whether
EDUCATION database has been created or not. for that, write the following
command in MySQL:

 Ifyouwanttocheckthecreateddatabasethroughpython,writethefollowingpython code
to show the present databases in MySQL.
importmysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",passwd="computer")
democursor=demodb.cursor()
democursor.execute("SHOWDATABASES")
for i in democursor:
print(i)
OUTPUT:

Here,wecanseethatEDUCATIONdatabasehasbeen created.

Create a table in database:

CODE:

importmysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",
passwd="computer", database="EDUCATION")
democursor=demodb.cursor( )
democursor.execute("CREATE TABLE STUDENT (admn_no int primary key,
snamevarchar(30),genderchar(1),DOBdate,streamvarchar(15),marksfloat(4,2))")

Page31
 Toverifythetablecreatedornot,writethefollowingcodeinpython:

importmysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",
passwd="computer", database="EDUCATION")
democursor = demodb.cursor( )
democursor.execute("showtables") for
i in democursor:
print(i)

OUTPUT:

Insertthedatainthetable:

importmysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",
passwd="computer", database="EDUCATION")
democursor=demodb.cursor( )
democursor.execute("insertintostudentvalues(%s,%s,%s,%s,%s,%s)",(1245, 'Arush',
'M','2003-10-04', 'science', 67.34))
demodb.commit()

Fetchthedatafromtable:

importmysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",
passwd="computer", database="EDUCATION")

Page32
democursor=demodb.cursor( )
democursor.execute("select*fromstudent") for
i in democursor:
print(i)

OUTPUT:

Updatetherecord:

importmysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",
passwd="computer", database="EDUCATION")
democursor=demodb.cursor( )
democursor.execute("updatestudentsetmarks=55.68whereadmn_no=1356")
demodb.commit( )

Deletearecord:

importmysql.connector
demodb=mysql.connector.connect(host="localhost",user="root",
passwd="computer", database="EDUCATION")
democursor=demodb.cursor( )
democursor.execute("deletefromstudentwhereadmn_no=1356")
demodb.commit( )

Page33