SQL and RDBMS SQL stands for Structured Query

SQL and RDBMS SQL stands for Structured Query Language. A relational database management system (RDBMS) fully supports SQL.

What is SQL Set-oriented means that SQL processes sets of data in groups. Non-procedural means that SQL does not care about how the result is obtained, but it stresses on what result will be obtained. SQL commands can be classified into three types: Data Definition Language (DDL) commands Data Manipulation Language (DML) commands Data Control Language (DCL) commands

DDL Data Definition Language (DDL) commands are used to create, alter and delete tables, views and indexes, including CREATE DATABASE DROP DATABASE CREATE TABLE DROP TABLE ALTER TABLE CREATE INDEX DROP INDEX CREATE VIEW DROP VIEW

DML Data Manipulation Language (DML) commands are used to update, insert, modify and query the data in the database, including • • SELECT INSERT UPDATE DELETE

DML The reasons for writing programs to issue SQL commands are 1. meeting specific needs of an organisation 2. providing interface for users who do not know SQL 3. handling errors 4. enforce database security

DCL Data Control Language (DCL) commands help DBA to control and maintain the database, including GRANT, ALTER USER etc.

Creating a Table The basic syntax for creating a new table is CREATE TABLE Table. Name 1 (Field. Name 1 Field. Type [(Field. Width [, Precision])] [NOT NULL] [PRIMARY KEY] [UNIQUE] [, Field. Name 2. . . ]); • Create table brassitem(instrument char(15, qty numeric (3, 0), price numeric (3, 2), supplier char(20);

Creating a Table with Constraints Some DBMS enforces constraints to insure data integrity. The constraints include 1. the field value is unique (keywords UNIQUE or PRIMARY KEY). 2. the field value is non-empty (keywords NOT NULL or PRIMARY KEY). 3. providing default field value (keyword DEFAULT) 4. generating a number for a numeric field when new record is created (keyword AUTO_INCREMENT) 5. make sure that a foreign key will be updated if the primary key is updated (keyword FOREIGN KEY, CHECK, CASCADE, RESTRICT, ON DELETE and ON UPDATE)

Filling Table with Data The basic syntax for inserting a new record is: INSERT INTO Table. Name [(Field. Name 1 [, Field. Name 2. . . ])] VALUES (Expression 1 [, Expression 2. . . ]); • Insert into brassitem values(‘Trombone’, 3, 600. 00, ‘Grace musicals’);

Other DDL Commands • CREATE INDEX Index. Name ON Table. Name (Field. Name [ASC|DESC], . . . ); • ALTER TABLE Table. Name 1 ADD | ALTER (MODIFY|CHANGE) | DROP [COLUMN] Field. Name 1 Field. Type [(Field. Width [, Precision])]; • MODIFY COLUMN is used to change the size and data type of a field. CHANGE COLUMN is used to change a field name.

• The SQL command to delete a table: DROP TABLE Table. Name; • The SQL command to delete database: DROP DATABASE DBName;