A

LAB REPORT
ON
MY SQL EXERCISE

PREPARED BY:
Jay Prakash Shah
Symbol no.35111
(BBA, 2ND SEMESTER)
Section ‘c’
Patan Multiple campus
Patandhoka, Lalitpur

SUBMITTED TO:
Mr. Binit Shrestha
Subject Faculty Member
Patan Multiple Campus
Patandhoka, Lalitpur

In partial fulfillment of the requirement of the course Database Management

System studied in BBA Second semester, Tribhuwan University

Sept 2023

1
Table of Contents
CHAPTER 1: SQL BRIEF DESCRIPTION...........................................................................................................4
1.1. Introduction to SQL...........................................................................................................................4
1.2. Introductory Commands of SQL........................................................................................................4
a) Show database.............................................................................................................................4
b) Use database...............................................................................................................................5
c) Drop database.............................................................................................................................6
d) Show tables.................................................................................................................................7
e) Creating a new database.............................................................................................................7
CHAPTER 2: DDL BASIC CONCEPT................................................................................................................9
CHAPTER 3: DDL COMMAND.....................................................................................................................10
3.1. Create Table....................................................................................................................................10
3.2. Drop Table......................................................................................................................................11
3.3. Alter Table......................................................................................................................................11
3.3.1. Drop column............................................................................................................................12
3.3.2. Adding a column in existing table............................................................................................13
3.3.3. Adding multiple columns in existing table...............................................................................14
3.3.6. Rename table...........................................................................................................................15
3.3.4. Adding a column with default value........................................................................................16
3.3.5. Changing datatype of an existing column................................................................................17
3.4. Truncate Table................................................................................................................................18
3.5. Different Between Truncate and Drop Command..........................................................................19
CHAPTER 4: SQL CONSTRAINTS.................................................................................................................21
4.1. NOT NULL.......................................................................................................................................21
4.2. Unique............................................................................................................................................21
4.3. Default............................................................................................................................................22
4.4. Primary Key.....................................................................................................................................23
4.5. Foreign key.....................................................................................................................................24
4.6. Check Constraints...........................................................................................................................25
CHAPTER 5: DML DEFINITION....................................................................................................................26
CHAPTER 6: DML COMMAND....................................................................................................................27
6.1. Inserting Record..............................................................................................................................27
6.2. Inserting a NULL Value in a Table....................................................................................................27

2
 6.3 Insert a Default Value in the Table..................................................................................................28
6.4 Delete all the record of table...........................................................................................................29
6.5. Delete a Particular Record Using Where Clause.............................................................................30
6.6. Updating Single Column Value in a Record.....................................................................................31
6.7. Updating Multiple Column Value in a Record.................................................................................32
CHAPTER 7: SQL CLAUSES..........................................................................................................................34
7.1. Display all Record of Table..............................................................................................................34
7.2. Displaying Certain Records Based on Condition.............................................................................34
7.3. Displaying Specific Columns from Record.......................................................................................35
CHAPTER 8: SQL SELECT CLAUSES..............................................................................................................37
8.1. Where clause..................................................................................................................................37
8.2. Like clause (pattern matching)........................................................................................................38
8.3. Describe clause...............................................................................................................................38
8.4. Order By clause...............................................................................................................................39
8.5. Group By clause..............................................................................................................................40
8.6. Distinct clause.................................................................................................................................41
8.7. AND operator.................................................................................................................................42
8.8. OR operator....................................................................................................................................43
CHAPTER 9: SQL VIEW...............................................................................................................................45
9.1. Different Between View and Table.................................................................................................45
9.2. Creating a View...............................................................................................................................45
9.3. Deleting a View...............................................................................................................................46

3
 CHAPTER 1: SQL BRIEF DESCRIPTION

1.1. Introduction to SQL

Structured query language (SQL) is a well-established data language. It is used for managing
data in a relational database management system (RDBMS) which stores data and relationship
between data in a form of tables. It is a programming language to operate databases. It includes
data creation, deletion, fetching rows, modifying rows, etc. There are multiple SQL dialects such
as MySQL, oracle, Microsoft SQLserver, PostgreSQL, SQLite, of which MySQL is the most
used by the companies, being also open-source and free. It is use to communicate to a database
in a human readable form. It allows users to query the Database in a number of ways, using
English-like statements.

SQL follows the following rules:

 SQL is not case sensitive. Generally, keywords of SQL are written in uppercase.
 statements of SQL are dependent on text lines. We can use a single SQL statement on one
or multiple text line.
 using the SQL statements, you can perform most of the actions in a database.
 SQL depends on tuple relational calculus and relational algebra.

