We will be using below Students table to explain the

Example Queries:-

If there are a large number of tuples satisfying the query conditions, it might be
resourceful to view only a handful of them at a time.

• The LIMIT clause is used to set an upper limit on the number of tuples
  returned by SQL.
• It is important to note that this clause is not supported by all SQL versions.
• The LIMIT clause can also be specfied using the SQL 2008 OFFSET/FETCH
  FIRST clauses.
• The limit/offset expressions must be a non-negative integer.

Example Queries to demonstrate LIMIT Clause.

• SELECT *
  FROM Students
  LIMIT 3;

Output of the query gives First three Student Records.

• SELECT *
  FROM Students
  ORDER BY age
  LIMIT 3;

Output of the query gives three Student Records in order of Ascending Ages.

The LIMIT operator can be used in situations such as the above, where we need to
find the top N students in a class and based on any condition statements.

Using LIMIT along with OFFSET

LIMIT x OFFSET y simply means skip the first y entries and then return the next x
entries.
OFFSET can only be used with the ORDER BY clause. It cannot be used on its own.
OFFSET value must be greater than or equal to zero. It cannot be negative, else
returns error.
Example query to demonstrate LIMIT and OFFSET.

• SELECT *
  FROM Students
  LIMIT 3 OFFSET 2
  ORDER BY roll_no;

Returns 3 Student Records skipping first two records in Table.

ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also
used to add and drop various constraints on the existing table.

ALTER TABLE – ADD

ADD is used to add columns into the existing table. Sometimes we may require to add
additional information, in that case we do not require to create the whole database
again, ADD comes to our rescue.

Syntax:

• ALTER TABLE table_name
  ADD (Columnname_1 datatype,
  Columnname_2 datatype,
  Columnname_n datatype);

ALTER TABLE – DROP

DROP COLUMN is used to drop column in a table. Deleting the unwanted columns from
the table.

Syntax:

• ALTER TABLE table_name
  DROP COLUMN column_name;

ALTER TABLE-MODIFY

It is used to modify the existing columns in a table. Multiple columns can also be modified
at once.
Syntax may vary slightly in different databases. Syntax(Oracle,MySQL,MariaDB):

• ALTER TABLE table_name
  MODIFY column_name column_type;

Syntax(SQL Server):

• ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

Queries
Sample Table:

Student

• ROLL_NONAME
  1 Ram
  2 Abhi
  3 Rahul
  4 Tanu

QUERY:•

To ADD 2 columns AGE and COURSE to table Student

• ALTER TABLE Student ADD (AGE number(3),COURSE varchar(40));

OUTPUT:

• ROLL_NONAMEAGECOURSE
  1 Ram
  2 Abhi
  3 Rahul
  4 Tanu

• MODIFY column COURSE in table Student

• ALTER TABLE Student MODIFY COURSE varchar(20);

After running the above query maximum size of Course Column is reduced to 20
from 40.

• DROP column COURSE in table Student.

ALTER TABLE Student DROP COLUMN COURSE;

• OUTPUT:
  ROLL_NONAMEAGE
  1 Ram
  2 Abhi
  3 Rahul
  4 Tanu