×
Don’t watch the clock; do what it does. Keep going.
--Your friends at LectureNotes
Close

Database Management System

by Being Human
Type: PracticalViews: 58Uploaded: 1 month agoAdd to Favourite

Share it with your friends

Suggested Materials

Leave your Comments

Contributors

Being Human
Being Human
DBMS Lab Manual-2017 15CSL58 INTRODUCTION TO SQL Pronounced as SEQUEL: Structured English QUERY Language  Pure non-procedural query language  Designed and developed by IBM, Implemented by Oracle  1978 System/R IBM- 1st Relational DBMS  1979 Oracle and Ingres  1982 SQL/DS and DB2 IBM  Accepted by both ANSI + ISO as Standard Query Language for any RDBMS  SQL86 (SQL1) : first by ANSI and ratified by ISO (SQL-87), minor revision on 89 (SQL-89)  SQL92 (SQL2) : major revision  SQL99 (SQL3) : add recursive query, trigger, some OO features, and non-scholar type  SQL2003 : XML, Window functions, and sequences (Not free)  Supports all the three sublanguages of DBMS: DDL, DML, DCL  Supports Aggregate functions, String Manipulation functions, Set theory operations, Date Manipulation functions, rich set of operators ( IN, BETWEEN, LIKE, IS NULL, EXISTS)  Supports REPORT writing features and Forms for designing GUI based applications DATA DEFINITION, CONSTRAINTS, AND SCHEMA CHANGES Used to CREATE, ALTER, and DROP the descriptions of the database tables (relations) Data Definition in SQL CREATE, ALTER and DROP table…………………………………….……relation row……………………………………..…….tuple column………………………………….……attribute DATA TYPES  Numeric: NUMBER, NUMBER(s,p), INTEGER, INT, FLOAT, DECIMAL  Character: CHAR(n), VARCHAR(n), VARCHAR2(n), CHAR VARYING(n)  Bit String: BLOB, CLOB  Boolean: true, false, and null Dept. of ISE, CIT, Gubbi. Page 1
DBMS Lab Manual-2017  Date and Time: DATE (YYYY-MM-DD) TIME( HH:MM:SS)  Timestamp: DATE + TIME  USER Defined types 15CSL58 CREATE SCHEMA Specifies a new database schema by giving it a name Ex: CREATE SCHEMA COMPANY AUTHORIZATION Jsmith; CREATE TABLE  Specifies a new base relation by giving it a name, and specifying each of its attributes and their data types Syntax of CREATE Command: CREATE TABLE <table name> ( <Attribute A1> <Data Type D1> [< Constarints>], <Attribute A2> <Data Type D2> [< Constarints>], ……. <Attribute An> <Data Type Dn> [< Constarints>], [<integrity-constraint1>, <integrity-constraint k> ] ); - A constraint NOT NULL may be specified on an attribute A constraint NOT NULL may be specified on an attribute Ex: CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) );  Specifying the unique, primary key attributes, secondary keys, and referential integrity constraints (foreign keys). Ex: CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), Dept. of ISE, CIT, Gubbi. Page 2
DBMS Lab Manual-2017 15CSL58 FOREIGN KEY (MGRSSN) REFERENCES EMP(SSN));  We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys) Ex: CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP ON DELETE SET DEFAULT ON UPDATE CASCADE); DROP TABLE  Used to remove a relation (base table) and its definition.  The relation can no longer be used in queries, updates, or any other commands since its description no longer exists Example: DROP TABLE DEPENDENT; ALTER TABLE:  Used to add an attribute to/from one of the base relations drop constraint -- The new attribute will have NULLs in all the tuples of the relation right after the command is executed; hence, the NOT NULL constraint is not allowed for such an attribute. Example: ALTER TABLE EMPLOYEE ADD JOB VARCHAR2 (12);  The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple. This can be done using the UPDATE command. DROP A COLUMN (AN ATTRIBUTE)  ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE; All constraints and views that reference the column are dropped automatically, along with the column. ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS RESTRICT; Successful if no views or constraints reference the column. ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT; Dept. of ISE, CIT, Gubbi. Page 3
DBMS Lab Manual-2017  15CSL58 ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT ―333445555‖; BASIC QUERIES IN SQL  SQL has one basic statement for retrieving information from a database; the SLELECT statement  This is not the same as the SELECT operation of the relational algebra  Important distinction between SQL and the formal relational model;  SQL allows a table (relation) to have two or more tuples that are identical in all their attribute values  Hence, an SQL relation (table) is a multi-set (sometimes called a bag) of tuples; it is not a set of tuples  SQL relations can be constrained to be sets by using the CREATE UNIQUE INDEX command, or by using the DISTINCT option  Basic form of the SQL SELECT statement is called a mapping of a SELECT-FROMWHERE block SELECT <attribute list> FROM <table list> WHERE <condition>  <attribute list> is a list of attribute names whose values are to be retrieved by the query  <table list > is a list of the relation names required to process the query  <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query SIMPLE SQL QUERIES Basic SQL queries correspond to using the following operations of the relational algebra: SELECT PROJECT JOIN All subsequent examples uses COMPANY database as shown below: Example of a simple query on one relation Query 0: Retrieve the birth date and address of the employee whose name is 'John B. Smith'. Q0: SELECT BDATE, ADDRESS FROM EMPLOYEE Dept. of ISE, CIT, Gubbi. Page 4

Lecture Notes