Introduction to DBMS and SQL

1. Introduction to DBMS and SQL GUIDED BY : MR. YOGESH SAROJ (PGT-CS) Presented By : JAYA XII –COM SEEMA XII-COM.

2. Data Base Management System (DBMS) • Data:- Data means raw fact. • Database :- A collection of meaningful information. Eg. Telephone directory. • Data Base Management System:-The collection of interrelated data containing information about one particular field & set of programs to access those data. • Manage the whole data in perfect manner for following reasons: • User can search information easily • User can insert data easily in order • Updating easily • Deleting information easily • Save data permanently

3. Advantage of DBMS • 1. It reduces data redundancy. • 2. It controls data inconsistency to a large extent. • 3. It restricted unauthorized access. • 4. It facilitates sharing of data. • 5. It provides to describe backup & recovery.

4. We have so many softwares for managing Database: • DB2 • MS Access • Fox Pro • SQL Server • Oracle • My SQL

5. SQL • SQL (Structure Query Language) It is relational database language that enables you to create and operate on relational database.

6. Feature of SQL •  It is a non procedural language. •  It is a 4GL programming language. (i.e only What to do? not How to do?). •  It is a case insensitive language.

7. Constraints of SQL • A constraint is a condition or check that is applied to a column or set of columns in a table. • Null Constraint: It means a Unknown Value. Eg. mobile number(10) null • Not Null Constraint: It means always a Known Value. Eg. name varchar2(20) not null

8. Unique Constraint: It ensures that no two rows have the same value in the specified column(s). i.e Known Value (Distinct) or Unknown Value. Eg. ecode number(5) unique • Primary Key Constraint: It is similar to Unique constraint except that the Primary Key can not allow Null values so that this constraint must be applied to columns declared as Not Null. i.e Always Known Value (Distinct). Eg. empid char(5) primary key

9. Default Constraint: A default value can be specified for a column using default clause when a user does not enter a value for that column. Eg. grade char(2) default= ‘E1’ • Check Constraint: It limits values that can be inserted into a column. Eg. sal number(10) check(sal > 2000)

10. Foreign Key Constraint: Whenever two tables are related by a common column then Foreign Key is present in the Child table (Related Table or Detail Table) and it is derived from primary key of Parent Table (Primary Table or Master Table). • Eg. Two Tables: • Items (Itemno, Description, Price) • Orders (Orderno, Orderdate, Itemno, Qty) • where Itemno & Orderno are Primary Key and Itemno is Foreign Key. i.e both the tables are related through common column Itemno. • Note: It may be possible that Primary Key and Foreign Key are same. • Eg. create table Items • ( Itemno char(5) Primary Key, • …………….); • create table Orders • ( Orderno number(5) Primary Key, • Itemno char(5) references Items(Itemno), • ……………. • );

11. Classification of SQL Commands • DDL Commands • DML Commands • DCL Commands • TCL Commands • Query Language

12. DDL Commands • DDL (Data Definition Language): It provides commands for defining various database objects (i.e defining relation schemas, deleting relations, creating indexes, and modifying relation schemas etc.) • Eg. Create, Alter, Drop etc.

13. Create Command • The tables are created by using CreateTable command and also its columns are named, data types and sizes are supplied for each column. • Syntax:create table <table_name> • ( • <col1> <datatype> [<size>] [<constraint>], • <col2> <datatype> [<size>] [<constraint>], • ……….. • <coln> <datatype> [<size>] [<constraint>] • ); • Eg. create table emp1 • ( • empid char(4) primary key, • ename varchar2(20) not null, • sal number(5) check(sal>2000) • );

15. Alter Command • Altering Table: The alter table command is used to modify the structure of existing table. (i.e adding a column, add an integrity constraint etc.). • Adding Columns: The new column will be added with NULL values for all rows currently in table. • Syntax: alter table <table_name> • add (<col1> <datatype> <size> [<constraint>], • <col2> <datatype> <size> <constraint>] …….); • Eg. alter table emp • add (tel_number number(11) );

16. Alter table • Modifying Column Definitions: To change datatype, size, default value and NOT NULL column constraint of a column definition. • Syntax:alter table <table_name> • modify (<col_name> <new_datatype> <new_size> ); • Eg. alter table emp • modify (tel_number number(13) );

17. Drop table • Drop Table Command: It removes a table from the database . • Syntax: • drop table <table_name>; • Eg. Drop table emp;

18. DML • (Data Manipulation Language): It enables users to manipulate data (i.e commands to insert, delete, and modify tuples in the database). • Eg. Insert, Update, Delete etc.

