Question 01: When Oracle database implemented PL/SQL Language?
Answer: The first version of the PL/SQL language is introduced in Oracle version 6. In oracle Oracle7, released in 1992, introduced PL/SQL stored procedures and triggers.
Question 02: In which version of Oracle RAC is implemented?
Answer: Oracle 9i database introduced Oracle RAC in year 2001. Later Oracle Database 10g introduced grid computing in 2003.
Question 03: What is Oracle database instance?
Answer: An Oracle instance is a set of memory structures that manage different database files. It consists of Shared/System Global Area (SGA) and a set of background processes.
Question 04: Name the Oracle schema object types.
Tables, Indexes, Partitions, Views, Sequences, Dimensions, Synonyms, PL/SQL subprograms and packages.
Question 05: What are the characteristics of Materialized Views?
i) Just like table materialized views contain data and consume storage space.
ii) They can be refreshed when the data in their master tables changes.
iii) They can improve performance of SQL execution when used for query rewrite operations.
iv) The existence of materialize view is transparent to SQL applications and users. So if optimizer sees use of materialize view is more efficient than accessing original table then for that query materialize view will be automatically used.
Question 06: Name the different types of Constraints
NOT NULL, Unique, Primary, Foreign, Check.
Question 07: What are the DDL statements?
DDL statements do structural changes or drop schema objects. For example: CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT.
Question 08: What are the DML statements?
Data manipulation language (DML) statements query or manipulate data in existing schema objects. For example: SELECT, INSERT, UPDATE, MERGE, DELETE, EXPLAIN PLAN, LOCK TABLE.
Question 09: What are the TC statements?
Transaction control statements manage the changes made by DML statements and group DML statements into transactions. For example: COMMIT, ROLLBACK, ROLLBACK TO SAVEPOINT, SAVEPOINT, SET TRANSACTION.
Question 10: What are the Session Control statements?
Session Control Statements dynamically manage the properties of a user session. For example: ALTER SESSION, SET ROLE.
Question 11: What are the System Control statements?
System control statements change the properties of the database instance. The only system control statement is ALTER SYSTEM.
Question 12: What are the Embedded SQL statements?
Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program.
For example:
- Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE).
- Specify a database and connect to it (DECLARE DATABASE, CONNECT).
- Assign variable names (DECLARE STATEMENT).
- Initialize descriptors (DESCRIBE).
- Specify how error and warning conditions are handled (WHENEVER).
- Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE).
- Retrieve data from the database (FETCH).
Showing posts with label Exercise. Show all posts
Showing posts with label Exercise. Show all posts
Monday, August 30, 2010
Saturday, December 5, 2009
Audit and Profile Management Exercises in Oracle
In your lab for this week you are going to work with three different areas and processes within the Oracle Database that can be used to control data security. Each of these three processes has its own distinctive application to providing levels of security. In each case the individual processes deal with either limiting a users access to the database, limiting access to processes within the database, or keeping track of what the user is doing while in the database.
For the lab you will be using the SCOTT user which is already created in your instance. In Step 4 you will also be asked to shutdown you instance, make some edits to the init.ora file for your instance and then restart the instance.
 | L A B S T E P S |  |
