Working With Additional Database Objects
Required skills: SQL, Database, Security, Automation, Oracle
There is a lot more to a database than just tables and the data in them. A very important part of database security has to do with using and understanding Database Views. Being able to enhance the efficiency of the database tables by using additional indexes is also a very important aspect of database development. Finally, being able to create and use Database Sequences to automate some of the sequential processes in the database can not only save time but improve efficiency. In Lab 7, you will have a chance to work with all three of these areas.
General Lab Information and Considerations
This lab will utilize the same set of tables found in the script file (LeeBooks.SQL) that were used for Labs 4 through 6.
Each query in the script file you will create, must be numbered (use either –1 or REM 1 comments for numbering) and in order. The SQL for the following exercises should be written using notepad and run in SQL*Plus. Read each problem carefully and follow the directions as stated.
A Clean Script File:
A script file is meant to be like a program. The file can be run every time the code needs to be executed without having to retype the code again each time. For this reason, it is important that there are no errors in the code inside the file. You can go back and forth between notepad and Oracle when creating your script file to check your queries and verify if they work or not. However, you do not want to create your final output file until after you have verified that everything in your script is correct by running it in its entirety at least once and viewing the output. Once this has been done, you can create your final output file, with echo on to create the document, you can turn in with your lab. Remember in using a Spool Session, you must type "SPOOL OFF" at the SQL> PROMPT after your Script stops spooling to capture all of your data!
Lab Do's and Don't's
Do Not include the LEEBOOKS.SQL as part of your lab script.
Do use Notepad to write your query script file.
Do Not write your queries in Word.
Do test each query before moving on to the next.
Do Not include extra queries for a problem unless the problem explicitly asks for more than one query.
Do test your queries before creating your final output file.
Do Not turn in a scrip file that has queries with errors.
Do number each query using either --1 or REM 1 comment notation.
Do Not start your query on the same line as the comment.
Do remember to check your final output and script file for accuracy.
Do Not turn in your lab without first checking your output file to verify that it is correct.
Things to keep in mind:
If you are not sure of the table names in your user schema, you can use the following select statement to list them.
SELECT * FROM TAB;
If you want to know the name of the columns in a particular table, you can use the following command to list them.
DESC
Making a script file containing a series of describe statements for each table and then spooling the output will give you a listing of all the tables with column names.
Be sure to review and verify your final output when you are finished. Do Not assume anything.
Write queries for each of the stated problems in the steps below that will return a result set of data to satisfy the requirements. When finished, your script file should have a total of 13 queries and your resulting output file should show both the query and result set for each.
Deliverables
The deliverable for this lab will include:
Your script file with the seven queries and oneexplanation in it. Be sure your name, course number, and lab number are in a comment area at the top of your file.
An output file created using SET ECHO ON showing both the SQL code and the results. Be sure that you include your answer to question #3 as a comment in this file.
Both documents are to be zipped into a single file before submitting to the iLab Dropbox for Week 7.
L A B S T E P S
STEP 1:
Create a simple view named CUST_VIEW using the book_customer table that will display the customer number, first and last name, and the state for every customer currently in the database. Now insert the following data into the book_customer TABLE using an INSERT statement. (Do not use the View for this insert.) CUSTOMERID - 1021, FIRSTNAME – EDWARD, LASTNAME – BLAKE, STATE – TX. Now query your view and display the new record.
STEP 2:
Create a complex view named CUST_ORDER that will list the customer number, last name, and state from the BOOK_CUSTOMER table, in addition to the order number and order date from the BOOK_ORDER table. Insert the following data into this view (use the view for the insert statement): CUSTOMERID - 1022, LASTNAME – smith, STATE – KS, ORDERID - 1021, and ORDERDATE – 10-OCT-2004.
STEP 3:
In your own words, explain why the insert statement for the view you created in Step 2 did not work.
STEP 4:
Create a sequence that can be used to assign a publisher ID number to a new publisher. Define the sequence to start with seven, increment by two, and stop at 1000. Name the sequence PUBNUM_SEQ.
STEP 5:
Insert two new publishers into the PUBLISHER table, one named Double Week with a contact name of Jennifer Close at 800-959-6321, and the second one named Specific House with a contact name of Freddie Farmore at 866-825-3200. Use your new sequence to create the PUBID for each record. Now, query your PUBLISHER table to see your two new records.
STEP 6:
Using a single query, query the PUBNUM_SEQ to determine what both the current sequence number is and the next sequence number will be.
STEP 7:
Create a unique index on the combined columns ORDERID and CUSTOMERID in the BOOK_ORDER table. Give the index a name of BOOK_ORDER_IDX.
STEP 8:
Determine how many objects you currently own in your schema by querying the USER_OBJECTS view in the Data Dictionary. Your result set should list the different object types that you find and include a count by object type.
General Lab Information and Considerations
This lab will utilize the same set of tables found in the script file (LeeBooks.SQL) that were used for Labs 4 through 6.
Each query in the script file you will create, must be numbered (use either –1 or REM 1 comments for numbering) and in order. The SQL for the following exercises should be written using notepad and run in SQL*Plus. Read each problem carefully and follow the directions as stated.
A Clean Script File:
A script file is meant to be like a program. The file can be run every time the code needs to be executed without having to retype the code again each time. For this reason, it is important that there are no errors in the code inside the file. You can go back and forth between notepad and Oracle when creating your script file to check your queries and verify if they work or not. However, you do not want to create your final output file until after you have verified that everything in your script is correct by running it in its entirety at least once and viewing the output. Once this has been done, you can create your final output file, with echo on to create the document, you can turn in with your lab. Remember in using a Spool Session, you must type "SPOOL OFF" at the SQL> PROMPT after your Script stops spooling to capture all of your data!
Lab Do's and Don't's
Do Not include the LEEBOOKS.SQL as part of your lab script.
Do use Notepad to write your query script file.
Do Not write your queries in Word.
Do test each query before moving on to the next.
Do Not include extra queries for a problem unless the problem explicitly asks for more than one query.
Do test your queries before creating your final output file.
Do Not turn in a scrip file that has queries with errors.
Do number each query using either --1 or REM 1 comment notation.
Do Not start your query on the same line as the comment.
Do remember to check your final output and script file for accuracy.
Do Not turn in your lab without first checking your output file to verify that it is correct.
Things to keep in mind:
If you are not sure of the table names in your user schema, you can use the following select statement to list them.
SELECT * FROM TAB;
If you want to know the name of the columns in a particular table, you can use the following command to list them.
DESC
Making a script file containing a series of describe statements for each table and then spooling the output will give you a listing of all the tables with column names.
Be sure to review and verify your final output when you are finished. Do Not assume anything.
Write queries for each of the stated problems in the steps below that will return a result set of data to satisfy the requirements. When finished, your script file should have a total of 13 queries and your resulting output file should show both the query and result set for each.
Deliverables
The deliverable for this lab will include:
Your script file with the seven queries and oneexplanation in it. Be sure your name, course number, and lab number are in a comment area at the top of your file.
An output file created using SET ECHO ON showing both the SQL code and the results. Be sure that you include your answer to question #3 as a comment in this file.
Both documents are to be zipped into a single file before submitting to the iLab Dropbox for Week 7.
L A B S T E P S
STEP 1:
Create a simple view named CUST_VIEW using the book_customer table that will display the customer number, first and last name, and the state for every customer currently in the database. Now insert the following data into the book_customer TABLE using an INSERT statement. (Do not use the View for this insert.) CUSTOMERID - 1021, FIRSTNAME – EDWARD, LASTNAME – BLAKE, STATE – TX. Now query your view and display the new record.
STEP 2:
Create a complex view named CUST_ORDER that will list the customer number, last name, and state from the BOOK_CUSTOMER table, in addition to the order number and order date from the BOOK_ORDER table. Insert the following data into this view (use the view for the insert statement): CUSTOMERID - 1022, LASTNAME – smith, STATE – KS, ORDERID - 1021, and ORDERDATE – 10-OCT-2004.
STEP 3:
In your own words, explain why the insert statement for the view you created in Step 2 did not work.
STEP 4:
Create a sequence that can be used to assign a publisher ID number to a new publisher. Define the sequence to start with seven, increment by two, and stop at 1000. Name the sequence PUBNUM_SEQ.
STEP 5:
Insert two new publishers into the PUBLISHER table, one named Double Week with a contact name of Jennifer Close at 800-959-6321, and the second one named Specific House with a contact name of Freddie Farmore at 866-825-3200. Use your new sequence to create the PUBID for each record. Now, query your PUBLISHER table to see your two new records.
STEP 6:
Using a single query, query the PUBNUM_SEQ to determine what both the current sequence number is and the next sequence number will be.
STEP 7:
Create a unique index on the combined columns ORDERID and CUSTOMERID in the BOOK_ORDER table. Give the index a name of BOOK_ORDER_IDX.
STEP 8:
Determine how many objects you currently own in your schema by querying the USER_OBJECTS view in the Data Dictionary. Your result set should list the different object types that you find and include a count by object type.
Posted In:SQLSecurityOracle |
Related projects:
Php Expert With Mysql Database Knowledge 2
ttached breaking down each problem.
I need an Expert with high-level of understanding to fix these issues. The custom code has over 100,000 lines and has 7 levels of array. PMB me for discussion. Please read the attached document.
Additional Info (Added 4/18/2011 at 10:00 EST)...System Message: This is a reposting of project PHP Expert with MySQL Database Knowledge (1302723875).
I need an Expert with high-level of understanding to fix these issues. The custom code has over 100,000 lines and has 7 levels of array. PMB me for discussion. Please read the attached document.
Additional Info (Added 4/18/2011 at 10:00 EST)...System Message: This is a reposting of project PHP Expert with MySQL Database Knowledge (1302723875).
Php Expert With Mysql Database Knowledge
I have a custom php script with Mysql database and it has many bugs. I have found many things not working with the script. Some are minor and others require custom php/mysql programming skills since they are not complete. I have made a document attached breaking down each problem.
I need an Expert with high-level of understanding to fix these issues. The custom code has over 100,000 lines and has 7 levels of array. PMB me for discussion. Please read the attached document.
I need an Expert with high-level of understanding to fix these issues. The custom code has over 100,000 lines and has 7 levels of array. PMB me for discussion. Please read the attached document.
Website Redesign With Complete Database and Modules
Additional files submitted:
Proposal+-+Data+selling+portal+NEW.pdf
Simple Database Needed
r an immediate turn around on this. I already have a large scale filemaker database created, but that is for my business and is not web based and is to advanced for what I am looking for here.
Additional work in connection with this database will be available at a furture date depending on speed and accuracy of this project whom ever it is awarded to.
If you have any questions or already have a similar database then please contact me.
Good Luck Bidding.
Additional work in connection with this database will be available at a furture date depending on speed and accuracy of this project whom ever it is awarded to.
If you have any questions or already have a similar database then please contact me.
Good Luck Bidding.
Create a website with a database
have seperate login per member.
I need to be able to adjust the website myself.
But also I need to be able to change and add members in the database myself.
I have no knowlegde att all of websites.
The website will be a logistics website.
The website needs to be secured, so no one can copy any information.
I would appreciate a high google ranking.
I would like moving objects/pictures on the website.
I need to be able to adjust the website myself.
But also I need to be able to change and add members in the database myself.
I have no knowlegde att all of websites.
The website will be a logistics website.
The website needs to be secured, so no one can copy any information.
I would appreciate a high google ranking.
I would like moving objects/pictures on the website.
Java JDBC Connection with Derby Database
><eAnnotations source="UDP">
<details key="ibm.optim.isCommandLineRequest" value="true"/>
</eAnnotations>
<pointAndShootState/>
More information will be provided on awarding the project. I have very clear instructions from my side
Please feel free to contact directly, if anyone is interested. Since it is a small project, I will be able to pay only 100 USD.
<details key="ibm.optim.isCommandLineRequest" value="true"/>
</eAnnotations>
<pointAndShootState/>
More information will be provided on awarding the project. I have very clear instructions from my side
Please feel free to contact directly, if anyone is interested. Since it is a small project, I will be able to pay only 100 USD.
Working with RRD Tool, Zenoss and WHMCS
n=right style="padding-left: 20px; width: 320px">
Additional files submitted: (Files are only available for logged in users)
ZenossJSONAPI.zip
Additional files submitted: (Files are only available for logged in users)
ZenossJSONAPI.zip
Php Expert With Mysql Database Knowledge
I have a custom php script with Mysql database and it has many bugs. I have found 7 things not working with the script. Some are minor and others require custom php/mysql programming skills. I have made a document attached breaking down each problem.
I need an Expert with high-level of understanding to fix these issues. The custom code has over 100,000 lines and has 7 levels of array. PMB me for discussion. Please read the attached document.
I need an Expert with high-level of understanding to fix these issues. The custom code has over 100,000 lines and has 7 levels of array. PMB me for discussion. Please read the attached document.
Server Management API Control Panel working with OVH API
l be particularly useful.
The way I have written this project request is quite specific as to how I want it to work. If you have any better ideas for the way that it should run or in the coding of it then I will be more than happy for you to use them.
There is no real set budget for this product, so please bid appropriately for the service that is required.
Thanks for reading.
The way I have written this project request is quite specific as to how I want it to work. If you have any better ideas for the way that it should run or in the coding of it then I will be more than happy for you to use them.
There is no real set budget for this product, so please bid appropriately for the service that is required.
Thanks for reading.
Simple Database Needed
se created, but that is for my business and is not web based and is to advanced for what I am looking for here.
Additional work in connection with this database will be available at a furture date depending on speed and accuracy of this project whom ever it is awarded to.
If you have any questions or already have a similar database then please contact me.
Good Luck Bidding.
Additional work in connection with this database will be available at a furture date depending on speed and accuracy of this project whom ever it is awarded to.
If you have any questions or already have a similar database then please contact me.
Good Luck Bidding.
Asterisk broadcast caller with webbased database
identify account from caller id
8a. plays welcome message prompts to cancel request or make changes
8b. if not canceled calls, texts, & emails contacts and tell them have message waiting
8c. prompts to repeat message or to conference in on original incoming call.
Changes Menu - The initial account changes option menu.
9a. enter pin
9b. change pin
9c. update your broadcast message
9d. update add or delete contacts
9e. close account
8a. plays welcome message prompts to cancel request or make changes
8b. if not canceled calls, texts, & emails contacts and tell them have message waiting
8c. prompts to repeat message or to conference in on original incoming call.
Changes Menu - The initial account changes option menu.
9a. enter pin
9b. change pin
9c. update your broadcast message
9d. update add or delete contacts
9e. close account
Working with databases in vb
exe and will be named Sentto.mdb . I need the following subs. I need the code to connect to the database. Then i need a sub made that will add an email address to the database. I also need a sub that will check the database for a certain email address i specify and will let me know if that email was found in the database or not found in the database. These are pretty basic functions to do with a database i just dont have any experience working with vb6 and databases.
Site member pay with Amember database by xocarl
br/>
E: We also have message forums. New site paying members must be signed up with username and password for site of course, and ALSO automatically registered to message forums under this same username and password. If you do not understand this clarification if full please e-mail me.
F: I have a poll on my website using Telerik software web building tools. I need it personalized, it is already installed on site but not personalized.
E: We also have message forums. New site paying members must be signed up with username and password for site of course, and ALSO automatically registered to message forums under this same username and password. If you do not understand this clarification if full please e-mail me.
F: I have a poll on my website using Telerik software web building tools. I need it personalized, it is already installed on site but not personalized.
JAVA & JSP Trainer
472;The Expression Language and the JSTL
JDBC Training Outline (oracle , mysql, Sybase or any other db)
Re-Introducing JDBC
Database concepts review
Key JDBC classes and the java.sql package
Connecting to databases
Working with a database connection
Ad-Hoc SQL queries with Statement and PreparedStatement
JDBC transactions
JDBC design patterns
JDBC Training Outline (oracle , mysql, Sybase or any other db)
Re-Introducing JDBC
Database concepts review
Key JDBC classes and the java.sql package
Connecting to databases
Working with a database connection
Ad-Hoc SQL queries with Statement and PreparedStatement
JDBC transactions
JDBC design patterns
EtherPad lite (need experience working with etherpadlite)
Implementing word, excel and power point functionality in Etherpad lite need this project done extremely fast less the 30 days preferred. so called zoho or google docs like functionality. need to have experience working with node.js,and etherpad in general.
Need a JS plugin working with J1.7.x and JS 2.6.x
Hello,
I need this plugin:
http://joomlaextensions.latitudweb.com/products/js-fia.html
working with Jomsocial 2.6.x and Joomla 1.7.x.
I will send you the plugin, also, if you need, the jomsocial release.
Best regards
noelle
I need this plugin:
http://joomlaextensions.latitudweb.com/products/js-fia.html
working with Jomsocial 2.6.x and Joomla 1.7.x.
I will send you the plugin, also, if you need, the jomsocial release.
Best regards
noelle
Working with Files in Java
eproduce file1 from the remaining parts) and all the files stored is maintained in a LIST.
2.Load button when clicked
shows all the files stored on the disk using LIST and when selected an item from LIST should be able to display the original file
i.e The LIST show all the files that are stored in RAID-5 arch on the disk. when selected a file from LIST that file is generated from the RAID-5 parts of the file and displayed.
2.Load button when clicked
shows all the files stored on the disk using LIST and when selected an item from LIST should be able to display the original file
i.e The LIST show all the files that are stored in RAID-5 arch on the disk. when selected a file from LIST that file is generated from the RAID-5 parts of the file and displayed.
Online food ordering shop working with GPRS printer Urgent!
un Smoothly and very up to date in terms of today's online ordering systems,
I would like to see examples of your work if you have done something similar and would like to review the quality i believe this would help me choose who to go with.
I ONLY WANT THE BEST IN BOTH DESIGN AND DEVELOPMENT With fresh design concept that is up to date with today's website.
I look forward to hearing from you all.
Thank You
I would like to see examples of your work if you have done something similar and would like to review the quality i believe this would help me choose who to go with.
I ONLY WANT THE BEST IN BOTH DESIGN AND DEVELOPMENT With fresh design concept that is up to date with today's website.
I look forward to hearing from you all.
Thank You
Delphi class for working with Google Plus
illustration of class working you need to write a little test program.
For working with HTTP/HTTPS must use library Synapse.
So, class must:
1. Authorize using email and password
2. Add the man (using his ID) in the Circle
3. Delete the man (using his ID) from the Circle
For working with HTTP/HTTPS must use library Synapse.
So, class must:
1. Authorize using email and password
2. Add the man (using his ID) in the Circle
3. Delete the man (using his ID) from the Circle
Script to be combined
OpenX JavaScript tag */-->
Additional files submitted: (Files are only available for logged in users)
booknow+page.GIF
Additional files submitted: (Files are only available for logged in users)
booknow+page.GIF
