Scribd
Upload
78 views10 pages

Ex No 3

Uploaded by

k2210

This document demonstrates different SQL select statements and operators using sample data from tables. It shows how to use operators like comparison, arithmetic, logical and more to retrieve specific records based on conditions. Various SQL commands like select, create table, insert and delete are also used.

Copyright:

Attribution Non-Commercial (BY-NC)
Download as DOC, PDF, TXT or read online from Scribd
Download as doc, pdf, or txt

Ex No 3

Uploaded by

k2210
78 views10 pages
This document demonstrates different SQL select statements and operators using sample data from tables. It shows how to use operators like comparison, arithmetic, logical and more to retrieve specific records based on conditions. Various SQL commands like select, create table, insert and delete are also used.

Original Title

EX.NO.3

Copyright

© Attribution Non-Commercial (BY-NC)

Available Formats

DOC, PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

This document demonstrates different SQL select statements and operators using sample data from tables. It shows how to use operators like comparison, arithmetic, logical and more to retrieve specific records based on conditions. Various SQL commands like select, create table, insert and delete are also used.

Copyright:

Attribution Non-Commercial (BY-NC)
Download as DOC, PDF, TXT or read online from Scribd
Download as doc, pdf, or txt
78 views10 pages

Ex No 3

Uploaded by

k2210
This document demonstrates different SQL select statements and operators using sample data from tables. It shows how to use operators like comparison, arithmetic, logical and more to retrieve specific records based on conditions. Various SQL commands like select, create table, insert and delete are also used.

Copyright:

Attribution Non-Commercial (BY-NC)
Download as DOC, PDF, TXT or read online from Scribd
Download as doc, pdf, or txt
You are on page 1/ 10

EX.

NO:3

SELECT STATEMENTS AND OPERATORS


AIM:

To execute the different kinds of select statements and operations in SQL.

WORKING WITH SQL COMMANDS

CREATE

SQL> create table test105 as (select * from emp105 where eid=101);

Table created.

SQL> desc test105;

Name Null? Type


----------------------------------------- -------- ----------------------------

EID NUMBER(5)
NAME VARCHAR2(10)
DOJ DATE
AGE NUMBER(5)
SALARY NUMBER(10)

SQL> select * from test105;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
101 aaa 15-JAN-09 28 25000

SQL> select * from emp105;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
100 aaa 09-FEB-09 25 15000
101 aaa 15-JAN-09 28 25000
102 bbb 03-MAR-10 24 20000
103 ccc 07-MAY-10 26 25000

SQL> create table test1105 as ( select * from emp105 where eid=1);

Table created.
SQL> desc test1105;

Name Null? Type


----------------------------------------- -------- ----------------------

EID NUMBER(5)
NAME VARCHAR2(10)
DOJ DATE
AGE NUMBER(5)
SALARY NUMBER(10)

SQL> select * from test1105;

no rows selected

INSERT

SQL> insert into test105 (select * from emp105);

4 rows created.

SQL> select * from test105;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
101 aaa 15-JAN-09 28 25000
100 aaa 09-FEB-09 25 15000
101 aaa 15-JAN-09 28 25000
102 bbb 03-MAR-10 24 20000
103 ccc 07-MAY-10 26 25000

SQL> select rowid from emp105;

ROWID
------------------
AAADVOAABAAAI1aAAA
AAADVOAABAAAI1aAAB
AAADVOAABAAAI1aAAC
AAADVOAABAAAI1aAAD

SQL> select * from test105;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
101 aaa 15-JAN-09 28 25000
100 aaa 09-FEB-09 25 15000
101 aaa 15-JAN-09 28 25000
102 bbb 03-MAR-10 24 20000
103 ccc 07-MAY-10 26 25000

SQL> select rowid from test105;

ROWID
------------------
AAADVPAABAAAI1iAAA
AAADVPAABAAAI1jAAA
AAADVPAABAAAI1jAAB
AAADVPAABAAAI1jAAC
AAADVPAABAAAI1jAAD

SQL> delete from test105 where rowid='AAADVPAABAAAI1iAAA';

1 row deleted.

SQL> select * from test105;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
100 aaa 09-FEB-09 25 15000
101 aaa 15-JAN-09 28 25000
102 bbb 03-MAR-10 24 20000
103 ccc 07-MAY-10 26 25000

ALIAS

SQL> select salary sal from emp105;

SAL
----------
15000
25000
20000
25000

SQL> select * from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
SQLPLUS_PRODUCT_PROFILE TABLE
PRODUCT_PRIVS VIEW
PRODUCT_USER_PROFILE SYNONYM
EMP105 TABLE
SAMPLE TABLE
TEST105 TABLE
TEST1105 TABLE

7 rows selected.

SQL> desc emp105;

Name Null? Type


