How To Purpose Alteration Ascendance Inward Sql - Database Tutorial

In this SQL tutorial  we volition run across how to utilisation ALTER ascendency command inwards tabular array on database. ALTER ascendency is mainly used to add, modify as well as driblet columns, indexes as well as constraints on tabular array inwards relational database e.g. MySQL, Oracle, Sybase as well as SQL Server.  Though ALTER is non portion of classical CRUD performance only it’s ane of the of import DDL command. One of the most frequent utilisation of ALTER ascendency inwards SQL is adding as well as removing indexes to amend performance of SQL SELECT queries. In this SQL as well as database tutorial nosotros volition run across unlike ways of using ALTER command. By using ALTER ascendency nosotros tin add, edit or modify table, views as well as databases. We tin add together novel column on the table, nosotros tin alter value of column or nosotros tin rename column also.

10 ALTER ascendency Example inwards SQL

Let’s run across around of the most oft used instance of ALTER ascendency inwards SQL. In this department nosotros volition run across How to utilisation ALTER ascendency to add, modify as well as driblet existing columns inwards table.

Example 1 : How to add together column inwards existing tabular array using ALTER clause inwards SQL:

Suppose nosotros convey ane tabular array called pupil alongside champaign roll_number, stu_name, class, subject as well as nosotros desire to add together ane to a greater extent than column called marks-obtained, how volition produce using ALTER Command.

ALTER TABLE Student ADD marks_obtained Number (3);

When this champaign is added to the tabular array it volition comprise NULL Value yesteryear default.

Example two :  How to modify existing column using ALTER ascendency inwards SQL:

Suppose straight off nosotros desire to modify inwards a higher house added champaign mark_obtained from divulge to decimal information type nosotros tin utilisation modify column along alongside alter command.

ALTER TABLE Student ALTER COLUMN marks_obtained DECIMAL (5, 2);

Example three :  How to driblet existing column from tabular array using ALTER ascendency inwards SQL:

Now if nosotros desire to driblet around existing column from the tabular array nosotros utilisation driblet along alongside ALTER ascendency

ALTER TABLE Student DROP column marks_obtained;

Example 4. How to driblet Primary Key Constraint using ALTER ascendency inwards database :

Suppose at that topographic point is a PRIMARY KEY CONSTRAINT named 'pk_roll_num' for the column roll_number of the Student tabular array as well as nosotros desire to driblet this constraint

ALTER TABLE Student DROP CONSTRAINT 'pk_roll_num’;

Example 5. How to add together primary key constraints using ALTER ascendency inwards SQL:

Now if nosotros desire to add together primary key constraint inwards tabular array Student named 'pk_roll_num' for the column roll_number' . We produce similar this

ALTER TABLE Student ADD Constraint 'pk_roll_num PRIMARY KEY(roll_number)

Example 6. How to driblet unusual key Constraint using Alter command:

Suppose at that topographic point is a unusual key constraint named 'fk_roll_num' for the column roll_number' of the 'Marks' table as well as nosotros desire to driblet this constraint

ALTER TABLE Marks DROP CONSTRAINT 'fk_roll_num’;

Example seven : How to add together unusual key Constraints using alter command

Now if nosotros desire to add together foreign key constraint inwards tabular array Marks named 'fk_roll_num' for the column roll_number' .we produce similar this

ALTER TABLE Marks ADD Constraint 'fk_roll_num FOREIGN KEY (roll_number) REFERENCES Students (roll_number);

Example 8 : How to add together unique key Constraints using ALTER command

Now if nosotros desire to add together Unique key constraint inwards tabular array Student named ‘unique_roll_no ' for the column roll_number' .we produce similar this

ALTER TABLE Student ADD CONSTRAINT unique_roll_no UNIQUE (roll_Number);

Example 9 : How to driblet unique Key Constraint using ALTER command

Now if nosotros desire to driblet unique key constraint inwards tabular array Student named ‘unique_roll_no ' for the column roll_number' .we produce similar this

ALTER TABLE Student DROP CONSTRAINT unique_roll_no);

Example 10 : How to add together depository fiscal establishment fit Constraint using ALTER ascendency inwards SQL

Basic syntax for adding depository fiscal establishment fit constraint inwards tabular array is similar this

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

This is all almost ALTER ascendency examples inwards SQL. We convey seen 10 unlike ways of using ALTER ascendency inwards SQL. ALTER is ane of the most of import patch working inwards whatsoever database e.g. MySQL, Oracle or SQL Server, peculiarly from ascendency line. Since nosotros often require to add, modify as well as driblet columns, indexes as well as constraints inwards table.