1.2. Introductory Commands of SQL

a) Show database
This command is used to show the list of databases names on the MySQL server host.

Syntax:
show databases;

4
Example:

In this figure, databases are shown with their name.

b) Use database
This command is used to specify the name of database we want to select. After selecting the
database, we can perform various operations on it such as creating table, inserting data, updating
data and deleting data.

Syntax:
use database name;

Example:

5
In this figure, database is used from the databases.
c) Drop database
This command is used to deletes all user data and log files, as well as any backup and restore
history for the database.

Syntax:
drop database name;

Example:

6
In this figure, from the databases, moon is drop
d) Show tables
This command is used to show tables from the listed database.

Syntax:
show tables;

Example:

7
In this figure, list of table is shown.

e) Creating a new database

This command is used to create database.

Syntax:
create database name;

Example:

8
In this figure, database test is created.

9
 CHAPTER 2: DDL BASIC CONCEPT

DDL stands for “Data Definition Language”. It is a subset of SQL (Structured Query Language)
used to define, manage, and manipulate the structure of a database. DDL command is used to
create, modify, and delete database object like table, index, views and store procedures.
Examples of DDL command includes CREATE, ALTER, and DROP, among others. This
command helps in setting up the database schema and defining the rules that govern the data
stored in the database.

DDL is standardized language with commands to define the storage groups, different structures
and objects in a database. It is also used in a generic sense to refer to any language that describe
data. It also includes statements to alter the objects and impose or drop certain constraints on
table, such as the followings;

 UNIQUE
 PRIMARY
 FOREIGN KEY
 CHECK

These constraints are used to enforce uniqueness, referential or domain integrity.

10
 CHAPTER 3: DDL COMMAND

3.1. Create Table

This command is used to create a new table in a database.

Syntax:
create table table_name (column_name1 and datatype (size of the
column) constraints, column_name2 datatype (size of the column)
constraints, column_namen datatype (size of the column) constraints);

Example:

In the above figure, new table is used.

There is a created command used which create an earth table in a database.

3.2. Drop Table

This command is used to delete or remove a table along with its contained data and rows.

Syntax:
drop table table_name;

11
 Example:

In the above figure, drop table is used which makes the earth data delete from the databases.

3.3. Alter Table

This command is used to modify or delete a specific column from table.

Syntax:
alter table table_name drop columnname

Example:

12
In the above figure, alter table command is used which can modify or delete a specific column
Data of age is deleted from the previous table.

3.3.1. Drop column

This command is used to remove or delete column.

Syntax:
alter table table_name drop column column_name

Example:

13
In this figure, drop column command is used. Empid is dropped from the previous table using
drop column command as shown in above figure.

3.3.2. Adding a column in existing table

It is used to add a new column by using alter table command.

Syntax:
alter table table_name add(column_name data type(length) constraint);

14
Example:

In this given figure, a new column is used. There is alter table command is used which add a
phone column in the previous table and modify the table by adding phone column.

3.3.3. Adding multiple columns in existing table

It is used to add multiple column by using alter table command by adding comma.

