OR, select customer_name from full outer join depositor borrower using (customer_name) where is or is null loan_number null account_number OR, select customer_name from natural full join depositor borrower where is or is null loan_number null account_number RA: customer_name (σ loan_number = null account_number = null (depositor П borrower)) Output: customer_name Nobody Generalized Projection This operation extends the projection operation by allowing arithmetic functions to be used in the projection list. As an example, let’s revisit query Q8: we want to know how the results of the students (in Q4) would look like if we were to give away 5 marks as grace. How would we find it? SQL: select from student_name, + 5 result; student_name, marks + 5 (result) RA: П Aggregate Functions Aggregate functions take a collection of values and return a single value as a result. For example, consider the following table: employee employee_name branch_name branch_city salary A DU Dhaka 1000 B DU Dhaka 2000 C BUET Dhaka 3000 D KUET Khulna 4000 E KU Khulna 5000 F RU Rajshahi 6000 Q20. Find the number of branches appearing in the relation. employee SQL: select branch_name) from employee; RA: G count-distinct(branch_name) (employee) Output: count(distinct branch_name) 5 Q21. Find the total salary of all employees at branch of the bank. each SQL: select sum(salary) from group by branch_name, employee branch_name; RA: branch_nameG sum(salary) (employee) Output: branch_name sum(salary) BUET 3000
OR, select customer_name from depositor full outer join borrower using (customer_name) where loan_number is null or account_number is null OR, select customer_name from depositor natural full join borrower where loan_number is null or account_number is null RA: П customer_name (σ loan_number = null account_number = null (depositor borrower)) Output: customer_name Nobody Generalized Projection This operation extends the projection operation by allowing arithmetic functions to be used in the projection list. As an example, let’s revisit query Q8: we want to know how the results of the students (in Q4) would look like if we were to give away 5 marks as grace. How would we find it? SQL: select student_name, + 5 from result; RA: П student_name, marks + 5 (result) Aggregate Functions Aggregate functions take a collection of values and return a single value as a result. For example, consider the following employee table: employee_name branch_name branch_city salary A DU Dhaka 1000 B DU Dhaka 2000 C BUET Dhaka 3000 D KUET Khulna 4000 E KU Khulna 5000 F RU Rajshahi 6000 Q20. Find the number of branches appearing in the employee relation. SQL: select branch_name) from employee; RA: G count-distinct(branch_name) (employee) Output: count(distinct branch_name) 5 Q21. Find the total salary of all employees at each branch of the bank. SQL: select branch_name, sum(salary) from employee group by branch_name; RA: branch_nameG sum(salary) (employee) Output: branch_name sum(salary) BUET 3000
DBMS_An_Interactive_Tutorial.docx