** Edited Q: \"regarding tables can i we do in oracle sql??\" Yes, Oracle SQL sh

Question

** Edited
Q: "regarding tables can i we do in oracle sql??"

Yes, Oracle SQL should work!

Database management/SQL question
(Scroll down for the SPJDatabase)

if exists(Select * from sys.databases where name = 'spjdatabase')
Create DATABASE spjdatabase
GO
USE spjdatabase

CREATE TABLE j (
jno varchar(20),
jname varchar(20),
city varchar(20)
)

CREATE TABLE p (
pno varchar(20),
pname varchar(20),
color varchar(20),
pweight int(11),
city varchar(20)
)

CREATE TABLE s (
sno varchar(20),
sname varchar(20),
sstatus int(11),
city varchar(20)
)

CREATE TABLE spj (
sno varchar(20),
pno varchar(20),
jno varchar(20),
qty int(11)
)

INSERT INTO spjdatabase.s (sno, sname, sstatus, city)
VALUES
('S1', 'Smith', '20', 'London'),
('S2', 'Jones', '10', 'Paris'),
('S3', 'Blake', '30', 'Paris'),
('S4', 'Clark', '20', 'Lodon'),
('S5', 'Adams', '30', 'Athens')

INSERT INTO spjdatabase.p (pno, pname, color, pweight, city)
VALUES
('P1', 'Nut', 'Red', '12', 'London'),
('P2', 'Bold', 'Green', '17', 'Paris'),
('P3', 'Screw', 'Blue', '17', 'Rome'),
('P4', 'Screw', 'Red', '14', 'London'),
('P5', 'Cam', 'Blue', '12', 'Paris'),
('P6', 'Cog', 'Red', '19', 'London')

INSERT INTO spjdatabase.j (jno, jname, city)
VALUES
('J1', 'Sorter', 'Paris'),
('J2', 'Punch', 'Rome'),
('J3', 'Reader', 'Athens'),
('J4', 'Console', 'Athens'),
('J5', 'Collator', 'London'),
('J6', 'Terminal', 'Oslo'),
('J7', 'Tape', 'London')

INSERT INTO spjdatabase.spj (sno, pno, jno, qty)
VALUES
('S1', 'P1', 'J1', '200'),
('S1', 'P1', 'J4', '700'),
('S2', 'P3', 'J1', '400'),
('S2', 'P3', 'J2', '200'),
('S2', 'P3', 'J3', '200'),
('S2', 'P3', 'J4', '500'),
('S2', 'P3', 'J5', '600'),
('S2', 'P3', 'J6', '400'),
('S2', 'P3', 'J7', '800'),
('S2', 'P5', 'J2', '100'),
('S3', 'P3', 'J1', '200'),
('S3', 'P4', 'J2', '500'),
('S4', 'P5', 'J3', '300'),
('S4', 'P5', 'J7', '300'),
('S5', 'P2', 'J2', '200'),
('S5', 'P2', 'J4', '100'),
('S5', 'P5', 'J5', '500'),
('S5', 'P5', 'J7', '100'),
('S5', 'P6', 'J2', '200'),
('S5', 'P1', 'J4', '100'),
('S5', 'P3', 'J4', '200'),
('S5', 'P4', 'J4', '800'),
('S5', 'P5', 'J4', '400'),
('S5', 'P6', 'J4', '500')

-- change in the column names

SELECT sno AS Sno, sname AS SNAME, sstatus AS SSTATUS, city AS CITY FROM S;

SELECT pno AS Pno, pname AS PNAME, color AS COLOR, pweight AS PWEIGHT, city AS CITY FROM P;

SELECT jno AS Jno, jname AS JNAME, city AS CITY FROM J;

SELECT sno AS Sno, pno AS Pno, jno AS Jno, qty AS QTY FROM SPJ;

Write an ANSI SQL script to answer the following questions in the SPJDatabase you created. Do not tailor your answers to the instance implemented. You must use the information and techniques described in class. Name your script YourLastNameHWNo7.sql. Make sure you have comments at the top of your script that includes your name. I will test your script on my computer. Do not create the tables or the database in your script. Remember to place Use SPJDatabase at the top of your script. You will copy you script onto my computer at the beginning of class. You will also turn in a paper copy of your script. If you have multiple pages you must have your name on each page and all corner. You must write your query for the users! Before each query you must put pages stapled together in the upper left hand Selcct 1 as Question l Select 2 as Question2 Select 3 as Question3 The SQJDatabase is described as: suppliers-parts-jobs database S table is the set of all suppliers P table is the set of all parts J table is the set of all jobs, work projects SPJ table is the set of all suppliers who supply the respective parts to the respective jobs in the quantity listed. 1. Get all supplier-number/part-number/project-number triples such that the indicated supplier, part, and project are all colocated. 2. Get all supplier-number/part-number/project-number triples such that the indicated supplier part, and project are not all colocated. 3. Get all supplier-number/part-number/project-number triples such that no two of the indicated supplier, part, and project are colocated. 4. Get part numbers for parts supplied by a supplier in London. 5. Get part numbers for parts supplied by a supplier in London to a project in London.

Explanation / Answer

USE spjdatabase;

Part 1) Join between all the 4 tables is done and in where clause city of supplier , job and parts are checked for colocation.

SELECT s.sno AS Sno, p.pno AS Pno, j.jno AS Jno

FROM s

INNER JOIN spj

ON spj.sno = s.sno

INNER JOIN p

ON p.pno = spj.pno

INNER JOIN j.jno = spj.jno

WHERE s.city = p.city AND p.city = j.city ;

Part 2) Join between all the 4 tables is done and in where clause city of supplier , job and parts are checked for not colocating.

SELECT s.sno AS Sno, p.pno AS Pno, j.jno AS Jno

FROM s

INNER JOIN spj

ON spj.sno = s.sno

INNER JOIN p

ON p.pno = spj.pno

INNER JOIN j.jno = spj.jno

WHERE s.city <> p.city AND p.city <> j.city AND j.city <> s.city;

Part 3)

SELECT s.sno AS Sno, p.pno AS Pno, j.jno AS Jno

FROM s

INNER JOIN spj

ON spj.sno = s.sno

INNER JOIN p

ON p.pno = spj.pno

INNER JOIN j.jno = spj.jno

WHERE s.city <> p.city AND p.city <> j.city;

Part 4) Join between the table s,p,spj is done and in where clause supplier city London is filtered.

SELECT p.pno AS Pno

FROM p

INNER JOIN spj

ON spj.pno = p.pno

INNER JOIN s

ON s.sno = spj.sno

WHERE s.city = ‘London’;

Part 5) Join between the 4 tables is done and in where clause supplier city and job city which are London are filtered.

SELECT p.pno AS Pno

FROM s

INNER JOIN spj

ON spj.sno = s.sno

INNER JOIN p

ON p.pno = spj.pno

INNER JOIN j.jno = spj.jno

WHERE s.city = ‘London’ AND j.city = ‘London’;

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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