PRACTICAL FILE

OF
BBA - 307
[ INFORMATION SYSTEM MANAGEMENT ]
BACHELOR OF BUSINESS ADMINISTRATION
Academic Session – : 2023 – 24

Submitted by – Submitted to :
GURPREET Ms. Bharti Aggarwal
12921301721 Assistant Professor
TABLE OF CONTENTS
Sr.No List Of Programs Page No Faculty’s Sign

1. DDL commands

2. DATATYPES IN SQL

3. CREATE TABLE
EMPTIAS(EMP_ID,EMP_NAME,EMP_DEPT,DESIGNATIO
N,EMP_CONTACT,SALARY)

4. DROP COMMAND

5. ALTER COMMAND

6. TRUNCATE COMMAND

7. COMMENT COMMAND

8. RENAME COMMAND

9. DML COMMANDS

10. SELECT COMMAND

11. ALTEAST 15 RECORDS ENTERED IN THE EMPTIAS TABLE

USING INSERT COMMAND

12. UPDATE COMMAND

13. DELETE COMMAND

14. DCL COMMANDS

15. GRANT COMMAND

16. REVOKE COMMAND

17. TCL COMMANDS

18. COMMITCOMMAND

19. ROLLBACK COMMAND

20. SAVEPOINT COMMAND

21. DATA CONSTRAINTS

22. PRIMARY KEY

23. FOREIGN KEY

24. UNIQUE KEY

25. COMPOSITE KEY

26. CHECK KEY

27. NULL KEY

28. DESC COMMAND

29. HAVING CLOUSE

30. GROUP BY CLOUSE

31. DUAL TABLE

32. FOREIGN KEY

33. ORDER BY CLOUSE

34. AGGREGATE FUNCTIONS

35. SUM() FUNCTION

36. AVG() FUNCTION

37. MIN() FUNCTION

38. MAX() FUNCTION

39. COUNT() FUNCTION

40. E-R MODEL

41. TOOLS OF E-R MODEL

42. DESIGN DATABASE

43. DATABASE DESIGN USING E-R MODEL

DDL COMMANDS
1. CREATE COMMAND

To create a database in the SQL database. To do this, we use CREATE COMMANDS.

2. ALTER COMMAND

ALTER is a DDL command which changes or modifies the existing structure of the
database, and it also changes the schema of database objects.

3. RENAME COMMAND
RENAME is a DDL command which is used to change the name of the database table.

OUTPUT
4. TRUNCATE COMMAND

TRUNCATE is DDL command which deletes or removes all the records from the table. This
command also removes the space allocated for storing the table records.

OUTPUT

5. DROP COMMAND

To delete the database from the SQL database. To do this, you have to use DROP COMMAND.
DATA TYPES IN SQL

An developer must be aware of what type of data will be stored inside each column
while creating a table. The data type guideline for SQL is to understand what type
of data is expected inside each column and it also identifies how SQL will interact
with the stored data.
For every database, data types are primarily classified into three categories.
o Numeric Datatypes
o Date and Time Database
o String Database

Numeric Data Types in MYSQL

Exact Numeric Datatype
There are nine subtypes which are given below in the table. The table
contains the range of data in a particular type.
Approximate Numeric Datatype
The subtypes of this datatype are given in the table with the range.
String Data Types in MYSQL
Character String Datatype
The subtypes are given in below table –

Unicode Character String Datatype

The details are given in below table –

Server String Data Type in SQL

There are four subtypes of this datatype which are given below:
Server Date and Time Data Type in SQL
The details are given in the below table.

Other Data Types

XML Datatype
XML data type allows storage of XML documents and fragments in a SQL Server
database
Array Datatype
SQL Server does not have a built-in array datatype. However, it is possible to simulate
arrays using tables or XML data types.
This text discusses the properties of data types in MYSQL.
SQL data types define the type of data that can be stored in a database column or variable.
Here are the most common SQL data types:
CREATE TABLE EMPTIAS

DROP COMMAND

ALTER COMMAND

OUTPUT

TRUNCATE COMMAND

OUTPUT
 RENAME COMMAND

OUTPUT

DML COMMANDS

