Skip to document
Welcome to StudocuSign in to access the best study resources
Guest user
0followers
HomeMy LibraryAsk AI
  • You don't have any recent items yet.
My Library
  • You don't have any courses yet.
  • You don't have any books yet.
  • You don't have any Studylists yet.
Premium
This is a Premium Document. Some documents on Studocu are Premium. Upgrade to Premium to unlock it.

Dbms labkcet

DBMS lab manual
Course

Database Management Systems

3 Documents
Students shared 3 documents in this course
Academic year: 2023/2024
Uploaded by:
Anonymous Student
This document has been uploaded by a student, just like you, who decided to remain anonymous.
Jawaharlal Nehru Technological University, Hyderabad

Comments

Please sign in or register to post comments.

Students also viewed

Related documents

Preview text

Lab Manual

Department of Computer Science

DATABASE MANAGEMENT SYSTEM

INTRODUCTION TO SQL

Pronounced as SEQUEL: Structured English QUERY Language

 Pure non-procedural query language

 Designed and developed by IBM, Implemented by Oracle

 1978 System/R IBM- 1st Relational DBMS

 1979 Oracle and Ingres

 1982 SQL/DS and DB2 IBM

 Accepted by both ANSI + ISO as Standard Query Language for any RDBMS

 SQL86 (SQL1) : first by ANSI and ratified by ISO (SQL-87), minor revision on 89

(SQL-89)

 SQL92 (SQL2) : major revision

 SQL99 (SQL3) : add recursive query, trigger, some OO features, and non-scholar type

 SQL2003 : XML, Window functions, and sequences (Not free)

 Supports all the three sublanguages of DBMS: DDL, DML, DCL

 Supports Aggregate functions, String Manipulation functions, Set theory operations, Date

Manipulation functions, rich set of operators ( IN, BETWEEN, LIKE, IS NULL,

EXISTS)

 Supports REPORT writing features and Forms for designing GUI based applications

Data Definition in SQL

CREATE, ALTER and DROP

table ............................................................. relation

row... ............................................................ tuple

column... ..................................................... attribute

DATA TYPES

 Numeric: NUMBER, NUMBER(s,p), INTEGER, INT, FLOAT, DECIMAL

 Character: CHAR(n), VARCHAR(n), VARCHAR2(n), CHAR VARYING(n)

 Bit String: BLOB, CLOB

 Boolean: true, false, and null

Experiment 1:

Consider following databases and draw ER diagram and convert entities and relationships

to relation table for a given scenario.

1. COLLEGE DATABASE:

STUDENT (USN, SName, Address, Phone, Gender)

SEMSEC (SSID, Sem, Sec)

CLASS (USN, SSID)

SUBJECT (Subcode, Title, Sem, Credits)

IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)

2. COMPANY DATABASE:

EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)

DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)

DLOCATION (DNo,DLoc)

PROJECT (PNo, PName, PLocation, DNo)

WORKS_ON (SSN, PNo, Hours)

SOLUTION:

College Database: E-R Diagram

Mapping

entities and

relationships

to relation

table

(Schema

Diagram)

Experiment 2

Consider the MOVIE DATABASE

Write following relational algebra queries for a given set of relations.

1. Find movies made after 1997

2. Find movies made by Hanson after 1997

3. Find all movies and their ratings

4. Find all actors and directors

5. Find Coen’s movies with McDormand

SOLUTION:

Common notations of Relational Algebra

Operation Purpose

Select(σ) The SELECT operation is used for selecting a subset of the tuples

according to a given selection condition

Projection(π) The projection eliminates all attributes of the input relation but those

mentioned in the projection list.

Union

Operation(𝖴)

UNION is symbolized by symbol. It includes all tuples that are in

tables A or in B.

Set Difference(-) - Symbol denotes it. The result of A - B, is a relation which includes

all tuples that are in A but not in B.

Intersection(∩) Intersection defines a relation consisting of a set of all tuple that are

in both A and B.

Cartesian

Product(X)

Cartesian operation is helpful to merge columns from two relations.

Inner Join Inner join, includes only those tuples that satisfy the matching

criteria.

Theta Join(θ) The general case of JOIN operation is called a Theta join. It is

denoted by symbol θ.

EQUI Join When a theta join uses only equivalence condition, it becomes a equi

join.

Natural Join(⋈) Natural join can only be performed if there is a common attribute

(column) between the relations.

Outer Join In an outer join, along with tuples that satisfy the matching criteria.

