• Document: Database Administration Lab
  • Size: 3 MB
  • Uploaded: 2019-05-17 21:54:56
  • Status: Successfully converted


Some snippets from your converted document:

Channabasaveshwara Institute of Technology (Affiliated to VTU, Belgaum & Approved by AICTE, New Delhi) (NAAC Accredited & ISO 9001:2015 Certified Institution) NH 206 (B.H. Road), Gubbi, Tumkur – 572 216. Karnataka. Department of Information Science &Engineering Database Administration Lab Subject Code: 15CSL58 B.E - V Semester Lab Manual 2018-19 DBMS Lab Manual-2018 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-2018 15CSL58  Date and Time: DATE (YYYY-MM-DD) TIME( HH:MM:SS)  Timestamp: DATE + TIME  USER Defined types 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-2018 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

Recently converted files (publicly available):