(Previous) < 1 2 3 4 5 > (Next)

Writing Basic SQL Statements

List the capabilities of SQL SELECT statements

        
 select * ....  [everything] 
 select distinct .... [distinct values in selection] 
 select :whatever,:other ....  [using bind variables]
 select .......for update of ...[Aquires a lock]

Execute a basic SELECT statement

select * from users where user_id=3 
select email,user_name from employees where name='Daniel'

Differentiate between SQL statements and SQL*Plus commands.

SQL* Plus commands are: COPY, EDIT, EXECUTE,EXIT, GET, HOST,QUIT, PASSWORD,RUN,SAVE,SET,SPOOL,START

SQL commands are: ALTER, ANALYZE, AUDIT,CONNECT, CREATE, DELETE ,DROP , GRANT , INSERT, LOCK, NOAUDIT, RENAME, REVOKE, SELECT, SET ROLE, SET TRANSACTION, TRUNCATE, UPDATE.

PL/SQL commands : BEGIN, DECLARE

Other SQL*Plus commands: APPEND,CHANGE, CLEAR BUFFER,DEL,INPUT,LIST, DEFINE ,SHOW,RECOVER , DESCRIBE

NOTE: In case disabling of any of the previous commands is required, this can be done through the PRODUCT_USER_PROFILE table:

Restricting and Sorting Data

Limit the Rows retrieved by a query

 select animals from zoo where classification='mammals'; 

 select cars from lot where year > to_date('2000-01-01','YYYY-MM-DD') ; 

 select boat_names from triton where boat_names like '__uk%'; 

 select count(*) from classifieds where to_date(expires,'YYYY-MM-DD') >= to_date(sysdate,'YYYY-MM-DD') or expires is null; 

Sort the rows retrieved by a query

 select houses from city where city='Ensenada' order by block_id;

 select cuartos from hoteles order by precio desc;

 select cuarto from hoteles order by precio asc;[asc is the default 

 select empleados from empresa order by upper(nombres) asc;

 select rios from continente order by lugar,longitud,profundidad;
   

Single Row Functions

Describe various types of functions available in SQL.

Character, Date and Number Functions.

Use character,date and number functions in SELECT statements

There are functions returning Character Values :
1. CHR : Returns the character value given character number

   select CHR(37) a, CHR(100) b, CHR(110) c from dual; RETURNS:

 
		     A B C
		     - - -
		     % d n
		     

2. CONCAT :Returns string1 concatenated with string2

   select CONCAT('perro','gato') from dual; RETURNS:

  

                    CONCAT('P
		    ---------
		    perrogato
		    

3. INITCAP :Returns the string with the first character in every word capatilzed and the rest lowercase

   select INITCAP('esto ES paRA el FUNcioNaMienTo de oracle ') "La prueba" from dual; RETURNS:

  
                 La prueba
		 -----------------------------------------
		 Esto Es Para El Funcionamiento De Oracle
		 

4. LOWER :Returns the string in all lowercase

   select LOWER ('ESTO ES paRA el FUNcioNaMienTo de oracle ') "Minusculas" from dual; RETURNS:

  
                Minusculas
		-----------------------------------------
		esto es para el funcionamiento de oracle
		

Other Character functions include : LPAD, LTRIM, NLS_INITCAP, NLS_LOWER, NLS_UPPER, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, SUBSTRB, TRANSLATE, UPPER, ASCII, INSTR, INSTRB, LENGTH, LENGTHB, NLSSORT

There are numeric functions :
1. ABS : Returns the absolute value of a number

   select ABS(-24),ABS(-414),ABS(24) from dual; RETURNS:

 
              ABS(-24)  ABS(-414)      ABS(24)
	      ---------- ---------- ----------
	      	24	  414	      24

2. FLOOR: Returns the largest integer equal to or less than the number

   select FLOOR(-131.7), FLOOR(23.5) from dual; RETURNS:

 
             FLOOR(-131.7) FLOOR(23.5)
	     ------------- -----------
	     	 -132	       23

3. ROUND : Returns the left number rounded to right number places of the decimal point

   select ROUND(1.57),ROUND(1.57,1),ROUND(20.87,2) from dual; RETURNS:

            ROUND(1.57) ROUND(1.57,1) ROUND(20.87,2)
	    ----------- ------------- --------------
	    	  2	      1.6	   20.87

Other Number functions include: ACOS, ASIN, ATAN, ATAN2, CEIL, COS, COSH, EXP, LN, LOG, MOD, POWER, SIGN, SIN, SINH, SQRT, TAN, TANH, TRUNC

There are date functions :
1. ADD_MONTHS: Returns the date plus x months specfied.

   select ADD_MONTHS('1999-12-2',12) from dual; RETURNS:

 
  
                  ADD_MONTHS
		  ----------
		  2000-12-02

Other Date functions include: LAST_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, ROUND, SYSDATE, TRUNC

Describe the use of conversion functions.

These are mostly used within PL/SQL in order to coerce a datatype to explicitly be converted into another type,the most commonly user conversion functions are TO_DATE and TO_CHAR. Among other conversion functions are : CHARTROWID, CONVERT, HEXTORAW, RAWTOHEX, ROWIDTOCHAR, TO_LABEL, TO_MULTI_BYTE, TO_NUMBER, TO_SINGLE_BYTE.

(Previous) < 1 2 3 4 5 > (Next)