All the learnig objectives.

Database of student enrollment in courses and books adopted for each course.

2011-12-12T01:11:00.001+05:30

Tables

STUDENT (Regno: String, Name: String, Major: String, BDate: Date)

COURSE (Courseno: Int, Cname: String, Dept: String)

ENROLL (Regno: String, Courseno: Int, Sem: Int, Marks: Int)

BOOK_ADOPTION (Courseno: Int, Sem: Int, BookISBN: Int)

TEXT (BookISBN: Int, Title: String, Publisher: String, Author: String)

1. Create above tables with properly specifying primary keys and foreign keys.

SQL> CREATE TABLE STUDENT(

2 REGNO VARCHAR(10),

3 NAME VARCHAR(10),

4 MAJOR VARCHAR(10),

5 BDATE DATE,

6 PRIMARY KEY(REGNO));

Table created.

SQL> CREATE TABLE COURSE(

2 COURSENO INT,

3 CNAME VARCHAR(10),

4 DEPT VARCHAR(10),

5 PRIMARY KEY(COURSENO));

Table created.

SQL> CREATE TABLE TEXT(

2 BOOKISBN INT,

3 TITLE VARCHAR(20),

4 PUBLISHER VARCHAR(20),

5 AUTHOR VARCHAR(20),

6 PRIMARY KEY(BOOKISBN));

Table created.

SQL> CREATE TABLE ENROLL(

2 REGNO VARCHAR(10),

3 COURSENO INT,

4 SEM INT,

5 MARKS INT,

6 PRIMARY KEY(REGNO,COURSENO,SEM),

7 FOREIGN KEY(REGNO) REFERENCES STUDENT(REGNO) ON DELETE SET NULL,

8 FOREIGN KEY(COURSENO) REFERENCES COURSE(COURSENO) ON DELETE SET NULL);

Table created.

SQL> CREATE TABLE BOOKADOPTION(

2 COURSENO INT,

3 SEM INT,

4 BOOKISBN INT,

5 PRIMARY KEY(COURSENO,SEM),

6 FOREIGN KEY(COURSENO) REFERENCES COURSE(COURSENO) ON DELETE SET NULL,

7 FOREIGN KEY(BOOKISBN) REFERENCES TEXT(BOOKISBN) ON DELETE SET NULL);

Table created.

2. Enter at least 7-8 tuples for each relation.

SQL> INSERT INTO STUDENT VALUES('&REGNO','&NAME','&MAJOR','&BDATE');

Enter value for regno: 2BV10MCA01

Enter value for name: JAMES

Enter value for major: COMPUTERS

Enter value for bdate: 01-JAN-89

old 1: INSERT INTO STUDENT VALUES('&REGNO','&NAME','&MAJOR','&BDATE')

new 1: INSERT INTO STUDENT VALUES('2BV10MCA01','JAMES','COMPUTERS','01-JAN-89')

1 row created.

SQL> INSERT INTO TEXT VALUES(&BOOKISBN,'&TITLE','&PUBLISHER','&AUTHOR');

Enter value for bookisbn: 101

Enter value for title: DATABASE SYSTEMS

Enter value for publisher: PEARSON

Enter value for author: NAVATHE

old 1: INSERT INTO TEXT VALUES(&BOOKISBN,'&TITLE','&PUBLISHER','&AUTHOR')

new 1: INSERT INTO TEXT VALUES(101,'DATABASE SYSTEMS','PEARSON','NAVATHE')

1 row created.

SQL> INSERT INTO ENROLL VALUES('&REGNO',&COURSENO,&SEM,&MARKS);

Enter value for regno: 2BV10MCA01

Enter value for courseno: 1

Enter value for sem: 3

Enter value for marks: 80

old 1: INSERT INTO ENROLL VALUES('&REGNO',&COURSENO,&SEM,&MARKS)

new 1: INSERT INTO ENROLL VALUES('2BV10MCA01',1,3,80)

1 row created.

SQL> INSERT INTO BOOKADOPTION VALUES(&COURSENO,&SEM,&BOOKISBN);

Enter value for courseno: 1

Enter value for sem: 3

Enter value for bookisbn: 101

old 1: INSERT INTO BOOKADOPTION VALUES(&COURSENO,&SEM,&BOOKISBN)

new 1: INSERT INTO BOOKADOPTION VALUES(1,3,101)

1 row created.

3. Displaying the table contents

SQL> SELECT * FROM STUDENT;

REGNO NAME MAJOR BDATE

2BV10MCA01 JAMES COMPUTERS 01-JAN-89

2BV10MCA02 JOHN NETWORKS 02-JAN-90

2BV10MCA03 ARNOLD SYSTEMS 03-FEB-89

2BV10MCA04 KIM E&C 04-MAR-90

2BV10MCA05 FRED E&E 05-APR-89

2BV10MCA06 HARIS MECH 05-MAY-89

6 rows selected.

SQL> SELECT * FROM COURSE;

COURSENO CNAME DEPT

1 DBMS COMPUTERS

2 CN NETWORKS

