UNIT - II

 

2.1 Introduction to SQL

            The Structured Query Language is often abbreviated with the letters SQL. Some people pronounce it by spelling out the letters, as in “ESS-CUE-ELL”. Others pronounce it as “sequel”. Both pronunciations are fine, and both are used by respected professionals in the industry. SQL is a standard language for accessing and manipulating databases. SQL is a standard database query language. It was developed by IBM Research in the mid 70's and standardized by ANSI in 1986.

2.1.1.  QBE

            What is mean by Query?

            Queries are the primary mechanism for retrieving information from a database and consist of questions presented to the database in a predefined format. Many database management systems use the Structured Query Language (SQL) standard query format.

            QBE is a feature included with various database applications that provides a user-friendly method of running database queries. Typically without QBE, a user must write input commands using correct SQL (Structured Query Language) syntax. This is a standard language that nearly all database programs support. However, if the syntax is slightly incorrect the query may return the wrong results or may not run at all.

            The Query By Example feature provides a simple interface for a user to enter queries. Instead of writing an entire SQL command, the user can just fill in blanks or select items to define the query she wants to perform. For example, a user may want to select an entry from a table called "Table1" with an ID of 123.

            Query by example (QBE) method helps beginners to create SQL queries. This enables users to select items from lists, and handle the syntax details to make it easier to create ad hoc queries. QBE designs are easy to use and save time by minimizing typing.

2.1.2. Tasks of a Query Language

SQL supports the following categories of commands.

Data Definition Language: DDL is a subset of sql statements used for defining the objects in a database.

Create

CREATE TABLE:  The create table command is used to create a new table in the database.

Syntax

CREATE TABLE table_name (column_name datatypes[,....]);

·         table_name is the name of the new table.

·         column_name  is the of the column in the table.

·         datatype is the system defined or user_defined datatype for the column.

Example

CREATE TABLE stu_details(regno varchar2(20), name varchar2(100), DOB date);

alter

      ALTER TABLE: Alter command is used to change structure of a table. This change could be either to modify an existing attribute characteristic or probably to add a new attribute.

Syntax

To Modify existing column in table

alter table <table_name>modify(column definition....);

To Add new column in table

alter table <table_name> add(column definition....);

Example

alter table  stu_details modify (name varchar2(20));

alter table  stu_details add(address varchar2(20));

drop

Drop command is used to delete both the structure and the records stored in a table.

          Syntax

drop table <table_name>;

          Example

drop table stu_details;

truncate

TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax

Truncate table<table_name>;

Example

Truncate table stu_details;

Data Manipulation Language (DML)

DML is a subset of sql statements used to retrieve and manipulate data from the tables.

insert

INSERTcommand is used to add one or more rows to a table. The values are separated by commas and the datatypes are enclosed in single quotes. The values must be entered in the same order as they are defined in the table.