oracle sql statement help. I will upvote a clear, detailed response. Thank you!!

more joins (inner and outer), subqueries and other SQL issues

You are suggested to adopt a user friendly prefix string for all the tables and other objects you will create in your Oracle server user schema for your homework solutions. For example th3p11xxx means a table for homework 3s problem 1s first table, xxx stands for a meaningful table name, say employee. As for the sample tables used in the class such as the tables in the company database, you can just keep the original names.

Important Announcement: It will be helpful to attack the questions at the end of each chapter!

Reading and practicing: Read the textbook, notes, sample scripts and any reference book you like.

1) (20 points) Use the relational schema of the company database you can see at bottom of this page. Your instructor already illustrated how to use it in the class. Use the schema to design queries to answer the following questions.

a) Retrieve the information for employees who come from department 1 or 2. Hints: two ways, way 1: compound searching criteria, way2: using union set operations

b) Retrieve fname, lname and salary information from employee table

c) Retrieve each Departments name and its managers name. Hints: you need to join two tables through certain common field.

d) Retrieve both employee names and all dependent names

2. (30 points) Here are a couple subquery problems regarding to the tables in the company database. Although you might be tempted to use flat queries to answer some of them, you have to use sub-queries (if they are not marked as non-flat query problems) in this question in order to get better understanding to sub-queries. (Hints: sample script file and textbook.) To find out the script, visit homework 3 for the script.

a) find out the lowest salary (flat query)

b) find out the lowest salary for each department (flat query) hint: use group by

c) Find out the names of those people whose salary is the lowest salary in each department. (A little bit more challenging, use nested query or called subquery)

d) find out ssns of those people who work on two projects. (nested query)

e) Find out who works on two projects, i.e. to find out names of those people

f) write a select command to calculate what the salary would be if every body get a 7% raise. (flat query)

g) Write an update command to give all employees in the Research department a seven percent raise in salary.

h) Retrieve the names of all employees who have two or more dependents

3. (20 points)In Oracle, tables, procedures etc. are called objects. Each object has an owner. For example, if you create a table, you are the owner of the table. Therefore, by default, your account is the owner of all the objects created by you using your account. Whenever you retrieve information from a table named as tablename, Oracle will automatically append your account name as the prefix to the table name ending with a dot delimiter. Then Oracle will check whether the table youraccount.tablename exists or not. If yes, Oracle certainly allows you to retrieve information from that table. For example, when I say select * from employee; it actually is equivalent to saying that select * from s14zhangs.employee; where s14zhangs is my account name. Otherwise, if you want to use a table owned by another account, you have to explicitly use owner account of the table. For example, your account name is tom, but you want to retrieve information from a table t1 created by Kathy in the same database, you have to use select * from Kathy.t1 instead of select * from t1.

a. List the result of the select * from zhangsfall05.employee; hint you will be shown that ORA-00942: table or view does not exist, but the reason is that you have no privilege to do so

b. List the result of select * from employee; if you have no employee table, create that table by running the company script, then redo this query.

c. list the result of select * from youraccount.employee; note that you need to use your oracle account in the query. The result should be the same as b).

d. show script how to create a synonym for employee table, and then select all from both the original table and the synonym just created by you to compare the result. The result should be the same as b and c.

create synonym s for employee;

e. show script how to create a public synonym for employee table. Hint, you will find that you have no privilege to create public synonym.

create public synonym ps for employee;

ERROR at line 1:

ORA-00942: table or view does not exist

you need explicit GRANT select privilege

grant select on ps to yourpartnerid;

Hint:

Result of 1) and 2) will remind you that Oracle server has a very strong privilege control mechanism. As a common user, you do have abilities to grant some privilege to other users, your power is very limited. I need to grant your proper privileges to your accounts for you to perform certain operations .

If you have privilege problems, talk to your instructor to get the privilege granted.

4. (30) inner join, outer join

Let us assume you have following commands to create two tables and have them populated with a couple records.

create table student

(

ssn varchar2(10) primary key,

name varchar2(20)

);

insert into student values('111', 'tester');

insert into student values('222', 'tester2');

insert into student values('333', 'tester2');

create table course

(

ssn varchar2(20),

courseid varchar2(20)

);

insert into course values ('111', 'CSCI242');

