DBMS Previous Question papers

Database Management System Question Papers

DBMS+Previous+Year+q+Papers

Read More

DBMS viva questions with answers

Here are the DBMS viva questions with short answers included for quick learning...

Dbms+Forms Complete Questions

Read More

DBMS by Ramakrishnan

I know we all have that greed to expect all the chapters of our syllubus to find in one Text Book.But surely we are not gonna find it!!

As far as OU syllubus is concerned one text book will not suffice for a subject.The same case goes with Database Management Systems.The other prescribed book will be Ramakrishnan,and here is the book for you without wasting your time searching for the e-book.
Here you go :)

Dbms by Raghu Ramakrishnan

Read More

SQL data file

Often u get a thought in your mind to practice dbms programs at home and want to work with different sql queries to see the different outputs so that you can excel in the subject, but you dont find the desired table data to include in your sql plus.Even if you create tables using CREATE TABLE query then to work on it to see different possibilities, you need to insert large amount of data in the table which is tiresome task.
Here is the solution with all the tables and the data included which i am providing for you all, which will help you for the same.

--
-- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved.
--
-- NAME
-- demo.sql
--
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables in the
-- current schema. It should be STARTed by each user wishing to
-- access the tables. To remove the tables use the demodrop.sql
-- script.
--
-- USAGE
-- From within SQL*Plus, enter:
-- START demo.sql

SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF

DROP TABLE EMP1;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP1
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMP1 VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP1 VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP1 VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP1 VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP1 VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP1 VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP1 VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP1 VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP1 VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP1 VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP1 VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP1 VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP1 VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP1 VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);

CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.

EXIT


Just copy paste the above code in your notepad file and save it as "demo.sql" .Remember it should end with .sql extension.
After saving it with .sql extension....all you have to do is go to that file location through sql prompt or paste the file in the "BIN" folder of your installed directory and type "START demo.sql" (without quotes) in SQL PLUS prompt.
Thats it!!!All the tables along with the data will be inserted in your database.Enjoy!! :)

Queries(doubts) and Comments are always welcome :)

Read More

SQL Descriptions

*. DESCRIPTIONS::

a.DDL[Data Definition Language]:These statements are used to define the database structure or schema.
Examples:

* CREATE - to create objects in the database
syntax: CREATE TABLE
(,...);

* ALTER - alters the structure of the database
syntax: ALTER TABLE ADD/MODIFY (,...);

* DROP - delete objects from the database
syntax: DROP TABLE ;

* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
syntax: TRUNCATE TABLE ;

* RENAME - rename an object
syntax: RENAME TO ;

DML[Data Manipulation Language]:These statements are used for managing data within schema objects.
Examples:

* INSERT - insert data into a table
syntax: INSERT into values(,colname>,...);

* UPDATE - updates existing data within a table
syntax: UPDATE SET col=value[col n1,...] WHERE(condition);

* DELETE - deletes all records from a table, the space for the records remain
syntax: DELETE from WHERE ;

DRL[Data Retrieving Language]:These statements are used for retrieving the data from the database.
Examples:

* SELECT - retrives the selected data from the table
syntax: SELECT(distinct/all(*)) from WHERE(condition);

DCL [Data Control Language]:These statements controls the privileges provided to the user in the database.
Examples:

* GRANT - gives user's access privileges to database
syntax: GRANT ALL/privilege names( ON TO );

* REVOKE - withdraw access privileges given with the GRANT command
syntax: REVOKE ALL/privilege names( ON from );


TCL[Transaction Control]:These statements are used to manage the changes made by DML statements.
It allows statements to be grouped together into logical transactions.
Examples:

* COMMIT - save work done
syntax: COMMIT;

* SAVEPOINT - identify a point in a transaction to which you can later roll back
syntax: SAVEPOINT s1;

* ROLLBACK - restore database to original since the last COMMIT
syntax: ROLLBACK s1;

*. BUILT-IN FUNCTIONS:
CHARACTER FUNCTIONS:

1.LOWER : Converts given string into lower-case.
syntax : SELECT lower() from ;

2.UPPER : Converts given string into upper-case.
syntax : SELECT upper() from ;

3.INITCAP: This functions prints the initial letter as upper-case letter.
syntax : SELECT INITCAP() from ;

4.CONCAT : Combines two strings using the symbol '||'.
syntax : SELECT ,CONCAT('name is',) from ;

5.LPAD/RPAD: padding of the string is done with the given symbol
syntax : SELECT LPAD('',,'');

: SELECT RPAD('',,'');

6.SUBSTR : Selects substring from a given string.
syntax : SELECT substr(,,) from DUAL;

