DML PDF

Data Structures Database design SESSION 5 INTRODUCTION TO SQL DML Data Manipulation Language DML is a type of SQL command used to manipulate data stored in a database. DML commands are used to insert, update, delete, and retrieve data from a database. DML commands are used by developers and...

Data Structures Database design SESSION 5 INTRODUCTION TO SQL DML Data Manipulation Language DML is a type of SQL command used to manipulate data stored in a database. DML commands are used to insert, update, delete, and retrieve data from a database. DML commands are used by developers and database administrators to manage the data stored in the database. These commands are typically used in conjunction with DDL (Data Definition Language) commands, which are used to define and modify the structure of the database objects. ßelal A. Hamed Data Manipulation Language Some of the commonly used DML commands are: 1. SELECT: Used to retrieve data from one or more tables in the database. 2. INSERT: Used to add new rows of data to a table in the database. 3. UPDATE: Used to modify existing rows of data in a table in the database. 4. DELETE: Used to remove one or more rows of data from a table in the database. ßelal A. Hamed Introduction to SQL SESSION 5 INTRODUCTION TO SQL DML INSERT STATEMENT  Insert command INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table Insert a row into a table Insert multiple rows into a table using a single INSERT statement  Insert command Syntax: 1. Insert a row into a table INSERT INTO table_name (col1,col2,…) VALUES ("value1", "value2",...) 2. INSERT multiple rows – insert multiple rows into a table using a single INSERT statement INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2),..., (value_list_n);  Insert command EXAMPLE 1 EXAMPLE 2 INSERT INTO Person INSERT INTO Person (id, LastName, City) (id, LastName, City) VALUES VALUES (222, ‘Hassan', ‘Assuit') (1, ‘Osama', ‘Minia’), (2, ‘Eslam', ‘Assuit’), (3, ‘Alyaa', ‘Cairo'); Example: Students Sheet Students Sheet Platform Name : SWE Platform Description: Software Engineering Graduate Profile: ALL Found. Total Att. Appno Name F-code Faculty Major Address Telno Startdate Grade Hrs 123 Ahmed SC-phy Science Physics Haram 3386842 A 600 14 Sep 0 124 Mona Eng-cs Engineering Computer Dokki 3389745 B 591 15 Sep 5,33897 445 127 Ali Com-ac Commerce Accounting Nasr City 2241593 A 550 21 Sep 9 223 Karim Med-bio Medicine Biochemistry Sheraton 2286845 C 600 14 Sep 6 ßelal A. Hamed Example: Students Sheet Platform (name , desc , graduate) Students(appno, name , f-code, address) Std_Tel(appno, telno) Students_platform(pfname,appno,Foundgrade,attd, start_date) Fac_majors(f-code ,faculty , major) ßelal A. Hamed Example: Students Sheet Independent tables ßelal A. Hamed Example: Students Sheet FK-dependent tables ßelal A. Hamed Example: Students Sheet FK-dependent tables ßelal A. Hamed Introduction to SQL SESSION 5 INTRODUCTION TO SQL DML UPDATE STATEMENT  Update command UPDATE: This command is used to update or modify the value of a column in the table. To modify existing data in a table. We can Update one column in Row and several columns in row ßelal A. Hamed Update command Syntax: One Column in a Row UPDATE table_name SET column_1 = {new value} [WHERE {conditions} ] Update several Columns in a Row: UPDATE table_name SET c1 = v1, c2 = v2,... cn = vn [WHERE condition] ßelal A. Hamed Update command Examples: One Column in a Row UPDATE Person SET City= ‘Assiut’; Update several Columns in a Row: UPDATE Person SET Address = ‘241 El-haram ', City = ‘Giza' WHERE LastName = ‘El-Sayed’; ßelal A. Hamed Example: Students Sheet ßelal A. Hamed Introduction to SQL SESSION 5 INTRODUCTION TO SQL DML DELETE STATEMENT  Delete command DELETE: It is used to remove one or more row from a table. To remove one or more rows from a table completely Syntax: DELETE FROM table_name [WHERE {condition} ] First, you specify the name of the table from which the rows are to be deleted in the FROM clause. ßelal A. Hamed Delete command EXAMPLE: 1. Delete All Data DELETE FROM Person 2. Specify the row DELETE FROM Person WHERE LastName = ‘El-Sayed' ßelal A. Hamed Delete command EXAMPLE: 3. Deleting Rows Based on Another Table DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%'); ßelal A. Hamed Example: Students Sheet ßelal A. Hamed Introduction to SQL SESSION 5 INTRODUCTION TO SQL DML SELECT STATEMENT Select command SELECT: is used to fetch the data from the database To retrieve data from a table When processing the SELECT statement, SQL Server processes the FROM clause first and then the SELECT clause even though the SELECT clause appears first in the query. Syntax: SELECT FROM < table list> [ WHERE ] ßelal A. Hamed Elements of the SELECT Statement Element Expression Role SELECT Defines which columns to return FROM Defines table(s) to query WHERE Filters rows using a predicate GROUP BY Arranges rows by groups HAVING Filters groups using a predicate ORDER BY Sorts the output ßelal A. Hamed Logical Query Processing The order in which a query is written is not the order in which it is evaluated by SQL Server. 5: SELECT 1: FROM 2: WHERE 3: GROUP BY 4: HAVING 6: ORDER BY ßelal A. Hamed Conditions Comparison Conditions Other Conditions = Equal. Between …… AND ….. > greater than. (between two values - Inclusive). >= greater than or equal. IN (set) (Match any of a list of values) < less than. , =, , !< Logical Operators AND, OR, NOT Arithmetic Operators +, -, *, /, % Concatenation + ßelal A. Hamed Example: Students Sheet ßelal A. Hamed Example: Students Sheet ßelal A. Hamed Example: Students Sheet ßelal A. Hamed Example: Students Sheet ßelal A. Hamed Data Manipulation Language Summary SELECT column1, column2,... UPDATE table_name FROM table_name SET column1 = value1, column2 = value2,... WHERE condition; WHERE condition; INSERT INTO table_name DELETE FROM table_name (column1, column2,...) WHERE condition; VALUES (value1, value2,...); ßelal A. Hamed Assignment: Company Database Schema 1. Create a database named “CompanyDB” 2. Create the following tables Employee(Fname, Lname, SSN, BDATE, Addresss, Gender, Salary, Superssn, Dno) Department( Dname, DNum ,MGRSSN, MGRStart ,date) Works_for( ESSN, Pno, Hours) Project( Pname, Pnumber, Plocation, City, Dnum) Dependent( ESSN, Dependent_name, Gender, Bdate) 3. Insert a 5 rows in each table ßelal A. Hamed Assignment: Company Database Schema Table Data ßelal A. Hamed Assignment: Company Database Schema Table Data ßelal A. Hamed Assignment: Company Database Schema 4. Try to create the following Queries: Display the Projects full data of the projects with a name starts with "a" letter. Display all the employees in department 30 whose salary from 1000 to 2000 LE monthly. Display the employees data who works in department number 10. Display the female names who work under supervision of supervisor 223344 Display the first name and lname of employees as full name and their supervisorSSN ßelal A. Hamed