Query Analysis and Optimizing in Oracle

Query Analysis and Optimizing in Oracle

Introduction to Oracle 9i: SQL

  
Oracle 9i DBA Track-4 Exams
  
OCA
  
Exam #1Z0-007 Introduction to Oracle 9i: SQL
  
Exam #1Z0-031- Oracle 9i Database Fundamentals I
  
OCP
  
Exam #1Z0-032- Oracle 9i Database Fundamentals II
  
Exam #1Z0-033- Oracle 9i Database: Performance Tuning
  
Introduction to Oracle 9i: SQL
  
Writing Basic SQL Select statements
  
                List the capabilities of SQL SELECT statements
  
                Execute a basic SELECT statement
  
                Differentiate between SQL statements and SQL *Plus commands
  
Restricting and sorting Data
  
                Limit the rows retrieved by a query
  
                Sort the rows retrieved by a query
  
Single Row Functions
  
                Describe various types of functions available in SQL
  
                Use character, number and date functions in SELECT statements
  
                Use conversion functions
  
Displaying data from multiple tables
  
                Write SELECT statements to access data from more than one table using equality and non-equality joins
  
                View data that generally does not meet a join condition by using outer joins 
  
                Join a table to it-self using a self-join
  
Aggregating data using group functions 
  
                Identify the available group functions 
  
                Use group functions 
  
                Group data by using the GROUP BY functions
  
                Include or exclude grouped rows by using the HAVING clause
  
Sub queries
  
                Describe the types of problems that sub-queries can solve
  
                Define subqueries
  
                List the types of subqueries
  
                Write single row and multiple rows subquries
  
Producing Readable output with iSQL*Plus
  
                Produce queries that require a substitution variable
  
                Produce more readable output
  
                Create and execute script files
  
Manipulating data
  
                Describe each DML statement
  
                Insert rows into a table
  
                Update rows from a table
  
                Delete rows from a table
  
                Merge rows from a table
  
                Control transactions
  
Creating and managing tables
  
                Describe the main database objects
  
                Create tables
  
                Describe the data types that can be used when specifying column definition
  
                Alter table definitions
  
                Drop, rename ad truncate tables
  
Including constraints
  
                Describe constraints
  
                Create and maintain constraints
  
Creating views
  
                Describe a view
  
                Create, alter the definition and drop a view
  
                Retrieve data through a view
  
                Insert update and delete data through a view
  
                Create and use an inline view
  
                Perform TOP ‘N’ Analysis
  
Creating other database objects
  
                Create, maintain and use sequences
  
                Create and maintain indexes
  
                Create private and public synonyms
  
Controlling user access
  
                Create users
  
                Create roles to ease setup and maintenance of the security model
  
                Use the GRANT and REVOKE statements to grant and revoke object privileges
  
 
  
 
  
                
  

how to communicate any ug general 8 i learn dj workshop how technology co key functions instructor up business and IT pattern security internet and awk mining synthesis joins effects cs3 training module 2 14 hours Build it com project canadian Made sap is if required reports 8 ms windows network hr new no.3 ladder safety design- sumif communication skills the company microsoft NT over 20 release 11 5 cross culture april 6th p2 foundation Pentium payroll and accounts Creative and innovation we complete tips of dreamweaver 40 their take the class datawarehouse the training any other Lean 5s drawing quality a it organization s not 0 system organization s enable it IS STANDARDS keep you from expectations body s 25 years t find open source technologies how to do personal trainer bd software the quality management 3 levels help prepare you job server Imap4 ba analysis Filmmaking form validation ecc programs the design requirement are being focus how georgia management boston 4 week Supervisory Management the needs the delivery of advanced lr life and health CE the processes The Formatting Toolbars arena use of -- manual Phi what are your support training emulator nt tools PLC CCNA Oracle Java QTP excel software testing CPR english quickbooks IATA MCSE networking computer AutoCAD management informatica electrical Tally forklift ABAP hvac safety scada pds piping Finance ITIL project management spoken english acls PHP communication insurance sas pmp embedded siebel ERP peoplesoft pdms soft skills welding telecom marketing animation AS400 train the trainer fico photoshop payroll phlebotomy six sigma Cognos J2EE UNIX asp net chennai sap fico solidworks electronics housekeeping plumbing CCNP free anger management vlsi mortgage matlab personality development gsm bookkeeping banking nursing hardware plastering first aid sharepoint american heart association cpr dot net Ndt embedded systems NLP software ielts Cisco Flash OSHA aix Powerpoint mobile dba Design access business objects Oracle Dba engineering india data warehousing db2

What are the functions of functions key in Computer from F1 to F12?

What are the functions of functions key in Computer? F1 to F12

Answer:

Commonly known as "function keys", F1 through F12 may have a variety of different uses or no use at all. Depending on the installed operating system and the software program currently open will change how each of these keys operate. A program is capable of not only using each of the function keys, but also combining the function keys with the ALT and/or CTRL keys, for example, Microsoft Windows users can press ALT + F4 to close the program currently active.

Below is a short-listing of some of the common functions of the functions keys on computers running Microsoft Windows. As mentioned above not all programs support function keys and/or may perform different tasks then those mentioned below. If you are looking for more specific shortcut keys and function key examples we suggest seeing our shortcut key page.

F1

* Almost always used as the help key, almost every program will open the help screen when this key is pressed.

* Windows Key + F1 would open the Microsoft Windows help and support center.

* Open the Task Pane.

F2

* In Windows commonly used to rename a highlighted icon or file.

