Click to edit Master title style

Unit - 3

Database Management
System
1
 Objectiv
es title style
Click to edit Master

 Create Ta b l e i n a D a t a b a s e u s i n g S Q L

 Insert Ta b l e i n a D a t a b a s e u s i n g S Q L

 Using Select Command

2 2
Click to edit Master title style

Retrieving Data Using

Query / SQL Commands

3 3
 Click to edit Master title style
Introduction
The data from the database can be retrieved using DDL (Data
Definition Language) or DML (Data Manipulation Language)
Commands. SQL is a Structured Query Language in which the
user will be able to do Create Table, Insert Table, select data
from the table, Update the Table, Delete the Table, Drop and
Alter the Table. From the above mentioned options for a table,
some of the commands are classified under Data Definition
Language (DDL) and Data Manipulation Language (DML).
SQL is a Case Sensitive language.
4 4
 Different Commands in
Click
Create
SQL to edit
Table Master
– Used to create a tabletitle style
in MySQL
Insert Table – Used to insert rows in a table
Select – Used to fetch the data from the table
Update – Used to modify the records
Delete – Used to delete the records
Drop – Used to delete the table
Alter – Used to add / delete the columns
From the above functions used in the tables,
the functions are classified under DML and DDL.
The functions which are classified under DML are
Select, Insert, Update and Delete. The functions
which are classified under DDL are Create, Alter,
Drop 5 5
 Create Table in a Database
Click to edit Master title style
using SQL
To create a Table using SQL, the syntax is
Create table TableName (ColumnName1 datatype, ColumnName2 datatype);
Steps to create Table using SQL
Step 1: Select Tools SQL option.
Step 2: Execute SQL Statement window will appear.
Step 3: To create a table, give the commands in the syntax
and Click Execute option.
Step 4: If there are no errors in the syntax, it will be displayed
as “Command Successfully executed” and a table is
created with the defined fields.
Step 5: Select window from Menu bar and Click Close.
Step 6: Open the saved database .
Step 7: The created table will appear. 6 6
Create Table in a Database
Click to edit Master title style
using SQL

7 7
 Insert Table in a database
Click to edit Master title style using
SQL
This command is used to insert rows in a table.
Syntax to insert Table is
insert into tablename values(value1, value2, …………., valuen);
While using the command, the data values should be in the same
order as the column names in a table.
Steps to insert Rows in a Table using SQL
Step 1: Select Tools SQL option.
Step 2: Execute SQL Statement window will appear.
Step 3: Give the insert table command and click on
Execute button. If there are no errors in the
declaration then it will display “Command Executed
Successfully”, and the rows will be added to the table.
Step 4: After Entering the command, Double click the Table and the data will appear. 8 8
Insert Table in a database using
Click to edit Master title style
SQL

9 9
 Select Command
Click to edit Master title style
This command is used to retrieve a subset of
rows and columns from more than one tables.
Syntax: Select <column1>, <column2>………..
<column n> from Tablename
Steps for Select Commad:
Step 1: Select Tools SQL option.
Step 2: Execute SQL Statement window will appear.
Step 3: When Select command is executed and you
won’t be able to view the result then go to the main
window and select Queries option and select
“Create Query in SQL View” option.
SQL Design Window will appear.

10
10
 Select
Click to edit Master title style
Command

Step 4: Now enter the query in the window displayed.

Step 5: Run the query from Edit Run Query or by pressing F5 key from the keyboard.
Step 6: The result will be executed with the fields given in the table.
Step 7: In the same way if you want to add more records also you can modify in the select
command and when you run the query it will be updated in the result. 11
11
 Select
Click to edit Master title style
Command

12
12
 Select Command using Where
Click to edit Master title style
Condition
This Condition helps in specifying the criteria for selection of rows to be returned from the table.
Syntax: Select <Column name1><Column name2>…… from <TableName>where Condition;
Steps for select command using where condition:
Step 1: Select Tools SQL option.
Step 2: Execute SQL Statement window will appear.
Step 3: When Select command is executed and you won’t be able to view the result then go to the
main window and select Queries option and select “Create Query in SQL View” option. SQL Design
Window will appear.
Step 4: Now enter the query in the window displayed.
Step 5: Run the query from Edit Run Query or by pressing F5 key from the keyboard.

13
13
 Select Command using Where
Click to edit Master title style
Condition

Step 6: The result will be displayed as below.

Step 7: When executing the Where clause you can also include Relational Operators, Logical Operators.

14
14
 Select Command using Distinct
