To Study and write DML Commands for given query.
- The SQL commands that deal with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
- It is the component of the SQL statement that controls access to data and to the database. Basically, DCL statements are grouped with DML statements.
- INSERT: It is used to insert data into a table Types of INSERT COMMAND a) Inserting a single record Syntax: INSERT INTO < relation/table name> (field_1,field_2……field_n)VALUES (data_1,data_2, ...... data_n); b) Inserting all records from another relation Syntax: INSERT INTO relation_name_1 SELECT Field_1,field_2,field_n FROM relation_name_2 WHERE field_x=data; c) Inserting multiple records Syntax: INSERT INTO relation_name field_1,field_2, ....field_n) VALUES (&data_1,&data_2,.......&data_n);
- UPDATE: It is used to update existing data within a table. Syntax: UPDATE relation name SET Field_name1=data,field_name2=data, WHERE field_name=data;
- DELETE: It is used to delete records from a database table. a) DELETE-FROM: This is used to delete all the records of relation. Syntax DELETE FROM relation_name; b) DELETE -FROM-WHERE: This is used to delete a selected record from a relation. Syntax: DELETE FROM relation_name WHERE condition;
- SELECT: The SELECT command shows the records of the specified table. Syntax: SELECT (column1,column2) FROM (Table Name)WHERE condition;
- Start the program.
- Read the given query.
- insert values into the table using INSERT INTO < relation/table name> (field_1,field_2……field_n)VALUES (data_1,data_2, ...... data_n);
- Update the existing data within a table by UPDATE command by UPDATE relation name SET Field_name1=data,field_name2=data, WHERE field_name=data;
- Delete the records in a table by DELETE command DELETE FROM relation_name WHERE condition;
- Use SELECT command shows the records of the specified table. SELECT (column1,column2) FROM (Table Name)WHERE condition;
- Show the output
- Stop the program
select categoryName,description from categories order by categoryName;
SELECT * FROM customer WHERE city = 'London' AND grade > 200;
SELECT * FROM orders WHERE (purch_amt < 200 OR NOT (ord_date >= '2012-02-10' AND customer_id < 3009));
SELECT * FROM customer WHERE grade IS NULL;
DELETE FROM surgeries WHERE surgery_id = 3 or surgeon_id = 4;
DELETE FROM customer WHERE OPENING_AMT BETWEEN 4000 AND 6000;
DELETE FROM customer WHERE LENGTH(CUST_NAME) = 6;
UPDATE products SET product_name = 'Grapefruit' WHERE product_id = 4;
UPDATE suppliers SET supplier_name = 'A1 Suppliers' WHERE supplier_id = 8;
UPDATE products SET reorder_lvl = 40 WHERE category = 'Grocery';
Thus the basic DML commands are executed.