SQL Character Functions

CHR returns the character having the binary equivalent to n as a VARCHAR2 value.
This function takes as an argument a NUMBER value,or any value that can be implicitly converted to NUMBER,and returns a character.
EX:SELECT CHR(67)CHR(65)CHR(84) FROM DUAL;Output is CAT

CONCAT returns char1 concatenated with char2.This function is equivalent to the concatenation operator (). The function is useful when there are spaces in the values to be concatenated. The concatenation operator does not permit spaces.

INITCAP returns char,with the first letter of each word in uppercase,all other letters in lowercase.Words are delimited by white space or characters that are not alphanumeric.
EX:SELECT INITCAP('the boy') FROM DUAL;Output is The Boy.

LOWER returns char, with all letters lowercase.
EX:SELECT LOWER('HOUSE') from dual;Output is house.

UPPER returns char, with all letters uppercase.
EX:SELECT LOWER('house') from dual;Output is HOUSE.

LPAD(exp1,n,exp2) returns expr1,left-padded to length n characters with the sequence of characters in expr2.This function is useful for formatting the output of a query.If expr2 is not specified,then the default is a single blank.If expr1 is longer than n,then this function returns the portion of expr1 that fits in n.The argument n is the total length of the return value as it is displayed on your terminal screen.
EX:SELECT LPAD('Home',10,'*') from dual;Output is ******Home.

RPAD(exp1,n,exp2) returns expr1,right-padded to n characters with expr2.If you do not specify expr2, then it defaults to a single blank. If expr1 is longer than n, then this function returns the portion of expr1 that fits in n.
The argument n is the total length of the return value as it is displayed on your screen.
EX:SELECT RPAD('Home',10,'*') from dual;Output is Home******.

LTRIM removes all characters contained in specified set in its .If you do not specify set,it defaults to a single blank.If char is a character literal,then you must enclose it in single quotes.Oracle Database begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.
EX:SELECT LTRIM('***WORD','*') FROM DUAL;Output is WORD.

RTRIM removes all of the characters that appear in set from the right end.If you do not specify set,it defaults to a single blank.If char is a character literal,then you must enclose it in single quotes.
EX:SELECT RTRIM('WORD***','*') FROM DUAL;Output is WORD.

TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character is a character literal,then it should be enclosed in single quotes.
EX:SELECT TRIM('**WORD***','*') FROM DUAL;Output is WORD.

REPLACE returns char with every occurrence of search_string replaced with replacement_string.If replacement_string is omitted or null,then all occurrences of search_string are removed.If search_string is null,then char is returned.
EX:SELECT REPLACE('RED','R','B') FROM DUAL;Output is BED.

SUBSTR functions return a portion of string,beginning at character position, substring_length characters long.If position is 0,then it is treated as 1.If position is positive,it counts from the beginning of string to find the first character.If position is negative,it counts backward from the end of string.If substring_length is omitted, then Oracle returns all characters to the end of string. If substring_length is less than 1, then Oracle returns null.
EX:SELECT SUBSTR('MYHOUSE',3,4) "Substring" FROM DUAL;Output is HOUS

TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. If expr is a character string, then you must enclose it in single quotation marks.TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.
EX:SELECT ename from emp WHERE SOUNDEX(ename)= SOUNDEX('SMYTHE');

SQL Numeric Functions

Some of the most commonly used numeric functions are:

ABS(n) returns the absolute value of n.
ex:SELECT ABS(-15) FROM DUAL;This gives and output of 15.

CEIL(n) returns smallest integer greater than or equal to n.
EX:SELECT CEIL(15.7) FROM DUAL;Output is 16.

FLOOR(n) returns largest integer equal to or less than n
EX:SELECT FLOOR(15.7) FROM DUAL;Output is 15.

MOD(m,n) returns the remainder of m divided by n. Returns m if n is 0.
SELECT MOD(11,4) FROM DUAL;Output is 3.

POWER(m,n) returns m raised to the nth power. The base m and the exponent n can be any numbers, but if m is negative, then n must be an integer.
EX:SELECT POWER(3,2) FROM DUAL;Output is 9

ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places.The argument integer can be negative to round off digits left of the decimal point.
EX1:SELECT ROUND(15.193,1) FROM DUAL;Output is 15.2
EX2:SELECT ROUND(1.5) FROM DUAL;Output is 2.

