DBM: Group A- Database Programming Languages – SQL

MES’s Wadia College of Engineering Pune-01

Department of Electronics and Telecommunication Engineering
Name of Student: Class:
Semester/Year: Roll No:
Date of Performance: Date of Submission:
Examined By: Experiment No: Part A-01

Group A- Database Programming Languages – SQL

ASSIGNMENT NO: 01
AIM: Study of Open Source Relational Databases: MySQL

OBJECTIVES:
• To develop basic, intermediate and advanced Database programming skills.
• To develop basic Database administration skill.

APPRATUS:
• Operating System recommended: 64-bit Open source Linux or its derivative
• Database Language: MySQL

INSTALLATION STEP:
Step 1: Update and Install
sudo apt-get update
sudo apt-get install mysql-server
Step 2: Create MySql db directory structure
sudo mysql_install_db
Step 3: Remove some dangerous defaults and lock down access to our database system
sudo mysql_secure_installation
Step 4: To Access Root User
mysql –u username –p
Enter password:

CONCLUSION:

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

QUESTIONS:
1. What is Relational Database Management System?
2. What is MySQL Server?
3. What are the features of MySQL?
4. What is default port for MySQL server?
5. List the different datatypes in MySQL.
6. Explain DML, DDL, TCL and DCL with syntax
7. Write down the command to get list of databases in MySQL

Assignment Evaluation

0: Not done 2: Late Complete 4: Complete

1: Incomplete 3: Needs improvement 5: Well Done

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

MES’s Wadia College of Engineering Pune-01

Department of Electronics and Telecommunication Engineering
Name of Student: Class:
Semester/Year: Roll No:
Date of Performance: Date of Submission:
Examined By: Experiment No: Part B-02

GROUP: A ASSIGNMENT NO: 02

AIM:
Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as
Table, View, Index, Sequence, Synonym

OBJECTIVES:
• To develop basic, intermediate and advanced Database programming skills.
• To develop basic Database administration skill.

SOFTWARES REQUIRED:
• Operating System recommended: 64-bit Open source Linux or its derivative
• Front End: Java/PHP/Python Backend: MySQL database

Problem Statement:
(Create following tables with constraints, alter table, insert, drop table , rename table, view,
index, synonym, sequence/AUTO_INCREMENT)

Account(Acc_no, branch_name,balance)
branch(branch_name,branch_city,assets)
customer(cust_name,cust_street,cust_city)
Depositor(cust_name,acc_no)
Loan(loan_no,branch_name,amount)
Borrower(cust_name,loan_no)

Theory:

A schema is the collection of multiple database objects, which are known as schema objects.
These objects have direct access by their owner schema. Below table lists the schema objects.
Table - to store data
View - to project data in a desired format from one or more tables
Sequence - to generate numeric values
Index - to improve performance of queries on the tables
Synonym - alternative name of an object
Department of Electronics and Telecommunication Engineering
 DBM: Group A- Database Programming Languages – SQL

One of the first steps in creating a database is to create the tables that will store an
organization's data. Database design involves identifying system user requirements for
various organizational systems such as order entry, inventory management, and accounts
receivable. Regardless of database size and complexity, each database is comprised of tables.

Table of Contents
1. DDL
2. DML
3. DCL
4. TCL

DDL
DDL is short name of Data Definition Language, which deals with database schemas and
descriptions, of how the data should reside in the database.
• CREATE - to create a database and its objects like (table, index, views, store
procedure, function, and triggers)
• ALTER - alters the structure of the existing database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the
records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object
DML
DML is short name of Data Manipulation Language which deals with data manipulation and
includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc.,
and it is used to store, modify, retrieve, delete and update data in a database.
• SELECT - retrieve data from a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - Delete all records from a database table
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - interpretation of the data access path
• LOCK TABLE - concurrency Control
DCL
DCL is short name of Data Control Language which includes commands such as GRANT
and mostly concerned with rights, permissions and other controls of the database system.
• GRANT - allow users access privileges to the database
• REVOKE - withdraw users access privileges given by using the GRANT command
TCL
TCL is short name of Transaction Control Language which deals with a transaction within a
database.
• COMMIT - commits a Transaction
• ROLLBACK - rollback a transaction in case of any error occurs
• SAVEPOINT - to rollback the transaction making points within groups
• SET TRANSACTION - specify characteristics of the transaction

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

----------------------------------------------------------------------------------------------------------------
-------
SQL Statements For Tables

char(n). Fixed length character string, with user-specified length n.

