Be patient, Everything is coming Together.: August 2018

Tuesday, August 21, 2018

Game XML Design Code

click here to download

Friday, August 17, 2018

working on employee schema

Create the following tables based on the above Schema Diagram with appropriate data types

and constraints.

EMPLOYEE (Fname, Mname, Lname, SSN, Bdate, Address, Gender, Salary, SuperSSN, Dno)

DEPARTMENT(Dnumber, Dname, MgrSSN, Mgrstartdate)

DEPENDENT (ESSN, Dependent_Name, Gender, Bdate, Relationship)

SQL> create table employee_18 (fname varchar2(20),minit varchar2(1),lname varchar2(20),ssn number(10) primary key,bdate date,address varchar2(40),sex varchar2(1),salary number(5),super_ssn number(9),dno number(1));

SQL> Insert into employee values('&fname','&minit','&lname',&ssn,'&bdate','&address','&sex',&salary,&super_ssn,&dno);

Enter value for fname:john

Enter value for minit:B

Enter value for lname:Smith

Enter value for ssn:123456789

Enter value for bdate:09-jan-1965

Enter value for address:731Fondren,Houston,TX

Enter value for sex:M

Enter value for salary:30000

Enter value for super_ssn:333445555

Enter value for dno:5

1 row created.

SQL> /

Enter value for fname:Franklin

Enter value for minit:T

Enter value for lname:Wong

Enter value for for: ssn:333445555

Enter value for bdate:08-dec-1955

Enter value for address:638Voss,Houston,TX

Enter value for sex:M

Enter value for salary:40000

Enter value for super_ssn:888665555

Enter value for dno:5

1 row created.

SQL> /

Enter value for fname:Alica

Enter value for minit:J

Enter value for lname:Zelaya

Enter value for for: ssn:999887777

Enter value for bdate:19-jan-1968

Enter value for address:3321Castle,Spring,TX

Enter value for sex:F

Enter value for salary:25000

Enter value for super_ssn:987654321

Enter value for dno:4

1 row created.

SQL> /

Enter value for fname:Jennifer

Enter value for minit:S

Enter value for lname:Wallace

Enter value for for: ssn:987654321

Enter value for bdate:20-jun-1941

Enter value for address:291Berry,Bellaire,TX

Enter value for sex:F

Enter value for salary:43000

Enter value for super_ssn:888665555

Enter value for dno:4

1 row created.

SQL> /

Enter value for fname:Ramesh

Enter value for minit:K

Enter value for lname:Narayan

Enter value for for: ssn:666884444

Enter value for bdate:15-sep-1962

Enter value for address:975 Fire Oak,Humble,TX

Enter value for sex:M

Enter value for salary:38000

Enter value for super_ssn:333445555

Enter value for dno:5

1 row created.

SQL> /

Enter value for fname:Joyce

Enter value for minit:A

Enter value for lname:English

Enter value for for: ssn:453453453

Enter value for bdate:31-jul-1972

Enter value for address:5631Rice,Houston,TX

Enter value for sex:F

Enter value for salary:25000

Enter value for super_ssn:333445555

Enter value for dno:5

1 row created.

SQL> /

Enter value for fname:Ahmad

Enter value for minit:V

Enter value for lname:Jabbar

Enter value for for: ssn:987987987

Enter value for bdate:29-mar-1969

Enter value for address:980Dallas,Houston,TX

Enter value for sex:M

Enter value for salary:25000

Enter value for super_ssn:987654321

Enter value for dno:4

1 row created.

SQL> /

Enter value for fname:James

Enter value for minit:E

Enter value for lname:Borg

Enter value for for: ssn:888665555

Enter value for bdate:10-nov-1937

Enter value for address:450Stone,Houston,TX

Enter value for sex:M

Enter value for salary:55000

Enter value for super_ssn:NULL

Enter value for dno:1

1 row created.

SQL> create table department(dname varchar2(10),dnum number(1) primary key,mgr_ssn number(9),mgr_start_date date );

Table created.

SQL> insert into department values('&dname',&dnum,&mgr_ssn,'&mgr_start_date');

Enter value for dname:Research

Enter value for dnum:5

Enter value for mgr_ssn:333445555

Enter value for mgr_start_date:22-may-1988

1 row created.

SQL> /

Enter value for dname:Administration

Enter value for dnum:4

Enter value for mgr_ssn:987654321

Enter value for mgr_start_date:01-jan-1995

1 row created.

SQL> /

Enter value for dname:Headquarters

Enter value for dnum:1

Enter value for mgr_ssn:888665555

Enter value for mgr_start_date:19-jun-1981

1 row created.

SQL> Create table Dept_locations(dnum number(1),dlocation varchar2(10),primary key(dnum,dlocation);

);

Table created.

SQL> insert into dept_locations values(&dnum,'&dlocation');

