Answer:
1. Data Manipulation is:
- Retrieval of information from the database
- Insertion of new information into the database
- Deletion of information in the database
- Modification of information in the database
2. ADML is a language which enables users to access and manipulate data. The goal is to provide efficient human interaction with the system.
3. There are two types of DML:
- Procedural: the user specifies what data is needed and how to get it
- Nonprocedural: the user only specifies what data is needed
- Easier for user
- May not generate code as efficient as that produced by procedural languages
4. A query language is a portion of a DML involving information retrieval only. The terms DML and query language are often used synonymously.
DML Commands : Select, Insert, Update, Delete
- Insert – This command is used to add record(s) to a table. While inserting a record using the insert statement, the number of records being entered should match the columns of the table. In case the number of items being entered are less than the number of columns, in that case the field names also need to be specified along with the insert statement. See code example below
Example: insert into student sr_no,st_name,st_class,st_add values(1,’Ram’, ’BCA’,1101,’jaipur’ ; 1 row inserted
2. Select – This command is used to fetch a result set of records from a table, view or a group of tables, views by making use of SQL joins. Retrieval of data using SQL statements can be done by using different predicates along with it like
A
- Where b) Group By c) Having d) Order By Examples :
There area table student that are shown in below:
Use of where using select command:
- Select * from student where st_ad=’jaipur’;
Output:
2. Select st_name,st_roll,st_class from student where st_name=’ram’;
Ram 1101 BCA
Group by
The Group Bystatement in SQL is used for aggregation, which means that the result that is returned is based on grouping of results based on a column aggregation.
Example : select st_add,sum(st_roll) from student where group by st_add Having
The Having statement in SQL makes sure that an SQL SELECT statement should only return rows where aggregate values match conditions that are stated.
Example : select st_add,sum(st_roll) from student where groupby st_add having st_roll>2000;
Order By
The Order By clause in SQL is used to set the sequence of the output in terms of being alphabetical, magnitude of size, order of date. It may accompanied by an ‘asc’ or ‘desc’ clause so as to specify whether the results are in ascending or descending order. Note: The results of a select query that does not use asc or desc is in ascending order, by default.
Example : for ascending order :
Select * from student order by st_name asc;
For descending order : Select * from student order by st_name desc ;
3. Update – This command is used to edit the record(s) of a table. It may be used to update a single row based on a condition, all rows, or a set of rows based on a condition. It is used along with the set clause. Optionally, a where clause may be used to match conditions. See code example Update table student set st_roll=6576 where st_name=’Ram’;
4. Delete – This command is used to remove record(s) from a table. All records may be removed in one go, or a set of records may be deleted based on a condition. See code example below:
Delete from student where st_name=1101