3 SIMULATION SYSTEMS

4 OS COMPUTERS

5 DSP E&C

6 CIRCUITS E&E

7 SOM MECH

8 OOP COMPUTERS

9 DATACOM NETWORKS

10 MACHINES MECH

10 rows selected.

SQL> SELECT * FROM TEXT;

BOOKISBN TITLE PUBLISHER AUTHOR

101 DATABASE SYSTEMS PEARSON NAVATHE

102 OPERATING SYSTEM TATA SIBERSCHATZ

103 SIMULATIONS TATA JERRY BANKS

104 DIP PEARSON JERRY

105 SOM APRESS DAVID

106 DESIGNING TATA GEORGE

107 DATACOM TATA ELMASRI

108 CIRCUITS PEARSON TOM

8 rows selected.

SQL> SELECT * FROM ENROLL;

REGNO COURSENO SEM MARKS

2BV10MCA01 1 3 80

2BV10MCA02 4 5 70

2BV10MCA03 3 5 89

2BV10MCA04 5 7 90

2BV10MCA05 6 5 76

2BV10MCA06 7 7 89

2BV10MCA01 4 5 79

2BV10MCA04 5 5 80

8 rows selected.

SQL> SELECT * FROM BOOKADOPTION;

COURSENO SEM BOOKISBN

1 3 101

3 3 103

4 3 102

5 5 104

6 7 108

7 5 105

9 3 107

10 3 106

8 rows selected.

4. Demonstrate how you add a new textbook to the database and make this book be adopted by some department.

SQL> INSERT INTO TEXT(BOOKISBN,TITLE,PUBLISHER,AUTHOR) VALUES(109,'COMPUTER NETWORKS','TATA','FOROUZAN');

1 row created.

SQL> INSERT INTO BOOKADOPTION(COURSENO,SEM,BOOKISBN) VALUES(2,3,109);

1 row created.

SQL> SELECT * FROM TEXT;

BOOKISBN TITLE PUBLISHER AUTHOR

101 DATABASE SYSTEMS PEARSON NAVATHE

102 OPERATING SYSTEMS TATA SIBERSCHATZ

103 SIMULATIONS TATA JERRY BANKS

104 DIP PEARSON JERRY

105 SOM APRESS DAVID

106 DESIGNING TATA GEORGE

107 DATACOM TATA ELMASRI

108 CIRCUITS PEARSON TOM

109 COMPUTER NETWORKS TATA FOROUZAN

9 rows selected.

SQL> SELECT * FROM BOOKADOPTION;

COURSENO SEM BOOKISBN

1 3 101

3 3 103

4 3 102

5 5 104

6 7 108

7 5 105

9 3 107

10 3 106

2 3 109

9 rows selected.

5. Produce a list of textbooks (include course number, book ISBN, book title) in alphabetical order for courses offered by MCA department that uses more than two books.

SQL> SELECT C.COURSENO COURSE_NO,T.BOOKISBN

2 BOOKISBN,T.TITLE BOOK_TITLE

3 FROM BOOKADOPTION B,TEXT T,COURSE C

4 WHERE B.BOOKISBN=T.BOOKISBN AND

5 B.COURSENO=C.COURSENO AND C.DEPT='COMPUTERS'

6 GROUP BY C.COURSENO,T.BOOKISBN,T.TITLE

7 HAVING COUNT(B.COURSENO)>2;

no rows selected.

6. List any department that has all its adopted books published by a specific publisher.

SQL> SELECT DISTINCT DEPT

2 FROM COURSE

3 WHERE COURSENO IN(SELECT COURSENO

4 FROM BOOKADOPTION

5 WHERE BOOKISBN IN(SELECT BOOKISBN

6 FROM TEXT

7 WHERE PUBLISHER='PEARSON')

8 );

DEPT

COMPUTERS

E&C

E&E

Company DataBase 6.42 solution of 5th edition Navathe TextBook

2011-12-11T12:10:00.001+05:30

Consider the following database for the company.

EMPLOYEE ( Ssn : Int, Fname : String, Lname : String, Address : String, Gender : Char, Salary : Int, Super_SSN: Int, Dno: Int)

DEPARTMENT (Dnumber: Int, Dname: String, Mgr_SSN: Int, Mgr_start_date: Date)

PROJECT (Pnumber: Int, Pname: String, Plocation: String, Dno: Int)

DEPENDENT (Essn: Int, Depedent_name: String, Gender: Char, BDate: Date, Relationship: String)

WORKS_ON (Essn: Int, Pnumber: Int, Hours: Real)

1. Create above tables with properly specifying primary keys and foreign keys.

SQL> CREATE TABLE EMPLOYEE(

2 SSN INT,

3 FNAME VARCHAR(10),

4 LNAME VARCHAR(10),

5 ADDRESS VARCHAR(20),

6 GENDER CHAR,

7 SALARY FLOAT,

8 PRIMARY KEY(SSN));

Table created.

