Chapter 9: More On Database & SQL – Advanced Concepts Informatics Practices Class XII (CBSE Board)

Revised as per CBSE Curriculum 2015

Visit www.ip4you.blogspot.com for more…. Authored By:- Rajesh Kumar Mishra, PGT (Comp.Sc.) Kendriya Vidyalaya Upper Camp, Dehradun (Uttarakhand) e-mail : [email protected]

Integrity Constraints One of the major responsibility of a DBMS is to maintain the Integrity of the data i.e. Data being stored in the Database must be correct and valid. An Integrity Constraints or Constraints are the rules, condition or checks applicable to a column or table which ensures the integrity or validity of data. The following constraints are commonly used in MySQL.  NOT NULL  PRIMARY KEY Most of the constraints are applied with  UNIQUE * Column definition which are called Column-Level (in-line Constraints) ,but  DEFAULT * some of them may be applied at column  CHECK * Level as well as Table-Level (Out-line  FOREIGN KEY * constraints) i.e. after defining all the columns. Ex.- Primary Key & Foreign Key

*

Not included in the syllabus (recommended for advanced learning)

Type of Constraints S.N

Constraints

Description

1

NOT NULL

Ensures that a column cannot have NULL value.

2

DEFAULT

Provides a default value for a column, when nothing is given.

3

UNIQUE

Ensures that all values in a column are different.

4

CHECK

Ensures that all values in a column satisfy certain condition.

5

PRIMARY KEY

Used to identify a row uniquely.

6

FOREIGN KEY

Used to ensure Referential Integrity of the data.

UNIQUE v/s PRIMARY KEY  UNIQUE allows NULL values but PRIMERY KEY does not.  Multiple column may have UNIQUE constraints, but there is only one PRIMERY KEY constraints in a table.

Implementing Primary Key Constraints Defining Primary Key at Column Level: mysql> CREATE TABLE Student ( StCode char(3) NOT NULL PRIMARY KEY, Stname char(20) NOT NULL, ……………………….. );

Defining Primary Key at Table Level: mysql> CREATE TABLE Student ( StCode char(3) NOT NULL, Stname char(20) NOT NULL, ……………………….. PRIMARY KEY (StCode) );

Constraint is defined after all column definitions.

A Composite (multi-column) Primary key can be defined as only a Table level whereas Single-column Primary key can be defined in both way i.e. Column level or Table level.

Implementing Constraints in the Table mysql> CREATE TABLE Student (StCode char(3) NOT NULL PRIMARY KEY, Stname char(20)

NOT NULL,

StAdd

varchar(40),

AdmNo

char(5) UNIQUE,

StSex

char(1) DEFAULT

StAge

integer CHECK (StAge>=5) );

CREATE TABLE EMP ( Code char(3)

Column level constraints are defined with column definitions.

‘M’,

NOT NULL,

Name char(20) NOT NULL, City varchar(40), Pay Decimal(10,2), PRIMARY KEY (Code) );

Table level constraints are defined after all column definitions.

Implementing Foreign Key Constraints  A Foreign key is non-key column in a table whose value is derived from the Primary key of some other table.  Each time when record is inserted or updated in the table, the other table is referenced. This constraints is also called Referential Integrity Constraints.  This constraints requires two tables in which Reference table (having Primary key) called Parent table and table having Foreign key is called Child table.

Foreign Key

EMPLOYEE

DEPARTMENT

EmpID Name City Sal DeptNo Child Table

DeptNo DeptName Head Location Parent Table

Primary key

Implementing Foreign Key CREATE TABLE Department ( DeptNo char(2) NOT NULL PRIMARY KEY, DeptName char(10) NOT NULL, Head char(30) );

Cont.. Parent table

Child Table in CREATE TABLE Employee which Foreign ( EmpNo char(3) NOT NULL PRIMARY KEY, key is defined. Name char(30) NOT NULL, City char(20), Parent table and column Sal decimal(8,2), to be referenced.. DeptNo char(2), FOREGIN KEY (DeptNo) REFERENCES Departmet (DeptNo));  A Table may have multiple Foreign keys.  Foregn key may have repeated values i.e. Non-Key Column

Modifying Table Constraints  Adding new column and Constraints ALTER TABLE ADD [ ][] mysql> ALTER TABLE Student ADD (TelNo Integer); mysql> mysql> mysql> mysql>

ALTER ALTER ALTER ALTER

TABLE TABLE TABLE TABLE

Student Emp ADD Emp ADD Emp ADD

ADD (Age Integer CHECK (Age>=5)); Sal Number(8,2) DEFAULT 5000 ; PRIMARY KEY (EmpID); PRIMARY KEY (Name,DOB);

 Modifying Existing Column and Constraints ALTER TABLE
MODIFY [ ] [] mysql> ALTER TABLE Student MODIFY Name VARCHAR(40); mysql> ALTER TABLE Emp MODIFY (Sal DEFAULT 4000 ); mysql> ALTER TABLE Emp MODIFY (EmpName NOT NULL);

Modifying Table Constrains

cont..

 Removing Column & Constraints ALTER TABLE
DROP | mysql> ALTER TABLE Student DROP TelNo; mysql> ALTER TABLE Emp DROP JOB, DROP Pay; mysql> ALTER TABLE Student DROP PRIMARY KEY;

 Changing Column Name of Existing Column ALTER TABLE
CHANGE

mysql> ALTER TABLE Student CHANGE Name Stname Char(40);

Viewing & Disabling Constraints  To View the Constraints The following command will show all the details like columns definitions and constraints of EMP table. mysql> SHOW CREATE TABLE EMP; Alternatively you can use DESCribe command: mysql> DESC EMP;

 Enabling / Disabling Foreign Key Constraint  You may enable or disable Foreign key constraints by setting the value of FOREIGN_KEY_CHECKS variable.  You can’t disable Primary key, however it can be dropped (deleted) by Alter Table… command. 

To Disabling Foreign Key Constraint mysql> SET FOREIGN_KEY_CHECKS = 0;



To Enable Foreign Key Constraint mysql> SET FOREIGN_KEY_CHECKS = 1;

Grouping Records in a Query  Some time it is required to apply a Select query in a group of records instead of whole table.  You can group records by using GROUP BY clause with Select command. A group column is chosen which have non-distinct (repeating) values like City, Job etc.  Generally, the following Aggregate Functions [MIN(), MAX(), SUM(), AVG(), COUNT()] etc. are applied on groups. Name

Purpose

SUM()

Returns the sum of given column.

MIN()

Returns the minimum value in the given column.

MAX()

Returns the maximum value in the given column.

AVG()

Returns the Average value of the given column.

COUNT()

Returns the total number of values/ records as per given column.

Aggregate Functions & NULL Values Consider a table Emp having following records asEmp Code

Name

Sal

E1

Ram Kumar

NULL

E2

Suchitra

4500

E3

Yogendra

NULL

E4

Sushil Kr

3500

E5

Lovely

4000

mysql> mysql> mysql> mysql> mysql> mysql>

Select Select Select Select Select Select

Sum(Sal) from EMP; Min(Sal) from EMP; Max(Sal) from EMP; Count(Sal) from EMP; Avg(Sal) from EMP; Count(*) from EMP;

Aggregate function ignores NULL values i.e. NULL values does not play any role in calculations.

     

12000 3500 4500 3 4000 5

Aggregate Functions & Group An Aggregate function may applied on a column with DISTINCT or ALL keyword. If nothing is given ALL is assumed.  Using SUM () This function returns the sum of values in given column or expression. mysql> mysql> mysql> mysql> mysql>

Select Select Select Select Select

Sum(Sal) from EMP; Sum(DISTINCT Sal) from EMP; Sum (Sal) from EMP where City=‘Kanpur’; Sum (Sal) from EMP Group By City; Job, Sum(Sal) from EMP Group By Job;

 Using MIN () This functions returns the Minimum value in the given column. mysql> Select Min(Sal) from EMP; mysql> Select Min(Sal) from EMP Group By City; mysql> Select Job, Min(Sal) from EMP Group By Job;

Aggregate Functions & Group  Using MAX () This function returns the Maximum value in given column. mysql> Select Max(Sal) from EMP; mysql> Select Max(Sal) from EMP where City=‘Kanpur’; mysql> Select Max(Sal) from EMP Group By City;

 Using AVG () This functions returns the Average value in the given column. mysql> Select AVG(Sal) from EMP; mysql> Select AVG(Sal) from EMP Group By City;

 Using COUNT (<*|column>) This functions returns the number of rows in the given column. mysql> Select Count (*) from EMP; mysql> Select Count(Sal) from EMP Group By City; mysql> Select Count(*), Sum(Sal) from EMP Group By Job;