DML is an abbreviation of Data Manipulation Language.

The DML commands in Structured Query Language change the data present in the SQL database.
We can easily access, store, modify, update and delete the existing records from the database using
DML commands.

Following are the four main DML commands in SQL:

1. SELECT Command
2. INSERT Command
3. UPDATE Command
4. DELETE Command

SELECT DML 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.

OUTPUT

INSERT DML Command

INSERT is another most important data manipulation command in Structured Query Language,
which allows users to insert data in database tables.
OUTPUT

UPDATE DML Command

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.
OUTPUT

DELETE DML Command

DELETE is a DML command which allows SQL users to remove single or multiple existing
records from the database tables.

This command of Data Manipulation Language does not delete the stored data permanently from
the database. We use the WHERE clause with the DELETE command to select specific rows from
the table.

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.

This example shows all the values of every column from the table.

This example shows all the values of a specific column from the table.
This example describes how to use the WHERE clause with the SELECT DML command.

DCL COMMANDS

DCL Commands include commands such as GRANT and REVOKE which mainly deal
with the rights , permissions , and other controls of the database system

GRANT COMMAND

REVOKE COMMAND
TCL COMMANDS

Transactions group a set of tasks into a single execution unit. Each transaction begins with a
specific task and ends when all the tasks in the group are successfully completed. If any of the
tasks fail, the transaction fails. Therefore, a transaction has only two results: success or failure.

COMMIT COMMAND

ROLLBACK COMMAND
SAVEPOINT COMMAND

DATA CONSTRAINTS

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the table. If there is any violation between the constraint and the data
action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and
table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row
in a table

FOREIGN KEY - Prevents actions that would destroy links between tables

CHECK - Ensures that the values in a column satisfies a specific condition

DEFAULT - Sets a default value for a column if no value is specified

CREATE INDEX - Used to create and retrieve data from the database very quickly

PRIMARY KEY
FOREIGN KEY

UNIQUE KEY

OUTPUT
CHECK KEY

GROUP BY CLAUSE

ORDER BY CLAUSE
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()

Aggregate functions are a vital component of database management systems. They allow us to
perform calculations on large data sets quickly and efficiently. For example, these functions
generate statistical reports, perform financial analysis, and manage inventory levels.

In addition, we can better understand the data we are working with by using aggregate functions.

For example, we can easily calculate the average price of all products in our inventory or find the
total sales for a particular time. Without aggregate functions, we would need to manually sort
through each data point, which would be time-consuming and error-prone.

Overall, aggregate functions are essential for anyone working with large amounts of data and
seeking to gain valuable insights from it.

MAX
MIN

COUNT

SUM

AVERAGE
E-R MODEL

The Entity Relational Model is a model for identifying entities to be represented in the database
and representation of how those entities are related. The ER data model specifies enterprise
schema that represents the overall logical structure of a database graphically.

The Entity Relationship Diagram explains the relationship among the entities present in the
database. ER models are used to model real-world objects like a person, a car, or a company and
the relation between these real-world objects. In short, the ER Diagram is the structural format of
the database.

ER diagrams are used to represent the E-R model in a database, which makes them easy to be
converted into relations (tables).

ER diagrams provide the purpose of real-world modeling of objects which makes them intently
useful.

ER diagrams require no technical knowledge and no hardware support.

These diagrams are very easy to understand and easy to create even for a naive user.

It gives a standard solution for visualizing the data logically.

ER Model consists of Entities, Attributes, and Relationships among Entities in a Database

System.

Entity

An Entity may be an object with a physical existence – a particular person, car, house, or
employee – or it may be an object with a conceptual existence – a company, a job, or a university
course.

Attributes

Attributes are the properties that define the entity type. For example, Roll_No, Name, DOB,
Age, Address, and Mobile_No are the attributes that define entity type Student. In ER diagram,
the attribute is represented by an oval.

Relationship

Relationship Type represents the association between entity types. For example, ‘Enrolled in’ is
a relationship type that exists between entity type Student and Course. In ER diagram, the
relationship type is represented by a diamond and connecting the entities with lines
DATABASE DESIGN USING E-R MODEL

SAMPLE SYNTAX