Code In Action

  The simplest solution which worked for me is Changing the date format for the session. ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD' You can permanently set the default nls_date_format as well: ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD'

mysqldump command is used to create a text version of the database. This creates a SQL file that contains all SQL statements  which we can use to restore/recreate the original database. Below is the command: $ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]

There is no difference between Count(*) and Count(1). They are same both in terms of result and performance. Actual syntax of Count() function is as follows. Syntax: As per syntax, you can use either * or an expression as an argument to function. Here 1 is a non-null expression, so it is same as Count(*).  Brief Introduction: 1. Count returns the number of rows returned by the query. Some examples of Count: 1. select count(*) from employees; 2. select count(salary) from employees; 3. select count(distinct manager_id) from employees; References: a. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions032.htm

UNION & UNION ALL: 1. UNION , UNION ALL both merge 2 tables or 2 query results into a single temporary table. The 2 tables must have same structure, otherwise sql returns error. 2. The main difference is UNION will remove duplicate records from results, where as UNION ALL will include duplicate records too. 3. The other difference is, UNION ALL is typically better than UNION, since UNION requires Database server to do the additional work of removing any duplicates. So, when it is certain that there will not be any duplicates in the 2 tables or having duplicates is not a problem, use UNION ALL for better performance.

     Difference between Delete, Truncate and Drop is often an interview question to the novice programmers. Let's see what is the actual difference between these. DELETE: 1.The DELETE command is used to remove rows from a table. 2. A WHERE clause can be used to only remove some rows. 3. If no WHERE condition is specified, all the rows will be removed. 4. After performing a DELETE operation, you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. 5. This operation will cause all DELETE triggers on the table to fire. 6. It's a DML command. DELETE query with WHERE clause DELETE FROM EMP WHERE EMP_ID = 1; DELETE query without WHERE clause, is used to remove all the rows from table. DELETE FROM EMP; DELETE EMP; TRUNCATE: 1. TRUNCATE removes all the rows from a table. 2. The operation cannot be rolled back and no triggers will be fired. 3. TRUNCATE is much faster than DELETE, because, when you execute DELETE,...

How to delete duplicate rows from a table in SQL ? Recently, I have attended written test for a company. The written test consists of 2 java programs, and 3 sql queries. In those 3 queries, this was also one question. More Details about the question: the table data is like below. the output should be like below. That means, you need to remove duplicate rows from table. first lets create the table. CREATE TABLE EMP (EMP_ID INTEGER, EMP_NAME VARCHAR2(20), SALARY INTEGER); Now insert the values into table EMP. INSERT INTO EMP VALUES  (1,'sreenath',10000); INSERT INTO EMP VALUES  (1,'sreenath',10000); INSERT INTO EMP VALUES  (2,'ulasala',20000); INSERT INTO EMP VALUES  (3,'ulasala sreenath',30000); INSERT INTO EMP VALUES  (3,'ulasala sreenath',30000); To solve this problem, first you need to query only distinct records from EMP and then insert those records into a temporary table. for that use below query. que...

The built in LOB data types in Oracle are 1. BLOB 2. CLOB 3. NCLOB 4. BFILE These data types enables to store large amounts of unstructured data such as text, images, videos, sounds in binary or character format. They also provide efficient, random, piecewise access to data. Difference between LOB data types and LONG and LONG RAW datatypes: A table can contain multiple LOB columns, but only one LONG column. The maximum size of LONG column is 2 gigabytes, but the maximum size of a LOB can be 128 terabytes depending on the database block size. LOBs support random access to data, but LONGs support only sequential access. Tables with LOB columns can be replicated, but tables with LONG columns cannot. From Oracle 9i, LOB datatypes can be stored inline i.e within a table, out-of-line i.e within a tablespace, using a LOB locator, or in an external file( BFILE data types). BLOB Datatype : The BLOB datatype stores unstructured binary data in the database. BLOBs can store up ...

     In this tutorial i am going to explain about data types, RAW, LONG RAW, LONG, DATE and TIMESTAMP in Oracle. The previous tutorials are available here part 1 , part 2 RAW And LONG RAW DataTypes :      The RAW and LONG RAW are used to store binary data or byte strings. These are variable-length datatypes like the VARCHAR2 datatype. You can use them to store graphics, sound, documents, or arrays of binary data. As per Oracle, these data types are not subjected to automatic character set conversion between different systems. Some Oracle services such as Oracle Net Services and Import/Export automatically convert CHAR, VARCHAR2 and LONG data from database character set to the user session character set, if the two character sets are different. The maximum length of RAW datatype is 255 bytes. Syntax : Column_name RAW(10) The maximum length of LONG RAW datatype is 2GB. Syntax : Column_name LONG RAW A table can have only one LONG ...

     In the last tutorial, we learned about Character Data Types in Oracle SQL. In this tutorial, we are going to learn about Numeric data types that oracle supports. Numeric Data Types : The data that can be stored in numeric data types can be zero (0), infinity, positive and negative fixed and floating-point numbers. You can also store values that are results of invalid operations, such as NAN or "not a number". Numeric data types are categorized into 2 types 1. NUMBER Data Type. 2. FLOAT Data Type. 1. NUMBER Datatype : 1. It stores zero, positive and negative fixed and floating point numbers. 2. The range for positive numbers is from 1 x 10 -130   to 9.99...9 x 10 125 with up to 38 significant digits. 3. The range for negative numbers is from -1 x 10 -130   to 9.99...99 x 10 125 with up to 38 significant digits.  Syntax 1: column_name NUMBER syntax 2 : column_name NUMBER(precision,scale) 4. precision specifies the total n...

    In relational databases, the data is stored in the form of  tables. A table is set of rows, and a row contains multiple columns. each such a row is called as record, and all the data related to that record, resides in columns. Each column in sql has a data type. A data type specifies which kind of data a column can accommodate. While creating tables, you must specify, data type for each column. The built in data types that oracle supports are categorized into : 1.Character Data Types 2.Numeric Data Types 3.Raw, Long Raw and Long Data Types 4.Date and Time Data Types 5.Large Object Data Types 6.RowID Data Types Let’s see all the categories in detail, Character Data Types : The kind of data that you can store in any character data type is alphanumeric data, such as words, text from any language. All the data stored in the form of Strings, with the values corresponding to the character encoding scheme. Character encoding scheme is a set of charact...