Practical No 5 DBMS

PRACTICAL NO 5:-Execute DML Commands in SQL
NAME:-Deepak Yadav
ROLLNO-220463
PRACTICAL RELATED QUESTIONS
Q.1)
SQL> create table emp16(emp_no number(10),e_name

varchar2(10),dept_no number(10),dept_name
varchar2(10),job_id number(10),salary
number(10),hiredate date);
Table created.
Q.2)
SQL> insert all

2 INTO
emp16(emp_no,e_name,dept_no,dept_name,job_id,salar
y,hiredate)
values(25,'SAAD',25,'COMP',220425,2500000,'16-AUG-
2006')
3 INTO
y,hiredate)
values(26,'SAUD',25,'COMP',220426,2500000,'16-AUG-
2006')
4 INTO
y,hiredate)
values(27,'KABIR',25,'COMP',220427,2500000,'16-
AUG-2006')
5 select *from DUAL;

3 rows created.
Q.3)
A.)
insert into emp16
values(1,'SHREYAS',100,'SALES',111,40000,'28-sep-
2014');
1 row created.
B.)
SQL> delete from emp16 where e_name='SHREYAS';
1 row deleted.
SQL> select*from emp16;
EMP_NO E_NAME DEPT_NO DEPT_NAME JOB_ID

SALARY HIREDATE
---------- ---------- ---------- ----------
---------- ---------- ---------
25 SAAD 25 COMP
220425 2500000 16-AUG-06
26 SAUD 25 COMP
220426 250000 18-AUG-06
27 KABIR 25 COMP
220426 220000 19-AUG-06
C.)
SQL> update emp16 set salary=50000 where

dept_name='PRODUCTION';
1 row updated.

EMP_NO E_NAME DEPT_NO DEPT_NAME
JOB_ID SALARY HIREDATE
---------- ---------- ---------- ----------
---------- ---------- ---------
25 SAAD 25 COMP
220425 2500000 16-AUG-06
26 SAUD 25 COMP
220426 250000 18-AUG-06
27 KABIR 25 COMP
220426 220000 19-AUG-06
1 AFSHAAN 101 PRODUCTION
111 50000 28-SEP-14
D.)
SQL> select*from emp16 where salary>=25000 and

salary<=60000;

---------- ---------- ---------- ----------
---------- ---------- ---------
111 50000 28-SEP-14
EXERCISE.
Q.1)
SQL> create table emp16(emp_no number(10),e_name

varchar2(10),dept_no number(10),dept_name
varchar2(10),job_id number(10),salary
number(10),hiredate date);
Table created.
A.)
SQL> insert all

2 INTO
y,hiredate)
values(25,'SAAD',25,'COMP',220425,2500000,'16-AUG-
2006')
3 INTO
y,hiredate)
values(26,'SAUD',25,'COMP',220426,2500000,'16-AUG-
2006')
4 INTO
y,hiredate)
values(27,'KABIR',25,'COMP',220427,2500000,'16-
AUG-2006')
5 INTO
y,hiredate)
values(1,'AFSHAAN',101,'PRODUCTION',111,50000,'28-
SEP-2014')
6 INTO
y,hiredate)
values(2,'NOFIL',101,'PRODUCTION',222,30000,'10-
sep-2014')
7 Select *from DUAL;

5 rows created.
B.)

---------- ---------- ---------- ----------
---------- ---------- ---------
25 SAAD 25 COMP
220425 2500000 16-AUG-06
26 SAUD 25 COMP
220426 250000 18-AUG-06
27 KABIR 25 COMP
220426 220000 19-AUG-06
111 50000 28-SEP-14
2 NOFIL 101 PRODUCTION
222 30000 10-SEP-14
C.)
SQL> select*from emp16 where

Dept_name='PRODUCTION' and salary>40000;

---------- ---------- ---------- ----------
---------- ---------- ---------
111 50000 28-SEP-14
D.)
SQL> update emp16 set salary=30000 where

dept_name='SALES';
1 row updated.
E.)
SQL> delete from emp16 where dept_name='SALES' and

salary<10000;
1 row deleted.
F.)
SQL> select *from emp16 where dept_name='SALES';

---------- ---------- ---------- ----------
---------- ---------- ---------
3 ADNAN 101 SALES
223 20000 10-SEP-14
4 AVINASH 101 SALES
223 60000 10-SEP-14
5 ANSH 101 SALES
223 69000 10-SEP-14
Q.2)
SQL> create table dept16(dept_no number(10)

primary key,dpt_name varchar2(10),location
varchar2(10),job_id number(10));
Table created.
SQL> create table emp16(emp_no number(10),emp_name

varchar2(10),salary number (10),ph_no
number(10),dept_no number(10) references
dept16(dept_no));
Table created.
A.)
SQL> insert into dept16

