Thursday, August 25, 2011
Experiment No. 2,3,4
Experiment No. 2
Q-1> Display all tables of oracle ?
Q-2> Display a column in table dept ?
Q-3> List all dept names and location ?
Q-4> Display diff jobs available for an emp ?
Q-5> Compile the entire details of employee name “smith” ?
Q-6> Give list of emp name & their job spec who are working in dept no 20 ?
Q-7> Compile the details of emp working in dept no 3 ?
Q-8> Obtain list of all empno and names who are manager ?
Q-9> Display names of all emp whose comm is greater then salary ?
Q-10> Find list of emp whose sal is greater then 2000 along with their job spec ?
Q-11> Give list of all emp along with their monthly and annual income ?
Q-12> List all emp names and their salaries who are working in dept no 10 ?
Q-13> Give details of all dept and their no’s while changing the column name as department and department no ?
Q-14> List all managers with their respective name manager and deptno ?
Q-15> Display list of emp whose job is analyst ?
Experiment No. 3
Q-1> Create a table client master with the following fields client_no, name, address1, address2, city, state, pin_code, remarks, balance due and add the constraints?
• Create a primary key constraint on the column client_no.?
• create the following check constraints
Data values being inserted into the column client_no must start with ‘c’.
Data values being inserted into the column name should be allowed in upper case only?
Q-2> Insert five rows in the table client master?
Q-3> Show all rows of client master?
Q-4> Add a new column in your table : AGE ?
Q-5> Update the table client master ?
Q-6> Delete a row from client master where age is greater then 60?
Q.7> Create table supplymaster (supplierno,suplliername,address1,address2,city,state,
pincode,remarks,baldue) by using clientmaster.
Q.8> Insert values in table supplymaster by using clientmaster.
Q-9> Drop table client master?
Experiment No. 4
Q-1> Find list of emp whose empno is greater then manager no ?
Q-2> Find all manager not in dept no 10 ?
Q-3> Display everyone in dept no 20 who is neither a clerk, salesman nor analyst ?
Q-4> List all emp names, their jobs, salary in ascending order by theire jobs ?
Q-5> List all empname who are ordered by the jobs within each job there should be Descending order by empno ?
Q-6> Retrieve details of emp whose sal is between 1000 and 2000 both inclusive?
Q-7> Retrieve all details of employees whose name is either smith, blake, allen, scott, clark and King ?
Q-8> To list all employees whose salary is greater than or equal to 2000.
Q-9> To list all the employees hired during the year 1982
Q-10> To get all employees from dept 10 and 20.
Q-11> To list all employees whose name begins with ‘J’.
Q-12> To list employees whose name begins with ‘J’ and has ‘N’ as the third character?
Q-13> To list all employees not entitled for commission.
Q-14> To list all employees who receive commission.
Q-15> To list employees working in dept no 10 or 20.
Q-16> To list employees hired after 01-jan-81 and working in dept no. 10
Q-17> To list employees working in dept other than 10.
Q-1> Display all tables of oracle ?
Q-2> Display a column in table dept ?
Q-3> List all dept names and location ?
Q-4> Display diff jobs available for an emp ?
Q-5> Compile the entire details of employee name “smith” ?
Q-6> Give list of emp name & their job spec who are working in dept no 20 ?
Q-7> Compile the details of emp working in dept no 3 ?
Q-8> Obtain list of all empno and names who are manager ?
Q-9> Display names of all emp whose comm is greater then salary ?
Q-10> Find list of emp whose sal is greater then 2000 along with their job spec ?
Q-11> Give list of all emp along with their monthly and annual income ?
Q-12> List all emp names and their salaries who are working in dept no 10 ?
Q-13> Give details of all dept and their no’s while changing the column name as department and department no ?
Q-14> List all managers with their respective name manager and deptno ?
Q-15> Display list of emp whose job is analyst ?
Experiment No. 3
Q-1> Create a table client master with the following fields client_no, name, address1, address2, city, state, pin_code, remarks, balance due and add the constraints?
• Create a primary key constraint on the column client_no.?
• create the following check constraints
Data values being inserted into the column client_no must start with ‘c’.
Data values being inserted into the column name should be allowed in upper case only?
Q-2> Insert five rows in the table client master?
Q-3> Show all rows of client master?
Q-4> Add a new column in your table : AGE ?
Q-5> Update the table client master ?
Q-6> Delete a row from client master where age is greater then 60?
Q.7> Create table supplymaster (supplierno,suplliername,address1,address2,city,state,
pincode,remarks,baldue) by using clientmaster.
Q.8> Insert values in table supplymaster by using clientmaster.
Q-9> Drop table client master?
Experiment No. 4
Q-1> Find list of emp whose empno is greater then manager no ?
Q-2> Find all manager not in dept no 10 ?
Q-3> Display everyone in dept no 20 who is neither a clerk, salesman nor analyst ?
Q-4> List all emp names, their jobs, salary in ascending order by theire jobs ?
Q-5> List all empname who are ordered by the jobs within each job there should be Descending order by empno ?
Q-6> Retrieve details of emp whose sal is between 1000 and 2000 both inclusive?
Q-7> Retrieve all details of employees whose name is either smith, blake, allen, scott, clark and King ?
Q-8> To list all employees whose salary is greater than or equal to 2000.
Q-9> To list all the employees hired during the year 1982
Q-10> To get all employees from dept 10 and 20.
Q-11> To list all employees whose name begins with ‘J’.
Q-12> To list employees whose name begins with ‘J’ and has ‘N’ as the third character?
Q-13> To list all employees not entitled for commission.
Q-14> To list all employees who receive commission.
Q-15> To list employees working in dept no 10 or 20.
Q-16> To list employees hired after 01-jan-81 and working in dept no. 10
Q-17> To list employees working in dept other than 10.
Tuesday, August 23, 2011
Assignment No.1
Q.1. Construct an E-R diagram for a car-insurance company whose customers own
one or more cars each. Each car has associated with it zero to any number of
recorded accidents.
Q.2. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.
Q.3. A university registrar’s office maintains data about the following entities: (a)courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints.
Q.4. Consider a database used to record the marks that students get in different exams of different course offerings.
a. Construct an E-R diagram that models exams as entities, and uses a ternary
relationship, for the above database.
b. Construct an alternative E-R diagram that uses only a binary relationship between students and course-offerings. Make sure that only one relationship exists between a particular student and course-offering pair, yet you can represent the marks that a student gets in different exams of a course offering.
Q.5. Design an E-R diagram for keeping track of the exploits of your favourite sports team. You should store the matches played, the scores in each match, the players in each match and individual player statistics for each match. Summary statistics should be modeled as derived attributes
one or more cars each. Each car has associated with it zero to any number of
recorded accidents.
Q.2. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.
Q.3. A university registrar’s office maintains data about the following entities: (a)courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints.
Q.4. Consider a database used to record the marks that students get in different exams of different course offerings.
a. Construct an E-R diagram that models exams as entities, and uses a ternary
relationship, for the above database.
b. Construct an alternative E-R diagram that uses only a binary relationship between students and course-offerings. Make sure that only one relationship exists between a particular student and course-offering pair, yet you can represent the marks that a student gets in different exams of a course offering.
Q.5. Design an E-R diagram for keeping track of the exploits of your favourite sports team. You should store the matches played, the scores in each match, the players in each match and individual player statistics for each match. Summary statistics should be modeled as derived attributes
Subscribe to:
Posts (Atom)