insert into course values ('111', 'CSCI203');

insert into course values ('222', 'CSCI203');

insert into course values ('', 'CSCI204');

a. If a student takes a course, list the students ssn name and the course name, if a student takes multiple courses, multiple records should be listed. the result should look like the following. write a SQL command to find out the answer.

SSN NAME COURSEID

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

111 tester CSCI242

111 tester CSCI203

222 tester2 CSCI203

b. In addition to the above result, if a student exists in the student table but does not take any courses, we also want it to be listed in the result just leave courseid field blank. write a SQL command to find out the answer.

SSN NAME COURSEID

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

111 tester CSCI242

111 tester CSCI203

222 tester2 CSCI203

333 tester2

b. We want to list all course names in the following table, regardless it is registered by any student or not. If it is registered by multiple students, multiple records need to be listed, if a course in the course table is not registered by any people, the course still needs to be listed simply with ssn and names blank. write a SQL command to get the following result.

SSN NAME COURSEID

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

111 tester CSCI242

111 tester CSCI203

222 tester2 CSCI203

CSCI204

Schema and tables*************************

--comments can be added

DROP TABLE works_on;

DROP TABLE dependent;

DROP TABLE project;

DROP TABLE dept_location;

ALTER TABLE department DROP CONSTRAINT fk_mgrssn CASCADE;

ALTER TABLE employee DROP CONSTRAINT fk_dno CASCADE;

DROP TABLE department;

DROP TABLE employee;

CREATE TABLE employee

(fname VARCHAR2(12),

minit CHAR(1),

lname VARCHAR2(12),

ssn CHAR(9) NOT NULL,

bdate DATE,

address VARCHAR2(40),

sex CHAR(1),

salary NUMBER(7) NOT NULL,

superssn CHAR(9) references employee(ssn),

/*superssn CHAR(9),*/

/*either way, but need to know superssn is from ssn*/

CONSTRAINT pk_ssn PRIMARY KEY (SSN));

ALTER TABLE employee ADD (dno NUMBER(2) NOT NULL);

INSERT INTO employee VALUES ('Wolons', 'E', 'Aimee', '888665555',

'10-NOV-1927', '450 Stone, edsion, NJ', 'F', 55000, null,1);

INSERT INTO employee VALUES ('Ramirez', '', 'Damian', '987654321',

'20-JUN-1931', '291 Berry, albany, NY', 'M', 43000, '888665555',4);

INSERT INTO employee VALUES ('Hall', '', 'Nathan', '333445555',

'09-DEC-1945', '638 bruce, Oneonta, NY', 'M', 40000, '888665555',5);

INSERT INTO employee VALUES ('Kelly', '', 'David', '999778888',

'19-JUL-1958', '3321 central ave, New York, NY', 'M', 25000, '987654321',4);

INSERT INTO employee VALUES ('Philip', '', 'Dolensek', '123456789',

'09-JAN-1955', '731 clinton, Oneonta, NY', 'M', 30000, '333445555',5);

INSERT INTO employee VALUES ('Reale', '', 'Michael', '666884444',

'15-SEP-1952', '975 Fire Oak, Newark, NJ', 'M', 38000, '333445555',5);

INSERT INTO employee VALUES ('Smith', '', 'Jason', '453453453',

'31-JUL-1962', '5631 Rice, Harrison, NJ', 'F', 25000, '333445555',5);

INSERT INTO employee VALUES ('Warren', 'V', 'Samantha', '987987987',

'29-MAR-1959', '908 Dallas, albany, NY', 'F', 25000, '987654321',4);

select e.fname, e.lname, s.fname, s.lname

from employee e, employee s

where e.superssn=s.ssn;

CREATE TABLE department

(dname VARCHAR2(20) NOT NULL,

dnumber NUMBER(2) NOT NULL,

mgrssn CHAR(9) NOT NULL,

mgrstartdate DATE,

CONSTRAINT pk_dnumber PRIMARY KEY (DNUMBER),

CONSTRAINT fk_mgrssn FOREIGN KEY (mgrssn) REFERENCES employee (ssn));

INSERT INTO department VALUES ('Research',5 , '333445555', '22-MAY-1978');

INSERT INTO department VALUES ('Administration',4 , '987654321', '01-JAN-1985');