TRUNC returns n truncated to m decimal places. If m is omitted, then n is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.
EX:SELECT TRUNC(15.79,1) FROM DUAL;Output is 15.7.

SIGN(n) returns the sign of n.
The sign is -1 if n is < n ="0"> 0.
EX:SELECT SIGN(-15) FROM DUAL;Output is -1.

SQRT(n) returns the square root of n.
EX:SELECT SQRT(16) FROM DUAL;Output is 4.

WIDTH_BUCKET lets you construct equiwidth parts, in which the part range is divided into intervals that have identical size.
EX: When u give WIDTH_BUCKET(credit_limit, 100, 5000, 10) in select statement,it divides the credit_limit into 10 equal parts and and all those credits that r below 500 fall under bucket 1 and from 500-1000 comes bucket 2 and from 1000 to 1500 comes bucket 3 and so on till bucket 10 to make a total credit_limit of 5000.

Basic PLSQL DataTypes

Here are some of the most commonly used datatypes:

CHAR is a fixed length datatype.If you don't specify a max size, it defaults to 1. The maximum size for a CHAR in a column is 2000 bytes.CHAR retains any data and space pads the values.

VARCHAR2 is a variable length string that must have it's maximum length declared before use.In Oracle 10g,a VARCHAR2 column may be up to 4000 bytes.In this the unused space is released back.

NUMBER(p,s) is a numeric data type having precision p and scale s.The precision p can range from 1 to 38.The scale s can range from -84 to 127.
EX:-Number(5,2) means 5 spaces will be alloted in which scaling is 2 digits and precision is 3 digits.Here scale indicates the number of digits that can be given after decimal point.

FLOAT datatype stores zero,positive,and negative floating-point numbers.It doesnt have any scaling factor and usually used in business.

DATE is a date datatype that has the ability to store the month,day,year, century,hours,minutes,and seconds.
When 2 DATE datatypes are substracted then,it gives the difference of days but when an exact difference is needed TIMESTAMP datatype helps.

TIMESTAMP datatype stores all the information that the DATE datatype stores,but also includes fractional seconds.

VARCHAR is not supported and is reserved for future use

SQL-Basic Concepts

SQL stands for Structured Query language. It is a non procedural language and it is a database language.

There are 3 broad categories in SQL commands.

• Data Definition language (DDL)
• Data Manipulation Language (DML)
• Transaction Control Language(TCL)
  

Now lets look at the various commands that come under each of the categories.

Create,Alter, Drop come under DDL.
Insert,Select,Delete,Update fall under DML.
Rollback,Savepoint,Commit fall under TCL.

Now lets look in detail.

DDL:

CREATE TABLE is the command used to create tables.

SYNTAX: CREATE TABLE tablename (col-name1 col-definition1, col-name2 col-definition2…);

EX:Create table student(stname varchar2(20),stno number(4));

ALTER TABLE command is used to modify a table definition or can be used to alter column definition in table.

SYNTAX: ALTER TABLE tablename MODIFY (Column definition);

EX1:alter table student rename to student_details;
Ex2:alter table student_details add stmarks number(3);

DROP TABLE:This command is used to remove an existing table permanently from database.

SYNTAX: DROP TABLE tablename; EX:Drop table student_details;

DML:

SELECT: This is used to select values or data from table.

SYNTAX: SELECT column name1, column name2, …. FROM tablename;

If all column values in a table are to be selected, then we use SELECT * from tablename;

EX1:select stname from student_details;
EX:2:select * from student_details;

INSERT:This command is used to insert rows into the table.

SYNTAX: INSERT INTO tablename VALUES (value,value,….);

EX:Insert into student_details values('A',01,95)

DELETE: This command is used to delete rows from a table.

SYNTAX: DELETE FROM tablename WHERE condition;

The WHERE clause is optional and when a condition is used in where clause of delete ,then the rows that satisfy the condition gets fetched from table and get deleted.

EX1:Delete from student_details where stno=01;
EX2:Delete from student_details;

In EX2 all the rows in the table gets deleted but still the table structure retains in the database.This is one major difference between delete and drop where if drop is used the entire table is deleted but unconditional delete deletes entire data but table structure remains.

UPDATE: This SQL command is used to modify the values in an existing table.

SYNTAX: UPDATE tablename SET columnname = value, columnname = value,….. WHERE condition;

If the WHERE clause is not used then,the value that is given to set, is set to entire coloumn.If WHERE is used,the rows which satisfies the WHERE condition are fetched and only for these rows the column values we placed in SET statement gets updated.

