CSE 312, DIU

MOHSHI MASNAD

1

What we have done so far……..

LOGICAL TESTS

AGGREGATE FUNCTIONS (BASIC)

JOINING OPERATIONS

SIMPLE SUB QUERY

SET OPERATIONS

GROUP BY

HAVING

2

What we are going to learn.…

ALIASES

SET COMPARISONS

CHECKING EMPTY RELATION

VIEW

INTEGRITY CONSTRAINTS

CHECK

Primary Key

Foreign Key

3

Our Tables

4

ALIASES12

5

SET

comparisons13

6

SET

comparisons13

7

SET

comparisons13

8

check for

EMPTY13

9

Similarly NOT EXISTS can be used if necessary

10

User 1

User 2

ID Name Address Age

Select ID , Name from Orginal_Table

Select Name, Address, Age from Orginal_Table

Boss of

Orginal_Table

Scenario 1

11

User 1

User 2

ID Name Address Age

Boss of

Orginal_Table

VIEW of the Desired

portion of Original_Table

VIEW of the Desired

portion of Original_Table

A1 A2 A3 A4 A5

B1 B2 B3

C1 C2

Scenario 2

A1 A2 A3 A4 A5

B1 B2 B3

C1 C2

Intermediate Table

(VIEW)

Result

Scenario 2

The

VIEW14

The

VIEW14

The

VIEW14

The

VIEW14

The

VIEW14

CONSTRAINTOn a single relation

19

NOT NULL

UNIQUE

CHECK

16

PRIMARY KEY

20

16

PRIMARY KEY

21

16

PRIMARY KEY

22

16

PRIMARY KEY

23

16

Referential Integrity

THE FOREIGN KEY

24

Attribute 3

10

11

11

12

12

Attribute 1

10

11

12

Table_1 (CHILD)

Table_2 (PARENT)

FOREIGN KEY

17

Referential Integrity

THE FOREIGN KEY

25

17

Referential Integrity

THE FOREIGN KEY

26

17

Referential Integrity

THE FOREIGN KEY

27

17

Referential Integrity

THE FOREIGN KEY

28

17

NAMING

CONSTRAINTS

29

18

DROPPING A TABLE

with constraints

30

20

31

DROPPING A TABLE

with constraints20

32

ALTER : changing the table structure21

33

ALTER : changing the table structure21

34

ALTER : changing the table structure

The clause cascade automatically

disables foreign key constraints that depend

on the (disabled) primary key

21

CLASS

TASK

1.Create a view showing the students’ ID and their corresponding number of phone no. entries.

2.Write any SQL example using NOT EXIST.3.Find out the ID of the students whose semesters are above all the

semesters of the students who have informed their blood group and contact information.

4.From (3), If at least 1 result is found, then show their CGPA’s .5.Create DEPARTMENT and EMPLOYEE tables according to given

schema and design with appropriate constraints (unique, not null,

check and primary key)

6.Now put referential integrity where necessary.7.Add a new column EMP_AGE in EMPLOYEE table.

8.Impose ON DELETE CASCADE and check deletion.

STUDENT (ID, NAME, SEMESTER, DATE_OF_BIRTH)

STUDENT _RESULT (ID, CGPA)

STUDENT _CONTACT (ID, PHONE_NO)

STUDENT _BLOOD_GROUP (ID, B_GROUP)

DEPARTMENT (DEPT_ID, DEPT_NAME)

EMPLOYEE (EMP_ID, EMP_NAME, EMP_DEPT)