Consider the following relations for a bus reservation system application:

BUS (ROUTENO, SOURCE, DESTINATION) PASSENGER

(PID, PNAME, DOB, GENDER)
BOOK_TICKET PID, ROUTENO, JOURNEY_DATE, SEAT_NO)

 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include constraint that DOB of passenger should be after 2010
b. Display the passengers who had booked the journey from Bangalore to Chennai on 03- NOV-2014

c. List the details of passengers who have traveled more than three times on the same route.
d. Create a view that displays the RouteNo, source, destination and journey_date whichmoves
from Chennai to Pune.
e. Create an index on PID in passenger table.

Consider the following relations for a boat management application for a beach resort:
SAILOR (SID, NAME, DOB, GENDER)
BOAT (BID, BTYPE, BNAME, COLOR)
BTYPE can take two values (D, S)
D – Deluxe and S –Super Deluxe
SAILS (SID, BID, DOT, SHIFT)
DOT – Date of Trip
SHIFT can take two values – FN or AN
 A sailor is assigned a boat on a day. A sailor is permitted to sail the boat for only one shift one
day.
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
Include constraints for BTYPE and SHIFT as mentioned above
a. Develop a SQL query to list the details of boats whose type is Super Deluxe and Color is Red.
b. Develop a view that will keep track of sailor id, sailor name, date of trip, boat id, boat type, boat name
and shift.
c. Create synonym for sailor table.

Consider the following relations for an order processing application:

CUSTOMER (CID, NAME)
PRODUCT (PCODE, PNAME, UNIT_PRICE)
CUST_ORDER (OCODE, ODATE, CID)
ORDER_PRODUCT (OCODE, PCODE, NOU)
NOU – Number of Units. An order can contain many products.
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Ensure that product names should be within Laptop, Mouse, Server, Air conditioner
b. Develop a SQL query to list the details of products whose unit price is greater than the average
 price of all products
c. List the customer names who have orders more number of products
d . Create a view that displays the PCODE, PNAME and NOU of the product ordered

Consider the following relations for a transport management system application:

BUS (ROUTENO, SOURCE, DESTINATION)
DRIVER (DID, DNAME, DOB, GENDER)
ASSIGN_ROUTE (DID, ROUTENO, JOURNEY_DATE)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include constraints that the routeNo starts with letter 'R' and gender of driver is always 'Male'
b. Develop a SQL query to list the details of drivers who have traveled more than three times on
the same route
c. Create a sequence named Driver_Sequence that will get incremented by 1. Use the created
sequence while inserting DID into Driver table.
d. Create a view that displays the DID, DNAME assigned for RouteNo 'R5' on 02-NOV-2014

Consider the following relations for a transport management system application:

DRIVER (DCODE, DNAME, DOB, GENDER)
CITY (CCODE, CNAME)
TRUCK (TRUCKCODE, TTYPE)
TTYPE can take two values (‘L’,’H’) L-Ligh and H- Heavy
Each truck is assigned a unique truck code. There can be many trucks belonging tothe same truck type.
DRIVE_TRUCK (TRUCKCODE, DCODE, DOT, CCODE)
DOT – Date of Trip
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint as mentioned above and the gender of driver is always 'male'.
b. Develop a SQL query to list the details of each driver and the number of trips traveled.
c. Create an index on truck_code in Drive_truck table
d. Create a view that displays the Driver details and also the city in which he drives a truck

Consider the following relations for an order-processing database application in a company:

CUSTOMER (CUSTOMERNO VARCHAR2 (5), CNAME VARCHAR2 (30), CITY
VARCHAR2 (30))
Implement a check constraint to check CUSTOMERNO starts with ‘C’
CUST_ORDER (ORDERNO VARCHAR2 (5), ODATE DATE, CUSTOMERNO REFERENCES
CUSTOMER, ORD_AMT NUMBER (8))
Implement a check constraint to check ORDERNO starts with ‘O’
ITEM (ITEMNO VARCHAR2 (5), ITEM_NAME VARCHAR2 (30), UNIT_PRICE NUMBER (5))
Implement a check constraint to check ITEMNO starts with ‘I’
ORDER_ITEM(ORDERNO REFERENCES CUST_ORDER,ITEMNO REFERENCES ITEM,
QTY NUMBER (3))
SHIPMENT (ORDERNO REFERENCES CUST_ORDER, ITEMNO REFERENCES ITEM,
SHIP_DATE DATE)
Here, ORD_AMT refers to total amount of an order (ORD_AMT is a derived attribute);
ODATE is the date the order was placed; SHIP_DATE is the date an order is shipped.
  The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
