Database Management Systems Lab Rohit Kumar

BTCS 505-18 2224525

Practical 02. Data Types, Creating Tables, Retrieval of Rows using Select
Statement, Conditional Retrieval of Rows, Alter and Drop Statements.
Data Types :- In a Database Management System (DBMS), a data type is a classification that
specifies the type of data that a column can hold in a database table. It defines the kind of
value and the operations that can be performed on that data. Data types are crucial because
they inform the DBMS how much physical storage to allocate for the data and how to
interpret bit patterns representing the data.

Key Functions of Data Types in DBMS:-

1. Data Integrity and Validation:
o Ensure that only valid data is stored in the database.

o Prevent insertion of invalid data types, enhancing data reliability.

2. Storage Optimization:

o Optimize the use of disk space by allocating appropriate storage sizes.

o Different data types consume different amounts of storage.

3. Performance Enhancement:
o Improve query execution speed through proper indexing and data retrieval
methods.

o Facilitate efficient data manipulation and processing.

4. Data Interpretation:

o Help the DBMS understand how to process and display the stored data.
o Define the permissible operations (e.g., arithmetic on numbers, concatenation
on strings).

Common Categories of Data Types:-

1. Numeric Data Types:

o Store numerical values.

o Examples: INTEGER, SMALLINT, FLOAT, DOUBLE, DECIMAL.
2. Character/String Data Types:

o Store alphanumeric characters and text.

o Examples: CHAR(n), VARCHAR(n), TEXT, CLOB.

3. Date and Time Data Types:

o Store dates, times, and timestamps.

1
Database Management Systems Lab Rohit Kumar
BTCS 505-18 2224525
o Examples: DATE, TIME, TIMESTAMP, DATETIME.
4. Binary Data Types:

o Store binary data such as images or files.

o Examples: BLOB, BINARY, VARBINARY.

5. Boolean Data Types:

o Store logical values.

o Examples: BOOLEAN, storing TRUE or FALSE.

6. Spatial Data Types:

o Store geometric data.

o Examples: GEOMETRY, POINT, LINESTRING, POLYGON.

7. JSON and XML Data Types:

o Store structured data in JSON or XML format.
o Examples: JSON, XML.

 Creating Tables, Retrieval of Rows using Select Statement, Conditional Retrieval

of Rows, Alter and Drop Statements :-
Create Database :- To create a database in MySQL, you use the CREATE DATABASE
statement. Optionally, you can specify the character set and collation for the database.
Syntax :- create database database_name;
Example :- create database rohan;

Show Database :- The SHOW DATABASES statement in MySQL is used to display all
databases that exist on the MySQL server to which the user is connected. It provides a list of
all databases that the user has access to, based on their permissions.

Syntax :- show databases;

Use Database :- The USE statement in MySQL is used to select a particular database to work
with. After executing the USE command, all subsequent SQL queries will apply to the

2
Database Management Systems Lab Rohit Kumar
BTCS 505-18 2224525
selected database unless another database is explicitly specified. It essentially sets the default
database for the current session.

Syntax :- use database_name;

Example :- use rohit;
Create Table :- The CREATE TABLE statement in MySQL is used to create a new table in a
database. A table consists of rows and columns, where each column represents a specific data
field, and each row is a record. The table is structured with defined data types, constraints,
and optional parameters like indexing or auto-incrementing.
Syntax :- create table table_name ( column_name1 data_type [constraint], column_name2
data_type [constraint], ... [table_constraints] );

Example :- create table allstudent(id int,fullname varchar(20),address varchar(30),class

varchar(10),age int,dob date);
Show Tables :- The SHOW TABLES statement in MySQL is used to list all the tables in the
currently selected database. This command provides an overview of the available tables and
helps in identifying the structure of the database.
Syntax :- show tables;

Insertion :- The INSERT statement in MySQL is used to add new rows (records) to a table.
You can insert a single row or multiple rows at once. The values provided in the INSERT
statement must match the order and data types of the columns in the table.

Syntax :- insert into table_name values (value1, value2, value3, ...);

Example :-