Left Outer Join(

)

In the left outer join, operation allows keeping all tuple in the left

relation.

3. Find all movies and their ratings

πtitle, rating(Movies)

4. Find all actors and directors

πactor(Actors) 𝖴 πdirector(Directors)

5. Find Coen’s movies with McDormand

e1 = πtitle(σactor=‘McDormand_ (Acts))

e2 = πtitle(σdirector=‘Coen_ (Movies))

result = e1 ∩ e

CREATE TABLE EMPLOYEE

(SSN VARCHAR2 (20) PRIMARY KEY,

FNAME VARCHAR2 (20),

LNAME VARCHAR2 (20),

ADDRESS VARCHAR2 (20),

SEX CHAR (1),

SALARY INTEGER,

SUPERSSN REFERENCES EMPLOYEE (SSN),

DNO REFERENCES DEPARTMENT (DNO));

NOTE: Once DEPARTMENT and EMPLOYEE tables are created we must alter department

table to add foreign constraint MGRSSN using sql command

ALTER TABLE DEPARTMENT

ADD MGRSSN REFERENCES EMPLOYEE (SSN);

5. Inserting/Updating/Deleting Records in a Table,

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSECE01‘,‘JOHN‘,‘SCOTT‘,‘BANGALORE‘,‘M‘, 450000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSCSE01‘,‘JAMES‘,‘SMITH‘,‘BANGALORE‘,‘M‘, 500000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSCSE02‘,‘HEARN‘,‘BAKER‘,‘BANGALORE‘,‘M‘, 700000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSCSE03‘,‘EDWARD‘,‘SCOTT‘,‘MYSORE‘,‘M‘, 500000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSCSE04‘,‘PAVAN‘,‘HEGDE‘,‘MANGALORE‘,‘M‘, 650000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSCSE05‘,‘GIRISH‘,‘MALYA‘,‘MYSORE‘,‘M‘, 450000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSCSE06‘,‘NEHA‘,‘SN‘,‘BANGALORE‘,‘F‘, 800000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSACC01‘,‘AHANA‘,‘K‘,‘MANGALORE‘,‘F‘, 350000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSACC02‘,‘SANTHOSH‘,‘KUMAR‘,‘MANGALORE‘,‘M‘, 300000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSISE01‘,‘VEENA‘,‘M‘,‘MYSORE‘,‘M‘, 600000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ̳RNSIT01‘,‘NAGESH‘,‘HR‘,‘BANGALORE‘,‘M‘, 500000);

INSERT INTO DEPARTMENT VALUES ( ̳1‘,‘ACCOUNTS‘,‘01-JAN-

01‘,‘RNSACC02‘);

INSERT INTO DEPARTMENT VALUES ( ̳2‘,‘IT‘,‘01-AUG-16‘,‘RNSIT01‘);

INSERT INTO DEPARTMENT VALUES ( ̳3‘,‘ECE‘,‘01-JUN-08‘,‘RNSECE01‘);

INSERT INTO DEPARTMENT VALUES ( ̳4‘,‘ISE‘,‘01-AUG-15‘,‘RNSISE01‘);

INSERT INTO DEPARTMENT VALUES ( ̳5‘,‘CSE‘,‘01-JUN-02‘,‘RNSCSE05‘);

Update

UPDATE EMPLOYEE SET DNO=‘5‘, SUPERSSN=‘RNSCSE06‘ WHERE

SSN=‘RNSCSE05‘;

Delete entries of employee table where DNO =1;

DELETE FROM EMPLOYEE WHERE DNO=1;

6. COMMIT and ROLLBACK

Before concluding this section on Data Manipulation Language commands there are two

further commands, which are very useful. Changes made to the database by INSERT,

UPDATE and DELETE commands are temporary until explicitly committed. This is

performed by the command:

COMMIT;

On execution of this command all changes to the database made by you are made

permanent and cannot be undone.

 A COMMIT is automatically executed when you exit normally from SQL*Plus.

However, it does no harm to occasionally issue a COMMIT command.

 A COMMIT does not apply to any SELECT commands as there is nothing to

commit.

 A COMMIT does not apply to any DDL commands (eg CREATE TABLE,

CREATE INDEX, etc). These are automatically committed and cannot be rolled

back.

 If you wished to rollback (ie undo) any changes made to the database since the

last commit, you can issue the command:

ROLLBACK;

A group of related SQL commands that all have to complete successfully or otherwise be

rolled back, is called a transaction. Part of your research for Outcome 3 includes

