Database Management System Lab - Practical

Savitribai Phule Pune University Third Year of Computer Engineering (2015 Course) 310247:Database Management System Lab
Teaching Scheme: Practical : 04 Hours/Week	Credit 02	Examination Scheme: Practical: 50 Marks Term Work: 25 Marks
Course Objectives: • To develop basic, intermediate and advanced Database programming skills • To develop basic Database administration skills • To percept transaction processing
Course Outcomes: On completion of the course, student will be able to– • Develop the ability to handle databases of varying complexities • Use advanced database Programming concepts

Group A- Database Programming Languages – SQL, PL/SQL

1	Study of Open Source Relational Databases : MySQL
2	Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View, Index, Sequence, Synonym
3	Design at least 10 SQL queries for suitable database application using SQL DML statements: Insert, Select, Update, Delete with operators, functions, and set operator.
4	Design at least 10 SQL queries for suitable database application using SQL DML statements: all types of Join, Sub-Query and View.
5	Write a PL/SQL block of code for the following requirements:- Schema: 1. Borrower(Rollin, Name, DateofIssue, NameofBook, Status) 2. Fine(Roll_no,Date,Amt)  Accept roll_no & name of book from user.  Check the number of days (from date of issue), if days are between 15 to 30 then fine amount will be Rs 5per day.  If no. of days>30, per day fine will be Rs 50 per day & for days less than 30, Rs. 5 per day.  After submitting the book, status will change from I to R.  If condition of fine is true, then details will be stored into fine table.
6	Write a PL/SQL block of code using parameterized Cursor, that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall. If the data in the first table already exist in the second table then that data should be skipped.
7	PL/SQL Stored Procedure and Stored Function. Write a Stored Procedure namely proc_Grade for the categorization of student. If marks scored by students in examination is <=1500 and marks>=990 then student will be placed in distinction category if marks scored are between 989 and900 category is first class, if marks 899 and 825 category is Higher Second Class Write a PL/SQL block for using procedure created with above requirement.  Stud_Marks(name, total_marks) Result(Roll,Name, Class)
8	Write a database trigger on Library table. The System should keep track of the records that are being updated or deleted. The old value of updated or deleted records should be added in Library_Audit table.

                                                                                                       

Group B Large Scale Databases

9	Design and Implement any 5 query using MongoDB
10	Implement aggregation and indexing with suitable example using MongoDB.
11	Study of Open Source NOSQL Database: MongoDB (Installation, Basic CRUD operations, Execution)
12	Create simple objects and array objects using JSON
13	Encode and Decode JSON Objects using Java/PHP

Group C Mini Project : Database Project Life Cycle

14	Implement MYSQL database connectivity with Java Implement Database navigation operations (add, delete, edit,) using JDBC.

-          Champ96k

Practical No-1       Study of Open Source Relational Databases : MySQL

In this practical perform some basic following command

1.       Create Table

2.       Insert Database

3.       Update Database

4.       Remove Database

5.       Add Column/Row

6.       Display Database

siem@siem-OptiPlex-390:~$ mysql -u root –p

mysql> show databases;

mysql> create database Database_Name;

mysql> show databases;

mysql> create table Table_Name(id int(10),name varchar(10),address varchar(20));

mysql> show tables;

mysql> desc Table_Name;

mysql> insert into Table_Name values ('2',"tushar","trambak");

mysql> select * from Table_Name;

mysql> select name from Table_Name;

mysql> select id,name from Table_Name;

mysql> alter table info add column ph int(10);

mysql> select id from info  where name='hardik';

mysql> select id from Table_Name where name='hardik' and address='nashik';

mysql> update Table_Name set ph=1234567890 where id=1;

mysql> select * from Table_Name

mysql> update Table_Name set ph=9833846241 where id=2;

mysql> update Table_Name set ph=123456789 where id=1;

Practical No-2       Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View, Index, Sequence, Synonym

In this practical perform some basic following command

1.       Create Table

2.       Insert Database

3.       Show Database

4.       Implement

a) like '%a';

b)  like 'a%';

c) like '%a%';

d) like '_a%';

5.       Implement

a) max()

b) min()

c) avg

6.       Order By

a) Ascending

b) Descending

mysql> select * from Table_Name where name like '%a';

mysql> select * from Table_Name where name like 'a%';

mysql> select * from Table_Name where name like '_a%';

mysql> select * from Table_Name where name like '%a%';

mysql>  select * from Table_Name order by name asc;

mysql>  select * from Table_Name order by name desc;

mysql> select min(acc_no) from Table_Name;

mysql> select max(acc_no) from Table_Name;

mysql> select avg(acc_no) from Table_Name;

mysql> select sum(acc_no) from Table_Name;

Practical No -3       Design at least 10 SQL queries for suitable database application using SQL DML    statements: Insert, Select, Update, Delete with operators, functions, and set operator

In this practical perform some basic following command

• .       Create Table
• .       Insert
• .       Select
• .       Update
• .       Delete with operators
• .       Functions
• .       set operator

mysql> create database assign1;

mysql> use assign1;

mysql> create table emp(Id int(20),Name char(40),DOB char(40),Designation char(40),Join_date char(40),Salary int(20),Department char

    -> (40));

mysql> desc emp;

mysql> alter table emp add primary key (Id);

mysql> desc emp;

mysql> insert into emp values(102,”Navin”,”1-06-1998″,”Developer”,”28-07-2016″,5000,”Electronics”);

mysql> insert into emp values(103,”Iram”,”12-09-1998″,”Tester”,”01-11-2015″,25000,”Electronics”);

mysql> insert into emp values(104,”Nikul”,”20-12-1998″,”Developer”,”25-03-2014″,15000,”Computer”);

mysql> select * from emp;

mysql> select * from emp where Department=”Computer”;

mysql> select * from emp where Salary>20000;

mysql> delete from emp where Designation=”Manager”;

mysql> select * from emp;

mysql> select Name from emp where Salary=(select min(Salary) from emp);

mysql> select Name from emp where Salary=(select max(Salary) from emp);

mysql> select avg(Salary) from emp;

mysql> select avg(Salary) from emp where Department=’Electronics’;

mysql> select count(Id) from emp where Department=”Computer”;

mysql> select Name from emp where Name like “N%l”;

mysql> select Name from emp where Salary BETWEEN 10000 AND 80000;

mysql> select Name from emp where Salary in(5000,20000,30000,50000);

mysql> select Name from emp where Department=”ELectronics”;

mysql> create table emp1(Id int(20),Name char(40),DOB char(40),Designation char(40),Join_date char(40),Salary int(20),Department char  (40));

mysql> insert into emp1 values(101,”Roger”,”02-07-1997″,”Developer”,”29-04-2016″,”5000″,”ENTC”);

mysql> insert into emp1 values(111,”Steve”,”15-09-1999″,”Tester”,”31-10-2013″,”30000″,”Computer”);

mysql> select * from emp1;

mysql> select Id from emp UNION select Id from emp1;

mysql> insert into emp1 values(102,”Tony”,”12-08-2000″,”Tester”,”25-10-2016″,”35000″,”Computer”);

mysql> select Id from emp where Id IN (select Id from emp1);

mysql> select Id from emp where Id NOT IN (select Id from emp1);

Practical No -4   Design at least 10 SQL queries for suitable database application using SQL DML statements: all types of Join, Sub-Query and View.