Home | | Database Management Systems | SQL Overview

Chapter: Database Management Systems : SQL & Query Optimization

SQL Overview

Structured Query Language. The standard for relational database management systems (RDBMS)

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‘;

 


Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail
Database Management Systems : SQL & Query Optimization : SQL Overview |


Privacy Policy, Terms and Conditions, DMCA Policy and Compliant

Copyright © 2018-2023 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.