WEEK 5 - DML.pdf

Introduction to DML SQL Data Manipulation Language (DML) commands are used to manipulate data within the database. The main DML commands are: INSERT: Adds new records to a table. UPDATE: Modifies existing records in a table. DELETE: Removes records from a table. SELECT: Retrieves data from one...

Introduction to DML SQL Data Manipulation Language (DML) commands are used to manipulate data within the database. The main DML commands are: INSERT: Adds new records to a table. UPDATE: Modifies existing records in a table. DELETE: Removes records from a table. SELECT: Retrieves data from one or more tables. 1.SELECT -It is used to retrieve data from database CLAUSES: WHERE-which row to retrieve GROUP BY-arrange data into groups HAVING-select among group by clause ORDER BY-specifies the order AS- Alias or rename tables SYNTAX: SELECT * from ; Ex. Emp Age Contact # Address SELECT * from Emp; 2. INSERT command -It is used to inserting data into table, you can add I or more records into a simple table. SYNTAX: INSERT into ( col.name1 < datatype>, col.name2 < datatype>,… col.name n < datatype>) values (‘value1’, ‘value2’, ‘value n’); Col1 Col2 … Col n Ex. INSERT into Emp ( Eid int, ‘ename’ varchar(20), ‘City’ varchar(20), Salary int) values ( 1,’ ABD’,’ Davao’, 20,000) ; Eid ename City Salary 1 ABD Davao 20,000 3. UPDATE command -modified the record present in existing table SYNTAX: UPDATE set where condition; Ex. UPDATE Emp set salary=30,000 where ename =’ABD’; Eid ename City Salary 1 ABD Davao 30,000 UPDATE command Emp Ename age 001 ABC null UPDATE employees SET age = 24 WHERE age IS NULL; Emp Ename age 001 ABC 24 4. DELETE command -it is used to delete some or all records from the existing table Eid Ename age 001 ABC 32 002 DEF 42 003 GHI 45 Syntax: DELETE from where ; Ex. DELETE from Emp where Eid=’001’; Eid Ename age 002 DEF 42 003 GHI 45 DELETE command EX. DELETE from Emp; Eid Ename age Insert Data into Table To add data to the table, we use INSERT INTO, the syntax is as shown below: Syntax: //Below query adds data in specific column, (like Column1=Value1)// insert into Table_name(Column1, Column2, Column3) Values (Value1, value2, value3); //Below query adds data in table in sequence of column name(Value1 will be added in Column1 and so on)// Insert into Table_name Values (Value1, value2, value3); //Adding multiple data in the table in one go// Insert into Table_name Values (Value01, value02, value03), (Value11, value12, value13), (Value21, value22, value23), (ValueN1, valueN2, valueN3) Example Query This query will add data in the table named Subject -- Insert some sample data into the Customers table INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone) VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'), (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'), (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'), (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'), (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx'); Output: Create a Table Using Another Table We can also use CREATE TABLE to create a copy of an existing table. In the new table, it gets the exact column definition all columns or specific columns can be selected. If an existing table was used to create a new table, by default the new table would be populated with the existing values ​from the old table. Syntax: CREATE TABLE new_table_name AS SELECT column1, column2,… FROM existing_table_name WHERE ….; Query: Output: CREATE TABLE SubTable AS SELECT CustomerID, CustomerName FROM customer; SQL | ALTER (RENAME) Sometimes we may want to rename our table to give it a more relevant name. For this purpose, we can use ALTER TABLE to rename the name of the table. SQL ALTER TABLE is a command used to modify the structure of an existing table in a database. Note: Syntax may vary in different databases. Syntax(Oracle,MySQL,MariaDB): ALTER TABLE table_name RENAME TO new_table_name; Syntax(MariaDB): ALTER TABLE table_name CHANGE COLUMN old_name TO new_name; Query: CREATE TABLE Student ( INSERT: id INT PRIMARY KEY, name VARCHAR(50), INSERT INTO Student (id, name, age, email, phone) age INT, VALUES email VARCHAR(50), (1, 'Amit', 20, '[email protected]', '9999999999'), phone VARCHAR(20) (2, 'Rahul', 22, '[email protected]', '8888888888'), ); (3, 'Priya', 21, '[email protected]', '7777777777'), (4, 'Sonia', 23, '[email protected]', '6666666666'), (5, 'Kiran', 19, '[email protected]', '5555555555'); Output: Change the name of the table Student to Student_Details. Query: ALTER TABLE Student RENAME TO Student_Details; Output: Student_Details To Add a New Column with ALTER TABLE To add a new column to the existing table, we first need to select the table with ALTER TABLE command table_name, and then we will write the name of the new column and its datatype with ADD column_name datatype. Let’s have a look below to understand better. Syntax: ALTER TABLE table_name ADD column_name datatype; Query: ALTER TABLE Student ADD marks INT; Output: SQL ALTER TABLE – ADD, DROP, MODIFY The ALTER TABLE statement in SQL is used to add, remove, or modify columns in an existing table. The ALTER TABLE statement is also used to add and remove various constraints on existing tables. ALTER TABLE ADD Column Statement in SQL ADD is used to add columns to the existing table. Sometimes we may require to add additional information, in that case, we do not require to create the whole database again, ADD comes to our rescue. ALTER TABLE ADD Column Statement Syntax: ALTER TABLE table_name ADD (Columnname_1 datatype, Columnname_2 datatype, …Columnname_n datatype); Query: Example: ALTER TABLE Student ADD ROLL_NO NAME (AGE number(3),COURSE varchar(40)); 1 Ram 2 Abhi Output: 3 Rahul 4 Tanu ROLL_NO NAME AGE COURSE 1 Ram To ADD 2 columns AGE and 2 Abhi COURSE to table Student. 3 Rahul 4 Tanu MODIFY column COURSE in table Student. Query: ALTER TABLE Student MODIFY COURSE varchar(20); After running the above query the maximum size of the Course Column is reduced to 20 from 40. DROP column COURSE in table Student. Query: ALTER TABLE Student DROP COLUMN COURSE; Output: ROLL_NO NAME AGE 1 Ram 2 Abhi 3 Rahul 4 Tanu SQL | UPDATE Statement The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using the UPDATE statement as per our requirement. In a very simple way, we can say that SQL commands(UPDATE and DELETE) are used to change the data that is already in the database. The SQL DELETE command uses a WHERE clause. Syntax UPDATE table_name SET column1 = value1, column2 = value2,… WHERE condition; table_name: name of the table column1: name of first , second, third column…. value1: new value for first, second, third column…. condition: condition to select the rows for which the values of columns needs to be updated. Parameter Explanation UPDATE: Command is used to update the column value in the table. WHERE: Specifies the condition which we want to implement on the table. Note: In the above query the SET statement is used to set new values to the particular column and the WHERE clause is used to select the rows for which the columns are needed to be updated. If we have not used the WHERE clause then the columns in all the rows will be updated. So the WHERE clause is used to choose the particular rows. Query: CREATE TABLE Customer( CustomerID INT PRIMARY KEY, Output: CustomerName VARCHAR(50), LastName VARCHAR(50), Country VARCHAR(50), Age int(2), Phone int(10) ); -- Insert some sample data into the Customers table INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone) VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'), (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'), (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'), (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'), (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx'); Select * from Customer; Update Single Column Update the column NAME and set the value to ‘Nitin’ in the rows where the Age is 22. UPDATE Customer SET CustomerName = 'Nitin' WHERE Age = 22; Output: Updating Multiple Columns Update the columns NAME to ‘Satyam’ and Country to ‘USA’ where CustomerID is 1. UPDATE Customer SET CustomerName = 'Satyam', Country = 'USA' WHERE CustomerID = 1; Output: Note: For updating multiple columns we have used comma(,) to separate the names and values of two columns. Omitting WHERE Clause If we omit the WHERE clause from the update query then all of the rows will get updated. UPDATE Customer SET CustomerName = 'Shubham'; Output: The table Customer will now look like this, SQL | DELETE Statement Existing records in a table can be deleted using the SQL DELETE Statement. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause. Syntax: DELETE FROM table_name WHERE some_condition; table_name: name of the table Parameter Explanation Some_condition: condition to choose a particular record. DELETE FROM table_name(means we have to delete from table. Note: We can delete single as well as multiple records depending on the condition we provide in the WHERE clause. If we omit the WHERE clause then all of the records will be deleted and the table will be empty. The sample table is as follows: Deleting Single Record Delete the rows where NAME = ‘Rithvik’. This will delete only the fourth row. Query: DELETE FROM GFG_EMPLOyees WHERE NAME = 'Rithvik'; Output: Deleting Multiple Records Delete the rows from the table GFG_EMPLOyees where the department is “Development”. This will delete 2 rows(the first row and the seventh row). Query: DELETE FROM GFG_EMPLOyees WHERE department = 'Development'; Output: Delete All of the Records There are two queries to do this as shown below, Query: DELETE FROM GFG_EMPLOyees; Or DELETE * FROM GFG_EMPLOyees; Output: All of the records in the table will be deleted, there are no records left to display. The table GFG_EMPLOyees will become empty! Important Note: DELETE is a DML(Data Manipulation Language) command hence operation performed by DELETE can be rolled back or undone. SQL INSERT INTO Statement The INSERT INTO statement of SQL is used to insert a new row/record in a table. There are two ways of using the SQL INSERT INTO statement for inserting rows. SQL INSERT Query 1. Only Values The first method is to specify only the value of data to be inserted without the column names. INSERT INTO Syntax: INSERT INTO table_name VALUES (value1, value2, value3); table_name: name of the table. value1, value2 value of first column, second column,… for the new record Insert Data in Specified Columns – Syntax: INSERT INTO table_name (column1, column2, column3) VALUES ( value1, value2, value3); table_name: name of the table. column1: name of first column, second column. value1, value2, value3 value of first column, second column,… for the new record Suppose there is a Student database and we want to add values. ROLL_NO NAME ADDRESS PHONE AGE 1 Ram Delhi xxxxxxxxxxxxxx 18 2 RAMESH GURGAON xxxxxxxxxxxxxx 18 3 SUJIT ROHTAK xxxxxxxxxxxxxx 20 4 SURESH ROHTAK xxxxxxxxxxxxxx 18 3 SUJIT ROHTAK xxxxxxxxxxxxxx 20 2 RAMESH GURGAON xxxxxxxxxxxxxx 18 Method 1 (Inserting only values) – SQL INSERT Query If we want to insert only values then we use the following query: Method 1 (Inserting only values) – SQL INSERT Query If we want to insert only values then we use the following query: Query: INSERT INTO Student VALUES The table Student will now look like this: ('5','HARSH','WEST BENGAL', 'XXXXXXXXXX','19'); ROLL_NO NAME ADDRESS PHONE Age Output: 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18 5 HARSH WEST BENGAL XXXXXXXXXX 19 Method 2 (Inserting values in only specified columns) – SQL INSERT INTO Statement If we want to insert values in the specified columns then we use the following query: Query: INSERT INTO Student (ROLL_NO, NAME, Age) VALUES ('5','PRATIK','19'); Output: The table Student will now look like this: ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18 5 PRATIK null null 19 Notice that the columns for which the values are not provided are filled by null. Which are the default values for those columns? 2. Using SELECT in INSERT INTO Statement We can use the SELECT statement with INSERT INTO statement to copy rows from one table and insert them into another table. The use of this statement is similar to that of the INSERT INTO statement. The difference is that the SELECT statement is used here to select data from a different table. The different ways of using INSERT INTO SELECT statement are shown below: Inserting all columns of a table – INSERT INTO SELECT Statement Syntax: INSERT INTO first_table SELECT * FROM second_table; first_table: name of first table. second_table: name of second table. We have used the SELECT statement to copy the data from one table and the INSERT INTO statement to insert from a different table. Syntax: INSERT INTO first_table(names_of_columns1) SELECT names_of_columns2 FROM second_table; first_table: name of first table. second_table: name of second table. names of columns1: name of columns separated by comma(,) for table 1. names of columns2: name of columns separated by comma(,) for table 2. We have used the SELECT statement to copy the data of the selected columns only from the second table and the INSERT INTO statement to insert in the first table. Copying specific rows from a table – INSERT INTO SELECT Statement We can copy specific rows from a table to insert into another table by using the WHERE clause with the SELECT statement. We have to provide appropriate conditions in the WHERE clause to select specific rows. INSERT INTO table1 SELECT * FROM table2 WHERE condition; first_table: name of first table. second_table: name of second table. condition: condition to select specific rows. Suppose there is a LateralStudent database ROLL_NO NAME ADDRESS PHONE Age 7 SOUVIK HYDERABAD XXXXXXXXXX 18 8 NIRAJ NOIDA XXXXXXXXXX 19 9 SOMESH ROHTAK XXXXXXXXXX 20 Method 1 – (Inserting all rows and columns) If we want to insert only values then we use the following query: SQL INSERT INTO SELECT Query: INSERT INTO Student SELECT * FROM LateralStudent; Output: This query will insert all the data of the table LateralStudent in the table Student. The table Student will now look like this, ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18 7 SOUVIK DUMDUM XXXXXXXXXX 18 8 NIRAJ NOIDA XXXXXXXXXX 19 9 SOMESH ROHTAK XXXXXXXXXX 20 Method 2(Inserting specific columns) If we want to insert values in the specified columns then we use the following query: SQL INSERT INTO SELECT Query: INSERT INTO Student(ROLL_NO,NAME,Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent; Output: This query will insert the data in the columns ROLL_NO, NAME, and Age of the table LateralStudent in the table Student and the remaining columns in the Student table will be filled by null which is the default value of the remaining columns. The table Student will now look like this, ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18 7 SOUVIK null null 18 8 NIRAJ null null 19 9 SOMESH null null 20 Select specific rows to insert: INSERT INTO Student SELECT * FROM LateralStudent WHERE Age = 18; Output: This query will select only the first row from table LateralStudent to insert into the table Student. The table Student will now look like this, ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18 7 SOUVIK DUMDUM XXXXXXXXXX 18 To insert multiple rows in a table using Single SQL Statement: Syntax: INSERT INTO table_name(Column1,Column2,Column3,…….) VALUES (Value1, Value2,Value3,…..), (Value1, Value2,Value3,…..), (Value1, Value2,Value3,…..), ……………………….. ; Where, table_name: name of the table. Column 1: name of the first column, second column. Values: Value1, Value2, Value3: the value of the first column, second column. For each new row inserted, you need To provide Multiple lists of values where each list is separated by “,”. Every list of values corresponds to values to be inserted in each new row of the table. Values in the next list tell values to be inserted in the next Row of the table. Example: The following SQL statement inserts multiple rows in Student Table. Query: INSERT INTO STUDENT(ID, NAME,AGE,GRADE,CITY) VALUES(1,"AMIT KUMAR",15,10,"DELHI"), (2,"GAURI RAO",18,12,"BANGALORE"), (3,"MANAV BHATT",17,11,"NEW DELHI"), (4,"RIYA KAPOOR",10,5,"UDAIPUR"); Output: Thus STUDENT Table will look like this: ID NAME AGE GRADE CITY 1 AMIT KUMAR 15 10 DELHI 2 GAURI RAO 18 12 BANGALORE 3 MANAV BHATT 17 11 NEW DELHI 4 RIYA KAPOOR 10 5 UDAIPUR