Bsc CSIT Semester 4 – Database Management System – Unit 6: SQL (8 Hrs.)

Create two table Courses (CID, Course, Dept) and HoD (Dept, Head) using SQL language with all constraints [Primary key, Foreign key and Referential Integrity]. Assume the types of attributes by your own.

Retrive the TName, SName, SPhone for “ABC” school using SQL from given relation as below.

1. TEACHER (TID, TName, TAddress, TQualification)
2. SCHOOL (SID, SName, SAddress, SPhone)
3. SCHOOL_TEACHER (SID, TID, No_of Period)

Explain Assertion and Triggers with example.

Consider a banking database with three tables and primary keys underlined as given below:
Customer(CustomerID, CustomerName, Address, Phone, Email)
Owns(CustomerID, AccountNumber)
Account(AccountNumber, AccountType, Balance)
Write both relational algebra and SQL queries:
a. To display name of all customers who live in “Kathmandu”.
b. To count total number of customers.
c. To find name of those customers who have balance greater than or equal to 100000.
d. To find average balance of each account type.

Consider a banking database with three labels and primary key underlined as given below:
Customer (CustomerID , CustomerName, Address, Phone, Email)
Borrows (CustomerID, LoanNumber )
Loan ( LoanNumber , LoanType, Amount )

Write both relational algebra and SQL queries:
a. To display name of all customers who live in “Lalitpur” in ascending order of name.
b. To count total number of customers having loan at the bank.
c. To find name of those customers who have loan amount greater than or equal to 500000.
d. To find average loan amount of each accoun’t type.\

Consider the library database schema containing the tables given below where primary keys are underlined.

USER (UserID, User_name, Address)
USES (UserID, BookID, Issued_date, Returned_date)
BOOK (BookID, Book_name, Author, PublisherID)
PUBLISHER (PublisherID, Publisher_name, Address)

Specify the following queries in both SQL and Relational Algebra on this database schema:
a. Retrieve names of all books issued to the user “Hari”.
b. Retrieve the names of all books published by “ABC publication”.
c. Count the number of books published by the author “Ram”.