Database Management System · Unit 6 · 8 hrs
SQL
Exam-focused notes for SQL (Database Management System, CSC265): what the TU syllabus asks and how it has actually been tested, with 5 solved past questions from this unit.
What this unit covers
- Data Definition and Data Types
- Specifying Constraints
- Basic Retrieval Queries
- Complex Retrieval Queries
- INSERT, DELETE, and UPDATE Statements
- Views
Complex Retrieval Queries
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.[10]
- Customer(<uCustomerID</u, CustomerName, Address, Phone, Email) - Owns(<uCustomerID</u, <uAccountNumber</u) - Account(<uAccountNumber</u, AccountType, Balance) --- Steps: 1. Apply selection (σ) on Customer table where Address = 'Kathmandu' 2. Apply project...
Full solved answer →Consider a banking database with three tables 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 account type.[10]
- Customer(<uCustomerID</u, CustomerName, Address, Phone, Email) - Borrows(<uCustomerID</u, <uLoanNumber</u) - Loan(<uLoanNumber</u, LoanType, Amount) --- $$\tau{CustomerName}(\pi{CustomerName}(\sigma{Address='Lalitpur'}(Customer)))$$ Where: - $\sigma$ = Se...
Full solved answer →Specifying Constraints
Explain Assertion and Triggers with example. [5]
--- An assertion is a predicate (condition) that expresses a constraint that the database must always satisfy. It is a general integrity constraint that is not tied to a single table but can span multiple tables. - Assertions are part of SQL's Data Definiti...
Full solved answer →Basic Retrieval Queries
Retirve the TName, SName, SPhone for “ABC” school using SQL from given relation as below. [5]
The question asks to retrieve TName (Teacher Name), SName (Student Name), and SPhone (Student Phone) for the school named "ABC" using SQL. To answer this, we assume the following relational schema (standard school database relations): --- --- --- Step Descr...
Full solved answer →Data Definition and Data Types
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. [5]
- Courses (CID, Course, Dept) - HoD (Dept, Head) --- Attribute Type Reason ------------------------- CID INT Numeric course identifier Course VARCHAR(50) Course name as string Dept VARCHAR(30) Department name as string Head VARCHAR(50) Head of Department na...
Full solved answer →Make Unit 6 stick
Practice CSC265 with flashcards & quizzes