Syntax:
alter table table_name add(column_name1
data_type(length)constrain,column_name2 datatype(length)contrain.

15
Example:

In the above figure, multiple column is used. There is alter table command which is used to add
caste, address, and phone2 column by using a comma. You can also modify or delete the specific
column through alter table statement.

3.3.6. Rename table

It is used to rename a table by using alter table statement.

Syntax:
rename table table_name to new name table_name;

16
Example:

In the above the table, rename table is used. From the previous table, employee and student,
employee is renamed as worker as you can see in the above table.

3.3.4. Adding a column with default value

It is used to set default value for a column by using default constraints.

Syntax:
alter table name add(column name datatype(length)default default
value);

17
Example:

In the above figure, default column is used.

For the ‘nationality’, ‘Nepali’ default column is used and for the column ‘post’, ‘supervisor
default column is used.

3.3.5. Changing datatype of an existing column

It is used to change the datatype of a column of an existing table by using the alter
Column option.

18
 Syntax:
alter table table name modify column name datatype(length);

Example:

In the above figure, employee’s name is modify from the previous table as shown in
Table.

3.4. Truncate Table

The Truncate table command deletes the data inside a table, but not the table itself.

Syntax:
truncate table table_name;

Example:
19
 In the above figure, truncate table command is used which delete the data of students from the
previous table.

3.5. Different Between Truncate and Drop Command

1. Truncate:
 The “Truncate” command is used to remove all the rows from a table, effectively deleting
all the data in a table.
 Truncate operation can be faster than DELETE, as it doesn’t log individual row deletions
and doesn’t generate as much undo and redo data.
 Truncate doesn’t release space allocated for the table and it also resets the auto-increment
counters if present.
 Truncate cannot be used on tables with foreign key constraints unless those constraints
are disabled or removed.

2. Drop:

20
  The “drop” command is used to completely remove a table or database from the database
management system.
 The drop command permanently deletes a table and all its data, freeing up the space
allocated for it.
 Once a table is dropped, it cannot be recovered, and all associated objects like indexes,
triggers, and constraints are also removed.
In summary, Truncate removes all rows from a table while keeping the table structure intact,
whereas DROP completely deletes the table along with its data and any associated objects. Both
commands are used for different purposes depending on the need for data retention or complete
Removal.

21
 CHAPTER 4: SQL CONSTRAINTS

4.1. NOT NULL

The NOT NULL constraint is used to ensure that a given column of a table is never assigned the
null value.

Syntax:
create table table_name (column_name data_type not null);

Example:

In the above figure, not null of table shophy is created.

4.2. Unique
Unique constraints is used to ensure that all values in a column are different.

22
 Syntax:
create table table_name(column1 data_type not null primary key,column2
data_type not null unique);

Example:

In the above figure, unique data is used for rollno.

4.3. Default
The default constraint is used to set a default value for a column.

Syntax:

23
 create table table_name (column_name data_type default default_value);

Example:

In this figure, default value Pooja is added.

4.4. Primary Key

The primary key constraint specifies that the constrained columns' values must uniquely identify
each row.

Syntax:
create table table_name(column1 data_type,column2 data_type, column3
data_type, not null,……, primary key(column1,column2,column3));

Example:

24
 In the above figure, primary key is set to std_id and roll_no.
4.5. Foreign key
The foreign key constraint is used to prevent actions that would destroy links between tables.

Syntax:
create table table_name(column1 data_type1,…..,constraint
fk_constraint_name references referenced_table_name(referenced_column)
on update cascade);

Example:

In the above figure, stu_id is foreign key from the previous table which was primary key.

25
4.6. Check Constraints
The check constraint is used to limit the value range that can be placed in a column.

Syntax:
create table table_name(column1 datatype,column2 datatype,
……,constraint constraint_name check(condition));

Example:

In this table, check empage is done by using CHECK constraints.

26
 CHAPTER 5: DML DEFINITION

DML stand for “Data Manipulation Language”. It is a sub set of SQL (Structured Query
Language) used to interact with databases for inserting, updating, deleting, and retrieving data.
Examples of DML content of your database efficiently, keeping the data up to date and relevant.
A popular data manipulation language is Structured Query Language or SQL, used to retrieve
and manipulate data in a relational database. With SQL, you can perform operations on the
database and also use it to create a database. SQL uses specific commands like Create, Drop,
Insert, etc., to carry out the required tasks.

DML is an abbreviation for Data Manipulation Language. Represents a collection of

programming languages explicitly used to make changes to the database, such as:

 CRUD operations to create, read, update and delete data.

 Using INSERT, SELECT, UPDATE, and DELETE commands.
 DML commands are often part of a more extensive database language, for example, SQL
(structured query language). These DMLs can have a specific syntax to handle data in
that language.
DML has two main classifications which are procedural and non-procedural programming,
which is also called declarative programming. The SQL dealing with the manipulation of data
present in the database belongs to the DML or Data Manipulation Language, including most of
the SQL statements.

27
 CHAPTER 6: DML COMMAND

6.1. Inserting Record

Inserting a record in a database means adding a new row or entry to a table within the database.
This operation is performed using the DML command called “INSERT”.

Syntax:
insert into table_name value(value1,value2,value3,…,);

Example:

In the above figure, data of stu_id, stu_fname, stu_lname, gender, phone, stu_dob is inserted.

6.2. Inserting a NULL Value in a Table

Statement or use the keyword "NULL" to explicitly specify it.

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

28
 Example:

In the above figure, null value is insert for the column rohit.

6.3 Insert a Default Value in the Table

To insert a default value into a table, you can again omit the specific column when performing
the insertion. If a column has a default value defined, the database will automatically use that
default value for the column if you don't provide any explicit value during the insertion.

Syntax
insert into table_name value(value_1, value_2,default,default,.......)

29
 Example:

In the given figure, default value is inserted for status active.

6.4 Delete all the record of table.

To delete all records from a table in SQL, you can use the DELETE command without
specifying a condition.

Syntax:
delete from table_name;

30
 Example:

In the table, employee’s table record is deleted completely.

6.5. Delete a Particular Record Using Where Clause

It identifies to delete a particular record from a table using the WHERE clause in SQL, you can
specify the condition the record you want to delete.

Syntax:
delete from your_table_name where id = 123;

31
 Example:

Here, Empid =5 is deleted completely from the previous table.

6.6. Updating Single Column Value in a Record

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

Syntax:
update table_name set column1=value1 where primary key
attribute=value;

32
 Example:

In the above figure, updating the record of column gender f to m of stu_id 12 from the previous
table.

6.7. Updating Multiple Column Value in a Record

It is use to update multiple column value in a table.

Syntax:
update table_name set column name 1=value1, column_name2=value2,
column_name3=value3 where primary key attribute=value;

33
Example:

In the above figure, multiple column record is update like Jayprakesh shah is update as rishab
from previous table similarly, Bhagat and gender ‘m’ is updated as Bhagat and ‘f’ from the
previous one.

34
 CHAPTER 7: SQL CLAUSES

7.1. Display all Record of Table

SELECT clauses use * character to retrieve all record from table.

Syntax:
select * from table_name;

Example:

In the figure, all record of table is display.

7.2. Displaying Certain Records Based on Condition

It is used to display certain record of table based on condition using WHERE clauses, AND, OR,
<, >, etc.

Syntax:
Select * from table_name where column=value;

35
 Example:

In the table, only gender male ’m’ record is display.

7.3. Displaying Specific Columns from Record

It is used to display specific columns record from the table.

Syntax:
select column1, column2,…..from table_name;

36
Example:

In the table, specific record is display, the record of stu_fname, stu_lname and phone is display
from the previous table student.

37
 CHAPTER 8: SQL SELECT CLAUSES

8.1. Where clause

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

Syntax:
Select column1,column2,……from table_name where condition;

Example:

In the table, where clauses is used it display the record of id, name, gender etc. from the table
student where age is greater than 19.

38
8.2. Like clause (pattern matching)
Like clause is used to search for a specified pattern in a column.

Syntax:
select * from table_name where column_name like ‘%a’;

Example:

In the table, like clause is used to search address from the previous table.

8.3. Describe clause

It displays metadata about the columns, indexes, and data partitions of tables or views.

Syntax:

39
 desc table_name;

Example:

In this table, students table is describe.

8.4. Order By clause

It is used to sort (ascending or descending order) the result set of a query based on one or more
columns.

Syntax:
select column1, colum2 from table_name order by column1 asc, column2
desc;

40
 Example:

In this figure, column name is sort as ascending order and nationality is sort as descending order
from the previous table.

8.5. Group By clause

It is used to group rows that have the same values in specified columns into summery rows. It’s
often used in combination with aggregate functions like SUM, COUNT, AVG, etc.

Syntax:
select column_name, avg (column) from table_name group by column_name;

41
 Example:

In this table, average fee is shown.

8.6. Distinct clause

It is used to retrieve only unique values from a specified column or columns

Syntax:
select distinct column _name from table_name;

42
 Example

In this table, distinct clause is used and unique value of gender column is displayed.

8.7. AND operator

It is used to combine multiple conditions in a WHERE clause to filter the results of the query.

Syntax:
select * from table_name where condition1 and condition2;

43
 Example:

In this table, AND operator is used which retrieve all student from “Nepali” Nationality whose
fee is greater than 21000.

8.8. OR operator
Syntax:
select * from table_name where column1 = ‘value1’ or column2 > value2;

44
Example:

In this table, OR operator retrieve rows where the category is either ‘electronic’ or price > 20000.

45
 CHAPTER 9: SQL VIEW

9.1. Different Between View and Table

1. Table
 A table is a fundamental database object that stores data in a structured manner.
 It consists of rows and columns, where each row represents a record, and each column
represents a field or attribute of that record.
 Tables are used to store and manage data persistently.
 They can be created, updated, and deleted using SQL commands.
 Data in a table is physically stored on disk.
 Tables can have indexes to optimize data retrieval.

2. View
 A view is a virtual table that is derived from one or more base tables or other views.
 It does not store data itself; instead, it presents data from the base tables in a specific way.
 Views are defined by SQL queries and can contain joins, filters, aggregations, and other
transformations.
 Views provide a way to present a subset of the data or to simplify complex queries.
 They can enhance security by allowing users to access specific columns of a table
without exposing the entire table.

Views do not consume additional storage space since they are just saved queries that are
executed on-the-fly when queried.

9.2. Creating a View

It is used to create view in a table.

Syntax:
create view view_name as (select statement);

46
 Example:

In the table, View Company is created from product table.

9.3. Deleting a View

It is use to delete a view in a table.

Syntax:
drop view view_name;

47
Example:

In the table, company view is drop.

48