Wednesday, March 30, 2011

SE IT ( Assignment )

Notice

All the SE IT students are informed that they have to prepare a soft copy of assignment as given below. Every student have to solve only one quetion from the qution paper.

Roll number Question paper Questions
1 to 7 June 2009 Q.1. to Q.7. (one question each)
8 to 14 Dec 2009 Q.1. to Q.7. (one question each)
15 to 21 May 2010 Q.1. to Q.7. (one question each)
22 to 28 Dec 2010 Q.1. to Q.7. (one question each)
29 to 34 Dec 2007 Q.1. to Q.7. (one question each)

Monday, March 28, 2011

Notice to SE comp students

The following students are notified to complete softcopy of Question assigned in below table. Further we put there answer on the blog for all students.


Roll No
Paper
Question From
Question To
1
May2008
Q.1  [A]  &  [B]
Q3  [A]  &  [B]
9
May2008
Q.4  [A]  &  [B]
Q.6  [A]  &  [B]
11
Dec2008
Q.1  [A]  &  [B]
Q.3  [A]  &  [B]
19
Dec2008
Q.4  [A]  &  [B]
Q.6  [A]  &  [B]
46
May2009
Q.1  [A]  &  [B]
Q.3  [A]  &  [B]
49
May2009
Q.4  [A]  &  [B]
Q.6  [A]  &  [B]
50
Dec2009
Q.1  [A]  &  [B]
Q.3  [A]  &  [B]
53
Dec2009
Q.4  [A]  &  [B]
Q.6  [A]  &  [B]
71
2008,2009(may,Dec)
Q.7 of all paper

   
Note: its compulsory  

Dec2009


may2009


dec2008


May2008


Monday, March 14, 2011

please change the table of Question 2 as

Employee(emp_name,street,city)
Works(emp_name,company_name,salary)
company(company_name,city)
Manager(emp_name,manager_name)

Monday, March 7, 2011

Question Bank

Q.1.
Given the following relational schema

DIVISION(Div #, Div_Name, Director)
DEPARTMENT(Dept #, Dept_Name, Location, Div #)
EMPLOYEE(Emp #, Emp_Name, Salary, Address, Dept #)
State the following queries in SQL
a) Get the employee name, department name and division name for all employees whose salary is above Rs.20,000.
b) List the names of all employees who work in “Information Technology” Division.
c) List the departments and employees in each department located in “Mumbai”.

Q.2. Consider the relational DB
Employee(emp_name,street,city)
Works(emp_name,city)
Manager(emp_name,manager_name)
Give expression in relational algebra and SQL for the following:
i. Find name of all employee in the database who live in same city as the company for which they work.
ii. Find name of employee who earn more than every employee of “small Bank corporation”.
iii. Delete all tuples in work relationship for employee of “small Bank corporation.

Q.3. Discuss the various levels of data abstraction and explain task of DBA.

Q.4. with the help of block diagram explain basic structure of DBMS.

Q.5. write short notes on generalization specialization and weak entity set.

Q.6.
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.7. Explain view in detail with its benefit and limitation?

Q.8. Explain serializability in detail with appropriate example?

Q.9. Explain any five relational algebra operator?

Q.10. Write Short note
a. weak entity set b. Aggregation
c. Specialization and genralization

Assignment No 2

Assignment No.2 (A Batch)

Q1) Given the following relational schema

DIVISION(Div #, Div_Name, Director)
DEPARTMENT(Dept #, Dept_Name, Location, Div #)
EMPLOYEE(Emp #, Emp_Name, Salary, Address, Dept #)
State the following queries in SQL
a) Get the employee name, department name and division name for all employees whose salary is above Rs.20,000.
b) List the names of all employees who work in “Information Technology” Division.
c) List the departments and employees in each department located in “Mumbai”.

Q. 2)
Consider the relational DB
Employee ( employee_name,street, city)
Works (employee_name, company_name, salary)
Company(company_name, city)
Manager(employee_name, manager_name)
Give expression for the following :
(i) Find names of all employees in the database who live in same city as the companty which they work in.
(ii) Find name of employees who earn more than every employee of “Small Bank Corporation”.
(iii) Delete all tuples in works relationship for employees of “Small Bank Corporation”.
Write following queries for given table.
Q.3)
(i) Client_master (Client_no (PK), name, address1, address2, city, state, pincode, bal_due)
(ii) Product_master ( Product_no(PK),Description, Profit_percent, Unit measure, Qty_no_hand, Recorder, Sellprice, Cost-price)
a) Write down SQL syntax to create above tables.
b) Write down SQL syntax to insert data into above tables(one example each).
c) Perform following SQL operation.
(2) Find out names of all the clients.
(3) Print the entire client-master tables.
(4) Retrieve the list of names and the-cities.
(5) List the various products available from the product-master table.
(6) Find the names of all the clients having name starts with letter’a’.

Assignment No.2 (B Batch)

Q.1 Write following queries for given table.

