Questions

Filter

Subject: All Subjects

Topic: All Topics

Explain how aggregation and generalization help to minimize redundancy while designing ER Diagram.

View Answer

Consider the following relational database:

  • Employee(empid, gender, name, dob, salary, address, deptno, start_date)
  • Department(dpetno, name)
  • Students(std_id, gender, name, dob, projectno, deptno)
  • Project(projectno, name, location)


Write Relational algebra expression for the following:

  1. Find employee name and address who works in "xyz company".
  2. Delete all employees whose salary is greater than Rs. 1,00,000.
  3. Update department name to "SitaGroup" where student "Sita" is involved.
  4. Find the average salary of all employees.
  5. Insert student record named "Gopal Shrestha" for department "Ram Group".
View Answer

Consider the following relational database:

  • Employee(empid, gender, name, dob, salary, address, deptno, start_date)
  • Department(dpetno, name)
  • Students(std_id, gender, name, dob, projectno, deptno)
  • Project(projectno, name, location)


Write SQL queries for the following based on the schema given:

  1. Display the name of employees working in 'BBM' Department.
  2. List details of employees earning salary above average salary.
  3. List details of all students along with the name of project they are involved in. Also display the name of the department.
  4. Decrease salary by 10% of all employees whose salary is more than 10,000.
  5. Insert (empid, name, dob, salary) to the table Employee.
View Answer

Consider the following relational database:

  • Patient(PID, Pname, Paddress, Pgender, disease)
  • Doctor(DID, Dname, Daddress, Department, salary)
  • Appointment(PID, DID, time)

 

Write SQL syntax for the following:

  1. Display name and address of patients who are suffering from Tuberculosis(TB).
  2. Count the number of doctors working in ENT department.
  3. Display name of doctor who is receiving maximum salary.
View Answer

Consider the table given below:

eID eName Designation Address Age
1001 Rabin Marketing Kathmandu 27
1002 Shekhar Sales Lalitpur 32
1003 Raman Accounting Lalitpur 33
1006 Rashmi Sales Kathmandu 31
1015 Sushmita Marketing Bhaktapur 28
1027 Shubham Marketing Bhaktapur 29


Write the following commands for the given table.

  1. Create a table named 'Employees' for the following entries, using different constraints. Also use primary key in the table creation.
  2. Insert all the data in the employees table like given above.
  3. Select all the entries with designation 'Marketing'.
  4. Select all the entries with address 'Kathmandu'.
View Answer

Consider the following relational database schema consisting of the four relation schemas.

  • passenger (pid, pname, pgender, pcity)
  • agency (aid, aname, acity)
  • bus (bid, bdate, time, src, dest)
  • booking (pid, aid, bid, bdate)

 

Answer the following question using relational algebra queries:

  1. Give the details of all buses from Dhangadhi to Kathmandu.
  2. Find the name of passengers who booked at least one bus.
  3. Find the bus number for the passenger with pid p04 for bus to butwal before 20/05/2021.
  4. Find the name of passenger who has not booked any bus.
  5. Find the details of all male passengers associated with Ugratara Agency.
View Answer

Consider the relational database:

  • Students (Roll, SName, SAddress, SContact, SFee)
  • Teachers (TID, TName, TSalary, TAddress)
  • Teaches (Roll, TID)

 

Write the SQL statement for the following:

  1. Find the record of the student who pay fee in the range of Rs. 1000 and Rs. 10000.
  2. Find the name of teacher consisting of at most 6 characters.
  3. List the name of all teachers in ascending order.
  4. Find the name of the highest paying teacher.
  5. Insert a new tuple in the relation teacher.
View Answer

Consider the relational database:

  • Students (Roll, SName, SAddress, SContact, SFee)
  • Teachers (TID, TName, TSalary, TAddress)
  • Teaches (Roll, TID)

 

Write the relational algebra for the following:

  1. Find the Roll of all students who are taught by teacher "Raman".
  2. Delete all students who are from Biratnagar.
  3. Increase the fee of students who are from Kathmandu by 2%.
  4. Find the name of teacher who lives in Kathmandu and get salary greater than Rs. 100000.
  5. Change the name of student to Sushila whose roll number is 10.
View Answer

Consider the following database, where primary keys are underlined.

  • Supplier(supplier_id, supplier_name, city)
  • Supplies(supplier_id, part_id, quantity)
  • Parts(part_id, part_name, color, weight)

 

Write relation algebra and SQL expressions for each of the following queries.

  1. Find the name of all suppliers located in the city "Kathmandu" that supplies part 'P01'.
  2. Find the name of all parts supplied by "RD Traders".
  3. Find the name of all parts that are supplied in quantity greater than 300.
  4. Find the number of parts supplied by "S02".
  5. Find the number of parts supplied by each supplier.
View Answer

Consider the following schema of the relational database.

  • Publisher(pid, name, location)
  • Book(bid, title, author, page, price)
  • Publish(bid, pid, publish_date)

 

Write Relational Algebra expressions for the following:

  1. Find title of book published by Addison_wisely.
  2. Update the author name to "MNP" of book "introduction database".
  3. Find the publish date of book "Database Management".
View Answer

Normalize the following Student_Course table to 2NF.

S_ID S_Name C_ID C_Name
1 Subham C01 English
1 Subham C02 Economics
2 Supriya C03 Mathematics
3 Sawan C01 English
View Answer

Consider schema U = { A, B, C, D, E, F } and the set of functional dependencies:

{AB→C, B→E, CF→D}

 

Find closure of AB.

View Answer