CAREER POINT UNIVERSITY

HAMIRPUR (H.P)

Practical File
Of
DBMS(Database Management System)(CS251)
Submitted by: Submitted to:
Name: Sweta sharma Ms Krishma
Roll No. H230659 Assistant Professor
Class: B.Tech CSE Department of CSE
Semester: 4th

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

SCHOOL OF ENGINEERING AND TECHNOLOGY
 INDEX

Sr. Practical Page no. Dates Sign

no.
1. Introduction to MySQL, MySQL 1-3 12/02/24
Command-Client and Query Browser.
2. To execute the data definition language 4-5 19/02/24
(DDL) commands and creating the table
using RDBMS.
3. Insertion values in table. 6 04/03/24

4. Select and update query. 7 11/03/24

5. Delect query and drop commands. 8 18/03/24

6. Joining two tables using inner join. 9 22/04/24

7. To join two tables using LEFT JOIN. 10 29/04/24

8. To join two tables using RIGHT JOIN. 11 13/05/24

9. Use of where, order by, limit clause. 12 20/ 05 /2

4
10. Write MySQL queries for numeric 13-17 27/05/24
functions, date – time function and
special function.
 PRACTICAL-1

AIM:- INTRODUCTION TO MYSQL, MYSQL,COMMAND-LINE-

CLIENT AND QUERY BROWSER.
MySQL:- MySQL is an open source SQL database management system developed by MySQLAB
SWEDEN.

MySQL-AB is a commercial company. It was originally developed to handle large database much
faster than existing solution.

Features of MySQL:-

• It is a relational database management system.

• It is an open source.
• It is very fast and reliable and easy to use .
• MySQL server works in client or server or embedded system.

Client server:-

It supports different back ends, several different client program and libraries, administrative tools
and a wide range of APIS.

Embedded:-

It means that you can link into your applications to get a smaller, faster, easier to manage products.

Written in c and c++

Security:-

• It means a privilege and password system is used that is very flexible and secure.
• Password is secure because all password traffic is encrypted when you connect to server.

Scalability:-

It can handle large database server with database that contain 50 million records.

Connectivity:-

The connector/ODBC interface provides MySQL supports for client program that use ODBC (open
database connectivity).

(1)
Localization:-

The server can provide error massages to the client in many languages.
• MySQL commands line client:-
• The command line client MySQL client is just one of the many tools that can be used to
manipulate MySQL database.
• One can issue SQL queries to database using the command line MySQL client.
• Which is used during the installation of MySQL when you set user/host grant permission in
the “MySQL” database.

MySQL query browser:-

• It is graphical tool provide by MySQL –AB for creating and optimizing in a graphical
environment.
• It is design for help your query analysis data store within your MySQL database .
• The MySQL migration tool kit required java run time.

Connection Dialog :-

• Once MySQL query browser has been started it displays a connection dialog window.
• You need to specify the MySQl server you are connecting in the credential needed for
authorization on that server which machine host that server and database you will be
querying.
• If the server connection is successfully established all the value are saved for future
connection.
• The password field is however always empty for security regions, the password is not sore
along with other options.

The main query language:-

• All of the applications functionality is available is available this window.

• The query browser is divided into several section.

Query tool bar :- The query tool bas is where you create and execute your queries.

Three navigation buttons :- Go back, Next, Refresh.

Two action buttons :- Execute, Stop.

Advanced tool bar :-

Three set of buttons

(2)
 1. The transaction button :- Start, commit and Roll back.

2. Query management button :

Explain, compare.

3. Query building buttons :-

Select, From, where & so on.

Keys :-

F11 – to maximize query area.

Ctrl+O – open query.

Ctrl+S – save query.

Ctrl+N – clear the query.

Ctrl+Alt+Enter – Split the tab and execute

To split the result area right click on it and choose either split tag vertically or horizontally .
OUTPUT:-

(3)
 PRACTICAL-2

AIM:- EXECUTE THE DATA DEFINITION LANGUAGE (DDL)

COMMANDS AND CREATING THE TABLE USING RDBMS.

Data definition language:-

• DDL statement are use to create , delete or change the object to database .
• Typically a database administrator is responsible for DDL statement.

The commands used are

Create- It is use to create a table.

Syntax- create table<table name>

Table Creation:-

• Reserved words cannot be used.

• Underscore, numerals, letters are allowed but no blank space.
• Maximum length of table name is 30 characters.
• Two different tables should not have same name.
• We should specify a unique column name.
• We should specify proper data type along with width.
• We can include not null condition when needed by default it is null.

Procedure:-

• Start>program files>MySQL>MySQL query browser.

• Create database if not exists student record.
• Use student record.
• Execute the above commands.
• Next run the following commands.
• Create table if not exists student.
• (
• Name varchar(30),
• Roll_no varchar(20), • Course varchar(10),
• Stream varchar(10));

