dbms notes

INDEX
1. SQL BASICS

The Structured Query Language (SQL) is a language that enables you to create and operate on relational databases, which are sets of related information stored in tables.

The basic structure of a SQL expression consists of three clauses:
Select
From
Where

SELECT:- The select clause is used to list the attributes desired in the result of a query. It corresponds to the projection operation of the relational algebra.

FROM:- The from clause lists the relation to be scanned in the evaluation of the expression. It correspond to the Cartesian product operation of the relational algebra.

WHERE:- The where clause consists of a predicate involving attributes of the relations that appear in the from clause.

A TYPICAL SQL QUERY HAS THE FORM:- Select A1,A2………,An from r1,r2………...,rm where P

Here, Each Ai represents to an attribute. Each ri represents a relation. P is the predicate. 2. PROGRAM TO CREATE TABLE

SYNTAX:
CREATE TABLE
( (), ());

Create the table described below:
Table Name: CLIENT_MASTER

COLUMN NAME
DATA TYPE
SIZE
CLIENTNO
VARCHAR2
6
NAME
VARCHAR2
20
CITY
VARCHAR2
15
PINCODE
NUMBER
8
STATE
VARCHAR2
15
BALANCEDUE
NUMBER
10

SQL> CREATE TABLE CLIENT_MASTER 2 ( CLIENTNO VARCHAR2(6), 3 NAME VARCHAR2(20), 4 CITY VARCHAR2(15), 5 PINCODE NUMBER(6), 6 STATE VARCHAR2(10), 7 BALANCEDUE NUMBER(10));

Table created.

SQL> DESC CLIENT_MASTER; Name Null? Type ----------------------------------------- -------- ---------------------------- CLIENTNO VARCHAR2(6) NAME VARCHAR2(20) CITY VARCHAR2(15) PINCODE NUMBER(6) STATE

References: SQL> ALTER TABLE EMP 2 ADD CONSTRAINT S SALARY CHECK (SALARY >1000);