Ask the Experts
Ask the Experts
Oracle development languages
-
Oracle LEFT JOIN vs. LEFT OUTER JOIN: What's the difference?
Get an explanation of the different types of Oracle joins, including whether there's really a difference between the LEFT JOIN and LEFT OUTER JOIN syntax in PL/SQL. Continue Reading
-
Three tips for using Oracle insert syntax to insert multiple rows
If you're looking for insert syntax to insert multiple rows in Oracle, read these tips from three of our community members. Continue Reading
-
Making Monday the start of the week in Oracle SQL
One reader asks how to set up a report in Oracle SQL so that Monday is the first day of the week. Continue Reading
-
When to use double quotes in Oracle column to avoid invalid identifier
Are you seeing an invalid identifier? Learn when to use double quotes in Oracle column names to avoid this in this tip from PL/SQL expert Dan Clamage. Continue Reading
-
How to concatenate rows into a single CLOB in PL/SQL
PL/SQL expert Dan Clamage explains how to concatenate rows into a single CLOB in PL/SQL. Continue Reading
-
Using the SQL GROUP BY clause for counting combinations
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
-
How to sort an SQL UNION query with special ORDER BY sequence
SQL expert Rudy Limeback explains how to sort an SQL UNION query using a special ORDERY BY sequence. Continue Reading
-
Using an SQL SELECT statement from a non-existing table
SQL expert Rudy Limeback explains how to formulate a query using an SQL SELECT statement from a non-existing table. Continue Reading
-
Using LEFT OUTER JOIN query to get zero row counts in SQL
An SQL expert explains how using a LEFT OUTER JOIN query can retrieve zero row counts in SQL. Continue Reading
-
How to create an SQL CHECK constraint for two letters
SQL expert Rudy Limeback explains how to create a SQL CHECK constraint for two letters when trying to write a query to retrieve data from two tables. Continue Reading
-
How to return a zero in SQL instead of no row back for a select count
Want your row to return a zero instead of getting no row backs for some combinations when doing a select count in SQL? Find out how here. Continue Reading
-
Which normal form is used most?
SQL expert Rudy Limeback explains what normal form is used in most database projects. Continue Reading
-
Using BETWEEN with DATETIMEs in SQL
Expert Rudy Limeback is asked if SQL can be used to retrieve data between two dates, including the two dates. Continue Reading
-
SQL query for co-authored books
SQL expert Rudy Limeback gives advice for writing a query that involves a number of tables. Continue Reading
-
SQL for hourly totals for the last 48 hours
SQL expert Rudy Limeback explains to an Oracle user how to user asks how to generate datetimes or use left outer join to get hourly totals for the last 48 hours in SQL. Continue Reading
-
LEFT OUTER JOIN to a MIN/MAX row
An Oracle user asks SQL expert Rudy Limeback how to use a LEFT OUTER JOIN with a MIN/MAX condition. Continue Reading
-
How do I retrieve second transaction for each customer?
An Oracle user asks how to write a query to retrieve the second transaction for each customer. Continue Reading
-
SQL to round up to nearest thousand
SQL expert Rudy Limeback explains how to use the CEILING function. Continue Reading
-
Select from one table based on condition in another
SQL expert Rudy Limeback explains how to select from one table based on condition in another using an inner join in Oracle. Continue Reading
-
How to use DISTINCT on just one column
SQL expert Rudy Limeback explains how to use DISTINCT on just one column. Continue Reading
-
Integrity constraints and referential integrity
What is the difference between integrity constraints and referential integrity? Continue Reading
-
Use HAVING with CASE to count specific instances
I'm trying to construct a SELECT statement that will return people that have NO live courses running at the moment. Continue Reading
-
Difference between driving table and driver table in Oracle
I need to know the difference between the driving table and driver table in Oracle. Continue Reading
-
SQL query for all Fridays in a year
How to get all Fridays for year 2007 in SQL? Continue Reading
-
What is the difference between left outer join and (+) outer join?
What is the difference between left outer join and (+) outer join? Continue Reading
-
The MAX of two MAX values in SQL
Can I get the max of two columns into one column in SQL? Continue Reading
-
Obtaining COUNTs for males and females in one SQL query
For my table the gender column has only two values; F and M. I would like to do a count of both genders in one SQL query. Continue Reading
-
Using GROUP BY to collapse rows
Can I get the following with one select statement in SQL? Continue Reading
-
UPDATE only rows which match another table in SQL
I have a SQL query where I am trying to update a column in a table from data in another table. This works fine when I try to update all the records in the first table; however, Is it possible to update only the missing data? Continue Reading
-
SQL to calculate days to next status change
I would like to find out how long a particular customer order has been on hold in SQL. Continue Reading
-
Employees with the same first and last names in SQL
How do I SELECT a list of employees who have the same last and first name in SQL? Continue Reading
-
Columns in the SELECT not in the GROUP BY
I have an SQL query where I am using the SUM function with the GROUP BY clause. I need to select a couple more columns from the table without adding those columns in the GROUP BY clause. Continue Reading
-
LEFT OUTER JOIN without using LEFT OUTER JOIN
Can I get the same result as LEFT OUTER JOIN without using LEFT OUTER JOIN? Continue Reading
-
ORA-04082: NEW or OLD references not allowed in table level triggers
I am calling a stored procedure from a trigger. It shows the error: "ORA-04082: NEW or OLD references not allowed in table level triggers." Continue Reading
-
Add a column in a specific position in a table
I have created one table with three columns. I want to drop one column and add the column back to the same table in the same location. Continue Reading
-
Totals from a UNION query
I'm trying to retrieve a count of rows using a union, but for some reason I get two rows returned with different counts. Continue Reading
-
ORA-01702: a view is not appropriate here
I'm creating an index on a view using Oracle 9.2.0. I get the error ORA-01702 (a view is not appropriate here). Continue Reading
-
COUNT(*) or COUNT(1)
How do you specify difference between count(*) and count(1) in a select statement? Which is best to use and when? Continue Reading
-
Encountered the symbol "ELSE" when expecting one of the following
I am a beginner to Oracle. I am getting this error: "ORA-06550: line 21, column 1: PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:" Continue Reading
-
GROUP BY multiple columns
My table structure is empid, category, col1, col2, amount. I would like to get the results as total of amounts for the col1 and col2 combinations, with a particular category. Continue Reading
-
Sorting a clob column
I'm trying to sort a column type asc as well as desc. I'm able to sort the clob column desc but not able to sort it asc. Can you please help me out? Continue Reading
-
Finding the highest COUNT in a GROUP BY query
I want to get the maximum of totalcontracts from the retrieved data using a select statement. Continue Reading
-
Foreign keys in a composite primary key
Is it possible to use one of the attributes of a composite primary key as a foreign key? Continue Reading
-
DISTINCT applies to all columns in the result
Could you tell me what are the effects of using the DISTINCT keyword before a group of different column names in a SELECT statement? Continue Reading
-
Difference between two dates in hours
How to calculate the difference between two dates in hours? Continue Reading
-
Using SQL to find the number of Sundays in a month
I want to find out how many Sundays come in any month in SQL. Continue Reading
-
ON DELETE RESTRICT
I would like to know where we use DELETE RESTRICT? Continue Reading
-
ORDER BY a specified sequence
I'm trying to create an SQL query with the IN clause where I give the IDs to match. Now I want the order of these records to be the same as I give in the IN clause. But it's not happening. Continue Reading
-
Multiple rows with same value in one column
I'm doing a search from one table and my goal is to show only the rows with the same value in one of the columns. I am only interested in seeing the rows for all the emp_no that shows more than once. Continue Reading
-
DELETE WHERE NOT EXISTS
I have two tables, A1 and A2, and they both have a column called SSn. How will I delete a row from table A2 that doesn't exist on A1? Continue Reading
-
LEFT OUTER JOIN on more than two tables
Is it possible to perform LEFT OUTER JOIN in more than two tables? If possible, please give me details. Continue Reading
-
LEFT OUTER JOIN with ON condition or WHERE condition?
I would like your explanation of the difference between a LEFT OUTER JOIN ON condition and a WHERE condition. Continue Reading
-
Converting from CHAR to DATE datatype
As a time value, I used the CHAR datatype and need to convert to a DATE value while manipulating/computing. How do I use the DATE datatype instead? Continue Reading
-
Alternative to LEFT OUTER JOIN?
-
PL/SQL to display all tables
-
How to validate zip code, city, and state code
-
FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows...
-
Age constraint on a date column
-
COALESCE on three columns
-
Getting a row number in a group
-
What does SELECT 1 accomplish?
-
Closest date before or after a given date
-
How can I use INSERT INTO to copy an entire table into another?