Experiment:1

Introduction of MySQL
MySQL is a fast, easy-to-use RDBMS being used for many small and big
businesses. MySQL is developed, marketed and supported by MySQL AB,
which is a Swedish company. MySQL is becoming so popular because of many
good reasons –
 MySQL is released under an open-source license. So you have nothing to
pay to use it.
 MySQL is a very powerful program in its own right. It handles a large
subset of the functionality of the most expensive and powerful database
packages.
 MySQL uses a standard form of the well-known SQL data language.
 MySQL works on many operating systems and with many languages
including PHP, PERL, C, C++, JAVA, etc.
 MySQL works very quickly and works well even with large data sets.

RDBMS Terminology
o Database − A database is a collection of tables, with related data.
o Table − A table is a matrix with data. A table in a database looks like a simple
spreadsheet.
o Column − One column (data element) contains data of one and the same kind,
for example the column postcode.
o Row − A row (= tuple, entry or record) is a group of related data, for example
the data of one subscription.
o Redundancy − Storing data twice, redundantly to make the system faster.
o Primary Key − A primary key is unique. A key value cannot occur twice in
one table. With a key, you can only find one row.
o Foreign Key − A foreign key is the linking pin between two tables.
o Compound Key − A compound key (composite key) is a key that consists of
multiple columns, because one column is not sufficiently unique.
o Index − An index in a database resembles an index at the back of a book.
o Referential Integrity − Referential Integrity makes sure that a foreign key
value always points to an existing row.

Experiment:2
 Introduction of Project
An event management system project in PHP is an open-source project which
was created to help organize events with the use of PHP programming language
without any additional tools required for its implementation.
Event Management Software has been widely used in many industries
including the entertainment industry like music festivals as well as the corporate
world for managing large events, and it is applied in the field of education for
educational events. The management software designed for event administration
mainly contains modules like an event database, a resource planner and
schedule, human resources management, advertising management, registration
system, ticketing system, and so on.

Technologies used:
Frontend: Bootstrap, html, css.
Backend: Express, Node.js
Database: MySQL
1.Number of Modules:
There are basically three modules in this project. All these three modules has different
functionality as per the requirements.

1. User interface module – This module contains all the html pages related
to the users need for example registration, login, items etc..
2. Admin interface module - This module contains all the html pages
related to the employee/admin need for example registration, login etc..
3. Database management of user and admin module – This module
revolves around data in the form of rows and column. It contains many
different – 2 tables like events, event_type etc..

2.Number of Tables:

Multiples tables are used for this project such as events, event_type, participants
etc..

Events:
Event_type:

Participants:

Experiment:3
 DDL and DML command
DDL COMMAND: -
Data Definition Language helps you to define the database structure or schema. DDL
commands help you to create the structure of the database and the other database
objects. Its commands are auto-committed so, the changes are saved in the database
permanently. The full form of DDL is Data Definition Language.

Commands: -

1. CREATE Command -
CREATE statements is used to define the database structure schema:
Syntax
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Ex. ->
2.INSERT Command ->
This is a statement that is a SQL query. This command is used to insert data into
the row of a
table.

Syntax
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);

3.ALTER Command-
Alters command allows you to alter the structure of the database.

Syntax
ALTER TABLE MODIFY(COLUMN DEFINITION....);
 alter table Doctor
-> add column address varchar(50) after participents;

4.UPDATE Command-
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]
Ex.

5.DELETE Command-
This command is used to remove one or more rows from a table.

DELETE FROM table_name [WHERE condition];

Ex.
DML COMMAND: -
DML commands it to allow you to manage the data stored in the database, although
DML commands are not auto-committed. Moreover, they are not permanent. So, It is
possible to roll back the operation. The full form of DML is Data Manipulation
Language.

Commands: -
1. SELECT Command ->
SELECT is the most important data manipulation command in Structured Query
Language. The SELECT command shows the records of the specified table. It also
shows the particular record of a particular column by using the WHERE clause.
Syntax
SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM Name_o
f_table;  
SELECT * FROM table_name;  

2. INSERT Command ->

INSERT is another most important data manipulation command in Structured Query

Language, which allows users to insert data in database tables.

Syntax
INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , column_Name3 
, .... column_NameN )  VALUES (value_1, value_2, value_3, .... value_N ) ; 

3.UPDATE Command-
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]

4.DELETE Command-
This command is used to remove one or more rows from a table.

DELETE FROM table_name [WHERE condition];

 Experiment:4
Retrieval Command
SELECT Command: -
SELECT is the most important data manipulation command in Structured
Query Language. The SELECT command shows the records of the specified
table. It also shows the particular record of a particular column by using the
WHERE clause.
1. Without using where clause

Syntax
SELECT column1, column2, ...
FROM table_name;
SELECT * FROM table_name;

2. Using Where Clause –

3.Logical Operation -
AND Syntax-
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax-
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax-
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
 IN Syntax-
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

BETWEEN Syntax->
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

ORDER BY Syntax->
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... 
HAVING Syntax->
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
 Experiment:5
JOIN Operation: inner join ,left join ,right join, cross join ,natural join

