Oracle

Need Help in Oracle Assignment?
We can help you if you are having difficulty with your Oracle Assignment. Just email your Oracle Assignment at admin@assignmentcache.com.
We provide help for students all over the world in Oracle Assignment.

Items 1 to 10 of 63 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

Grid  List 

Set Ascending Direction
  1. ITSE 2309 LAB 2 More SQL Queries and Modification

    ITSE 2309 LAB 2 More SQL Queries and Modification

    Regular Price: $12.00

    Special Price: $10.00

    ITSE 2309 LAB 2 More SQL Queries and Modification


    Oracle 11g SQL–Chapters- 3,6,8,9,11,12,
    You will continuing using items created in Lab 1


    Lab 2a -- Problems 1–4,
    1. For each customer, list each stock item ordered,
    1) the manufacturer,
    2) the quantity ordered, and
    3) the total price paid.
    Include the following columns in the order given below:
    - From Customer Table: Company
    - From Stock Table: Description
    - From the Manufact Table: Manu_Name
    - From the Items Table: Quantity, Total Price
    Order the output by Company and Description.
    Submit/hand in Output from SQL query


    2. List all orders with a shipping date between December 25, 1999 and January 5, 2000
    Include
    1) the Order Number,
    2) Order Date,
    3) Customer company name, and
    4) Shipping Date.
    Order by
    Customer Company Name and Order Number.
    Submit/hand in Output from SQL query


    3. Count the number of customers who do not have any orders placed.
    Submit/hand in Output from SQL query


    4. List all customers –
    I) Who are ordering equipment whose description begins with 'tennis' or 'volleyball'.
    II )Include
    1) Customer number,
    2) Stock number, and
    3) Description.
    Submit/hand in Output from SQL query
    Do not repeat any rows.


    Lab 2b Problems 5, 6, 7 and 8
    5. Use the following SQL CREATE commands to CREATE the following tables in your
    CREATE TABLE Professor
    (Prof_ID NUMBER(3) Constraint pk_Professor Primary Key,
    Prof_Lname VARCHAR2(15) NOT NULL,
    Prof_Hiredate DATE,
    Prof_Sal NUMBER(8,2),
    Prof_Dept CHAR(6),
    );
    CREATE TABLE Student
    (Stu_ID NUMBER(4) Constraint pk_Student Primary Key,
    Stu_Lname VARCHAR2(15) NOT NULL,
    Stu_Major CHAR(6),
    Stu_CredHrs NUMBER(4),
    Stu_GradePts NUMBER(5),
    Prof_ID NUMBER(3),
    CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)
    REFERENCES Professor
    );
    Submit/Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables created.


    6. Insert the following data into the tables created above using SQL INSERT commands.
    Professor Table:
    Prof_ID Prof_Lname Prof_Hiredate Prof_Sal Prof_Dept
    123 Hilbert 20-MAY-1992 58000.00 MATH
    243 Newell 15-JUL-1997 65500.00 CMPSCI
    389 Lessing 04-APR-1988 40250.00 ENG
    Student Table:
    Stu_ID Stu_Lname Stu_Major Stu_CredHrs Stu_GradePts Prof_ID
    2001 Parker CMPSCI 52 160 243
    2166 Smith ENG 30 75 389
    3200 Garcia MATH 62 248 123
    4520 Smith CMPSCI 45 157 NULL
    BE SURE TO ISSUE A COMMIT AFTER TABLE MODIFICATION COMMANDS HAVE BEEN RUN SUCCESSFULLY.
    Submit a
    Listing of each INSERT command,
    The systems response and the resulting tables after the INSERTS are completed
    (Example: SELECT * FROM Student;).


    7. Perform the following SQL DELETE statements. Be sure to do them in order.
    Issue a COMMIT command after all DELETEs have run.
    a. Try to delete Professor 389. What message do you get? ___________________________
    b. Delete Student 2166.
    c. Now Delete Professor 389. Explain why the first attempt in a. was unsuccessful, and this time the DELETE was successful.
    Submit/hand in : A listing of the DELETE statements.
    The answers to questions a. b. and c.
    A listing of the two tables after the deletes have run.


    8. Perform the following UPDATE commands.
    Issue a COMMIT command after all UPDATEs have run.
    a. Replace the value of the Prof_ID for Student 4520 with 243.
    b. Add 10% to the salary for each professor
    Submit/hand in : A listing of the UPDATE statements
    A listing of the two tables after the UPDATEs have run.

    Learn More
  2. CMIS420 PROJECT 2 Mail-Order Database DML and DDL statements

    CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database

    Regular Price: $25.00

    Special Price: $20.00

    CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database


    Overview:
    Use SQL, PL/SQL, and Triggers to design and create a Mail-Order Database System. Please create your own data for testing purpose. Use the attached file "Project 2 Tables" as a guide to creating the tables. You should pre-populate the PARTS, CUSTOMERS, EMPLOYEE and ZIPCODES tables.


    Due Date:
    Check the due date in Syllabus for the exact date for this assignment. No project will be accepted after the due date.


    Deliverables:
    Turn in all SQL scripts in the form of a SQL script files. The script files should include,


    1. A script file containing all the DML and DDL statements. That is, the SQL used to create the tables and sequence and the SQL to pre-populate or insert records in the tables. Name this file XXX_PROJ2.sql, where XXX are you intials.
    2. A file containing the PL/SQL package (Specification and Body) that provides the functionality listed in the requirements below. Name this file XXX_PROJ2.pkg, where XXX are you initials.
    3. A file containing the database triggers. Name this file XXX_PROJ2.trg, where XXX are you initials.
    4. Finally, provide a test SQL*PLUS routine (PL/SQL anonymous block) that will test the PL/SQL functionality developed. Name this file XXX_PROJ2_tst.sql, where XXX are your initials.


    You should submit your assignment through WebTycho as you did for previous assignments.
    Use winzip or any zip software to package the four (4) files into one file called XXX_project2.zip, where XXX are your initials.


    Requirements:
    The Mail-Order Database consists of the following tables and attributes. Please ensure that all constraints are created when creating the tables. All constraints other than NOT NULL constraints must be named.
    1. EMPLOYEE(ENO, ENAME, ZIP, HDATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    2. PARTS(PNO, PNAME, QOH, PRICE, REORDER_LEVEL, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    3. CUSTOMERS(CNO, CNAME, STREET, ZIP, PHONE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    4. ORDERS(ONO, CNO, ENO, RECEIVED, SHIPPED, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    5. ODETAILS(ONO, PNO, QTY, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    6. ZIPCODES(ZIP, CITY)
    7. ORDERS_ERRORS(TRANSACTION_DATE, ONO, MESSAGE)
    8. ODETAILS_ERRORS(TRANSACTION_DATE, ONO, PNO, MESSAGE)
    9. RESTOCK(TRANSACTION_DATE, PNO)


    • The EMPLOYEE table contains information about the employees of the company. The ENO (Employee Number) attribute is the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
    • The PARTS table keeps a record of the inventory of the company. The record for each part includes its number (PNO) and name (PNAME) as well as the quantity on hand (QOH), the unit price (PRICE) and the reorder level (REORDER_LEVEL). PNO is the primary key for this table.
    • The CUSTOMERS table contains information about the customers of the mail-order company. Each customer is assigned a customer number (CNO), which serves as the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
    • The ORDERS table contains information about the orders placed by customers, the employee who took the orders, and the dates the orders were received and shipped. Order number (ONO) is the primary key. The Customer number (CNO) attribute is a foreign key referring to the CUSTOMERS table, and the ENO attribute is a foreign key referring to the EMPLOYEES table.
    • The ODETAILS table contains information about the various parts order by the customer within a particular order. The combination of ONO and PNO attributes forms the primary key. The ONO attribute is a foreign key referring to the ORDERS table, and the PNO attribute is a foreign key referring to the PARTS relation.
    • The ZIPCODES table maintains information about the zip codes for various cities. ZIP is the primary key.
    • The ORDERS_ERRORS table contains information about any errors that occurred when an order is processed. Transaction date is the date of the transaction.
    • The ODETAILS_ERRORS table contains information about all errors that occur when processing an order detail. Transaction date is the date of the transaction.
    • The RESTOCK table contains information about all parts (PNO) that are below the reorder level. Transaction date is the date of the transaction.


    1. Write a package called Process_Orders to process customer orders. This package should contain four procedures and a function, namely;
    Add_order. This procedure takes as input customer number, employee number, and received date and tries to insert a new row in the Orders table. If the received date is null, the current date is used. The shipped date is left as null. If any errors occur, an entry is made in the Orders_errors table. A sequence called Order_number_seq should be used to populate the order number (ONO) column.
    Add_order_details. This procedure receives as input an order number, part number, and quantity and attempts to add a row to the Odetails table. If the quantity on hand for the part is less than what is ordered, an error message is sent to the Odetails_errors table. Otherwise, the part is sold by subtracting the quantity ordered from the quantity on hand for this part. A check is also made for the reorder level. If the updated quantity for the part is below the reorder level, an entry is made to the Restock table.
    Ship_order. This procedure takes as input an order number and a shipped date and tries to update the shipped value for the order. If the shipped date is null, the current date is used. If any errors occur, an entry is made in the Orders_errors table.
    Delete_order. This procedure takes as input an order number and tries to delete records from both the Orders and Odetails tables that match this order number. If any errors occur or there is no record that matches this order number, an entry is made in the Orders_errors table.
    Total_emp_sales. This function takes as input an employee number. It computes and returns the total sales for that employee.


    2. Create triggers on the PARTS, ORDERS, and ODETAILS tables to populate the CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY columns when an insert or update is made. Use SYSDATE and the pseudo column USER to populate these columns.


    3. Write a trigger that fires when a row in the Orders table is updated or deleted. The trigger should record the dropped order records in another table called deleted_orders. The deleted_orders table should also contain a date attribute that keeps track of the date and time the action (update or delete) was performed. This date is quite different from the CREATED_DATE and UPDATED_DATE from the Order table. Do not copy these dates to the deleted_order table. Please include the table creation script for the deleted_orders table in the script file.


    4. Create a sequence called order_number_seq that will be used to populate the order number (ONO) column.


    5. Write a PL/SQL anonymous block to test the above.

    Learn More
  3. MSCD640 Assignment 1

    MSCD640 Assignment 1

    Regular Price: $60.00

    Special Price: $50.00

    MSCD640 Assignment 1


    Part 2: Hands-on exercise


    You’ve just been through an eight week intensive training for database administration. You are now a support DBA. You’ve been assigned to handle tickets for a database environment. Handle each of the trouble tickets below. Capture all of your work as either screen shots or spooled output. You’ll need to hand in this output to the shift manager at the end of your shift that verifies you successfully handled each ticket.


    ---------------------------------------------------------------------------------
    Ticket #1: Production DBA has reported that the database you’re assigned to is not in archive log mode. You need to enable archive logging in your assigned database.



    Verify:
    SQL> archive log list;
    ---------------------------------------------------------------------------------
    Ticket #2: User has reported that they’re receiving an open cursors error from the application. You need to verify the database initialization setting of open_cursors and double the setting (for example, if it’s 50, change it to 100).



    Verify:
    SQL> show parameter open_cursors;
    ---------------------------------------------------------------------------------
    Ticket #3: Production DBA notices abnormal wait times related to the online redo logs. The DBA recommends that you immediately add one online redo log to your database. Make sure you size the new online redo log the same size as the current logs.



    Verify:
    SQL> select group#, member from v$logfile;
    ---------------------------------------------------------------------------------
    Ticket #4: Development DBA has filed a ticket asking for an additional tablespace. Create a tablespace, named AP_DATA size its datafile at 20M.



    Verify:
    SQL> select tablespace_name from dba_tablespaces;
    ---------------------------------------------------------------------------------
    Ticket #5: Development team has filed a ticket asking for a new user account. Create a user named AP_MGMT with the password of f00b0r and assign it the following:
    • Assign it the default tablespace of AP_DATA
    • Assign it the temporary tablespace of TEMP
    • Grant it connect, create table, and create sequence
    • Alter the user to have a quota of unlimited on the AP_DATA


    Verify:
    SQL> connect ap_mgmt/f00b0r
    SQL> select * from user_users;
    ---------------------------------------------------------------------------------
    Ticket # 6: Development team is requesting that you create two tables in the AP_DATA account: EMP and DEPT. Here are the requirements.


    • The DEPT table needs 2 columns: dept_id, dept_name
    • The dept_id is a number.
    • The dept_id is the primary key.
    • The dept_name is varchar2(30)
    • The dept_name needs a check constraint that limits it to the following values (‘HR’, ‘IT’, ‘SECURITY’)


    • The EMP table needs 3 columns: emp_id, emp_name, dept_id
    • The emp_id column is a number.
    • The emp_id column is the primary key.
    • The emp_name column is varchar2(30)
    • The dept_id column is a number.
    • The EMP(dept_id) column needs a foreign key constraint defined that references the DEPT(dept_id) parent table.


    Verify:
    SQL> desc emp;
    SQL> desc dept;
    ---------------------------------------------------------------------------------
    Ticket #7: The application team needs you to seed the EMP and DEPT tables with data:


    insert into dept (dept_id, dept_name) values(1, ‘HR’);
    insert into dept (dept_id, dept_name) values(2, ’IT’);
    insert into dept (dept_id, dept_name) values(3, ‘SECURITY’);
    insert into dept (dept_id, dept_name) values(4, ‘WAREHOUSE’);


    insert into emp (emp_id, emp_name, dept_id) values (50, ‘GEORGE’, 2);
    insert into emp (emp_id, emp_name, dept_id) values (20, ‘JANE’, 1);
    insert into emp (emp_id, emp_name, dept_id) values (30, ‘JOHN’, 3);


    Verify:
    SQL> select * from emp;
    SQL> select * from dept;
    ---------------------------------------------------------------------------------
    Ticket #8: After the EMP and DEPT tables have been created and seeded (prior two steps). An end user is reporting strange locking issues with the DEPT and EMP tables. You need to run a script that validates whether or not the EMP table has an index created on the foreign key column.


    ---------------------------------------------------------------------------------
    Ticket #9: Development team is reporting that they now require an index be added to the EMP table on the dept_id column. Make its tablespace APP_DATA.


    Verify:
    SQL> select index_name, column_name from user_ind_columns;
    ---------------------------------------------------------------------------------
    Ticket #10: The storage manager is concerned about disk space. The manager wants a report showing how much space all of the tablespaces in your database are consuming. The manager would like to see space free and space consumed in the report.



    ---------------------------------------------------------------------------------
    Ticket #11: The security department is reporting a massive security breach!! They are requesting that you lock all users in your database except for SYS.



    ---------------------------------------------------------------------------------
    Ticket #12: The application team wants a report showing the space used for each table and index in the AP_MGMT account:



    ---------------------------------------------------------------------------------
    Ticket #13: The production DBAs have requested that archive logging be disabled for your database. Disable archive logging in your database:



    Verify:
    SQL> archive log list;
    ---------------------------------------------------------------------------------
    Ticket #14: With a bit of sadness, you realize your shift will end soon. Before you leave, your team leader would like you to write few sentences indicating whether or not your training prepared you adequately for the job. Do you have any recommendations for your team leader?


     

    Learn More
  4. MIS 562 Week 7 Homework

    MIS 562 Week 7 Homework Roles and Privileges

    Regular Price: $15.00

    Special Price: $12.00

    MIS 562 Week 7 Homework Roles and Privileges


    Part 1
    Using the following Data Dictionary views write the statements that will perform the following actions. Be sure to test your statements. (Do not use SELECT *)
    ROLE_ROLE_PRIVS
    ROLE_SYS_PRIVS
    ROLE_TAB_PRIVS
    USER_ROLE_PRIVS
    USER_SYS_PRIVS
    USER_TAB_PRIVS


    Question SQL statement or Answer
    1. Determine what privileges your account has been granted through a role. (10 points)


    2. Determine what system privileges your account has been granted. (10 points)


    3.Execute the following statement then determine what table privileges your account has been granted. (15 points)
    Grant select on student to public;


    4. Determine what system privileges the DVONLINE role has. (10 points)

    5. Analyze the following query and write a description of the output it produces. (15 points)
    SELECT COUNT(DECODE(SIGN(total_capacity-20), -1, 1, 0, 1)) "<=20",
    COUNT(DECODE(SIGN(total_capacity-21), 0, 1, -1, NULL,
    DECODE(SIGN(total_capacity-30), -1, 1)))"21-30",
    COUNT(DECODE(SIGN(total_capacity-30), 1, 1)) "31+"
    FROM
    (SELECT SUM(capacity) total_capacity, course_no
    FROM section
    GROUP BY course_no)


    6. Determine the top three zip codes where most of the students live. Use an analytical function. The query will product 10 rows. (10 points)



    Part 2
    Analyze the file from Doc Share called utlpwdmg.sql and analyze the code in this file. Write a paragraph that describes what the function performs. What are the inputs parameters, the output parameter and what does the function do? (30 points)

    Learn More
  5. Oracle Database to Track Students Grade ERD

    Oracle Database to Track Students Grade

    Regular Price: $15.00

    Special Price: $12.00

    Oracle Database to Track Students Grade


    Build an Oracle database to track students grade in a class:
    The database tracks
    *Student Information
    *Instructor Information
    *Class information
    *Grading Breakdown
    *Students' Grades


    Different queries will show
    *List of students with semester grades
    *List of students who received an A
    *Average grades
    *Above average grades

    Learn More
  6. MIS 562 Week 5 Homework Query Optimization

    MIS 562 Week 5 Homework Query Optimization

    Regular Price: $20.00

    Special Price: $15.00

    MIS 562 Week 5 Homework Query Optimization


    Using the student schema from week 2, provide answers to the following questions.


    Question
    SQL statement or Answer
    1. Generate statistics for the student, enrollment, grade, and zipcode tables (15 pts)


    2. Write a query that performs a join, a subquery, a correlated subquery using the student, enrollment, grade, and zipcode tables. Execute each query to show that it produces the same results. (15 pts)


    3. Produce an autotrace output and explain plan for each query. (10 pts)


    4. Analyze the results and state which performs best and why. Write an analysis of what operations are being performed for each query. Determine which query is the most efficient and explain why (10 pts)

    Learn More
  7. CMIS 320 Lab 4 Homework 4 Choose Oracle datatypes

    CMIS 320 Lab 4 Homework 4 Choose Oracle datatypes

    Regular Price: $8.00

    Special Price: $5.00

    CMIS 320 Lab 4 Homework 4 Choose Oracle datatypes


    Instructions
    Choose Oracle datatypes for the following attributes from a normalized relation including:
    Vendor(VendorID, Address, ContactName)
    Item (ItemID, Description)
    PriceQuote(VendorID, ItemID, Price)
    Describe why you selected the datatypes for each attribute.

    Learn More
  8. CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script

    CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script


    Homework Submission Requirements:
    Homework should be submitted as an attached file in your assignment folder. Text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
    You must name your file to include your first initial of your first name and last name.
    Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.


    Part I Data Modeling
    You are working for country club with thousands of members. You have been tasked with designing a database to keep track of the members and their guests.
    The club keeps track of mail and telephone contact information, name and membership number. When a member joins the club they can become a social member with pool, racket ball and weight room privileges or golf member which includes all of the social member privileges plus access to the golf course.
    Develop, document and design an EER for this situation.
    Submit your word document no later than the due date.


    Part II Lab 3
    In lab 2, you will learn how to edit and run a SQL script in your schema in NOVA Oracle database. You also continue to learn simple SQL commands, such as select and insert ...


    Build and execute SQL script
    In this lab, we learn how to write a SQL script that can be run on NOVA Oracle database to execute the SQL commands included in the file. You are provided with SQL Statements and sqlplus commands to add into the script. What you are requested to do is to reorganize the commands in proper order to avoid runtime errors.
    How to write a script: The script file is a text file which contains executable commands. For SQL script, it contains SQL statements which can be executed against a proper database. For our SQL script that runs against Oracle database. Therefore, we include some of Oracle specific SQLPlus commands that help us make output clear and understandable and allow us to save the output file.
    Get the txt file below that contains SQL and SQLPlus commands. Spool commands are for getting the execution results. Set echo on or off are for getting executed commands printed on the screen before the execution result. Others are all SQLcommands. These commands should work as they are without modification.
    Download lab3.txt
    There are a few simple suggested rules that can make your script robust.
    Use remark, rem, to add comment or reminder in the script anything followed rem is not executed.
    Spool on command should be the first command and followed
    Set echo on should be before any execution commands begin.
    Set echo off and spool off at the end of the file.
    For SQL DDL commands. Drop the table before create it. this gives you clean database to begin with. The table created last should be dropped first.
    For SQL DML, delete the records first before insert them into the table. This allow you begin with empty table. delete the records from child table first if primary key and foreign key constraints exist between the two tables. However, insert into parent table first then insert into child table. Don't forget the child record needs a refernce key value from the parent table. Always end with commit command to save the data.
    you can use vi text editor to edit the script on NOVA or use a text editor at local to create and edit the script and upload to Nova using sFTP. The script MUST be saved with .sql extension in order for it to be run in SQLPLus
    After you successfully create and executed your script. Transfer your output file back and Submit it through assignment for grading.

    Learn More
  9. CMIS 320 Lab 2 Homework 2 Data modeling and Sample database

    CMIS 320 Lab 2 Homework 2 Data modeling and Sample database

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 320 Lab 2 Homework 2 Data modeling and Sample database


    Homework Submission Requirements:
    Homework should be submitted as an attached file in your assignment folder. text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
    You must name your file to include your first initial of your first name and last name.
    Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.


    Part I Data Modeling
    For each of the following descriptions:
    a. A piano manufacturer wants to track all pianos it makes. Each piano has a unique serial number and a manufacturing completion date. Each instrument represents exactly one piano model, all of which have an identification number and model. The company produces thousands of pianos of a certain model, and the design is specified before any single piano exists.
    b. A vendor builds multiple types of tablet computers. Each has a type identification number and a name. The key specifications for each type include amount of storage and display type. The company uses multiple processor types, exactly one of which is used for a specific tablet type. The same processor can be used in multiple types of tablets. Each processor has a manufacturer and a manufacturer's unique code that identifies it.
    perform the following tasks:
    1. Identify the degree and cardinalities of the relationship.
    2. Express the relationships graphically with an E-R diagram.
    Document your work into a single, well-organized, well-written word document and submit no later than due date.


    Part II Lab 2
    In lab 2, you will learn how to run a SQL script to create a sample database in your schema in NOVA Oracle database. You also start to learn simple SQL commands, such as select and insert


    Click here to open Lab 2 instruction page


    This part of lab is to learn to run a SQL script on NOVA Oracle database server, and spool screen output to a text file. These techniques will be used when you work on your project 1 and 2. Download script file. This is sql script file containing completed sql commands to create tables and constraints and populate data to each table. The database you create is called JustLee Book Store. The code should be bug free.
    Download lab2.sql script
    Start spool command and run the script:
    Log on NOVA, make a dir named scripts using command mkdir scripts
    Then cd to newly created script directory
    Upload your script to scripts directory using FTP
    (I downloaded a free FTP client CORE FTP LE) and use it to upload and download file)
    Now you have a newly create dir and your script is uploaded. You are ready to run your script.
    Using SQL*Plus, log on NOVA Oracle database with your account
    At SQL prompt,Type the following command to turn on spool function: SQL>spool spool01.lst
    spool01.lst is the spool file name
    Then use this command to run the script: SQL>@lab2.sql
    The command will execute all the SQL commands inside the script and output will be spooled to spool01.lst
    In the script, there are drop table statement as well. When first run, these commands will fail because the tables are not created yet.
    You can run the script multiple times without any problems.
    When you finish the execution, issue command SQL>spool off
    This command will end spool function and save output to the file you defined
    When you have done all of these. Use FTP get spool01.lst to your local computer and submit it as part of homework 2
    You should know how to perform the following tasks using sqlplus on NOVA Oracle database after you complete this lab:
    Using FTP to upload or download a file (scripts...)
    Using sqlplus to log on to NOVA Oracle database
    Using sqlplus command, spool to output screen output to a text file and transfer the file back to your local PC.
    Understand the steps taken place when you perform FTP file transfer and Spooling your output.
    Test the database you create and learn SQL queries.
    You need to spool the execution results of the following commands.
    Do a database query: select table_name from user_tables; USER_TABLES is a database system view that allow users to see the tables they created in their own schema.
    You should be able to list the tables you just created
    use this query: select * from "table_name from the above query"; you can retrieve the data in these tables one a time
    use this query: select count(*) from "table_name from the above query"; you can retrieve the record counts in these tables one a time
    Use SQL*Plus command "desc books" to check columns and their datatypes. And insert one record into the table. Use SQL command "commit" to save the record into your database.
    When you have done all of these. Use FTP get spooled file to your local computer and submit it as part of homework 2

    Learn More
  10. Joan Casteel Oracle 11g SQL Chapters 13 Multiple Choice Questions Solution

    Joan Casteel Oracle 11g SQL Chapters 13 Multiple Choice Questions Solution

    Regular Price: $12.00

    Special Price: $10.00

    Joan Casteel Oracle 11g SQL Chapters 13 Multiple Choice Questions Solution


    To answer the following questions, refer to the tables in the JustLee Books database.
    Questions 1–7 are based on successful execution of the following statement:
    CREATE VIEW changeaddress
    AS SELECT customer#, lastname, firstname, order#,
    shipstreet, shipcity, shipstate, shipzip
    FROM customers JOIN orders USING (customer#)
    WHERE shipdate IS NULL
    WITH CHECK OPTION;


    1. Which of the following statements is correct?
    a. No DML operations can be performed on the CHANGEADDRESS view.
    b. The CHANGEADDRESS view is a simple view.
    c. The CHANGEADDRESS view is a complex view.
    d. The CHANGEADDRESS view is an inline view.


    2. Assuming there’s only a primary key, and FOREIGN KEY constraints exist on the underlying tables, which of the following commands returns an error message?
    a. UPDATE changeaddress SET shipstreet = '958 ELM ROAD' WHERE customer# = 1020;
    b. INSERT INTO changeaddress VALUES (9999, 'LAST', 'FIRST', 9999, '123 HERE AVE', 'MYTOWN', 'AA', 99999);
    c. DELETE FROM changeaddress WHERE customer# = 1020;
    d. all of the above
    e. only a and b
    f. only a and c
    g. none of the above


    3. Which of the following is the key-preserved table for the CHANGEADDRESS view?
    a. CUSTOMERS table
    b. ORDERS table
    c. Both tables together serve as a composite key-preserved table.
    d. none of the above


    4. Which of the following columns serves as the primary key for the CHANGEADDRESS view?
    a. Customer#
    b. Lastname
    c. Firstname
    d. Order#
    e. Shipstreet


    5. If a record is deleted from the CHANGEADDRESS view based on the Customer# column, the customer information is then deleted from which underlying table?
    a. CUSTOMERS
    b. ORDERS
    c. CUSTOMERS and ORDERS
    d. Neither—the DELETE command can’t be used on the CHANGEADDRESS view.


    6. Which of the following is correct?
    a. ROWNUM can’t be used with the view because it isn’t included in the results the subquery returns.
    b. The view is a simple view because it doesn’t include a group function or a GROUP BY clause.
    c. The data in the view can’t be displayed in descending order by customer number because an ORDER BY clause isn’t allowed when working with views.
    d. all of the above
    e. none of the above


    7. Assuming one of the orders has shipped, which of the following is true?
    a. The CHANGEADDRESS view can’t be used to update an order’s ship date because of the WITH CHECK OPTION constraint.
    b. The CHANGEADDRESS view can’t be used to update an order’s ship date because the Shipdate column isn’t included in the view.
    c. The CHANGEADDRESS view can’t be used to update an order’s ship date because the ORDERS table is not the key-preserved table.
    d. The CHANGEADDRESS view can’t be used to update an order’s ship date because the UPDATE command can’t be used on data in the view.


    Questions 8–12 are based on successful execution of the following command:
    CREATE VIEW changename
    AS SELECT customer#, lastname, firstname
    FROM customers
    WITH CHECK OPTION;
    Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.


    8. Which of the following is a correct statement?
    a. No DML operations can be performed on the CHANGENAME view.
    b. The CHANGENAME view is a simple view.
    c. The CHANGENAME view is a complex view.
    d. The CHANGENAME view is an inline view.


    9. Which of the following columns serves as the primary key for the CHANGENAME view?
    a. Customer#
    b. Lastname
    c. Firstname
    d. The view doesn’t have or need a primary key.


    10. Which of the following DML operations could never be used on the CHANGENAME view?
    a. INSERT
    b. UPDATE
    c. DELETE
    d. All of the above are valid DML operations for the CHANGENAME view.


    11. The INSERT command can’t be used with the CHANGENAME view because:
    a. A key-preserved table isn’t included in the view.
    b. The view was created with the WITH CHECK OPTION constraint.
    c. The inserted record couldn’t be accessed by the view.
    d. None of the above—an INSERT command can be used on the table as long as the PRIMARY KEY constraint isn’t violated.


    12. If the CHANGENAME view needs to include the customer’s zip code as a means of verifying the change (that is, to authenticate the user), which of the following is true?
    a. The CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.
    b. The ALTER VIEW . . . ADD COLUMN command can be used to add the necessary column to the existing view.
    c. The CHANGENAME view can be dropped, and then the CREATE VIEW command can be used to re-create the view with the necessary column included in the new view.
    d. All of the above can be performed to include the customer’s zip code in the view.
    e. Only a and b include the customer’s zip code in the view.
    f. Only a and c include the customer’s zip code in the view.
    g. None of the above includes the customer’s zip code in the view.


    13. Which of the following DML operations can’t be performed on a view containing a group function?
    a. INSERT
    b. UPDATE
    c. DELETE
    d. All of the above can be performed on a view containing a group function.
    e. None of the above can be performed on a view containing a group function.


    14. You can’t perform any DML operations on which of the following?
    a. views created with the WITH READ ONLY option
    b. views that include the DISTINCT keyword
    c. views that include a GROUP BY clause
    d. All of the above allow DML operations.
    e. None of the above allow DML operations.


    15. A TOP-N analysis is performed by determining the rows with:
    a. the highest ROWNUM values
    b. a ROWNUM value greater than or equal to N
    c. the lowest ROWNUM values
    d. a ROWNUM value less than or equal to N


    16. To assign names to the columns in a view, you can do which of the following?
    a. Assign aliases in the subquery, and the aliases are used for the column names.
    b. Use the ALTER VIEW command to change column names.
    c. Assign names for up to three columns in the CREATE VIEW clause before the subquery is listed in the AS clause.
    d. None of the above—columns can’t be assigned names for a view; they must keep their original names.


    17. Which of the following is correct?
    a. The ORDER BY clause can’t be used in the subquery of a CREATE VIEW command.
    b. The ORDER BY clause can’t be used in an inline view.
    c. The DISTINCT keyword can’t be used in an inline view.
    d. The WITH READ ONLY option must be used with an inline view.


    18. If you try to add a row to a complex view that includes a GROUP BY clause, you get which of the following error messages?
    a. virtual column not allowed here
    b. data manipulation operation not legal on this view
    c. cannot map to a column in a non-key-preserved table
    d. None of the above—no error message is returned.


    19. A simple view can contain which of the following?
    a. data from one or more tables
    b. an expression
    c. a GROUP BY clause for data retrieved from one table
    d. five columns from one table
    e. all of the above
    f. none of the above


    20. A complex view can contain which of the following?
    a. data from one or more tables
    b. an expression
    c. a GROUP BY clause for data retrieved from one table
    d. five columns from one table
    e. all of the above
    f. none of the above

    Learn More

Items 1 to 10 of 63 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

Grid  List 

Set Ascending Direction