Scribd
Upload
6 views13 pages

SQL Table Creation and Management Guide

The document outlines the creation and manipulation of SQL tables, including primary and foreign keys, as well as various SQL commands such as INSERT, UPDATE, DELETE, and RENAME. It details the structure of the 'food' and 'hotel' tables, along with operations performed on them, including altering columns and managing data. Additionally, it covers transaction control commands like COMMIT, ROLLBACK, and SAVEPOINT, as well as granting permissions on tables.

Uploaded by

Vasanth B
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
6 views13 pages

SQL Table Creation and Management Guide

The document outlines the creation and manipulation of SQL tables, including primary and foreign keys, as well as various SQL commands such as INSERT, UPDATE, DELETE, and RENAME. It details the structure of the 'food' and 'hotel' tables, along with operations performed on them, including altering columns and managing data. Additionally, it covers transaction control commands like COMMIT, ROLLBACK, and SAVEPOINT, as well as granting permissions on tables.

Uploaded by

Vasanth B
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATING PRIMARY KEY TABLE:

create table food


2 (fid number(2) primary key,
3 item varchar(20) not null,
4 price number(5) not null);

Table created.

SQL> set lines 100 pages 100;


SQL> select *
2 from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
FOOD TABLE
SALGRADE TABLE

CREATING FORIEGN KEY TABLE :

create table hotel


2 (hid number(2) primary key,
3 hname varchar(30) not null,
4 loc varchar(20) not null,
5 fid number(2),
6 foreign key(fid) references food(fid));

Table created.

creating the duplicate table:

create table employee


2 as
3 select * from emp;

Table created.

SQL> select *
2 from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEE TABLE
FOOD TABLE
HOTEL TABLE
SALGRADE TABLE

7 rows selected.
ALTER :

alter table food


2 add colour varchar(10) not null;

Table altered.

desc food
Name Null? Type
----------------------------------------------------- --------
------------------------------------
FID NOT NULL NUMBER(2)
ITEM NOT NULL VARCHAR2(20)
PRICE NOT NULL NUMBER(5)
COLOUR NOT NULL VARCHAR2(10)

alter table food


2 rename column price to amt;

Table altered.

desc food
Name Null? Type
----------------------------------------------------- --------
------------------------------------
FID NOT NULL NUMBER(2)
ITEM NOT NULL VARCHAR2(20)
AMT NOT NULL NUMBER(5)
COLOUR NOT NULL VARCHAR2(10)

alter table food


2 drop column colour;

Table altered.

SQL> desc food


Name Null? Type
----------------------------------------------------- --------
------------------------------------
FID NOT NULL NUMBER(2)
ITEM NOT NULL VARCHAR2(20)
AMT NOT NULL NUMBER(5)

select *
2 from tab;

RENAME COMMAND :

rename food to dish;

Table renamed.

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DISH TABLE
EMP TABLE
EMPLOYEE TABLE
HOTEL TABLE
SALGRADE TABLE

7 rows selected.

TRUNCATE COMMAND:

truncate table employee;

Table truncated.

select *
2 from employee;

no rows selected

DROP COMMAND:

drop table employee;

Table dropped.

SQL> select *
2 from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BIN$alW92kFnSLGjh157xhrGsg==$0 TABLE
BONUS TABLE
DEPT TABLE
DISH TABLE
EMP TABLE
HOTEL TABLE
SALGRADE TABLE

7 rows selected.

flashback table employee to before drop;

Flashback complete.

SQL> select *
2 from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DISH TABLE
EMP TABLE
EMPLOYEE TABLE
HOTEL TABLE
SALGRADE TABLE

7 rows selected.

drop table employee purge;

Table dropped.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DISH TABLE
EMP TABLE
HOTEL TABLE
SALGRADE TABLE

6 rows selected.

rename dish to food;

Table renamed.

DML:

INSERT CPMMAND:

insert into food values(10,'idli',30);

1 row created.

SQL> insert into food values(20,'dosa',50);

1 row created.

SQL> insert into food values(30,'pulav',50);

1 row created.

SQL> insert into food values(40,'puliyogare',40);

1 row created.

select *
2 from food;

FID ITEM AMT


---------- -------------------- ----------
10 idli 30
20 dosa 50
30 pulav 50
40 puliyogare 40
50 bbb 40

SQL> insert into food values(50,'bbb',40);

1 row created.

UPDATE COMMAND:

update food
2 set item = 'rice bath'
3 where fid = 50;

1 row updated.

SQL> select *
2 from food;

FID ITEM AMT


---------- -------------------- ----------
10 idli 30
20 dosa 50
30 pulav 50
40 puliyogare 40
50 rice bath 40

DELETE COMMAND:

delete from food


2 where fid=30;

1 row deleted.

SQL> select *
2 from food;

FID ITEM AMT