Enter value for dnum:1

Enter value for dlocation:Houston

1 row created.

SQL> /

Enter value for dnum:4

Enter value for dlocation:Stafford

1 row created.

SQL> /

Enter value for dnum:5

Enter value for dlocation:Bellaire

1 row created.

SQL> /

Enter value for dnum:5

Enter value for dlocation:SugarLand

1 row created.

SQL> /

Enter value for dnum:5

Enter value for dlocation:Houston

1 row created.

SQL> create table Works_on(essn number(10),pno number(2),hours number(3,1),primary key(essn,pno));

Table created.

SQL> insert into works_on values(&essn,&pno,&hours) ;

Enter value for essn:123456789

Enter value for pno:1

Enter value for hours:32.5

1 row created.

SQL> /

Enter value for essn:123456789

Enter value for pno:2

Enter value for hours:7.5

1 row created.

SQL> /

Enter value for essn:666884444

Enter value for pno:3

Enter value for hours:40.0

1 row created.

SQL> /

Enter value for essn:453453453

Enter value for pno:1

Enter value for hours:20.0

1 row created.

SQL> /

Enter value for essn:453453453

Enter value for pno:2

Enter value for hours:20.0

1 row created.

SQL> /

Enter value for essn:333445555

Enter value for pno:2

Enter value for hours:10.0

1 row created.

SQL> /

Enter value for essn:333445555

Enter value for pno:3

Enter value for hours:10.0

1 row created.

SQL> /

Enter value for essn:333445555

Enter value for pno:10

Enter value for hours:10.0

1 row created.

SQL> /

Enter value for essn:333445555

Enter value for pno:20

Enter value for hours:10.0

1 row created.

SQL> /

Enter value for essn:999887777

Enter value for pno:30

Enter value for hours:30.0

1 row created.

SQL> /

Enter value for essn:999887777

Enter value for pno:10

Enter value for hours:10.0

1 row created.

SQL> /

Enter value for essn:987987987

Enter value for pno:10

Enter value for hours:35.0

1 row created.

SQL> /

Enter value for essn:987987987

Enter value for pno:10

Enter value for hours:5.0

1 row created.

SQL> /

Enter value for essn:987654321

Enter value for pno:30

Enter value for hours:20.0

1 row created.

SQL> /

Enter value for essn:987654321

Enter value for pno:20

Enter value for hours:15.0

1 row created.

SQL> /

Enter value for essn:888665555

Enter value for pno:20

Enter value for hours:NULL

1 row created.

SQL> /

Enter value for essn:123456789

Enter value for pno:3

Enter value for hours:10.0

1 row created.

SQL> create table project(pname varchar2(10),pnum number(2) primary key,plocations varchar2(10),dnum number(1));

Table created.

SQL> insert into project values('&pname',&pnum,'&ploc',&dnum);

Enter value for pname:ProductX

Enter value for pnum:1

Enter value for ploc:Bellaire

Enter value for dnum:5

1 row created.

SQL> /

Enter value for pname:ProductY

Enter value for pnum:2

Enter value for ploc:SugarLand

Enter value for dnum:5

1 row created.

SQL> /

Enter value for pname:ProductZ

Enter value for pnum:3

Enter value for ploc:Houston

Enter value for dnum:5

1 row created.

SQL> /

Enter value for pname:Computerization

Enter value for pnum:10

Enter value for ploc:Stafford

Enter value for dnum:4

1 row created.

SQL> /

Enter value for pname:Reorganization

Enter value for pnum:20

Enter value for ploc:Houston

Enter value for dnum:1

1 row created.

SQL> /

Enter value for pname:Newbenefits

Enter value for pnum:30

Enter value for ploc:Stafford

Enter value for dnum:4

1 row created.

SQL> create table dependent(essn number(9),dependent_namevarchar2(10),sex char(1),bdate date,realationship varchar2(10),primary key(essn,dependent_name) );

Table created.

SQL> insert into dependent values(&essn,'&dependent_name','&sex','&bdate','&realationship');

Enter value for essn:333445555

Enter value for dependent_name:Alice

Enter value for sex:F

Enter value for bdate:05-apr-1986

Enter value for realationship:Daughter

1 row created.

SQL> /

Enter value for essn:333445555

Enter value for dependent_name:Theodore

Enter value for sex:M

Enter value for bdate:25-nov-1983

Enter value for realationship:Son

1 row created.

SQL> /

Enter value for essn:333445555

Enter value for dependent_name:Joy

Enter value for sex:F

Enter value for bdate:03-may-1958

Enter value for realationship:Spouse

1 row created.

SQL> /

Enter value for essn:987654321

Enter value for dependent_name:Abner

Enter value for sex:M

Enter value for bdate:28-feb-1942

Enter value for realationship:Spouse

1 row created.

SQL> /

Enter value for essn:123456789

