SQL : Interview questions and answers - Part 1

Oracle
 
Oracle :- Oak Ridge Analytical Computer Logical Engine
 
 
Statements of Oracle:-
DDL – Data definition language
1)      Create – Used to make new database object
2)      Alter- To modify the structure of the table
3)      Add – Used to add new column
4)      Modify – Used to modify structure of existing columns
5)      Drop – Used to remove columns
 
DML – Data manipulation language
1)      Insert – New statement to be inserted
2)      Update – Used to modify the existing records
3)      Delete – Used to remove the records from the table
 
DCL – Data control language
1)      Grant – Used to give permissions
2)      Revoke – Used to cancel permissions for (insert, update, delete and select)
 
TCL – Transaction control language
1)      Commit – Used to save changes made by DML statements
2)      Roll back – It is used to cancel transactions which are not saved
3)      Save Point – It is used to mark the transaction here
 
DRL – Data retrieval language
1)      Select – Used to retrive data from tables it is for read only purpose
 
Set Operators:-
 
Union – Union operator returns all rows selected by either query. Use the union operator to return all rows from multiple tables and eliminate any duplicate rows.
Null values are not ignored during duplicate checking.
 
Union all – Combines the result of two select statements into one result set including the duplicates.
 
Minus – Minus operator returns rows from the first query that are not present in the second query.
 
Mathematical Functions:-
 
ABS – This function returns the absolute value of a number.
Syntax – ABS(number)
Ex- select ABS(-32) from dual; o/p – 32
ABS(-32.4)
o/p: 32.4
 
ABS(-32.45)
o/p: 32.4
 
ABS(32.67)
o/p: 32.67
 
ABS(32.67 * -1)
o/p: 32.67
 
CEIL - This function returns the smallest integer value that is greater than or equal to a number.
 
Syntax- CEIL( number )
Ex - CEIL(32.65)
o/p: 33
 
CEIL(32.1)
o/p: 33
 
CEIL(32)
o/p: 32
 
CEIL(-32.65)
o/p: -32
 
CEIL(-32)
o/p: -32
 
EXTRACT - This function extracts a value from a date or interval value.
You can only extract YEAR, MONTH, and DAY from a DATE.
You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.
Syntax:
 
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )
 
Ex -
EXTRACT(YEAR FROM DATE '2003-08-22')
o/p: 2003
 
EXTRACT(MONTH FROM DATE '2003-08-22')
o/p: 8
 
EXTRACT(DAY FROM DATE '2003-08-22')
o/p: 22
 
FLOOR - This function returns the largest integer value that is equal to or less than a number.
Syntax- FLOOR( number )
Ex -
FLOOR(5.9)
o/p: 5
 
FLOOR(34.29)
o/p: 34
 
FLOOR(-5.9)
o/p: -6
 
ROUND - This function returns a number rounded to a certain number of decimal places.
Syntax (WITH NUMBERS): ROUND( number, [ decimal_places ] )
Ex-
ROUND(125.315)
o/p: 125
 
ROUND(125.315, 0)
o/p: 125
 
ROUND(125.315, 1)
o/p: 125.3
 
ROUND(125.315, 2)
o/p: 125.32
 
ROUND(125.315, 3)
o/p: 125.315
 
ROUND(-125.315, 2)
o/p: -125.32
 
TRUNC - This function returns a number truncated to a certain number of decimal places.
Syntax (WITH NUMBERS) : TRUNC( number, [ decimal_places ] )
Ex-
TRUNC(125.815)
o/p: 125
 
TRUNC(125.815, 0)
o/p: 125
 
TRUNC(125.815, 1)
o/p: 125.8
 
TRUNC(125.815, 2)
o/p: 125.81
 
TRUNC(125.815, 3)
o/p: 125.815
 
TRUNC(-125.815, 2)
o/p: -125.81
 
TRUNC(125.815, -1)
o/p: 120
 
TRUNC(125.815, -2)
o/p: 100
 
TRUNC(125.815, -3)
o/p: 0
 
TO_CHAR – This function converts a number or date to a string.
 
Syntax : TO_CHAR( value, [ format_mask ], [ nls_language ] )
Ex-
TO_CHAR(1210.73, '9999.9')
o/p: ' 1210.7'
 
TO_CHAR(-1210.73, '9999.9')
o/p: '-1210.7'
 
TO_CHAR(1210.73, '9,999.99')
o/p: ' 1,210.73'
 
TO_CHAR(1210.73, '$9,999.00')
o/p: ' $1,210.73'
 
TO_CHAR(21, '000099')
o/p: ' 000021'
 
TO_CHAR(sysdate, 'yyyy/mm/dd')
o/p: '2003/07/09'
 
TO_CHAR(sysdate, 'Month DD, YYYY')
o/p: 'July 09, 2003'
 
TO_CHAR(sysdate, 'FMMonth DD, YYYY')
o/p: 'July 9, 2003'
 
TO_CHAR(sysdate, 'MON DDth, YYYY')
o/p: 'JUL 09TH, 2003'
 
TO_CHAR(sysdate, 'FMMON DDth, YYYY')
o/p: 'JUL 9TH, 2003'
 
TO_CHAR(sysdate, 'FMMon ddth, YYYY')
o/p: 'Jul 9th, 2003'
 
TO_CHAR(sysdate, 'FMMonth DD, YYYY')
o/p: 'July 9, 2003'
 
TO_CHAR(sysdate, 'FMMON DDth, YYYY')
o/p: 'JUL 9TH, 2003'
 
TO_CHAR(sysdate, 'FMMon ddth, YYYY')
o/p: 'Jul 9th, 2003'
 
TO_DATE - This function converts a string to a date.
 
Syntax : TO_DATE( string1, [ format_mask ], [ nls_language ] )
Ex-
TO_DATE('2003/07/09', 'yyyy/mm/dd')
o/p: date value of July 9, 2003
 
TO_DATE('070903', 'MMDDYY')
o/p: date value of July 9, 2003
 
TO_DATE('20020315', 'yyyymmdd')
o/p: date value of Mar 15, 2002
 
TO_NUMBER - This function converts a string to a number.
 
Syntax : TO_NUMBER( string1, [ format_mask ], [ nls_language ] )
Ex-
TO_NUMBER('1210.73', '9999.99')
o/p: 1210.73
 
TO_NUMBER('546', '999')
o/p: 546
 
TO_NUMBER('23', '99')
o/p: 23
 
TO_NUMBER('1210.73')
o/p: 1210.73
 
TO_TIMESTAMP - This function converts a string to a timestamp.
 
Syntax : TO_TIMESTAMP( string1, [ format_mask ] [ 'nlsparam' ] )
Ex:
 
TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.
 
TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')
would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.
 
TO_TIMESTAMP_TZ - This function converts a string to a timestamp with time zone.
 
Syntax:TO_TIMESTAMP_TZ( string1 , [ format_mask ] [ 'nlsparam' ] )
Ex-
TO_TIMESTAMP_TZ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM')
would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.
 
TO_TIMESTAMP_TZ('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM')
would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.
x