SQL commands in database management system

Database Language • Structured Query Language (SQL) is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. • SQL uses certain commands like Create, Drop, and Insert etc. to carry out the required tasks. Dr.Purnendu 2

• These SQL commands are mainly categorized into five categories as: • DDL – Data Definition Language • DML – Data Manipulation Language • DQL-Data Query Language • DCL – Data Control Language • Though many resources claim there to be another category of SQL clauses TCL – Transaction Control Language. So we will see in detail about TCL as well. (https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/) Dr.Purnendu 3

Activity 1: Create a table Person_info with 5 attributes and insert 5 rows into it and then show the table Dr.Purnendu 5

Activity 1: Create a table Person_info with 5 attributes and insert 5 rows into it and then show the table. Dr.Purnendu 6

Install EMP/DEPT table Dr.Purnendu 7

Use describe table_name to get the schema and its related Info. Dr.Purnendu 8

Click on (SQL WorkshopObject Browser) to see all the tables that we have created on left hand side and on right side u can see the attributes and other info. Dr.Purnendu 9

Import your CSV or excel data directly to work on that, go to App Builderclick on create and then from a file to upload ur csv/excel file Dr.Purnendu 10

So now you can work around this table Dr.Purnendu 11

Describe AI to see the schema Dr.Purnendu 12

Insert multiple rows(values) in the table, select all and then run. Note:Remember that these commands may change for different platforms (e.g MYSQL) Dr.Purnendu 13

Apex command to insert multiple rows. • begin • INSERT INTO AI VALUES (76, 2023, 'clothings', 'Caps', 600, 0.99); • INSERT INTO AI VALUES (77, 2022, 'Bikes', 'Touring Bikes', 6600, 0.90); • end; Dr.Purnendu 14

For MYSQL the multiple rows insertion follows this way. So better choose 1 platform and work on it. So in this course we will learn Apex Oracle and some time MYSQL too. Dr.Purnendu 15

Data Definition Language (DDL) Dr.Purnendu 16

Data Definition Language (DDL) • Specification notation for defining the database schema Example: create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2)) • DDL compiler generates a set of table templates stored in a data dictionary • Data dictionary contains metadata (i.e., data about data) • Database schema • Integrity constraints • Primary key (roll uniquely identifies student) • Authorization • Who can access what Dr.Purnendu 17

Data Definition Language (DDL) • Examples of DDL commands: • CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers). • DROP – is used to delete objects from the database. • ALTER-is used to alter the structure of the database. • TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed. • COMMENT –is used to add comments to the data dictionary. • RENAME –is used to rename an object existing in the database. Dr.Purnendu 18

Alter in Apex Oracle: ALTER-is used to alter the structure of the database, Earlier the table Person_info have attributes such as ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) but at line 2 and 3 we have added a new attribute named phone_number. Dr.Purnendu 19

Alter in Apex Oracle-Output Dr.Purnendu 20

Alter in Oracle Dr.Purnendu 21

Truncate will remove the data but table will still be there e.g here data inside table Person_info will be deleted although table will still be there. Dr.Purnendu 22

After truncate we can see that data is not found, but we know that after running truncate only data gets deleted but schema does not deletes. So to check the schema type command (describe Person_Info) Dr.Purnendu 23

Truncate and Drop (Oracle) Dr.Purnendu 24

So, now after running truncate all the data is removed only schema is left. It is faster than DELETE as it removes all rows in a single operation. Dr.Purnendu 25

Dropping Student table and then trying to see its schema, it shows object not found that means (Schema+Data) all dropped. Dr.Purnendu 26

Delete: Here we have Created a table name AAA and inserted multiple values into it, now delete the first row where AAA_ID=1, note here we will use ‘where’ clause and we will study about it in coming sessions. Dr.Purnendu 27

Delete Command: It may be slower than TRUNCATE because it removes rows one at a time. Before Delete After running Delete command Dr.Purnendu 28

Activity 2: Do the following activities 1) Create a table (schema), 2) Add the rows(values) using insert, 3) Delete the table, 4) Truncate the table, 5) Apply all the Alter commands given at (https://www.w3schools.com/sql/sql_alter.asp) 6) Drop that table. Note: Have a screen shot of all these activities and mail me the word format(extension: Name_Classrollno) at: purnendu.pandey@kiet.edu Dr.Purnendu 29

DML(Data Manipulation Language) • DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. • Examples of DML: • INSERT – is used to insert data into a table. • UPDATE – is used to update existing data within a table. • DELETE – is used to delete records from a database table. • MERGE – is used to make changes in one table based on values matched from anther. It can be used to combine insert, update, and delete operations into one statement. Dr.Purnendu 30

DML(Data Manipulation Language) Dr.Purnendu 31

DQL-Data Query Language • SELECT – is used to retrieve data from the a database. Dr.Purnendu 32

DCL(Data Control Language) • DCL (Data Control Language): DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system. • Examples of DCL commands: • GRANT-gives user’s access privileges to database. • REVOKE-withdraw user’s access privileges given by using the GRANT command. Dr.Purnendu 34

SQL commands in database management system

More Related Content

Similar to SQL commands in database management system

Recently uploaded

SQL commands in database management system