Include the constraint as mentioned above.
a. Develop a SQL query to list the order number and number of items in each order
b. Create a synonym on for CUST_ORDER table
c. Create a view that will keep track of the details of each customer and the number of ordersplaced
by each customer

Consider the following relational schema for a banking database application:

CUSTOMER (CID, CNAME)
ACCOUNT (ANO, ATYPE, BALANCE, CID)
An account can be a savings account or a current account. Check ATYPE in ‘S’ or ‘C’.
Acustomer can have both types of accounts.
TRANSACTION (TID, ANO, TTYPE, TDATE, TAMOUNT) TTYPE CAN BE ‘D’ OR ‘W’
D- Deposit; W – Withdrawal
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
Include the constraints as mentioned above.
a. Write a query that lists the customer details and the number of accounts each customer has.
b. Create a sequence named Customer_Sequence which gets incremented by 10 and use this
sequence to give values of CID in customer table.
c. Create a view that will keep track of the details of each customer and account details who have
both savings and current account.

Consider the following relational schema for a banking database application:

CUSTOMER (CID, CNAME)
BRANCH (BCODE, BNAME)
ACCOUNT (ANO, ATYPE, BALANCE, CID, BCODE)
An account can be a savings account or a current account. Check ATYPE in ‘S’ or ‘C’. A customer can
have both types of accounts.
TRANSACTION (TID, ANO, TTYPE, TDATE, TAMOUNT)
TTYPE CAN BE ‘D’ OR ‘W’
D- Deposit; W – Withdrawal
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
Include the constraints as mentioned above.
a. Develop a SQL query to list the details of branches and the number of accounts in each branch.
b. Develop a SQL query to list the details of customers who have performed three transactions on a
day.
c. Create a view that will keep track of the details of each customer and account details who have both
savings and current account.
Consider the following relational schema for a library management system:
BOOK (BOOKID, TITLE, PUBLISHERCODE, NO_OF_COPIES)
PUBLISHER (PUBLISHERCODE, PUBLISHER_NAME)
AUTHOR (AUTHORID, AUTHOR_NAME)
BOOK_AUTHOR (BOOKID, AUTHORID)
BORROWWER (CARDNO, NAME)
BOOK_LOAN (BOOK_ID, CARDNO, DATEOUT, DUEDATE, STATUS)
Implement a Check Constraint for STATUS (‘R’ – Returned, ‘T’ – To be returned)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
Include the constraints as mentioned above.
a. Develop a SQL query to list the details of borrowers who do not have any books checked out.
b. Develop a SQL query to list the details of borrowers who have more than five books checked
out.
c. Create an index on BookID in Book_Loan table
d. Create a view that will keep track of the card number, card holders name and number ofbooks
borrowed (Number of books with status ‘T’)

Consider the following Staff relational schema:

STAFF (STAFFNO, NAME, DOB, GENDER, DOJ, DESIGNATION, BASIC_PAY,
DEPTNO)
GENDER must take the Value ‘M’ or ‘F’
DEPT (DEPTNO, NAME)
SKILL (SKILL_CODE, DESCRIPTION, CHARGE_OUTRATE)
STAFF_SKILL (STAFFNO , SKILL_CODE)
PROJECT (PROJECTNO, PNAME, START_DATE, END_DATE, BUDGET,
PROJECT_MANAGER_STAFFNO)
WORKS (STAFFNO, PROJECTNO, DATE_WORKED_ON, IN_TIME, OUT_TIME)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
Include the constraints as mentioned above.
a. Develop a SQL query to list the details of staff who earn less than the basic pay of all staff.
b. Create a view that keeps track of DeptNo, DeptName and number of staff in each department.
c. Develop a SQL query to list the details of staff who have more than three skills.
d. Create an index on StaffNo in Works table

Consider the following relational schema for a company database application:

EMPLOYEE (ENO, NAME, GENDER, DOB, DOJ, DESIGNATION, BASIC, DEPT_NO, PAN,
SENO)
Implement a Check Constraint for GENDER PAN – Permanent account Number
SENO – Supervisor Employee Number
DEPARTMENT (DEPT_NO, NAME, MENO)
MENO - Manager Employee Number
PROJECT (PROJ_NO, NAME, DEPT_NO)
WORKSFOR (ENO, PROJ_NO, DATE_WORKED, HOURS)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
Include the constraints as mentioned above.
a. Develop a SQL query to list the details of department which has more than 3 employees
working for it.
b. Create a view that keeps track of DeptNo, DeptName and number of employees in each
department.
c. Develop an SQL query to list the departments and the details of manager in each department.
d. Create an index on EmpNo in WorksFor table
e. Develop a procedure Employee_Increment that will accept Employee number and increment
amount as input and update the basic pay of the employee in the employee table.

