If we do R (X) R, what happens?            R = R 8 R

 

Division (¸)      R1 (A,B)          R2 (B)

a1

b1

a1

b2

a2

b1

a3

b1

a4

b2

a5

b1

a5

b2

         b1                                Both     Not in the second

          b2

 

           

 

R = R1 ¸ R2 Þ  R (A)

                               a1

                               a5

 

                                         

If     R2 (B)                  R = R1 ¸ R2 Þ  R (A)

   b1                                                  a1

                                                        a2

                                                                    a3

                                                                    a5

                                                                                                                                                                                                                       

If     R2 (B)                  R = R1 ¸ R2 Þ  R (A)

   b1                                                  Æ

   b2                                                  

               b3                                                 

                                                                    

 

If     R2 (B)                  R = R1 ¸ R2 Þ  R (A)

   Æ                                                  a1

                                                        a2

                                                                    a3

                                                                    a4

                                                                            a5

 

Employee (ENo, EName)

Assigned_To (ENo, PNo, Hours)

Project (PNo, PName, Architect)

 

 

Q1.  Display Emp No of employees working for the Project P1 

            pEno  (tPNo  = ‘pi’ (Assigned_To))

Q2.  Display Emp No and Emp Name of employees working for the Project P1

            Employee  8  pEno  (tPno = ‘pi’ (Assigned_To))

Q3.  Display ENo and EName of employees workng for Project named “Database”

         pENo, EName  {Employee  8  pEno  [(Assigned_To 8  ( pEno  (tPName = ‘Database’ (Project))]}

 

Q4.  Display ENo and EName of employees working on both project P1 and P2 

            R1 = tPno = ‘P1’ Ú Pno = ‘P2’ (Project)

            R2 = pPNo (R1)

            R3 = Assigned_To  ¸ R2  Find ENo working for P1 and P2

            R4 = Employee  8 R3

            R5 = pENo, EName  (R4)

 

Q5.  Display ENo of employees who work on at least all of the project that Employee 107  works on

            R1 = tPno = 107 (Assigned_To)

            R2 = pPNo (R1)

            R3 = Assigned_To  ¸ R2

            R4 = R3 – 107                         Exclude 107

 

 

Q6.  Display ENo of employees who don’t work on Project P1

            R1 = tPno = ‘P1’ (Assigned_To)              2nd Way

            R2 = pENo (R1) 

            R3 = pENo (Assigned_To)

            R4 = R3 – R2

Q7. Display ENo of Employees who work on all projects:

 

R1= P PNo (Project)

R2= (Assign To) / R1

R3=P ENo(R2)

 

Q8. Display Eno of Employees other than employee 107 who work on at least one project that employee 107 works on:

R1=G ENo=107(Assigned To)

R2= P PNo (R1)

R3=(Assigned To) 8 R2

R4=P ENo(R3) - 107

SQL (Structured Query Language)

Retrieval: SELECT

Modification: UPDATE SELECT<Attribute name list>

INSERT FROM<Relation name list>

DELETE [WHERE<predicate>]

[GROUP.BY<field list>[HAVING Predicate]]

[ORDER BY <field list>]

 

Class

Cname

Time

Room

MAN200

MF9

222

EE545

MWF3

221

MAN410

MWF8

311

CS150

MWF

311

CS250

MWF2

221

 

Student

SID

Name

Major

150

Kaya

MAN

200

Ayse

MATH

250

Burak

EE

300

Filiz

MAN

350

Hasan

MATH

450

Lemi

EE

400

Riza

MAN

100

Can

EE

 

 Enrollment 

SID

Cname

Grade

100

EE545

A

150

MAN200

C

200

EE545

A

200

CS250

A

300

CS150

B

400

MAN200

B

400

MAN410

B

400

CS250

A

450

MAN200

B

 

 

 

 

Operations:

1. Simple Retrieval: SELECT * FROM STUDENT

Output: A copy of Student table.

2. Projection: SELECT Name, SID FROM STUDENT

Name

SID

kaya

150

burak

200

3. Selection in the sense of restriction

All EE major => SELECT *From Student

WHERE Major="EE"

The logical signs, which can be used after "Where":

=, ~=, >, ~>, >=, <, ~<, <=, AND, OR, NOT

4. Selection & Projection together

SELECT Name, SID

FROM Student

WHERE Major = "EE"

5. Duplicate Removal: e.g. Removing SID: 100,150,200,200,400...

SELECT SID

FROM enrollment

 

6. Use DISTINCT: e.g. output SID: 100,150,200,400...

SELECT DISTINCT SID

FROM Enrollment

 

7. Output formatting: SELECT Class_Cname,"Meets now in Room", Room+3

FROM Class

Example Output:

MAN200 Meets now in Room 225

 

8. Sorting the Results:

SELECT SID, name

FROM Enrollment

ORDER BY Cname, SID

Syntax of "Order By" Statement

ORDER BY <attribute name[<order> asc./desc.]>
 

9. Join Queries:

Class schedule of Student 400(What courses ,when,where)

SELECT SID, enrollment,cname,class.cname,time,room

FROM Enrollment, class

WHERE Enrollment.Cname=Class.Cname

AND SID=400

QUESTIONS:

1. What is the output of this SQL statement:

    SELECT FROM Student WHERE Major !="EE"

    Answer: Only the names:

                   Kaya, Filiz, Ayse, Riza, Hasan

2. What can be said about table fields where this statement gives a correct output:

    SELECT FROM Tablex WHERE Tablex.ID==Tablex.PID ORDERBY Name, ID

    Answer: The table contains 3 or more fields. The 3 we know are ID, PID and Name. ID and PID may contain some values which are equal.