SQL/RDBMS is one of the most widely used databases to date, and therefore SQL skills are important in most work roles.
In this SQL interview questions article, I will share with you some of the most frequently asked questions on SQL (Structured Query Language). This article is the perfect guide for you to learn all concepts related to SQL, Oracle, MS SQL Server, and MySQL databases. In this topic, all the examples are related to SQL Server.
I am keeping 10 questions and answers in each part, So Please read all 10 parts of this SQL Interview Questions article and get ready to crack the SQL interview for beginners or experience level.
SQL Interview Questions & Answer – Best TOP 10 (Day – 1)
SQL Interview Questions & Answer – Best TOP 10 (Day – 2)
Contents
What are constraints in SQL server?
- The constraint is a rule or restriction imposed on an individual column such that unwanted data can’t be inserted into tables.
- We can create constraints on single or multiple columns of any table.
- Constraints maintain the data integrity and accuracy in the table.
- Constraints can be column level or table level. Column-level constraints apply to a column, and table-level constraints apply to the whole table.
How many types of constraints are in the SQL server?
Constraints are divided into many types. Please follow the below details. SQL Interview Questions and answer
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
- UNIQUE
What is NOT NULL constraint ?
It will be restricted to a NULL value. That means It will not allow the NULL value at record insert/update time.
Please find the below example for creating a table with not null constraint. SQL Interview Questions and answer
CREATE TABLE TBL_EMPLOYEE (
ID INT NOT NULL,
EMPLOYEENAME VARCHAR(200) NOT NULL,
COMPANYWEBSITE VARCHAR(300)
);
INSERT INTO TBL_EMPLOYEE (ID, EMPLOYEENAME, COMPANYWEBSITE) VALUES ( 1, 'LIPSA','WWW.INFOSYNTAX.COM')
The above record will be inserted successfully.
INSERT INTO TBL_EMPLOYEE (ID, EMPLOYEENAME, COMPANYWEBSITE) VALUES ( 1, NULL,'WWW.INFOSYNTAX.COM')
The above record will not insert into the employee table because we are passing null value in the EmployeeName column.
What is a PRIMARY KEY ?
- This is a constraint which in forces not null value and no repeated value in a column using this constraint the record is unique identify.
- It will not allow inserting/update null value
- It will also not allow inserting/update duplicate value
Please find the below example for creating a table with a primary key. SQL Interview Questions and answer
CREATE TABLE TBL_EMPLOYEE (
ID INT PRIMARY KEY,
EMPLOYEENAME VARCHAR(200) NOT NULL,
COMPANYWEBSITE VARCHAR(300)
);
INSERT INTO TBL_EMPLOYEE (ID, EMPLOYEENAME, COMPANYWEBSITE) VALUES ( 1, 'LIPSA', 'WWW.INFOSYNTAX.COM')
The above record will be inserted successfully.
INSERT INTO TBL_EMPLOYEE (ID, EMPLOYEENAME,COMPANYWEBSITE) VALUES ( 1, 'SRIYA', 'WWW. INFOSYNTAX.COM')
The above record will not insert into the employee table because the employee ID is duplicate with the old value or the employee ID “1” already available on the table.
INSERT INTO TBL_EMPLOYEE (ID, EMPLOYEENAME, COMPANYWEBSITE) VALUES ( NULL, 'LION', 'WWW. INFOSYNTAX.COM')
The above record will not insert into the employee table because the employee ID is a NULL value.
Before start to check SQL Interview Questions & Answer topic, Please understand the related chapter
Introduction to Structured Query Language.
What is a FOREIGN KEY? SQL Interview Questions
FOREIGN KEY constraints which helpful up to make a relationship between two or more than two tables using a common attribute.
- It always refers to the same table primary key or another table primary key to make a relationship.
- It is just the opposite of the primary key that means it can accept duplicate values and null.
- It supports the concept of referential integrity.
-- Create Talbe
CREATE TABLE TBL_DEPARTMENT (
DEPARTID INT NOT NULL,
DEPARTNAME VARCHAR(200),
DEPARTLOC VARCHAR(300),
);
-- Insert records
INSERT INTO TBL_DEPARTMENT (DEPARTID, DEPARTNAME, DEPARTLOC) VALUES ( 1, 'TRAINING DEPARTMENT','BANGALORE');
INSERT INTO TBL_DEPARTMENT (DEPARTID, DEPARTNAME, DEPARTLOC) VALUES ( 2, 'ACCOUNT DEPARTMENT','NEW DELHI');
The above record will insert successfully.
-- Create Talbe
CREATE TABLE TBL_EMPLOYEE (
ID INT PRIMARY KEY,
EMPLOYEENAME VARCHAR(200) NOT NULL,
COMPANYWEBSITE VARCHAR(300),
DEPARTID INT,
CONSTRAINT FK_TBL_EMPLOYEE_TBL_DEPARTMENT FOREIGN KEY (DEPARTID) REFERENCES TBL_DEPARTMENT(DEPARTID)
);
-- Insert records
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE, DEPARTID) VALUES ( 1, 'LIPSA','WWW. INFOSYNTAX.COM',1);
The above record will insert successfully.
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE, DEPARTID) VALUES ( 1, 'SRIYA','WWW. INFOSYNTAX.COM',NULL)
It will throw the error because the department null value does not exist in the referred table.SQL Interview Questions and answer
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE, DEPARTID) VALUES ( NULL, 'LION','WWW. INFOSYNTAX.COM',5)
The above record will not insert into the employee table because Department 5 is not available.
What is a CHECK constraint ?
- CHECK constraint is used for check a condition.
- CHECK constraint must be specified as a logical expression that evaluated specific condition either TRUE or FALSE.
- Specific condition should be satisfies all values in a column.
-- Create Table
CREATE TABLE TBL_EMPLOYEE (
ID INT PRIMARY KEY,
EMPLOYEENAME VARCHAR(200) NOT NULL,
COMPANYWEBSITE VARCHAR(300),
AGE INT CHECK (AGE>=20)
);
-- Insert records
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE, AGE) VALUES ( 1, 'LIPSA','WWW. INFOSYNTAX.COM', 24)
Above record will be inserted successfully.
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE, AGE) VALUES ( 2, 'LION','WWW. INFOSYNTAX.COM', 16)
Above record will not insert into the employee table because age value is less then 18.
What is a DEFAULT constraint ?
The DEFAULT constraint is used to provide a default value for an attribute.
When you inserting data that does not provide any specific value automatically assign default value only if you specified DEFAULT constraint.
DEFAULT constraint only imposes at the column level.
-- Create Table
CREATE TABLE TBL_EMPLOYEE (
ID INT PRIMARY KEY,
EMPLOYEENAME VARCHAR(200) NOT NULL,
COMPANYWEBSITE VARCHAR(300) DEFAULT 'WWW. INFOSYNTAX.COM'
);
-- Insert Records
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE) VALUES ( 1, 'LIPSA', 'WWW. INFOSYNTAX.COM');
The above record will be inserted successfully.
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME) VALUES ( 2, 'LION')
The above record will insert successfully. It will insert ‘www. infosyntax.com’ value for CompanyWebSite column.
What is a UNIQUE constraint ?
Using this constraint no duplicate value is allowed in a column but accepts null.
It will not allow duplicate values on the column.
It will allow null value only one time.
-- Create Table
CREATE TABLE TBL_EMPLOYEE (
ID INT PRIMARY KEY,
EMPLOYEENAME VARCHAR(200) UNIQUE,
COMPANYWEBSITE VARCHAR(300)
);
-- Insert Records
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE) VALUES ( 1, 'LIPSA','WWW. INFOSYNTAX.COM');
The above record will be inserted successfully.
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE) VALUES ( 2, 'LIPSA','WWW. INFOSYNTAX.COM')
The above record will not insert into the employee table because employee name is duplicate with the old value.
INSERT INTO TBL_EMPLOYEE (ID,EMPLOYEENAME,COMPANYWEBSITE) VALUES ( 3, NULL,'WWW. INFOSYNTAX.COM')
The above record will be inserted successfully.
What is referential integrity ?
Referential integrity is a concept of the relational database, It states that table relationships must between tables in a database remain accurate by applying constraints to prevent users or applications from entering inaccurate data or pointing to data that doesn’t exist. SQL Interview Questions
What are difference between Table level and Column level constraints?
Table level constraints:-SQL Interview Questions
- Table level constraints are those constraints which are imposed end of the column declaration during table deign.
- Table level constraints can able to create one or more then one constraint is imposed at one point of time.
- Table level constraints is syntactically not clear as compare to Column level constraints.
Column level constraints:-
- Column level constraints are those constraints which are imposed in just after column rectification during table deign.
- Column level constraints maximum one constraint is imposed at one point of time.
- Column level constraints is syntactically more clear.