(iii) Client_master (Client_no (PK), name, address1, address2, city, state, pincode, bal_due)
(iv) Product_master ( Product_no(PK),Description, Profit_percent, Unit measure, Qty_no_hand, Recorder, Sellprice, Cost-price)
a) Write down SQL syntax to create above tables.
b) Write down SQL syntax to insert data into above tables(one example each).
c) Perform following SQL operation.

(1) Find out clients who stay in the city Mumbai.
(2) Print the list of clients who are located in city either Pune,Nashik or Nagpur.
(3) Print the list of clients whose balance due are greater than value 10000.
(4) Find out the product with description ‘pen’.
(5) Find the product whose selling price is greater than 2000 and less than or equal to 5000.

Q.2.
Consider the following Employees database:
Employee(employee_name, street, city)
Works(employee_name, company_name, salary)
Company(company_name, city)
Manages(employee_name,manger_name)
Answers the following SQL queries.

(i) Find the names, street address, and city of residence of all employees who works for “XYZ” and earn more than Rs. 25,000.
(ii) Find all the employees who live in the same cities as the company for which they work in.
(iii) List all employees who live in the same cities as their managers .
(iv) List all employees who earn more than the average salaries of all employees in their company.
(v) List the names of companies which has the maximum number of employees.


Q.3)

A Library has the following relations:

LIBRARY (Code, Name, No_of_Books)
PERSON (Id, Name, Age)
IS-NUMBER (Code, Id, Date_of_joining)
BOOKS (Accession_No, Id, Date_of_Borrow)
Borrowed-By (Accession_No, id, Date_of_borrow)
Answer the following queries using SQL/Relational Algebra:

(i) List all the books borrowed before 2nd March 2002 and details of borrowers.

(ii) List the details of the books and the persons who has borrowed the book costing above Rs. 2,000.

(iii) Give details of person who have not borrowed any book.

(iv) List the person who have borrowed at least all the books borrowed by the person with Id 201.



Assignment No.2 (C Batch)
Q.1.
Consider the insurance database where primary keys are underlined and answer
the queries using SQL
PERSON (Driver_id, Name, Address)
CAR (License, Model, Year)
ACCIDENT(Report_no, Data, Location)
OWNS (Driver_id, License)
PARTICIPATED (Driver_id, License, Report_No, Damage amount)

(i) Find the total number of people who owned cars that were involved in accidents in 2004.
(ii) Find the number of accident in which car belonging to ‘John Smith’ were involved.
(iii) Add a new accident to database assume any values for required attributes.
(iv) Delete ‘Santro’ belonging ‘John Smith’. Update the damage amount for the car with license number ‘AABB2000’ in the accident with repert number ‘AR2197’ to $ 3000.



Q.2.
For the given Employee data base, given an expression in SQL for each of the following query :
EMPLOYEE (emp-name, street, city)
WORKS COMPANY (company-name, city)
MANAGES (emp-name, manager-name)
(i) Modify the data base so that ‘Jones’ now lives in ‘Newtown’.
(ii) Find all employees in the database who live in the same cities as the companies for which they work.
(iii) Find the company that has the most employees.
(iv) Give all employees of First Bank Corporation a 10 percent raise




Q.3)
Consider the insurance database where primary keys are underlined and the queries using SQL/Rotational algebra.

PERSON (Driver_id, Name, Address)
CAR (License, Model, Year)
ACCIDENT (Report_No, Data, Location)
OWNS (Driver_id, License)
PARTICIPATED (Driver_id, License, Report_No, Damage amount):
(i) Add a new accident to database assume any valves for required attributes.
(ii) Delete ‘Alto belonging ‘HMB’ updated the damage amount for the car with license no’XXYY 1000’ in the accident with report number’AR 2197’ to $ 1000.
(iii) Find the total number of people who owned cars that were involved in accidents in 2995.
(iv) Find the number of accident in which car belonging to ‘HMB’ were involved.
Answers Experiment No. 1


1. SQL> create table deptit(deptno number(2) primary key,dname varchar(14) not null,loc varchar(13) not null);

Table created.

SQL> desc deptit
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME NOT NULL VARCHAR2(10)
LOC NOT NULL VARCHAR2(13)

SQL> create table empit(empno number(4) primary key,ename varchar(20) not null,deptno number(2),
2 job char(9) check(job=upper(job)),hiredate date default sysdate,foreign key(deptno) references
3 dept(deptno));

Table created.

SQL> describe empit;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(2)
ENAME NOT NULL VARCHAR2(15)
DEPTNO NUMBER(2)
JOB VARCHAR2(10)
HIREDATE DATE

2. SQL> alter table empit add(sal number(4,2) check (sal>0),comm number(2));

Table altered.

3. SQL> describe empit;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(2)
ENAME NOT NULL VARCHAR2(15)
DEPTNO NUMBER(2)
JOB VARCHAR2(10)
HIREDATE DATE
SAL NUMBER(4,2)
COMM NUMBER(2)

SQL> alter table empit modify(sal number(8,2));

Table altered.

SQL> describe empit;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(2)
ENAME NOT NULL VARCHAR2(15)
DEPTNO NUMBER(2)
JOB VARCHAR2(10)
HIREDATE DATE
SAL NUMBER(8,2)
COMM NUMBER(2)