Enter value for dependent_name:Michael

Enter value for sex:M

Enter value for bdate:04-jan-1988

Enter value for realationship:Son

1 row created.

SQL> /

Enter value for essn:123456789

Enter value for dependent_name:Alice

Enter value for sex:F

Enter value for bdate:30-dec-1988

Enter value for realationship:Daughter

1 row created.

SQL> /

Enter value for essn:123456789

Enter value for dependent_name:Elizabeth

Enter value for sex:F

Enter value for bdate:05-may-1967

Enter value for realationship:Spouse

1 row created.

SQL> alter table employee add foreign key(super_ssn) references employee;

Table altered.

SQL> alter table employee add foreign key(dno) references department;

Table altered.

SQL> alter table department add foreign key(mgr_ssn) references employee;

Table altered.

SQL> alter table works_on add foreign key(essn) references employee;

Table altered.

SQL> alter table works_on add foreign key(pno) references project;

Table altered.

SQL> alter table project add foreign key(dnum) references department;

Table altered.

SQL> alter table dependent add foreign key(essn) references employee;

Table altered.

Working on Sailors Table

1.Find the names and ages of all sailors.
SQL> select sname,age from sailors;
SNAME             AGE
----------           ----------
Dustin                 45.0
Brutus                 33.0
Lubber                55.5
Andy                   25.5
Rusty                  35.0
Horatio               35.0
Zorba                 16.0
Horatio              35.0
Art                     25.5
Bob                   63.5
10 rows selected.

2.Find all sailors with a rating above 7.
SQL> select * from sailors where rating>7;
       SID SNAME          RATING        AGE
       ------ ---------- ---------- ----------
        31 Lubber              8       55.5
        32 Andy                8       25.5
        58 Rusty              10         35
        71 Zorba              10         16
        74 Horatio             9         35

3.Find the names of sailors who have reserved boat number 103.
SQL> select * from sailors s,reserves r where s.sid=r.sid and r.bid=103;
       SID SNAME          RATING        AGE    BID    DAY
---------- ---------- ---------- ---------- -------- ------
        22 Dustin              7         45     103   10-AUG-98
        31 Lubber              8       55.5     103   11-JUN-98
        74 Horatio             9         35     103   09-AUG-98

4.Find the sids of sailors who have reserved a red boat.
SQL> select s.sid from sailors s,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red';
       SID
----------
        22
        22
        31
        31
        64

5.Find the names of sailors who have reserved a red boat.
SQL> select s.sname from sailors s,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red';
SNAME
----------
Dustin
Dustin
Lubber
Lubber
Horatio

6.Find the colors of boats reserved by Lubber.
SQL>select b.color from sailors s,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and s.sname='Lubber';

COLOR
-----
red
green
red

7.Find the names of sailors who have reserved atleast one boat.
SQL> select s.sname from sailors s,reserves r,boats b where s.sid=r.sid   and r.bid=b.bid and s.sid in (select sid from reserves group by sid having count(s.sid)>1)
SNAME
----------
Dustin
Dustin
Dustin
Dustin
Lubber
Lubber
Lubber
Horatio
Horatio
9 rows selected.

8.Compute increments for the ratings of persons who have sailed two different boats on the same day.
SQL> select rating+1 from sailors where sid in (select sid from reserves group by sid having count(day)>+2);
RATING+1
----------
         8
         9

9.Find the ages of sailors whose name begins and ends with B and has at least three characters.
SQL>select age from sailors s where s.sname like 'B_%b';
       AGE
----------
      63.5

