Coupon Accepted Successfully!


Querry Languages (SQL)

SQL operators :
  1. Rename operation : it is used to rename the attributes i.e columns or relations i.e tables using ‘as’ clause.
  2. ALL : This specifies all the tuples and explicitly doesn’t remove the duplicates from the result set.

Basic structure of SQL

It consists of three clauses: select, from and where.

Select corresponds to attributes to be retrieved from the table

From is the relation or data structure to be scanned to get the desired result.

Where clause specifies the condition used while fetching the data from one or multiple tables.

  1. AND &OR : are used normally when there are multiple conditions to be specified in the query.
    Example: select emp_no, emp_name from employees where emp_name = ‘Alex’ and emp_sal> 10000
  2. NOT : The rows get selected where the specified condition is false.
    Example: select emp_name, emp_no from emp where NOT dept = ‘HR’

DCL Commands

  1. ORDER BY clause: This is used to arrange or sort the result set either in ascending or descending orders by using keywords ASC,DESC.
    Example : select emp_id, emp_name from employees where dept= ‘Finance’
    Order by emp_nodesc
    Group by is used with conjunction to group functions to retrieve grouped data according to one or more columns.

DDL commands

The SQL supports the DDL operations are used to create, modify and delete database objects.

  1. CREATE statement
    To make a new database, table, index, or stored procedure.

CREATE TABLE statement

A commonly used CREATE command is the CREATE TABLE command. The typical usage is:

CREATE TABLE [table name] ( [column definitions] ) [table parameters].

column definitions: A comma-separated list consisting of any of the following

  • Column definition: [column name][data type]{NULL | NOT NULL}{column options}
  • Primary key definition: PRIMARY KEY ( [comma separated column list] )
  • Constraints: {CONSTRAINT}[constraint definition]
  • RDBMS specific functionality
  1. DROP statements
    To destroy an existing database, table, index, or view.
    A DROP statement in SQL removes an object from a relational database management system (RDBMS). The typical usage is simply:
    DROP objecttypeobjectname.
  2. ALTER statements
    To modify an existing database object.
    The typical usage is:
    ALTER objecttypeobjectnameparameters.
  3. Truncate
    These command permanently removes the rows of the table from the database and noclauses can be appended to this command.
    Example : Truncate table table-name

DML commands


SQL supports querying, inserting, deleting updating of records in a table. These operations can be performed using DML commands.The DML commands are Insert, update,delete.

  1. Insert : used to add new rows into a table
    Insert into students (s_rollnumber,s_name, addr1, gender, email) values
    (123, ‘amar’,’abc..’, ‘M’,[email protected])
  2. Update : used to update the value of an attribute in a row.
    Update students set email [email protected]
    Where s_rollnumber = 123
  3. Delete : used to delete rows from the table
    Delete from students where s_rollnumber = 123

Nested queries:

A query within a query or a query nested in a larger query is called a subquery. A subquery can be added in SELECT, FROM OR WHEREclauses.

SQL joins :

SQL joins are generally used to combine rows from different tables bassed on a common field among tables. SQL joins can be used in ‘where’ clause of select, update, insert and delete commands.

  1. Inner join or equi join: The query with inner join turns all rows from different tables which satisfies the join condition. It returns the rows when there is atleast one match in both tables.
    Example: selectcust_id, cust_name, order_id,order_date from customer c,orders o
    Where c.cust_id = o.cust_id
  2. Left outer join : It returns all rows from left table and matched rows from right table.
    Example:select cust_id, cust_name, order_id,order_date from customer c left outer join orders o on c.cust_id = o.cust_id
    This returns all rows from customer table and matched rows from order table.
  3. Right outer join : It returns all rows from right table and matching rows from left table.
    Example: select cust_id, cust_name, order_id,order_date from customer c right outer join orders o on c.cust_id = o.cust_id
    This returns all rows from order table and matched rows from customer table.
  4. Full join: This returns all rows from both the tables and fills in nulls for missing matches on either sides.
    Eg: select cust_id, cust_name, order_id,order_date from customer c full outer join orders o on c.cust_id = o.cust_id
    order by o.cust_id

Files and Indexes


The information is represented in the form of records and these records are stored in the memory i.e. in pages. These records are logically organized into files. A file of records is a collection of records that may extend to several pages. Each record has a unique identifier called record id or rid in short and page containing a particular can be accessed by using this rid. A basic file structure called heap file contains records in random order and supports the retrieval of the records by using rid.

The data in a heap file is unordered and the data can be retrieved using the unique identifier of every record in the file and every page in the file is of same size. The operations supported on a heap file are create file, destroy file, insert record, delete record, get a record with a given rid and scan all the records in the file.

The two alternatives to maintain the information is discussed below.

Sequential file organization

Sequential file organization


Multitable clustering

Store several relations in one file using a multitable clustering file organization.



Multitable clustering organization of customer and depositor:


A sequential file organization is better way of processing records in sorted order based on some search key, sometimes this search key may be a primary key or a super key or not. To allow the faster access of records the records are linked together by using pointers. The pointer in each record points to the next key in the sorted order. To reduce the number of accesses to different the records are physically stored as close as possible.

Clustering file organization

A cluster file organization stores the data from two or more relations in a same block. This kind of file organization allows us to read records that would satisfy join condition and data read from only one block. The records in the cluster file organization are chained together using pointers. When clustering is to be used it depends on the query types which are used frequently. This is decided by the database designer. Clustering is very useful in improving performance when used carefully.


An index is an auxiliary data structure which is used to speed up the record search. The indexes are used to gain faster random access to records on a file. There are two kinds of indices,

  1. Ordered indices – this is based on a sorted order of the values. The file containing the records is sequentially ordered based on a primary index which can be a primary key or a super key.
    There are two types of ordered indices,
    Dense index: Every search key value will have an index record in the file. In a dense index, the primary index will contain the search key value and pointer to the first record with the search key value. The rest of the records would be arranged sequentially with the same search value.
    Sparse index: Only some of the search key values will have index records. To access a record, we find the index entry with largest key and starting from that record’s index entry following the pointers the desired record is found.
    B+ tree index files
    B+ tree is efficient disk based structure which stores value of key or value in pair. B+ tree supports faster record search, fast record traversal and maintains sorted tree structure. A B+ tree a tree where the nodes of the tree is equal to pages on the disk. The B+ tree has leaf nodes and interior nodes.
  2. Hash indices- The values are distributed in different buckets, and the bucket to which the value is determined using hash function.
    B tree index files:
    B tree indexes are similar to B+ tree indices. The different between these two indices is that B-tree index eliminates the redundant storage of key values. In B-tree, search key values appear only once, thus B-tree indices uses less space.

Test Your Skills Now!
Take a Quiz now
Reviewer Name