All the learnig objectives.

Monday, 12 December 2011

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

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

Sunday, 11 December 2011

Company DataBase 6.42 solution of 5th edition Navathe TextBook

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