CS457 Syllabus & Progress

### Some simpleSELECT examples

• Simple SELECT examples

1. Find the fname and lname of all employees

 ``` select fname, lname from employee ```

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

 ``` select fname, lname from employee where dno = 4 ```

3. 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:

 πfname, lname, address ( σ(dname='Research' and dnumber = dno) ( employee × department )   )

You can see some resemblance between SQL and Relational Algebra....

4. 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 ```

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

 ``` Find out "who works on what project": SELECT essn, pno FROM works_on Add info on project name: SELECT essn, pno FROM works_on, project WHERE pno = pnumber Find ssn who work on 'ProductX': SELECT essn FROM works_on, project WHERE pno = pnumber AND pname = 'ProductX'```

6. 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 pno = pnumber AND pname = 'ProductX' Add in information of employee using foreign key: SELECT fname, lname FROM employee, works_on, project WHERE ssn = essn AND pno = pnumber AND pname = 'ProductX'```

7. 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'```