10.Find the names of sailors who have reserved a red or a green boat.
SQL>( select s.sname from sailors s ,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red')intersect (select s.sname from sailors s ,reserves r,boats b   where s.sid=r.sid and   r.bid=b.bid and b.color='green')
SNAME
----------
Dustin
Horatio
Lubber

11.Find the names of sailors who have reserved aboth a red and a green boat.
SQL>select s.sname from sailors s where s.sid in((select r.sid from boats18 b,reserves r where r.bid=b.bid and b.color='red') UNION (select r2.sid from boats b2,reserves r2 where r2.bid=b2.bid and b2.color='green'));
SNAME
----------
Dustin
Lubber

12.Find the sids of all sailors who have reserved red boats but not green boats.
SQL> select s.sid from sailors s ,reserves r,boats b   where   s.sid=r.sid and r.bid=b.bid and b.color='red' and b.color!='green';
       SID
----------
        22
        22
        31
        31
        64

13.Find all sids of sailors who have amrating of 10 or have reserved boat 104
SQL>(select s.sid from sailors s ,reserves r,boats b where   s.sid=r.sid and s.rating=10) union (select s.sid from sailors s ,reserves r,boats b where s.sid=r.sid and r.bid=104);
       SID
----------
        22
        31

14.Find the names of sailors who have not reserved a red boat.
SQL> select s.sid from sailors s ,reserves r,boats b where   s.sid=r.sid   and r.bid=b.bid and b.color!='red';
       SID
----------
        22
        22
        31
        64
        74


15.Find sailors whose rating is better than some sailor called Horatio.
SQL> select * from sailors where rating > all(select rating from sailors where sname='Horatio');
       SID SNAME          RATING        AGE
---------- ---------- ---------- ----------
        58 Rusty              10         35
        71 Zorba              10         16


16.Find sailors whose rating is better than every sailor called Horatio.
SQL>select * from sailors where rating > any (select rating from sailors where sname='Horatio');
       SID SNAME          RATING        AGE
---------- ---------- ---------- ----------
        31 Lubber              8       55.5
        32 Andy                8       25.5
        58 Rusty              10         35
        71 Zorba              10         16
        74 Horatio             9         35


17.Find the sailors with the highest rating.
SQL> select sname from sailors where rating=(select max(rating) from sailors );
SNAME
----------
Rusty
Zorba

18.Find the names of sailors who have reserved aboth a red and a green boat.
SQL>select s.sname from sailors s where s.sid in((select r.sid from boats18 b,reserves r where r.bid=b.bid and b.color='red') UNION (select r2.sid from boats b2,reserves r2 where r2.bid=b2.bid and b2.color='green'));
SNAME
----------
Dustin
Lubber


19.Find the names of sailors who have reserved all boats.
SQL> select * from sailors where not exists (( select bid from boats) minus (select bid from sailors s,reserves r where s.sid=r.sid));
       SID SNAME          RATING        AGE
---------- ---------- ---------- ----------
        22 Dustin              7         45
        29 Brutus              1         33
        31 Lubber              8       55.5
        32 Andy                8       25.5
        58 Rusty              10         35
        64 Horatio             7         35
        71 Zorba              10         16
        74 Horatio             9         35
        85 Art                 3       25.5
        95 Bob                 3       63.5
10 rows selected.

20.Find the average age of all sailors.
SQL> select avg(age) from sailors;
AVG(AGE)
----------
      36.9


21.Find the average of sailors with a rating of 10.
SQL> select avg(age) from sailors where rating=10;
AVG(AGE)
----------
      25.5


22.Find the name and age of oldest sailor.
SQL> select sname , age from sailors where age=(select max(age) from sailors);
SNAME             AGE
---------- ----------
Bob              63.5


23.Count the number of sailors.
SQL> select count(sid) from sailors;
COUNT(SID)
----------
        10


24.Count the number of different sailor names.
SQL> select distinct count(distinct(sname)) from sailors;
COUNT(DISTNCT(SNAME))
----------
        9

25.Find the names of sailors who are older than the oldest sailor with a rating of 10.
SQL>select * from sailors where age>all(select age from sailors where rating=10);
SID   SNAME           RATING       AGE
-----   -----------      -------------     --------
22   dustin           7       45
31   lubber           8       55.5
95   bob           3       63.5

26.Find the age of the youngest sailor for each rating level.
SQL> select rating,min(age) from sailors group by rating
    RATING   MIN(AGE)
---------- ----------
         1         33
         3       25.5
         7         35
         8       25.5
         9         35
        10         16
6 rows selected.

27.Find the age of the youngest sailor who is eligible to vote (i.e., is atleast 18 years old) for each rating level with atleast two such sailors.
SQL>select min(age) from sailors where age>=18 group by rating having count(age)>1;
MIN(AGE)
________________
25.5
35
25.5

28.For each red boat, find the number of reservations for this boat.
SQL> select count(r.sid) from reserves r,boats b where r.bid=b.bid and b.color='red'group by b.bid;
COUNT(R.SID)
___________________
3
2

29. Find the average age of sailors for each rating level that has atleast two sailors.
SQL> select avg(age),rating,count(sid) from sailors group by rating   having count(sid)>=2;
   AVG(AGE)     RATING COUNT(SID)
    ----------           ----------        ----------
          44.5                  3          2
          40                    7          2
          40.5                  8          2
          25.5                  10         2

30.Find the average age of sailors who are of voting age (i.e., is atleast 18 years old) for each rating level that has atleast two sailors.
SQL> select avg(age) from sailors group by rating having count(sid)>1;
AVG(AGE)
_______________
40.5
40
44.5
25.5

31.Find the average age of sailors who are of voting age (i.e., is atleast 18 years old) for each rating level that has atleast two such sailors.
SQL> select min(age) from sailors where age>=18 group by rating having count(age)>1;
MIN(AGE)
________________
25.5
35
25.5

32. Find those ratings for which the average age of sailors is the minimum over all ratings.
SQL> select rating from (select avg(age) age,rating from sailors group by rating) where age=(select min(avg(age)) from sailors group by rating);
    RATING
----------
        10