Session 2: DDL, DML, DQL, DCL, TCL

Estimated Completion Time: 60 Minutes

Let’s now focus on the online feedback system for training company called Alliance.
The system flow is well analyzed & ER Model is ready.
Now it’s time to develop the required table structure, but the constraint from client is that all
queries must be written using only ANSI SQL Syntax.

Exercise 2.1

Hands-on Exercise Objective

After completing the hands-on exercises, you will be able to:
Understand How to Create Tables

Problem Statement:
You have to create all tables whose structure is as mentioned below:

Trainer_Info
Field Description Type Size
Trainer_Id Unique ID to every trainer Character 20
Salutation Salutaion of trainer Character 7
Trainer_Name Name of trainer Character 30
Trainer_Location Location of trainer Character 30
Trainer_Track Track of trainer e.g. JAVA, .Net, Mainframe Character 15
Trainer_Qualification Qualification of trainer e.g. MCA,B. Tech Character 100
Trainer_Experiance Experience of trainer e.g .2,4 years Integer 11
Trainer_Email Email Id of trainer Character 100
Trainer_Password Login password of Trainer Character 20
Batch_Info
Field Description Type Size
Batch_Id Unique ID to every batch Character 20
Batch_Owner Batch owner name Character 30
Batch_BU_Name Business unit to which batch belongs Character 30

Module_Info
Field Description Type Size
Module_Id Unique ID to every module e.g J2SE, J2EE Character 20
Module_Name Name of module e.g. Core Java SE 1.6 Character 40
Module_Duration Duration in hrs Integer 11

Associate_Info
Field Description Type Size
Associate_Id Unique ID to every associate Character 20
Salutation Salutaion of associate Character 7
Associate_Name Name of associate Character 30
Associate_Location Location of associate Character 30
Associate_Track Track of associate e.g. JAVA, .Net, Mainframe Character 15
Associate_Qualification Qualification of associate e.g. MCA,B. Tech Character 200
Associate_Email Email Id of associate Character 100
Associate_Password Login password of associate Character 20

Questions
Field Description Type Size
Question_Id Unique ID to every question Character 20
Module_Id Module id from module Character 20
Question_Text Actual feedback question Character 900

Associate_Status
Field Description Type Size
Associate_Id Associate Id from associate_Info Character 20
Module_Id Module Id from module Character 20
Start_Date Date when associate batch started Character 20
End_Date Date when associate batch ended Character 20
AFeedbackGiven Flag to test whether Associate has given feedback or not Character 20
TFeedbackGiven Flag to test whether Trainer has given feedback or not Character 20
 Trainer_Feedback
Field Description Type Size
Trainer_Id Trainer Id from trainer_Info Character 20
Question_Id Question Id from Questions Character 20
Batch_Id Batch Id from batch_Info Character 20
Module_Id Module Id from module Character 20
Trainer_Rating Rating given by trainer for a perticular question Integer 11

Associate_Feedback
Field Description Type Size
Associate_Id Associate Id from associate_Info Character 20
Question_Id Question Id from Questions Character 20
Module_Id Module Id from module Character 20
Associate_Rating Rating given by associate for a perticular question Integer 11

Login_Details
Field Description Type Size
User_Id User id for login Character 20
User_Password Password to authenticate user Character 20

Deliverables Expected:
All Tables creation as per details mentioned above

Exercise 2.2

Hands-on Exercise Objective

After completing the hands-on exercises, you will be able to:
Understand how to alter table

Problem Statement:
Modify the table Associate_Status to include following two columns:
Column Name: Batch_Id
Details: Batch Id from batch_Info
Data Types: Character
Size: 20
Column Name: Trainer_Id
Details: Trainer Id from trainer_Info
Data Types: Character
Size: 20
Modify two columns data types of the table Associate_Status as follows:
Start_Date
Desired Type: Date
End_Date
Desired Type: Date

Deliverables Expected:
New Columns in batch_Info & trainer_Info & changed column data types

Exercise 2.3

Hands-on Exercise Objective

After completing the hands-on exercises, you will be able to:
Insert data into tables

Problem Statement:
Insert below details into table:

Trainer_Info
Trainer_Id,Salutation,Trainer_Name,Trainer_Location,Trainer_Track,Trainer_Qualification,Train
er_Experiance,Trainer_Email,Trainer_Password

F001,Mr.,PANKAJ GHOSH,Pune,Java,Bachelor of
Technology,12,Pankaj.Ghosh@alliance.com,fac1@123
F002,Mr.,SANJAY RADHAKRISHNAN ,Bangalore,DotNet,Bachelor of
Technology,12,Sanjay.Radhakrishnan@alliance.com,fac2@123
F003,Mr.,VIJAY MATHUR,Chennai,Mainframe,Bachelor of
Technology,10,Vijay.Mathur@alliance.com,fac3@123
F004,Mrs.,NANDINI NAIR,Kolkata,Java,Master of Computer
Applications,9,Nandini.Nair@alliance.com,fac4@123
F005,Miss.,ANITHA PAREKH,Hyderabad,Testing,Master of Computer
Applications,6,Anitha.Parekh@alliance.com,fac5@123
F006,Mr.,MANOJ AGRAWAL ,Mumbai,Mainframe,Bachelor of
Technology,9,Manoj.Agrawal@alliance.com,fac6@123
F007,Ms.,MEENA KULKARNI,Coimbatore,Testing,Bachelor of
Technology,5,Meena.Kulkarni@alliance.com,fac7@123
F009,Mr.,SAGAR MENON ,Mumbai,Java,Master of Science In Information
Technology,12,Sagar.Menon@alliance.com,fac8@123
Batch_Info
Batch_Id,Batch_Owner,Batch_BU_Name
B001,MRS.SWATI ROY,MSP
B002,MRS.ARURNA K,HEALTHCARE
B003,MR.RAJESH KRISHNAN,LIFE SCIENCES
B004,MR.SACHIN SHETTY,BFS
B005,MR.RAMESH PATEL,COMMUNICATIONS
B006,MRS.SUSAN CHERIAN,RETAIL & HOSPITALITY
B007,MRS.SAMPADA JAIN,MSP
B008,MRS.KAVITA REGE,BPO
B009,MR.RAVI SEJPAL,MSP

Module_Info
Module_Id,Module_Name,Module_Duration
O10SQL,Oracle 10g SQL ,16
O10PLSQL,Oracle 10g PL/ SQL ,16
J2SE,Core Java SE 1.6,288
J2EE,Advanced Java EE 1.6,80
JAVAFX,JavaFX 2.1,80
DOTNT4,.Net Framework 4.0 ,50
SQL2008,MS SQl Server 2008,120
MSBI08,MS BI Studio 2008,158
SHRPNT,MS Share Point ,80
ANDRD4,Android 4.0,200
EM001,Instructor,0
EM002,Course Material,0
EM003,Learning Effectiveness,0
EM004,Environment,0
EM005,Job Impact,0
TM001,Attendees,0
TM002,Course Material,0
TM003,Environment,0

Associate_Info
Associate_Id,Salutation,Associate_Name,Associate_Location,Associate_Track,Associate_Qualifi
cation,Associate_Email,Associate_Password

A001,Miss.,GAYATHRI NARAYANAN,Gurgaon,Java,Bachelor of
Technology,Gayathri.Narayanan@hp.com,tne1@123
A002,Mrs.,RADHIKA MOHAN,Kerala,Java,Bachelor of Engineering In Information
Technology,Radhika.Mohan@cognizant.com,tne2@123
A003,Mr.,KISHORE SRINIVAS,Chennai,Java,Bachelor of Engineering In
Computers,Kishore.Srinivas@ibm.com,tne3@123
A004,Mr.,ANAND RANGANATHAN,Mumbai,DotNet,Master of Computer
Applications,Anand.Ranganathan@finolex.com,tne4@123
A005,Miss.,LEELA MENON,Kerala,Mainframe,Bachelor of Engineering In Information
Technology,Leela.Menon@microsoft.com,tne5@123
A006,Mrs.,ARTI KRISHNAN,Pune,Testing,Master of Computer
Applications,Arti.Krishnan@cognizant.com,tne6@123
A007,Mr.,PRABHAKAR SHUNMUGHAM,Mumbai,Java,Bachelor of
Technology,Prabhakar.Shunmugham@honda.com,tne7@123

Questions
Question_Id,Module_Id,Question_Text
Q001,EM001,Instructor knowledgeable and able to handle all your queries
Q002,EM001,All the topics in a particular course handled by the trainer without any gaps or
slippages
Q003,EM002,The course materials presentation, handson, etc. refered during the training are
relevant and useful.
Q004,EM002,The Hands on session adequate enough to grasp the understanding of the topic.
Q005,EM002,The reference materials suggested for each module are adequate.
Q006,EM003,Knowledge and skills presented in this training are applicatible at your work
Q007,EM003,This training increases my proficiency level
Q008,EM004,The physical environment e.g. classroom space, air-conditioning was conducive to
learning.
Q009,EM004,The software/hardware environment provided was sufficient for the purpose of
the training.
Q010,EM005,This training will improve your job performance.
Q011,EM005,This training align with the business priorities and goals.
Q012,TM001,Participants were receptive and had attitude towards learning.
Q013,TM001,All participats gained the knowledge and the practical skills after this training.
Q014,TM002,The course materials presentation, handson, etc. available for the session covers
the entire objectives of the course.
Q015,TM002,Complexity of the course is adequate for the particpate level.
Q016,TM002,Case study and practical demos helpful in understanding of the topic
Q017,TM003,The physical environment e.g. classroom space, air-conditioning was conducive to
learning.
Q018,TM003,The software/hardware environment provided was adequate for the purpose of
the training.

Associate_Status
Associate_Id,Module_Id,Batch_Id,Trainer_Id,Start_Date,End_Date
A001,O10SQL,B001,F001,2000-12-15,2000-12-25
A002,O10SQL,B001,F001,2000-12-15,2000-12-25
A003,O10SQL,B001,F001,2000-12-15,2000-12-25
A001,O10PLSQL,B002,F002,2001-2-1,2001-2-12
A002,O10PLSQL,B002,F002,2001-2-1,2001-2-12
A003,O10PLSQL,B002,F002,2001-2-1,2001-2-12
A001,J2SE,B003,F003,2002-8-20,2002-10-25
A002,J2SE,B003,F003,2002-8-20,2002-10-25
A001,J2EE,B004,F004,2005-12-1,2005-12-25
A002,J2EE,B004,F004,2005-12-1,2005-12-25
A003,J2EE,B004,F004,2005-12-1,2005-12-25
A004,J2EE,B004,F004,2005-12-1,2005-12-25
A005,JAVAFX,B005,F006,2005-12-4,2005-12-20
A006,JAVAFX,B005,F006,2005-12-4,2005-12-20
A006,SQL2008,B006,F007,2007-6-21,2007-6-28
A007,SQL2008,B006,F007,2007-6-21,2007-6-28
A002,MSBI08,B007,F006,2009-6-26,2009-6-29
A003,MSBI08,B007,F006,2009-6-26,2009-6-29
A004,MSBI08,B007,F006,2009-6-26,2009-6-29
A002,ANDRD4,B008,F005,2010-6-5,2010-6-28
A005,ANDRD4,B008,F005,2010-6-5,2010-6-28
A003,ANDRD4,B009,F005,2011-8-1,2011-8-20
A006,ANDRD4,B009,F005,2011-8-1,2011-8-20

Deliverables Expected:
All records inserted in tables

Exercise 2.4

Hands-on Exercise Objective

After completing the hands-on exercises, you will be able to:
Understand how to change data in table

Problem Statement:
Change the password of trainer F004 from fac4@123 to nn4@123

Deliverables Expected:
Updated password of trainer F004.

Exercise 2.5
 Hands-on Exercise Objective
After completing the hands-on exercises, you will be able to:
Understand how to delete data from table.

Problem Statement:
Remove following record form associate_status table
A003,J2EE,B004,F004,2005-12-1,2005-12-25

Deliverables Expected:
Removed record from associate status.

Exercise 2.6

Hands-on Exercise Objective

After completing the hands-on exercises, you will be able to:
Understand how to perform TOP N records.

Problem Statement:
Fetch first five trainers who have maximum years of experience & display there details.

Deliverables Expected:
Top five experience trainers.

Exercise 2.7

Hands-on Exercise Objective

After completing the hands-on exercises, you will be able to:
Understand transaction.

Problem Statement:
Begin transaction & insert below to records into Login_Details
'U001' Admin1@123
'U002' Admin2@123
Perform rollback operation & verify whether any records are inserted in table or not.

Deliverables Expected:
Empty table

Exercise 2.8

Hands-on Exercise Objective

After completing the hands-on exercises, you will be able to:
Understand TCL.

Problem Statement:
Create a dummy user in database. Grant create & select table privilege to him/her.
Repeat the above all queries using login credentials of newly created user.
Revoke the privilege assigned to this newly created user.

Deliverables Expected:
Same result as achieved till now.

Exercise 2.9

Hands-on Exercise Objective

After completing the hands-on exercises, you will be able to:
Understanding how to drop table.

Problem Statement:
Remove table Login_Details from database.

Deliverables Expected:
No table found message while querying Login_Details table.