investigating transaction processing and the implications of rollback and commit.

3. All constraints and views that reference the column are dropped automatically, along

with the column.

SQL> ALTER TABLE DEPARTMENT DROP column LOC CASCADE

CONSTRAINTS;

Table altered.

SQL> desc dept

Name Null? Type

DEPTNO NOT NULL NUMBER(38)

DNAME VARCHAR2(10)

PINCODE NOT NULL NUMBER(6)

4. Rename the column DNAME to DEPT_NAME in dept table

SQL> ALTER TABLE DEPT RENAME COLUMN DNAME TO DEPT_NAME ;

Table altered.

SQL> DESC DEPARTMENT;

Name Null? Type

DEPTNO NOT NULL NUMBER(38)

DEPT_NAME VARCHAR2(10)

LOC VARCHAR2(4)

PINCODE NOT NULL NUMBER(6)

5. Change the datatype of colunm loc as CHAR with size 10

SQL> ALTER TABLE DEPARTMENT MODIFY LOC CHAR(10) ;

Table altered.

SQL> DESC DEPARTMENT;

Name Null? Type

DEPTNO NOT NULL NUMBER(38)

DEPT_NAME VARCHAR2(10)

LOC CHAR(10)

PINCODE NOT NULL NUMBER(6)

6. Delete table

SQL> DROP TABLE DEPARTMENT;

Table dropped.

Experiment 5A

Consider Employee table

EMPNO EMP_NAME DEPT SALARY DOJ BRANCH

E101 Amit oduction 45000 12-Mar-00 Bangalore

E102 Amit HR 70000 03-Jul-02 Bangalore

E103 sunita anagemen 120000 11-Jan-01 Mysore

E105 sunita IT 67000 01-Aug-01 Mysore

E106 mahesh Civil 145000 20-Sep-03 Mumbai

Perform the following

1. Display all the fields of employee table

2. Retrieve employee number and their salary

3. Retrieve average salary of all employee

4. Retrieve number of employee

5. Retrieve distinct number of employee

6. Retrieve total salary of employee group by employee name and count similar names

7. Retrieve total salary of employee which is greater than >

8. Display name of employee in descending order

9. Display details of employee whose name is AMIT and salary greater than 50000;

1. Display all the fields of employee table

SQL> select * from employee;

EMPNO EMP_NAME DEPT SALARY DOJ BRANCH

E101 Amit Production 45000 12-MAR-00 Bangalore

E102 Amit HR 70000 03-JUL-02 Bangalore

E103 sunita Management 120000 11-JAN-01 mysore

E105 sunita IT 67000 01-AUG-01 mysore

E106 mahesh Civil 145000 20-SEP-03 Mumbai

2. Retrieve employee number and their salary

SQL> select empno, salary from employee;

EMPNO SALARY

E101 45000

E102 70000

E103 120000

E105 67000

E106 145000

EMP_NAME

sunita

sunita

mahesh

Amit

Amit

9. Display details of employee whose name is AMIT and salary greater than 50000;

SQL> select * from employee

2 where emp_name='Amit' and salary>50000;

EMPNO EMP_NAME DEPT SALARY DOJ BRANCH

E102 Amit HR 70000 03-JUL-02 Bangalore

Experiment 5B

For a given tables

Create tables and perform the following

1. How the resulting salaries if every employee working on the ‘Research’ Departments is

given a 10 percent raise.

2. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the

maximum salary, the minimum salary, and the average salary in this department

3. Retrieve the name of each employee Controlled by department number 5 (use EXISTS

operator).

4. Retrieve the name of each dept and number of employees working in each department

which has at least 2 employees

5. Retrieve the name of employees who born in the year 1990’s

6. Retrieve the name of employees and their dept name (using JOIN)

Was this document helpful?
Premium
This is a Premium Document. Some documents on Studocu are Premium. Upgrade to Premium to unlock it.

Dbms labkcet

Course: Database Management Systems

3 Documents
Students shared 3 documents in this course

University: Jawaharlal Nehru Technological University, Hyderabad

Was this document helpful?

This is a preview

Do you want full access? Go Premium and unlock all 42 pages

  • Access to all documents

  • Get Unlimited Downloads

  • Improve your grades

Upload

Share your documents to unlock

Already Premium?
Lab Manual
Department of Computer Science
DATABASE MANAGEMENT SYSTEM

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Why is this page out of focus?

This is a Premium document. Become Premium to read the whole document.

Students also viewed

Related documents