Database Management (DI03016041)

Date: _________________
Practical No.5: Write SQL queries to use Update, alter, rename, delete, truncate anddistinct.
A. Objective:
SQL commands is to facilitate efficient data management in databases by providing
functionalities such as inserting new data, retrieving specific information, updating
existing records, deleting unnecessary data, and altering table structures.

B. Expected Program Outcomes (POs)

Basic and Discipline specific knowledge: Apply knowledge of basic mathematics,
science and engineering fundamentals and engineering specialization to solve the
engineering problems.
Problem analysis: Identify and analyse well-defined engineering problems using
codified standard methods.
Design/ development of solutions: Design solutions for engineering well-defined
technical problems and assist with the design of systems components or processes
to meet specified needs.
Project Management: Use engineering management principles individually, as a
team member or a leader to manage projects and effectively communicate about
well- defined engineering activities.
Life-long learning: Ability to analyze individual needs and engage in updating in
the context of technological changes in field of engineering.

C. Expected Skills to be developed based on competency:

“SQL Commands”
This practical is expected to develop the following skills.
1. Developing the skill to retrieve specific information from a database.
2. Gaining proficiency in manipulating data by using SQL commands.
3. Learning to effectively manage and organize data by utilizing SQL commands.

D. Expected Course Outcomes(Cos)

CO4: Apply SQL Commands for creating, manipulating and controlling databases.

E. Practical Outcome(PRo)
Students will be able to create and manipulate SQL tables.

F. Expected Affective domain Outcome(ADos)

1) Follow safety practices.
2) Follow Coding standards and practices.
3) Demonstrate working as a leader/ a team member.
256120316009 page-
 Database Management (DI03016041)

4) Follow ethical practices.

5) Maintain tools and equipment.

G. Prerequisite Theory:

SQL Commands
 SQL commands are instructions. It is used to communicate with the
database. Itis also used to perform specific tasks, functions, and queries of
data.
 SQL can perform various tasks like create a table, add data to tables, drop
thetable, modify the table, set permission for users.

Types of SQL Commands

 There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

256120316009 page-
 Database Management (DI03016041)

1. Data Definition Language (DDL)

DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc. All the command of DDL are auto-committed that means it
permanently save all the changes in the database. Here are some commands that
come under DDL:

a. CREATE
b. ALTER
c. DROP
d. TRUNCATE

a. CREATE:
It is used to create a new table in the database.
Syntax : CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Example : CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email
VARCHAR2(100), DOB DATE);

b. DROP:
It is used to delete both the structure and record stored in the table.
Syntax : DROP TABLE table_name;
Example : DROP TABLE EMPLOYEE;

c. ALTER :
It is used to alter the structure of the database. This change could be either to modify
the characteristics of an existing attribute or probably to add a new attribute.
Syntax: To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

ALTER TABLE table_name MODIFY(column_definitions.... );
Example :
ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

d. TRUNCATE :
It is used to delete all the rows from the table and free the space containing the table.
Syntax : TRUNCATE TABLE table_name;
Example : TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

DML commands are used to modify the database. It is responsible for all form
of changes in the database. The command of DML is not auto-committed that means
it can't permanently save all the changes in the database. They can be rollback. Here
are some commands that come under DML:

256120316009 page-
 Database Management (DI03016041)

a. INSERT
b. UPDATE
c. DELETE

a. INSERT :
The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax: INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1,
value2, value3,.....valueN);
Or
INSERT INTO TABLE_NAME VALUES (value1, value2, value3,..... valueN);

For example: INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

b. UPDATE :
This command is used to update or modify the value of a column in the table.

Syntax : UPDATE table_name SET [column_name1= value1, .. column_nameN =valueN]

[WHERE CONDITION]

For example : UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'

c. DELETE :
It is used to remove one or more row from a table.

Syntax : DELETE FROM table_name [WHERE condition];

For example : DELETE FROM students WHERE Author="Sonoo";

3. Data Control Language

DCL commands are used to grant and take back authority from any database
user.
Here are some commands that come under DCL:

a. Grant
b. Revoke

a. Grant : It is used to give user access privileges to a database.

Example : GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

b. Revoke : It is used to take back permissions from the user.

Example : REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

256120316009 page-
 Database Management (DI03016041)

4. Data Query Language

DQL is used to fetch the data from the database. It uses only one command:

SELECT : This is the same as the projection operation of relational algebra. It is

usedto select the attribute based on the condition described by WHERE clause.

Syntax: SELECT expressions FROM TABLES WHERE conditions;

For example: SELECT emp_name FROM employee WHERE age > 20;

4. Resources/Equipment Required
Sr. Instrument/Equipment/
Specification Quantity
No. Components/Trainer kit
Hardware: Computer Computer (i3-i5 preferable), RAM minimum
1
System 2 GB and onwards
2 As Per
Operating System Windows/ Linux
Batch
3 Software Oracle Size

Notepad, Notepad++, Sublime Text or

4 Text Editor
similar

H. Safety and necessary Precautions followed

NA

I. Source code:

Write SQL queries to use Update, alter, rename, delete, truncate and distinct.

Table: ACCOUNT.

(a) Change the name ‘pateljigar’ to ‘patelhiren’.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
_ ___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

(b) Change the name and city where account number is A005. (new name = ‘kothari
nehal’and new city = ‘patan’).
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
_ ___________________________________________________________________________________________________________________________

(c) Display only those records where loan taken status is ‘YES’.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(d) Add the new column (address varchar2 (20)) into table ACCOUNT.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(e) Create another table ACCOUNT_TEMP (acc_no, name, balance) from table
ACCOUNT.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

(f) Rename the table ACCOUNT to ACCOUNT_MASTER.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(g) Update the column balance for all the account holders. (Multiply the balance
by2 foreach account holders)
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
________________________________________________________________________________________________________________________

(h) Delete the records whose account no is A004.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

Table: LOAN.
(a) For each loan holders Add 100000 Rs. Amount into the column loan_amt.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(b) For each loan holders Increase the interest rate 2%.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(c) Display only those records where loan holder taken a loan in month of January.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

(d) Modify the structure of table LOAN by adding one column credit_no
varchar2(4).
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(e) Display the Loan amount*2 of table LOAN.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(f) Display the records of table LOAN by date wise in ascending order.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(g) Display the records of table LOAN by account number wise in

descendingOrder.
___________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(h) Increase the size 5 to 7 of column acc_no.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

Table: INSTALLMENT.
(a) Change the Inst_Date ‘2-Feb-04’ to ’3-Mar-04’.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(b) Reduce 5000 amount from all Installment holders.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

(c) Add the amount 5000 where loan no is ‘L003’ and ‘L002’.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(d) Change the column size of 5 to 7 where column name is Loan_no.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(e) Delete row where inst_no is ‘I001’.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

(f) Only create a structure of table installment1 from table installment.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

Table: TRANSACTION.
(a) Insert any duplicate value and display all the records without any
duplicaterows.
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

(b) Select all the records in descending order(account number wise).

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

(c) Display amt, date, and type of transaction by date wise.

___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________________

246120316047 page-
 Database Management (DI03016041)

J. Practical related Quiz.

1) Which SQL command is used to add new data into a database table?
a) SELECT b) INSERT
c) DELETE d) UPDATE

2) Which SQL command is used to modify the structure of a table in a database?

a) ALTER b) UPDATE
c) MODIFY d) CHANGE

3) Which SQL command is used to remove all data from a table in a database
whilekeeping the table structure intact?
a) TRUNCATE b) DELETE
c) REMOVE d) CLEAN

K. References / Suggestions
1) [Link]
2) [Link]
3) [Link]

L. Assessment-Rubrics

Faculty
Marks Obtained Date
Signature
Program Implementation Student’s engagement
Correctness and Presentation in practical activities Total
(4) Methodology (3) (3) (10)
R1 R2 R3

246120316047 page-