varchar(n). Variable length character strings, with user-specified maximum length n.
int. Integer (a finite subset of the integers that is machine-dependent).
smallint. Small integer (a machine-dependent subset of the integer domain type).
numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of
decimal point.
real, double precision. Floating point and double-precision floating point numbers, with machine-dependent
precision.
float(n). Floating point number, with user-specified precision of at least n digits.

Constraints

Constraints are the set of rules defined in Oracle tables to ensure data integrity. These rules
are enforced placed for each column or set of columns. Whenever the table participates in
data action, these rules are validated and raise exception upon violation. The available
constraint types are NOT NULL, Primary Key, Unique, Check, and Foreign Key.
The below syntax can be used to impose constraint at the column level.
Syntax:
column [data type] [CONSTRAINT constraint_name] constraint_type
All constraints except NOT NULL, can also be defined at the table level. Composite
constraints can only be specified at the table level.

NOT NULL Constraint

A NOT NULL constraint means that a data row must have a value for the column specified
as NOT NULL. If a column is specified as NOT NULL, the Oracle RDBMS will not allow
rows to be stored to the employee table that violate this constraint. It can only be defined at
column level, and not at the table level.
Syntax:
COLUMN [data type] [NOT NULL]

UNIQUE constraint

Sometimes it is necessary to enforce uniqueness for a column value that is not a primary key
column. The UNIQUE constraint can be used to enforce this rule and Oracle will reject any
rows that violate the unique constraint. Unique constraint ensures that the column values are
distinct, without any duplicates.
Syntax:
Column Level:
COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]
Table Level: CONSTRAINT [constraint name] UNIQUE (column name)
Note: Oracle internally creates unique index to prevent duplication in the column values.
Indexes would be discussed later in PL/SQL.
CREATE TABLE TEST
( ... ,
NAME VARCHAR2(20)

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

CONSTRAINT TEST_NAME_UK UNIQUE,

... );
In case of composite unique key, it must be defined at table level as below.

CREATE TABLE TEST

( ... ,
NAME VARCHAR2(20),
STD VARCHAR2(20) ,
CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD)
);

Primary Key

Each table must normally contain a column or set of columns that uniquely identifies rows of
data that are stored in the table. This column or set of columns is referred to as the primary
key. Most tables have a single column as the primary key. Primary key columns are restricted
against NULLs and duplicate values.
Points to be noted -
1. A table can have only one primary key.
2. Multiple columns can be clubbed under a composite primary key.
3. Oracle internally creates unique index to prevent duplication in the column values.
Indexes would be discussed later in PL/SQL.
Syntax:
Column level:
COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]
Table level:
CONSTRAINT [constraint name] PRIMARY KEY [column (s)]
The following example shows how to use PRIMARY KEY constraint at column level.
CREATE TABLE TEST (ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... );

The following example shows how to define composite primary key using PRIMARY KEY
constraint at the table level.
CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) );

Foreign Key

When two tables share the parent child relationship based on specific column, the
joining column in the child table is known as Foreign Key. This property of corresponding
column in the parent table is known as Referential integrity. Foreign Key column values in
the child table can either be null or must be the existing values of the parent table. Please note
that only primary key columns of the referenced table are eligible to enforce referential
integrity.
If a foreign key is defined on the column in child table then Oracle does not allow the parent
row to be deleted, if it contains any child rows. However, if ON DELETE CASCADE option
is given at the time of defining foreign key, Oracle deletes all child rows while parent row is
being deleted. Similarly, ON DELETE SET NULL indicates that when a row in the parent
table is deleted, the foreign key values are set to null.
Syntax:

Column Level:

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name

(column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name)

REFERENCES] [referenced table name (referenced column name)]

The following example shows how to use FOREIGN KEY constraint at column level.
CREATE TABLE TEST (ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES
PARENT_TEST(ccode), ... );

Usage of ON DELETE CASCADE clause

CREATE TABLE TEST (ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES
PARENT_TEST (ccode) ON DELETE CASCADE, ... );

Check constraint

Sometimes the data values stored in a specific column must fall within some acceptable range
of values. A CHECK constraint requires that the specified check condition is either true or
unknown for each row stored in the table. Check constraint allows to impose a conditional
rule on a column, which must be validated before data is inserted into the column. The
condition must not contain a sub query or pseudo column CURRVAL NEXTVAL, LEVEL,
ROWNUM, or SYSDATE.
Oracle allows a single column to have more than one CHECK constraint. In fact, there is no
practical limit to the number of CHECK constraints that can be defined for a column.
Syntax:
Column level:
COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]
Table level:
CONSTRAINT [name] CHECK (condition)
The following example shows how to use CHECK constraint at column level.
CREATE TABLE TEST ( ..., GRADE char (1) CONSTRAINT TEST_CHK CHECK
(upper (GRADE) in ('A','B','C')), ... );

