Whole DBMS questions

1

Overview of SQL DDL, DML and DCL Commands.

DDL is Data Definition Language statements. Some examples:

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the

records ar

removed

COMMENT - add comments to the data dictionary

GRANT - gives user's access privileges to database

REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

DCL is Data Control Language statements. Some examples:

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

SET TRANSACTION - Change transaction options like what rollback segment to use

2

Basic SQL DDL Commands.

To practice basic SQL DDL Commands such as CREATE, DROP, etc.

1. SQL - CREATE TABLE

Syntax: CREATE TABLE tablename (column_name data_ type constraints, …)

Example:

INPUT:

SQL> CREATE TABLE Emp ( EmpNo short CONSTRAINT PKey PRIMARY KEY,

EName VarChar(15), Job Char(10) CONSTRAINT Unik1 UNIQUE,

Mgr short CONSTRAINT FKey1 REFERENCES EMP (EmpNo),

Hiredate Date, DeptNo short CONSTRAINT FKey2 REFERENCES DEPT(DeptNo));

RESULT: Table created.

SQL>Create table prog20 (pname varchar2(20) not null), doj date not null,dob date not null,

sex varchar(1) not null, prof1 varchar(20),prof2 varchar(20),salary number(7,2) not null);

RESULT:

Table created.

SQL>desc prog20;

Name Null? Type

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

PNAME NOT NULL VARCHAR2(20)

DOJ NOT NULL DATE

DOB NOT NULL DATE

SEX NOT NULL VARCHAR2(1)

PROF1 VARCHAR2(20)

PROF2 VARCHAR2(20)

SALARY NOT NULL NUMBER(7,2)

3

2. SQL - ALTER TABLE

INPUT:

SQL>ALTER TABLE EMP ADD CONSTRAINT Pkey1 PRIMARY KEY (EmpNo);

RESULT: Table Altered.

Similarly, ALTER TABLE EMP DROP CONSTRAINT Pkey1;

3. SQL - DROP TABLE

– Deletes table structure – Cannot be recovered – Use with caution

INPUT:

SQL> DROP TABLE EMP; Here EMP is table name

RESULT: Table Dropped.

4. TRUNCATE TRUNCATE TABLE <TABLE NAME>;

Basic SQL DML Commands.

To practice basic SQL DML Commands such as INSERT, DELETE, etc.

1. SQL - INSERT INTO

Syntax: INSERT INTO tablename VALUES (value list)

 Single-row insert

INSERT INTO S VALUES(‘S3’,’SUP3’,’BLORE’,10)

 Inserting one row, many columns at a time

INSERT INTO S (SNO, SNAME) VALUES (‘S1’, ‘Smith’);S1’ Smith’

 Inserting many rows, all/some columns at a time.

INSERT INTO NEW_SUPPLIER (SNO, SNAME)

SELECT SNO, SNAME FROM S

WHERE CITY IN (‘BLORE’,’MADRAS’)

4

Other Examples:

INPUT:

SQL>Insert into prog values (‘kkk’,’05-may-56’);

RESULT: 1 row created.

INPUT:

SQL>Insert into prog20 values(‘Hema’,’25-sept-01’28-jan-85’,’f’,’c’,’c++’,’25000’);

RESULT: 1 row created.

INPUT:

SQL>Insert into prog values(‘&pname’,’&doj’);

SQL> Insert into prog values('&pname','&doj');

Enter value for pname: ravi

Enter value for doj: 15-june-81

RESULT:

old 1: Insert into prog values('&pname','&doj')

new 1: Insert into prog values('ravi','15-june-81')

1 row created.

2. SQL - UPDATE

Syntax: UPDATE tablename SET column_name =value [ WHERE condition]

Examples:

UPDATE S SET CITY = ‘KANPUR’ WHERE SNO=‘S1’

UPDATE EMP SET SAL = 1.10 * SAL

SQL> update emp set sal=20000 where empno=7369;

1 row updated.

5

3. SQL - DELETE FROM

Syntax: DELETE FROM tablename WHERE condition

Examples:

DELETE FROM SP WHERE PNO= ‘P1’

DELETE FROM SP

INPUT:

SQL>Delete from emp where empno=7369;

RESULT: 1 row deleted.

Basic SQL DCL Commands.

To practice basic SQL DCL Commands such as COMMIT, ROLLBACK etc.

1. COMMIT

Save changes (transactional).

Syntax:

COMMIT [WORK] [COMMENT 'comment_text']

COMMIT [WORK] [FORCE 'force_text' [,int] ]

FORCE - will manually commit an in-doubt distributed transaction

force_text - transaction identifier (see the DBA_2PC_PENDING view)

int - sets a specific SCN.

If a network or machine failure prevents a distributed transaction from committing

properly, Oracle will store any commit comment in the data dictionary along with the

transaction ID.

INPUT:

SQL>commit;

RESULT: Commit complete.