values(101,'COMP','MUMBAI',220425);
1 row created.

values(102,'SALES','MUMBAI',220426);
1 row created.

values(103,'PRODUCTION','MUMBAI',220427);
1 row created.

values(104,'MANAGER','MUMBAI',220428);
1 row created.

values(105,'PURCHASE','MUMBAI',220429);
1 row created.

values(105,'PURCHASE','MUMBAI',220429);
1 row created.
SQL> insert into emp16

values(25,'SAAD',2500000,8976498921,101);
1 row created.

values(26,'NOFIL',250000,8976498920,102);
1 row created.

values(27,'AFSHAAN',25000,8976498970,103);
1 row created.

values(28,'SAUD',290000,8976898970,104);
1 row created.
B.)
SQL> select *from emp16;
EMP_NO EMP_NAME SALARY PH_NO

DEPT_NO
---------- ---------- ---------- ----------
----------
25 SAAD 2500000 8976498921
101
26 NOFIL 250000 8976498920
102
27 AFSHAAN 25000 8976498970
103
28 SAUD 290000 8976898970
104
SQL> select *from dept16;

DEPT_NO DPT_NAME LOCATION JOB_ID
---------- ---------- ---------- ----------
101 COMP MUMBAI 220425
102 SALES MUMBAI 220426
103 PRODUCTION MUMBAI 220427
104 MANAGER MUMBAI 220428
105 PURCHASE MUMBAI 220429
C.)
SQL> update emp16 set dept_no=201 where

emp_name='SHREYAS';
1 row updated.
D.)
SQL> delete from emp16 where dept_no=201;
1 row deleted.
E.)
SQL> select *from emp16;
EMP_NO EMP_NAME SALARY PH_NO

DEPT_NO
---------- ---------- ---------- ----------
----------
25 SAAD 2500000 8976498921
101
26 NOFIL 250000 8976498920
102
27 AFSHAAN 25000 8976498970
103
28 SAUD 290000 8976898970
104
SQL> select *from dept16;
DEPT_NO DPT_NAME LOCATION JOB_ID

---------- ---------- ---------- ----------
101 COMP MUMBAI 220425
102 SALES MUMBAI 220426
103 PRODUCTION MUMBAI 220427
104 MANAGER MUMBAI 220428
105 PURCHASE MUMBAI 220429
106 RRR MUMBAI 220429
201 RAR MUMBAI 220429
7 rows selected.
SQL> Spool off;

Practical No 5 DBMS

Uploaded by

Document Informationclick to expand document information

Document Informationclick to expand document information

Copyright:

Available Formats

Practical No 5 DBMS

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Practical No 5 DBMS

Uploaded by

Copyright:

Available Formats

PRACTICAL NO 5:-Execute DML Commands in SQL

PRACTICAL RELATED QUESTIONS

SQL> create table emp16(emp_no number(10),e_name

SQL> insert all

5 select *from DUAL;

SQL> select*from emp16;

EMP_NO E_NAME DEPT_NO DEPT_NAME JOB_ID

SQL> update emp16 set salary=50000 where

SQL> select*from emp16;

SQL> select*from emp16 where salary>=25000 and

EMP_NO E_NAME DEPT_NO DEPT_NAME

SQL> create table emp16(emp_no number(10),e_name

SQL> insert all

7 Select *from DUAL;

SQL> select*from emp16;

EMP_NO E_NAME DEPT_NO DEPT_NAME

SQL> select*from emp16 where

EMP_NO E_NAME DEPT_NO DEPT_NAME

SQL> update emp16 set salary=30000 where

SQL> delete from emp16 where dept_name='SALES' and

SQL> select *from emp16 where dept_name='SALES';

EMP_NO E_NAME DEPT_NO DEPT_NAME

SQL> create table dept16(dept_no number(10)

SQL> create table emp16(emp_no number(10),emp_name

SQL> insert into dept16

SQL> insert into dept16

SQL> insert into dept16

SQL> insert into dept16

SQL> insert into dept16

SQL> insert into dept16

SQL> insert into emp16

SQL> insert into emp16

SQL> insert into emp16

SQL> insert into emp16

SQL> select *from emp16;

EMP_NO EMP_NAME SALARY PH_NO

SQL> select *from dept16;

SQL> update emp16 set dept_no=201 where

SQL> delete from emp16 where dept_no=201;

SQL> select *from emp16;

EMP_NO EMP_NAME SALARY PH_NO

SQL> select *from dept16;

DEPT_NO DPT_NAME LOCATION JOB_ID

SQL> Spool off;

You might also like