I. SCHEMAS
Table 1 : STUDIES
PNAME (VARCHAR), SPLACE (VARCHAR), COURSE (VARCHAR), CCOST (NUMBER)
Table 2 : SOFTWARE
PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR), SCOST (NUMBER), DCOST (NUMBER), SOLD (NUMBER)
Table 3 : PROGRAMMER
PNAME (VARCHAR), DOB (DATE), DOJ (DATE), SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHAR), SAL (NUMBER)
LEGEND :
PNAME – Programmer Name, SPLACE – Study Place, CCOST – Course Cost, DEVIN – Developed in, SCOST – Software Cost, DCOST – Development Cost, PROF1 – Proficiency 1
QUERIES :
Programmer
Mr. Arvind – has 15 years of experience
KEYS:
32.SELECT ‘Mr.’ || PNAME || ‘ – has ‘ || TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) || ‘ years of experience’ “Programmer” FROM PROGRAMMER WHERE SEX = ‘M’ UNION SELECT ‘Ms.’ || PNAME || ‘ – has ‘ || TRUNC (MONTHS_BETWEEN (SYSDATE,DOJ)/12) || ‘ years of experience’ “Programmer” FROM PROGRAMMER WHERE SEX = ‘F’;
II . SCHEMA :
Table 1 : DEPT
DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)
Table 2 : EMP
EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2))
MGR is the empno of the employee whom the employee reports to. DEPTNO is a foreign key.
QUERIES
KEYS: