Saturday, July 25, 2009

Operators in SQL

Operators are divided in to four categories.

1. Comparison operator

2. Arithmetic operator

3. Logical operator

4. Set operator

Comparison operator

Grater than (>)

Eg :

SELECT student_no

FROM student

WHERE mark>75;

Less Than

Eg :

SELECT student_no

FROM student

WHERE mark<40;

Less or Equal

Eg:

SELECT student_no

FROM student

WHERE mark <=40;

Equal

Eg:

SELECT student_no

FROM student

WHERE name = ‘Sanjaya’;

Not Equal

Eg:

SELECT student_no

FROM student

WHERE name <> ‘Sanjaya’;

Between

Eg:

SELECT student_no

FROM student

WHERE name, BETWEEN 85 AND 65

Not Between

Eg:

SELECT student_no

FROM student

WHERE name NOT BETWEEN ‘Sanjaya’ AND ‘CHANI’;

Like Operator

, ,

%----------------%

Begin Characters End

Like Operator is use for pattern machine (%) is use to match any string of length zero or more characters.

Sample Questions

Write a query for employee, the name starting with “m” that are in employee name column and include inside the employee table?

SELECT emp_name

FROM emp , ,

WHERE emp_name like m% ;

Write a query for employee, the name starting with “S” that are in employee name column and include inside the employee table and the name should be have four characters?

SELECT emp_name, emp_sal,emp_add

FROM emp_name , ,

WHERE emp_name Like S% _ _ _ _ ;

Write a program for employee, who are in the employee table and display their Name, Salary, and Commission. The employee in the who emp table, their name should be started with “M” and also have four characters?

Eg:

SELECT emp_name, sal, com

FROM emp , ,

WHERE emp_name like M %_ _ _ _ ;

Arithmetic operators

An arithmetic operator is a combination of one or more value, operators and function which evaluate a value.

* _ % +

Eg: SELECT emp_no, sal,sal*2”newsal”

FROM emp;

Out put

Emp_no

Sal

NewSal

1111

10500

21000

2222

5000

10000

Dual

It is a special table in the data dictionary call don my. It help’s to do the mathematics in SQL.

Eg: SELECT 10+20 “out put”

FROM dual;

Out put -

Out put

30

Logical Operators

1. AND

To satisfy the condition both must be true.

SELECT name, sal

FROM emp

WHERE name =’AA’ AND sal> 15000;

2. OR

To satisfy the condition one must be true.

SELECT name,sal

FROM emp

WHERE name=’AA’ SAL > 60000

3. NOT

It is use to reverse the result. It will execute if the condition is false.

SELECT *

FROM emp

WHERE NOT sal>10000;

Data types in Oracle

1. Number (size)

Store fixed and floating point numbers.

2. Chw (size)

Stored fixed length character. Specified length in byte can be stored up to 2000 characters.

3. Varcha (size)

This data type use to store variable length string having a maximum size up to 4000 bytes.

5. Date

It store date data, it’s has year, month, date, hours, minutes, & seconds. It can be displayed in various formats.

5. Long

It is a stranded data type can be stored up to 2GB.

Creating Tables

Syntax

CREATE TABLE table name

(Column 1 data type (size);

Column 2 data type (size));

Eg:

CREATE TABLE emp

(emp_no Number (10);

Name varcha (20);

Sal number (10));

How to Copy a Table?

Resources for copy a table

1. Backup the original

2. who archive them before remove

3. Give a copy some one else

Syntax

CREATE TABLE New table name

AS SELECT * FROM Old table name;

Eg:

CREATE TABLE emp1

AS SELECT * FROM emp;

How to add a column?

Syntax

ALTER TABLE table name

ADD column name data type (size);

Eg:

ALTER TABLE emp

ADD Nic No Number (10);

How to add multiple columns?

If you are adding more than one column the column definition should be enclosing parenthesis and separated by comas (‘).

ALTER TABLE emp

ADD (nic no number(10), DOB date);

How to drop a column?

Syntax

ALTER TABLE table name

DROP Column column name;

Eg:

ALTER TABLE emp

DROP COLUMN NIC_No;

How to modify a column?

Syntax

ALTER TABLE table name

MODIFY column name data type (Size);

Eg:

ALTER TABLE emp

MODIFY empno number (20);

How to drop a table?

Syntax

DROP TABLE table name

How to truncate table?

This command is a similar to drop command but is does not remove the structure of the table.

Syntax

TRUNCATE TABLE table name;

Eg:

TRUNCATE TABLE emp;

How to remove a table?

This is use to rename a table and other data base object such as views, stored procedure and functions.

Syntax

RENAME old table name to new table name

Eg:

RENAME emp to employee

Views

Views are virtual table, which use to data derived from different tables. Views provide simplicity as well as security.

Syntax

CREATE VIEW view name

AS SELECT column name1, column name2

FROM table name;

Eg

CREATE VIEW empview

AS SELECT empno, name

FROM emp;

How to drop views?

Syntax

DROP VIEW view name

Eg:

DROP VIEW empview

Constraints

Constraints are helping you to understand how tables and columns related to each other constraints define the action under which data is valid. Create table statements let you to enclose several different kind of constraints on a table such as candidate key, primary key, foreign key, and check conditions one or more constraints can be added to a table

No comments:

Post a Comment

Hi thank you for comment in Assignment Lanka " Book Mark on me' To future references.

Tag

Assignment Lanka Tag Cloud
Computer Networks The History of Local Area Networks, LAN, The Topologies of a Networks, LANs describe different types of transmission Medias, Local Area Networks Access Methods, Carrier Sense Multiple Access with Collision Detect, Development of LAN Technologies. LAN -Token Ring, LAN Ethernet Digital, LAN - Ethernet Sun microsystems, LAN - Ethernet Mixed Environment, LAN - Token Ring was introduced by IBM LAN - IBM implementation of Token Ring, Token Ring Novell, LAN Token Ring - in a mixed environment, LAN - Fiber Distributed Data Interface, LAN - ATM, LAN Components, LAN Switching Methods, Virtual Local Area Network, Port based VLAN, Mac based VLAN, Protocol based VLAN, User Base VLAN, PC networks Components, PC networks Shared resources, PC Network operating systems, PC networks Novell Netware, PC networks Windows NT, PC networks IBM LAN Server Computer Programming Languages HTML Language, The Generations of Programming Languages, Different types of High Level Languages, Different types of High Level Languages Disadvantages
Computer Networks - IBM LAN Server, Windows NT Networks, Novell Netware, Network operating systems, Networks Shared, Networks Components, User Base, Protocol based, Mac based, Port based, VLAN, LAN Switching, LAN Components, ATM, Fiber Data, Token Ring, Token Ring Novell, IBM implementation, Ethernet, Sun microsystems, Ethernet Digital, Token passing, LAN Technologies, CSMA/CD, Access Methods, Transmission, Networks, The History of Local Area Networks, LAN