11 - SQL (LectureSession12) Chapters8 (continued)...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
SQL (continued) (Lecture Session-12) Chapters 8 Wed, Feb 17, 2010 1 DLPD, BITS-Pilani.                Prepared  by Dr.R. Gururaj, Asst.Professor of CS
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Wed, Feb 17, 2010 DLPD, BITS-Pilani.                Prepared  by Dr.R. Gururaj, Asst.Professor of CS 2 •DML Commands •Views in SQL Contents
Background image of page 2
Wed, Feb 17, 2010 DLPD, BITS-Pilani.                Prepared  by Dr.R. Gururaj, Asst.Professor of CS 3 FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO DNAME DNUMBER MGRSSN MGRSTARTDATE DNUMBER DLOCATION PNAME PNUMBER PLOCATION DNUM ESSN PNO HOURS ESSN DEPENDENT_NAME SEX BDATE RELATIONSHIP DEPENDENT WORKS_ON PROJECT DEPT_LOCATIONS DEPARTMENT EMPLOYEE Company database schema
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Wed, Feb 17, 2010 DLPD, BITS-Pilani.                Prepared  by Dr.R. Gururaj, Asst.Professor of CS 4 Ex.:1 SELECT bdate, address FROM EMPLOYEE WHERE Fname = ‘john’; Ex:2 Join operation SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname = ‘Research” and Dnumber = Dno; Retrieves first name, last name and address from joined truples from employee and department. The joining condition is Dnumber in department table is equal to Dno in employee table. We can also do aliasing (renaming) of tables to avoid ambiguity.
Background image of page 4
Wed, Feb 17, 2010 DLPD, BITS-Pilani.                Prepared  by Dr.R. Gururaj, Asst.Professor of CS 5 Ex.3 SELECT E.Fname, S.Fname FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.superssn = S.ssn; Retrieves the employee’s first name and his immediate supervisor’s first name. This is an example of self joining. Ex. 4 SELECT ssn FROM EMPLOYEE; Retrieves all ‘ssn’ from employee table
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
DLPD, BITS-Pilani.                Prepared  by Dr.R. Gururaj, Asst.Professor of CS 6 Ex. 5 SELECT ssn, dname FROM EMPLOYEE, DEPARTMENT; This will retrieve ssn, Dname from the relation which is result of cross product of employee and department tables. Ex. 6:
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 20

11 - SQL (LectureSession12) Chapters8 (continued)...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online