19. Insert table • Inserting Data into Table • The data can be inserted in a table using Insert Into command. • Syntax: insert into <table_name> [<column_lists>] • values (<value1>, <value2>, …………….); • Eg. insert into emp1 • values(‘E001’,’Vipin’,5000); • Note:Here the order of values matches the order of columns in the create table command of the table. • Or insert into emp1 (empid, ename, sal) • values(‘E001’,’Vipin’,5000); Note:The columns not listed in the insert into command will have their default values or null values.

20. Insert Table • Mass Level Data Insertion • If you want to insert data from user online then you can use insertion through substitution of parameters.(i.e & as substitution operator or place holder). • Syntax:insert into <table_name> • values(‘&col1’,’&col2’,………); • Note: The parameters for character values are enclosed in single quote. • Eg. insert into emp1 • values(‘&empid’,’&ename’,&sal); • It will asked from you to Enter value for empid, Enter value for ename and Enter value for sal. • Then a message display that 1 row created.

21. Update table • Modifying Data with Update Command • This is a DML statement used to modify or change some or all of the values in an existing row of a table. • Syntax: update <table_name> • set col1 = <new_value>, • col2 = < new_value>, • …..coln = <new_value> • [where <condition>]; • Eg. update emp • set sal= 400; ‘updates all rows • Eg. update emp • set sal= sal*2, ename= ‘JONES’ • where empno = 7844; ‘update only one row

22. Delete Command • This is also a DML statement used to remove row(s) of a table. • Syntax: delete from <table_name> • [where <condition>]; • Eg. delete from emp • where sal < 5000;

23. DCL (Data Control Language) Commands • The rights or permissions assigned to user(s) to use some or all of Oracle objects are known as privileges. • Granting Privileges: It is used to assigning permissions to users. (Only DBA can assign) • Syntax: grant <permissions> ‘select, insert, delete, update • on <object_name> • to <username>; • Eg. grant insert on emp to user1; ‘only user1 can insert • grant all on emp to public; ‘assign all permissions to all users. • Revoking Privileges: It get back permissions from the users. • Syntax: revoke <permission> on <object_name> from <username>; • Eg. revoke all on emp from user1; ‘get back all permissions from user1. • revoke select on emp from public; ‘get back select permission from all users.

24. TCL • (Transaction Control Language): It controls over transaction processing by specifying the beginning and ending of transactions. • Eg. Commit, Rollback, Rollback to, Save point etc.

25. TCL Commands • Oracle treat a transaction as a single entity & incase of successful termination of transaction the changes are made permanent. The commands used with transactions are: • COMMIT: It ends the current transaction by saving database changes & starts a new transaction. • Eg. commit; ‘i.e end or start a transaction • ROLLBACK: It ends the current transaction by discarding database changes & starts a new transaction. • Eg. rollback; ‘i.e undo upto commit • SAVEPOINT: It defines breakpoints or bookmarks for the transaction to allow partial rollbacks. • Eg. savepoint P1; • ROLLBACK TO: Its undo up to given bookmark or breakpoint. Eg. rollback to P1;

26. Query Language • Query language : The Select command of SQL make queries on the database i.e it is given to produce certain specified information from the database table(s). • There are various ways and combinations to use a select statement. • The complex syntax of SQL Select command is as given below: • Select <column_list> • From <table_name> • Where <condition> • Group By <list_of_column(s)> • Having <search_condition> ‘Having is dependent upon Group By • Order By <column_name>;

27. Select Command • Select * from emp; • Select 2+3 from dual; • Select empid, ename from emp; • Select * from emp where ename=‘smith’; • Select distinct sal from emp; • Select job from emp where deptno= (select deptno from emp where ename=‘SMITH’);

28. Select Command • Eg. select sum(sal), deptno • from emp • group by deptno; • Output: SUM(SAL) DEPTNO • --------- ------------------- • 8750 10 • 10875 20 9400 30

29. Select Command • Eg. select sum(sal), deptno • from emp • group by deptno • having deptno= 30; • Output: SUM(SAL) DEPTNO • -------- -------------------- • 9400 30

30. Select Command • Eg. select sum(sal), deptno • from emp • group by deptno • order by deptno desc; • Output: SUM(SAL) DEPTNO • --------- -------------------- • 9400 30 • 10875 20 • 8750 10

31. Questions • Ques1. What is difference b/w having clause and where clause in select query? • Ques2. What is difference b/w delete and drop command? • Ques3. Write a query to select second largest salary from emp table?

32. Questions • Ques4. What is the output of following query? select 2+3 from emp; • Ques5. Give three application areas of Database. • Ques 6: What is the difference b/w unique and primary key constraint?

33. Queries?

34. Thanks