4.





























Answers Experiment No. 2


Select * from tab.
select * from dept;
select dname,loc from dept;

SQL> select distinct job from emp;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

5. SQL> select * from emp where ename='smith';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20



SQL> select ename,job from emp where deptno=20;
ENAME JOB
---------- ---------
smith clerk
jones manager
scott analyst

7. SQL> select * from emp where deptno=30;

EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7499 allen 30 salesman 20-FEB-81 1600 300
7499

7521 ward 30 salesman 22-FEB-81 1250 500


7654 martin 30 salesman 28-SEP-81 1250 1400



EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7698 blake 30 manager 01-MAY-81 2850


7844 turner 30 salesman 08-SEP-81 1500 0


7876 adams 30 clerk 23-MAY-87 1100



EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7900 james 30 clerk 03-DEC-81 950



7 rows selected.
8. SQL> select empno,ename from emp where job='manager';

EMPNO ENAME
---------- ----------
7566 jones
7698 blake
7782 clark


9. SQL> select * from emp where comm>sal;

EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7654 martin 30 salesman 28-SEP-81 1250 1400



10. SQL> select ename,job from emp where sal>2000;

ENAME JOB
---------- ---------
jones manager
blake manager
clark manager
scott analyst
king president
ford analyst

6 rows selected.

SQL> select ename,job,sal from emp where sal>2000;

ENAME JOB SAL
---------- --------- ----------
jones manager 2975
blake manager 2850
clark manager 2450
scott analyst 3000
king president 5000
ford analyst 3000

6 rows selected.


11.
SQL> select ename,sal as month,12*sal as annual from emp;

ENAME MONTH ANNUAL
---------- ---------- ----------
smith 800 9600
allen 1600 19200
ward 1250 15000
jones 2975 35700
martin 1250 15000
blake 2850 34200
clark 2450 29400
scott 3000 36000
king 5000 60000
turner 1500 18000
adams 1100 13200

ENAME MONTH ANNUAL
---------- ---------- ----------
james 950 11400
ford 3000 36000
miller 1300 15600

14 rows selected.

12.
SQL> select ename,sal from emp where deptno=10;

ENAME SAL
---------- ----------
clark 2450
king 5000
ford 3000
miller 1300

13.
SQL> select dname as department,deptno as department_no from dept;

DEPARTMENT DEPARTMENT_NO
-------------- -------------
ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40
sales 3

14.
SQL> select ename,deptno,job from emp where job='manager';

ENAME DEPTNO JOB
---------- ---------- ---------
jones 20 manager
blake 30 manager
clark 10 manager

15.
SQL> select * from emp where job='analyst';

EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7788 scott 20 analyst 19-APR-81 3000

7902 ford 10 analyst 03-DEC-81 3000
Answers Experiment No. 3

1.
SQL> create table clientmast(client_no varchar(4) primary key check(client_no like 'c%')
2 ,name varchar(4) not null,address1 varchar(8),address2 varchar(8),city varchar(8)
3 ,state varchar(8),pin_code number,remark varchar(5),bal_due number(7,2)
4 ,check(name=upper(name)));

Table created.

SQL> describe clientmast;
Name Null? Type
----------------------------------------- -------- ----------------------------
CLIENT_NO NOT NULL VARCHAR2(4)
NAME NOT NULL VARCHAR2(4)
ADDRESS1 VARCHAR2(8)
ADDRESS2 VARCHAR2(8)
CITY VARCHAR2(8)
STATE VARCHAR2(8)
PIN_CODE NUMBER
REMARK VARCHAR2(5)
BAL_DUE NUMBER(7,2)

2.
SQL> insert into clientmast values('c01','ABC1','nagpada1','byculla1','mumbai','maharas',01,'good1',
1000);

1 row created.

SQL>
Wrote file afiedt.buf

