Database Management System Practical Question- 2017

B.Sc(Hons) in CSE Part-II Fourth Semester Examination, 2017

CSE-224 Database Management System Practical

Time: 3 hours                                                                                                             Marks: 60

(Answer any two of the following question as selected by the examiners)

1.      Consider the employee database Emp consisting of the following tables where the primary keys are underlined:

            employee (employee_name, street, city)

            works (employee_name, company_name, salary)

            company (company_name, city)

            manages (employee_name, manager’s_name)

Write down the SQL expression for the following queries:

a)      Find the names, cities and salaries of all employees who work for Prime Bank Ltd.

b)      Find the total salaries of each company.

c)      Add a record in the database using a form.

d)     Display your result of query (a) on a report.

  2. Consider the banking database bank consisting of the following tables where the primary keys are underlined:

            branch (branch_name, branch_street, assets)

            customer (customer_name, customer_street, customer_city)

            loan (loan_number, branch_name, amount)

            borrower (customer_name, loan_number )

            account (account_number, branch_name, balance)

            depositor (customer_name, account_number )

Write down the SQL expression for the following queries:

a)      Find all the customers who have an account but no loan in the bank.

b)      Delete all loans with loan amounts between 13,000/- and 20,000/-

c)      Add a record in the database using a form.

d)     Display your result of query (a) on a report.

   3. Consider the banking database bank consisting of the following tables where the primary keys are underlined:

            branch (branch_name, branch_street, assets)

            customer (customer_name, customer_street, customer_city)

            loan (loan_number, branch_name, amount)

            borrower (customer_name, loan_number )

            account (account_number, branch_name, balance)

            depositor (customer_name, account_number )

Write down the SQL expression for the following queries:

a)      Find all the customers who have an account but no loan in the bank.

b)      Find the average account balance at the Mohakhali Branch.

c)      Add a record in the database using a form.

d)     Display your result of query (a) on a report.

4.   Consider the employee database Emp consisting of the following tables where the primary keys are underlined:

            employee (employee_name, street, city)

            works (employee_name, company_name, salary)

            company (company_name, city)

            manages (employee_name, manager’s_name)

Write down the SQL expression for the following queries:

a)      Find the names, streets and cities of all employees who work for Prime Bank Ltd and earn more than TK. 10,000.

b)      Find all employees in the database who earn more than each employee of Prime Bank Ltd.

c)      Delete a record in the database using a form.

d)     Display your result of query (a) on a report.

5.   Consider the banking database bank consisting of the following tables where the primary keys are underlined:

            branch (branch_name, branch_street, assets)

            customer (customer_name, customer_street, customer_city)

            loan (loan_number, branch_name, amount)

            borrower (customer_name, loan_number )

            account (account_number, branch_name, balance)

            depositor (customer_name, account_number )

Write down the SQL expression for the following queries:

a)      Find all the names and loan numbers of all customers who have a loan from the bank.

b)      List all customer names in alphabetic order that have an account at the “Dhanmondi Branch”.

c)      Add a record in the database using a form.

d)     Display your result of query (a) on a report.

6. Consider the banking database bank consisting of the following tables where the primary keys are underlined:

            branch (branch_name, branch_street, assets)

            customer (customer_name, customer_street, customer_city)

            loan (loan_number, branch_name, amount)

            borrower (customer_name, loan_number )

            account (account_number, branch_name, balance)

            borrower (customer_name, account_number )

Write down the SQL expression for the following queries:

a)      Find all the customers who have a loan, an account or both at the bank.

b)      Insert one tuple into the relation account with values, the account number is SB_005 at Mohakhali Branch and the balance is Tk. 2,50,000.

c)      Add a record in the database using a form.

d)     Display your result of query (a) on a report.

7.   Consider the employee database Emp consisting of the following tables where the primary keys are underlined:

            employee (employee_name, street, city)

            works (employee_name, company_name, salary)

            company (company_name, city)

            manages (employee_name, manager’s_name)

Write down the SQL expression for the following queries:

e)      List the employee names and their salaries by manager name.

f)       Find those companies whose employee earn a higher salary on an average than the average salary of Prime Bank Ltd.

g)      Add a record in the database using a form.

h)      Display your result of query (a) on a report.

8.  Consider the employee Company database consisting of the following tables where the primary keys are underlined:

            lives (person_name, street, city)

            works (person _name, company_name, salary)

            location (company_name, city)

            manages (person _name, manager’s_name)

Write down the SQL expression for the following queries:

a)      Modify the database to change Karim’s living city to a new one.

b)      Find the number of tuples in works relation.

c)      Add a record in the database using a form.

d)     Display your result of query (a) on a report.

9. Consider the employee database Company consisting of the following tables where the primary keys are underlined:

            lives (person_name, street, city)

            works (person _name, company_name, salary)

            location (company_name, city)

            manages (person _name, manager’s_name)

Write down the SQL expression for the following queries:

a)      Find the names, cities and managers name of all employees who work for Brac Bank Ltd.

b)      Find all employees who do not work for Beximco Ltd.

c)      Add a record in the database using a form.

d)     Display your result of query (a) on a report.