The following example shows how to use CHECK constraint at table level.
CREATE TABLE TEST ( ..., CONSTRAINT TEST_CHK CHECK (stdate < = enddate), );

create database Student;

show databases;
use Student;
drop database Student;

DDL : create, desc, alter, drop, rename,

create table <table_name> (column_name1 dat_type(size) [constraint], column_name2

data_type(size) [constraint],....column_nameN dat_type(size) [constraint]);

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

create table Student_info(RollNO integer(10) NOT NULL, Name varchar(30), MobNo

integer(10));
desc student_info;
show tables;
drop table student_info;
alter table student_info add(emailid varchar(30));
alter table student_info modify(emailid char(10));
rename Student_info to Stud_data;
create index idx on student_info(rollno);
alter table student_info drop index idx;

index::
show databases;
use student;
show tables;
desc student_info;

Create table using subquery

A table can be created from an existing table in the database using a subquery option.It copies
the table structure as well as the data from the table. Data can also be copied based on
conditions.The column data type definitions including the explicitly imposed NOT NULL
constraints are copied into the new table.
The below CTAS script creates a new table EMP_BACKUP. Employee data of department
20 gets copied into the new table
.
CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database very fast. The users cannot see the
indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without
(because the indexes also need an update).
So, only create indexes on columns that will be frequently searched against.
CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column1, column2, ...);

Example:

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

create index roll_no on student_info(rollno);

alter table student_info drop index roll_no;

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);

Note: The syntax for creating indexes varies among different databases. Therefore: Check the
syntax for creating indexes in your database.
CREATE INDEX Example

The SQL statement below creates an index named "idx_lastname" on the "LastName"
column in the "Persons" table:
CREATE INDEX idx_lastname
ON Persons (LastName);

If you want to create an index on a combination of columns, you can list the column names
within the parentheses, separated by commas:
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

MS Access:
DROP INDEX index_name ON table_name;

SQL Server:
DROP INDEX table_name.index_name;

DB2/Oracle:
DROP INDEX index_name;

MySQL:
ALTER TABLE table_name
DROP INDEX index_name;

SQL Sequence

Sequence is a feature supported by some database systems to produce unique values on

demand. Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence.
AUTO_INCREMENT is applied on columns, it automatically increments the column value
by 1 each time a new record is entered into the table. Sequence is also some what similar to
AUTO_INCREMENT but it has some extra features.

create table Student_info(RollNO integer(10) Primary key AUTO_INCREMENT, Name

varchar(30), MobNo integer(10));

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

insert into Student_info (Name, MobNo) values (‘Amol’, 9049417616);

insert into Student_info (Name, MobNo) values (‘Amol’, 9049417616);

Creating Sequence

Syntax to create sequences is,

CREATE Sequence sequence-name

start with initial-value
increment by increment-value
maxvalue maximum-value
cycle|nocycle

Initial-value specifies the starting value of the Sequence, increment-value is the value by
which sequence will be incremented and maxvalue specifies the maximum value until which
sequence will increment itself. Cycle specifies that if the maximum value exceeds the set
limit, sequence will restart its cycle from the beginning. No cycle specifies that if sequence
exceeds maxvalue an error will be thrown.
Example to create Sequence

The sequence query is following

CREATE Sequence seq_1

start with 1
increment by 1
maxvalue 999
cycle ;

Example to use Sequence

The class table,

ID NAME
1 abhi
2 adam
4 alex

The sql query will be,

INSERT into class value(seq_1.nextval,'anu');

Result table will look like,

ID NAME
1 abhi
2 adam
4 alex
1 anu

Once you use nextval the sequence will increment even if you don't Insert any record into the
table.

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

CREATE SYNONYM:

Examples To define the synonym offices for the table locations in the schema hr, issue the
following statement:

CREATE SYNONYM offices

FOR hr.locations;

To create a PUBLIC synonym for the employees table in the schema hr on the remote
database, you could issue the following statement:

CREATE PUBLIC SYNONYM emp_table

FOR hr.employees@remote.us.oracle.com;
A synonym may have the same name as the underlying object, provided the underlying object
is contained in another schema.

Oracle Database Resolution of Synonyms: Example Oracle Database attempts to resolve

references to objects at the schema level before resolving them at the PUBLIC synonym
level. For example, the schemas oe and sh both contain tables named customers. In the next
example, user SYSTEM creates a PUBLIC synonym named customers for oe.customers:

CREATE PUBLIC SYNONYM customers FOR oe.customers;

