Queries under DDL and DML commands

Data Definition Language (DDL)

Syntax for creating a table is as:

CREATE TABLE <table name> (

<column_name1> <data type>[(<width>)]

[constraint <constraint name> <constraint type>],

<column_name2> <data type>[(<width>)],

<column_name3> <data type>[(<width>)],

<column_name4> <data type>[(<width>)],

........................

<column_nameN> <data type>[(<width>)] );

Example

Create table section (

course_id varchar (8),

sec_id varchar (8),

Semester varchar (6),

Year numeric (4,0),

Building varchar (15),

room_number varchar (7),

Time slot id varchar (4),

Primary key (course_id, sec_id, semester, year),

Check (semester in ('Fall', 'Winter', 'Spring', 'Summer'))

Create table person (

ID char (10),

name char (40),

mother char (10),

father char (10),

primary key (ID),

foreign key father references person,

foreign key mother references person )

Create table course (

course_id char (5) primary key,

title varchar (20),

dept_name varchar(20),

foreign key (dept_name) references department,

on delete cascade

on update cascade )

Create table Employee (empno number (4) constraint pk_emp primary key,

ename varchar2(50),

salary number(10,2),

hire_date date,

gender char(1) constraint chk_gen check(gender in ('M', 'F', 'm', 'f')),

email varchar2(50) unique );

Create table Persons ( P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255) DEFAULT 'Sandes' )

Drop and Alter Table Constructs

The drop table command deletes all information about the dropped relation from the database. The alter table command is used to alter attributes/domain/constraint to an existing relation.

To Add attributes in table

Alter table <table_name>

add <attribute_name> <datatype(size)>

Example

Alter table employee

Add salary numeric(5.2)

To drop attributes from table

Alter table <table_name>

Drop column <attribute_name>

To change the data type of a column in a table

Alter table <table_name>

Alter column <column_name> datatype(size)

To modify the length of data type in a table

Alter table <table_name>

Alter column <column_name> datatype(size)

To add NOT NULL constraint

Alter table <table_name>

Alter column <column_name> datatype(size) NOT NULL

Example

Alter table employee

Alter column Emp_Id nvarchar(50) NOT NULL

To Add NULL Constraint

Alter table <table_name>

Alter column <column_name> datatype(size) NULL

To Add Default Constraint

Alter table <table_name>

Add constraint <constraint name> Default <default_value> For <column_name>

To Add constraint in table

Alter table <table_name>

Add constraint <constraint name> <constraint type> <column_name>

Example

Alter table employee

Add constraint pk_emp PRIMARY KEY (Emp_Id)

Alter tabke employee

Add constraint check_salary CHECK (salary>=20000)

Alter table employee

Add constraint fk_emp FOREIGN KEY (dept_num) References Department

Alter table employee

Add constraint unique_emp UNIQUE (email)

To drop constraint from table

Alter table <table_name>

Drop constraint <constraint_name>

Example

Alter table employee

Drop constraint pk_emp

Alter table employee

Drop constraint fk_emp

Alter table employee

Drop constraint check_salary

Alter table employee

Drop constraint unique_emp

To drop a default constraint

Alter table <table_name>

Drop constraint <constraint_name>

Data Manipulation Language (DML)

Select Statement

The SELECT statement is used to select data from a given table. The tabular result is stored in a result table which is also called as a result-set. In order to select all columns from a table, we need to use a *symbol instead of column names. The DISTINCT keyword is used to return only distinct or different values.

Syntax

• SELECT "column_name" FROM "table_name"
• SELECT * FROM "table_name"
• SELECT DISTINCT "column_name" FROM "table_name"

Where

A Where clause can be used or added to the Select statement if we need to select a data from the table conditionally. Then the comparison results can be combined using the logical connectives and, or and not. The following operations can be used with the WHERE clause

Syntax

• SELECT "column_name" FROM "table_name"
  WHERE "condition"
  
  
• SELECT "column_name" FROM "table_name"
  WHERE "simple condition" [AND/OR] "simple condition"