insert into allstudent values(100,"Aadarsh Kumar","Birpur","CSE",21,'2001-2-9');

insert into allstudent values(101,"Ronak Kumar","madhepura","CSE",20,'2001-11-10');

insert into allstudent values(100,"Ronak Kumar","katihar","CSE",19,'2003-11-9');

insert into allstudent values(101,"Rishav Kumar","Madhepura","CSE",21,'2001-11-9');
insert into allstudent values(102,"Ranjan Kumar","katihar","CSE",20,'2002-9-9');

3
Database Management Systems Lab Rohit Kumar
BTCS 505-18 2224525
insert into allstudent values(103,"Rohan Raj","darbhanga","CSE",22,'2001-10-4');
insert into allstudent values(104,"Ritesh Kumar","Supaul","CSE",20,'2004-11-9');

insert into allstudent values(105,"Raj Kumar","Araria","CSE",21,'2001-8-9');

insert into allstudent values(106,"Rohan","Supaul","CSE",21,'2003-10-9');

insert into allstudent values(107, "Amit", "bhimnagar","CSE",22,'2001-10-1');

insert into allstudent values(108,"Nitish Kumar","Balua","CSE",20,'2003-8-8');

Select Statement :- The SELECT statement in MySQL is used to retrieve data from one or
more tables in a database. The result is stored in a result table (called the result set). The
SELECT statement can be customized to fetch specific columns, filter rows, sort data, group
results, and much more.

Syntax :- select*from table_name;

Example :- select*from allstudent;

Conditional Retrieval of Rows :- Conditional retrieval of rows in MySQL is done using the
WHERE clause in the SELECT, UPDATE, or DELETE statements. The WHERE clause
filters the rows based on specific conditions or criteria. It retrieves only the rows that meet
the condition(s) specified. You can use a variety of operators and functions to define
conditions, such as comparison operators, logical operators, pattern matching, and more.

Syntax :- select field_name from table_name where field_name = value;

Example :- select fullname from student_details where fullname = "Raj Kumar";

4
Database Management Systems Lab Rohit Kumar
BTCS 505-18 2224525
Alter Statement :- The ALTER statement in MySQL is used to modify an existing table's
structure. It allows you to add, delete, or modify columns, as well as change other table
properties like indexes, constraints, and even rename the table itself. The ALTER statement is
essential for maintaining the flexibility of a database as the data model evolves over time.

Syntax:-alter table table_name add new_column _name data_type;

Example :-

alter table student_details add CGPA int;

describe student_details;

Drop Statement :- The DROP statement in MySQL is used to delete database objects such as
tables, databases, indexes, or views. Once an object is dropped, it is permanently removed
from the database, including all of its data and associated metadata. The DROP statement is a
powerful command and should be used with caution, as it cannot be rolled back unless you
have a backup.
Syntax :- alter table table_name drop column_name ;

Example:-
alter table student_detail drop CGPA;
describe ;

Syntax :- drop table table_name;

Example :-

5
Database Management Systems Lab Rohit Kumar
BTCS 505-18 2224525
Drop table student_details;
show tables;

Rename :- The RENAME statement in MySQL is used to change the name of a table or a
column. It allows you to update the names of these database objects without having to
recreate them. This can be useful for correcting naming errors, improving naming
conventions, or simply renaming objects to better reflect their purpose.
Syntax :- alter table table_name rename column old_column_name to new_column_name;
Example :-
alter table student_details rename column class to department;

describe student_details;

Syntax :- rename table student_details to student_information;

Example :-

rename table student_details to student_information;

show tables;

6
Database Management Systems Lab Rohit Kumar
BTCS 505-18 2224525

Truncate Statement :- The TRUNCATE statement in MySQL is used to quickly remove all
rows from a table, effectively emptying the table while retaining its structure (schema) for
future use. Unlike the DELETE statement, which removes rows one at a time and can be
slower, TRUNCATE is a more efficient way to clear out large amounts of data. It does not
generate individual row delete operations and typically executes faster.
Syntax :- truncate table student_details;

Example :-
truncate table student_details;

select*from student_details;