If the user sh then issues the following statement, then the database returns the count of rows
from sh.customers:

SELECT COUNT(*) FROM customers;

To retrieve the count of rows from oe.customers, the user sh must preface customers with the
schema name. (The user sh must have select permission on oe.customers as well.)

SELECT COUNT(*) FROM oe.customers;

If the user hr's schema does not contain an object named customers, and if hr has select
permission on oe.customers, then hr can access the customers table in oe's schema by using
the public synonym customers:

SELECT COUNT(*) FROM customers;

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as
if the data were coming from one single table.
CREATE VIEW Syntax

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: A view always shows up-to-date data! The database engine recreates the data, using the
view's SQL statement, every time a user queries a view.
SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has several views installed by default.

The view "Current Product List" lists all active products (products that are not discontinued)
from the "Products" table. The view is created with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No;

Then, we can query the view as follows:

SELECT * FROM [Current Product List];

Another view in the Northwind sample database selects every product in the "Products" table
with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);

We can query the view above as follows:

SELECT * FROM [Products Above Average Price];

Another view in the Northwind database calculates the total sale for each category in 1997.
Note that this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName, Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;

We can query the view above as follows:

SELECT * FROM [Category Sales For 1997];

We can also add a condition to the query. Let's see the total sale only for the category
"Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName = 'Beverages';
SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

FROM table_name
WHERE condition;

Now we want to add the "Category" column to the "Current Product List" view. We will
update the view with the following SQL:
CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;
SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax
DROP VIEW view_name;

Examples:
insert into student_info values(3,'amol',2154455,'abc@gmai');
insert into student_info values(1,'vijay',21543255,'asvs@gmai');
select * from student_info;

view::
create view myview as select rollno,name from student_info;
select * from myview;
create view myview2 as select rollno,name from student_info where rollno>1;

(Create following tables with constraints, alter table, insert, drop table , rename table, view,
index, synonym, sequence/AUTO_INCREMENT)

Account(Acc_no, branch_name,balance)
branch(branch_name,branch_city,assets)
customer(cust_name,cust_street,cust_city)
Depositor(cust_name,acc_no)
Loan(loan_no,branch_name,amount)
Borrower(cust_name,loan_no)

Solve following queries:

Q1.Create Depositor table with foreign key with on delete cascade constraint on columns
cust_name and acc_no.
Q2. Create Borrower table with foreign key with on delete cascade constraint on columns
cust_name,loan_no.
Q3. Create Account table with primary key and AUTO_INCREMENT constraint on Acc_no
column
Q4. Create Loan table with primary key and AUTO_INCREMENT constraint on loan_no
column.
Q5. Create Customer table with primary key constraint on cust_name column.

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

Q6. Create View on Account table and Loan Table.

Q7. Insert following Data into above tables
Q.8. Create synonym for customer table as cust.
Q.9. Create sequence acc_seq and use in Account table for acc_no column.
Q.10 Insert the following data into all the above tables.

**** Attach a separate sheet with query statement and output.

Conclusion:

Assignment Evaluation

0: Not done 2: Late Complete 4: Complete

1: Incomplete 3: Needs improvement 5: Well Done

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

MES’s Wadia College of Engineering Pune-01

Department of Electronics and Telecommunication Engineering
Name of Student: Class:
Semester/Year: Roll No:
Date of Performance: Date of Submission:
Examined By: Experiment No: Part A-03

GROUP: B ASSIGNMENT NO: 03 and 04

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

OBJECTIVES:
• To develop basic, intermediate and advanced Database programming skills.
• To develop basic Database administration skill.

SOFTWARE REQUIRED:
• Operating System recommended: 64-bit Open source Linux or its derivative
• Front End: Java/PHP/Python Backend: MySQL database

Problem Statement:
(Insert, Select, Update, Delete, operators, functions, set operator, all constraints, view, index,
synonym, sequence)
Account(Acc_no, branch_name,balance)
branch(branch_name,branch_city,assets)
customer(cust_name,cust_street,cust_city)
Depositor(cust_name,acc_no)
Loan(loan_no,branch_name,amount)
Borrower(cust_name,loan_no)

Input: insert Data into above tables and fire queries on databases;

Theory:

Set Operators:

The set operations union, intersect, and except operate on relations and correspond to the
relational algebra operations   −
Each of the above operations automatically eliminates duplicates; to retain all duplicates use
the corresponding multiset versions union all, intersect all and except all.

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

Suppose a tuple occurs m times in r and n times in s, then, it occurs:

m + n times in r union all s
min(m,n) times in r intersect all s
max(0, m – n) times in r except all s

