1. Database Schema for a sales system scenario Customer(Cust id : integer, cust_

Question

1. Database Schema for a sales system scenario
Customer(Cust id : integer, cust_name: string)
Item(item_id: integer, item_name: string, price: integer)
Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold: integer)
For the above schema, perform the following?
a) Create the tables with the appropriate integrity constraints
b)Impose check constraint on price column of item table by that price <=200.
c) Insert around 5 records in each of the tables
d) List all the bills for the current date with the customer names and item numbers.
e) Give a count of how many products have been bought by each customer
f) Create a view which lists out the bill_no, bill_date, cust_id, item_id, price, qty_sold,
amount
g) Change cust_name column type as CHAR from VARCHAR in customer table by appropriate command.

2. Database Schema for a sales system scenario
Customer(Cust id : integer, cust_name: string)
Item(item_id: integer, item_name: string, price: integer)
Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold: integer)
For the above schema, perform the following?
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Give a count of how many products have been bought by each customer
d) Give a list of products bought by a customer whose cust_id as 104 (using sub query)
e) List all the Customers only who purchase the items(using set operators).
f) Delete bill data from sale table whose cust_id=101
g) Add Cust_address column to customer table

3. Database Schema for a sales system scenario
Customer(Cust id : integer, cust_name: string)
Item(item_id: integer, item_name: string, price: integer)
Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold: integer)
For the above schema, perform the following?
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Give a count of how many customers have been bought atleast two items.
d)Arrange all bill nos of the customers in ascending order based on item nos.
e) List the details of customers whose bill no between 10 and 50.
f) Find the names of all customers whose item name includes the substring ?corn?.
g)Delete qty_sold column in a sale table.



4. Database Schema for a Student Library scenario
Student(Stud_no : integer, Stud_name: string)
Membership (Mem_no: integer, Stud_no: integer)
Book (book_no: integer, book_name:string, author: string)
Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer)
For the above schema, perform the following?
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Write query to impose condition so that iss_date should not greater than today?s date
d) List all the student names with their membership numbers
e) Write query to change Iss_rec table?s column name ?iss_date? to ?dateof_issue?
f) Write a query to count different book titles (book_names).
g) Create a view which lists out the iss_no, iss _date, stud_name, book_name, author, book_name by using above database tables

5. Database Schema for a Student Library scenario
Student(Stud_no : integer, Stud_name: string)
Membership (Mem_no: integer, Stud_no: integer)
Book (book_no: integer, book_name:string, author: string)
Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer)
For the above schema, perform the following?
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Give a count of how many books have been bought by each student
d) List all the student names who are having Mem_no
e) Write query to add student address column to the student table
f) Write a query to list all the issues given to the student whose stud_no is 101
g) Delete the data from iss_rec whose iss_date is currentdate


6. Database Schema for a Student Library scenario

Student(Stud_no : integer, Stud_name: string)
Membership(Mem_no: integer, Stud_no: integer)
Book(book_no: integer, book_name:string, author: string)
Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer)

For the above schema, perform the following?
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Give a list of books, in alphabetical order, taken by student with stud_no as 508
d) List the total no of books based on author who wrote more than two books
e) List the details of all issues between iss_no 1 and 5
f) List the details of students whose name starts with a ends with r
g) Write a query to delete ?author? column in book table



7. Database Schema for a Bank scenario
Branch (branch_name, branch_city, assets)
Customer (customer_name, customer_street, customer_city)
Loan (loan_number, branch_name, amount)
Borrower (customer_name, loan_number)
Account (account_number, branch_name, balance)
Depositor (customer_name, account_number)
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Impose check constraint on amount column of Loan table by that amount <=25000.
d) Find the customer name, loan number and loan amount of all customers having a loan
at the Perryridge branch.
e)Write query to change Loan table?s column name ?loan_number? to ?l_num?
f) Find the average account balance at the Perryridge branch.
g) Create a view of all loan data in the loan relation, hiding the amount attribute.

8. Database Schema for a Bank scenario
Branch (branch_name, branch_city, assets)
Customer (customer_name, customer_street, customer_city)
Loan (loan_number, branch_name, amount)
Borrower (customer_name, loan_number)
Account (account_number, branch_name, balance)
Depositor (customer_name, account_number)
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Find the names of all branches where the average account balance is more than $1,200.
d) Find all customers who have both a loan and an account.
e) Find the average account balance at the Perryridge branch.
f)Write query to add Account_ type column to the Account table
i) Delete all accounts at every branch located in the city ?Needham?.

9. Database Schema for a Employee-pay scenario
employee(emp_id : integer, emp_name: string)
department(dept_id: integer, dept_name:string)
paydetails(emp_id : integer, dept_id: integer, basic: integer, deductions: integer, additions: integer, DOJ: date)
payroll(emp_id : integer, pay_date: date)
For the above schema, perform the following?
a)Create the tables with the appropriate integrity constraints
b)Impose check constraint on basic column of paydetails table by that basic>=6000
c) Insert around 5 records in each of the tables
d) List the employee details department wise
e) Give a count of how many employees are working in each department
f) Create a view which lists out the emp_name, department, basic, deductions, netsalary
g) Change emp_name column type as CHAR from VARCHAR in employee table by appropriate command.


10. Database Schema for a Employee-pay scenario

employee(emp_id : integer, emp_name: string)
department(dept_id: integer, dept_name:string)
paydetails(emp_id : integer, dept_id: integer, basic: integer, deductions: integer, additions: integer, DOJ: date)
payroll(emp_id : integer, pay_date: date)

For the above schema, perform the following?
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Give a count of how many employees are working in each department.
d) List all the employee names who joined on a particular date (using subquery)
e) List all employees having date of joing(using set operators)
f) Delete employee information whose name is ?Sita?
g) Add Marital status(married/unmarried) column to the employee table


11. Database Schema for a Employee-pay scenario

employee(emp_id : integer, emp_name: string)
department(dept_id: integer, dept_name:string)
paydetails(emp_id : integer, dept_id: integer, basic: integer, deductions: integer, additions: integer, DOJ: date)
payroll(emp_id : integer, pay_date: date)

For the above schema, perform the following?
a) Create the tables with the appropriate integrity constraints
b) Insert around 5 records in each of the tables
c) Give a count of how many employees are working in each department with atleast 2 employees.
d) List all the employee details whose department name starts with ?e? and ends with ?d?.
e) List the details of employees whose basic salary is between 5,000 and 20,000
f) List names of employees along with DOJ in ascending order.
g) Delete deptname column from department table.

Explanation / Answer

class MyContextInformation : IDisposable { [ThreadStatic] private static MyContextInformation current; public MyContextInformation Current { get { return current; } } private MyContextInformation previous; public MyContextInformation(Object myData) { this.myData = myData; previous = current; current = this; } public void Dispose() { current = previous; } }

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.