(
Refresh the table in the schemata window to expand it.

Next step is to populate the table with some data.

Syntax:- insert into student values

Insert into student values (“Kanchan”,”H220495”,”Btech”,”CSE”)

OUTPUT:-

(
 PRACTICAL-3
AIM:- INSERTING VALUES IN TABLE.
Data Manipulation:-

It is that which is used to retrieve and manipulate data in relation database. This involves inserting
data into database table, retrieving and modifying existing data and delete data from existing table.

INSERTING:-

If you are adding values for all the columns of the table , you do not need to specify the columns
names in the SQL query. However , make sure the order of the values is in the same order as the
columns in the table.
This command adds one or more records to a database table.
Syntax - Insert into [table name] values (“,”);

OUTPUT:-

(
 PRACTICAL-4

AIM:- SELECT AND UPDATE QUERY.

The SQL UPDATE query is used to modify the existing records in a table. You can use the WHERE
clause with the UPDATE query to update the select rows, otherwise all the rows would be affected.

The basic syntax of the UPDATE query with a WHERE clause is as follows –

Syntax:- mysql>select * from table name;

• To show a column in mysql, we use select query.

• Syntax :- mysql>select column1,column2.............column n from table name;
• To change a column or row in a table in mysql, we use update query.
• Syntax :- mysql>update table name set column1=value1.......column n= value n where
(condition);

OUTPUT:-

(
 PRACTICAL-5

AIM:- DELETE QUERY AND DROP COMMANDS.

Delete query is used for deleting a column or row in mysql.

The WHERE clause in the sql delete command is optional and it identifies the rows in the column
that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be
careful while writing a DELETE query without WHERE clause.

Syntax- mysql>delete from table name where [condition];

Drop command is used to delete a table or database permanently.

The SQL DROP command is used to remove an object from the database. If you drop table, all the
rows in the table is deleted and the table structure is removed from the database. A table is dropped
we cannot get it back, so be careful while using DROP command. When a table is dropped all the
references to the table will not be valid. If a table is dropped, all the relationships with other tables
will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the
table will also be dropped, if you want use the table again it has to be recreated with the integrity
constraints, access privileges and the relationships with other tables should be established again.

Syntax- drop table table name;

OUTPUT:-

(
 PRACTICAL-6

AIM:- JOINING TWO TABLES USING INNER JOIN.

The INNER JOIN selects all rows from both participating tables as long as there is a match between
the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables
.The INNER JOIN selects all rows from both participating tables as long as there is a match between
the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.
Inner join use a comparison operator to match rows from two tables based on the values in common
columns from each table.

Join command is used to join two tables.

Syntax:-

Select * from table 1 inner join table 2 using [column name];

OUTPUT:-

(
 PRACTICAL-7

AIM:- TO JOINS TWO TABLES USING LEFT JOIN.

The SQL LEFT JOIN (specified with the keywords LEFT JOIN and ON) joins two tables and fetches all
matching rows of two tables for which the SQL expression is true, plus rows from the first table that
do not match any rows in the second table.

Left join is used to join two table from left side .

Syntax:-

Mysql>select * from table 1 left join table 2 using [column name];

OUTPUT:-

(1
 PRACTICAL-8

AIM:- TO JOINS TWO TABLES USING RIGHT JOIN.

The SQL RIGHT JOIN , joins two tables and fetches rows based on a condition , which is matching in
both the tables (before and after the JOIN clause mentioned in the syntax below), and the
unmatched rows will also be available from the table written after the JOIN clause.

Right join is used to join two tables from right side.

Syntax:-mysql> select * from table1 right join table2 using [column name];

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all
( matched and unmatched) rows from the tables on both sides of the join clause.

Full join is used to join two tables from both sides.

Syntax:- mysql > select * from table1 fill join talble2 using[column name];

OUTPUT:-

(1
 PRACTICAL-9
AIM:- USE OF WHERE , ORDER BY ,LIMIT CLAUSE.
Where clause:-

We can use a conditional clause called the where clause to filter out the results using this clause .We
can specify a selection criteria to select the required records from the table.

Syntax:- Select field1,field2------field n from table name WHERE[condition];

Order By clause:-

The mysql order by clause is used to sort the records in your result set.

Syntax:- Select expression from table name where (condition) order by expression;

Limit clause:-

The limit clause is used in select query to set the number of rows will be displayed in result set.

Syntax:-Select column 1, column 2------------column n from table name limit count;

OUTPUT:-

(1
 PRACTICAL-10

AIM:- WRITE MySQL QUERIES FOR NUMERIC FUNCTION DATA

TIME FUNCTION AND SPECIAL FUNCTION.

1. NUMERIC FUNCTION:-

Following are the numeric function supported by mysql:

ABSO], AVGO], min[], max[], sum[]]

//ABSO:-

It returns the absolute value of a number.

Syntax:- ABS[number];

//AVG[]:-

It returns the average value of an expression.

Syntax:- AVG[expression];

OUTPUT:-

(13)
// Min[]:-

It returns the minimum value of a column.

Syntax:- select Min [column name] from table;

//Max[]:-

It returns the maximum value of a column.

Syntax:- select Max[column name] from table;

OUTPUT:-

// Sum:-

It return the sum of values.

Syntax:- Sum[expression] from table where [condition];

(14)
OUTPUT:

1. DATA TIME FUNCTIONS:-

// Add data[]:-

It return a data after a certain time.

Syntax:- Add data [expression];

// Add Time[]:-

It return a time after a certain interval.

Syntax:- Add Time [exp1,exp2,exp3];

//Current Data[]:-

It returns the current data of the system.

Syntax:- Select current data[];

// Current Time[]:-

It return the current time of the system.

Syntax:- Select current time[];

(1
OUTPUT:

2. SPECIAL FUNCTION:-

// Bin[]:-

It converts a decimal number to a binary number.

Syntax:- Bin[Number];

Example:- Select Bin[399];

// Convert:-

It converts a value from one datatype to another datatype.

Syntax:-[value, datatype];

Example:- Select convert[399,char];

//user[]:-

It return the user name and host name for the current Mysql user.

Syntax:- User[];

Example:- Select user[];

(1
OUTPUT:

//Version:-

It returns the current version of Mysql database.

Syntax:- Version[];

Example:- select version[];

OUTPUT:-

(1