EX:Update student_details set stmarks=99 where stno=01;
This updates the marks of student no 01 as 99.

TCL:

ROLLBACK:-This is used to undo the current transaction.

SYNTAX: ROLLBACK;

COMMIT:-This is used to make all changes permanent in database and also marks the end of transaction.

SYNTAX: COMMIT;

It is always good practice to commit changes to database at regular intervals since it helps to avoid loss of data when computer shuts down due to unavoidable reasons.

SAVEPOINT: This is used to identify a point in a transaction to which we can later rollback.

SYNTAX: SAVEPOINT savepoint_identifier;

Difference between Database and Instance in Oracle

DATABASE:

Database is the term used to describe the physical files that store information.

There are three types of physical files:

• Data files:-These store all the data that is needed by a database engine.
• Control files:-These store metadata(ie data about data) about the rest of the database for use by the Oracle engine.
• Redo-log files:-These files record all changes made to the data and serves as backup and helps in recovery.

INSTANCE:

Database files needs specific memory structures and processes to interact with data in the database.Without them,there is no use with having only the database files.

So to put in a nutshell,A memory structure and the background process that is needed to access data in database is what is called an instance.

An instance has two major memory structures:

The System Global Area or Shared Global Area (SGA):-It stores information in memory that is shared by the various processes in Oracle.

The Program Global Area or Private Global Area (PGA):-It contains information that is private to a particular process.

SGA contains:

Database buffer cache that is used to cache information read from the data files.
Data dictionary cache used to cache metadata information.
Library cache that caches recently used SQL and PL/SQL statements.

PGA allocates memory for information such as variables,arrays,and cursor information that is private to each process.

The instance also contains numerous background processes that cooperate to fulfill all the various functions needed like Database Writer that writes all changes to the database,and Process Monitor that cleans up after failed user processes.

For an SQL server,A database comprises of a collection of objects such as Tables.An SQL server installation may contain many databases.

SQL Practice Test 4

1. If sum(sal) of a department is greater than 10,000 ,then return it as highest valued dept, if sum(sal)>5000 and less than 10000 return it as mediumvalued, else low valued department.
2. Get the count of distinct job and manager for each department.
3. Get the day wise count of employees hired.
4. Get the count of employees department wise whose hiredate is less than Jan 2005.
5. Get the department wise, manager wise, job wisesum(sal) and also department wise salary, manager wisesalary, job wise salary, department manager wisesalary.
6. Get me minimum salary of employee in department 10.If the minimum salary is greater that maximum commission in that department, display it as null.
7. Get the average salary of employees who were hired during 1980-1985 department wise.
8. Get me average salary of employees who were hired year wise and also department wise.
9. Get me sum(sal)-sum(comm) job wise for all employees.
10. Get me all employees whose salary is greater than average salary in their department.

SQL Practice Test 3

1. Display the avg(sal) of each department sorted according to sum of salaries of each dept wise.
2. Display the number of employees for all the jobs.
3. Display the sum of average salaries of all the jobs.
4. Display the sum of all the average salaries of all the employees who is working as clerk or manager.
5. Display all enames and dnames of emp and dept tables.
6. Display the employee information who is working as manager in department ‘SALES’.
7. Display the grades of employees based on their salaries from grade and emp table.
8. Display the experience of employees in months as well as in years without using library functions.
9. Display the employee name and his department name he is working with.
10. Display the sum of all the employees who has salary greater than average salaries of their respective departments.
11. Display the employee information whose salary is greater than the average salary of all the employess.
12. Display the job,avg(sal) from emp table whose salary of each employee is greater than the maximum average salary of job.
13. List all the employees names who draws max salary for each department.
14. List all the employess whose sal is greater than all average salaries of all jobs.
15. List only first five employess who draws the highest salary from the emp table.
16. Edit all the names, jobs of an employee to make only their initial letter as capital.
17. Insert the dname of the dept with the job(having dname of its deptno as its suffix) of the emp table and make the initial letter capital.
18. Delete only those rows that are of dept 30.
19. Insert any one duplicate record and delete it.
20. Delete all the rows in the student table.
21. Add a primary constraint to the existing student table field stid.
22. Delete the table student.
23. Calculate the cumulative sal of all the employees.
24. Select an employee who dont have a manager.
25. List all the dept names that are contained in the emp table.