Aggregate Functions & Conditions You may use any condition on group, if required. HAVING clause is used to apply a condition on a group. mysql> Select Job, Sum(Pay) from EMP ‘Having’ is Group By Job HAVING Sum(Pay)>=8000; used with mysql> Select Job, Sum(Pay) from EMP Group By Group By Job HAVING Avg(Pay)>=7000; Clause only. mysql> Select Job, Sum(Pay) from EMP Group By Job HAVING Count(*)>=5; mysql> Select Job, Min(Pay),Max(Pay), Avg(Pay) from EMP Group By Job HAVING Sum(Pay)>=8000; mysql> Select Job, Sum(Pay) from EMP Where City=‘Dehradun’ Group By Job HAVING Count(*)>=5; Where clause works in respect of whole table but Having works on Group only. If Where and Having both are used then Where will be executed first.

Displaying Data from Multiple Tables - Join Query

Some times it is required to access the information from two or more tables, which requires the Joining of two or more tables. Such query is called Join Query. MySQL facilitates you to handle Join Queries. The major types of Join is as follows   

Cross Join (Cartesian Product) Equi Join Non-Equi Join Natural Join

Cross Join – Mathematical Principle Consider the two set A= {a,b} and B={1,2} The Cartesian Product i.e. AxB = {(a,1) (a,2) (b,1) (b,2)} Similarly, we may compute Cross Join of two tables by joining each Record of first table with each record of second table. R

RxS

S

A

B

C

C

X

Y

p

q

s

s

q

r

m n

t

t

n

m

o

p

s

o

p

s

l

m u

X

The table will contain (4x3=12) rows and 6 columns.

A

B

C

C

X

Y

p

q

s

s

q

r

p

q

s

t

n

m

p

q

s

o

p

s

m

n

t

s

q

r

m

n

t

t

n

m

m

n

t

o

p

s









..

..

l

m

u

o

p

s

Equi Join – Mathematical Principle In Equvi Join, records are joined on the equality condition of Joining Column. Generally, the Join column is a column which is common in both tables. Consider the following table R and S having C as Join column. R

S

T (Equi Join)

A

B

C

C

X

Y

p

q

s

s

q

r

m n

t

t

n

m

o

p

s

o

p

s

l

m u

A

B

C

C

X

Y

p

q

s

s

q

r

m

n

t

t

n

m

o

p

s

s

p

r

The result table will contain 6 columns but records are selected those are having Equal value for C column in both table.

Non-Equi Join – Mathematical Principle In Non-Equi Join, records are joined on the condition other than Equal operator (>,<,<>,>=,<=) for Joining Column (common column). Consider the following table R and S having C as Join column and <> (not equal) operator is applied in join condition. R S T (Non-Equi Join) A

B

C

C

X

Y

p

q

s

s

q

r

m n

t

t

n

m

o

p

s

o

p

s

l

m u

<>

The result table will contain 6 columns but records are selected those are having not- equal value for C column in both table.

A

B

C

C

X

Y

p

q

s

t

n

m

p

q

s

o

p

s

m

n

t

s

p

r

m

n

t

o

p

s

o

p

s

t

n

m

o

p

s

o

p

s

l

m

u

s

q

R

l

m

u

t

n

M

l

m

u

o

p

s

Natural Join – Mathematical Principle The Natural Join is much similar to Equi Join i.e. records are joined on the equality condition of Joining Column except that the common column appears one time. Consider the following table R and S having C as Join column. R

S

T (Natural Join)

A

B

C

C

X

Y

p

q

s

s

q

r

m n

t

t

n

m

o

p

s

o

p

s

l

m u

A

B

C

X

Y

p

q

s

q

r

m

n

t

n

m

o

p

s

p

r

The result table will contain 5 columns (common column is eliminated ) but records are selected those are having Equal value for C column in both table.

Implementing Join Operation in MySQL Foreign Key

Consider the two tables EMP and DEPT Primary Key

EMP

Primary Key

DEPT

EmpID

EName

City

Job

Pay

DeptNo

E1

Amitabh

Mumbai

Manager

50000

D1

E2

Sharukh

Delhi

Manager

40000

D2

E3

Amir

Mumbai

Engineer

30000

D1

E4

Kimmi

Kanpur

Operator

10000

D2

E4

Puneet

Chennai

