SQL Overview

SQL Overview

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

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: CHARACTER SET, COLLATION, TRANSLATION,  ASSERTION, DOMAIN

 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 WHERE condition

SQL –Relations, Tables & Views

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 Paramount Movie 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 Paramount Movie  WHERE year   =   “1979‘;

Have same result as

SELECT title FROM Movie WHERE studioName   =AND”Paramount‘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

Same result as query from tables

SELECT name FROM Movie, MovieExec WHERE producerC# = cert# AND title = “The the World‘;