Flavor of Fun

Group by clause

The group by is an useful command which helps to print the data based on a particular group. The other conditions that are used usually can be applied here.

SQL> select max(sal), deptno from emp group by deptno;

MAX(SAL) DEPTNO

———- ———-

8000 10

12910 20

11910 30

SQL> select max(sal), deptno from emp group by deptno having count(*) > 3;

MAX(SAL) DEPTNO

———- ———-

12910 20

11910 30

Tags: SQL by admin
19 Comments »

Count Function

In order to count the number of rows, count function is used. It can take three different arguments.

Count(*), Count(Col_name), Count(distinct col_name).

Count(*)

It counts all rows, inclusive of duplicates and Nulls.

select count(*) from emp; 13

Count(colname)

It counts the number of values present in the column without including Nulls.

select count(deptno) from emp; 13

Count(distinct colname)

It is similar to count(col_name) but eliminates duplicate values while counting.

select count(distinct deptno) from emp; 3

Tags: SQL by admin
263 Comments »

Next_day

SQL> select next_day(d,’tuesday’) from mtemp;

NEXT_DAY(

———

27-JAN-76

Round

SQL> select round(d,’year’) from mtemp (20-oct-98)

2 ;

ROUND(D,’

———

01-JAN-99

select trunc(d,’year’) from mtemp

TRUNC(D,’

———

01-JAN-98

Greatest

1* select greatest(sysdate,d) from mtemp

SQL> /

GREATEST(

———

07-MAR-05

07-MAR-05

Numeric Functions

select abs(-14.54) from dual;14.54

select ceil(14.82) from dual;15

select floor(14.82) from dual;14

select power(4,2) from dual;16

select mod(4,3) from dual;1

select trunc(100.256,2) from dual;100.25

select sqrt(2) from dual;1.414

Tags: SQL by admin
98 Comments »

Privilege Command

The Grant & Revoke are called as the privilege commands.

Grant Command

Grant privileges on <object-name > to <user name>;

Grant all on customer to user1;

Grant insert, update on customer to user1;

Revoke Command:

Revoke privileges on <object-name > from <user name>;

Revoke all on customer from user1;

Revoke insert, update on customer from user1;

Tags: SQL by admin
15 Comments »

Select Command

Select column_name …. From table_name;

Select * from emp;

Select no from emp;

Select distinct no from emp;

Select * from emp where no = 101;

Select no,name,dno from emp order by name;

Select no,name,dno from emp order by name,dno desc;

Update Command

Update table_name set field = value,… where condition;

Update emp set salary = 5000 where no = 101;

Delete Command

Delete from <table_name> where condition;

Delete from emp where dno = 10;

Tags: SQL by admin
874 Comments »

Integrity Constraints.

It is a mechanism used by oracle to prevent invalid data entry into the table. It is enforcing rule for the column in a table.

• Domain integrity constraints (Not null, Check)
• Entity integrity constraints (Unique, Primary key)
• Referential integrity constraints (Relationship between tables)

Not Null

SQL> create table mtemp(no number(3) constraint kk not null,cname varchar2(10));

Table created.

Check

SQL> create table mtemp(no number(3) check(no >10));

Table created.

Unique

SQL> create table mtemp(no number(3) unique);

Table created.

Primary Key

SQL> create table mtemp(no number(3),name varchar2(10),primary key(no));

Table created.

Foreign Key

SQL> create table mtemp1(no number(3),name varchar2(10), primary key(no))

Table created.

SQL> create table mtemp2(eno number(3) primary key,

2 no number(3),foreign key(no) references mtemp1(no))

Table created.

1 create table mtemp2(eno number(3) primary key,

2* no number(3),foreign key(no) references mtemp1(no) on delete cascade)

Table created.

Tags: SQL by admin
4 Comments »

Number

• It is used to store numbers.

Date

• It is used to store date and time in any table. Oracle database uses its own data format to store the date in a fixed length of 7 bytes each for century, month, day, year, hour, minute and second.
• The default date datatype is in the format “dd-mon-yy”.

Raw

• To store byte oriented data like binary data or byte strings.
• The maximum size of this data byte is 255 bytes.

Long Raw

• It is used to store binary data of variable length, which can have a maximum size of 2 GB.
• This data type cannot be indexed. Further all of the limitations faced by the long data type holds good for long raw data type also.

Tags: SQL by admin
2 Comments »

Character :

• Char is used when a fixed length character string is required.
• The length of this data type can vary between 1-2000 bytes. The default is one byte.

Varchar2

• It supports variable length character string.
• The maximum size of this datatype would be anywhere from 1-4000 bytes.

Long

• This data type supports variable character length.
• The maximum size is 2 GB.
• It is Similar to varchar2.
• Only one column in a table can to be allowed, which should not contain any of the unique or primary key constraints.
• The column can not be indexed.

Tags: SQL by admin
28 Comments »

Hi everybody…..Hereafter you will find some informative articles on SQL basics. I hope this will be interesting.

Database Models:

Object based logical model: It describes data and data relationship constraints.

Record based logical model: It describes data structure and access techniques.

The other classification would be

• Hierarchical
• Network
• Relational

Hierarchical Model

It has a simple structure and it is easy to use

The relationship is parent child.

It reduces the redundancy

Drawbacks

Leads to wastage of storage space

Many-to-Many relationship is not possible

Deletion of parent results in deletion of children which leads to anomaly.

Network Model

- It has improvement over Hierarchical model.

- Multiple parent-child relationships is possible.

- Many-to-Many relationship is possible

- Parent(node) & link.

- The speed of retrieving is more when compared to Hierarchical Model.

Drawbacks

- Complex to construct

- The pointer maintenance leads to drawbacks.

Relational Model

- The data is stored in rows & columns.

- Each column has a unique name called the attribute.

- Relational operators are allowed

- Mathematical theory

Relational Operators

- Single (Restrict, Project)

- Binary (union, intersect, product, join, difference, divide)

Tags: SQL by admin
429 Comments »