DBMS Commands DDL DML DCL ENTITY RELATIONSHIP.pptx

DBMS commands

1. DATABASE MANAGEMENT systems LAB

2. DBMS LAB SYLLABUS

3. ex 1: Design a database for enterprise applications with the various Data Models Aim: Enterprise Application : College STUDENT ( USN , SName , Address, Phone, Gender ) SEMSEC ( SSID , Sem, Sec ) CLASS ( USN , SSID ) SUBJECT ( Subcode , Title, Sem, Credits ) IAMARKS ( USN , Subcode , SSID , Test1, Test2, Test3, FinalIA ) Solution: E-R Diagram Schema Diagram

4. Example Banking Enterprise Hospital Management System Library Management system University Enterprise Employee Management System

5. College Enterprise – ER Diagram

6. College Enterprise – Schema Diagram

7. Ex 2 : DDL commands Creating , Modifying, Deleting Relations Using SQL

8. DDL The Data Definition Language (DDL): This subset of SQL supports the creation, deletion, and modification of definitions for tables and views. Create table Alter table ADD Column MODIFY Data type DROP Column RENAME Column Drop Table Rename Table Truncate

9. DDL - CREATE CREATE TABLE: This is used to create a new relation (table) Syntax: CREATE TABLE < relation_name / table_name > (field_1 data_type (size), field_2 data_type (size)); Example: CREATE TABLE Students ( sid NUMBER (3), name CHAR (10), login CHAR (5), age INTEGER, GPA real);

10. DDL - ALTER 1) ADD...: This is used to add some extra fields into existing relation. Syntax: ALTER TABLE relation_name ADD (new field_1 data_type (size), new field_2 data_type (size),..); Example: ALTER TABLE std ADD (Address CHAR(10));

11. DDL - ALTER 2) MODIFY...: This is used to change the width as well as data type of fields of existing relations. Syntax: ALTER TABLE relation_name MODIFY (field_1 newdata_type (Size), field_2 newdata_type (Size),.... field_newdata_type (Size)); Example: ALTER TABLE student MODIFY( sname VARCHAR(10), login VARCHAR(5));

12. DDL - ALTER 3) DROP...: This is used to remove any field of existing relations. Syntax: ALTER TABLE relation_name DROP COLUMN ( field_name ); Example: ALTER TABLE Students DROP column (name);

13. DDL - ALTER 4) RENAME: This is used to change the name of fields in existing relations. Syntax: ALTER TABLE relation_name RENAME COLUMN (OLD field_name ) to (NEW field_name ); Example: ALTER TABLE Students RENAME COLUMN name to sname ;

14. DDL - DROP DROP TABLE: This is used to delete the structure of a relation. It permanently deletes the records in the table. Syntax: DROP TABLE relation_name ; Example: DROP TABLE Students;

15. DDL - TRUNCATE DROP TABLE : This statement in sql causes the table to be deleted from database. Even the name of table under database is deleted. TRUNCATE : This statement in sql deletes all the rows (instances) of the table leaving behind structure of table(schema) and relationship.  The name of table under database and also schema is retained.  Only data is deleted .

16. DDL - TRUNCATE Truncate is a DDL command & delete is a DML command. Syntax: TRUNCATE TABLE <Table name> Example TRUNCATE TABLE Students;

17. DDL- RENAME RENAME: It is used to modify the name of the existing database object. Syntax: RENAME TABLE old_relation_name TO New_relation_name ; Example: RENAME TABLE Students TO Students1;

18. Ex 3: DML commands The Data Manipulation Language (DML): This subset of SQL allows users to pose queries and to insert, delete, and modify rows. INSERT UPDATE DELETE

19. DML – INSERT INSERT INTO: This is used to add records into a relation. These are three type of INSERT INTO queries which are as Inserting a single record Syntax: INSERT INTO < relation/table name> (field_1,field_2…… field_n )VALUES (data_1,data_2,........ data_n ); Example: INSERT INTO Students ( sid,name,login,age,gpa )VALUES (50000,’Dave’,’dave@cs’,19,3.2);

20. Tuples are inserted ,using the INSERT command. We can insert a single tuple into the Students table as follows:

21. Inserting all records from another relation Syntax: INSERT INTO relation_name_1 SELECT Field_1,field_2,field_n FROM relation_name_2 WHERE field_x =data; Example: INSERT INTO Students SELECT sno , sname FROMstudent1 WHERE name = ‘ Ramu ‘;

22. Inserting multiple records Syntax: INSERT INTO relation_name field_1,field_2,..... field_n ) VALUES (&data_1, &data_2,........ & data_n ); Example: INSERT INTO Students ( sid , name, login, age, gpa ) VALUES (& sid , ’&name’, ’&login’, &age, & gpa ); Enter value for sid : 53666 Enter value for name: Jones Enter value for login: jones@cs Enter value for age: 18 Enter value for gpa : 3.2

23. DML - UPDATE UPDATE-SET-WHERE: This is used to update the content of a record in a relation. Syntax: UPDATE relation name SET Field_name1=data, field_name2=data, WHERE field_name =data; Example: UPDATE Students SET name = ‘James’ WHERE sid =53650;

24. We can modify the column values in an existing row using the UPDATE command. For example, we can increment the age and decrement the gpa of the student with sid 53688:

25. DML - DELETE DELETE-FROM : This is used to delete all the records of a relation but it will retain the structure of that relation. 1.) DELETE-FROM : This is used to delete all the records of relation. Syntax: DELETE FROM relation_name ; Example: DELETE FROM Students;

26. DML - DELETE DELETE -FROM-WHERE: This is used to delete a selected record from a relation. Syntax: DELETE FROM relation_name WHERE condition; Example: DELETE FROM Students WHERE sid = 53650;

27. We can delete tuples using the DELETE command. We can delete all Students tuples with name equal to Smith using the command:

28. QUERYING RELATIONAL DATA

29. To Retrieve data from one or more tables. SELECT FROM: To display all fields for all records. Syntax : SELECT * FROM relation_name ; Example : select * from Students;

30. select * from Students;

31. SELECT FROM: To display a set of fields for all records of relation. Syntax: SELECT a set of fields FROM relation_name ; Example: select sid , name, gpa from Students;

32. SELECT - FROM -WHERE: This query is used to display a selected set of fields for a selected set of records of a relation. Syntax: SELECT a set of fields FROM relation_name WHERE condition;   Example: select * FROM Students S WHERE S.age <18;

33. We can also combine information in the Students and Enrolled relations

34. We can also combine information in the Students and Enrolled relations. Output: name cid Smith Topology112