An intoduction to sql and its components

It helps to understand SQL and DDL, DML, DCL, TCL

1. An Introduction to SQL and its components Dr. Monika Jain Dr. Ambedkar Institute o f Management Studies and Research

2. Learning Objective Students will be able to : Understand features of SQL Classify components of SQL Differentiate between DDL and DML Apply use different DML and DDL commands for database applications

3. SQL-Structured Query Language Language (for communicating databases) Structure (on tables) Table/Relation E.F. Codd Relational Model First give theoretical concept that data can be stored IBM implemented SQL for the First time Old name - SEQUEL-Simple English Query Language English like language --SELECT , FROM, WHERE, GROUP BY, ORDER BY Oracle , DB2, MySql databases uses SQL No SQL ( for unstructured data)

4. Features of SQL (Structured Query Language) Domain Specific Language -RDBMS SQL is the fourth generation (4GL) language used to manipulate and retrieve data from the relational databases. SQL is an ANSI (American national Standards Institute) standard language It is non-procedural language( Declarative ) Select name FROM employee WHERE city =‘DELHI’;

5. Features of SQL (Structured Query Language) SQL enables the programmer or database administrator(DBA) to do the following: Retrieve desired information Modify a database structure Update the information of the database Add user permissions on the table or database

6. Features of SQL SQL is used to communicate with the database It allows user to define and manipulate data in the database. It allows user to create different database objects and remove when required. SQL can execute a queries against a database to get information A query is a user request to retrieve the desired data from the database based on some conditions . IRCTC. Query means asking questions to database

7. SQL Commands

8. Data Definition Language (DDL) DDL commands deals with the structure of the database DDL commands are auto commit After DDL commands are executed , one cannot undo the changes DDL commands are faster

9. Data Definition Language DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. CREATE - Used to create a new table, a view of a table, or other object in database. ALTER - Used to modify an existing database object, such as a table. DROP - Used to delete an entire table, a view of a table or other object in the database. TRUNCATE : It is used to delete all the rows from the table and free the space containing the table Rename : It is used to rename the table

10. Data Manipulation Language ( DML ) INSERT - Used to create a record. UPDATE - Used to change certain records. DELETE - Used to delete certain records. Data Query Lanuguage ( DQL ) These statements are used to perform queries on the database to get back the desired information. SELECT - Used to retrieve certain records from one or more tables.

11. Data Control Language ( DCL ) DCL includes commands which mainly deals with the rights, permissions and other controls of the database system. GRANT- Used to give a privilege to someone. REVOKE- Used to take back privileges granted to someone.

12. Transaction Control Language ( TCL ) Transaction Control Language(TCL) commands are used to manage transactions in the database. COMMIT- COMMIT  command is used to permanently save any transaction into the database. When we use any DML command like INSERT, UPDATE or DELETE, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back. ROLLBACK- Is used to UNDO the changes in the database If we have used the UPDATE command to make some changes into the database, and realise that those changes were not required, then we can use the ROLLBACK command to rollback those changes, if they were not commited using the COMMIT command. SAVEPOINT- command is used to temporarily save a transaction so that you can rollback to that point whenever required.

13. THANK YOU