Questions

Filter

Subject: DBMS

Topic: SQL (Structured Query Language)

Consider the following relational database:

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

 

Write SQL statements for the following:

  1. Insert a new tuple in the relation patient.
  2. List all the patients who were checked by doctors Pankaj and Rubi.
  3. List name and id of doctors whose salary is less than Rs. 120,000.
  4. Find name of patients whose name begins with 'Ru'.
  5. Increase salary of all doctors by 10% who works in Forensic department.
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 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 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 SQL statement for the following:

  1. Find the name of the publisher which hasn't published any book.
  2. Find the name of the publisher whose name contains at most 7 characters.
  3. Delete the attribute location from the publisher table.
  4. Find the name of the publisher which has published the most expensive book.
  5. Update page number of all books to 600 whose price is greater than 500.
View Answer

Consider the relational database:

  • Account(acc_number, branch_name, balance)
  • Depositor(cust_name, acc_number)
  • Branch(branch_name, city)

 

Write the SQL statement for the following: 

  1. Find the record of the customer with balance in between Rs 10,000 and Rs 100,000.
  2. Find the name of depositor consisting of at most 6 characters.
  3. Find all the account maintained at branch Bhaktapur.
  4. Increase the interest by 10% to the account with balance over Rs 1,000,000.
  5. Find the name of depositor whose Name starts with 'Z'.
View Answer

Consider the following relational database of a college.

  • Student(RollNumber, StudentName, Address)
  • Teachers(TeacherlD, TecherName, TeachingSubject)
  • College(RollNumber, TeacherlD)

 

Write SQL for the following requests.

  1. Find the name of Students who live in Lalitpur.
  2. Find the name of the teacher who teaches the Database Management System subject.
  3. Find the name of the teacher who teaches Computer Organization subject to student John Smith.
  4. Insert a new tuple into relation teachers.
  5. Delete records of students whose address is "Pokhara''.
View Answer

Consider the following relational schema.

Employee(E_ID, E_Name, Dept_Name, Salary, Address)

 

Now write down the SQL for the following:

  1. Display E_Name who is taking maximum salary.
  2. Display all Dept_Name where number of Employees are less than 10.
  3. Display E_Name of employee whose address is either Mechi, Nepalgunj or Pokhara.
  4. Display E_ID and E_Name of employees whose name starts with “S” and Ends with “M”.
  5. Increase the salary of employees by 50% whose salary is less than 10000.
View Answer

Following table is provided to you:

Employee

Emp_Id Name Address Salary
1 Anil Parajuli Chabahil 50000
2 Gokul Pandey Pokhara 60000
3 Krishna Bdr Oli Chabahil 70000
4 Sumedha Prajapati Bhaktapur 80000
5 Pravin Shrestha Lazimpat 90000
6 Alka Maharjan Lalitpur 100000

 

Write SQL statements for the above table.

i. To create above table named Employee.

ii. To insert all the values in the table.

iii. To increase the salary of Pravin by 15%.

iv. To change the name of Alka to Mamata.

v. To delete the record of Anil.

View Answer

Consider the following relations.

  • Users(userID, username, password, email, dateOfBirth, gender, registerDate)
  • Categories(categoryID, categoryName, upperLimit)
  • Expenses(expenseID, spentDate, amount, categoryID, userID)

 

Write down the SQL statements for the following:

  1. Insert a new user.
  2. Find users whose date of birth is before Jan 01, 2000.
  3. Find top 5 categories on which users spend their money.
  4. Find categories in which no expenditure has been made so far.
  5. Find those users whose expenditure is not less than that of userID 405.
View Answer