SQL
interview query requested for Junior degree programming
job interviews. Although syntax of SQL question so as to add a column with default worth
varies just a little bit from database to database, it has all the time been carried out utilizing
ALTER key phrase of ANSI SQL. Including columns within the current desk within the MySQL database
is fairly simple and straight ahead and we are going to see an instance of SQL question for MySQL database which provides a column with a default worth. You may as well present
constraints like NULL or NOT NULL whereas
including a brand new column within the desk.
On this SQL tutorial, we’re including the third column in a desk known as Contacts which
accommodates identify and telephone of
contacts. Now we need to add one other column e mail with the default worth “abc@yahoo.com”.
We are going to
use ALTER command in SQL to try this. By the best way that is subsequent in our SQL
tutorials like How
to affix three tables in SQL and SQL
question to seek out duplicate information in desk. In case you haven’t learn them but,
then chances are you’ll discover them helpful.
Add, Modify and Drop Column in MySQL desk with ALTER key phrase
On this SQL question instance we are going to see :2) add column in a MySQL desk with NOT NULL constraints
SELECT *
FROM Contacts;
+——-+———-+
| identify |
telephone |
+——-+———-+
| James |
80983243 |
| Johny |
67543212 |
| Harry |
12341234 |
| Ron |
44446666 |
+——-+———-+
4 rows IN
SET (0.00 sec)
mysql> ALTER
TABLE contacts ADD COLUMN
e mail varchar(20) DEFAULT
“abc@yahoo.com”
-> ;
Question OK, 4
rows affected (0.20
sec)
Data: 4 Duplicates: 0 Warnings: 0
mysql> SELECT
* FROM
Contacts;
+——-+———-+—————+
| identify |
telephone | e mail
|
+——-+———-+—————+
| James |
80983243 |
abc@yahoo.com |
| Johny |
67543212 |
abc@yahoo.com |
| Harry |
12341234 |
abc@yahoo.com |
| Ron |
44446666 |
abc@yahoo.com |
+——-+———-+—————+
4 rows IN
SET (0.00 sec)
SQL
question to drop column in MySQL desk
You may as well take away a column within the current desk through the use of alter desk drop
column SQL question as proven within the beneath instance:
ALTER TABLE
Contacts DROP COLUMN e mail;
Question OK, 4
rows affected (0.27
sec)
Data: 4 Duplicates: 0 Warnings: 0
mysql> SELECT
* FROM
Contacts;
+——-+———-+
| identify |
telephone |
+——-+———-+
| James |
80983243 |
| Johny |
67543212 |
| Harry |
12341234 |
| Ron |
44446666 |
+——-+———-+
4 rows IN
SET (0.00 sec)
SQL
question so as to add NOT NULL constraints to a column in MySQL desk
Now we are going to see SQL question so as to add one other column in an current desk with NOT
NULL constraints. Once you add column with NOT NULL constraints and with out default worth then
there worth shall be empty.
ALTER TABLE
contacts ADD COLUMN e mail varchar(20) NOT NULL;
Question OK, 18
rows affected (0.22
sec)
Data: 18 Duplicates: 0 Warnings: 0
mysql> SELECT
* FROM
Contacts;
+——-+———-+——-+
| identify |
telephone | e mail |
+——-+———-+——-+
| James |
80983243 |
|
| Johny |
67543212 |
|
| Harry |
12341234 |
|
| Ron |
44446666 |
|
+——-+———-+——-+
4 rows IN
SET (0.00 sec)
mysql> INSERT
INTO Contacts VALUES (“Ruby”, 12345678, NULL);
ERROR 1048 (23000): COLUMN ‘e mail’
can’t be NULL
Now you’ll be able to see that the e-mail column shouldn’t be accepting null values as a result of
It is created with NOT NULL constraints.
We now have seen MySQL database examples however examples are generic and will work on different databases as properly e.g. Oracle,
SQL
Server and Sybase.
Successfully utilizing NULL and NOT NULL
constraints can considerably enhance the code high quality of each database and Server.
By fastidiously making use of constraints like NULL and NOT
NULL you’ll be able to successfully validate every inserted report within the desk.
Associated Database and SQL tutorials from Javarevisited Weblog
