Dbms Lab Manual RGPV

Sagar Institute of Science, Technology & Research, SISTec-R LAB MANUAL

Department

Computer

science

and

engineering

Session

2013-14

Fifth Teacher’s name

Prof. Anidra Katiyar & Prof. Sujeet singh

Semester

Subject

RDBMS

Sub. code

ME-506

LAB MANUAL Prepared by: Prof. Anidra Katiyar & Prof. Sujeet singh SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL). The query and update commands form the DML part of SQL: ●

SELECT - extracts data from a database

●

UPDATE - updates data in a database

●

DELETE - deletes data from a database

●

INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are: ●

●

●

●

●

CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table

Titles you can't find anywhere else

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.













●

CREATE INDEX - creates an index i ndex (search key)

●

DROP INDEX - deletes an index i ndex

SQL is a standard language for accessing and manipulating databases .

What is SQL?

1-SQL stands for Structured Query Language 2-SQL lets you access and manipulate databases 3-SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

1-SQL can execute queries against a database 2-SQL can retrieve data from a database 3-SQL can insert records in a database 4-SQL can update records in a database 5-SQL can delete records from a database 6-SQL can create new databases 7-SQL can create new tables in a database 8-SQL can create stored procedures in a database 9-SQL can create views in a database 10-SQL can set permissions on tables, procedures, and views

SQL is a Standard - BUT....















However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner. Note: Most of the SQL database programs also have their own proprietary extensions

in addition to the SQL standard!

Using SQL in Your Web Site

To build a web site that shows some data from a database, you will need the following: ●

An RDBMS database program (i.e. MS Access, SQL Server, MySQL)

●

A server-side scripting language, like PHP or ASP

●

SQL

●

HTML / CSS

SQL is a standard language for accessing databases. Our SQL tutorial will teach you how to use SQL to access and manipulate data in: MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems. . CONSTRAINTS

: PRIMARY, UNIQUE, CHECK, REFERENCES

PRIMARY CONSTRAINTS SECONDARY CONSTRAINTS

CONSTRAINTS BASED ON

: NOT NULL, DEFAULT

2 LEVELS COLUMN LEVEL and

TABLE LEVEL

Except Not null all others can be defined as Both table and column level. But Not null as only column level. E.g. For SECONDARY CONSTRAINTS 1.

NOT NULL CONSTRAINT















2. DEFAULT CONSTRAINT

CREATE TABLE EMP(ENO NUMBER(3)

NOT NULL, ENAME

VARCHAR2(10),DOJ VARCHAR2(10 ),DOJ DATE

DEFAULT SYSDATE);

3. PRIMARY CONSTRAINT (COLUMN LEVEL) UNIQUE

CREATE TABLE EMP(ENO NUMBER(3)

NOT NULL CONSTRAINT

UNIEMP UNIQUE, ENAME VARCHAR2(10));

PRIMARY KEY

CREATE TABLE EMP( ENO NUMBER(3) CONSTRAINT PKEMP PRIMARY KEY, ENAME VARCHAR2(10));

4. CHECK CONSTRAINT

CREATE TABLE BANK( ACNO NUMBER(2) CONSTRAINT PKBANK PRIMARY KEY, ACTYPE VARCHAR2(2) CONSTRAINT CKBANK CHECK ( ACTYPE IN ('SB','CA','RD')),ACNAME ('SB','CA','RD')),ACNAME VARCHAR2(10), VARCHAR2(10),

AMOUNT NUMBER(4));

5. REFERENCES

CREATE TABLE EMP( ENO NUMBER(3) CONSTRAINT PKE9 PRIMARY















6. REFERENCES (REFERING TO DIFFERENT TABLE)

CREATE TABLE DEPT(DEPTNO NUMBER(2) CONSTRAINT PKDNO PRIMARY KEY, DNAME VARCHAR2(10), LOC VARCHAR2(10));

CREATE TABLE EMP( EMPNO NUMBER(4),ENAME VARCHAR2(10), SAL NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FKDNO REFERENCES DEPT(DEPTNO));

7. TABLE LEVEL CONSTRAINTS

UNIQUE

TABLE LEVEL

CREATE TABLE BANK( ACNO NUMBER(3),ACTYPE VARCHAR2(10), BAL NUMBER(7,2),PLACE VARCHAR2(10),CONSTRAINT UNIBANK UNIQUE(ACNO,ACTYPE)); PRIMARY KEY(TABLE LEVEL) CREATE TABLE BANK( ACNO NUMBER(2), ACTYPE VARCHAR2(2) CONSTRAINT

CKBANK CHECK (ACTYPE IN ('SB','CA','RD')),AMOUNT ('SB','CA','RD')),AMOUNT

NUMBER)

Sagar Institute of Science, Technology &















Department Teacher’s name

Subject

Computer

science

and

engineering Anidra Katiyar Database management System

Session

2013-14

Semester

Fifth

Sub. code

CS-503

Program 1 Delete duplicate row from the table.

DELETE FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT GROUP BY DEPTNO HAVING COUNT(DEPTNO)>1); DELETE FROM emp A WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM emp WHERE A.DEPTNO=B.DEPTNO); OR DELETE FROM DEPT A WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM DEPT B WHERE A.DEPTNO=B.DEPTNO);

Ques1:-Delete the row containing name Ram? Ques2:-Delete all the rows having same name more than once? Ques3:- Delete the row of employee whose name start with M?

















Sagar Institute of Science, Technology & Research, SISTec-R EXPERIMENT NO-2


Subject

Computer

science

and


Session

2013-14

Semester

Fifth

Sub. code

CS-503

Program 2 Display the alternate row from table.

SELECT * FROM EMP WHERE ROWID IN(SELECT DECODE(MOD(ROWNUM,2),0,ROWID) FROM EMP); OR SELECT * FROM GDEPT WHERE ROWID IN(SELECT DECODE(MOD(ROWNUM,2),0,ROWID) FROM GDEPT);

Ques1:-Show the name of those employees who earn c ommission?



















Subject

Computer

science

and


Session

2013-14

Semester

Fifth

Sub.code

CS-503

















Ques3:- Delete the row of employee whose salary is more than 25000?

















Subject

Database management System

Sub. code

CS-503

Program 4

Update multiple rows in using single update statement DISPLAY ALL THE DETAILS WHERE DEPT IS EITHER SALES OR RESEARCH

Select * from emp where dname = any(select dname from emp where dname = ‘sales’ or dname = ‘research’); Select * from emp where dname = any(select dname from emp where Dname like(‘sales’,’research’)); like(‘sales’,’research’)); Ques1:-Find the name of those entire employee who work in Delhi and update their location to Bombay? Ques2:-Find the name of those dept which are in i n same city? Ques3:- Write a query to raise the salary by 50% of those employees who do not have a commission?

















Sagar Institute of Science, Technology T echnology & Research, SISTec-R EXPERIMENT NO-5


Subject

Computer

science

and


Program 5 Find the third highest paid and third lowest paid salary.

SOL: SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP

Session

2013-14

Semester

Fifth

Sub. code

CS-503



















SAL>(SELECT MIN(SAL) FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP))));

Ques1:-Write a query to find all those employee who are in the dept which has the max salary of all dept? Ques2:- Write a query to find those entire employees who earn maximum salary? Ques3:- Write a query to find those employees who work in that dept in which the higher salary taker works?

















Teacher’s name

Subject

Anidra Katiyar Database management System

Semester

Fifth

Sub.code

CS-503

DISPLAY from NTH ROW

SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT WHERE ROWNUM<=(SELECT COUNT(*)-&N FROM DEPT));

Program 6 Display the 3rd, 4th, 9th rows from table.

SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT WHERE ROWNUM<=(SELECT COUNT(*)-&3 FROM DEPT)); SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT WHERE ROWNUM<=(SELECT COUNT(*)-&4 FROM DEPT)); SELECT * FROM DEPT WHERE ROWID NOT IN(SELECT ROWID FROM DEPT



















Subject

Computer

science

and


Session

2013-14

Semester

Fifth

Sub. code

CS-503

















'[JKLM]%'

Ques1:-Write a query to find that t hat how many employees are there whose name ends with N? Ques2:- Write a query to find that how many employees are there whose name ends with M without using like operator?

















SELECT last_name, hire_date FROM employees WHERE hire_date < trunc(sysdate,'MM')+15; Ques1:-Write a query to find the t he data of that entire employee whose name ends with t? Ques2:- Find the DOB of that employee who was born on the same date on which the maximum salary earner was born?





















Subject

Computer

science

and


Session

2013-14

Semester

Fifth

Sub. code

CS-503

















This is used for identifying a point in the transaction to which a programmer can later roll back. That is it is possible for the programmer to divide a big transaction into subsections each having a Savepoint defined in it. The command used in SQL for this is simply: SAVEPOINT savepointname; For example: UPDATE….. DELETE…. SAVEPOINT e1; INSERT…. UPDATE….

















Teacher’s name

Subject

Anidra Katiyar Database management System

Semester

Fifth

Sub.code

CS-503

Program 10 Write a sql statement for rollback commit and save points.

SQL> SELECT * FROM DEPT; DEPTNO DNAME

LOC

--------- -------------- ------------10 ACCOUNTING

NEW YORK

















60 IP

BANGALORE 70 IT

GOA

7 rows selected. SQL> ROLLBACK TO SAVEPOINT B; Rollback complete. SQL> SELECT * FROM DEPT; DEPTNO DNAME

LOC

--------- -------------- ------------10 ACCOUNTING

NEW YORK

20 RESEARCH

DALLAS

30 SALES

CHICAGO

















DBMS_OUTPUT.PUT_LINE('STOCK LEVEL IS BELOW ORDER LEVEL'); insert into temp values(pname,'out of stock',sysdate); stock',sysdate); commit; end if; end; Ques1:-Draw a sequence diagram for roll back and save point activity in ATM transaction? Ques2:-Write syntax for rollback SQL query using suitable example?



















here. */ END; Let us see an example of the above DECLARE TEMP_COST NUMBER(10, 2);

















· A line with a single dot (“.”), and then · A line with run; As with Oracle SQL programs, we can invoke a PL/SQL program either by typing it in sqlplus or by putting the code in a file and invoking the file in the various ways we learned in Getting Started With Oracle.

































Program 11 Write a pl/sql for select, insert, update and delete statements.

CREATE TABLE TEMP ( ENAME VARCHAR2(10),

















Sagar Institute of Science, Technology T echnology &























































































































































Dbms Lab Manual RGPV

Recommend Documents