7.INSTR : Used to find the position of a string in a given string.
syntax : SELECT INSTR(','');

8.LTRIM/RTRIM: Trims the string with the given characters.
syntax : LTRIM(,);
: RTRIM(,);

9.LENGTH : Defines number of characters in a given string or column.
syntax : SELECT LENGTH(<string.) as length from ;

10.TRANSLATE : Translates given string character by character.
syntax : SELECT TRANSLATE('','','');

11.REPLACE : Translate given string word by word.
syntax : SELECT REPLACE('','','');

* Types of Group Functions
->AVG
->COUNT
->MAX
->MIN
->STDDEV
->SUM
->VARIANCE

-> AVG and SUM for numeric data.
SQL> SELECT AVG(SAL),SUM(SAL) from ;
-> MIN and MAX for any datatype.
SQL> SELECT MIN(hiredate), MAX(hiredate) from ;
-> COUNT(*) returns the number of rows in a table.
SQL> SELECT COUNT(*) FROM emp WHERE deptno = 30;
-> COUNT(expr) returns the number of nonnull rows.
SQL> SELECT COUNT(comm) FROM emp WHERE deptno=30;

->The NVL function forces group functions to include null values.
SQL> SELECT AVG(NVL(comm,0)) FROM emp;

Creating Groups of Data: GROUP BY Clause:
SQL> SELECT column,
2 group_function(column)
3 FROM table[WHEREcondition]
4 [GROUP BYgroup_by_expression]
5 [ORDER BYcolumn];

->Divide rows in a table into smaller groups by using the GROUP BY clause.
->All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.
->Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY.
->Cannot use the WHERE clause to restrict groups.
->Use the HAVING clause to restrict groups.

HAVING CLAUSE:
->Use the HAVING clause to restrict groups
►Rows are grouped.
►The group function is applied.
►Groups matching the HAVING clause are displayed.
SQL> SELECT column,group_function
2 FROM table[WHEREcondition]
3 [GROUP BYgroup_by_expression]
4 [HAVINGgroup_condition]
5 [ORDER BYcolumn];

Types of Subqueries:
->Single-Row Subqueries
->Multiple-Row Subqueries
Operator Meaning
IN Equal to any member in the list
ANY Compare value to each value returned by the subquery
ALL Compare value to every value returned by the subquery
->Multiple-Column Subqueries

CONSTRAINTS:
->Constraints enforce rules at the table level.
->Constraints prevent the deletion of a table if there are dependencies.
->The following constraint types are valid in Oracle:
►NOT NULL
►UNIQUE
►PRIMARY KEY
►FOREIGN KEY
►CHECK
DEFINING CONSTRAINTS:
SQL>CREATE TABLE [schema.]table
2 (column datatype
3 [DEFAULT expr]
4 [column_constraint],...[table_constraint][,...]);
->NOT NULL constraint:
►Ensures that null values are not permitted for the column.
SQL> CREATE TABLE emp1(
2 empno NUMBER(4),
3 enameVARCHAR2(10) NOT NULL);
->UNIQUE KEY constraint:
►Defined at either the table level or the column level.
SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE(dname));
->PRIMARY KEY constraint:
levelSQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE (dname),
6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));
->FOREIGN KEY constraint:
FOREIGN KEY
Defines the column in the child table at the table constraint level.
REFERENCES
Identifies the table and column in the parent table.
ON DELETE CASCADE
Allows deletion in the parent table and deletion of the dependent rows in the child table.
->CHECK constraint:
Defines a condition that each row must satisfy
Expressions that are not allowed:
►References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
►Calls to SYSDATE, UID, USER, and USERENV functions
►Queries that refer to other values in other rows.

VIEWING constraints:
SQL> SELECT constraint_name, constraint_type,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = 'EMP';

VIEW: Logically represents subsets of data from one or more tables
CREATING a view:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]];
->Modify the EMPVU10 view by using CREATE OR REPLACE VIEW clause.
REMOVE view:
SQL>DROP VIEW view;

SEQUENCE: Generates primary key values.
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n| NOCACHE}];
->NEXTVAL returns the next available sequence value.
It returns a unique value every time it is referenced, even for different users.
CURRVAL obtains the current sequence value.
NEXTVAL must be issued for that sequence before CURRVAL contains a value.

INDEX: schema object
CREATE INDEX index ON table(column[, column]...);

SYNONYM: Alternative name for an object.
CREATE [PUBLIC] SYNONYM synonym FOR object;

PL/SQL is an extension to SQL with design features of programming languages.
DECLARE –Optional
–Variables, cursors, user-defined exceptions
•BEGIN –Mandatory
–SQL statements
–PL/SQL statements
•EXCEPTION –Optional
–Actions to perform when errors occur
•END; –Mandatory.

SQL Cursor: cursor is a private SQL work area.
There are two types of cursors:
►Implicit cursor ►Explicit cursors

Implicit cursor attributes verify the outcome of DML statements:
► SQL%ROWCOUNT ► SQL%FOUND
► SQL%NOTFOUND ► SQL%ISOPEN
Explicit cursors are defined by the programmer.

What is an exception?
►Identifier in PL/SQL that is raised during execution
Sample predefined exceptions:
►NO_DATA_FOUND
►TOO_MANY_ROWS ►INVALID_CURSOR
►ZERO_DIVIDE ►DUP_VAL_ON_INDEX.
Packages: Group logically related PL/SQL types,items and subgroups.
Consists of 2 types: -Specification.
-Body.

Read More