Difference between CHAR, VARCHAR AND VARCHAR2
Filed under: MySQL Interview Questions, Oracle Interview Questions, SQL SERVER Interview Questions
CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.
SQL> CREATE TABLE char_test (col1 CHAR(10));
Table created.
SQL> INSERT INTO char_test VALUES (‘qwerty’);
1 row created.
SQL> SELECT col1, length(col1), dump(col1) “ASCII Dump” FROM char_test;
COL1 LENGTH(COL1) ASCII Dump
———- ———— ————————————————————
qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32
VARCHAR2
VARCHAR2 is used to store variable length character strings. The string value’s length will be stored on disk with the value itself.
SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));
Table created.
SQL> INSERT INTO varchar2_test VALUES (‘qwerty’);
1 row created.
SQL> SELECT col1, length(col1), dump(col1) “ASCII Dump” FROM varchar2_test;
COL1 LENGTH(COL1) ASCII Dump
———- ———— ————————————————————
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121
VARCHAR
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
How to find highest and second highest record or column value in sql
Filed under: MySQL Interview Questions, Oracle Interview Questions, SQL SERVER Interview Questions
MySQL: In mysql to get the highest and second highest column or record we can use order by and limit
For example : The below query will give me the highest record:
SELECT * FROM `student` where class=’tenth’ ORDER BY mark desc LIMIT 0,1
To get the second highest record:
SELECT * FROM `student` where class=’tenth’ ORDER BY mark desc LIMIT 0,1
To get highest record or second highest record in Oracle we can use ROWNUM . Oracle doesn’t have LIMIT and ROWNUM works in the same way as LIMIT works in MYSQL.
Below query will give me the highest or the max student record with highest marks
SELECT * FROM `student` where class=’tenth’ and ROWNUM == 1 ORDER BY marks
Second highest record can be get through below command
SELECT * FROM `student` where class=’tenth’ and ROWNUM == 2 ORDER BY marks
Tedious way would be to write nested sql query to find the second highest record
SELECT score,player FROM scores WHERE score=(SELECT max(score) FROM scores WHERE score< (SELECT max(score) FROM scores));
The above finds the second highest score in the scores table and the name of the player.
Reading from right to left:
1. The first select finds the highest score.
2. The second select find the highest from the rest.
3. The last select finds the name of the player.
What are the methods that can ensure asynchronous execution of the Transact-SQL statement or stored procedure?
Filed under: .NET Interview Questions, ADO.NET Interview Questions, SQL SERVER Interview Questions
BeginExecuteNonQuery
BeginExecuteReader
How to find duplicate entry or duplicate row in a table
Filed under: MySQL Interview Questions, Oracle Interview Questions, PostgreSQL Interview Questions, SQL SERVER Interview Questions
In this sql command we are trying to find the duplicate entry with duplicate name. This sql command will give all the MIN id which has duplicate entry
SELECT MIN(id) AS dupid,COUNT(name) AS dupcnt
FROM tablename
GROUP BY name HAVING dupcnt>1
It should work on Oracle, Mysql