![COMPONENTS:
DDL [Data Definition Language]
DML [Data Manipulation Language]
DQL [Data Query Language]
DCL [Data Control Language]](https://image.slidesharecdn.com/dbmschapter-3-230926163104-76a503cb/85/DBMS-Chapter-3-ppsx-4-320.jpg)
![Sorting Data of a Table: Order by
Data can be viewed in a sorted order.
Syntax
Select * from TBLNAME ORDER BY Col1 [Order], Col2
[ORDER],, ColN [Order]);
Example
Select * from Acc ORDER BY bname;
Select * from Acc ORDER BY bname desc;
DBMS-3330703-2017 HKPATEL 21
Ano Bal Bname
A02 AND
A03 700 KSAD
A04 300 KSAD
A01 500 VVN
Ano Bal Bname
A01 500 VVN
A03 700 KSAD
A04 300 KSAD
A02 AND](https://image.slidesharecdn.com/dbmschapter-3-230926163104-76a503cb/85/DBMS-Chapter-3-ppsx-21-320.jpg)
DBMS Chapter-3.ppsx
- 2. SQL, a Structured Query Language. It is a language that provides an interface to relation database system. SQL was developed by IBM in 1970. Oracle, at that time known as Relational software, released first commercially available implementation of SQL. SQL has been accepted as a standard language for RDBMS today. So, any RDBMS product supports minimal set of SQL functionalities along with its own extra functionalities. SQL
- 3. SQL is used as command language in Oracle products. Oracle SQL contains many extensions provided by SQL *PLUS in form of interactive SQL and PL/SQL. Features :- It is a non-procedural language . This means that it requires only to specify what operations to be done to retrieve data, without specifying how to perform that operations. It is an English-like language.
- 4. COMPONENTS: DDL [Data Definition Language] DML [Data Manipulation Language] DQL [Data Query Language] DCL [Data Control Language]
- 5. 1. DDL (Data Definition Lang.) :- It is a set of SQL commands used to create, modify and delete database objects such as tables, views, indices, etc. It is normally used by DBA and database designers. It provides command like- CREATE: to create object in a database. ALTER: to alter the schema, or logical structure of database. DROP: to delete objects from the database. TRUNCATE: to remove all records from the table .
- 6. 2. DML (Data Manipulation Lang.):- It is a set of SQL command used to insert, modify and delete data in a database. It is normally used by general users who are accessing database via pre- developed applications. It provides commands like- I ) INSERT: to insert data into table. II) UPDATE: to modify existing data in a table. III) DELETE: to delete record from a table. IV) LOCK: to lock tables to provides concurrency control among multiple users.
- 7. 3. DQL (Data Query Lang.) :- It is components of SQL that allows data retrieval from the database. It provides command like SELECT. This command is a heart of SQL, and allows data retrieval in different ways. 4. DCL (Data Control Lang.):- It is set of SQL commands used to control access to data and database. Occasionally DCL command are grouped with DML commands.
- 8. It provides commands like- I) COMMIT: to save work permanently. II) ROLLBACK: to undo work and restore database to previous state. III) SAVEPOINT: to identify a point in a transaction to which work can be undone. IV) GRANT: to give access privileges to users on the database. V) REVOKE: to withdraw access privileges given to users on the database.
- 9. Rules of SQL: SQL statement start with an action verb. For example, SELECT statement. Each verb may be followed by number of clauses. For example, WHERE. A space separates key words and clauses. For example, DROP table Employee. A comma , separates parameters without a clause. A semicolon ; is used to end SQL statement. Statement may be split across lines but keyword cannot be split.
- 10. Character/String Data type Numeric Data type Date Data type Binary Data type Data Types
- 11. CHARACTER DATA TYPE Sr. No. PURPOSE CHAR VARCHAR VARCHAR2 1 STORAGE CAPACITY Up to 255 bytes up to 2000 bytes 4000 bytes 2 SPACE CHAR is fixed length, right padded with spaces. VARCHAR is fixed length, right padded NULL VARCHAR2 is variable length. 3 It will waste space on the disc memory. This type of the data type is used to reserve the space for future usage. The length of the variable string values are stored within the values itself on the disc. 4 STANDARD ANSI standard Oracle standard 5 COMPARE Faster Slower 6 DEFAULT SIZE 1 No No CHAR v/s VARCHAR v/s VARCHAR2
- 12. NUMBER(P) : Fixed point number with scale of zero and precision p NUMBER(P,S): Floating Point number P: Precision part, i.e maximum number of digits. S: Scale, i.e number of digits to the right of the decimal point NUMBER Floating point number with precision of 38 NUMERIC DATA TYPE
- 13. DATE Default Format DD-MON-YY i.e 01-AUG-17 SYSDATE DATE DATA TYPE
- 14. RAW Store binary type data, max length 255 bytes LONG RAW Large number of binary data Often called as Binary Large Object (BLOB) Max length 2GB A LONG RAW column can not be indexed BINARY DATA TYPE
- 15. CREATING TABLE CREATE TABLE T_NAME (ColName1 datatype (SIZE), ColName2 datatype (SIZE),, ColNameN datatype (SIZE)); Table name should be unique Table name & Column name must start with alphabets. Table name & Column name are not case sensitive Each column is separated by comma (,) Each statement end with semicolon (;) DBMS-3330703-2017 HKPATEL 15
- 16. Describe table Syntax Describe TBL_NAME; Desc TBL_NAME; Describes a given table It Display Column name, not null constraints, data type and size of each table. EX: DESC ACC; OUTPUT DBMS-3330703-2017 HKPATEL 16 Name Null? Type Ano Char(10) bal Number(9) Bname Varchar(10)
- 17. Inserting New Rows Syntax Insert into TBL_Name (ColName1, ColName2,, ColNameN) Values (EXPR1, EXPR2,, EXPRN); EXP can be a constant value, some variable, or any EXPR. Character and Date constant are enclosed within single quotes. Ex: Insert into acc (ano,bal,bname) values(A01,500,VVN); Insert into acc (bal, ano, bname) values(700, A04,AND); DBMS-3330703-2017 HKPATEL 17
- 18. Inserting NULL values 3 Methods Null Keyword Insert into ACC Values(A02,NULL,VVN); Empty single Quote Insert into ACC Values(,800,VVN); Omit the related columns Insert into ACC (ano, bname) Values(A02,AND); DBMS-3330703-2017 HKPATEL 18
- 19. SELECT Command- Viewing data of the table Variation of SELECT command is as follow Display All rows and All Columns Select * from Acc; Filtering Table Data Selected columns, All rows Select col1,col2,,colN from TBLNAME; Select ano, bal from ACC; Selected rows and all columns Select * from TBLNAME where CONDITION; Select * from ACC where bname=VVN; DBMS-3330703-2017 HKPATEL 19 Ano Bal Bname A01 500 VVN A02 AND A03 700 KSAD A04 300 KSAD Ano Bal A01 500 A02 A03 700 A04 300 Ano Bal Bname A01 500 VVN
- 20. Selected columns, selected rows Select col1, col2, , colN from TBLNAME where condition; Select ano from ACC where bal>600; Eliminating Duplication Select Distinct Col1, col2,,colN from TBLNAME; Select bname from ACC; Select distinct bname from ACC; DBMS-3330703-2017 HKPATEL 20 Ano Bal Bname A03 700 KSAD Bname AND VVN KSAD KSAD Bname AND VVN KSAD
- 21. Sorting Data of a Table: Order by Data can be viewed in a sorted order. Syntax Select * from TBLNAME ORDER BY Col1 [Order], Col2 [ORDER],, ColN [Order]); Example Select * from Acc ORDER BY bname; Select * from Acc ORDER BY bname desc; DBMS-3330703-2017 HKPATEL 21 Ano Bal Bname A02 AND A03 700 KSAD A04 300 KSAD A01 500 VVN Ano Bal Bname A01 500 VVN A03 700 KSAD A04 300 KSAD A02 AND
- 22. Grouping Data of a Table: Group by GROUP BY Group records based on distinct values Syntax Select col1,col2,,colN aggr.Fun (Argument) from TBLNAME group by Col1,Col2,,ColN; Select bname, sum(bal) TOTAL BALfrom ACC group by bname; DBMS-3330703-2017 HKPATEL 22 Bname Total BAL AND KSAD 1000 VVN 500
- 23. Grouping Data of a Table: Having HAVING Syntax Select col1,col2,,colN aggr.Fun (Argument) from TBLNAME group by Col1,Col2,,ColN; Having condition EX. Select bname, SUM(bal) from ACC group by bname Having bname=VVN; DBMS-3330703-2017 HKPATEL 23 Bname Total BAL VVN 500
- 24. Copying Table into Another table CREATING A TABLE FROM ANOTHER TABLE SYNTAX CREATE TABLE NEWTBLNAME (COL1, COL2,,COLN) AS SELECT COL1,COL2,,COLN FROM SOUCETBLNAME WHERE CONDITION; EX. CREATE TABLE ACC1 AS SELECT * FROM ACC; O/P: TABLE CREATED DBMS-3330703-2017 HKPATEL 24
- 25. Copying Data into Another table DBMS-3330703-2017 HKPATEL 25 INSERTING DATA INTO A TABLE FROM ANOTHER SYNTAX INSERT INTO DESTTBL (COL1, COL2,,COLN) SELECT COL1,COL2,,COLN FROM SOUCETBLNAME WHERE CONDITION; EX. INSERT INTO ACC1 SELECT * FROM ACC; O/P: 5 ROWS CREATED
- 26. DELETING RECORDS FROM A TABLE DELETE ALL ROWS SYNTAX DELETE FROM TBLNAME; EX. DELETE FROM ACC1; O/P: 5 ROWS DELETED DELETE SPECIFIC ROWS SYNTAX DELETE FROM TBLNAME WHERE CONDITION; EX. DELETE FROM ACC1 WHERE BNAME=KSAD; O/P: 2 ROWS DELETED DBMS-3330703-2017 HKPATEL 26
- 27. UPDATING RECORDS FROM A TABLE UPDATING ALL ROWS SYNTAX UPDATE TBLNAME SET COL1=EXPR1, COL2=EXPR2; EX. UPDATE ACC1 SET BAL=BAL+(BAL*0.10); O/P: 5 ROWS UPDATED DELETE SPECIFIC ROWS SYNTAX UPDATE TBLNAME SET COL1=EXPR1, COL2=EXPR2 WHERE CONDITION; EX. UPDATE ACC1 SET BNAME=KARAMSAD WHERE BNAME=KSAD;; O/P: 2 ROWS UPDATED DBMS-3330703-2017 HKPATEL 27
- 28. Rename Change the name of Table Syntax Rename TBLNAME to NEWTBLNAME Ex. Rename Acc1 to Acc_VVN DBMS-3330703-2017 HKPATEL 28
- 29. Drop Drop the specified Table Syntax Drop table TBLNAME Ex. Drop table Acc_VVN DBMS-3330703-2017 HKPATEL 29
- 30. Truncate Empties given table Syntax Truncate table TBLNAME Ex. truncate table Acc_VVN DBMS-3330703-2017 HKPATEL 30
- 31. Delete V/S Truncate DBMS-3330703-2017 HKPATEL 31 Delete Truncate Through this command removes either all records or a set of rows from table Through this command removes all record from given table Where clause used with delete Where clause not used with truncate Slower compare to Truncate Faster compare to Delete Deleted records can recover using ROLLBACK command Deleted records can not recover Delete from tab_name; OR Delete from tbl_name where colname=expr Truncate table tab_name;
- 32. Altering Table Schema Adding New Row Dropping Columns Modifying Existing Columns DBMS-3330703-2017 HKPATEL 32
- 33. Adding New Row in Existing Table Syntax Alter table TBLNAME ADD( newCol1 datatype(size), newCol2 datatype2(size),); Ex. Alter table Acc_VVN (badd varchar(30)); O/P: Table altered DBMS-3330703-2017 HKPATEL 33
- 34. Dropping Columns in Existing Table Syntax Alter table TBLNAME Drop column ColName; Ex. Alter table Acc_VVN drop badd; O/P: Table altered DBMS-3330703-2017 HKPATEL 34
- 35. Modifying Existing Table Change the data type of a column Change the size of a column syntax Alter table TBLNAME Modify (columnName newdatatype(newSize); Ex. Alter table modify (ano varchar(10)); O/P: Table altered DBMS-3330703-2017 HKPATEL 35