----------------------------------------- -------- ----------------------

EID NUMBER(5)
NAME VARCHAR2(10)
DOJ DATE
AGE NUMBER(5)
SALARY NUMBER(10)

ARITHMETIC OPERATORS

SQL> select name from emp105 where doj='15-jan-09';

NAME
----------
aaa

SQL> select eid+10,name from emp105;

EID+10 NAME
---------- ----------
110 aaa
111 aaa
112 bbb
113 ccc

SQL> update emp105 set eid=eid+10;

4 rows updated.

SQL> select * from emp105;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000
COMPARISON OPERATORS

SQL> select * from emp105 where eid in(111,115);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
111 aaa 15-JAN-09 28 25000

SQL> select * from emp105 where eid not in(111,115);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000

SQL> select * from emp105 where eid between 109 and 110;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000

SQL> select * from emp105 where eid not between 109 and 110;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000

SQL> select * from emp105 where name like 'a%';

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
100 aaa 09-FEB-09 25 15000
101 aaa 15-JAN-09 28 25000

SQL> select * from emp105 where name like 'b%';

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
102 bbb 03-MAR-10 24 20000

SQL> select * from emp105 where name like '%ccc%';


EID NAME DOJ AGE SALARY
---------- ---------- --------- ---------- ----------
103 ccc 22-OCT-10 26 25000

SQL> select * from emp105 where name like '%c%';

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
103 ccc 22-OCT-10 26 25000

SQL> select * from emp105 where name is not null;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000

SQL> select * from emp105 where name is null;

no rows selected

SQL> select * from emp105 where eid not in(select eid from emp105 where eid!=110
);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000

SQL> select * from emp105 where eid=(select max(salary) from emp105);

no rows selected

SQL> select * from emp105 where salary=(select max(salary) from emp105);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
111 aaa 15-JAN-09 28 25000
113 ccc 07-MAY-10 26 25000

SQL> select * from emp105 where salary>(select min(salary) from emp105);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000

SQL> select * from emp105 where salary<(select min(salary) from emp105);

no rows selected

SQL> select * from emp105 where salary<>(select min(salary) from emp105);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000

SQL> select * from emp105 where salary >= (select min(salary) from emp105);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000

SQL> select * from emp105 where salary!=(select min(salary) from emp105);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000

LOGICAL OPERATORS

SQL> select * from emp105 where eid=110 and name='mmm';

no rows selected

SQL> select * from emp105;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000
SQL> select * from emp105 where eid=110 and name in(select name from emp105 wher
e eid=110);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000

SQL> select * from emp105 where name not in(select name from emp105 where eid=11
0);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000

SQL> select * from emp105 where eid=110 or name in(select name from emp105 where
eid=112);

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000
112 bbb 03-MAR-10 24 20000

SQL> desc test105;

Name Null? Type


----------------------------------------- -------- ----------------------------

EID NUMBER(5)
NAME VARCHAR2(10)
DOJ DATE
AGE NUMBER(5)
SALARY NUMBER(10)

SQL> alter table test105 drop(eid);

Table altered.

SQL> select * from test105;

NAME DOJ AGE SALARY


---------- --------- ---------- ----------
aaa 09-FEB-09 25 15000
aaa 15-JAN-09 28 25000
bbb 03-MAR-10 24 20000
ccc 07-MAY-10 26 25000

SQL> drop table test105;

Table dropped.

SQL> create table test105 as(select * from emp105);

Table created.

SQL> create table tst1051 as (select eid from emp105);

Table created.

SQL> desc tst1051;

Name Null? Type


----------------------------------------- -------- -------------------

EID NUMBER(5)

SQL> select * from tst1051;

EID
----------
110
111
112
113

SQL> create table tst1052 as(select eid,name from emp105);

Table created.

SQL> delete from tst1052;

4 rows deleted.

SQL> select * from emp105;

EID NAME DOJ AGE SALARY


---------- ---------- --------- ---------- ----------
110 aaa 09-FEB-09 25 15000
111 aaa 15-JAN-09 28 25000
112 bbb 03-MAR-10 24 20000
113 ccc 07-MAY-10 26 25000
SQL> insert into tst1052(select eid,name from emp105);

4 rows created.

SQL> delete from tst1052;

4 rows deleted.

SQL> insert into tst1052(eid)(select eid from emp105);

4 rows created.

SQL> select * from tst1052;

EID NAME
---------- ----------
110
111
112
113

SQL> insert into tst1052(eid,name)(select eid,name from emp105);

4 rows created.

SQL> select * from tst1052;

EID NAME
---------- ----------
110
111
112
113
110 aaa
111 aaa
112 bbb
113 ccc

8 rows selected.

RESULT:

Thus the various select statements and operators were executed and verified successfully.

You might also like