Find Jobs
Hire Freelancers

Database Assignment

$30-5000 USD

In Progress
Posted over 20 years ago

$30-5000 USD

Paid on delivery
The S-P-J-SPJ database in this assignment. 1. Table Structures: Table S: s#: VARCHAR2(5) sname: VARCHAR2(20) status: NUMBER(5) city: VARCHAR2(15) Primary key: s# Table P: p#: VARCHAR2(6) pname: VARCHAR2(20) color: ARCHAR2(6) weight: NUMBER(5,1) city: VARCHAR2(15) Primary key: p# 2 Table J: j#: VARCHAR2(4) jname: VARCHAR2(20) city: VARCHAR2(15) Primary key: j# Table SPJ: s#: VARCHAR2(5) p#: VARCHAR2(6) j#: VARCHAR2(4) qty: NUMBER(9) Primary key: (s#,p#,j#) Foreign keys: s# references to column s# in table S p# references to column p# in table P j# references to column j# in table J [login to view URL] data: S S# SNAME STATUS CITY ----------------------------------------- S1 Smith 20 London S2 Jones 10 Paris S3 Blake 30 Paris S4 Clark 20 London S5 Adams 30 Athens P P# PNAME COLOR WEIGHT CITY --------------------------------------------- P1 Nut Red 12.0 London P2 Bolt Green 17.0 Paris P3 Screw Blue 17.0 Rome P4 Screw Red 14.0 London P5 Cam Blue 12.0 Paris P6 Cog Red 19.0 London J J# JNAME CITY -------------------------------- J1 Sorter Paris J2 Display Rome J3 OCR Athens J4 Console Athens J5 Raid London J6 EDS Oslo J7 Tape London SPJ S# P# J# QTY ----------------------------------- 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 P6 J3 300 S4 P6 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 ## Deliverables [login to view URL] Notes: -All the questions are the queries against the S-P-J-SPJ database.-Execute a SQL script to create the tables and load the data into the tables in the S-P-J-SPJ database. The SQL script should contain correct CREATE TABLE, INSERT, and COMMIT statements. Q1. Print all SNAME, PNAME pairs such that the indicated supplier is in Paris and the indicated part is in London and the indicated supplier ships the indicated part to a project in Paris. Q2. Print all S#s such that the indicated supplier supplies a Paris project with a red part and also supplies a non-Paris project with a red part. Write this query in two ways: a) as a FLAT SELECT statement. b) as a NESTED SELECT statement that does not contain any JOIN_CLAUSEs. Q3. Print the J#s of projects that are not supplied with any part that is available from a Paris supplier. Q4. Print the pairs of J#s such that the indicated projects are supplied by a common supplier and are supplied with a common part (This common part is not necessarily supplied by the same/common supplier).Note: In Q4, do NOT print any records of the form (x,x). In addition, if (x,y) is printed do NOT print (y,x). Q5. Print the P#s of parts that are supplied by at least one Paris supplier but are not supplied to any London project. 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request. 3) Complete ownership and distribution copyrights to all work purchased. Use the "set echo on" and "spool" commands to generate the following desired output to turn in: for each query, a print-out of each SELECT statement followed by the output from that SELECT statement. ## Platform windows and unix
Project ID: 2954998

About the project

20 proposals
Remote project
Active 21 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
Awarded to:
User Avatar
See private message.
$17 USD in 3 days
4.6 (148 reviews)
6.2
6.2
20 freelancers are bidding on average $35 USD for this job
User Avatar
See private message.
$25.50 USD in 3 days
5.0 (765 reviews)
7.0
7.0
User Avatar
See private message.
$25.50 USD in 3 days
4.9 (144 reviews)
6.9
6.9
User Avatar
See private message.
$8.50 USD in 3 days
4.8 (23 reviews)
6.1
6.1
User Avatar
See private message.
$21.25 USD in 3 days
4.7 (61 reviews)
5.2
5.2
User Avatar
See private message.
$42.50 USD in 3 days
4.6 (60 reviews)
4.5
4.5
User Avatar
See private message.
$29.75 USD in 3 days
4.9 (36 reviews)
3.8
3.8
User Avatar
See private message.
$12.75 USD in 3 days
4.9 (10 reviews)
3.8
3.8
User Avatar
See private message.
$20.40 USD in 3 days
4.9 (10 reviews)
3.4
3.4
User Avatar
See private message.
$21.25 USD in 3 days
4.6 (5 reviews)
3.4
3.4
User Avatar
See private message.
$17 USD in 3 days
4.9 (26 reviews)
2.9
2.9
User Avatar
See private message.
$21.25 USD in 3 days
4.9 (7 reviews)
2.2
2.2
User Avatar
See private message.
$255 USD in 3 days
5.0 (11 reviews)
2.1
2.1
User Avatar
See private message.
$29.75 USD in 3 days
5.0 (7 reviews)
2.0
2.0
User Avatar
See private message.
$17 USD in 3 days
4.3 (3 reviews)
0.0
0.0
User Avatar
See private message.
$12.75 USD in 3 days
5.0 (1 review)
0.0
0.0
User Avatar
See private message.
$34 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
See private message.
$85 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
See private message.
$17 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
See private message.
$13.60 USD in 3 days
0.0 (0 reviews)
0.3
0.3
User Avatar
See private message.
$8.50 USD in 3 days
0.0 (0 reviews)
0.0
0.0

About the client

Flag of UNITED STATES
United States
4.7
16
Member since Apr 19, 2003

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.