Aggregate Functions:

These functions operate on the multiset of values of a column of a relation, and return a value
avg: average value
min: minimum value
max: maximum value
sum: sum of values
count: number of values

IMPLEMENTATION:
Solve following query: Create different tables given below with appropriate constraints like
primary key, foreign key, check constrains, not null etc.
a) Account(Acc_no, branch_name,balance)
b) branch(branch_name,branch_city,assets)
c) customer(cust_name,cust_street,cust_city)
d) Depositor(cust_name,acc_no)
e) Loan(loan_no,branch_name,amount)
f) Borrower(cust_name,loan_no)
1. Find the names of all branches in loan relation.
2. Find all loan numbers for loans made at Akurdi Branch with loan amount > 12000.
3. Find all customers who have a loan from bank. Find their names, loan_no and loan
amount.
4. List all customers in alphabetical order who have loan from Akurdi branch.
5. Find all customers who have an account or loan or both at bank.
6. Find all customers who have both account and loan at bank.
7. Find all customers who have account but no loan at the bank.
8. Find average account balance at Akurdi branch.
9. Find the average account balance at each branch
10. Find no. of depositors at each branch.
11. Find the branches where average account balance > 12000
12. Find number of tuples in customer relation.
13. Calculate total loan amount given by bank.

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

14. Delete all loans with loan amount between 1300 and 1500.
15. Delete all tuples at every branch located in Nigdi.

CONCLUSION:

QUESTIONS:
4. How we can make use of Create statement to create multiple objects?
5. What is View? How is can helpful to user?
6. What is an Index? What are the types of indexes?
7. What is Sequence? How it is generated in MySQL?
8. What are different the Query Optimization technique's?
9. How to create synonyms in MySQL?
10. What are ACID properties?
11. Which are the different commands used to modify database object?
12. List down the different operators that support MySQL
13. What is difference between Delete, Drop and Truncate?

Assignment Evaluation

0: Not done 2: Late Complete 4: Complete

1: Incomplete 3: Needs improvement 5: Well Done

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

MES’s Wadia College of Engineering Pune-01

Department of Electronics and Telecommunication Engineering
Name of Student: Class:
Semester/Year: Roll No:
Date of Performance: Date of Submission:
Examined By: Experiment No: Part A-04

GROUP: B ASSIGNMENT NO: 05

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

OBJECTIVES:
• To develop basic, intermediate and advanced Database programming skills.
• To develop basic Database administration skill.

SOFTWARE REQUIRED:
• Operating System recommended: 64-bit Open source Linux or its derivative
• Front End: Java/PHP/Python Backend: MySQL database

IMPLEMENTATION:
1. Create following Tables
cust_mstr(cust_no,fname,lname)
add_dets(code_no,add1,add2,state,city,pincode)
Retrieve the address of customer Fname as 'xyz' and Lname as 'pqr'

2. Create following Tables

cust_mstr(custno,fname,lname)
acc_fd_cust_dets(codeno,acc_fd_no)
fd_dets(fd_sr_no,amt)
List the customer holding fixed deposit of amount more than 5000

3. Create following Tables

emp_mstr(e_mpno, f_name, l_name, m_name, dept,desg, branch_no)

Department of Electronics and Telecommunication Engineering

 DBM: Group A- Database Programming Languages – SQL

branch_mstr (name, b_no)

List the employee details along with branch names to which they belong

4. Create following Tables

emp_mstr(emp_no, f_name, l_name, m_name, dept)
cntc_dets(code_no, cntc_type, cntc_data)
List the employee details along with contact details using left outer join & right join

5. Create following Tables

cust_mstr (cust_no, fname, lname)
add_dets (code_no, pincode)
List the customers who do not have bank branches in their vicinity.

6. Create View on borrower table by selecting any two columns and perform insert
update delete operations

7. Create view on borrower and depositor table by selecting any one column from each
table perform insert update delete operations

8. Create updateable view on borrower table by selecting any two columns and perform
insert update delete operations.

CONCLUSION:

QUESTIONS:
1. What is Join Clause?
2. What are the different kinds of Joins? Explain in details?
3. What is Cross Join?
4. What is Null value? How it is different from zero value?
5. What are different MySQL constraints?
6. What is purpose of Normalization? How does it work?
7. What is difference between Join and Union?
8. What is the different aggregation function in MySQL?
9. Explain where and having clause in detail.
10. What is difference between Unique Key and Primary Key?

Assignment Evaluation
0: Not done 2: Late Complete 4: Complete

1: Incomplete 3: Needs improvement 5: Well Done

Department of Electronics and Telecommunication Engineering