Consider the following relational schema for a Product Sales database application:
Product (Prodid, Prodesc, Price, Stock)
Purchase (Purid, Proid, qty, supplierName)
Sales (Saleid, Proid, qty, custname)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on Saleid that it starts with letter ‘S’.
b. Display the ProdID and the sum of quantity purchased for each product.
c. Create a view that keeps track of Prodid, price, Purid, qty and customerName who made the
purchase.
d. Create a sequence named Product_Sequence that gets incremented by 10 and use it for
inserting Prodid values in Product table.

Consider the following relational schema for a Sales database application:

Product (Prodid, Prodesc, Price, Stock)
Purchase (Purid, Proid, qty, supplierName)
Sales (Saleid, Proid, qty, custname)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on Saleid that it starts with letter ‘S’.
b. Display the ProdIDs of the product which are purchased more than 5 times
c. Create a view that keeps track of Prodid, price, Purid, qty and customerName who made the
purchase.
d. Create a sequence named Product_Sequence that gets incremented by 10 and use it for
inserting Prodid values in Product table.

Consider the following relational schema for a Loan database application:

Customer (Custid, Custname, Age, phno)
Loan (Loanid, Amount, Custid)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on Loanid that it starts with letter ‘L’.
b. Display the list of the customerids and total Loan amount taken
 c. Display the CustId and CustName who have taken less than 2 loans
d. Create a view that keeps track of Custid, Custname, loanid and loan amount.
e. Create a sequence named Customer_Sequence that gets incremented by 3 and use it for
inserting Custid values in Customer table.

Consider the following relational schema for a Loan database application:

Customer (Custid, Custname, Age, phno)
HLoan (HLoanid, Amount, Custid)
VLoan (VLoanid, Amount, Custid)
Where HLoan is Housing loan and VLoan is a Vehicle loan.
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on HLoanid that it starts with letter ‘H’ and VLoanid starts with letter ‘
b. Display the number of VLoan taken by a particular customer id
c. Display the list of the customerids and total HLoan amount taken.
d. Create a view that keeps track of customer details who have taken both HLoan and VLoan.
e. Create a sequence named Customer_Sequence that gets incremented by 3 and use it for
inserting Custid values in Customer table.

Consider the following relational schema for a Loan database application:

Customer (Custid, Custname, Addr, phno,pan_no)
Loan (Loanid, Amount, Interest,Custid)
Account (Accid, Accbal, Custid)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on Custid that it starts with letter ‘C’
b. Display the customer id, name and account balance. Sort the output using custid
c. Display the accounts of custids ‘C01’,’C02’,’C03’
d. Display the custid who has account balance larger than other customers
e. Create an index on Accid of Account table.
f. Create a view that keeps track of customer id, loan amount and account balance.

Consider the following relational schema for a Sales database application:

Product (Prodid, Prodesc, Price, Stock)
Purchase (Purid, Proid, qty, supplierName)
Sales (Saleid, Proid, qty, custname)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on Saleid that it starts with letter ‘S’.
b. Display the names who are both supplier as well as customer
c. Display the amount (price * qty) of Products in each Sales.
d. Create a view which displays Product ids and sum of quantity in sales
e. Create a sequence named Product_Sequence that gets incremented by 10 and use it for
inserting Prodid values in Product table.
Consider the following relational schema for a Loan database application:
Customer (Custid, Custname, Age, phno)
Loan (Loanid, Amount, Custid, Emi)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on Custid that it starts with letter ‘C’.
b. Update the loan amount by increase in 2 % for all customers
c. Display the custid and Custname whose loan amount lies in the range of 30,000 to 50,000
d. Display the CustId and CustName who have taken less than 2 loans
e. Create a view that keeps track of Custid, Custname, loanid and loan amount.
f. Create a sequence named Customer_Sequence that gets incremented by 3 and use it forinserting
Custid values in Customer table.

