DBMS_An_Interactive_Tutorial.pdf

# Loannumber is null or accountnumber is null ra п

• 53

This preview shows pages 32–35. Sign up to view the full content.

loan_number is null or account_number is null RA: П customer_name ( σ loan_number = null account_number = null (depositor ̲̲̲̲̅̅̅̅ ̲̲̲̲̅̅̅̅ borrower)) Output: customer_name Somebody Anybody 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 , marks + 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: customers borrower depositor customers borrower depositor

This preview has intentionally blurred sections. Sign up to view the full version.

29 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 count ( distinct 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_name G sum ( salary ) (employee) Output: branch_name sum(salary) BUET 3000 DU 3000 KU 5000 KUET 4000 RU 6000 Note that the column header for the second column is not very meaningful. To give it a meaningful name, e.g., total_salary , we can rewrite the above query as below: SQL: select branch_name , sum ( salary ) as total_salary from employee group by branch_name; RA: branch_name G sum ( salary ) as total_salary (employee) Output: branch_name total_salary BUET 3000 DU 3000 KU 5000 KUET 4000 RU 6000 Q22. Find branch city, branch name wise total salary, average salary and also number of employees. SQL: select branch_city, branch_name , sum ( salary ), avg ( salary ) , count ( employee_name ) from employee group by branch_city, branch_name; RA: branch_city , branch_name G sum ( salary ), avg ( salary ), count ( salary ) (employee) Output: branch_city branch_name sum(salary) avg(salary) count(employee_name) Dhaka BUET 3000 3000.0000 1 Dhaka DU 3000 1500.0000 2 Khulna KU 5000 5000.0000 1
30 Khulna KUET 4000 4000.0000 1 Rajshahi RU 6000 6000.0000 1 Q23. From the following table, find the average balance for each customer who lives in Dhaka and has at least two accounts. customer_name customer_city customer_name account_no balance A Dhaka A A-101 1000 B Dhaka A A-102 2000 C Khulna B A-103 3000 D Dhaka C A-104 4000 D A-105 5000 D A-106 6000 customer account Here, we cannot insert both the conditions into the where clause, because counting how many accounts a customer has can only be done using an aggregate function ( count ). As aggregate functions operate on groups whereas the where clause operates on tuples, these two conditions cannot be put together. For solving this problem, SQL introduces another clause having where the conditions to be operated on groups are to be placed.

This preview has intentionally blurred sections. Sign up to view the full version.

This is the end of the preview. Sign up to access the rest of the document.
• Winter '16
• zeenath
• Relational Database, ........., Entity-relationship model, Relational model

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern