I need help with College Assignment for SQL
I have been give two SQL files and told to answer questions based on the Table in the file
Questions that need to be answered (List of questions in Requirements)
Example Questions
List all of the Primary Key Columns defined for each Table
List all of the Foreign Keys for each table? And each of those Foreign Key which Parent
Maximum I can pay $20
Instructions
Download and execute following files in your SQL Developer screen
Files : CityJail_Oracle.sql , Book_Order_Oracle.sql , Book_Order_ReportingNeeds.png and City_Jail_ReportingNeeds.png
The Files will be provided
When you open the file CityJail file , there must be CREATE TABLE section to create all tables and after that ALTER TABLE to add CONSTRAINTS.
Prep work: In this part of the project , students needs to understand how a Database physical structure is being build , Rather than just executing above script… Students must understand what they are running to build CityJail database system
Answer below questions only based on CityJail_Oracle.sql file
List all of the Primary Key Columns defined for each Table
List all of the Foreign Keys for each table? And each of those Foreign Key which Parent table they are referencing.
Is there any CHECK Constraint defined? If Yes on Which table
=============
Section B ( 50 pnts)
Prepare below reports for City Jail management Officer… Find solution for all 5 items below
Look for City City_Jail_RportingNeeds.png to answer questions
===================
Section C (20 pnts)
Choose 2 questions from below list and answer them based on BookOrder management database
Look for City Book_Order_Reports.png to answer questions
Provide the answers for the question on text file(SQL queries) so i can test the result
http://www35.zippyshare.com/v/YOqOJXak/file.html
http://www35.zippyshare.com/v/HhKMm51n/file.html
http://www35.zippyshare.com/v/hhAlmk8X/file.html
http://www35.zippyshare.com/v/38qZ5y0J/file.html
hello,
i can provide complete solution for your assignment noted here with fully functional query script as per your requirements.
for any query, feel free to ask.
wish you good luck with the project.
good day,
i have experienced coding with oracle db before and i believe i can help you with your assignment. looking forward. thank you.
best regards,
maranatha b.
level 5 user/affiliate
| Match | Count | SQL | Script |
| SELECT * FROM wanttobuy A, categories_wanttobuy B, seoclerks.members C WHERE A.wantid='49135' AND A.category=B.CATID AND A.USERID=C.USERID | 1 | SELECT A.*, B.name, B.seo, C.username, C.userlevel, C.footer_code, C.rating,
C.ratingcount, C.googleplus_profile, C.total_recommendations, C.USERID, C.profilepicture, C.average_response_time, C.lastlogin, C.addtime
FROM wanttobuy A, categories_wanttobuy B, seoclerks.members C
WHERE A.wantid='49135'
AND A.category=B.CATID AND A.USERID=C.USERID | /opt/clerks-staging/docroot/viewwant.php 163 () () |
| SELECT * FROM ratings_buyer WHERE USERID=60062 | 1 | SELECT sum(good) as thumbup, sum(bad) as thumbdown
FROM ratings_buyer WHERE USERID=60062 | /opt/clerks-staging/docroot/include/functions/main.php 19523 GetRatingsAsBuyerTotal() () |
| SELECT * FROM wanttobuy A, categories_wanttobuy B WHERE A.wantid != 49135 AND A.category=B.CATID AND A.active=1 AND (A.tags like '%Database Sql Oracle%' ) LIMIT 4 | 1 | SELECT A.wantid, A.title, A.p1, A.maxprice, B.name, B.seo
FROM wanttobuy A,
categories_wanttobuy B
WHERE A.wantid != 49135
AND A.category=B.CATID
AND A.active=1
AND (A.tags like '%Database Sql Oracle%' ) LIMIT 4 | /opt/clerks-staging/docroot/viewwant.php 551 () () |
| SELECT * FROM wanttobuy A, categories_wanttobuy B WHERE A.wantid != 49135 AND A.category=B.CATID AND A.active=1 AND A.USERID=60062 LIMIT 4 | 1 | SELECT A.wantid, A.title, A.p1, A.maxprice, B.name, B.seo
FROM wanttobuy A, categories_wanttobuy B
WHERE A.wantid != 49135
AND A.category=B.CATID
AND A.active=1
AND A.USERID=60062
LIMIT 4 | /opt/clerks-staging/docroot/viewwant.php 568 () () |
| SELECT * FROM wantcomments a, seoclerks.members b WHERE a.PID='49135' AND a.USERID=b.USERID and b.status='1' AND a.status=1 order by a.CID asc | 1 | SELECT a.comment, a.USERID, a.CID, a.time_added, a.istranslated, a.parentid, b.username, b.profilepicture
FROM wantcomments a, seoclerks.members b
WHERE a.PID='49135' AND a.USERID=b.USERID and b.status='1' AND a.status=1
order by a.CID asc | /opt/clerks-staging/docroot/viewwant.php 835 () () |
| UPDATE wanttobuy SET viewcount = viewcount + 1 WHERE wantid='49135' | 1 | UPDATE wanttobuy SET viewcount = viewcount + 1 WHERE wantid='49135' | /opt/clerks-staging/docroot/include/functions/main.php 1729 update_wantviewcount() () |
| SELECT * FROM wantbids as w, seoclerks.members AS m WHERE w.active=1 AND m.USERID=w.USERID AND m.status=1 AND w.USERID>0 AND w.wantid='49135' ORDER BY w.bidid ASC | 1 | SELECT w.bidid, w.PID, w.comment, w.title, w.pricepublic, w.USERID, w.adjustedprice, w.ip, m.profilepicture
FROM wantbids as w,
seoclerks.members AS m
WHERE w.active=1
AND m.USERID=w.USERID
AND m.status=1
AND w.USERID>0
AND w.wantid='49135'
ORDER BY w.bidid ASC
| /opt/clerks-staging/docroot/viewwant.php 1192 () () |
| SELECT * FROM seoclerks.members as m WHERE m.USERID=525439 | 1 | SELECT m.username, m.skills, m.userlevel, m.ip, m.total_recommendations
FROM seoclerks.members as m
WHERE m.USERID=525439 | /opt/clerks-staging/docroot/viewwant.php 1258 () () |
| SELECT * FROM seoclerks.members as m WHERE m.USERID=8619 | 1 | SELECT m.username, m.skills, m.userlevel, m.ip, m.total_recommendations
FROM seoclerks.members as m
WHERE m.USERID=8619 | /opt/clerks-staging/docroot/viewwant.php 1258 () () |
| SELECT * FROM categories_wanttobuy | 1 | SELECT * FROM categories_wanttobuy | /opt/clerks-staging/docroot/libraries/adodb5/adodb.inc.php 1899 CacheExecute() parseRedundantQueriesCache() |
| SELECT * FROM posts WHERE USERID=60062 | 1 | SELECT sum(positive_ratings) as thumbup,
sum(negative_ratings) as thumbdown
FROM posts
WHERE USERID=60062 | /opt/clerks-staging/docroot/libraries/adodb5/adodb.inc.php 1899 CacheExecute() GetBuyerRatingsTotal() |
| SELECT * FROM categories | 1 | SELECT * FROM categories | /opt/clerks-staging/docroot/libraries/adodb5/adodb.inc.php 1899 CacheExecute() parseRedundantQueriesCache() |
| select * from categories_software order by name asc | 1 | select * from categories_software order by name asc | /opt/clerks-staging/docroot/libraries/adodb5/adodb.inc.php 1899 CacheExecute() insert_GetSoftwareCategories() |
| select * from categories_wanttobuy order by name asc | 1 | select * from categories_wanttobuy order by name asc | /opt/clerks-staging/docroot/libraries/adodb5/adodb.inc.php 1899 CacheExecute() insert_get_wantcategories() |
| select * from categories_wanttotrade order by name asc | 1 | select * from categories_wanttotrade order by name asc | /opt/clerks-staging/docroot/libraries/adodb5/adodb.inc.php 1899 CacheExecute() insert_get_tradecategories() |
| SELECT * FROM seoclerks.members WHERE USERID='525439' | 1 | SELECT profilepicture FROM seoclerks.members WHERE USERID='525439' | /opt/clerks-staging/docroot/libraries/adodb5/adodb.inc.php 1899 CacheExecute() getUserProfileImage() |
| SELECT * FROM seoclerks.members WHERE USERID='8619' | 1 | SELECT profilepicture FROM seoclerks.members WHERE USERID='8619' | /opt/clerks-staging/docroot/libraries/adodb5/adodb.inc.php 1899 CacheExecute() getUserProfileImage() |
| SELECT * FROM seoclerks.members WHERE USERID='60062' | 1 | SELECT profile_data FROM seoclerks.members WHERE USERID='60062' | /opt/clerks-staging/docroot/include/functions/main.php 1927 getUserBackground() include() |
| count(*) | sql1 | error_msg |
|---|
| Load | Count | SQL | Max | Min |
