CS457 Syllabus & Progress

Some `SELECT` command examples

SELECT command examples

Find the fname and lname of all employees

select fname, lname from employee

Find the fname and lname of all employees in department 4:

select fname, lname from employee where dno = 4

Find the name and address of employees in the Research department:


  Name and address of all employees:

     SELECT fname, lname, address, dno      
     FROM employee


  Add in department name information:

     SELECT fname, lname, address, dno, dname      
     FROM employee, department
     where  dno = dnumber


  Get info for employees in the Research department

     SELECT fname, lname, address      	     
     FROM employee, department		     
     WHERE dno = dnumber			     
       AND dname = 'Research'

The following is the equivalence query in Relational Algebra:
You can see some resemblance between SQL and Relational Algebra....

Find the ssn of employees who work on the project 'ProductX'


  Find project 'ProductX':

       SELECT  pnumber, pname
       FROM    project
       WHERE   pname = 'ProductX'
 

  Find who (ssn) works on this project:                  

       SELECT  essn, pnumber, pname
       FROM    project, works_on      
       WHERE   pname = 'ProductX'
         AND   pno = pnumber


  Answer: (by projecting the requested attributes)

       SELECT  essn
       FROM    project, works_on
       WHERE   pname = 'ProductX'
         AND   pno = pnumber

Find the name of employees who work on the project 'ProductX'


   Previously: find ssn of employees working on ProductX:     

       SELECT  essn	     
       FROM    works_on, project      
       WHERE   pname = 'ProductX'
         AND   pno = pnumber


   Add in information of employee using foreign key essn:  

       SELECT  fname, lname     	  
       FROM    employee, works_on, project      
       WHERE   ssn = essn	        
 	 AND   pname = 'ProductX'
 	 AND   pno = pnumber

Some SELECT command examples

Some `SELECT` command examples