How does one escape special characters when building SQL queries?
The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:
SELECT name FROM emp WHERE id LIKE '%\_%' ESCAPE '\';
Use two quotes for every one displayed. Example:
SELECT 'Franks''s Oracle site' FROM DUAL;
SELECT 'A ''quoted'' word.' FROM DUAL;
SELECT 'A ''''double quoted'''' word.' FROM DUAL;
How does one eliminate duplicates rows from a table?
Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:
Method 1:
SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);
Method 2:
SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
SQL> -- Remember to recreate all indexes, constraints, triggers, etc on table...
Method 3: (thanks to Dennis Gurnick)
SQL> delete from my_table t1
SQL> where exists (select 'x' from my_table t2
SQL> where t2.key_value1 = t1.key_value1
SQL> and t2.key_value2 = t1.key_value2
SQL> and t2.rowid > t1.rowid);
Note: One can eliminate N^2 unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process.
Note 2: If you are comparing NOT-NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.
How does one generate primary key values for a table?
Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:
SQL> UPDATE table_name SET seqno = ROWNUM;
or use a sequences generator:
SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;
Finally, create a unique index on this column.
How does one get the time difference between two date columns?
Look at this example query:
select floor(((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference
from ...
If you don't want to go through the floor and ceiling math, try this method (contributed by Erik Wile):
select to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time_difference
from ...
Note that this query only uses the time portion of the date and ignores the date itself. It will thus never return a value bigger than 23:59:59.
How does one add a day/hour/minute/second to a date value?
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:
SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400
--------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002
08:32:13
The following format is frequently used with Oracle Replication:
select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2002 16:47:23 03-JUL-2002 16:47:53
How does one count different data values in a column?
Use this simple query to count the number of data values in a column:
select my_table_column, count(*)
from my_table
group by my_table_column;
A more sophisticated example...
select dept, sum( decode(sex,'M',1,0)) MALE,
sum( decode(sex,'F',1,0)) FEMALE,
count(decode(sex,'M',1,'F',1)) TOTAL
from my_emp_table
group by dept;
How does one count/sum RANGES of data values in a column?
A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:
select f2,
sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
from my_table
group by f2;
For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). Eg.
select ename "Name", sal "Salary",
decode( trunc(f2/1000, 0), 0, 0.0,
1, 0.1,
2, 0.2,
3, 0.31) "Tax rate"
from my_table;
Can one retrieve only the Nth row from a table?
SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
WHERE RN = 100;
Note: Note: In this first it select only one more than the required row, then it selects the required one. Its far better than using MINUS operation.
SELECT f1 FROM t1
WHERE rowid = (
SELECT rowid FROM t1
WHERE rownum <= 10
MINUS
SELECT rowid FROM t1
WHERE rownum < 10);
Alternatively...
SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
(SELECT rowid FROM emp WHERE rownum < 10);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
Can one retrieve only rows X to Y from a table?
Shaik Khaleel provided this solution to the problem:
SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101
) WHERE RN between 91 and 100 ;
Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).
Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:
SELECT *
FROM tableX
WHERE rowid in (
SELECT rowid FROM tableX
WHERE rownum <= 7
MINUS
SELECT rowid FROM tableX
WHERE rownum < 5);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);
Method 2: Use dynamic views (available from Oracle7.2):
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.ROWNUM,4) = 0;
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.
How does one select the TOP N rows from a table?
Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)
WHERE ROWNUM < 10;
Use this workaround with prior releases:
SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol >= a.maxcol)
ORDER BY maxcol DESC;
How does one code a tree-structured query?
Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.
The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:
select LEVEL, EMPNO, ENAME, MGR
from EMP
connect by prior EMPNO = MGR
start with MGR is NULL;
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:
select lpad(' ', LEVEL * 2) || ENAME ........
One uses the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
How does one code a matrix report in SQL?
Look at this example query with sample output:
SELECT *
FROM (SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job)
ORDER BY 1;
JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
How does one implement IF-THEN-ELSE in a select statement?
The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or columns based on the values of other columns in the select statement.
Some examples:
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from employees;
select a, b, decode( abs(a-b), a-b, 'a > b',
0, 'a = b',
'a < b') from tableX;
select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 'B is greater than A')...
select decode( GREATEST(A,B),
A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'),
'A NOT GREATER THAN B')...
Note: The decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the good things about Oracle, but use it sparingly if portability is required.
From Oracle 8i one can also use CASE statements in SQL. Look at this example:
SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END
FROM emp;
How can one dump/ examine the exact content of a database column?
SELECT DUMP(col1)
FROM tab1
WHERE cond1 = val1;
DUMP(COL1)
-------------------------------------
Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
Can one drop a column from a table?
From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.
Other workarounds:
1. SQL> update t1 set column_to_drop = NULL;
SQL> rename t1 to t1_base;
SQL> create view t1 as select <specific columns> from t1_base;
2. SQL> create table t2 as select <specific columns> from t1;
SQL> drop table t1;
SQL> rename t2 to t1;
Can one rename a column in a table?
No, this is listed as Enhancement Request 163519. Some workarounds:
1. -- Use a view with correct column names...
rename t1 to t1_base;
create view t1 <column list with new name> as select * from t1_base;
2. -- Recreate the table with correct column names...
create table t2 <column list with new name> as select * from t1;
drop table t1;
rename t2 to t1;
3. -- Add a column with a new name and drop an old column...
alter table t1 add ( newcolame datatype );
update t1 set newcolname=oldcolname;
alter table t1 drop column oldcolname;
How can I change my Oracle password?
Issue the following SQL command:
ALTER
USER <username> IDENTIFIED BY <new_password>
/
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name".
How does one find the next value of a sequence?
Perform an "ALTER SEQUENCE ... NOCACHE" to unload the unused cached sequence numbers from the Oracle library cache. This way, no cached numbers will be lost. If you then select from the USER_SEQUENCES dictionary view, you will see the correct high water mark value that would be returned for the next NEXTVALL call. Afterwards, perform an "ALTER SEQUENCE ... CACHE" to restore caching.
You can use the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or any other operation that would cause gaps in sequence values.
Workaround for snapshots on tables with LONG columns
You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one location to another. Eg:
COPY TO SCOTT/TIGER@REMOTE -
CREATE IMAGE_TABLE USING -
SELECT IMAGE_NO, IMAGE -
FROM IMAGES;
Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.
HP Printer Support
ReplyDeleteHP Printer Support Phone Number usa
HP Printer Tech Support Phone Number
HP Printer Tech Support Number
HP Printer Technical Support
HP Printer Technical Support Number
HP Printer Helpline Number
canon Printer Support Number
ReplyDeletecanon Printer Support Number USA
canon Printer Support
canon Printer Tech Support Phone Number
canon Printer Technical Support Phone Number
canon Printer Customer Service Number
canon Printer Customer Support Number
HP Printer Support Phone Number
ReplyDeleteBrother Printer Support Phone Number
Brother Printer Support Phone Number
Lexmark Printer Support Phone Number
Lexmark Printer Support Phone Number
HP Printer Support phone Number
HP Printer Support Phone Number
Epson printer Support Phone Number
Epson Printer Support Phone Number
HP Printer Installation Help
HP Printer Installation help
HOme PAGE
ReplyDeleteEpson Printer Support Phone Number
Epson Printer Technical Support Number
Epson Printer Technical Support Number
epson printer drivers download
epson printer drivers download
epson printer prints blank pages
epson printer prints blank pages
epson printer error code 0x800706b9
epson printer error code 0x800706b9
Canon
Canon Printer Support Phone Number
Canon Priner Technical Support Number
Canon Priner Technical Support Phone Number
Canon Printer Customer Support Number
Canon Printer Customer Support Number
Epson Printer Helpline Number
Epson Printer Helpline Number
Canon
ReplyDeleteCanon Printer Support
Canon Printer Support Number
Canon Printer Support Number USA
canon printer tech support phone number
Canon Printer technical Support Phone Number
Canon Printer customer Support Number
Canon Printer customer service number
HP
HP Printer Support
HP Printer Support Phone Number USA
HP Printer tech Support Number
HP Printer tech support phone Number
HP Printer technical support
HP Printer Number
HP Printer technical support Number
HP printer helpline number
EPSON
Epson Printer Support Number
Epson Printer Support
Epson Printer Support Phone Number
Epson Printer Support Phone Number USA
hp Printer
ReplyDeletehp printer Support
hp Support
brother printer
brother printer Support
brother Support
canon printer
canon printer Support
canon Support
epson
epson printer Support
epson Support
Dell Printer
dell printer Support
dell Support
Lexmark Printer
Lexmark Support
Lexmark printer Support
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteEpson Printer Support Phone Number
ReplyDeleteEpson Printer Support Number
Epson Printer Support
Epson Printer Technical Support Number
Epson Printer Technical Support Phone Number
Epson Printer Tech Support Number
Epson Printer Helpline Number
Epson Printer Customer Helpline Number
Epson Printer Customer Care Number
epson workforce wf 3530 support number/
HP Printer Support
ReplyDeleteHP Printer Support Phone Number
HP Printer Support Telephone Number
HP Printer Support Contact Number
HP Printer Support Toll Free Number
HP Printer Support Online
HP Printer Support Wireless
HP Printer Support Telephone
HP Printer Support Help
HP Printer Support Helpline
HP Printer Support Number usa
HP Printer Support Line
HP Printer Support Customer Service Number
HP Printer Support usa
Canon Printer Support Phone Number
ReplyDeleteCanon Printer Support Number
Canon printer technical Support
Canon Printer Support usa
Canon Printer Support Help
Canon Printer Support Online
Canon printer Support Telephone Number
Canon printer Customer Support Phone Number
--------------------------------------------------------------------
Linksys Support
Linksys Support Number
Linksys Customer Support Number
Linksys Support site
Linksys Support Phone
Linksys Support Chat
Linksys Support Line
Linksys Support Online
Linksys Support website
Linksys Support Number usa
Linksys Router Support Phone Number
Delta Skymiles Phone Number For Reservations
ReplyDeleteDelta Airlines Customer Service Number
Cheap Flight Tickets Websites
Jetblue Reservations Phone Number
Delta Airlines Phone Number
Jetblue Airlines Phone Number
United Airlines Reservation Phone Number
United Airlines Phone Number
Delta Reservations Phone Number
TechnoTools: Front-end web developers are responsible for how the website looks. They create the layout of the site and integrate graphics, applications (such as retail checkout tools) and other content. They also write web site programs in various types of computer languages, such as HTML or Javascript.
ReplyDeleteDelta is an American airline major company, headquarters in Atlanta, Georgia and operates over 5400 flights every day. Delta operates with domestic and international flights in 52 countries include 325 destinations. Travelers may choose different cabin option as per their needs and capabilities like Delta One, Premium Select, First Class, Delta Comfort+, Main Cabin, and Basic Economy. Delta Airlines Reservation Phone Number will help you to plan your vacation and trips. You may dial Delta Airlines Phone Number to know your flight status, changes in your flight, upgrade your seats. A team of travel advisor is ready to listen to you and make your fly easy.
ReplyDeleteHp printer now not just print the reviews or non-official pages yet what's greater channel tons step by step then one web page as proven with the aid of the client's fundamental. We resolutely agree with in giving prime HP Printer Support Phone Number to our clients who face burden whilst printing. HP printers can in like way have some default, which inconveniences the purchaser for a noteworthy long time. Any kind of weight which the HP printer client faces, he is uninhibitedly authorised to contact on our HP Printer Support number. Hp printer toughen work depicts that their first and most magnificent want is purchasers and customer's fulfillment. Our assist association develops each day ensuing to getting notion and devotion from customers all round masterminded nature.
ReplyDeleteThe most common problem experienced while dealing with printers is their installation. Most customers do not know how to download genuine drivers for their printers. Some have lost the CD came along with the printers.
ReplyDeletePerformance of printer is very slow
Happens when a printer is set to print high-quality output.
Printer not printing properly
This generally may occur when a printer is not plugged in properly.
Faded printing quality or horizontal spots on prints
When the print head is clogged due to dry ink then this problem can be seen.
Paper jamming
This occurs when papers got stuck in the rear tray.
‘’No papers’’ alert
This issue can be caused due to many problems like paper are not aligned well, a thickness of the sheets are not appropriate, or the sheets are wrinkled or crushed.
Printing isn’t completed
Sometimes you must have also observed printer only print have and get stopped in the middle of the task.
Well, above are some common problems, but are very easy to troubleshoot through Epson Printer Technical Support. Apart from this, there are several other issues as well which can be resolved by our experts. So, feel to connect by dialing toll-free number
The most common problem experienced while dealing with printers is their installation. Most customers do not know how to download genuine drivers for their printers. Some have lost the CD came along with the printers.
ReplyDeletePerformance of printer is very slow
Happens when a printer is set to print high-quality output.
Printer not printing properly
This generally may occur when a printer is not plugged in properly.
Faded printing quality or horizontal spots on prints
When the print head is clogged due to dry ink then this problem can be seen.
Paper jamming
This occurs when papers got stuck in the rear tray.
‘’No papers’’ alert
This issue can be caused due to many problems like paper are not aligned well, a thickness of the sheets are not appropriate, or the sheets are wrinkled or crushed.
Printing isn’t completed
Sometimes you must have also observed printer only print have and get stopped in the middle of the task.
Well, above are some common problems, but are very easy to troubleshoot through Epson Printer Technical Support. Apart from this, there are several other issues as well which can be resolved by our experts. So, feel to connect by dialing toll-free number
Very informative. thanks for sharing this.
ReplyDeletewe are canon printer support providers. we provide support across the world. if you want canon printer tech support on your desk Dial 18007976023 and login to support for canon all in one printer
Get technical support for your hp printer through best hp technician by dialling Hp printer support phone number in least time.
ReplyDeletehp printer support phone number
hp printer technical support number
hp printer support phone number
ReplyDeleteepson Printer Support
epson Printer Support Phone Number
epson Printer Support Number
epson Printer Toll Free Number
The Canon printers are prominent for its amazing performance, still individuals may experience issues while utilizing it. All things considered, you can legitimately approach the specialists for Canon Printer Customer Service. There are numerous mistakes that you may face during printing time.But you need only to contact us for technical support.canon Printer Support
ReplyDeletecanon Printer Support Phone Number
canon Printer Support Number
canon Printer Technical Support Number
canon Printer Customer Support Number
canon Printer Toll Free Number