JOIN FUNCTION -> MySQL last function is used to return the last value of the selected
column.
INNER JOIN-> The INNER JOIN selects all rows from both participating tables as long as
there is a match between the columns
LEFT JOIN -> he LEFT JOIN allows you to query data from two or more tables. Similar to
the INNER JOIN clause, the LEFT JOIN is an optional clause of the SELECT statement,
which appears immediately after the FROM clause. Suppose that you want to join two tables
t1 and t2
RIGHT JOIN -> The Right Join is used to joins two or more tables and returns all rows
from the right-hand table, and only those results from the other table that fulfilled the join
condition. If it finds unmatched records from the left side table, it returns Null value.
NATURAL JOIN -> A NATURAL JOIN is a JOIN operation that creates an implicit join
clause for you based on the common columns in the two tables being joined. Common
columns are columns that have the same name in both tables. A NATURAL JOIN can be an
INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
CROSS JOIN -> MySQL CROSS JOIN is used to combine all possibilities of the two or
more tables and returns the result that contains every row from all contributing tables. The
CROSS JOIN is also known as CARTESIAN JOIN, which provides the Cartesian product of
all associated tables.
 Experiment:6
User creation command –

MySQL server provides multiple types of privileges to a new user account.

Some of the most commonly used privileges are given below:

1. ALL PRIVILEGES: It permits all privileges to a new user account.

To GRANT ALL privileges to a user , allowing that user full control over
a specific database , use the following syntax: mysql> GRANT ALL
PRIVILEGES ON database_name.* TO 'username'@'localhost';

2. CREATE: It enables the user account to create databases and tables.

CREATE statements is used to define the database structure schema:

3.DROP: It enables the user account to drop databases and tables.

The DROP statement destroys the objects like an existing database, table,
index, or viewA DROP statement in SQL removes a component from a
relational database management system (RDBMS).

Syntax:DROP object object_name

Examples:

DROP TABLE table_name;

4.DELETE: It enables the user account to delete rows from a specific table.

DELETE is a DML command which allows SQL users to remove single or

multiple existing records from the database tables.

5.INSERT: It enables the user account to insert rows into a specific table.

INSERT is another most important data manipulation command in Structured

Query Language, which allows users to insert data in database tables.

6. SELECT: It enables the user account to read a database.

7. UPDATE: UPDATE is another most important data manipulation command in

Structured Query Language, which allows users to update or modify the existing data
in database tables.
 Experiment:7
Max , min , count , avg , sum , last , upper ,etc

COUNT FUNCTION - The COUNT function returns the total number of

values in the specified field. It works on both numeric and non-numeric data
types.

MIN FUNCTION - The COUNT function returns the total number of values in
the specified field. It works on both numeric and non-numeric data types.

MAX FUNCTION - The MAX function is the opposite of the MIN function. It
returns the largest value from the specified table field.

SUM FUNCTION - MySQL SUM function which returns the sum of all the
values in the specified column. SUM works on numeric fields only. Null values
are excluded from the result returned.
AVG FUNCTION - AVG function returns the average of the values in a
specified column. Just like the SUM function, it works only on numeric data
types.

UPPER FUNCTION - The UPPER() function to convert any lowercase

characters to uppercase.

LOWER FUNCTION - MySQL last function is used to return the last value of
the selected column.
 Experiment:8
Transaction Command
Transactional control commands are only used with the DML Commands such
as - INSERT, UPDATE and DELETE only. They cannot be used while creating
tables or dropping them because these operations are automatically committed
in the database
Commit:
The COMMIT command is the transactional command used to save changes
invoked by a transaction to the database.
The COMMIT command is the transactional command used to save changes
invoked by a transaction to the database. The COMMIT command saves all the
transactions to the database since the last COMMIT or ROLLBACK command.
The syntax for the COMMIT command is as follows.

ROLLBACK:
The ROLLBACK command is the transactional command used to undo
transactions that have not already been saved to the database. This command
can only be used to undo transactions since the last COMMIT or ROLLBACK
command was issued.
The syntax for a ROLLBACK command is as follows –
ROLLBACK;
SAVEPOINT:
A SAVEPOINT is a point in a transaction when you can roll the transaction
back to a certain point without rolling back the entire transaction.
The syntax for a SAVEPOINT command is as shown below
SAVEPOINT SAVEPOINT_NAME;
Constraint Command
PRIMARY KEY on CREATE TABLE->

The PRIMARY KEY constraint uniquely identifies each record in a

table.Primary keys must contain UNIQUE values, and cannot contain NULL
values.

FOREIGN KEY on CREATE TABLE ->

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.The table with the foreign key is called the
child table, and the table with the primary key is called the referenced or parent
table

UNIQUE Constraint on CREATE TABLE->

A unique key is a set of one or more than one fields/columns of a table that
uniquely identify a record in a database table
NOT NULL on CREATE TABLE->
The NOT NULL constraint enforces a column to NOT accept NULL values.
 Experiment:9
View Implementation
VIEW - A database view is a subset of a database and is based on a query that
runs on one or more database tables.
 Experiment:10

Control Flow

CONTROL FLOW- An operation that controls the recording or processing or

transmission of interpretation of data; "a control operation started the data
processing.