| STEP 1: Define a New Profile |
Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times. In this section of the lab we are going to create a custom profile that will then be applied to the SCOTT user.
- To begin, log into your instance as the SYS user.
- Write SQL script that will create a new profile named DBM449_SCOTT_PROFILE that will do the following:
- Limit the number of failed login attempts to 3 in a row.
- Limit the overall connection time to 10 hours (we will give him a little leeway incase he has to work overtime).
- Allow a session to be idle no more than 1 hour.
- Change the password every 60 days.
- Allow the user 3 days to change the password after it expires.
- Not allow a previous password be reused before there have been three password changes.
- Execute your pfile script and verify that the profile has been created by running a query against the DBA_PROFILES view in the data dictionary. Limit your output to ONLY the DBM449_SCOTT_PROFILE parameters.
| STEP 2: Testing the New Profile |
Now that we have a new profile for the SCOTT user we need to verify that it works properly. For obvious reasons there are going to be parts of the profile that we cannot test within the confines of this lab due to time constraints, but we can test to verify that the SCOTT user is being controlled by the profile.
- The first things we need to do is assign the profile to the SCOTT user. While still logged into your instance as the SYS user write and execute the SQL command that will assign the new SBM449_SCOTT_PROFILE profile to the SCOTT user.
- Now log into SCOTT (password is TIGER). Remember that you must supply the database instance name when logging in from the SQL> prompt just as you do when using the login window, i.e. CONN SCOTT/TIGER@DB####.WORLD.
- There are several things that we can test related to the logging in and changing a password so here we go.
- You should now be successfully connect to the SCOTT user. Write the connect command again on this time use an incorrect password. NOTE: you should get a warning message stating that you are no longer connected to Oracle. That is fine, just keep trying to log in.
- Repeat the above process until you get the ORA-28000: the account is lockederror which will indicate that the profile is working here.
- At this point we need to get the account unlocked so you will need to login to your instance as the SYS user and unlock the SCOTT account BUT DO NOT LOG BACK INTO THE SCOTT USER YET.
- Now we can test the password reuse parameter. To do this we must EXPIRE the current password. Write and execute the SQL command to expire the password for the SCOTT user.
- Now log back into the SCOTT user. You should receive a message stating that the password has expired (ORA-28001: the password has expired) and then prompting you to change the password.
- Try to reuse the TIGER password. You should receive the following - ORA-28007: the password cannot be reused.
- Now log into the SCOTT user again and this time change the password to LION to complete this step of the lab.
| STEP 3: Using the PRODUCT_USER_PROFILE table |
As the owner of a schema a user has certain inherited privileges that would allow the user to pass access to his/her own objects on to other users. Often times this can open up data to scrutiny by individuals who probably do not need to have access to it. These types of decisions should always be made by the DBA in charge of the database. One mechanism the DBA has to keeping users from using these inherited privileges is by excluding those commands using the PRODUCT_USER_PROFILE (PUP) table. In this section of the lab we are going to do this to the SCOTT user by setting up the scenario that will prohibit him from giving the user GEORGE (created in lab 2) access to the EMP table.
- To begin, copy the pupbld.sql file from $ORACLE_HOME/rdbms/admin to the C drive of your local computer or place it in the local F drive of your Citrix environment. Once you have downloaded it you will need to open the file and make two edits to the login strings; one at the top and one at the bottom. The login at the top of the script is for the SYSTEM user. The password is already set to MANAGER which is correct. You need to change the reference to the database instance to match your instance name (do not add the AS SYSDBA to this connection string). The other connection string is at the bottom and you also need to change the instance name here to match yours. Once you have made these changes then save the file.
- Now login to your database instance as the SYS user. Run the PUPBLD.SQL script from the SQL> prompt (DO NOT copy and paste the script). Remember that at the end of the script you should be connect as the SYS user. You can test this by issuing a SHOW USER command.
- Now we need to limit SCOTT from being able to use the GRANT command.
- Insert the proper values into the PRODUCT_USER_PROFILE table that will keep the SCOTT user from using the GRANT command. Remember that some of the values in your insert statement must be in upper case and some will need to be in mixed case. Once you have done this then query the table to verify the insert (REMEMBER: you cannot query the table as the SYS user, only as the SYSTEM user).
- Now we need to test our above settings and make sure they are working.
- Connect to the SCOTT user (remember that you changed the password to LION).
- Write and execute the statement that would GRANT the user GEORGE the ability to write a select statement and see the data in the EMP table owned by SCOTT. You should receive the following message - SP2-0544: Command "grant" disabled in Product User Profile.
- This verifies that you have now disabled the ability of the SCOTT user to allow another user to access any of the data in his schema.
| STEP 4: Setting up the Database to use Auditing |
Being able to audit what, when and where people are doing things in the database can be a very enlightening thing for a DBA. It can also be a very important tool in working with Data Security. Oracle provides the ability to do various types of auditing, but it takes some special setting up of the environment for this to work. In this step we are going to make the necessary adjustments to the current Oracle instance so that we can enable auditing and make some tests. If you need to review the processes to be used here then refer to the iLab Manual in week 1.
- First you need to make sure that you are logged into your instance as the SYS user.
- At this point issue a SHUTDOWN IMMEDIATE command to shut down you database instance.
- Once the instance is shut down you need to go into your Citrix Windows Explorer application, find your database instance set of directory folders, drill down to the pfile directory folder and open your init.ora file found in that folder.
- Under the section titled "Security and Auditing" you need to add the parameter AUDIT_TRAIL and set the parameter to DB_EXTENDED. This will allow the SQL_TEXT column of the DBA_AUDIT_OBJECT view to be populated. Save and close the file and then go back to your SQL*Plus session.
- Now using the init.ora file, start your instance back up to an OPEN status. You can do this by issuing a STARTUP PFILE= statement and pointing to your init.ora file.
- Once you have completed this process you are ready to begin setting up the database to audit some activity.
| STEP 5: Creating an Audit Trail |
Oracle permits audit trails to be generated for session login attempts, access to objects, and activity performed on objects. Again using the SCOTT user we are going to set up several scenarios for auditing what SCOTT does while in a session. NOTE: if you need to work through this process several times you can delete the values in the AUD$ base table by issuing the TRUNCATE TABLE AUD$ command while logged in as the SYS user.
- Make sure that you are connected as user SYS.
- Display value of the parameter AUDIT_TRAIL. For the VALUE column you should have a value of DB_EXTENDED.
- Now we can set up auditing to track what goes on in the database.
- Write SQL statements to audit successful and unsuccessful login attempts by SCOTT.
- Write SQL statement to audit any successful INSERT, UPDATE or DELETE performed on table DEPT in scott's schema.
- Now we need to test the audits to verify that they work.
- Log into the SCOTT user (remember that the password is LION) and perform the following:
- write and execute an UPDATE statement that will change the value in the LOC column of the DEPT table to MIAMI where the DEPTNO value is 10.
- Write and execute the INSERT statement that will in insert the following values into DEPT - (50, 'LEGAL', 'HOUSTON').
- Write and execute the DELETE statement that will delete the row from the DEPT table that was just inserted.
- Try to reconnect to the SCOTT user with an invalid password.
- Now connect back to the SYS user.
- While logged into your instance as the SYS user, query the DBA_AUDIT_OBJECT view of the data dictionary for the user name of the account (Not the OS), the object owner, the object name, the action name and the SQL command (text) from the DBA_AUDIT_OBJECT view in the Data Dictionary.
- Did you notice that the entries for successful logon and unsuccessful logon attempts were not there. Now query the user name, action name and return code values in the DBA_AUDIT_SESSION view. You should find that information here.
Related Documents
Basic Oracle Sql Exercise
Oracle Object type Exercises - VARRAY
For this lab you will begin by using the same set of tables that you used for Lab 1 so be sure that you are connected to Oracle as the DBM449_USER user. The objective of this lab will be to create a series of object-relational tables using the SQL*Plus editor that will allow data to be stored in a more "real-world" format. Data for your new tables can be found in the file Lab1_initialization.sql which is in the url http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html. You will need to manipulate the data in various ways, but the file will give you access to the raw data to use.
| L A B S T E P S | |
| STEP 1: Create a table with a column data type |
Modify the design of the COURSE table created in iLab 1 to incorporate the use of the column abstract data type.
- Write and execute the SQL to create an object type called COURSE_OBJ1 that contains the attributes course code and course name. Remember that with abstract objects you must use the / after the CREATE statement to execute it.
- Next, write and execute the SQL to create a table called NEW_COURSE1 that contains COURSE_OBJ1 and applicable original attributes from the original COURSE table. Keep in mind what attributes the new object type COURSE_OBJ1 contains.
- Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_COURSE1.
- Run DESCRIBE command to describe structure of table NEW_COURSE1.
- SET DESCRIBE DEPTH 2 and run DESCRIBE NEW_COURSE1 again.
- Execute a SELECT statement to query the data from the new table. Use the COLUMN column_name FORMAT A## session command to format columns within the table to keep the result set data from wrapping around. Be sure that you properly display data inside the object column. (HINT: When querying attributes of an abstract data type, you must use a correlation variable for the table.)
| STEP 2: Create an object table with a row data type |
Create a second COURSE table, this time as an object table using the row abstract data type.
- Write and execute the SQL to create an object called COURSE_OBJ2 that contains the attributes course code, course name, course date, instructor, and location.
- Write and execute the SQL to create a table called NEW_COURSE2 with each row defined by COURSE_OBJ2.
- Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_COURSE2.
- Execute a SELECT statement to query the data from the new table.
| STEP 3: Create a Varying Array |
Modify the design of the CLIENT table created in iLab 1 to incorporate the use of the Varying Array.
Related Documents
Basic Oracle Sql Exercise
Practice Oracle Joins Examples
Before starting this lab let's assume that you have the following two files Lab1_initialization.sql and 2.PUPBLD.SQL
Lab1_initialization.sqlDROP USER DBM449_USER CASCADE;
CREATE USER DBM449_USER
IDENTIFIED BY DEVRY
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO DBM449_USER;
GRANT CREATE MATERIALIZED VIEW, CREATE DATABASE LINNK TO DBM449_USER;
CONN DBM449_USER/DEVRY@db####.WORLD
DROP TABLE CLIENT;
DROP TABLE COURSE;
DROP TABLE COURSE_ACTIVITY;
DROP TABLE CORP_EXTRACT1;
DROP TABLE CORP_EXTRACT2;
CREATE TABLE CLIENT (
CLIENT_NO CHAR(8) PRIMARY KEY,
CLIENT_COMPANY VARCHAR(35) NOT NULL,
CLIENT_NAME VARCHAR(35) NOT NULL,
CLIENT_EMAIL VARCHAR(35),
CLIENT_PROGRAM CHAR(3) NOT NULL,
CLIENT_SCORE NUMBER NOT NULL);
CREATE TABLE COURSE (
COURSE_CODE CHAR(8)PRIMARY KEY,
COURSE_NAME VARCHAR(35) NOT NULL,
COURSE_DATE DATE NOT NULL,
COURSE_INSTRUCTOR VARCHAR(35) NOT NULL,
COURSE_LOCATION VARCHAR(20) NOT NULL);
CREATE TABLE COURSE_ACTIVITY (
ACTIVITY_CODE CHAR(8) PRIMARY KEY,
CLIENT_NO CHAR(8) NOT NULL,
COURSE_CODE CHAR(8) NOT NULL,
GRADE CHAR(1),
INSTR_NOTES VARCHAR (50));
CREATE TABLE CORP_EXTRACT1 (
EXTRACT_NO CHAR(3) PRIMARY KEY,
CLIENT_NO CHAR(8) NOT NULL,
CLIENT_NAME VARCHAR(35) NOT NULL,
CLIENT_EMAIL VARCHAR(35),
CLIENT_COMPANY VARCHAR(35) NOT NULL,
CLIENT_PROGRAM CHAR(3) NOT NULL,
CLIENT_SCORE NUMBER NOT NULL,
COURSE_NAME VARCHAR(35) NOT NULL,
COURSE_DATE DATE NOT NULL,
COURSE_INSTRUCTOR VARCHAR(35) NOT NULL,
COURSE_LOCATION VARCHAR(20) NOT NULL,
Course_STATUS VARCHAR(10) NOT NULL);
CREATE TABLE CORP_EXTRACT2 (
EXTRACT_NO NUMBER PRIMARY KEY,
CLIENT_NO CHAR(8) NOT NULL,
CLIENT_NAME VARCHAR(45) NOT NULL,
CLIENT_EMAIL VARCHAR(35),
CLIENT_COMPANY VARCHAR(35) NOT NULL,
CLIENT_PROGRAM CHAR(8) NOT NULL,
CLIENT_SCORE NUMBER NOT NULL,
COURSE_NAME VARCHAR(35) NOT NULL,
COURSE_DATE DATE NOT NULL,
COURSE_INSTRUCTOR VARCHAR(35) NOT NULL,
COURSE_LOCATION VARCHAR(20) NOT NULL,
Course_STATUS VARCHAR(10) NOT NULL);
/* Loading data rows */
/* Turn Escape character on */
/* Default escape character "\" */
/* Used to enter special characters (&) */
SET ESCAPE ON;
/* CLIENT rows */
INSERT INTO CLIENT VALUES('C2122542','Bryson, Inc.' ,'Smithson','smithson@bryson.com' ,'DBA',47);
INSERT INTO CLIENT VALUES('C2122356','SuperLoo, Inc.' ,'Flushing','flushing@superloo.com' ,'DBA',38);
INSERT INTO CLIENT VALUES('C2123871','D\&E Supply' ,'Singh' ,'rsingh@desupply.com' ,'EAI',42);
INSERT INTO CLIENT VALUES('C2134452','Gomez Bros.' ,'Ortega' ,'ortega@gomez.com' ,'DBA',39);
INSERT INTO CLIENT VALUES('C2256716','Dome Supply' ,'Smith' ,'smith@dome' ,'ADM',41);
/* COURSE rows */
INSERT INTO COURSE VALUES('DBA12345','DBA 101' ,'03-OCT-2005','Phung' ,'Kaanapali');
INSERT INTO COURSE VALUES('DBA12346','Advanced DBA' ,'23-NOV-2005','Browne' ,'San Mateo');
INSERT INTO COURSE VALUES('EAI12345','EAI Intro' ,'30-NOV-2005','Luss' ,'Danbury');
INSERT INTO COURSE VALUES('DBA12347','DBA 101' ,'08-JAN-2006','Fiorillo' ,'Paramus');
INSERT INTO COURSE VALUES('DBA12348','DBA 101' ,'28-FEB-2006','Majmundar' ,'Racine');
/* COURSE ACTIVITY rows */
INSERT INTO COURSE_ACTIVITY VALUES('A0000001','C2122542','DBA12345','A',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000002','C2122356','DBA12347','F',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000003','C2134452','DBA12345','B',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000004','C2122542','DBA12346','A',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000005','C2123871','EAI12345','A',NULL);
INSERT INTO COURSE_ACTIVITY VALUES('A0000006','C2122356','DBA12345',NULL,NULL);
/* CORP_EXTRACT1 rows */
INSERT INTO CORP_EXTRACT1 VALUES ('001','C2122542','Smithson','smithson@bryson.com','Bryson, Inc.','DBA',47,'EAI Intro','01-MAR-2007','Luss','Hilo','Enrolled');
INSERT INTO CORP_EXTRACT1 VALUES ('002','C2122356','Flushing','flushing@superloo.com','SuperLoo, Inc.','DBA',38,'DBA 101','03-OCT-2005','Luss','Hilo','Dropped');
INSERT INTO CORP_EXTRACT1 VALUES ('003','C2172249','Bizet','gbizet@bryson.com','Bryson, Inc.','EAI',44,'EAI Intro','01-MAR-2007','Luss','Hilo','Enrolled');
COMMIT;
GRANT SELECT ON COURSE TO PUBLIC;
GRANT SELECT ON CLIENT TO PUBLIC;
GRANT SELECT ON COURSE_ACTIVITY TO PUBLIC;
SET ESCAPE OFF;
2.PUPBLD.SQLconn system/manager@db1000.world
drop synonym product_user_profile;
create table sqlplus_product_profile as
select product, userid, attribute, scope, numeric_value, char_value,
date_value from product_user_profile;
drop table product_user_profile;
alter table sqlplus_product_profile add (long_value long);
rem +---------------------------------------+
rem | Create SQLPLUS_PRODUCT_PROFILE from scratch |
rem +---------------------------------------+
create table sqlplus_product_profile
(
product varchar2 (30) not null,
userid varchar2 (30),
attribute varchar2 (240),
scope varchar2 (240),
numeric_value decimal (15,2),
char_value varchar2 (240),
date_value date,
long_value long
);
rem
rem Remove SQL*Plus V3 name for sqlplus_product_profile
rem
drop table product_profile;
rem +------------------------------------------------------------------+
rem | Create the view PRODUCT_PRIVS and grant access to that |
rem +------------------------------------------------------------------+
drop view product_privs;
create view product_privs as
select product, userid, attribute, scope,
numeric_value, char_value, date_value, long_value
from sqlplus_product_profile
where userid = 'PUBLIC' or user like userid;
grant select on product_privs to public;
drop public synonym product_profile;
create public synonym product_profile for system.product_privs;
drop synonym product_user_profile;
create synonym product_user_profile for system.sqlplus_product_profile;
drop public synonym product_user_profile;
create public synonym product_user_profile for system.product_privs;
rem +---------------------------------------------------------------+
rem | CONNECT BACK AS THE SYS USER |
rem +---------------------------------------------------------------+
conn sys/oracle@DB###.world as sysdbaExercise DescriptionMy colleague, Ann Henry, operates a regional training center for a commercial software organization. She created a database to track client progress so she can analyze effectiveness of the certification program. CLIENT, COURSE, and COURSE_ACTIVITY are three of the tables in her database. The CLIENT table contains client name, company, client number, pre-test score, certification program and email address. The COURSE_ACTIVITY table contains client number, course code, grade, and instructor notes. The COURSE table contains the course code, course name, instructor, course date, and location. Although she and her instructors enter much of the data themselves, some of the data are extracted from the corporate database and loaded into her tables.Loading the initial data was easy. For grade entry at the end of each course, a former employee created a data entry form for the instructors. Updating most client information and generating statistics on client progress is not easy because Ann does not know much SQL. For now, she exports the three tables into three spreadsheets. To look up a grade in the COURSE_ACTIVITY spreadsheet, she first has to look up client number in the CLIENT spreadsheet. While this is doable, it is certainly not practical. For statistics, she sorts the data in the COURSE_ACTIVITY spreadsheet using multiple methods to get the numbers she needs.Every month, Ann's database tables need to be refreshed to reflect changes in the corporate database. Ann describes this unpleasant task. She manually compares the contents of newly extracted data from corporate to the data in her spreadsheets, copies in the new values, and then replaces the database contents with the new values.Ann needs our help. Let’s analyze her situation and determine what advanced SQL she could use to make her tasks easier.
L A B O V E R V I E W
Scenario/Summary Here are results from DESCRIBE commands that show structure (columns and their data types) of tables CLIENT and COURSE_ACTIVITY. You may refer to it while constructing your queries. For this lab you will be creating several documents. First, write your queries in Notepad to create a script file that will contain all of the queries asked for in lab steps 4 through 13. You can (and should) test each query as you write it to make sure that it works and is returning the correct data. Once you have all of your queries written then create a SPOOL session and run your entire script file. Be sure that you execute a SET ECHO ON session command before running the file so that both the query and the output will be captured in the SPOOL file. IMPORTANT: If you are using Windows Vista you will need to create a directory on your C: drive to SPOOL your file into. Vista will not allow you to write a file directly to the C: drive. This will give you two files for the lab. The third file will the be the Lab1 Report document found in Doc Sharing. You will need to put your responses to the questions asked in the various lab steps.Now let's begin.
- Which course(s) a specific client has taken
- What grade(s) a specific client has earned in a specific course
- Which clients did not take any courses
- Which courses were not taken by any client
Start Citrix Metaframe. Select SQL Plus and log in to your database instance. Use "sys" as User Name, and "oracle" as the Password. Enter the Host String as "DB9999.world as sysdba" where 9999 is the database number you have been assigned.Download the Lab1_initialization.sql and pupbld.sql files associated with the links to your C: drive or to the F: drive in your Citrix environment. You will need to open each of the files and edit the connection string to match your instance name. Once you have done this then run the pupbld.sql script first (DO NOT copy and paste it) in your SQL*Plus session. The script will create the product_user_profile synonym in the SYSTEM account which will be used each time you log in as a normal user. Next run the lab1_initalization.sql script in your session. The script will create a new user (DBM449_USER) that will be used in various labs in this course. Disregard the DROP TABLE error messages. They occur because the script is designed to work regardless of whether you have already created the tables or not. This way, you may run it if you ever decide to resent the contents of your tables to the original values. When you run the script for the first time, the error messages appear as you attempt to drop tables that do not exist.Once the script has finished you will be logged into the new user and ready to start your lab.You want to verify that everything completed successfully. To do this execute a SELECT * FROM TAB statement to make sure all 5 tables were created and then you can execute a SELECT COUNT(*) FROM statement using each of the table names. You should find the following numbers of records for each table.
L A B S T E P S Join the CLIENT and COURSE_ACTIVITY tables using a FULL OUTER JOIN.
- CLIENT table - 5 rows
- COURSE table - 5 rows
- COURSE_ACTIVITY table - 6 rows
- CORP_EXTRACT1 table - 3 rows
- CORP_EXTRACT2 table - 0 rows
Will the FULL OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.Join the CLIENT and COURSE_ACTIVITY tables using a RIGHT OUTER JOIN.
- Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Will the RIGHT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.Join the CLIENT and COURSE_ACTIVITY tables using a LEFT OUTER JOIN.
- Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Will the LEFT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.Join the CLIENT and COURSE_ACTIVITY tables using a NATURAL JOIN.
- Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Join the CLIENT and COURSE_ACTIVITY tables using a INNER JOIN.
- Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
- Will the NATURAL JOIN be helpful to Ann? Place your response in the lab report document for this step.
Will the INNER JOIN be helpful to Ann? Place your response in the lab report document for this step.Write a conclusion based on the five steps above, which join - if any - should Ann use to populate the spreadsheet that can answer her questions. Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION operator.
- Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION ALL operator.
- Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
- Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
- Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the INTERSECT operator.
- Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
- Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
- Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the MINUS operator.
- Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
- Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
- Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using a subquery with NOT IN operator.
- Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
- Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
- Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.
- Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
- Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
- Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Related DocumentsBasic Oracle Sql Exercise
Subscribe to:
Posts (Atom)