Executive

18000

D3

E5

Anupam

Kolkatta

Manager

35000

D3

E6

Syna

Banglore

Secretary

15000

D1



….

….

….





DeptNo

DName

Location

D1

Production Mumbai

D2

Sales

Delhi

D3

Admn

Mumbai

D4

Research

Chennai

Suppose we want complete details of employees with their Deptt. Name and Location…… this query requires the join of both tables

How to Join ? MySQL offers different ways by which you may join two or more tables.

 Method 1 : Using Multiple table with FROM clause The simplest way to implement JOIN operation, is the use of multiple table with FROM clause followed with Joining condition in WHERE clause. Select * From EMP, DEPT Where Emp.DeptNo = Dept.DeptNo ;

To avoid ambiguity you should use Qualified name i.e.
.

If common column are differently spelled then no need to use Qualified name.

 Method 2: Using JOIN keyword MySQL offers JOIN keyword, which can be used to implement all type of Join operation. Select * From EMP JOIN DEPT ON Emp.DeptNo=Dept.DeptNo ;

Using Multiple Table with FROM clause The General Syntax of Joining table isSELECT < List of Columns> FROM WHERE [Order By ..] [Group By ..]

 You may add more conditions using AND/OR NOT operators, if required.  All types of Join (Equi, No-Equi, Natural etc. are implemented by changing the Operators in Joining Condition and selection of columns with SELECT clause. Ex. Find out the name of Employees working in Production Deptt.

Select Ename From EMP, DEPT Where Emp.DeptNo=Dept.DeptNo AND Dname=‘Production’; Ex. Find out the name of Employees working in same city from where they belongs (hometown).

Select Ename From EMP, DEPT Where Emp.DeptNo=Dept.DeptNo And City=Location;

Using JOIN keyword with FROM clause MySQL ‘s JOIN Keyword may be used with From clause. SELECT < List of Columns> FROM JOIN ON [WHERE ] [Order By ..] [Group By ..] Ex. Find out the name of Employees working in Production Deptt. Select Ename From EMP JOIN DEPT ON Emp.DeptNo=Dept.DeptNo Where Dname=‘Production’; Ex. Find out the name of Employees working in same city from where they belongs (hometown) . Select Ename From EMP JOIN DEPT ON Emp.DeptNo = Dept.DeptNo WHERE City=Location;

Nested Query (A query within another query) Sometimes it is required to join two sub-queries to solve a problem related to the single or multiple table. Nested query contains multiple query in which inner query evaluated first. The general form to write Nested query isSelect …. From
Where (Select Column1 From
[Where ]) Ex. Find out the name of Employees working in Production Deptt.

Select Ename From EMP Where DeptNo = (Select DeptNo From DEPT Where DName=‘Production’); Ex. Find out the name of Employees who are getting more pay than ‘Ankit’. Select Ename From EMP Where Pay >= (Select Pay From EMP Where Ename=‘Ankit’ );

Union of Tables Sometimes it is required to combine all records of two tables without having duplicate records. The combining records of two tables is called UNION of tables. UNION Operation is similar to UNION of Set Theory. E.g. If set A= {a,c,m,p,q} and Set B= {b,m,q,t,s} Then AUB= {a,c,m,p,q,b,t,s} [All members of Set A and Set B are taken without repeating] Select …. From [Where ] UNION [ALL] Select …. From [Where ]; Ex. Select

UNION Select

Ename From PROJECT1 Ename From PROJECT2 ;

Both tables or output of queries must be UNION compatible i.e. they must be same in column structure (number of columns and data types must be same).

Chapter 9-MySQL-Advanced.pdf

Integrity Constraints. One of the major responsibility of a DBMS is to maintain the. Integrity of the data i.e. Data being stored in the Database. must be correct and ...

419KB Sizes 16 Downloads 338 Views

Recommend Documents

Chapter Tour Chapter
Pictures with captions/ Info graphics: Charts and/or maps (page with title):. Biography (People, some info):. Chapter Objectives: Primary Source Documents (Title ...

Chapter 1.2 Chapter 1.4.1
Disk Operating System (DOS). What is DOS, and why learn about it? Microsoft developed the Disk Operating System (DOS) in 1981. DOS, which is sometimes called MS-DOS, was designed for the IBM PC. Windows 98 and Windows. 2000 both support DOS commands

chapter p chapter 1
Write the product in standard form. 5) (3 + 5i)(2 + 9i). 5). Find the product of the complex number and its conjugate. 6) -1 - 5i. 6). CHAPTER 1. Find the domain of ...

