Practice Example Select and Project employee person name

Practice Example – Select and Project employee (person name, street, city) works (person name, company name, salary) company (company name, city) Express in RA each of the following queries: a. Find the names of all employees who live in city “Mysore”. b. Find the names of all employees whose salary is greater than 200, 000. c. Find the names of all employees working in “Apple” and whose salary is greater than 200, 000. Slide 6 - 2

Practice Example – Select and Project branch(branch name, branch city, assets) customer (customer name, cust street, cust city) loan (loan number, branch name, amount) borrower (customer name, loan number) account (account number, branch name, balance) depositor (customer name, account number) Express in RA each of the following queries: a. Find the names of all branches located in “Mumbai”. b. Find the names of all branches who have more than 500, 000 account balance. Slide 6 - 3

Practice Example – Select and Project Refer Handout : Student, Course, Section, Grade Report Express in RA each of the following queries: a. Find the names of all CS students. b. List all 4 credit courses offered by CS Department. c. List sections and course numbers taught by Prof. Anderson in Fall 2005. d. List all courses offered in 2007 e. List student numbers who scored “A” in section 85. Slide 6 - 4

Practice Example – Union Refer Handout : Student, Course, Section, Grade Report Express in RA using Union the following queries: a. Names of all CS or MATH major students. b. List all 4 credit courses offered by CS dept and 3 credit courses offered by Math dept. c. Name all instructors teaching in 2008. d. Two sections (85, 92) were offered in Fall 07. List student numbers who scored at least one “A” in that semester Slide 6 - 5

Practice Example – Intersection Refer Handout : Student, Course, Section, Grade Report Express in RA each of the following queries: a). List instructors who taught the same course in Fall of 2007 and Fall of 2008 b). Two sections (85, 92) were offered in Fall 07. List student numbers who scored both “A”s in that semester c). Two sections (85, 92) were offered in Fall 07. List student numbers who scored one “A” grade only. Slide 6 - 6

Practice Examples – Relational Algebra Refer Handout : COMPANY database a). Retrieve the names of employees in dept# 5 who work more than 10 hours per week on the 'Product. X' project. b). Find employee names supervised by 'Franklin Wong'. c). Retrieve employee names who work on every project. d). Retrieve employee names who do not work on any project. e). List the names of employees who have a dependent with the same first name as themselves f). List the last names of department managers who have no dependents Slide 6 - 7

Slide 6 - 8

Practice Examples – Aggregation Refer Handout : COMPANY database g). For each department, retrieve the department name, and the average salary of employees working in that department. h). Retrieve the average salary of all female employees i). Find names/addresses of employees who work on at least one project located in Houston but whose department has no location in Houston. j) For each project, list the project name and the total hours per week (by all employees) spent on that project. Slide 6 - 9

Examples of Queries in Relational Algebra n Q 1: Retrieve the name and address of all employees who work for the ‘Research’ department. RESEARCH_DEPT DNAME=’Research’ (DEPARTMENT) RESEARCH_EMPS (RESEARCH_DEPT DNUMBER= DNOEMPLOYEE) RESULT FNAME, LNAME, ADDRESS (RESEARCH_EMPS) n Q 6: Retrieve the names of employees who have no dependents. ALL_EMPS SSN(EMPLOYEE) EMPS_WITH_DEPS(SSN) ESSN(DEPENDENT) EMPS_WITHOUT_DEPS (ALL_EMPS - EMPS_WITH_DEPS) RESULT LNAME, FNAME (EMPS_WITHOUT_DEPS * EMPLOYEE) Slide 6 - 10

Consider the following relational schema pertaining to a student's database: Students (rollno, name, address ) Enroll( rollno, coursename) Where primary keys are shown in italics. The number of tuples in the student and Enroll tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that can be present in (Student *Enroll) , where * denotes natural join ? 8, 8 120, 8 960, 8