CS457 Syllabus & Progress

Some simple `SELECT` examples

Simple SELECT 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 fname and lname of employees in the 'Research' department who earn more than $30,000


  Previously:  info on employees in the Research department         

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


  Employees in the Research department who earn > 30000:

      SELECT fname, lname			 
      FROM employee, department     	 
      WHERE dno = dnumber			 
    	AND dname = 'Research'		 
    	AND salary > 30000

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: (limit the 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

For the projects located in 'Stafford', find the name of the project, the name of the controlling department, the last name of the department's manager, his address and birthdate


  Projects in 'Stafford':

     SELECT pnumber, pname, plocation
     FROM   project
     WHERE  plocation = 'Stafford'


  Add in info on the controlling department:

     SELECT pnumber, pname, dname, mgrssn
     FROM   project, department
     WHERE  dnum = dnumber
       AND  plocation = 'Stafford'


  Add in manager info of these departments:              

     SELECT pname, dname, lname, address, bdate
     FROM   project, department, employee
     WHERE  dnum = dnumber
       AND  mgrssn = ssn
       AND  plocation = 'Stafford'

Some simple SELECT examples

Some simple `SELECT` examples