--Your friends at LectureNotes

Database Management System

by Anna SuperkingsAnna Superkings
Type: NoteInstitute: ANNA UNIVERISTY Specialization: Information Technology EngineeringViews: 52Uploaded: 4 months ago

Share it with your friends

Suggested Materials

Leave your Comments


Anna Superkings
Anna Superkings
SQL Overview course material (Lecture Notes) Structured Query Language The standard for relational database management systems (RDBMS) SQL-92 and SQL-99 Standards – Purpose: Specify syntax/semantics for data definition and manipulation Define data structures Enable portability Specify minimal (level 1) and complete (level 2) standards Allow for later growth/enhancement to standard Catalog A set of schemas that constitute the description of a database Schema The structure that contains descriptions of objects created by a user (base tables, views, constraints) Data Definition Language (DDL) Commands that define a database, including creating, altering, and dropping tables and establishing constraints Data Manipulation Language (DML) Commands that maintain and query a database Data Control Language (DCL) Commands that control a database, including administering privileges and committing data CS6302 DATABASE MANAGEMENT SYSTEMS UNIT-2
course material (Lecture Notes) SQL Database Definition Data Definition Language (DDL) Major CREATE statements: CREATE SCHEMA – defines a portion of the database owned by a particular user CREATE TABLE – defines a table and its columns CREATE VIEW – defines a logical table from one or more views Other CREATE statements: ASSERTION, DOMAIN CHARACTER SET, COLLATION, TRANSLATION, In SQL, a VIEW is a virtual relation based on the result-set of a SELECT statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. In some cases, we can modify a view and present the data as if the data were coming from a single table. Syntax: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name condition WHERE SQL – Relations, Tables & Views CS6302 DATABASE MANAGEMENT SYSTEMS UNIT-2
course material (Lecture Notes) When we say Relation, it could be a Table or a View. There are three kind of relations: 1. Stored relations tables We sometimes use the term ―base relation‖ or ―base table‖ 1. Virtual relations views 2. Temporary results SQL – Create View Example: Create a view with title and year and made by Paramount studio. Movie (title, year, length, inColor, studioName, producerC#) CREATE VIEW ParamountMovie AS SELECT title,year FROM Movie WHERE studioName = ‗Paramount‘; SQL – Querying View A view could be used from inside a query, a stored procedure, or from inside another view. By adding functions, joins, etc., to a view, it allows us to present exactly the data we want to the user. SELECT title FROM ParamountMovie WHERE year = ‗1979‘; Have same result as SELECT title FROM Movie WHERE studioName = ‗Paramount‘ AND year = ‗1979‘; Example: Movie (title, year, length, inColor, studioName, producerC#) MovieExec (name, address, cert#, netWorth) CREATE VIEW MovieProd AS SELECT title, name FROM Movie, MovieExec WHERE producerC# = cert#; SELECT name FROM MovieProd WHERE title = ‗Gone With the Wind‘; Same result as query from tables SELECT name FROM Movie, MovieExec WHERE producerC# = cert# AND title = ‗The War Of the World‘; Data Types in SQL CS6302 DATABASE MANAGEMENT SYSTEMS UNIT-2
course material (Lecture Notes) String types CHAR(n) – fixed-length character data, n characters long Maximum length = 2000 bytes VARCHAR2(n) – variable length character data, maximum 4000 bytes LONG – variable-length character data, up to 4GB. Maximum 1 per table Numeric types NUMBER(p,q) – general purpose numeric data type INTEGER(p) – signed integer, p digits wide FLOAT(p) – floating point in scientific notation with p binary digits precision Date/time type DATE – fixed-length date/time in dd-mm-yy form CS6302 DATABASE MANAGEMENT SYSTEMS UNIT-2

Lecture Notes