SQL Default
Whenever a default constraint is applied to the table’s column, and the user has not specified the value to be inserted in it, then the default value which was specified while applying the default constraint will be inserted into that particular column.
The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records if no other value is specified.
Example:
CREATE TABLE TableName (ColumnName1 datatype DEFAULT Value, ColumnName2 datatype,…., ColumnNameN datatype);
The following SQL sets a DEFAULT value for the “City” column when the “Persons” table is created:
CREATE TABLE Persons (
ID int,
LastName varchar(255) ,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT ‘Sandnes’
);
To create a DEFAULT constraint on the “City” column when the table is already created, use the following SQL:
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT ‘Sandnes’ FOR City;
To drop a DEFAULT constraint, use the following SQL:
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;
ALTER TABLE Persons DROP CONSTRAINT df_City;
Course Video
NOTE: Practice below practice questions on MSSQL SERVER, it will not execute on site editor.
1. create table tourist and add 5 columns in it also add language column and set default value English in the column.
2. Create table with 5 column and add default constraint to two columns.
Hint: Default constraint is added on EducationLevel and Position column
3.create a table and add 2 default constraint using alter table.
Hint: Default constraint is added on Department and Position column using alter table
4. create table and add default constraint of user define name
5. Write a sql query to drop above default constrain
6. Develop a SQL script to add a default constraint to the ‘MetalType’ column of the Jewelry table, setting the default value as ‘Gold’. Following this, proceed to insert three records into the table.
7. How can you remove a default constraint from a column?
Hint:-To remove a default constraint, you first need to know its name and then use the ALTER TABLE statement to drop it.
8. What happens when you insert explicit values into columns that have default constraints?
Note:-If you insert explicit values into columns with default constraints, the specified values will override the default values
9. How can you use the DEFAULT keyword in an INSERT statement to insert the default value explicitly
Hint:-You can use the DEFAULT keyword to explicitly insert the default value for a column.
10. Design a SQL script to establish a table named “Restaurant,” incorporating a series of columns each with specified data types and constraints. The table should feature the following structure:
a. A “RestaurantID” column designated as the primary key to ensure unique identification of each record.
b. An “OrderName” column defined with an VARCHAR(50) data type to store the names of orders.
c. A “Location” column, also defined as VARCHAR(50), to indicate the location of the restaurant.
d. A “PricePerTable” column, specified as DECIMAL(10, 2) with a default value of 150.00, to represent the price per table.
e. An “AvailableTables” column, defined as INT with a default value of 10, to denote the number of available tables.
f. An “OpeningDate” column, defined as DATE with a default value of ‘2024-05-18’, to record the opening date of the restaurant.
YouTube Reference :
1) SQL Default in Hindi/Urdu
2) SQL Default in English