Consider the following relational schema for a Books Ordering database application:
Books (isbn, title, author, stock_qty, price, pub_year)
Customers (cust_id, cust_name, address)
Orders (order_no, cust_id, order_date) where cust_id refs Customers(cust_id)
Order_list (order_no, isbn, qty, ship_date) where order_no refs Orders(order_no),
isbn refs Books (isbn)
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on Cust_id that it starts with letter ‘C’.
b. Display the custid and Custname who have ordered more than 3 books on the same date
c. Display the CustId and CustName who have ordered very few number of books.
d. Create a view that keeps track of books that are ordered on 05-NOV-2014. Display isbn, title,
author, order_no, quantity and order_date.

Consider the following relational schema for Products Order database application:
Products (p_id, p_name, retail_price, qty_on_hand)
Orders (order_id, order_date)
Order_details (order_number, product_number, qty_ordered)
Where: order_number references order_id, product_number references p_id
 The primary keys are underlined. Identify the foreign keys and draw schema diagram
 Create the above mentioned tables and populate the tables
Note: Read all questions and populate values accordingly.
a. Include the constraint on orderid that it starts with letter ‘O’.
b. Display the ProdID and the sum of quantity ordered for each product.
c. Create a view that keeps track of P_id, price, order_id, qty_ordered and ordered_date.
Consider the insurance database given below.
PERSON(driver_id, name, address)
CAR(regno, model,year )
ACCIDENT(report_number,accd_date,location)
OWNS(driver_id,regno)
PARTICIPATED(driver_id,regno,report_number,damage_amount)

 Create the above tables by properly specifying the primary keys and foreign keys and enter at least
five tuples for each relation.
a. Update the damage amount for the car with specific regno in the accident with report number 12 to
25000.
b. Add a new accident to the database.
c. Find the total number of people who owned cars that were involved in accidents in the year 2008
d. Find the number of accidents in which cars belonging to a specific model were involved.

Consider the following employee and department tables.

EMPLOYEE(empno, ename, designation, manager, hiredate, salary, commission, deptno)
DEPARTMENT(deptno, dname, location)

 Create the above tables by properly specifying the primary keys and foreign keys and enter at least
five tuples for each relation.
a. List the names of employees whose name contain substring ‘LA’.
b. List the details of employees of salary are greater than or equal to the average salary of employee
table.
c. Create a view which consists of details of all ‘SALESMAN’.

Consider the following tables.

SAILOR(sid, sname, rating, age)
BOATS(bid, bname, colour)
RESERVES(sid, bid, day)
 Create the above tables by properly specifying the primary keys and foreign keys and enter at
least five tuples for each relation.
a. List the sailors in the descending order of their rating.
b. List the sailors whose youngest sailor for each rating and who can vote.
c. List the sailors who have reserved for both ‘RED’ and ‘GREEN’ boats.
d. List the details of the oldest sailor for each rating level.
consider the following relations for order processing database application in a company.
CUSTOMER(custno, cname, city)
ORDER(orderno, odate, custno, ord_amt )
ORDER_ITEM(orderno, itemno, quantity)
ITEM(itemno, unitprice)
SHIPMENT(orderno, warehouseno, ship_date)
WAREHOUSE(warehouseno, city)
 Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
a. Produce a listing: custname , No_of_orders , Avg_order_amount , where the
middle column is the total number of orders by the customer and the last
column is the average order amount for that customer.
b. List the orderno for orders that were shipped from all the warehouses that the
company has in a specific city.
c. Demonstrate the deletion of an item from the ITEM table and demonstrate a
method of handling the rows in the ORDER_ITEM table that contains this
particular item.

Consider the following database of student enrollment in courses and books adopted forthat
course.
STUDENT(regno, name, major, bdate)
COURSE(courseno, cname, dept)
ENROLL(regno, courseno, sem, marks)
BOOK_ADOPTION(courseno, sem, book_isbn)
TEXT(book_isbn,book_title,publisher, author)

 Create the above tables by properly specifying the primary keys and foreign keys
and enter at least five tuples for each relation.
a. Add a new text book to the database and make this book to be adopted by some
department.
b. Produce a list of text books ( includes courseno , book_isbn , book_title ) in the
alphabetical order for courses offered by the 'CS' department that use more than
two books.
c. List any department that has all its books published by a specific publisher.

The following are maintained by a book dealer.

AUTHOR(author_id, name, city, country)
PUBLISHER(publisher_id, name, city, country)
CATALOG(book_id, title, author_id, publisher_id , category_id, year, price)
CATEGORY(category_id, description)
ORDER_DETAILS(order_no, book_id, quantity)

 Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
a. Give the details of the authors who have 2 or more books in the catalog and the price of
the books is greater than the average price of the books in the catalog and the year of
publication is after 2000.
 b. Find the author of the book that has maximum sales.
