SQL Questions
1. 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:
a. Insert a new tuple in the relation patient.
b. List all the patients who were checked by doctors Pankaj and Rubi.
c. List name and id of doctors whose salary is less than Rs. 120,000.
d. Find name of patients whose name begins with 'Ru'.
e. Increase salary of all doctors by 10% who works in Forensic department.
2. 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:
a. Display the name of employees working in 'BBM' Department.
b. List details of employees earning salary above average salary.
c. List details of all students along with the name of project they are involved in. Also
display the name of the department.
d. Decrease salary by 10% of all employees whose salary is more than 10,000.
e. Insert (empid, name, dob, salary) to the table Employee.
3. 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:
a. Display name and address of patients who are suffering from Tuberculosis(TB).
b. Count the number of doctors working in ENT department.
c. Display name of doctor who is receiving maximum salary.
4. 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:
i. Find the record of the student who pay fee in the range of Rs. 1000 and Rs. 10000.
ii. Find the name of teacher consisting of at most 6 characters.
iii. List the name of all teachers in ascending order.
iv. Find the name of the highest paying teacher.
v. Insert a new tuple in the relation teacher.
5. 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.
a. Find the name of all suppliers located in the city "Kathmandu" that supplies part
'P01'.
b. Find the name of all parts supplied by "RD Traders".
c. Find the name of all parts that are supplied in quantity greater than 300.
d. Find the number of parts supplied by "S02".
e. Find the number of parts supplied by each supplier.
6. 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:
i. Find the name of the publisher which hasn't published any book.
ii. Find the name of the publisher whose name contains at most 7 characters.
iii. Delete the attribute location from the publisher table.
iv. Find the name of the publisher which has published the most expensive book.
v. Update page number of all books to 600 whose price is greater than 500.
7. 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:
a. Find the record of the customer with balance in between Rs 10,000 and Rs 100,000.
b. Find the name of depositor consisting of at most 6 characters.
c. Find all the account maintained at branch Bhaktapur.
d. Increase the interest by 10% to the account with balance over Rs 1,000,000.
e. Find the name of depositor whose Name starts with 'Z'.
8. 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.
a. Find the name of Students who live in Lalitpur.
b. Find the name of the teacher who teaches the Database Management System subject.
c. Find the name of the teacher who teaches Computer Organization subject to student
John Smith.
d. Insert a new tuple into relation teachers.
e. Delete records of students whose address is "Pokhara''.
9. Consider the following relational schema.
Employee(E_ID, E_Name, Dept_Name, Salary, Address)
Now write down the SQL for the following:
i. Display E_Name who is taking maximum salary.
ii. Display all Dept_Name where number of Employees are less than 10.
iii. Display E_Name of employee whose address is either Mechi, Nepalgunj or Pokhara.
iv. Display E_ID and E_Name of employees whose name starts with “S” and Ends with
“M”.
v. Increase the salary of employees by 50% whose salary is less than 10000.
10. 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:
a. Insert a new user.
b. Find users whose date of birth is before Jan 01, 2000.
c. Find top 5 categories on which users spend their money.
d. Find categories in which no expenditure has been made so far.
e. Find those users whose expenditure is not less than that of userID 405.