* Alt + Ctrl + F2 opens a new document in Microsoft Word.

* Ctrl + F2 displays the print preview window in Microsoft Word.

F3

* Often opens a search feature for many programs including Microsoft Windows.

* Shift + F3 will change the text in Microsoft Word from upper to lower case or a capital letter at the beginning of every word.

F4

* Open find window.

* Repeat the last action performed (Word 2000+)

* Alt + F4 will close the program currently active in Microsoft Windows.

* Ctrl + F4 will close the open window within the current active window in Microsoft Windows.

F5

* In all modern Internet browsers pressing F5 will refresh or reload the page or document window.

* Open the find, replace, and go to window in Microsoft Word.

* Starts a slideshow in PowerPoint.

F6

* Move the cursor to the Address bar in Internet Explorer and Mozilla Firefox.

* Ctrl + Shift + F6 opens to another open Microsoft Word document.

F7

* Commonly used to spell check and grammar check a document in Microsoft programs such as Microsoft Word, Outlook, etc.

* Shift + F7 runs a Thesaurus check on the word highlighted.

* Turns on Caret browsing in Mozilla Firefox.

F8

* Function key used to enter the Windows startup menu, commonly use to get into Windows Safe Mode.

F9

* Opens the Measurements toolbar in Quark 5.0.

F10

* In Microsoft Windows activates the menu bar of an open application.

* Shift + F10 is the same as right-clicking on a highlighted icon, file, or Internet link.

F11

* Full-screen mode in all modern Internet browsers.

F12

* Open the Save as window in Microsoft Word.

* Shift + F12 save the Microsoft Word document.

* Ctrl + Shift + F12 prints a document in Microsoft Word.

Comparison Conditions

Comparison Conditions

=

>

>=

<

<=

<>

BETWEEN…AND…

IN(set)

LIKE

IS NULL

We can display rows based on a range of values using the BETWEEN range condition. The range that we specify contains a lower limit and an upper limit.

To test for values in a specified set of values, use the IN condition. The IN condition is also known as the membership condition.

We may not always know the exact value to search for. We can select rows that match a character pattern by using the LIKE condition. The character pattern-matching operation is referred to as a wildcard search. Two symbols can be used to construct the search string. Ie. %(zero or more characters) and _ (single character).

The NULL conditions include the IS NULL condition and the IS NOT NULL condition. The IS NULL condition tests for nulls. A null value means the value is unavailable, unassigned, unknown, or inapplicable. Therefore, we cannot test with = because a null cannot be equal or unequal to any value.

- select empno, ename,job, deptno from emp where deptno=20;

- select empno, ename, salary from emp where lastname=’Lama’;

- select * from emp where hiredate=’01-JAN-95’ and salary>=5000 and lastname=’Smith’;

- select ename,slary from emp where salary between 2300 and 3500;

- select empno,ename,salary,mgr from emp where deptno in(20,30,40);

- select ename,hiredate from emp where hiredate like ‘%95’;

- select ename from emp where ename like ‘_e%’;

- select ename,mgr from emp where mgr is null;

- select ename,salary,comm. from emp where comm. is null;

- select empno, ename,job,salary from emp where salary>=1000 and job like ‘SYS%’;

- select empno, ename,job,salary from emp where salary>=1000 or job like ‘SYS%’;

- select empno, ename from emp where job in (‘MANAGER’, ‘CLERK’, ‘PEON’);

The order of rows returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. If we use the ORDER BY clause, it must be the last clause of the SQL statement. We can specify an expression, or an alias, or column position as the sort condition. Syntax:

SELECT expression

FROM table

WHERE conditions

ORDER BY columns [ASC|DESC];

If the ORDER BY clause is not used, the sort order is undefined, and the oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.

To reverse the order in which rows are displayed, specify the DESC keyword after the column name in the ORDER BY clause.

- select ename, job, depatno, hiredate from emp order by hiredate;

- select ename, job ,deptno, hiredate from emp order by hiredate desc;

- select empno, ename, salary, salry*12 Annsal from emp order by annsal;

- select ename, deptno, salary from emp order by deptno,salary desc;

Primary key constraints

Primary key constraints

This constraint avoids duplication of rows and does not allow NULL values, when enforced in a column or set of columns. A a result it is used to identify a row. A table can have only one primary key. If a primary key constraint is assigned to more than one column(i.e) or combination of columns it is said to be a composite primary key (or simply a composite key), which can contain a maximum of 16 columns. Primary key constraint cannot be defined in an alter table command when the table contains rows having NULL values.

Run and Check your out put

- select * from emp;

- select deptno, dname, loc from dept;

- select empno, ename, salary, hiredate from emp;

- select ename,salary, salary+100 from emp;

- select ename, salary, (salary*12)+100 from emp;

- select ename,slary, (salary*12)+comm. from emp;

- select ename,salary, (salary*12)+nvl(comm,0) from emp;

- select ename “Full Name”, salary*12 “Annual Salary” from emp;

- select ename||job from emp;

- select ename||’ is a ‘||job “Emp Detail” from emp;

- select deptno from emp;

- select distinct deptno from emp;

- select distinct deptno, job from emp;

- In SQL, we can display the structure of a table using the DESCRIBE command. The command shows the column names and data types, as well as whether a column must contain data or not.

- desc emp;

- describe emp;

Hello, world!
]]>

How to make Diet Coke and Mentos Rocket

What happen when diet coke and mentos get mixed??

It forms boom which get exploid


myths and facts health tips diet management