Introduction to Database Session 2.pptx

Introduction to Database P R O F K A L LO L D A S Session 2 INTRODUCTION TO SQL Basic Features Donald D. Chamberlin and Raymond F. Boyce developed the first version of SQL Basic features of SQL: This is primarily used to create, retrieve, and modify data regardless of the system they are worki...

Introduction to Database P R O F K A L LO L D A S Session 2 INTRODUCTION TO SQL Basic Features Donald D. Chamberlin and Raymond F. Boyce developed the first version of SQL Basic features of SQL: This is primarily used to create, retrieve, and modify data regardless of the system they are working on. It has two major categories:  Data definition language (DDL): Provides commands that can be used to create, modify, and delete database objects. The database objects include relation schemas, relations, views, sequences, catalogs, indexes, etc.  Data manipulation languages (DML): Provides commands that can be used to access and manipulate the data, that is, to retrieve, insert, delete, and update data in a database.  In addition, there are various techniques like embedded SQL, cursors, dynamic SQL, ODBC, JDBC, SQLJ, which can be used for accessing databases from other applications. Components of SQL  DDL: Data Definition Language – Creating Tables, Relationships, Views  DML: Data Manipulation Language - Querying, Adding, Modifying and Deleting Data  SQL/PSM : SQL/Persistent Storage Modules – Extends SQL by adding procedural programming capabilities (SQL is non-procedural language, It is 4GL), such as variables, flow of control, within SQL framework. This gives 3GL power to SQL  TCL: Transactional Control Language – Creating transaction scope  DCL: Data Control Language – Grant permission on various operations (e.g., Querying or Deleting, etc.) on various data DDL DDL commands are used for defining relation schemas, deleting relations, creating indexes, and modifying relation schemas.  Concept of Schema and Catalog in SQL:  Earlier there was no concept of relational database schema.  Thus, all the database objects were considered to be a part of same database schema.  Hence no two objects can share the same name.  Thus all users of the database have to coordinate with each other to ensure that they use different names for database objects.  To deal with problem, the present day database systems provide a three level hierarchy for naming different objects of relational database. This hierarchy comprises catalogs, which in turn consists of schemas, and various database objects (relations, Integrity constraints like referential integrity, views, domains etc) are incorporated within the schema.  Database system can have multiple catalogs, and users working with different catalogs can use the same name for database objects without any clashes.  Each user of database system is associated with a default catalog and schema.  Whenever the user connects with the database (by providing the unique combination of user name and password), he gets connected with the default catalog and schema. Data Types The common data types supported by SQL are → Numeric(p,s): In some cases decimal(p,s) is also used → Integer or INT: Represent data as a number without decimal points. → SMALLINT : represent data as a number without decimal points. The default size is usually smaller than INT → VARCHAR(n)/CHAR: Represent data as variable length string of characters of maximum size n. → DATE and TIME: Represent data as date or time. → BOOLEAN: → TIMESTAMP: Represent data consisting of both date and time. → Other Data Types supported by SQL: CLOB (CHARACTER LARGE OBJECT), BLOB (BINARY LARGE OBJECT) → User defined data type: The user-defined data type can be created using the CREATE DOMAIN command. CREATE DOMAIN Vchar AS VARCHAR(15); Now, Vchar can be used as data type for any attribute for which data type VARCHAR(15) is to be defined. Create Command  The CREATE TABLE command is used to define a new relation, its attributes and its data types. In addition, various constraints like key, entity integrity, and referential integrity constraints can also be specified.  Specifying Constraints: Constraints are required to maintain the integrity of the data, which ensures that the data in database is consistent, correct, and valid. Different types of constraints are:  PRIMARY KEY Constraint: The values in the primary key attribute are not null and are unique.  UNIQUE Constraint: The UNIQUE constraint ensures that the set of attributes have unique values, that is, no two tuples can have same value in the specified attributes. When UNIQUE constraint is applied on more than one attribute it is specified as table-level constraint.  CHECK Constraint: This constraint ascertains that the value inserted in an attribute must satisfy a given expression.  NOT NULL Constraint: The NOT NULL constraint is used to specify that an attribute will not accept null values.  FOREIGN KEY Constraint: This constraint ensures that the foreign key value in the referencing relation must exist in the primary key attribute of the referenced relation, that is, foreign key references the primary key attribute of referenced relation.  When FOREIGN KEY constraint is applied on more than one attribute it is specified as table level constraint. Alter Command  ALTER TABLE command is used for the following requirements. ► To add an attribute: ALTER TABLE BOOK ADD Pname VARCHAR(10) ► To modify the definition of an attribute: ALTER TABLE BOOK MODIFY Pname VARCHAR(50); ► To drop an attribute: ALTER TABLE BOOK DROP COLUMN Pname; ► To add a constraint: ► ALTER TABLE BOOK ADD CHECK (Book_title ‘’); ► ALTER TABLE BOOK ADD CONSTRAINT Cons_1 UNIQUE (ISBN); ► ALTER TABLE BOOK ADD FOREIGN KEY (P_ID) REFERENCES PUBLISHER; ► To drop a constraint: ALTER TABLE BOOK DROP CONSTRAINT Cons_1; ► To rename attribute and relation: ► ALTER TABLE BOOK RENAME COLUMN Page_count TO P_count; ► ALTER TABLE BOOK RENAME TO Book_detail; DROP TABLE Command  The DROP TABLE command is used to remove an already existing relation. ► The two clauses that can be used with DROP TABLE command are CASCADE and RESTRICT. ► If CASCADE clause is used, all constraints and views that reference the relation to be removed are also dropped automatically. ► RESTRICT clause, prevents a relation to be dropped if it is referenced by any of the constraints or views. ► DROP TABLE Book_detail CASCADE; ► DROP TABLE Book_detail RESTRICT; DML  Basically it comprises of different actions, ► Retrieval of data stored in the database: SELECT Command ► Select command: SELECT ISBN, Book_title, Category FROM BOOK; ► Where clause: SELECT Book_title, Category, Price FROM BOOK WHERE Category = ‘Novel’; ► BETWEEN Operator: SELECT * FROM BOOK WHERE Price BETWEEN 20 AND 30; ► IN operator: SELECT * FROM BOOK WHERE Category IN (‘Textbook’, ‘Novel’); ► Insertion of data into the database: INSERT Command ► INSERT INTO BOOK (ISBN, Book_title, Category, Price, Copyright_date, Year, Page_count, P_ID) VALUES (‘001-987-760-9’, ‘C++’, ‘Textbook’, 40, 2004, 2005, 800, ‘P001’); ► Modification of data stored in the database: UPDATE Command ► UPDATE PUBLISHER SET State = ‘Georgia’, Phone = ‘27643676’ WHERE Pname = ‘Bright Publications’; ► Deletion of data stored in the database: DELETE Command ► DELETE FROM BOOK WHERE Page_count