Assignment No 2

Assignment No.2 (A Batch)

Q1) Given the following relational schema

DIVISION(Div #, Div_Name, Director)
DEPARTMENT(Dept #, Dept_Name, Location, Div #)
EMPLOYEE(Emp #, Emp_Name, Salary, Address, Dept #)
State the following queries in SQL
a) Get the employee name, department name and division name for all employees whose salary is above Rs.20,000.
b) List the names of all employees who work in “Information Technology” Division.
c) List the departments and employees in each department located in “Mumbai”.

Q. 2)
Consider the relational DB
Employee ( employee_name,street, city)
Works (employee_name, company_name, salary)
Company(company_name, city)
Manager(employee_name, manager_name)
Give expression for the following :
(i) Find names of all employees in the database who live in same city as the companty which they work in.
(ii) Find name of employees who earn more than every employee of “Small Bank Corporation”.
(iii) Delete all tuples in works relationship for employees of “Small Bank Corporation”.
Write following queries for given table.
Q.3)
(i) Client_master (Client_no (PK), name, address1, address2, city, state, pincode, bal_due)
(ii) Product_master ( Product_no(PK),Description, Profit_percent, Unit measure, Qty_no_hand, Recorder, Sellprice, Cost-price)
a) Write down SQL syntax to create above tables.
b) Write down SQL syntax to insert data into above tables(one example each).
c) Perform following SQL operation.
(2) Find out names of all the clients.
(3) Print the entire client-master tables.
(4) Retrieve the list of names and the-cities.
(5) List the various products available from the product-master table.
(6) Find the names of all the clients having name starts with letter’a’.

Assignment No.2 (B Batch)

Q.1 Write following queries for given table.

(iii) Client_master (Client_no (PK), name, address1, address2, city, state, pincode, bal_due)
(iv) Product_master ( Product_no(PK),Description, Profit_percent, Unit measure, Qty_no_hand, Recorder, Sellprice, Cost-price)
a) Write down SQL syntax to create above tables.
b) Write down SQL syntax to insert data into above tables(one example each).
c) Perform following SQL operation.

(1) Find out clients who stay in the city Mumbai.
(2) Print the list of clients who are located in city either Pune,Nashik or Nagpur.
(3) Print the list of clients whose balance due are greater than value 10000.
(4) Find out the product with description ‘pen’.
(5) Find the product whose selling price is greater than 2000 and less than or equal to 5000.

Q.2.
Consider the following Employees database:
Employee(employee_name, street, city)
Works(employee_name, company_name, salary)
Company(company_name, city)
Manages(employee_name,manger_name)
Answers the following SQL queries.

(i) Find the names, street address, and city of residence of all employees who works for “XYZ” and earn more than Rs. 25,000.
(ii) Find all the employees who live in the same cities as the company for which they work in.
(iii) List all employees who live in the same cities as their managers .
(iv) List all employees who earn more than the average salaries of all employees in their company.
(v) List the names of companies which has the maximum number of employees.


Q.3)

A Library has the following relations:

LIBRARY (Code, Name, No_of_Books)
PERSON (Id, Name, Age)
IS-NUMBER (Code, Id, Date_of_joining)
BOOKS (Accession_No, Id, Date_of_Borrow)
Borrowed-By (Accession_No, id, Date_of_borrow)
Answer the following queries using SQL/Relational Algebra:

(i) List all the books borrowed before 2nd March 2002 and details of borrowers.

(ii) List the details of the books and the persons who has borrowed the book costing above Rs. 2,000.

(iii) Give details of person who have not borrowed any book.

(iv) List the person who have borrowed at least all the books borrowed by the person with Id 201.



Assignment No.2 (C Batch)
Q.1.
Consider the insurance database where primary keys are underlined and answer
the queries using SQL
PERSON (Driver_id, Name, Address)
CAR (License, Model, Year)
ACCIDENT(Report_no, Data, Location)
OWNS (Driver_id, License)
PARTICIPATED (Driver_id, License, Report_No, Damage amount)

(i) Find the total number of people who owned cars that were involved in accidents in 2004.
(ii) Find the number of accident in which car belonging to ‘John Smith’ were involved.
(iii) Add a new accident to database assume any values for required attributes.
(iv) Delete ‘Santro’ belonging ‘John Smith’. Update the damage amount for the car with license number ‘AABB2000’ in the accident with repert number ‘AR2197’ to $ 3000.



Q.2.
For the given Employee data base, given an expression in SQL for each of the following query :
EMPLOYEE (emp-name, street, city)
WORKS COMPANY (company-name, city)
MANAGES (emp-name, manager-name)
(i) Modify the data base so that ‘Jones’ now lives in ‘Newtown’.
(ii) Find all employees in the database who live in the same cities as the companies for which they work.
(iii) Find the company that has the most employees.
(iv) Give all employees of First Bank Corporation a 10 percent raise




Q.3)
Consider the insurance database where primary keys are underlined and the queries using SQL/Rotational algebra.

PERSON (Driver_id, Name, Address)
CAR (License, Model, Year)
ACCIDENT (Report_No, Data, Location)
OWNS (Driver_id, License)
PARTICIPATED (Driver_id, License, Report_No, Damage amount):
(i) Add a new accident to database assume any valves for required attributes.
(ii) Delete ‘Alto belonging ‘HMB’ updated the damage amount for the car with license no’XXYY 1000’ in the accident with report number’AR 2197’ to $ 1000.
(iii) Find the total number of people who owned cars that were involved in accidents in 2995.
(iv) Find the number of accident in which car belonging to ‘HMB’ were involved.