CHAPTER ONE
MAPS. 1. The VOC territories in Austronesian-speaking Asia, ca. the 1660s. 2. Indigenous ethno-linguistic groups of Taiwan. 3. Geographic distribution of ...

Chapter 5
not in the domain. The only critical point is x = 0. As x moves away from 0 on either side, the values of y decrease. The function has a local maximum value at (0, ...... (b) Since. ,. dV. dV dr dt dr dt. = we have. 2 . dV dr rh dt dt π. = (c). 2. 2

Chapter 15
373 cancelled each other and there is zero displacement throughout. To put the principle of superposition mathematically, let y1 (x,t) and y2 (x,t) be the displacements due to two wave disturbances in the medium. If the waves arrive in a region simul

Chapter 9
9.1 Introduction. In mathematics, the word, “sequence” is used in much the same way as it is in ordinary English. When we say that a collection of objects is listed ...

Chapter 09
In the late 1700s and early 1800s, he kept notes about his travels .... In 1762, he quit the company. In 1670, the Hudson's Bay Company held trading rights and.

Chapter 15
The most familiar type of waves such as waves on a string, water waves, sound waves, seismic waves, etc. is the so-called mechanical waves. These waves require a medium for propagation, they cannot propagate through vacuum. They involve oscillations

Physics 235 Chapter 3 - 1 - Chapter 3 Oscillations In this Chapter ...
In this Chapter different types of oscillations will be discussed. A particle carrying out oscillatory motion, oscillates around a stable equilibrium position (note: if ...

Chapter 1 – Getting Started Chapter 2 - PSM ... - GCAP CoolCast
What is Garden City Ammonia Program? What is Refrigeration? Why Refrigeration? Why Take an Operator I Course? Is there a Career in the Industrial ...

ACF Central Florida Chapter Named Southeast Region Chapter of the ...
Mar 31, 2016 - Page 1 ... or on Facebook at www. ... Plugrá® European-Style Butter; Vitamix; Ecolab; Allen Brothers; Wisconsin Milk Marketing Board; Atlantic ...

ACF Central Florida Chapter Named Southeast Region Chapter of the ...
Mar 31, 2016 - ... Vitamix; Ecolab; Allen Brothers; Wisconsin Milk Marketing Board; Atlantic Veal & Lamb;. American Technical Publishers; Par-Way Tryson Company; The ... for chefs in the United States, with the Certified Executive Chef®, ... ACF on

Chapter 1 – Getting Started Chapter 2 - PSM ... - GCAP CoolCast
How much Must I Know about Process Safety Management to be an Operator? Are there Any Organizations that Can Help Me in ... “To the Extent they can affect the process” Mean? How do I Properly Document this Training? ... are some Chemical Characte

Chapter 9_86-117p.pdf
These books have Spirit for theme. I shall never ... He said: 'I will make each of them threefold.' He and life .... "My son Bees create honey by gathering the sweet.

Chapter 3
The 4 step numbers in the example below, are also labels ... 3 • 2 = 6 , is just the point 3 on a number line, being scaled by 2 (made twice as far from the origin).

Chapter
order to communicate alarms from patient monitoring and therapeutic ... After implementation of the central application (AM), as specified in the ACM profile,.

Chapter
SPSS (version 12.0, SPSS Inc.) was used for all analysis. .... ence of prehospital ECG predictive of a reduced door-to-balloon time (mean ± SE) by 38.8 ... Lastly, a multivariate logistic regression model was constructed to determine the sig-.

CHAPTER 11 -
Taking a job at Starbucks would mean giving up that $75,000 a year job, the ...... signatures of people who no longer ...... The pagination of this electronic.

Chapter 1
converged to the highest peak because the selective pressure focuses attention to the area of .... thus allowing the formation of non-equal hyper-volume niches. In order to ..... The crossover operator exchanges the architecture of two ANNs in.

Chapter 1
strategy entails, the research findings are difficult to compare. .... rooms (cf. Li 1984; Wu 2001; Yu 2001). Comprehensive Surveys of EFL Learner Behaviours.

Chapter 4
For example, based on historical data, an insurance company could apply ..... ios we explicitly assume that the only goal of data mining is to optimize accuracy.