---------- -------------------- ----------
10 idli 30
20 dosa 50
40 puliyogare 40
50 rice bath 40
SQL*Plus: Release [Link].0 Production on Mon Apr 7 13:56:53 2025

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: SCOTT


Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release [Link].0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select *
2 from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DISH TABLE
EMP TABLE
EMPLOYEES TABLE
HOTEL TABLE
LAPTOP TABLE
SALGRADE TABLE

8 rows selected.

SQL> RENAME DISH TO FOOD


2 RENAME DISH TO FOOD;
RENAME DISH TO FOOD
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

SQL> RENAME DISH TO FOOD;

Table renamed.

SQL> select *
2 from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEES TABLE
FOOD TABLE
HOTEL TABLE
LAPTOP TABLE
SALGRADE TABLE

8 rows selected.

SQL> select *
2 FROM LAPTOP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300


30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500


30

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400


30

7698 BLAKE MANAGER 7839 01-MAY-81 2850


30

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000


20

7839 KING PRESIDENT 17-NOV-81 5000


10

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100


20

7900 JAMES CLERK 7698 03-DEC-81 950


30

EMPNO ENAME JOB MGR HIREDATE SAL COMM


---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300


10

14 rows selected.

SQL> SET LINES 100 PAGES 100


SQL> select *
2 from food;

no rows selected

SQL> insert into food('10','DOSA',40);


insert into food('10','DOSA',40)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into food values('10','DOSA',40);

1 row created.

SQL> insert into food values('20','IDLI',20);

1 row created.

SQL> insert into food values('30','PULAV',50);

1 row created.

SQL> insert into food values('40','POORI',40);

1 row created.

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
30 PULAV 50
40 POORI 40

SQL> UPDATE FOOD


2 [Link]='POORI'
3
SQL> UPDATE FOOD
2 [Link]='PONGAL'
3 WHERE FID=30;
[Link]='PONGAL'
*
ERROR at line 2:
ORA-01747: invalid [Link], [Link], or column specification

SQL> UPDATE FOOD


2 SET FNAME='POORI'
3 WHERE FID=30;

1 row updated.

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
30 POORI 50
40 POORI 40
SQL> COMMIT
2 COMMIT;
COMMIT
*
ERROR at line 2:
ORA-02185: a token other than WORK follows COMMIT

SQL> UPDATE FOOD


2 SET FNAME='PONGAL'
3 WHERE FID=30;

1 row updated.

SQL> DELETE FROM FOOD


2 WHERE FID=30;

1 row deleted.

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
40 POORI 40

SQL> COMMIT;

Commit complete.

SQL> ROLLBACK
2 ROLLBACK;
ROLLBACK
*
ERROR at line 2:
ORA-02181: invalid option to ROLLBACK WORK

SQL> ROLLBACK;

Rollback complete.

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
40 POORI 40

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
40 POORI 40

SQL> insert into food values('30','PULAV',50);

1 row created.

SQL> SAVEPOINT I;

Savepoint created.

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
40 POORI 40
30 PULAV 50

SQL> UPDATE FOOD


2 SET FNAMAE ='CURDRICE'
3 WHERE FID=40;
SET FNAMAE ='CURDRICE'
*
ERROR at line 2:
ORA-00904: "FNAMAE": invalid identifier

SQL> UPDATE FOOD


2 SET FNAME ='CURDRICE'
3 WHERE FID=40;

1 row updated.

SQL> SAVEPOINT U;

Savepoint created.

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
40 CURDRICE 40
30 PULAV 50

SQL> ROLLBACK TO U;

Rollback complete.

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
40 CURDRICE 40
30 PULAV 50

SQL> ROLLBACK TO I;

Rollback complete.

SQL> select *
2 from food;

FID FNAME AMT


---------- --------------- ----------
10 DOSA 40
20 IDLI 20
40 POORI 40
30 PULAV 50

----> DCL

SQL>SQL*Plus: Release [Link].0 Production on Tue Apr 8 12:34:04 2025

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: scott


Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release [Link].0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET LINE 100 PAGES 100


SQL> SELECT *
2 FROM TAB;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEES TABLE
FOOD TABLE
HOTEL TABLE
LAPTOP TABLE
SALGRADE TABLE

8 rows selected.

SQL> GRANT SELECT ON EMP TO HR;

Grant succeeded.

SQL> CONN
Enter user-name: HR
Enter password:
Connected.
SQL> select *
2 from emp;
from emp
*
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> select *
2 from [Link];

EMPNO ENAME JOB MGR HIREDATE SAL COMM


DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> conn
Enter user-name: scott
Enter password:
Connected.
SQL> select *
2 FROM TAB;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEES TABLE
FOOD TABLE
HOTEL TABLE
LAPTOP TABLE
SALGRADE TABLE

8 rows selected.

SQL> REVOKE SELECT ON EMP FROM HR;

Revoke succeeded.

SQL> CONN
Enter user-name: HR
Enter password:
Connected.
SQL> select *
2 from [Link];
from [Link]
*
ERROR at line 2:
ORA-00942: table or view does not exist

You might also like