Practical:1

Create multiples tables to design a database in mysql.

Create Table:

The CREATE TABLE statement is used to create a new table in a database.

Syntax:

CREATE TABLE table_name (

column1 datatype, column2
datatype, column3
datatype,
....
);

Output:
 Practical:2

Insert data into tables using queries.

Insert into:

The INSERT INTO statement is used to insert new records in a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...) VALUES

(value1, value2, value3, ...);

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

Output:
 Practical:3

Update table in mysql.

Update clause:

The UPDATE statement is used to modify the existing records in a table .

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Output:
 Practical:4

Apply delete and truncate query on table.

Delete command:

The DELETE statement is used to delete existing records in a table.

Syntax:

DELETE FROM table_name WHERE condition; Output:

Truncate table:

The truncate table command is used to empty a table.

Syntax:

Truncate table table_name;

 Practical:5

Alter schema using mysql.

Alter table:

The ALTER TABLE statement is used to add, delete, or modify columns in an

existing table.

The ALTER TABLE statement is also used to add and drop various constraints on
an existing table.

Syntax:

ALTER TABLE table_name

ADD column_name datatype;

ALTER TABLE table_name DROP

COLUMN column_name;
Output:
 Practical:6

Display records using different form of select statement.

Select statement:

The SELECT statement is used to select or fetch data from a database.

Syntax:

SELECT column1, column2, ...

FROM table_name; Output:

Select distinct:

The SELECT DISTINCT statement is used to return only distinct (different)

values.

Syntax:
SELECT DISTINCT column1, column2, ...
FROM table_name; Output:
 Practical:7

Apply aggregate functions on tables

Aggregate functions:
An aggregate function in SQL returns one value after calculating multiple
values of a column. We often use aggregate functions with the GROUP BY
and HAVING clauses of the SELECT statement.

There are 5 types of SQL aggregate functions:

• Count()

• Sum()

• Avg()

• Min()

• Max() Count:

COUNT counts how many rows are in a particular column.

Syntax:

SELECT COUNT (column name) FROM table_name;

Output:

Sum:

SUM adds together all the values in a particular column.

Syntax:
SELECT SUM (column name) FROM table_name;
Output:

Avg:

AVG calculates the average of a group of selected values.

Syntax:

SELECT AVG(column name) FROM table_name;

Output:

Max & Min:

MIN and MAX return the lowest and highest values in a particular column,
respectively.

Syntax:

• SELECT MAX(column name) FROM table_name;

• SELECT MIN(column name) FROM table_name;
Output:
 Practical:8

Implement various constraints on database tables.

Constraints:

SQL Constraints are the rules applied to a data columns or the complete table
to limit the type of data that can go into a table.

NOT NULL Constraint:

When applied to a column, NOT NULL constraint ensure that a column cannot
have a NULL value.

Syntax:

CREATE TABLE table_name(

column1 datatype not null,
column2 datatype,

.....

.....

columnN datatype

);

Output:
UNIQUE Key Constraint

When applied to a column, UNIQUE Key constraint ensure that a column

accepts only UNIQUE values.
Syntax:

CREATE TABLE table_name(

column1 datatype Unique ,
column2 datatype,

.....

.....

columnN datatype

);

Output:

PRIMARY Key Constraint

When applied to a column, PRIMARY Key constraint ensure that a column

accepts only UNIQUE value and there can be a single PRIMARY Key on a table
but multiple columns can constituet a PRIMARY Key.

Syntax:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,

.....
columnN datatype,

PRIMARY KEY(column_name)

);

Output:

FOREIGN Key Constraint

FOREIGN Key constraint maps with a column in another table and uniquely
identifies a row/record in that table.

Syntax:

CREATE TABLE table_name (

column1 datatype,
column2 datatype,

...
CONSTRAINT fk_name

FOREIGN KEY (column_name)

REFERENCES referenced_table(referenced_column)

);

Output:
 Practical:9

Create views using queries in mysql.

Views:

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.

CREATE VIEW :

To create a view in a database, you can use the SQL CREATE VIEW statement.

Syntax:

CREATE VIEW view_name AS SELECT

column1, column2....

FROM table_name WHERE

[condition]; Output:

Update View:

The SQL UPDATE VIEW command can be used to modify the data of a view.

Syntax:

UPDATE view_name
SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];
Output:

Renaming a View :

The RENAME TABLE statement in MySQL database is used to rename views.

Syntax:

RENAME TABLE old_view_name To new_view_name;

Output:

Drop view:

The SQL DROP VIEW statement is used to delete an existing view, along with
its definition and other information.

Syntax:
DROP VIEW view_name;
Output:
 Practical: 10

Apply group operations on the table.

Group by:

The GROUP BY statement groups rows that have the same values into
summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions

(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more
columns.

Syntax:

SELECT column_name(s)

FROM table_name

GROUP BY column_name(s);

Output:
 Practical:11

Sort data in tables using queries.

Order by:

The ORDER BY clause is used to sort the data in either ascending or

descending order, based on one or more columns. This clause can sort data by
a single column or by multiple columns.

Syntax:

SELECT column-list

FROM table_name

[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Output:
 Practical:12

Implement various string functions on table.

String functions:

String functions are used primarily for string manipulation.

ASCII()

Returns numeric value of left-most character.

Syntax:

SELECT ASCII(String); Output:

CHARACTER_LENGTH :

This string function returns the length of the given string. It shows the number
of all characters and spaces from the sentence.

Syntax:

SELECT CHARACTER_LENGTH(String);

Output:
CONCAT :

This string function concatenates two strings or words and forms a new string
in the result.

Syntax:

SELECT CONCAT(String_1, String_2, String_3, ...., String_N);

Output:

LCASE :

This string function allows users to convert the specified string into lower case
letters.

Syntax:

SELECT LCASE(String);
Output:

UCASE :

This string function allows users to convert the specified string into upper case
letters.

Syntax:
SELECT UCASE(String);
Output:
 Practical:13

Apply different types of joins operations on tables.

Joins:

A JOIN clause is used to combine rows from two or more tables, based on a
related column between them.

Types of Joins:

Inner join:

Returns records that have matching values in both tables Syntax:

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

Output:

Left Join :

The Left join keyword returns all records from the left table (table1), and the
matching records (if any) from the right table (table2).

Syntax:
SELECT column_name(s)

FROM table1
LEFT JOIN table2

ON table1.column_name = table2.column_name;

Output:

Right Join:

The Right Join keyword returns all records from the right table (table2), and
the matching records (if any) from the left table (table1).

Syntax:

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

Output:
Cross join:

The CROSS JOIN keyword returns all records from both tables (table1 and
table2).

Syntax

SELECT column_name(s)

FROM table1 CROSS

JOIN table2;
Output:
 Practical: 14

Apply where clause on the tables.

Where clause:

The Where clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Output:

AND Operator:

The WHERE clause can contain one or many AND operators.

The AND operator is used to filter records based on more than one conditions.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition1 AND condition2 AND condition3 ...;

Output:

OR Operator:

The WHERE clause can contain one or more OR operators.

The OR operator is used to filter records based on more than one conditions.

Syntax

SELECT column1, column2, ...

FROM table_name

WHERE condition1 OR condition2 OR condition3 ...;

Output:
 Practical:15

Apply like clause on tables.

Like clause:

The SQL LIKE operator is used to retrieve the data in a column of a table, based
on a specified pattern.

It is used along with the WHERE clause of the UPDATE, DELETE and SELECT
statements, to filter the rows based on the given pattern. These patterns are
specified using Wildcards.

• %: The percent sign represents zero, one or multiple characters.

• _: The underscore represents a single number or characters.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE columnn LIKE specified_pattern;

Output:
 Practical:16

Import and export data in MySQL.

In MySQL ,we can export and import data in MySQL Workbench.

MySQL Export Database

MySQL Workbench

Here, we are going to see database exporting, including tables using MySQL
Workbench. So open the workbench and type the password for the username.

Step 1: Go to the Menu bar and click on the Server. A popup screen appears,
then select the Data Export option, as shown in this screen. It will open a new
window of data export settings and options.

Step 2: Select any database that you want. It will also display corresponding
tables in the left section of the window. Here, we are going to select the
mytestdb database. We can also choose multiple database checkboxes to
include the database in the Export file. Similarly, we can select multiple tables.
Step 3: After selecting the database, including all tables, go to the drop-down
setting, and select any of the available options.

 Dump Data and Structure: It will save both table structure and data rows.
 Dump Data Only: It will save only the inserted rows in the tables.
 Dump Structure Only: It will save only the table structure, which are database
columns and data types defined by us.

Step 4: In the Export option, we can see two radio buttons that are explained
below.

 Export to Dump Project Folder: It will save all the tables as separate SQL
files under one folder. It will be useful when you import or restore the
export file one by one table.
 Export to Self-Contained File: It will store all the databases and tables in
a single SQL file. It is a good option when you want to import all the
databases, tables, and data rows using a single SQL file.
We can select the export path of our choice.

Step 5: Click the Start Export button, which displays the progress bar and log.
Now, we can verify the export files in the Document folder in our system.
 MySQL Import Database
MySQL Workbench
Here, we are going to see database importing using MySQL Workbench. So
open the workbench and enter the password for the username.

Step 1: Navigate to the Menu bar and click on the Server. A popup screen
appears, then select the Data Import option, as shown in this screen. It will
open a new window of data import settings and options.
Step 2: In the screen, we can see the two radio options to import databases
and tables, which are given below:

[Link] from Dump Project Folder.

[Link] by using Self-Contained File.

Step 3: Here, we will select ‘Import from Dump Project Folder’ and choose the
desired database for importing from the Data Import option.
Step 4: Choose the 'Dump Structure and Data' option and click the Start Import
button to import the databases and tables from the backup file. The following
screen explains all the steps clearly:

Step 5: Now, navigate to the Schema under the navigator option on the left
side of the workbench window and refresh it to see the currently imported
database or table.
 Practical:17
Generate report in MySQL.

A database report is the formatted result of database queries and contains

useful data for decision-making and analysis.

Report generating by dbForge Studio

dbForge Studio for MySQL is a universal IDE as it includes a set of tools

required to develop, administer, and manage a MySQL databases efficiently. It
offers integrated tools for data analytics and visualization.

Steps for generating report:

[Link] DB forge.

[Link] data analysis column.

[Link] design new report.

Dialogue box Wizad appaer-data report wizard

Connection establishment
Select database then click on next
Choose table from the database
Add coloums from the table into the report
Select the styles in which you want to style the report
Title your report and rename the report
Click on Finish

4. A dialogue box appears “Data Report Wizard” then choose report type –
Standard, click on next.
5. Establish connection with your database or local host.

6. Choose the database, then click on next

7. Choose the table from the database, then click on next.

[Link] columns from the table to the report, then click on next.

[Link] the desired styles for formatting the report, then click on next.
[Link] your report and rename it then click on finish.

To proceed with report creation, open the Toolbox window by clicking

the icon on the Data Report toolbar

1. In the Toolbox window, select the Label control and drop it onto the
report Detail Band.
2. Double-click the created label to invoke its in-place editor, which allows
you to input text. Then, use the Data Report toolbar to adjust the label
color and font options:
3. Switch to the Preview tab.