Unit4_Data manipulation commands.pdf

Course Code: 4040233101 Course Name: Database Management System...

Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 UNIT 4 Data Manipulation Commands Topics: AddingTableRowsSavingTableRowsListingTable Rows Updating Table Rows Restoring Table Rows DeletingTableRows Select Queries: With Conditional Restrictions Arithmetic Operators ← Logical Operators: AND, OR & NOT Special Operators: Between IS NULL Like IN Advanced Data Definition Commands: ChangingaColumn'sDataTypes ChangingaColumn'sData Characteristics AddingaColumn DroppingaColumn ColumnAdvanced DataUpdates AddingPrimaryandForeignKeyDeletingaTablefromtheDatabase AggregateFunction SQL View What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL is an ANSI (American National Standards Institute) standard Structured Query Language (SQL) is the language used to manipulate relational databases. SQL is tied very closely with the relational model. In the relational model, data is stored in structures called relations or tables. Each table has one or more attributes or columns that describe the table. In relational databases, the table is the fundamental building block of a database application. Tables are used to store data on Employees, Equipment, Materials, Warehouses, Purchase Orders, Customer Orders, etc. Columns in the Employee table, for example, might be Last Name, First Name, Salary, Hire Date, Social Security Number, etc. What Can SQL do? execute queries against a database retrieve data from a database insert records in a database update records in a database delete records from a database Page | 41  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 create new databases create new tables in a database create stored procedures in a database create views in a database set permissions on tables, procedures, and views DDL, DML, DCL, TCL DDL - Data Definition Language: statements used to define the database structure or schema. CREATE- to create objects in the database ALTER- alters the structure of the database DROP delete- objects from the database TRUNCATE- remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object DML - Data Manipulation Language: statements used for managing data within schema objects. SELECT - retrieve data from the a database INSERT -insert data into a table UPDATE- updates existing data within a table DELETE - deletes all records from a table, the space for the records remains DCL - Data Control Language. GRANT - gives user's access privileges to database REVOKE - withdraw access privileges given with the GRANT command TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. COMMIT - save work done Page | 42  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 SAVEPOINT- identify a point in a transaction to which you can later roll back ROLLBACK- restore database to original since the last COMMIT TRANSACTION - Change transaction options like isolation level and what rollback segment to use DML are not auto-commit. i.e. you can roll-back the operations, but DDL are auto-commit Basic Statements sequence of oracle for writing SQL. SELECT (COLUMN LIST) FROM (TABLE NAME) [WHERE (CONDITION )] [GROUP BY (COLUMN NAME)] [HAVING(CONDTION)] [ORDER BY (EXPRESSION)] Select The SELECT statement allows you to retrieve records from one or more tables in your database. This operation is also called projection. Syntax SELECT columns FROM tables WHERE predicates; Examples Select Queries Page | 43  Course Code: 4040233101 Course Name: Database Management System SEMESTER: 1 All columns select * from dept; Selected columns select LOC, DEPTNO from DEPT; Selected columns with Alias select Sname,Sno as Rollno,Maths as Vigaan from Student Add Selected columns and also perform Mathematical operations on column Select Sno,Sname,Eng+Maths as Total , Fees , Fees*0.10 as , Fees + Fees*0.10 after increment from Student Where Conditions Selected columns with > condition SELECT name, city, state FROM suppliers WHERE supplier_id > 1000 All columns with > and < condition SELECT * FROM emp where sal>2000 and sal