RDBMS_Unit3.1

Unit-I		Unit-II	Unit-III	Unit-IV	Unit-V
Part-I	Part-II	Part-I	Part-I	Part-I	Total
Part-III	Part-IV	Part-II	Part-II	Part-II
Part-V		Part-III

 

THE STRUCTURED QUERY LANGUAGE

WHAT IS SQL?

Structured Query Language (SQL) is a standard query language. It is commonly used with all relational databases for data definition and manipulation.

Some of the important features of SQL are:

• It is a non procedural language.

• It is an English-like language.

• It can process a single record as well as sets of records at a time.

• It is different from a third generation language (C& COBOL). All SQL statements define what is to be done rather than how it is to be done.

• It insulates the user from the underlying structure and algorithm.

• SQL has facilities for defining database views, security, integrity constraints, transaction controls, etc.

1.Explain DDL Commands with examples?

DATA DEFINITION LANGUAGE

The Data definition language (DDL) defines a set of commands used in the creation and modification of schema objects such as tables, indexes, views etc. These commands provide the ability to create, alter and drop these objects.

CREATE TABLE Command

Syntax:

CREATE TABLE <table name>(

Column_name1 data type (column width) [constraints],

Column_name2 data type (column width) [constraints], …………….);

Example 1:

CREATE      TABLE      EMP
(     
EMPID   NUMBER, 
ENAME  CHAR(10), 
AGE   INT, 
CITY   CHAR(25),

PHONE_NO   VARCHAR(20),

JOB VARCHAR2(10),

ESAL NUMBER,

DEPT_NO  VARCHAR(10)

);

 

Examples:

CREATE TABLE DEPARTMENT(DEPTNO NUMBER(15) PRIMARY KEY,DNAME VARCHAR2(25),NO_EMP NUMBER(20));

DESC COMMAND

It is used to describe the structure of the table.

Syntax:

DESC <TABLE_NAME>;

DESC  EMP;

 

ALTER TABLE Command:

This command is used for modification of existing structure of the table in the following situation:

• When a new column is to be added to the table structure.

• When the existing column definition has to be changed, i.e., changing the width of the data type or the data type itself.

• When integrity constraints have to be included or dropped.

• When a constraint has to be enabled or disabled. 

Syntax

ALTER TABLE <table name> ADD (<column name> <data type>…);

ALTER TABLE <table name> MODIFY (<column name> <data type>…);

ALTER TABLE <table name> DROP<constraint name>;

ALTER TABLE <table name> ADD CONSTRAINT <constraint name> < constraint type>(field name);

ALTER TABLE <table name> ENABLE/DISABLE <constraint name>; 

Example  

ALTER TABLE Emp MODIFY (empid NUMBER (7));

ALTER TABLE Emp ADD Gender char(1);

 

ALTER   TABLE   Emp   DROP COLUMN Gender;

DROP TABLE Command:

To delete the existing object from the database the following command is used.

Syntax:

DROP TABLE<table name>;

Example :

DROP TABLE emp; 

RENAME COMMAND

• RENAME command is used to rename an object.
• It renames a database table.

 

Syntax:

RENAME <old_name> TO <new_name>;

Example:

RENAME      emp   TO  employee;

2.Explain DML Commands with Examples?

DATA MANIPULATION LANGUAGE

Data manipulation language (DML) defines a set of commands that are used to query and modify data within existing schema objects.

DML statements consist of SELECT, INSERT, UPDATE and DELETE statements.

INSERT    command

 

·         INSERT command is used for inserting a data into a table.

·         Using this command, you can add one or more records to any single table in a database.

·         Syntax

 

INSERT INTO TableName [(columnList)]

VALUES (dataValueList)

 

·         TableName may be either a base table or an updatable view.

·         columnList represents a list of one or more column names separated by commas. The columnList is optional;

·         The dataValueList must match the columnList .

·         The number of items in each list must be the same.

·         The position of items in the two lists must be same, so that the first item in dataValueList applies to the first item in columnList, the second item in dataValueList applies to the second item in columnList, and so on.

·         The data type of each item in dataValueList must be same as the data type of the corresponding column.

 

Example:

INSERT INTO Emp(EmpId, ename, Age, City, Phone_No,Esal,Dept_No,Job) VALUES (1, ‘Ramu’, 20, ‘Hyd’, 945821564, 1512, 30000,’Programmer’);

                             Or

INSERT INTO Emp VALUES (2, ‘Ravi’,‘22',‘Vizag’,9453564164,1513, 35000,’Web Dev’);

                             Or

INSERT INTO Emp(EmpId, ename, Age, City, Phone_No,Esal,Dept_No) VALUES (&EmpId,’&ename’,&Age, &City’,&Phone_No,&Esal, &Dept_No,’&job’);

The value in column EmpId is an integer literal; it is not enclosed in single quotes. All other columns are character strings and are enclosed in single quotes.

 

Practise Tests:

INSERT INTO DEPARTMENT VALUES(1512,'DEVELOPMENT',25);

 

INSERT INTO DEPARTMENT VALUES(1513,'WEB DEV DEPT',10);

 

INSERT INTO DEPARTMENT VALUES(1514,'DB DEPT',8);

 

INSERT INTO DEPARTMENT VALUES(1515,'ANALYSIS  DEPT',10);

UPDATE Command:

The UPDATE statement allows the contents of existing rows in a named table to be changed. The format of the command is:

 

UPDATE TableName

SET columnName1 = dataValue1

[, columnName2 = dataValue2 . . .]

[WHERE searchCondition]

• TableName can be the name of a base table or an updatable view .

• The SET clause specifies the names of one or more columns that are to be updated.

• The WHERE clause is optional; if omitted, the named columns are updated for all rows in the table.

• If a WHERE clause is specified, only those rows that satisfy the searchCondition are updated.

• The new dataValue(s) must be compatible with the data type(s) for the corresponding column(s).

 

Example:

UPDATE all rows

Give all Employees a 3% pay increase.

 

UPDATE EMP

SET Esal = Esal*1.03;

As the update applies to all rows in the Employee table, the WHERE clause is omitted.

UPDATE specific rows

Give all Managers a 5% pay increase.

UPDATE EMP

SET Esal = Esal*1.05

WHERE job = ‘Analyst’;

DELETE Command

The DELETE statement allows rows to be deleted from a named table. The format of the command is:

DELETE FROM TableName

[WHERE searchCondition]

 

• TableName can be the name of a base table or an updatable view.

• The searchCondition is optional; if omitted, all rows are deleted from the table. If a searchCondition is specified, only those rows that satisfy the condition are deleted.

Delete all employees working in department 30;

 

DELETE FROM EMP WHERE DEPTNo=1514;

 

DELETE all rows

Delete all rows from the EMP table.

DELETE FROM EMP;