1* insert into clientmast values('c02','ABC2','nagpada2','byculla2','mumbai','maharas',02,'good2',
SQL>
1* insert into clientmast values('c02','ABC2','nagpada2','byculla2','mumbai','maharas',02,'good2',

1 row created.

SQL>
Wrote file afiedt.buf

1* insert into clientmast values('c03','ABC3','nagpada3','byculla3','mumbai','maharas',03,'good3',
SQL>
1* insert into clientmast values('c03','ABC3','nagpada3','byculla3','mumbai','maharas',03,'good3',

1 row created.

SQL>
Wrote file afiedt.buf

1* insert into clientmast values('c04','ABC4','nagpada4','byculla4','mumbai','maharas',04,'good4',
SQL>
1* insert into clientmast values('c04','ABC4','nagpada4','byculla4','mumbai','maharas',04,'good4',

1 row created.

SQL>
Wrote file afiedt.buf

1* insert into clientmast values('c05','ABC5','nagpada5','byculla5','mumbai','maharas',05,'good5',
SQL>
1* insert into clientmast values('c05','ABC5','nagpada5','byculla5','mumbai','maharas',05,'good5',

1 row created.

3.
SQL> SELECT * FROM CLIENTMAST;

CLIE NAME ADDRESS1 ADDRESS2 CITY STATE PIN_CODE REMAR BAL_DUE
---- ---- -------- -------- -------- -------- ---------- ----- ----------
c01 ABC1 nagpada1 byculla1 mumbai maharas 1 good1 1000
c02 ABC2 nagpada2 byculla2 mumbai maharas 2 good2 2000
c03 ABC3 nagpada3 byculla3 mumbai maharas 3 good3 3000
c04 ABC4 nagpada4 byculla4 mumbai maharas 4 good4 4000
c05 ABC5 nagpada5 byculla5 mumbai maharas 5 good5 5000

4.
SQL> ALTER TABLE CLIENTMAST ADD(AGE NUMBER(2,2));

Table altered.

SQL> ALTER TABLE CLIENTMAST MODIFY(AGE NUMBER(4,2));

Table altered.

5.
SQL> UPDATE CLIENTMAST SET AGE=35;

5 rows updated.

SQL> UPDATE CLIENTMAST SET AGE=65 WHERE NAME='ABC5';

1 row updated.

SQL> SELECT * FROM CLIENTMAST
2 ;

CLIE NAME ADDRESS1 ADDRESS2 CITY STATE PIN_CODE REMAR BAL_DUE
---- ---- -------- -------- -------- -------- ---------- ----- ----------
AGE
----------
c01 ABC1 nagpada1 byculla1 mumbai maharas 1 good1 1000
35

c02 ABC2 nagpada2 byculla2 mumbai maharas 2 good2 2000
35

c03 ABC3 nagpada3 byculla3 mumbai maharas 3 good3 3000
35


CLIE NAME ADDRESS1 ADDRESS2 CITY STATE PIN_CODE REMAR BAL_DUE
---- ---- -------- -------- -------- -------- ---------- ----- ----------
AGE
----------
c04 ABC4 nagpada4 byculla4 mumbai maharas 4 good4 4000
35

c05 ABC5 nagpada5 byculla5 mumbai maharas 5 good5 5000
65

6.
SQL> DELETE FROM CLIENTMAST WHERE AGE>60;

1 row deleted.

7.
SQL> Create table supplymaster(supplierno,supname,add1,add2,city,state,pincode,baldue) as select CL
IENT_NO,NAME,ADDRESS1,ADDRESS2,CITY,STATE,PIN_CODE,BAL_DUE from clientmast;

Table created.

8.
SQL> insert into supplymaster select CLIENT_NO,NAME,ADDRESS1,ADDRESS2,CITY,STATE,PIN_CODE,BAL_DUE
from clientmast;

4 rows created.

9.
SQL> select * from supplymaster;

SUPP SUPN ADD1 ADD2 CITY STATE PINCODE BALDUE
---- ---- -------- -------- -------- -------- ---------- ----------
c01 ABC1 nagpada1 byculla1 mumbai maharas 1 1000
c02 ABC2 nagpada2 byculla2 mumbai maharas 2 2000
c03 ABC3 nagpada3 byculla3 mumbai maharas 3 3000
c04 ABC4 nagpada4 byculla4 mumbai maharas 4 4000




















Answers Experiment No. 4
1.
SQL> select * from emp where empno>mgr;

EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7788 scott 20 analyst 19-APR-81 3000
7566

7844 turner 30 salesman 08-SEP-81 1500 0
7698

7876 adams 30 clerk 23-MAY-87 1100
7788


EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7900 james 30 clerk 03-DEC-81 950
7698

7902 ford 10 analyst 03-DEC-81 3000
7566

7934 miller 10 clerk 23-JAN-82 1300
7782


6 rows selected.





2.
SQL> select * from emp where job='manager' and deptno!=10;

EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7566 jones 20 manager 02-APR-81 2975
7839

7698 blake 30 manager 01-MAY-81 2850
7839

3.
SQL> select ename from emp where deptno=20 and job!='clerk' and job!='salesman' and job!='analyst';

ENAME
----------
jones

4.
SQL> select ename from emp order by job,sal desc;

ENAME
----------
scott
ford
miller
adams
james
smith
jones
blake
clark
king
allen

ENAME
----------
turner
ward
martin

14 rows selected.

5.
SQL> select ename,job,empno from emp order by job,empno desc;

ENAME JOB EMPNO
---------- --------- ----------
ford analyst 7902
scott analyst 7788
miller clerk 7934
james clerk 7900
adams clerk 7876
smith clerk 7369
clark manager 7782
blake manager 7698
jones manager 7566
king president 7839
turner salesman 7844
martin salesman 7654
ward salesman 7521
allen salesman 7499

14 rows selected.

6.

SQL> select * from emp where sal between 1000 and 2000;

EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7499 allen 30 salesman 20-FEB-81 1600 300
7698

7521 ward 30 salesman 22-FEB-81 1250 500
7698

7654 martin 30 salesman 28-SEP-81 1250 1400
7698


EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7844 turner 30 salesman 08-SEP-81 1500 0
7698

7876 adams 30 clerk 23-MAY-87 1100
7788

7934 miller 10 clerk 23-JAN-82 1300
7782

6 rows selected.
7
SQL> select * from emp where ename not in('smith','blake','allen','scott','clark');

EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7521 ward 30 salesman 22-FEB-81 1250 500
7698

7566 jones 20 manager 02-APR-81 2975
7839

7654 martin 30 salesman 28-SEP-81 1250 1400
7698


EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7839 king 10 president 17-NOV-81 5000


7844 turner 30 salesman 08-SEP-81 1500 0
7698

7876 adams 30 clerk 23-MAY-87 1100
7788


EMPNO ENAME DEPTNO JOB HIREDATE SAL COMM
---------- ---------- ---------- --------- --------- ---------- ----------
MGR
----------
7900 james 30 clerk 03-DEC-81 950
7698

7902 ford 10 analyst 03-DEC-81 3000
7566

7934 miller 10 clerk 23-JAN-82 1300
7782


9 rows selected.

8.
SQL> select ename,sal from emp where sal>=2000;

ENAME SAL
---------- ----------
jones 2975
blake 2850
clark 2450
scott 3000
king 5000
ford 3000

6 rows selected.


9
SQL> select ename,hiredate from emp where hiredate>'01-jan-82' and hiredate<'31-dec-82';

ENAME HIREDATE
---------- ---------
miller 23-JAN-82

10.
SQL> select ename from emp where deptno in(10,20);

ENAME
----------
smith
shafi
jones
clark
scott
king
ford
miller

8 rows selected.

11.
SQL> select ename from emp where ename like 'j%';

ENAME
jones
james

12.
SQL> select ename from emp where ename like 'j_n%';

ENAME
----------
jones

13

SQL> select ename,comm from emp where comm is null;

ENAME COMM
---------- ----------
smith
shafi
jones
blake
clark
scott
king
adams
james
ford
miller

11 rows selected.

14

SQL> select ename,comm from emp where comm is not null;

ENAME COMM
---------- ----------
allen 300
ward 500
martin 1400
turner 0

15
SQL> select ename,deptno,hiredate from emp where hiredate>'01-jan-81' and deptno=10;

ENAME DEPTNO HIREDATE
---------- ---------- ---------
clark 10 09-JUN-81
king 10 17-NOV-81
ford 10 03-DEC-81
miller 10 23-JAN-82

16.
SQL> select ename,deptno from emp where not deptno=10;

ENAME DEPTNO
---------- ----------
smith 20
allen 30
ward 30
jones 20
martin 30
blake 30
scott 20
turner 30
adams 30
james 30

10 rows selected.


Answers Experiment No. 5

1.
SQL> select count(*) from emp;

COUNT(*)
----------
15

2.
SQL> select count(*) from emp where job='clerk' and hiredate>'13-jan-81';

COUNT(*)
----------
3

4
SQL> select 12*sal as yearly_compen from emp where job='salesman';

YEARLY_COMPEN
-------------
19200
15000
15000
18000
5.
SQL> select avg(sal) from emp;

AVG(SAL)
----------
2268.33333

6.
SQL> select avg(12*sal) from emp where job='salesman';

AVG(12*SAL)
-----------
16800
7.
SQL> select min(sal) from emp;

MIN(SAL)
----------
800
8.
SQL> select ename from emp order by ename;

ENAME
----------
adams
allen
blake
clark
ford
james
jones
king
martin
miller
scott

ENAME
----------
shafi
smith
turner
ward

15 rows selected.

9.
SQL> select max(sal) from emp;

MAX(SAL)
----------
5000

10.
SQL> select to_char(hiredate,'dd/mm/yyyy/') from emp;

TO_CHAR(HIR
-----------
17/12/1980/
20/02/1981/

22/02/1981/
02/04/1981/
28/09/1981/
01/05/1981/
09/06/1981/
19/04/1981/
17/11/1981/
08/09/1981/

TO_CHAR(HIR
-----------
23/05/1987/
03/12/1981/
03/12/1981/
23/01/1982/

15 rows selected.

11.
SQL> select add_months(sysdate,4) from dual;

ADD_MONTH
---------
11-AUG-08

12.
SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
30-APR-08

13.
SQL> select last_day(hiredate) from emp;

LAST_DAY(
---------
31-DEC-80
28-FEB-81

28-FEB-81
30-APR-81
30-SEP-81
31-MAY-81
30-JUN-81
30-APR-81
30-NOV-81
30-SEP-81

LAST_DAY(
---------
31-MAY-87
31-DEC-81
31-DEC-81
31-JAN-82

15 rows selected.

Answers Experiment No. 6

1.

SQL> select ascii('A') from dual
2 ;

ASCII('A')
----------
65
2.
SQL> select mod(15,4) from dual;

MOD(15,4)
----------
3
3.
SQL> select empno,ename,greatest(sal,1500) from emp;

EMPNO ENAME GREATEST(SAL,1500)
---------- ---------- ------------------
7369 SMITH 1500
7499 ALLEN 1600
7521 WARD 1500
7566 JONES 2975
7654 MARTIN 1500
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1500

EMPNO ENAME GREATEST(SAL,1500)
---------- ---------- ------------------
7900 JAMES 1500
7902 FORD 3000
7934 MILLER 1500

14 rows selected.


4.
SQL> select empno,ename,least(sal,2000) from emp;

EMPNO ENAME LEAST(SAL,2000)
---------- ---------- ---------------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2000
7654 MARTIN 1250
7698 BLAKE 2000
7782 CLARK 2000
7788 SCOTT 2000
7839 KING 2000
7844 TURNER 1500
7876 ADAMS 1100

EMPNO ENAME LEAST(SAL,2000)
---------- ---------- ---------------
7900 JAMES 950
7902 FORD 2000
7934 MILLER 1300

14 rows selected.

5.
SQL> select round(442.23453) from dual;

ROUND(442.23453)
----------------
442
6.
SQL> select round(442.23453,2) from dual;

ROUND(442.23453,2)
------------------
442.23
7.
SQL> select dnameloc as department from dept;

DEPARTMENT
---------------------------
ACCOUNTINGNEW YORK
RESEARCHDALLAS
SALESCHICAGO
OPERATIONSBOSTON

8.
SQL> select deptno,decode(deptno,10,'TEN',20,'TWENTY',30,'THIRTY') as decode from dept;

DEPTNO DECODE
---------- ------
10 TEN
20 TWENTY
30 THIRTY
40
9.
SQL> select deptno from emp group by deptno having count(job)>2;

DEPTNO
----------
10
20
30

10.
SQL> select deptno from emp where job like 'clerk' group by deptno having count(job)>2;

no rows selected

11.
SQL> select deptno,count(ename),count(job) from emp group by deptno;

DEPTNO COUNT(ENAME) COUNT(JOB)
---------- ------------ ----------
10 3 3
20 5 5
30 6 6



12.
SQL> select job,count(*),12*sal as annual from emp group by job,12*sal having count(*)>1;

JOB COUNT(*) ANNUAL
--------- ---------- ----------
ANALYST 2 36000
SALESMAN 2 15000


13.
SQL> select deptno from emp where job like 'ANALYST' group by deptno having count(job)>1 or
2 count(job)=1;

DEPTNO
----------
20

14.
SQL> select job,avg(sal*12) as annual from emp group by job having avg(sal*12)>
2 all(select avg(sal*12) from emp where job like 'MANAGER');

JOB ANNUAL
--------- ----------
ANALYST 36000
PRESIDENT 60000

15
SQL> select deptno,count(ename),count(job) from emp group by deptno;

DEPTNO COUNT(ENAME) COUNT(JOB)
---------- ------------ ----------
10 3 3
20 5 5
30 6 6

16.

SQL> select instr(ename,'a') from emp;


Answers Experiment No. 7

1.

SQL> select ename,job from emp where job in(select job from emp where ename='ALLEN');

ENAME JOB
---------- ---------
ALLEN SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
WARD SALESMAN

2.

SQL> select ename,job from emp where deptno=(select deptno from dept where loc='CHICAGO');

ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
BLAKE MANAGER
TURNER SALESMAN
JAMES CLERK

6 rows selected.

3.

SQL> select ename,sal from emp where sal>all(select sal from emp where job='MANAGER');

ENAME SAL
---------- ----------
SCOTT 3000
KING 5000
FORD 3000


4.

SQL> select ename,sal from emp where sal<(select min(sal) from emp where job='MANAGER');

ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300

8 rows selected.

5.

SQL> select ename,dname,loc from emp,dept where emp.deptno=dept.deptno;

ENAME DNAME LOC
---------- -------------- -------------
SMITH RESEARCH DALLAS
ALLEN SALES CHICAGO
WARD SALES CHICAGO
JONES RESEARCH DALLAS
MARTIN SALES CHICAGO
BLAKE SALES CHICAGO
CLARK ACCOUNTING NEW YORK
SCOTT RESEARCH DALLAS
KING ACCOUNTING NEW YORK
TURNER SALES CHICAGO
ADAMS RESEARCH DALLAS

ENAME DNAME LOC
---------- -------------- -------------
JAMES SALES CHICAGO

FORD RESEARCH DALLAS
MILLER ACCOUNTING NEW YORK

14 rows selected.

6

SQL> select job from emp where deptno=10 union select job from emp where deptno=20;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT


SQL> select job,deptno from emp where deptno=10 union select job,deptno from emp where deptno=20;

JOB DEPTNO
--------- ----------
ANALYST 20
CLERK 10
CLERK 20
MANAGER 10
MANAGER 20
PRESIDENT 10

6 rows selected.

7

SQL> select job from emp where deptno=10 intersect select job from emp where deptno=20;

JOB
---------
CLERK
MANAGER

8

SQL> select job from emp where deptno=10 minus select job from emp where deptno=20;

JOB
---------
PRESIDENT



Answers Experiment No. 8


1.
SQL> select ename ' is ' job 'and working from' hiredate as sentence from emp;

SENTENCE
------------------------------------------------
SMITH is CLERKand working from17-DEC-80
ALLEN is SALESMANand working from20-FEB-81
WARD is SALESMANand working from22-FEB-81
JONES is MANAGERand working from02-APR-81
MARTIN is SALESMANand working from28-SEP-81
BLAKE is MANAGERand working from01-MAY-81
CLARK is MANAGERand working from09-JUN-81
SCOTT is ANALYSTand working from19-APR-87
KING is PRESIDENTand working from17-NOV-81
TURNER is SALESMANand working from08-SEP-81
ADAMS is CLERKand working from23-MAY-87
JAMES is CLERKand working from03-DEC-81
FORD is ANALYSTand working from03-DEC-81
MILLER is CLERKand working from23-JAN-82

14 rows selected.

2.
SQL> select ename,sal from emp where sal> (select avg(sal) from emp);

ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000

6 rows selected.

3

SQL> select ename,job from emp where job=(select job from emp where ename='SCOTT');

ENAME JOB
---------- ---------
SCOTT ANALYST
FORD ANALYST

4.

SQL> select * from dept where deptno in(select deptno from emp);

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO

5.


SQL> > select ename,sal from emp where sal=any(select sal from emp where deptno=30);
SP2-0734: unknown command beginning "> select e..." - rest of line ignored.
SQL> select ename,sal from emp where sal=any(select sal from emp where deptno=30);

ENAME SAL
---------- ----------
JAMES 950
WARD 1250
MARTIN 1250
TURNER 1500
ALLEN 1600
BLAKE 2850

6 rows selected.

6.

SQL> select ename,sal from emp where sal>all(select sal from emp where deptno=30);

ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000

7.

SQL> select * from dept where deptno in(select deptno from emp);

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO



Answers Experiment No. 9
1.

SQL> create index jobindex on emp(job);

Index created.


3.

SQL> create view empview as select emp.deptno,dname,sal,job from emp,dept where emp.deptno=dept.dept
no;

View created.

SQL> select * from empview;

DEPTNO DNAME SAL JOB
---------- -------------- ---------- ---------
20 RESEARCH 800 CLERK
30 SALES 1600 SALESMAN
30 SALES 1250 SALESMAN
20 RESEARCH 2975 MANAGER
30 SALES 1250 SALESMAN
30 SALES 2850 MANAGER
10 ACCOUNTING 2450 MANAGER
20 RESEARCH 3000 ANALYST
10 ACCOUNTING 5000 PRESIDENT
30 SALES 1500 SALESMAN
20 RESEARCH 1100 CLERK

DEPTNO DNAME SAL JOB
---------- -------------- ---------- ---------
30 SALES 950 CLERK
20 RESEARCH 3000 ANALYST
10 ACCOUNTING 1300 CLERK

14 rows selected.

4.

SQL> drop view empview;

View dropped.

5.

SQL> create view empview1 as select ename,job,sal from emp where deptno=10;

View created.

SQL> select * from empview1;

ENAME JOB SAL
---------- --------- ----------
CLARK MANAGER 2450
KING PRESIDENT 5000
MILLER CLERK 1300

6.

SQL> drop view empview1;

View dropped.

7.

SQL> create view empview2 as select deptno,sum(sal) as sum_sal from emp group by deptno;

View created.

SQL> select * from empview2;

DEPTNO SUM_SAL
---------- ----------
10 8750
20 10875
30 9400

8.

SQL> drop view empview2;

View dropped.

9.

SQL> create view empview4 as select ename,job,sal,dname from emp,dept where emp.deptno=dept.deptno;

View created.

SQL> select * from empview4;

ENAME JOB SAL DNAME
---------- --------- ---------- --------------
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
WARD SALESMAN 1250 SALES
JONES MANAGER 2975 RESEARCH
MARTIN SALESMAN 1250 SALES
BLAKE MANAGER 2850 SALES
CLARK MANAGER 2450 ACCOUNTING
SCOTT ANALYST 3000 RESEARCH
KING PRESIDENT 5000 ACCOUNTING
TURNER SALESMAN 1500 SALES
ADAMS CLERK 1100 RESEARCH

ENAME JOB SAL DNAME
---------- --------- ---------- --------------
JAMES CLERK 950 SALES
FORD ANALYST 3000 RESEARCH
MILLER CLERK 1300 ACCOUNTING

14 rows selected.

10.
SQL> drop view empview4;

View dropped.
Answers Experiment No. 10

1.

SQL> select deptno,avg(sal),min(sal),max(sal) from emp group by deptno;

DEPTNO AVG(SAL) MIN(SAL) MAX(SAL)
---------- ---------- ---------- ----------
10 2916.66667 1300 5000
20 2175 800 3000
30 1566.66667 950 2850

2.

SQL> select deptno,avg(sal),min(sal),max(sal) from emp group by deptno having avg(sal)>2000;

DEPTNO AVG(SAL) MIN(SAL) MAX(SAL)
---------- ---------- ---------- ----------
10 2916.66667 1300 5000
20 2175 800 3000

3.

SQL> select job,min(sal) from emp group by job;

JOB MIN(SAL)
--------- ----------
ANALYST 3000
CLERK 800
MANAGER 2450
PRESIDENT 5000
SALESMAN 1250

4.

SQL> select deptno,job,min(sal) from emp group by deptno,job;

DEPTNO JOB MIN(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 CLERK 800
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1250

9 rows selected.

5.

SQL> select deptno,job,min(sal) from emp group by deptno,job having min(sal)>2500;

DEPTNO JOB MIN(SAL)
---------- --------- ----------
10 PRESIDENT 5000
20 ANALYST 3000
20 MANAGER 2975
30 MANAGER 2850

6.

SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno
2 ;

DEPTNO MIN(SAL)
---------- ----------
10 2450
20 2975
30 2850

7.

SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno having min(sal)>2500;

DEPTNO MIN(SAL)
---------- ----------
20 2975
30 2850

8.

SQL> select ename,deptno,job from emp order by ename;

ENAME DEPTNO JOB
---------- ---------- ---------
ADAMS 20 CLERK
ALLEN 30 SALESMAN
BLAKE 30 MANAGER
CLARK 10 MANAGER
FORD 20 ANALYST
JAMES 30 CLERK
JONES 20 MANAGER
KING 10 PRESIDENT
MARTIN 30 SALESMAN
MILLER 10 CLERK
SCOTT 20 ANALYST

ENAME DEPTNO JOB
---------- ---------- ---------
SMITH 20 CLERK
TURNER 30 SALESMAN
WARD 30 SALESMAN

14 rows selected.

10.

SQL> select ename,deptno,job from emp where deptno in(10,20) order by deptno,sal desc;

ENAME DEPTNO JOB
---------- ---------- ---------
KING 10 PRESIDENT
CLARK 10 MANAGER
MILLER 10 CLERK
SCOTT 20 ANALYST
FORD 20 ANALYST
JONES 20 MANAGER
ADAMS 20 CLERK
SMITH 20 CLERK

8 rows selected.

SQL> select ename,deptno,job,sal from emp where deptno in(10,20) order by deptno,sal desc;

ENAME DEPTNO JOB SAL
---------- ---------- --------- ----------
KING 10 PRESIDENT 5000
CLARK 10 MANAGER 2450
MILLER 10 CLERK 1300
SCOTT 20 ANALYST 3000
FORD 20 ANALYST 3000
JONES 20 MANAGER 2975
ADAMS 20 CLERK 1100
SMITH 20 CLERK 800

8 rows selected.

11.

SQL> select ename,sal*12 as annual from emp order by annual;

ENAME ANNUAL
---------- ----------
SMITH 9600
JAMES 11400
ADAMS 13200
WARD 15000
MARTIN 15000
MILLER 15600
TURNER 18000
ALLEN 19200
CLARK 29400
BLAKE 34200
JONES 35700

ENAME ANNUAL
---------- ----------
SCOTT 36000
FORD 36000
KING 60000

14 rows selected.



Answers Experiment No. 11

1.

SQL> create table employee(empid number primary key,name varchar(15));

Table created.


SQL> reate table orders(prod_id number,product varchar(10),empid number,foreign key(empid) reference
s employee(empid));
SP2-0734: unknown command beginning "reate tabl..." - rest of line ignored.
SQL> create table orders(prod_id number,product varchar(10),empid number,foreign key(empid) referenc
es employee(empid));

Table created.

SQL> inser int employee values(1,'hansen');
SP2-0734: unknown command beginning "inser int ..." - rest of line ignored.
SQL> insert into employee values(1,'hansen');

1 row created.

SQL> insert into employee values(2,'syendson');

1 row created.

SQL> insert into employee values(3,'stephen');

1 row created.

SQL> insert into employee values(4,'pettersen');

1 row created.

SQL> insert into orders values(234,'printer',1);

1 row created.

SQL> insert into orders values(657,'table',3);

1 row created.

SQL> insert into orders values(865,'chair',3);

1 row created.

SQL> select * from employee;

EMPID NAME
---------- ---------------
1 hansen
2 syendson
3 stephen
4 pettersen


SQL> select * from orders;

PROD_ID PRODUCT EMPID
---------- ---------- ----------
234 printer 1
657 table 3
865 chair 3

2.

SQL> select name,product from employee e,orders o where e.empid=o.empid;

NAME PRODUCT
--------------- ----------
hansen printer
stephen table
stephen chair

3.

SQL> select name,product from employee e,orders o where e.empid=o.empid and product='printer';

NAME PRODUCT
--------------- ----------
hansen printer

4.

SQL> select name,product from employee left outer join orders on employee.empid=orders.empid;

NAME PRODUCT
--------------- ----------
hansen printer
stephen table
stephen chair
pettersen
syendson

5.

SQL> select name,product from employee right outer join orders on employee.empid=orders.empid;

NAME PRODUCT
--------------- ----------
hansen printer
stephen chair
stephen table

Lecture PPTs of MIS , Module 4 to Module 6

 Module 4 Social Computing (SC): Web 3.0 , SC in business-shopping, Marketing, Operational and Analytic CRM, E-business and E-commerce B2B B...