c. Demonstrate how you increase the price of books published by a specific publisher by
10%.

Consider the following database for a banking enterprise.

BRANCH(branch_name, branch_city, assets)
ACCOUNT(accno, branch_name, balance)
DEPOSITOR(customer_name, accno)
CUSTOMER(customer_name, customer_street, customer_city)
LOAN(loan_number, branch_name, amount)
BORROWER(customer_name, loan_number)

 Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
a. Find all the customers who have at least two accounts at the main branch.
b. Find all the customers who have an account at all the branches located in a specific
city.
c. Demonstrate how you delete all account tuples at every branch located in a specific
city.

Consider the following employee and department tables.

EMPLOYEE(empno, ename, designation, manager, hiredate, salary, commission, deptno)
DEPARTMENT(deptno, dname, location)

 Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
a. List the employees who joined before 1981.
b. List the total information of employee table along with dname and location of all
employees working under ‘Accounting’ and ‘Research’ in the descending order of
deptno.

Consider the following database for a banking enterprise.

CUSTOMER_FIXED_DEPOSIT(cust_id, last_name,mid_name,first_name,fixed_deposit_no,
amount, rate_of_interest)
CUSTOMER_LOAN(loan_no, cust_id, amount)
CUSTOMER_DETAILS(cust_id, acc_type )
 Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
a. List customer names of all customer who have taken a loan > 3,00,000.
b. List customer names of all customer who have the same account type as customer
‘jones simon’.
c. List customer names of all customer who do not have a fixed deposit.
Consider the following databases.
CUSTOMER(custno, custname, city, phone)
ITEM(itemno, itemname, itemprice, quantity)
INVOICE(invno, invdate, custno)
INVITEM(invno, itemno, quantity)

 Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
a. Display all item name along with the quantity sold.
b. Display item name and price as single column like “<item> price is <price>”
c. Display invoices, customer name and item names together (use join).
d. Find the customers who are not from “Chennai” (use set operator).

Consider the following database for a banking enterprise.

BRANCH(branch_name, branch_city, assets)
ACCOUNT(accno, branch_name, balance)
DEPOSITOR(customer_name, accno)
CUSTOMER(customer_name, customer_street, customer_city)
LOAN(loan_number, branch_name, amount)
BORROWER( customer_name, loan_number)
 Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
a. Display all the customers who were depositor and borrower.
b. Display all the customer name who are only depositor.
c. Display all branch name whose assets are greater than assets of branches located in
“Coimbatore” city.

Consider the employee databases.

EMPLOYEE(empname, street, city)
WORKS(empname, companyname, salary)
COMPANY(companyname, city)
MANAGES(empname, managername)
 Create the above tables by properly specifying the primary keys and foreign keys and
enterat least five tuples for each relation.
a. Find the names of all employees work for ‘First bank corporation’.
b. Find the names,street addresses and cities of residence of all employees who work for
‘First bank corporation’ and earn more than 200000 per annum.
c. Find the names of all employees in this database who live in the same city as the
companies for which they work.
d. Find the names of all the employees who earn more than every employees of ‘small
bank corporation’.
Consider the following employee and department tables.
EMPLOYEE(empno, ename, designation, manager, hiredate, salary, commission, deptno)
DEPARTMENT(deptno, dname, location)

 Create the above tables by properly specifying the primary keys and foreign keys and
Enter at least five tuples for each relation.
a. List the employees who annual salary is between 22000 and 25000
b. List the employees names along with their manager names.
c. List the dept who employees maximum no of ‘CLERK’s.

Consider the following company database.

EMPLOYEE(eno, name, dob, doj, designation, basicpay, deptno)
DEPARTMENT(deptno, name)
PROJECT(projno, name, deptno)
WORKSFOR(eno, projno, hours)
 Create the above tables by properly specifying the primary keys and foreign keys and
enterat least five tuples for each relation.
a. List the department number and the number of employees in each department.
b. List the details of employees who have worked in more than three projects on a day.
c. Develop a view that will keep track of the department number, the number of employees in
the department and the total basic pay expenditure for each department.

Consider the following employee and department tables.

EMPLOYEE(empno, ename, designation, manager, hiredate, salary, commission, deptno)
DEPARTMENT(deptno, dname, location)

 Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
a. List the employees whose salary is greater than at least one of the employees of
deptno 30
b. List the name, job, salary of employees in the department with the highest average salary.
c. List the employees who are working either as manager or analyst with salary ranging
from 2000 and 5000.