Directions for Exercises 5.3-5.25: For the schema that follows, write the adiate

Question

Directions for Exercises 5.3-5.25: For the schema that follows, write the adiated commands in SQL. Figure 5.11 shows the DDL for creating dese tables. It shows that departmentName is a foreign key in the CREATE TABLE Dept departmentName VARCHAR2(15), mgrld NUMBER(6), Figure 5.11 DDL and Insert Statements for Wo Project-Assign Exar 5 RAINT Dept _departName_pk PRIMARY KEY (departmentName); REATE TABLE Worker( empld NUMBER(6), lastlame VARCHAR2/20) NOT NULL isName VARCHAR2(15) NOT NULL, departmentName VARCHAR2(15), birthDate DATE, hireDate DATE, salary NUMBER(82), CONSTRAINT Worker empld_pk PRIMARY KEY (empld), CONSTRAINT Worker depa ept(departmentName)): rtname. fk FOREIGN KEY (departmentName) REFERENCES

Explanation / Answer

5.3

Select lastname,firstName from Worker where departmentName = 'Accounting';

5.4

Select firstname,lastName from Worker inner join Assign on Worker.empId = Assign.empId where Assign.projNo = 1001 order by firstname,lastName;

5.5

Select firstName,lastName from Worker where departmentName = 'Research' and salary = (Select min(salary) from Worker);

5.6

Select * from Project where budget = (Select max(budget) from Project);

5.7

Select firstName,lastName,departmentName from Worker inner join Assign on Worker.empId = Assign.empId where Assign.projNo = 1019;

5.8

Select firstName,lastName,rating from Worker inner join Assign on Worker.empId = Assign.empId inner join Project on Assign.projNo = Project.projNo where projMgrId = (Select empId from Worker where firstName = 'Michael' and lastName = 'Burns');

5.9

Create view ProjWorker as Select projNo,projName,empId,firstName,lastName from Worker inner join Assign on Worker.empId = Assign.empId inner join Project on Assign.projNo = Project.projNo ;

5.10

Select projNo,projName from ProjWorker where empId = 110;

5.11

Insert into Worker(empId,lastName,firstName,department) values(1999,'Smith','Jack','Research');

5.12

Update Assign set hoursAssignment = 10 where hoursAssignment = 20 and empId = 110 and projNo = 1019;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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