INSERT INTO department VALUES ('Computing Service', 1, '888665555', '19-JUN-1971');

ALTER TABLE employee ADD (CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department(dnumber));

CREATE TABLE dept_location

(dnumber NUMBER(2) ,

dlocation VARCHAR2(20),

CONSTRAINT pk_num_loc PRIMARY key (dnumber, dlocation),

CONSTRAINT fk_dnumber FOREIGN KEY (dnumber) REFERENCES department(dnumber));

INSERT INTO dept_location VALUES (1,'Fitzelle');

INSERT INTO dept_location VALUES (4,'Hunt union');

INSERT INTO dept_location VALUES (5,'Natza');

INSERT INTO dept_location VALUES (5,'Hunt union');

INSERT INTO dept_location VALUES (5,'Fitzelle');

CREATE TABLE project

(pname VARCHAR2(15),

pnumber NUMBER(2) NOT NULL,

plocation VARCHAR2(15) ,

dnum NUMBER(2),

CONSTRAINT pk_pnumber PRIMARY KEY (PNUMBER),

CONSTRAINT fk_dnum FOREIGN KEY (dnum) REFERENCES department(dnumber));

INSERT INTO project VALUES ('ProductX', 1,'library',5);

INSERT INTO project VALUES ('ProductY', 2,'Fitzelle hall',5);

INSERT INTO project VALUES ('ProductZ', 3,'Library',5);

INSERT INTO project VALUES ('Computerization', 10,'IRC',4);

INSERT INTO project VALUES ('Reorganization', 20,'IRC',1);

INSERT INTO project VALUES ('Newbenefits', 30,'Hunt Union',4);

CREATE TABLE dependent

(essn CHAR(9) NOT NULL,

dependent_name VARCHAR(9) NOT NULL,

sex VARCHAR2(1),

bdate DATE,

relationship VARCHAR(8),

CONSTRAINT pk_essn PRIMARY KEY (essn, dependent_name),

CONSTRAINT fk_essn FOREIGN KEY (essn) REFERENCES employee(ssn) ON DELETE CASCADE);

INSERT INTO dependent VALUES ('333445555','Alice', 'F','05-APR-1976','Daughter');

INSERT INTO dependent VALUES ('333445555','Theodore', 'M','10-OCT-1973','Son');

INSERT INTO dependent VALUES ('333445555','Joy', 'F','03-MAY-1948','Spouse');

INSERT INTO dependent VALUES ('987654321','Abner', 'M','29-FEB-1932','Spouse');

INSERT INTO dependent VALUES ('123456789','John', 'M','01-JAN-1978','Son');

INSERT INTO dependent VALUES ('123456789','Alice', 'M','31-DEC-1978','Daughter');

INSERT INTO dependent VALUES ('123456789','Elizabeth', 'F','05-MAY-1957','Spouse');

CREATE TABLE works_on

(essn CHAR(9) NOT NULL,

pno NUMBER(2) NOT NULL,

hours NUMBER(3,1),

CONSTRAINT pk_essn_pno PRIMARY KEY (essn, pno),

CONSTRAINT fk_pno FOREIGN KEY (pno) REFERENCES project(pnumber));

INSERT INTO works_on VALUES ('123456789',1,32.5);

INSERT INTO works_on VALUES ('123456789',2,7.5);

INSERT INTO works_on VALUES ('666884444',3,40.0);

INSERT INTO works_on VALUES ('453453453',1,20.0);

INSERT INTO works_on VALUES ('453453453',2,20.0);

INSERT INTO works_on VALUES ('333445555',2,10.0);

INSERT INTO works_on VALUES ('333445555',3,10.0);

INSERT INTO works_on VALUES ('333445555',10,10.0);

INSERT INTO works_on VALUES ('333445555',20,10.0);

INSERT INTO works_on VALUES ('999887777',30,30.0);

INSERT INTO works_on VALUES ('999887777',10,10.0);

INSERT INTO works_on VALUES ('987987987',10,35.0);

INSERT INTO works_on VALUES ('987987987',30,5.0);

INSERT INTO works_on VALUES ('987654321',30,20.0);

INSERT INTO works_on VALUES ('987654321',20,15.0);

INSERT INTO works_on VALUES ('888665555',20,null);