Click to edit Master title style
Condition
This command is used to eliminate the duplicate data from the table.
Syntax:
Select DISTINCT <Column Name> from <Table Name>
Steps for select command using Distinct Condition:
Step 1: Select Tools SQL option.
Step 2: Execute SQL Statement window will appear.
Step 3: When Select command is executed and you
won’t be able to view the result then go to the main
window and select Queries option and select
“Create Query in SQL View” option.
SQL Design Window will appear.
Step 4: Enter the SQL Command in the window. Here, depending on the field given in the query the result
15
15
will be displayed even if the table is having duplicate values also.
 Select Command using Distinct
Click to edit Master title style
Condition
In this window if you observe the output all the fields with the duplicate values will appear.
Step 5: When Distinct condition is used in the Select statement,
it will display only the single records but not the duplicate records.
Step 6: The output will be displayed by removing the duplicate records.

16
16
 Select
Click to Command
edit Master using titlebetween
style
Operator
Between operator defines the range of values that the common values will fall in to make condition True.
Here the range will include both Upper Value as well as Lower Value.
Syntax:
Select <Column Name> from <Table Name>
where ColName Between Start and end.
Steps for select command using between operator:
Step 1: Select Tools SQL option.
Step 2: Execute SQL Statement window will appear.
Step 3: When Select command is executed and you
won’t be able to view the result then go to the main
window and select Queries option and select
“Create Query in SQL View” option.
SQL Design Window will appear.
Step 4: Enter the SQL Command using between command.
In the above figure you can observe that the students who have scored marks
17
between 95 and 98 will display. 17
Select
Click to Command
edit Master using titleOrderstyleby clause
Operator
Order by Clause operator will allow you sorting of the results either
in ascending or Descending order of a query by one or more columns.
Syntax:
Select <Column Name>…<Column Name>from<Table Name>
where condition Order By <Column Name>
By default, order of sorting will be in ascending order and if you want the data in descending order.
Steps for select command using Order by Clause operator:
Step 1: Select Tools SQL option.
Step 2: Execute SQL Statement window displays.
Step 3: When Select command is executed and you won’t be able to view the result then go to the main
window and select Queries option and select “Create Query in SQL View” option. SQL Design Window
will appear.
Step 4: Enter the Order by clause command. When this command is executed by giving the criteria as
Name, all the name fields in the table will displayed in the ascending order.
18
18
Select
Click to Command
edit Master using Order
title styleby clause
Operator
Depending on the user criteria either the command can be given
in the Descending Order or Ascending Order and also from
where condition can also be given in the command.
To display the marks in the Descending Order, the following syntax is given.

19
19
Select *from
Click to editSAMPLE ORDER
Master title by Marks ASC;
style
To display the marks in the Ascending Order, the following syntax is given.
Select * from SAMPLE ORDER BY Marks ASC;
Update Command
Update command is used to change or
update the existing values in a table.
Syntax:
UPDATE TABLENAME
Set <Column Name> = <New Value>
Where Condition;
Steps:
Step 1: Click Tools SQL command.
Step 2: Execute SQL Statement window will appear.
Step 3: Select Table to which the data has to be modified.
Step 4: Enter the update command in Execute SQL Statement, depending on the
condition given and click on Execute Button. 20
20
Select *from
Click to editSAMPLE ORDER
Master title by Marks ASC;
style
Step 5: The value which is modified will be reflected in the respective table.

Step 6: Here, if you observe previously the marks of Rahul was 98,
but after modify command is applied the marks of Rahul is changed to 100. 21
21
Delete Command
Click to edit Master title style
This command is used to delete the
rows from the table but not from the
structure of a data.
Syntax:
Delete from Tablename Where
Condition;
For Example,
Delete from Tablename where
RollNo = 8;
From the table whose RollNo is 8 will be deleted.

22
22
 Alter Command
Click
Alter toisedit
Command used toMaster
add a columntitle style
or delete a column in a Table.
Syntax:
ALTER table <Table Name> ADD Column <ColumnName>
<Datatype> <Size> [Constraint];
Steps :
Step 1: Click Tools SQL option.
Step 2: Execute SQL Command statement window appear.
Step 3: Enter the ALTER command and click Execute button.
Step 4: When you Click Close option you cannot view the
newly added column.
Step 5: Close the Saved Database.
Step 6: Now open the table to which the column is added.
The updated column will be updated in the table. 23
23
 Drop Command
Click
Drop tois edit
Command Master
used to delete title
the added columnstyle
in the table.
Syntax: Alter Table name Drop ColumnName
The Column will be deleted.

24
24
Click to edit Master title style

25
25
Click to edit Master title style

26
26
Click to edit Master title style

27
27
Click to edit Master title style

Thank You

28