Data Manipulation Language (DML) Quiz

Data Manipulation Language (DML)

• DML is the subset of SQL used to manage data in a database.
• DML includes the following operations: Create, Read, Update, and Delete (CRUD).

Insert Query

• Purpose: To add new records to a database table.
• Syntax: INSERT INTO <table_name> (<column1>, <column2>, ... , <columnN>) VALUES (<value1>, <value2>, ... , <valueN>);
• Example: INSERT INTO CUSTOMERS (ID, Name, Salary) VALUES (1, 'John', 50000);
• Alternative Syntax (optional): INSERT INTO <table_name> VALUES (<value1>, <value2>, ... , <valueN>); (If values are provided for all columns in the table in the correct order)
• Note: The order of values provided in the VALUES clause must match the order of the columns specified in the query.

Select Query

• Purpose: To retrieve data from a database table.
• Syntax: SELECT <column1>, <column2>, ... , <columnN> FROM <table_name>;
• Example: SELECT ID, Name, Salary FROM CUSTOMERS;
• Note: You can select specific columns, all columns (SELECT *), or a combination thereof. The result is a result-set, which is a table representation of the data.

Delete Query

• Purpose: To remove existing records from a database table.
• Syntax: DELETE FROM <table_name> WHERE <condition(s)>;
• Examples:
  • DELETE FROM CUSTOMERS WHERE ID = 6; (Deletes a specific record)
  • DELETE FROM CUSTOMERS; (Deletes all records from the table)
• Note: The use of a WHERE clause is crucial for deleting specific records.

Update Query

• Purpose: To modify the data of existing records in a database table.
• Syntax: UPDATE <table_name> SET <column1> = <value1>, <column2> = <value2>, ... WHERE <condition(s)>;
• Examples:
  • UPDATE CUSTOMERS SET ADDRESS = 'New Address' WHERE ID = 6; (Updates the address of a specific record)
  • UPDATE CUSTOMERS SET ADDRESS = 'New Address', SALARY = 60000; (Updates multiple columns for all records)
• Note: Like DELETE, the use of WHERE clause is essential for specifying which records to modify.

Where Clause

• Purpose: To filter and restrict the data returned by the query.
• Syntax: Used within SELECT, UPDATE, and DELETE statements.
• Syntax (example): WHERE <condition(s)>
• Examples (Within SELECT):
  • SELECT ID, Name FROM CUSTOMERS WHERE SALARY > 50000; (Returns data only for customers with salary greater than 50000)
  • SELECT ID, Name FROM CUSTOMERS WHERE City = 'London' AND Salary > 40000; (Returns data for customers who meet both conditions)
• Notes:
  • The WHERE clause enables targeted data retrieval based on specified conditions.
  • It is used to filter and extract subsets of data from a table.
  • Use of comparison operators (>, <, =, >=, <=, !=), logical operators (AND, OR, NOT), and parentheses can further refine the conditions in WHERE clause.