IT53_DBMS-3C.pdf

III: STRUCTURED QUERY LANGUAGE-DML P a g e | 74 Data Manipulation Language The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data. The acronym CRUD refers to all of the major functions that need to be implemented in a...

III: STRUCTURED QUERY LANGUAGE-DML P a g e | 74 Data Manipulation Language The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data. The acronym CRUD refers to all of the major functions that need to be implemented in a relational database application to consider it complete. Each letter in the acronym can be mapped to a standard SQL statement: Insert Query SQL INSERT INTO Statement is used to add new rows of data to a table in the database. Syntax: There are two basic syntaxes of INSERT INTO statement as follows: Here, column1, column2,...column N are the names of the columns in the table into which you want to insert data. You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax would be as follows: Example: Following statements would create six records in CUSTOMERS table: You can create a record in CUSTOMERS table using second syntax as follows:  III: STRUCTURED QUERY LANGUAGE-DML P a g e | 75 All the above statements would produce the following records in CUSTOMERS table: Select Query SELECT Statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-sets. Syntax: The basic syntax of SELECT statement is as follows: Example: Consider the CUSTOMERS table having the following records: Following is an example, which would fetch ID, Name and Salary fields of the customers available in CUSTOMERS table: This would produce the following result:  III: STRUCTURED QUERY LANGUAGE-DML P a g e | 76 If you want to fetch all the fields of CUSTOMERS table, then use the following query: This would produce the following result: Delete Query SQL DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted. Syntax: The basic syntax of DELETE query with WHERE clause is as follows: You can combine N number of conditions using AND or OR operators. Example: Consider the CUSTOMERS table having the following records: Following is an example, which would DELETE a customer, whose ID is 6:  III: STRUCTURED QUERY LANGUAGE-DML P a g e | 77 Now, CUSTOMERS table would have the following records: If you want to DELETE all the records from CUSTOMERS table, you do not need to use WHERE clause and DELETE query would be as follows: Now, CUSTOMERS table would not have any record. Update Query SQL UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows, otherwise all the rows would be affected. Syntax: The basic syntax of UPDATE query with WHERE clause is as follows: You can combine N number of conditions using AND or OR operators. Example: Consider the CUSTOMERS table having the following records: Following is an example, which would update ADDRESS for a customer whose ID is 6:  III: STRUCTURED QUERY LANGUAGE-DML P a g e | 78 Now, CUSTOMERS table would have the following records: If you want to modify all ADDRESS and SALARY column values in CUSTOMERS table, you do not need to use WHERE clause and UPDATE query would be as follows: Now, CUSTOMERS table would have the following records: Where Clause SQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. If the given condition is satisfied, then only it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records. The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would examine in subsequent chapters. Syntax: The basic syntax of SELECT statement with WHERE clause is as follows: You can specify a condition using comparison or logical operators like >,