SQL> CREATE TABLE DEPARTMENT(

2 DNUMBER INT,

3 DNAME VARCHAR(20),

4 MGR_SSN INT,

5 MGR_START_DATE DATE,

6 PRIMARY KEY(DNUMBER),

7 FOREIGN KEY(MGR_SSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL);

Table created.

SQL> ALTER TABLE EMPLOYEE

2 ADD SUPER_SSN INT;

Table altered.

SQL> ALTER TABLE EMPLOYEE

2 ADD CONSTRAINT EFK1

3 FOREIGN KEY(SUPER_SSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL;

Table altered.

SQL> ALTER TABLE EMPLOYEE

2 ADD DNO INT;

Table altered.

SQL> ALTER TABLE EMPLOYEE

2 ADD CONSTRAINT EFK2

3 FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET NULL;

Table altered.

SQL> CREATE TABLE PROJECT(

2 PNUMBER INT,

3 PNAME VARCHAR(20),

4 PLOCATION VARCHAR(10),

5 DNO INT,

6 PRIMARY KEY(PNUMBER),

7 FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET NULL);

Table created.

SQL> CREATE TABLE DEPENDENT(

2 ESSN INT,

3 DEPENDENT_NAME VARCHAR(10),

4 GENDER CHAR,

5 BDATE DATE,

6 RELATIONSHIP VARCHAR(10),

7 PRIMARY KEY(ESSN,DEPENDENT_NAME),

8 FOREIGN KEY(ESSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL);

Table created.

SQL> CREATE TABLE WORKS_ON(

2 ESSN INT,

3 PNUMBER INT,

4 HOUR REAL,

5 PRIMARY KEY(ESSN,PNUMBER),

6 FOREIGN KEY(ESSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL,

7 FOREIGN KEY(PNUMBER) REFERENCES PROJECT(PNUMBER) ON DELETE SET NULL);

Table created.

2. Enter at least 7-8 tuples for each relation.

SQL> INSERT INTO EMPLOYEE VALUES(&SSN,'&FNAME','&LNAME','&ADDRESS','&GENDER',&SALARY,NULL,NULL);

Enter value for ssn: 333445555

Enter value for fname: FRANKLIN

Enter value for lname: WONG

Enter value for address: HOUSTON

Enter value for gender: M

Enter value for salary: 40000

old 1: INSERT INTO EMPLOYEE VALUES(&SSN,'&FNAME','&LNAME','&ADDRESS','&GENDER',&SALARY,NULL,NULL)

new 1: INSERT INTO EMPLOYEE VALUES(333445555,'FRANKLIN','WONG','HOUSTON','M',40000,NULL,NULL)

1 row created.

SQL> INSERT INTO DEPARTMENT VALUES(&DNUMBER,'&DNAME',&MGR_SSN,'&MGR_START_DATE');

Enter value for dnumber: 5

Enter value for dname: RESEARCH

Enter value for mgr_ssn: 333445555

Enter value for mgr_start_date: 22-AUG-1988

old 1: INSERT INTO DEPARTMENT VALUES(&DNUMBER,'&DNAME',&MGR_SSN,'&MGR_START_DATE')

new 1: INSERT INTO DEPARTMENT VALUES(5,'RESEARCH',333445555,'22-AUG-1988')

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(&SSN,'&FNAME','&LNAME','&ADDRESS','&GENDER',&SALARY,&SUPER_SSN,&DNO);

Enter value for ssn: 123456789

Enter value for fname: JOHN

Enter value for lname: SMITH

Enter value for address: HOUSTON

Enter value for gender: M

Enter value for salary: 30000

Enter value for super_ssn: 333445555

Enter value for dno: 5

old 1: INSERT INTO EMPLOYEE VALUES(&SSN,'&FNAME','&LNAME','&ADDRESS','&GENDER',&SALARY,&SUPER_SSN,&DNO)

new 1: INSERT INTO EMPLOYEE VALUES(123456789,'JOHN','SMITH','HOUSTON','M',30000,333445555,5)

1 row created.

SQL> UPDATE EMPLOYEE

2 SET SUPER_SSN=888665555, DNO=5

3 WHERE SSN=333445555;

1 row updated.

SQL> INSERT INTO PROJECT VALUES(&PNUMBER,'&PNAME','&PLOCATION',&DNO);

Enter value for pnumber: 1

Enter value for pname: PRODUCTX

Enter value for plocation: BELLAIRE

Enter value for dno: 5

old 1: INSERT INTO PROJECT VALUES(&PNUMBER,'&PNAME','&PLOCATION',&DNO)

new 1: INSERT INTO PROJECT VALUES(1,'PRODUCTX','BELLAIRE',5)

1 row created.

SQL> INSERT INTO DEPENDENT VALUES(&ESSN,'&DEPENDENT_NAME','&GENDER','&BDATE','&RELATIONSHIP');

Enter value for essn: 333445555

Enter value for dependent_name: ALICE

Enter value for gender: F

Enter value for bdate: 04-MAY-1986

Enter value for relationship: DAUGHTER

old 1: INSERT INTO DEPENDENT VALUES(&ESSN,'&DEPENDENT_NAME','&GENDER','&BDATE','&RELATIONSHIP')

new 1: INSERT INTO DEPENDENT VALUES(333445555,'ALICE','F','04-MAY-1986','DAUGHTER')

1 row created.

SQL> INSERT INTO WORKS_ON VALUES(&ESSN,&PNUMBER,&HOUR);

Enter value for essn: 123456789

Enter value for pnumber: 1

Enter value for hour: 32.5

old 1: INSERT INTO WORKS_ON VALUES(&ESSN,&PNUMBER,&HOUR)

new 1: INSERT INTO WORKS_ON VALUES(123456789,1,32.5)

1 row created.

3. Displaying the table contents.

SQL> SELECT * FROM EMPLOYEE;

SSN FNAME LNAME ADDRESS GENDER SALARY SUPER_SSN DNO

333445555 FRANKLIN WONG HOUSTON M 40000

987654321 JENNIFER WALLACE BELLAIRE F 43000

888665555 JAMES BORG HOUSTON M 55000

3 rows selected.

SQL> SELECT * FROM DEPARTMENT;

DNUMBER DNAME MGR_SSN MGR_START

5 RESEARCH 333445555 22-MAY-88

4 ADMINISTRATION 987654321 01-JAN-95

1 HEADQUARTERS 888665555 19-JUN-81

3 rows selected.

SQL> SELECT * FROM EMPLOYEE;

SSN FNAME LNAME ADDRESS GENDER SALARY SUPER_SSN DNO

--------- ---------- ---------- ---------- ------ --------- --------- ---------

123456789 John Smith Houston Male 30000 333445555 5

333445555 Franklin Wong Houston Male 40000 888665555 5

999887777 Alicia Zelaya Spring Female 25000 987654321 4

987654321 Jennifer Wallacia Bellaire Female 43000 888665555 4

666884444 Ramesh Narayan Humble Male 38000 333445555 5

453453453 Joyce English Houston Female 25000 333445555 5

987987987 Ahmad Jabbar Houston Male 25000 987654321 4

888665555 James Borg Houston male 55000 1

8 rows selected.

SQL> SELECT * FROM PROJECT;

PNUMBER PNAME PLOCATION DNO

1 PRODUCTX BELLAIRE 5

2 PRODUCTY SUGARLAND 5

3 PRODUCTZ HOUSTON 5

10 COMPUTERIZATION STAFFORD 4

20 REORGANIZATION HOUSTON 1

30 NEWBENEFITS STAFFORD 4

6 rows selected.

SQL> SELECT * FROM DEPENDENT;

ESSN DEPENDENT_ NAME GENDER BDATE RELATIONS

333445555 ALICE F 05-APR-86 DAUGHTER

333445555 THEODORE M 25-OCT-83 SON

333445555 JOY F 03-MAY-58 SPOUSE

987654321 ABNER M 28-FEB-42 SPOUSE

123456789 MICHEAL M 04-JAN-88 SON

123456789 ALICE F 30-DEC-88 DAUGHTER

123456789 ELIZABETH F 05-MAY-67 SPOUSE

7 rows selected.

SQL> SELECT * FROM WORKS_ON;

ESSN PNUMBER HOUR

123456789 1 32.5

123456789 2 7.5

666884444 3 40

453453453 1 20

453453453 2 20

333445555 2 10

333445555 3 10

333445555 10 10

333445555 20 10

999887777 30 30

999887777 10 10

987987987 10 35

987987987 30 5

987654321 30 20

987654321 20 15

888665555 20

16 rows selected.

4. Update salaries of all employees who work for Research department by 10%.

SQL> UPDATE EMPLOYEE

2 SET SALARY=SALARY+(SALARY*0.1)

3 WHERE DNO

4 IN (SELECT DNUMBER

5 FROM DEPARTMENT

6 WHERE DNAME='RESEARCH');

4 rows updated.

SQL> SELECT * FROM EMPLOYEE;

SSN FNAME LNAME ADDRESS GENDER SALARY SUPER_SSN DNO

--------- ---------- ---------- ---------- ------ --------- --------- ---------

123456789 John Smith Houston Male 33000 333445555 5

333445555 Franklin Wong Houston Male 44000 888665555 5

999887777 Alicia laya Spring Female 250000 987654321 4

987654321 Jennifer Wallacia Bellaire Female 43000 888665555 4

666884444 Ramesh Narayan Humble Male 41800 333445555 5

453453453 Joyce English Houston Female 27500 333445555 5

987987987 Ahmad Jabbar Houston Male 25000 987654321 4

888665555 James Borg Houston male 55000 1

8 rows selected.

5. Retrieve the names of each employee who work on all the projects controlled by department number 5.

SQL> SELECT FNAME,LNAME

2 FROM EMPLOYEE

3 WHERE SSN

4 IN (SELECT W.ESSN

5 FROM WORKS_ON W

6 WHERE W.PNUMBER

7 IN (SELECT P1.PNUMBER

8 FROM PROJECT P1

9 WHERE P1.DNO=5)

10 GROUP BY W.ESSN

11 HAVING COUNT(W.PNUMBER)=(SELECT COUNT(P2.PNUMBER)

12 FROM PROJECT P2

13 WHERE P2.DNO=5

14 GROUP BY P2.DNO)

15 );

no rows selected.

6. For each department that has three employees retrieve the department number and the number of employees making salary more than or equals to 30000.

SQL> SELECT COUNT(*) AS NO_OF_EMPLOYEES,DNO AS DEPT_NUMBER

2 FROM EMPLOYEE

3 WHERE SALARY>=30000 AND DNO

4 IN (SELECT DNO

5 FROM EMPLOYEE

6 GROUP BY DNO

7 HAVING COUNT(SSN)=3)

8 GROUP BY DNO;

NO_OF_EMPLOYEES DEPT_NUMBER

1 4

7. Retrieve the employees that do not have dependents

SQL> SELECT SSN,FNAME,LNAME

2 FROM EMPLOYEE

3 WHERE SSN

4 IN((SELECT SSN

5 FROM EMPLOYEE)

6 MINUS

7 (SELECT ESSN

8 FROM DEPENDENT));

SSN FNAME LNAME

453453453 JOYCE ENGLISH

666884444 RAMESH NARAYAN

888665555 JAMES BORG

987987987 AHMAD JABBAR

999887777 ALICIA ZELAYA

8. For each department retrieve the department number, minimum salary and the details of the employees who earn minimum salary in the department.

SQL> SELECT SSN,FNAME,SALARY,DNO

2 FROM EMPLOYEE

3 WHERE SALARY

4 IN (SELECT MIN(SALARY)

5 FROM EMPLOYEE

6 GROUP BY DNO);

SSN FNAME SALARY DNO

999887777 ALICIA 25000 4

987987987 AHMAD 25000 4

453453453 JOYCE 27500 5

888665555 JAMES 55000 1

Data Base Management System Querry 2

2011-12-07T23:33:00.000+05:30

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

CUSTOMER (custno: Int, cname: String, city: String)

ORDERS (orderno: Int, odate: Date, custno: Int, ordamt: Int)

ITEM (itemno: Int, unitprice: Int)

OREDERITEM (orderno: Int, itemno: Int, qty: Int)

WAREHOUSE (warehouseno: Int, city: String)

SHIPMENT (orderno: Int, warehouseno: Int, shipdate: Date)

1. Create above tables with properly specifying primary keys and foreign keys.

SQL> CREATE TABLE CUSTOMER(

2 CUSTNO INT,

3 CNAME VARCHAR(10),

4 CITY VARCHAR(10),

5 PRIMARY KEY(CUSTNO));

Table created.

SQL> CREATE TABLE ORDERS(

2 ORDERNO INT,

3 ODATE DATE,

4 CUSTNO INT,

5 ORDAMT INT,

6 PRIMARY KEY(ORDERNO),

7 FOREIGN KEY(CUSTNO) REFERENCES CUSTOMER(CUSTNO) ON DELETE SET NULL);

Table created.

SQL> CREATE TABLE ITEM(

2 ITEMNO INT,

3 UNITPRICE INT,

4 PRIMARY KEY(ITEMNO));

Table created.

SQL> CREATE TABLE ORDERITEM(

2 ORDERNO INT,

3 ITEMNO INT,

4 QTY INT,

5 FOREIGN KEY(ORDERNO) REFERENCES ORDERS(ORDERNO) ON DELETE SET NULL,

6 FOREIGN KEY(ITEMNO) REFERENCES ITEM(ITEMNO) ON DELETE SET NULL);

Table created.

SQL> CREATE TABLE WAREHOUSE(

2 WAREHOUSENO INT,

3 CITY VARCHAR(10),

4 PRIMARY KEY(WAREHOUSENO));

Table created.

SQL> CREATE TABLE SHIPMENT(

2 ORDERNO INT,

3 WAREHOUSENO INT,

4 SHIPDATE DATE,

5 PRIMARY KEY(ORDERNO,WAREHOUSENO),

6 FOREIGN KEY(ORDERNO) REFERENCES ORDERS(ORDERNO) ON DELETE SET NULL,

7 FOREIGN KEY(WAREHOUSENO) REFERENCES WAREHOUSE(WAREHOUSENO) ON DELETE SET

8 NULL);

Table created.

2. Enter at least 7-8 tuples for each relation.

SQL> INSERT INTO CUSTOMER VALUES(&CUSTNO,'&CNAME','&CITY');

Enter value for custno: 1

Enter value for cname: JOHN

Enter value for city: HUBLI

old 1: INSERT INTO CUSTOMER VALUES(&CUSTNO,'&CNAME','&CITY')

new 1: INSERT INTO CUSTOMER VALUES(1,'JOHN','HUBLI')

1 row created.

SQL> INSERT INTO ORDERS VALUES(&ORDERNO,'&ODATE',&CUSTNO,&ORDAMT);

Enter value for orderno: 1

Enter value for odate: 01-JAN-11

Enter value for custno: 1

Enter value for ordamt: 1000

old 1: INSERT INTO ORDERS VALUES(&ORDERNO,'&ODATE',&CUSTNO,&ORDAMT)

new 1: INSERT INTO ORDERS VALUES(1,'01-JAN-11',1,1000)

1 row created.

SQL> INSERT INTO ITEM VALUES(&ITEMNO,&UNITPRICE);

Enter value for itemno: 1

Enter value for unitprice: 50

old 1: INSERT INTO ITEM VALUES(&ITEMNO,&UNITPRICE)

new 1: INSERT INTO ITEM VALUES(1,50)

1 row created.

SQL> INSERT INTO ORDERITEM VALUES(&ORDERNO,&ITEMNO,&QTY);

Enter value for orderno: 1

Enter value for itemno: 1

Enter value for qty: 10

old 1: INSERT INTO ORDERITEM VALUES(&ORDERNO,&ITEMNO,&QTY)

new 1: INSERT INTO ORDERITEM VALUES(1,1,10)

1 row created.

SQL> INSERT INTO WAREHOUSE VALUES(&WAREHOUSENO,'&CITY');

Enter value for warehouseno: 1

Enter value for city: MUMBAI

old 1: INSERT INTO WAREHOUSE VALUES(&WAREHOUSENO,'&CITY')

new 1: INSERT INTO WAREHOUSE VALUES(1,'MUMBAI')

1 row created.

SQL> INSERT INTO SHIPMENT VALUES(&ORDERNO,&WAREHOUSE,'&SHIPDATE');

Enter value for orderno: 3

Enter value for warehouse: 1

Enter value for shipdate: 20-JUN-10

old 1: INSERT INTO SHIPMENT VALUES(&ORDERNO,&WAREHOUSE,'&SHIPDATE')

new 1: INSERT INTO SHIPMENT VALUES(3,1,'20-JUN-10')

1 row created.

3. Displaying the table contents.

SQL> SELECT * FROM CUSTOMER;

CUSTNO CNAME CITY

1 JOHN HUBLI

2 JACK HUBLI

3 DAVID DHARWAD

4 TINA DHARWAD

5 ALEN BELGAUM

6 FRED BELGAUM

6 rows selected.

SQL> SELECT * FROM ORDERS;

ORDERNO ODATE CUSTNO ORDAMT

1 01-JAN-11 1 1000

2 02-FEB-11 1 2000

3 03-MAR-11 3 1000

4 01-JAN-11 5 2000

5 02-FEB-11 5 5000

6 03-MAR-11 5 3000

7 01-JAN-11 4 2000

8 02-FEB-11 3 8000

8 rows selected.

SQL> SELECT * FROM ITEM;

ITEMNO UNITPRICE

1 50

2 100

3 150

4 200

5 250

6 300

7 350

8 400

8 rows selected.

SQL> SELECT * FROM ORDERITEM;

ORDERNO ITEMNO QTY

1 1 10

2 1 20

1 8 30

3 4 40

3 5 50

4 1 20

4 2 20

6 5 30

8 rows selected.

SQL> SELECT * FROM WAREHOUSE;

WAREHOUSENO CITY

1 MUMBAI

2 MUMBAI

3 NAGPUR

4 DELHI

5 BENGALURU

6 BENGALURU

7 BENGALURU

8 DELHI

8 rows selected.

SQL> SELECT * FROM SHIPMENT;

ORDERNO WAREHOUSENO SHIPDATE

3 1 20-JUN-10

2 1 09-MAY-11

1 1 01-MAY-11

1 2 02-MAY-11

4 2 04-MAY-11

5 4 05-MAY-11

6 4 06-MAY-11

3 2 01-MAY-11

8 rows selected.

4. Produce a listing: customer name, number of orders, average order amount where the middle column is the total number of orders by the customer and the last column is average order amount for that customer.

SQL> SELECT C.CNAME,COUNT(O.ORDERNO) AS NO_OF_ORDERS,AVG(O.ORDAMT) AS

2 AVG_AMOUNT

3 FROM CUSTOMER C,ORDERS O

4 WHERE C.CUSTNO=O.CUSTNO

5 GROUP BY C.CNAME;

CNAME NO_OF_ORDERS AVG_AMOUNT

ALEN 3 3333.33333

DAVID 2 4500

JOHN 1 2000

5. List the order number for the orders that were shift from all the warehouses that the company has in a specific city.

SQL> SELECT DISTINCT S.ORDERNO AS ORDER_NO

2 FROM SHIPMENT S, WAREHOUSE W

3 WHERE W.WAREHOUSENO=S.WAREHOUSENO

4 AND W.CITY LIKE 'MUMBAI' GROUP BY S.ORDERNO

5 HAVING COUNT(S.WAREHOUSENO)=(SELECT COUNT(W.WAREHOUSENO)

6 FROM WAREHOUSE W

7 WHERE W.CITY='MUMBAI');

ORDER_NO

6. Demonstrate how you delete item number 10 from the item table and make that field null in the orderitem table.

SQL> DELETE FROM ITEM WHERE ITEMNO=5;

1 row deleted.

SQL> SELECT * FROM ITEM;

ITEMNO UNITPRICE

1 50

2 100

3 150

4 200

6 300

7 350

8 400

7 rows selected.

SQL> SELECT * FROM ORDERITEM;

ORDERNO ITEMNO QTY

1 1 10

2 1 20

1 8 30

3 4 40

3 2 50

4 1 20

4 2 20

6 30

8 rows selected.

Data Base Management System Querry 1

2011-12-07T23:24:00.000+05:30

Consider the insurance database given below. The primary keys are underlined and the datatypes are specified.

PERSON (driverid: String, name: String, address: String)

CAR (regno: String, model: String, year: Int)

ACCIDENT (repno: Int, dat: Date, location: String)

OWNS (driverid: String, regno: String)

PARTICIPATED (driverid: String, regno: String, repno: Int, damageamt: Int)

1. Create the above tables by properly specifying the primary keys and foreign keys.

SQL> CREATE TABLE PERSON(

2 DRIVERID VARCHAR(10),

3 NAME VARCHAR(10),

4 ADDRESS VARCHAR(10),

5 PRIMARY KEY(DRIVERID));

Table created.

SQL> CREATE TABLE CAR(

2 REGNO VARCHAR(10),

3 MODEL VARCHAR(10),

4 YEAR INT,

5 PRIMARY KEY(REGNO));

Table created.

SQL>CREATE TABLE ACCIDENT(

2 REPNO INT,

3 DAT DATE,

4 LOCATION VARCHAR(10),

5 PRIMARY KEY(REPNO));

Table created.

SQL>CREATE TABLE OWNS(

2 DRIVERID VARCHAR(10),

3 REGNO VARCHAR(10),

4 PRIMARY KEY(DRIVERID,REGNO),

5 FOREIGN KEY(DRIVERID) REFERENCES PERSON(DRIVERID),

6 FOREIGN KEY(REGNO) REFERENCES CAR(REGNO));

Table created.

SQL> CREATE TABLE PARTICIPATED(

2 DRIVERID VARCHAR(10),

3 REGNO VARCHAR(10),

4 REPNO INT,

5 DAMAGEAMT INT,

6 PRIMARY KEY(DRIVERID,REGNO,REPNO),

7 FOREIGN KEY(DRIVERID) REFERENCES PERSON(DRIVERID),

8 FOREIGN KEY(REGNO) REFERENCES CAR(REGNO),

9 FOREIGN KEY(REPNO) REFERENCES ACCIDENT(REPNO));

Table created.

2. Enter at least 7-8 tuples for each relation.

SQL> INSERT INTO PERSON VALUES('&DRIVERID','&NAME','&ADDRESS');

Enter value for driverid: D1

Enter value for name: JACK

Enter value for address: HBL

old 1: INSERT INTO PERSON VALUES('&DRIVERID','&NAME','&ADDRESS')

new 1: INSERT INTO PERSON VALUES('D1','JACK','HBL')

1 row created.

SQL> INSERT INTO CAR VALUES('&REGNO','&MODEL',&YEAR);

Enter value for regno: C1

Enter value for model: ALTO

Enter value for year: 2008

old 1: INSERT INTO CAR VALUES('&REGNO','&MODEL',&YEAR)

new 1: INSERT INTO CAR VALUES('C1','ALTO',2008)

1 row created.

SQL> INSERT INTO ACCIDENT VALUES(&REPNO,'&DAT','&LOCATION');

Enter value for repno: 1

Enter value for dat: 01-JAN-11

Enter value for location: HBL

old 1: INSERT INTO ACCIDENT VALUES(&REPNO,'&DAT','&LOCATION')

new 1: INSERT INTO ACCIDENT VALUES(1,'01-JAN-11','HBL')

1 row created.

SQL> INSERT INTO OWNS VALUES('&DRIVERID','&REGNO');

Enter value for driverid: D1

Enter value for regno: C2

old 1: INSERT INTO OWNS VALUES('&DRIVERID','&REGNO')

new 1: INSERT INTO OWNS VALUES('D1','C2')

1 row created.

SQL> INSERT INTO PARTICIPATED VALUES('&DRIVERID','&REGNO',&REGNO,&DAMAGEAMT);

Enter value for driverid: D1

Enter value for regno: C6

Enter value for regno: 1

Enter value for damageamt: 20000

old 1: INSERT INTO PARTICIPATED VALUES('&DRIVERID','&REGNO',&REGNO,&DAMAGEAMT)

new 1: INSERT INTO PARTICIPATED VALUES('D1','C6',1,20000)

1 row created.

3. Displaying the table contents.

SQL> SELECT * FROM PERSON;

DRIVERID NAME ADDRESS

D1 JACK HBL

D2 JOHN BLG

D3 JAMES BLR

D4 FRED DWD

D5 TINA DWD

SQL> SELECT * FROM CAR;

REGNO MODEL YEAR

C1 ALTO 2008

C2 SANTRO 2009

C3 SWIFT 2008

C4 ALTO 2010

C5 FIGO 2009

C6 FIGO 2008

6 rows selected.

SQL> SELECT * FROM ACCIDENT;

REPNO DAT LOCATION

3 03-MAR-11 DWD

1 01-JAN-11 HBL

2 02-FEB-10 BLR

4 04-APR-10 HBL

5 05-MAY-11 BLR

6 06-JUN-11 BLG

6 rows selected.

SQL> SELECT * FROM OWNS;

DRIVERID REGNO

D1 C2

D1 C4

D1 C6

D2 C1

D2 C3

D2 C5

D3 C6

D4 C2

D5 C1

9 rows selected.

SQL> SELECT * FROM PARTICIPATED;

DRIVERID REGNO REPNO DAMAGEAMT

D1 C6 1 20000

D3 C6 2 10000

D2 C1 3 15000

D5 C1 4 25000

D4 C2 5 25000

D2 C1 6 10000

6 rows selected.

4. Demonstrate how you:

a) Update the damage amount for the car with the specific register number in the accident with report number 2 to Rs.25000.

SQL> UPDATE PARTICIPATED

2 SET DAMAGEAMT=25000

3 WHERE REPNO=2;

1 row updated.

SQL> SELECT * FROM PARTICIPATED;

DRIVERID REGNO REPNO DAMAGEAMT

D1 C6 1 20000

D3 C6 2 25000

D2 C1 3 15000

D5 C1 4 25000

D4 C2 5 25000

D2 C1 6 10000

6 rows selected.

b) Add a new accident to the database.

SQL> INSERT INTO ACCIDENT(REPNO,DAT,LOCATION) VALUES(7,'7-JUL-08','MLR');

1 row created.

SQL> SELECT * FROM ACCIDENT;

REPNO DAT LOCATION

1 01-JAN-11 HBL

2 02-FEB-10 BLR

3 03-MAR-11 DWD

4 04-APR-10 HBL

5 05-MAY-11 BLR

6 06-JUN-11 BLG

7 07-JUL-08 MLR

7 rows selected.

SQL> INSERT INTO PARTICIPATED(DRIVERID,REGNO,REPNO,DAMAGEAMT)

2 VALUES('D1','C4',7,31000);

1 row created.

SQL> SELECT * FROM PARTICIPATED;

DRIVERID REGNO REPNO DAMAGEAMT

D1 C6 1 20000

D3 C6 2 25000

D2 C1 3 15000

D5 C1 4 25000

D4 C2 5 25000

D2 C1 6 10000

D1 C4 7 31000

7 rows selected.

5. Find the total number of people who owned the cars that were involved in the accident in 2008.

SQL> SELECT COUNT(*) AS CAR_ACCIDENTS

2 FROM OWNS

3 WHERE REGNO

4 IN( SELECT REGNO

5 FROM PARTICIPATED

6 WHERE REPNO

7 IN(SELECT REPNO

8 FROM ACCIDENT

9 WHERE DAT LIKE '%11'));

CAR_ACCIDENTS

6. Find number of accidents in which cars belonging to a specific model were involved.

SQL> SELECT COUNT(*) AS NO_OF_ACCIDENTS

2 FROM PARTICIPATED

3 WHERE REGNO

4 IN (SELECT REGNO

5 FROM CAR

6 WHERE MODEL LIKE 'ALTO');

NO_OF_ACCIDENTS

SRS Format and Its sample.

2011-10-15T19:16:00.000+05:30

Link for SRS format -->

https://docs.google.com/viewer?a=v&pid=explorer&chrome=true&srcid=0B__oAxJAJw1gNDVjNGIxNGEtOTlhNC00OWViLTgxOTItOWM5YWY5N2M4OWU5&hl=en_US
.
.
Link for SRS sample-->

https://docs.google.com/leaf?id=0B__oAxJAJw1gMzE1YjcwOTQtZjVhNS00YzY0LWFjYWEtZjI3OGQ1MWZhYTJi&hl=en_US
.
.
Last date for Submission of SRS and IEEE format paper is 10-11-2011

CGPA calculator...

2011-07-06T01:21:00.000+05:30

Here it is a CGPA calculator, used for calculating CGPA as well as SGPA...

https://docs.google.com/leaf?id=0ByvWG3uTA27kYzZkMjQ0OGMtYmJlNy00OTY0LTlkYTUtZDliMTg1MWZlOTk2&hl=en

goto above mentioned link and download it and get started.

ALL THE BAEST

C programme without main() !!?? Is it possible!!?? One who thinks "NO" just try the posted programme..

2011-04-19T20:24:00.001+05:30

#include<stdio.h>
#define decode(s,t,u,m,p,e,d) m##s##u##t
#define begin decode(a,n,i,m,a,t,e)
begin()
{
       printf(" without main \n");
       system("PAUSE");           // is required in Dev C++ compiler
}
Ha ha... try and think how its possible, if you wont find how its possible then just mail me at
mailto:maruti.hulikatti@gmail.com   will reply you with solution.