6

2. ROLLBACK

Undo work done (transactional).

Syntax:

ROLLBACK [WORK] [TO [SAVEPOINT]'savepoint_text_identifier'];

ROLLBACK [WORK] [FORCE 'force_text'];

FORCE - will manually rollback an in-doubt distributed transaction

INPUT:

SQL>rollback;

RESULT:Rollback complete.

3. SAVEPOINT

Save changes to a point (transactional).

Syntax:

SAVEPOINT text_identifier

Example:

UPDATE employees

SET salary = 95000

WHERE last_name = 'Smith';

SAVEPOINT justsmith;

UPDATE employees

SET salary = 1000000;

SAVEPOINT everyone;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT justsmith;

COMMIT;

7

Writing and Practice of Simple Queries.

To write simple queries and practice them.

1. Get the description of EMP table.

SQL>desc emp;

RESULT:

Name Null? Type

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

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(3)

AGE NUMBER(3)

ESAL NUMBER(10)

2. Get the description DEPT table.

SQL>desc dept;

RESULT:

Name Null? Type

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

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

3.List all employee details.

SQL>select * from emp;

RESULT:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO AGE ESAL

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

7369 SMITH CLERK 7902 17-DEC-80 800 0 20 25 0

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 25 0

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 25 0

7566 JONES MANAGER 7839 02-APR-81 2975 500 20 25 0

7698 BLAKE MANAGER 7839 01-MAY-81 2850 1400 30 25 0

8

4.List all employee names and their salaries, whose salary lies between

1500/- and 3500/- both inclusive.

INPUT

SQL>select ename from emp where sal between 1500 and 3500;

RESULT

ENAME

----------

ALLEN

JONES

BLAKE

CLARK

SCOTT

TURNER

FORD

russel

greg

9 rows selected.

5. List all employee names and their and their manager whose manager is

7902 or 7566 0r 7789.

INPUT SQL>select ename from emp where mgr in(7602,7566,7789);

RESULT

ENAME

-------

SCOTT

FORD

6. List all employees which starts with either J or T.

INPUT SQL>select ename from emp where ename like ‘J%’ or ename like ‘T%’;

RESULT:

ENAME

---------

JONES

TURNER

JAMES

9

7. List all employee names and jobs, whose job title includes M or P.

INPUT SQL>select ename,job from emp where job like ‘M%’ or job like ‘P%’;

RESULT:

ENAME JOB

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

JONES MANAGER

BLAKE MANAGER

CLARK MANAGER

KING PRESIDENT

8. List all jobs available in employee table.

INPUT SQL>select distinct job from emp;

RESULT:

JOB

---------

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

assistant

clerk

7 rows selected.

9. List all employees who belongs to the department 10 or 20.

INPUT SQL>select ename from emp where deptno in (10,20);

RESULT:

ENAME

----------

SMITH

JONES

CLARK

SCOTT

KING

ADAMS

FORD

MILLER

8 rows selected.

10

10. List all employee names , salary and 15% rise in salary.

INPUT SQL>select ename , sal , sal+0.15* sal from emp;

RESULT:

ENAME SAL SAL+0.15*SAL

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

SMITH 800 920

ALLEN 1600 1840

WARD 1250 1437.5

JONES 2975 3421.25

MARTIN 1250 1437.5

BLAKE 2850 3277.5

CLARK 2450 2817.5

7 rows selected.

11. List minimum , maximum , average salaries of employee.

INPUT SQL>select min(sal),max(sal),avg(sal) from emp;

RESULT:

MIN(SAL) MAX(SAL) AVG(SAL)

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

3 5000 1936.94118

12. Find how many job titles are available in employee table.

INPUT SQL>select count (distinct job) from emp;

RESULT:

COUNT(DISTINCTJOB)

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

7

13. What is the difference between maximum and minimum salaries of

employees in the organization?

INPUT SQL>select max(sal)-min(sal) from emp;

RESULT:

MAX(SAL)-MIN(SAL)

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

4997

11

14. Display all employee names and salary whose salary is greater than

minimum salary of the company and job title starts with ‘M’.

INPUT SQL>select ename,sal from emp where job like ‘M%’ and sal > (select min (sal)

from emp);

RESULT

ENAME SAL

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

JONES 2975

BLAKE 2850

CLARK 2450

15. Find how much amount the company is spending towards salaries.

INPUT SQL>select sum (sal) from emp;

RESULT

SUM(SAL)

---------

32928

16. Display name of the dept. with deptno 20.

INPUT SQL>select ename from emp where deptno = 20;

RESULT

ENAME

----------

SMITH

JONES

SCOTT

ADAMS

17. List ename whose commission is NULL.

INPUT SQL>select ename from emp where comm is null;

ENAME

RESULT ----------

CLARK

SCOTT

KING

ADAMS

JAMES

FORD

6 rows selected.

12

        Click here to download complete questions