QT: 0001
Consider the following relations
Student (snum : integer ,sname:string,major :string,level : string,age:integer).
Class (name: string, meets_at: time, room: string, fid: integer).
Enrolled (snum: integer, cname:string).
Faculty (fid: intger, fname: string, deptid: integer);
Enrolled has on record per student-class pair such that the student is enrolled in the class.
Write the SQL queries. No duplicates should be printed.
1. Find the names of the juniors (level=JR) who are enrolled in class taught by X.XXXX
2. Find the age of the oldest student who is either a History major or enrolled in c course taught by X.XXXX.
3. Find the names of all classes that either meet in room R126 or have five or more students enrolled.
4. Find the names of all students who are enrolled in two classes that meet at the same time.
5. Find the names of faculty members who teach in every room in which some class is taught.
6. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.
7. For each level, print the level and the average age of students for that level.
8. For all levels except JR, print the level and the average age of students for that level.
9. For each faculty member that has taught classes only in room R128, print the faculty member’s name and the total number of classes she or he has taught.
10. Find the names of students enrolled in the maximum number of classes.
QT: 0002
Consider the following schema:
Suppliers (sid: integer, sname: string, address: string)
Parts (pid: integer, pname: string, color: string)
Catalog (sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers. Write the following
queries in SQL:
1. Find the pnames of parts for which there is some supplier.
2. Find the snames of suppliers who supply every part.
3. Find the snames of suppliers who supply every red part.
4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.
5. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part).
6. For each part, find the sname of the supplier who charges the most for that part.
7. Find the sids of suppliers who supply only red parts.
8. Find the sids of suppliers who supply a red part and a green part.
9. Find the sids of suppliers who supply a red part or a green part.
10. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.
11. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.
QT: 0003
The following relations keep track of airline flight information:
Flights(flno: integer, from: string, to: string, distance: integer,
departs: time, arrives: time, price: real)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well; every pilot is certified for some aircraft, and only pilots are certified to fly. Write each of the following queries in SQL.
1. Find the names of aircraft such that all pilots certified to operate them have salaries more than $80,000.
2. For each pilot who is certified for more than three aircraft, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified.
3. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.
4. For all aircraft with cruisingrange over 1000 miles, find the name of the aircraft and the average salary of all pilots certified for this aircraft.
5. Find the names of pilots certified for some Boeing aircraft.
6. Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago.
7. Identify the routes that can be piloted by every pilot who makes more than $100,000.
8. Print the enames of pilots who can operate planes with cruisingrange greater than 3000 miles but are not certified on any Boeing aircraft.
9 Print the name and salary of every nonpilot whose salary is more than the average salary for pilots.
10 Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 miles.
11 Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 miles, but on at least two such aircrafts.
QT: 0004
Consider the following relational schema. An employee can work in more than one department; the pct time field of the Works relation shows the percentage of time that a given employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, dname: string, budget: real, managerid: integer)
Write the following queries in SQL:
1. Print the names and ages of each employee who works in both the Hardware department and the Software department.
2. For each department with more than 20 full-time-equivalent employees (i.e., where the part-time and full-time employees add up to at least that many full-time employees), print the did together with the number of employees that work in that department.
3. Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.
4. Find the managerids of managers who manage only departments with budgets greater than $1 million.
5. Find the enames of managers who manage the departments with the largest budgets.
6. If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $5 million.
7. Find the managerids of managers who control the largest amounts.
8. Find the enames of managers who manage only departments with budgets larger than $1 million, but at least one department with budget less than $5 million.
QT: 0005
Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The key fields are underlined, and the domain of each field is listed after the field name. Therefore sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form the key for Catalog. The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in SQL.
1. Find the names of suppliers who supply some red part.
2. Find the sids of suppliers who supply some red or green part.
3. Find the sids of suppliers who supply some red part or are at 221 Packer Street.
4. Find the sids of suppliers who supply some red part and some green part.
5. Find the sids of suppliers who supply every part.
6. Find the sids of suppliers who supply every red part.
7. Find the sids of suppliers who supply every red or green part.
8. Find the sids of suppliers who supply every red part or supply every green part.
9. Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid.
10. Find the pids of parts supplied by at least two different suppliers.
11. Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.
12. Find the pids of parts supplied by every supplier at less than $200. (If any supplier either does not supply the part or charges more than $200 for it, the part is not selected.)
QT: 0006
[a] Consider the following relational schema and briefly answer the questions that follow:
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, budget: real, managerid: integer)
1. Define a table constraint on Emp that will ensure that every employee makes at least $10,000.
2. Define a table constraint on Dept that will ensure that all managers have age > 30.
3. Define an assertion on Dept that will ensure that all managers have age > 30.
4. Write SQL statements to delete all information about employees whose salaries exceed that of the manager of one or more departments that they work in. Be sure to ensure that all the relevant integrity constraints are satisfied after your updates.
[b] Write a program in java for getting time and date information from server using TCP.
QT: 0007
Consider the following relations:
Student(snum: integer, sname: string, major: string,level: string, age: integer)
Class(name: string, meets at: time, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record
per student-class pair such that the student is enrolled in the class.
1. Write the SQL statements required to create these relations, including appropriate versions of all primary and foreign key integrity constraints.
2. Express each of the following integrity constraints in SQL unless it is implied by the primary and foreign key constraint;
(a) Every class has a minimum enrollment of 5 students and a maximum enrollment of 30 students.
(b) At least one class meets in each room.
(c) Every faculty member must teach at least two courses.
(d) Only faculty in the department with deptid=33 teach more than three courses.
(e) Every student must be enrolled in the course called Math101.
(f) No department can have more than 10 faculty members.
(j) A student cannot add more than two courses at a time (i.e., in a single update).
QT : 0008
[a] Consider the following relations
Student (snum : integer ,sname:string,major :string,level : string,age:integer).
Class (name: string, meets_at: time, room: string, fid: integer).
Enrolled (snum: integer, cname:string).
Faculty (fid: intger, fname: string, deptid: integer);
Enrolled has on record per student-class pair such that the student is enrolled in the class.
Write the SQL queries. No duplicates should be printed.
1. Find the names of the juniors (level=JR) who are enrolled in class taught by X.XXXX
2. Find the age of the oldest student who is either a History major or enrolled in c course taught by X.XXXX.
3. Find the names of all classes that either meet in room R126 or have five or more students enrolled.
4. Find the names of all students who are enrolled in two classes that meet at the same time.
5. Find the names of faculty members who teach in every room in which some class is taught.
[b] Write a program to implement database connectivity from Java to database using JDBC.
QT: 0009
[a] Consider the following schema:
Emp(empno:number,ename:string,job:string,mgr:integer,hiredate date, sal:integer, comm.:integer, deptno:integer)
Employee(fname :string, minit : string, lname : string, ssn : integer, bdate : date, address : string, sex : string, salary : integer, superssn : integer, dno : integer)
Department(dname:string, dnumber:integer, mgrssn:integer, mgrstartdate : date)
Works_on(essn : integer, pno : integer, hours : integer)
Write the following queries in SQL:
Count the number of distinct salary values in the database.
Find the average commission for employees.
For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000.
Find the employees who have same job as employee numbered 7369 and having salary same as the salary of the employee numbered 7876 .
Display the employee number and names of all employees who earn more than the average salary .Sort the results in descending order of salary.
Display the name, department number, salary and commission of any employee whose salary and commission matches both the commission and salary of any employee in department number 30.
[b] Write a Java program to get date and time information from the server using UDP
QT: 0010
Consider the following requirements for student database
The following operations should be possible
Adding details for new students
Viewing details of existing students
Deleting details
The database of the student details consists of 7 fields – name, rno, dept, marks in 4 subjects.
a) Adding details
The user provides all the details for a new student. If rollno has already been assigned to another student, an appropriate error message has to be displayed
b) View details
The user can view all the details of a particular student in the database. Indexing is on the basis of roll number. If the roll number entered by the user is not found in the database an appropriate error message has to be displayed
c) Delete details
The user enters the roll number of the student whose details has to be deleted. If the roll number entered by the user is not found in the database an appropriate error message has to be displayed
Develop Student database in Netbeans 5.0 /4.0 Environment. Use Java Swings for GUI and JDBC for the Data base connectivity. Backend is ORACLE 9i.
QT: 0011
Consider the following requirements for Automation of Banks.
The BAS (Banking automation software) should have two modes of operation. One is Supervisor mode and the other is User mode.
The User can maintain two kinds of account. They are savings bank account [SBA] and the current account [CA]. The interest of 3.5% should be given to customer with SBA over the minimum balance maintained. The minimum balance for SBA should be fixed at Rs 5000 and for CA should be fixed at Re 1.No interest is given for CA Users.
The User [both SBA and CA] should be able to deposit or withdraw amount from their respective accounts. Create a separate interface for the same.
The supervisor should be able to view all the Transactions of all users and is authorized to create / Delete / modify all user accounts.
The BAS should automatically generate unique Account numbers for SBA and CA. It should
Have the provision to display interest with other Transaction User wise, Date wise and Month wise.The Total Deposits (all User accounts) of Bank should available in the supervisor mode in month wise.
Develop BAS in Netbeans 5.0 /4.0 Environment. Use Java Swings for GUI and JDBC for
the Data base connectivity. Backend is ORACLE 9i.
QT: 0012
[a] Design a webpage and use the following CSS properties
All the three types of Style sheet
3 background properties
Grouping
Class Selector
Border
3 font properties
[b] Consider the following relational schema and briefly answer the questions that follow:
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, budget: real, managerid: integer)
1. Define a table constraint on Emp that will ensure that every employee makes at least $10,000.
2. Define a table constraint on Dept that will ensure that all managers have age > 30.
3. Define an assertion on Dept that will ensure that all managers have age > 30.
4. Write SQL statements to delete all information about employees whose salaries exceed that of the manager of one or more departments that they work in. Be sure to ensure that all the relevant integrity constraints are satisfied after your updates.
QT: 0013
Write a DHTML document that shows the results of a color survey. The document should contain a form with radio buttons that allows users to vote for their favorite color. One of the colors should be selected as default. The document should also contain a table showing various colors and corresponding percentage of votes for each color (each row should be displayed in the color to which it is referring) Use attributes to format with, border and cell spacing for the table.
QT: 0014
[a] Drivers are concerned with the mileage obtained by their automobiles. One driver has kept track of several tankfuls of gasoline by recording the number of miles driven and the number of gallons used for each tankful. Develop a java script program that will take as input the miles driven and gallons used (both as integer) for each tankful. The program should calculate and output HTML text that displays the number of miles per gallon obtained for each tankful and prints the combined number of miles per gallon obtained for all tankfuls up to this point. Use prompt dialogs to obtain the data from the user.
[b] Write a program in java for getting time and date information from the server using TCP.
QT: 0015
To create a package to process payroll information of a company .
Tables used :
1) EMPTABLE table with following fields
SSN
ENAME
DEPARTMENT
ADDRESS
DOJ
BASIC
LOAN table with following fields
SSN
LOANDATE
LOANAMOUNT
INTEREST
LOANPERIOD
LOANTYPE
TOTALAMOUNT
BALANCE
EMI
The package provides the following functions over the company database .
Create entry for new employee in the table using the information provided by the employee in the emptable table ..
.For each employee, Gross salary is calculated from the employees’ basic pay using the HRA, DA, CCA, and performance interest .
Employees are eligible to avail loan from the company .Based the amount of loan they avail interest is calculated over the amount, and the EMI is calculated which is then deduced from their monthly salary of the employee for the period specified by the employee .This gives the net salary .
Employees who earn more than 1,15,000 p.a , pay Income tax based on the following criteria :
1,15,000 to 3,00,000 - 20%
3,00,000 to 5,00,000 - 30%
More than 5,00,000 - 45%
QT: 0016
Develop a Banking application that should perform the following operations :
Creating an account for a new user
Deposit /Withdraw amount from account
Calculate interest for savings account holder(if available)
CUSTOMER table with following fields
FNAME
LNAME
ADDRESS
ACCNO
USERNAME
PASSWORD
(ii) ACCOUNT table with the following fields
ACCNO
ACCTYPE
AMOUNT
BALANCE
QT: 0017
Emp(empno:number,ename:string,job:string,mgr:integer,hiredate date, sal:integer, comm.:integer, deptno:integer)
Employee(fname :string, minit : string, lname : string, ssn : integer, bdate : date, address : string, sex : string, salary : integer, superssn : integer, dno : integer)
Department(dname:string, dnumber:integer, mgrssn:integer, mgrstartdate : date)
Works_on(essn : integer, pno : integer, hours : integer)
Increment the salary of every employee by 300 and display their names and original salary.
Retrieve the name and salary of all employees and label it as NAME and SALARY.
Display distinct department and job in them.
Retrieve the employee details whose salary is less than or equal to commission.
Display the names of the employees Born between 01-JAN-1980 and 01-JAN-1982.Assume different dates.
List the employee number, name and manager id of SUNIL, ANAND and VIMAL
List the employees who don’t have Boss or Manager.
List the employee whose salary is greater than or equal to 1100 and/or their job 'clerk'
List the names of the employee whose job is not "Clerk, Manager, Analyst".
List the names of the employee according to their date of joining in the increasing chorological order.
Sort the rows based on the alias name ‘ANNSAL’
Sort the deptno in the Ascending order, sal in the each dept should be sorted in Descending order.
Display the name, job and salary for all employee whose job is clerk or analyst and their salary is not equal to $1000,$3000,or $5000.
Display the names of all employees where the third letter name is an A.
Display the name, job and salary for all employee whose job is clerk or analyst and their salary is not equal to $1000,$3000,or $5000.
QT : 0018
Emp(empno:number,ename:string,job:string,mgr:integer,hiredate date, sal:integer, comm.:integer, deptno:integer)
Employee(fname :string, minit : string, lname : string, ssn : integer, bdate : date, address : string, sex : string, salary : integer, superssn : integer, dno : integer)
Department(dname:string, dnumber:integer, mgrssn:integer, mgrstartdate : date)
Works_on(essn : integer, pno : integer, hours : integer)
Salgrad(grade: integer, losal : integer, hisal : integer, deptno : integer)
Increment the salary of every employee by 300 and display their names and original salary.
Retrieve the name and salary of all employees and label it as NAME and SALARY.
Display the names of the employees Born between 01-JAN-1980 and 01-JAN-1982.Assume different dates.
Display name of the employees, length of the name, concatenate job with name, Position of the character ‘A’ in the name for employees whose job contains ‘SALES’ as prefix.
Display name, salary, commission, and their annual salary with commission for all employees.
Write a query that hikes the salary of employees whose job is ‘ANALYST’ by 10%, ‘CLERK’ by 15%, ‘MANAGER’ by 20%.
Display all possible combination of tuples for emp with dept.
Display all employees name, number, dept number and their dept location.
Write a query that produces the following for each employee : <empname> earns <salary> monthly but wants <3 times salary> label the column salaries.
Write a query that will display the employee’s name with first letter capitalized and all other letters lower case and length of their name , for all employees whose name starts with J, A or M.
Create a query that will display the employee name ,department number and all the employees who work in the same department as a given employee
Display all employee’s names and hire-date along with their manager’s name and hire-date for all employees who have hired before their manager.
Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.
Retrieve the names of employees who have no dependents.
List names of manager who has at least one dependent.
Display all employees including King ,who has no manager.
QT : 0019
Emp(empno:number,ename:string,job:string,mgr:integer,hiredate date, sal:integer, comm.:integer, deptno:integer)
Employee(fname :string, minit : string, lname : string, ssn : integer, bdate : date, address : string, sex : string, salary : integer, superssn : integer, dno : integer)
Department(dname:string, dnumber:integer, mgrssn:integer, mgrstartdate : date)
Works_on(essn : integer, pno : integer, hours : integer)
Salgrad(grade: integer, losal : integer, hisal : integer, deptno : integer)
Retrieve all employees in department 5 whose salary is between $30,000 and $40,000.
Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.
Retreive the social security numbers of all employees who works on project number 1,2 or 3.
Count the number of distinct salary values in the database.
For each department, retrieve the department number, the number of employees in the department, and their average salary.
Find the departments having maximum salary greater than $2000.
Find the department that has maximum average salary.
Display the name, salary and the department of employees who get salary greater than the average salary of their respective department.
Display the employee name, department number and job title for all employees whose department location is Dallas.
Display the name, department name and salary of any employee whose salary and commission matches both the salary and commission of any employee located in DALLAS.
[b] Write a program in java for getting time and date information from the server using TCP.
QT : 0020
[a] Write the queries for the following in SQL
Creating a view.
Describing the structure of a view.
Retrieving Data from a View.
Modifying a view.
Using the ‘WITH CHECK OPTION’.
Removing a view.
[b] Create a tourism web site showing places of site seeing using hotspots and giving details about each of the places that can be visited. Make use of the following tags
(i) Table
(ii) Frame
(iv) Links (active, visited, hover should be of different color)
(v) use ordered, unordered and definition list
(vi) set the background to yellow and text to black color
Also there should be provision to provide feedback to this web site (Provide necessary
validation).
QT : 0021
[a] Design a shopping mall website allowing users to select a variety of items and to generate a bill for payment on check out. Make use of the following DHTML effects.
Background color Change (Get the color from the user)
Change of Images
Text Change
[b] Write the queries for the following in SQL
1) Creating a view.
2) Describing the structure of a view.
3) Retrieving Data from a View.
4) Removing a view
1 comments:
Can I Get Key For This
Post a Comment