SQL Query Example 1: a simple projection query

  • Query:

    • Find the fname and lname of all employees

    Answer: 

       select fname, lname                   
   from   employee

SQL Query Example 2: query with a simple tuple condition

  • Query:

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

    Answer: 

       select fname, lname                   
   from   employee
   where  dno = 4

SQL Query Example 3: a join query

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

    Solution: 

      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'

Example 4+5: multiple join condition - part 1

  • 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

Example 4+5: multiple join condition - part 2

  • 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


    
   Solution: 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