HW help for CS 514: I already have all 4 sql scripts in my sql interpreter: http

Question

HW help for CS 514:

I already have all 4 sql scripts in my sql interpreter:

https://mega.nz/#!WoZVmSrb!rd-nvbpF8UJYz7yNhaeebpXnJIcNFT91M_8VjttkSRA

Here is the what I need to do with the tables I created:

1. Make empno the primary key of both empbb02 and infobb02. Make
deptno the primary key for deptbb02. Create an appropriate two
column key for salsbb02. Use the naming convention for
all constraints, in Lab2, that was given in class.

2. Check your primary key for infobb02 by attempting to add a new row
with a duplicate empno.

3. Add a unique constraint for ename in empbb02, using the naming
convention. Then display a suitable set of columns for user_comstraints.

4. The team has decided that, except for the gm and catchers, a player
nickname should not exceed 16 letters. Implement this as a check
constraint. Confirm that this constraint is working.

5. Make empno a foreign key in infobb02, with parent table empbb02

6. Make depno a foreign key in empbb02, with parent table deptbb02

7. Test one of your foreign key constraints to make sure it is working.
Test it in two ways, one of which is attempting to delete a parent
table.
  
8. Display ename, pos, sal, hiredate from empbb02 for all employees not in
department 50. Make the column headers Last Name, Position, Date Hired.
Show dollar signs and commas for salary, and show zero cents for
each salary, without changing the data.

9. Drop the check constraint that you made earlier.

10. The boss makes a new year's resolution to send anniversary cards
once a month to the appropriate employees. Display enames and hiredates
of all players who were hired in the month of January. Your code
should work tomorrow even if you trade two thirds of the team tonight.

11. The boss wants to create a security department with deptno 60. A guy
named Mongo is a one man force, so add him to the dept. He has a perfect
record, so his batting average will be 1000, and his uniform number 007.
Mongo has no first name or nickname, and the hiredate can be given by
SYSDATE. Find his department a good Latin motto, and create values for
other fields with similar creativity, but some discretion. Mongo is not
big on levity.

12. Add a constraint that a department number must be evenly divisible
by 5. Test the constraint.

13. To infobb02 add a column named careeravg, which stands for the
batting average over an entire career (to date if still playing). Pick
3 players on the team and fill in the correct values for career batting
average.

Explanation / Answer

Question 1:

The sql queries are as follows:

1) Make empno the primary key of empbb02

ALTER TABLE empbb02 ADD CONSTRAINT pk_empbb02_empno PRIMARY KEY (EMPNO)

2) Make empno the primary key of infobb02

ALTER TABLE infobb02 ADD CONSTRAINT pk_infobb02_empno PRIMARY KEY (EMPNO)

3) Make deptno the primary key for deptbb02

ALTER TABLE deptbb02 ADD CONSTRAINT pk_deptbb02_deptno PRIMARY KEY (DEPTNO)

4) Two column key for salsbb02

ALTER TABLE salsbb02 ADD CONSTRAINT pk_salsbb02 PRIMARY KEY (GRADE, ROLE)

Question 2:

The first insert sql querie will be:

insert into infobb02 values('121','branch',null,'60',null,null);

The above command will be successfully.

The second insert sql querie will be:

insert into infobb02 values('121','branch',null,'60',null,null);

The above insert command will not work because EMPNO is primary key and it always be unique and not null.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.