Further Learning
Difference betwixt clustered as well as non-clustered index inwards SQL

Difference betwixt View as well as materialized stance inwards SQL

Difference betwixt candidate key as well as primary key inwards SQL

Difference betwixt correlated as well as noncorrelated subquery inwards database

When to utilisation truncate vs delete inwards database

Difference Betwixt Self Too Equi Bring Together Inwards Sql - Inner Bring Together Event Mysql

The principal deviation betwixt Self Join together with Equi Join is that In Self Join nosotros bring together ane tabular array to itself rather than joining 2 tables. Both Self Join together with Equi Join are types of INNER Join inwards SQL, but at that spot is the subtle deviation betwixt two. Any INNER Join amongst equal every bit bring together predicate is known every bit Equi Join. SQL Joins are the cardinal concept of SQL similar to correlated together with noncorrelated subqueries or using grouping yesteryear clause together with a proficient agreement of diverse types of SQL bring together is the must for whatever programmer. By the way, If yous convey written INNER bring together using where clause than using comparing operator every bit = volition hold upwards known every bit an equijoin. Equi bring together or Self bring together are non a formal JOIN or component division of the syntax, instead, they are a but pop means to refer sure enough bring together examples.

One of the best instance of Self Join, I convey seen inwards an SQL query Interview questions is "How produce yous notice all Employees who are Managers inwards Employee table", which is usually asked along amongst or together with then other pop inquiry how to notice minute highest salary of employee or questions related to join iii tables inwards ane SQL query.

In this SQL tutorial, nosotros volition larn self-join yesteryear instance piece solving this SQL query.

Self Join Example inwards SQL

In gild to solve this query let's starting fourth dimension run across schema together with information of Employee table.

mysql> select * from employee;
+--------+----------+---------+--------+--------+
| emp_id | emp_name | dept_id | salary | mgr_id |
+--------+----------+---------+--------+--------+
|    103 | Jack     |       2 |   1400 |    104 |
|    104 | John     |       2 |   1450 |    104 |
|    105 | Johnny   |       3 |   1050 |    104 |
|    108 | Alan     |       3 |   1150 |    104 |
|    106 | Virat    |       4 |    850 |    105 |
|    107 | Vina     |       4 |    700 |    105 |
|    109 | joya     |       4 |    700 |    105 |
+--------+----------+---------+--------+--------+
7 rows in set (0.00 sec)

In above  table all employees who are managers has at that spot emp_id every bit mgr_id inwards other employees together with yesteryear using SELF JOIN  i.e. bring together 2 instances of employee tabular array together with comparing, nosotros tin notice all employees who are managers. Here is the SELECT query example using self-join:

mysql> select distinct e.emp_id, e.emp_name from employee e join employee one thousand on e.emp_id=m.mgr_id;
+--------+----------+
| emp_id | emp_name |
+--------+----------+
|    104 | John     |
|    105 | Johnny   |
+--------+----------+
2 rows in set (0.00 sec)

In this instance of self join, nosotros convey joined employee tabular array to itself yesteryear using 2 tabular array aliases e together with m. We convey likewise used distinct keyword to take away duplicates here. You tin likewise tell this is an instance of EQUI JOIN because inwards bring together predicate nosotros convey used = or equal condition. In fact, this ane is an instance of INNER Join, SELF Join, together with EQUI Join at the same time.

If yous are confused near syntax of all possible bring together inwards SQL, hither is a handy diagram:

Self Join vs Equi Join

In the curt major deviation betwixt Self Join together with Equi Join inwards SQL is that Self Join requires solely ane tabular array piece most of Equi bring together is status used inwards bring together predicate. Since Equi Join is based on the status for comparison, it tin hap inwards whatever INNER, OUTER or SELF bring together inwards SQL.

That’s all on the difference betwixt Self Join together with Equi Join inwards SQL. Self Join is ane of the of import technique to solve many SQL query related employment where 2 columns of the tabular array comprise the same type of information e.g. hither emp_id together with dept_id are essentially same data.

Further Learning
Difference betwixt primary key together with candidate key inwards tabular array or SQL

Difference betwixt View together with Materialized persuasion inwards Database or SQL

What is deviation betwixt clustered together with nonclustered index inwards SQL

10 ALTER ascendence instance inwards SQL

10 Oracle Interview Questions for SQL developer

10 SQL Query Interview Questions from Java Interviews

P.S. - If yous are looking for online training/course to larn SQL from scratch, I propose yous joining Introduction to SQL yesteryear Jon Flanders. It's ane of the best sourse to larn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, gropuing data, advanced filtering together with SQL query optimization.