SQL and Query Processing

Write SQL statements to create a table and update value in a table. Also mention the use of cascade on delete while creating the table. Use your own assumption for the table and update conditions. [5]

How is the Group By Having clause used in SQL statements? Mention the syntax and example. [5]

What is the view? How can you create a view in SQL? Illustrate with an example. [5]

Given following relations, write relational algebra statements for Person(Pid, pname, dob, paid) and Doctor(Did, pid, dname, dspeciality). a. Retrieving name of person who is doctor b. Retrieving all doctors whose speciality is pediatric. [5]

Retrieve the Employee Name, Department Number and Department Name of "John Smith" using Relational Algebra. The relations are given below. a. EMPLOYEE (Ssn, Fname, Lname, Bdate, Address, Sex, Salary, SuperSSN, Dno), b. PROJECT (Pnumber, Pname, Plocation, Dnum), c. WORKS ON (Essn, Pno, Hours), d. DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr Start date) [5]

From the relations given below, answer the following questions EMPLOYEE (Ssn, Ename, Bdate, Address, Sex, Salary, Super_ssn, Dno), DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date), PROJECT (Pname, Pnumber, Plocation, Dnum), DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship), DEPT_LOCATIONS (Dnumber, Dlocation): a. Retrieve the name and address of all employees who work for the 'Computer' department in SQL, b. For each Department, retrieve the department number, the number of employees in the department, and their average salary using SQL, c. Retrieve the employees name and their Dname and Pname ordered by the employee's Dname, d. Retrieve the name and salary of all employees who work in department number 5 using Relational Algebra, e. Retrieve the name of the manager of each department using Relational Algebra.Following the above statement answer the following question.[10+0]

From the relation given below, answer the following questions. EMPLOYEE (Ssn, Fname, Mnit, Name, Bdate, Address, Sex, Salary, Super_ssn, Dno) DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date) PROJECT (Pname, Pnumber, Plocation, Dnum) DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship) DEPT_LOCATIONS (Dnumber, Dlocation): a. Retrieve all the attributes of employees who work for the 'Computer' department department no 5 using SQL, b. For each Department, retrieve the department number, the number of employees in department, and their average salary using SQL, c. For each project on which more than two employees work, retrieve the project number, name, and the number of employees who work on that project using SQL, d. Select the EMPLOYEE tuples whose department is 10, or those whose salary is greater than Rs 50,000 using Relational Algebra, e. Retrieve the names of employees who have no dependents using Relational Algebra.[10]

Consider a database system with following schemas; Hospital(hname, haddress, hspecilaity) Doctor(did, dname, dspecilization, ) Worksat(did, hname, workinghrs) Pharmacy(phname, hname, no_of_sales, total_revenue) Now write SQL statements and relational algebra statements for following queries: a. Select name of all doctors having specialization 'gyno', b. Select the name and address of hospital where working hours is 'day', c. Using natural join select the name of doctors whose working hours are 'night', d. Find the average salary of the doctors, e. Find names of hospital and their pharmacy which generate revenue more than 10000. Sort the result in descending order on the basis of pharmacy name.[10]

Define Outer Join in SQL. Given following relations, show the results of left and right outer joins.

Employee:

Department:

[5]