• SELECT "column_name" FROM "table_name"
  WHERE "column_name" BETWEEN 'value1' AND 'value2'

• SELECT "column_name" FROM "table_name"
  WHERE "column_name" IN ('value1', 'value2',....)

String Operations

SQL includes a string-matching operator for comparisons on character strings. The operator "like" uses patterns that are described using two special characters.

• Percent (%): The % character matches any substring.
• Underscore(_): The _ character matches any character.
• Concatenation (using "||")

Syntax:

• SELECT "column_name" FROM "table_name" WHERE "column_name" LIKE {PATTERN}

Ordering tuple

An ordering tuple is used to sort the tuple either in ascending or descending order in the result of the query. It specifies desc for descending order or asc for ascending order for each attribute. Ascending order is the default.

Syntax:

• SELECT "column_name" FROM "table_name" [WHERE "condition"]

For example: If we wish to list the entire Student relation in ascending order of amount and if several students have the same age, then order them in descending order by the first name.

Syntax

• SELECT * FROM Student ORDER BY age asc, first_name desc.

Tuple Variables

Tuple variables are defined in the FROM clause through the use of the as clause.

Find the names of all branches that have greater assets than some branch located in KTM

SELECT DISTINCT T.branch_name FROM branch as T, branch as S

WHERE T.assets> S.assets AND S.branch_city='KTM'

Keyword AS is optional and can be omitted

borrower AS T= borrower T

The Rename Operation

SQL allows renaming the relations and attributes using the AS clause such as old-name as new-name

Find the name, loan number, and loan amount of all customers; rename the column name loan_number as loan_id.

SELECT customer_name, borrower.loan_number AS loan_id, amount

FROM borrower, loan

WHERE borrower.loan_number= loan.loan_number

Set Operations

The set operations that SQL allows to be performed on a table data in a database are union, intersect and except. These set operations operate on relations and corresponds to the relational algebra operations ∪,∩ and −.

Each of these operations automatically eliminates duplicates. In order to retain all duplicates, we need to write union all, intersect all and except all respectively.

Suppose a tuple occurs m times in r and n times in s then it occurs:

• m + n times in r union all s
• min (m,n) times in r intersect all s
• max (0, m-n) times in except all s

Syntax (SQL statement) Set operation (SQL statement)

Aggregate Functions

An aggregate function summarizes the results of an expression over a number of rows, returning a single value. Some of the commonly-used aggregate functions are:

• avg: average value
• min:minimum value
• max: maximum value
• sum: the sum of values
• count:number of values

COUNT (*) is the only function which won't ignore Nulls. Other functions like SUM, AVG, MIN, MAX ignore Nulls. Each subgroup of tuples consists of the set of tuples that have the same value for the grouping attribute(s). Groupby clause used to group a set of tuples having the same value on given attribute. The attribute or attributes given in the group by clause are placed in one group.

Syntax:

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name condition

GROUP BY column_name

The HAVING clause is used for specifying a selection condition on groups rather than on individual tuples. Predicates in the having clause are applied after the formation of groups whereas predicates in the where clause are applied before forming groups.

Syntax

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name condition

GROUP BY column_name

HAVING aggregate_function(column_name) condition

NULL Values

It is possible for tuples to have a null value which is denoted by NULL for some of their attributes. NULL signifies an unknown value or a value that does not exist. The predicate NULL can be used to check for null values. For example:

Find all loan number which appears in the loan relation with null values for the amount.

SELECT loan_number

FROM loan

WHERE amount is NULL

The result of any mathematic expression involving null is null. Example: 5 + null returns null. Any comparison with null returns unknown. Example: 5< null or null<> null or null= null.

Three-valued logic using the truth value unknown:

OR:

(unknown or true) =true,

(unknown or false) =unknown

(unknown or unknown) =unknown

AND:

(true and unknown) =unknown,

(false and unknown) =false,

(unknownand unknown) =unknown

NOT: (not unknown) = unknown

References:

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhatt acharya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.