SQL

Question 1

What are the constraints you can use in SQL

Answer 1

not null: This constraint restricts the values that are null from being inserted into columns.
unique: This will allow only unique values in the column along with the null value
primary key: This constraint allows only those values that are not null and unique.
foreign key: This constraint helps in forming the relationship between two or more tables in SQL.
index: This constraint helps in improving query performance and fast retrieval of data
check: This is used when we have to check if all the data that is being inserted satisfy a condition.

Question 2

How are primary key constraints and unique key constraints different?

Answer 2

In a table in SQL, there can be many columns that can be a unique key but only one primary key is allowed on one table.The primary key is a combination of unique key plus null constraint, whereas unique key has only unique constraint and it can be null.

Question 3

When creating a table in SQL, you forget to make a column as the primary key, then is there any possibility to create a primary key on that column or do we have to delete the table from the database so that we can create a primary key while creating a table?

Answer 3

You can add the primary key after the creation of the table using the ALTER command.

alter table table_name add primary key(column_name);

Question 4

Suppose you want to create a student table having id, name, age and class as columns in it.Write down the query that will create that table in the “school” database.

Answer 4

create database school;

use school;

create table school(
id int,
name varchar(30),
age int,
class varchar(10)
);

Question 5

While creating a table, how will you decide on the column that can be converted into primary key?

Answer 5

There are set of rules that we can follow while creating a primary key:
1. A column must have unique values.

2. A column shouldn’t contain any null value.
3. Only one primary key can be created for one table.
4. Columns that are of type number are recommended for the primary key columns.

Question 6

In SQL, what are the commands that are the part of Data Definition Language?

Answer 6

Data Definition language or DDL commands are used to describe or define the structure of the database objects.In DDL, the following are the commands:

create
alter
drop
truncate
comment
rename

Question 7

Do drop and truncate commands have the same usage in SQL?

Answer 7

Both drop and truncate are part of DDL commands and also look similar while deleting records of the table in the database.But one major difference between both is that drop deletes all the records from the table as well as the table structure, whereas truncate will only delete all the records from the table but not the table structure.Also, the drop command can be used to delete the database, whereas truncate cannot be used to delete the database.

Question 8

Suppose you have created a table called “student” with column fields as id, name, age, address and class. But now you want to rename the “id” column to “student_id”, then how will you do that in SQL?

Answer 8

Using alter command, we can rename the column name.

alter table student
rename column id to student_id;

Here “id” column name is changed to “student_id” name.

Question 9

How rename and alter command different from each other while renaming a table in SQL?

Answer 9

rename command and alter both have similar working when renaming a table name in SQL except for the syntax.The only difference between both is that rename cannot be used to rename the temporary table, whereas alter command can rename a temporary table in SQL.

Question 10

How does normalization effect the performance in SQL?

Answer 10

The main reason to use the normalization forms in table data is to eliminate the repetition of data from it.So one thing we can do is say that it will guarantees the duplicate free data in the table.But achieving full normalization, it negatively affects the performance.

Question 11

Tell me the difference in 1nf, 2nf and 3nf forms of normalization

Answer 11

In 1nf or 1st normal form, the composite attribute is converted into a single-value attribute.Each column must only have one single data entry in each row.
In 2nf or 2nd normal form, the table should not have any partial dependency means the proper subset of the primary key shouldn’t determine any non-prime attribute.
In 3nf or 3rd normal form, there should not be any transitive dependency, which means non-prime attribute of the table should not be dependent on another non- prime attribute.

Question 12

Tell me about some of the benefits of normalization in SQL?

Answer 12

It is used to reduce or remove duplicates from the data.
To optimize storage space.
To prevent unwanted deletion of data.
To prevent data inconsistency.

Question 13

Explain the different subsets of SQL?

Answer 13

In SQL, the Most common subsets are DDL, DML, DQL, DCL and TCL.

DDL allows the user to create, alter and drop objects of the database.
DML allows the user to manipulate the data in the database using insert, update and delete commands.
DQL allows the user to fetch the data from the database using select command.
DCL commands like grant and revoke gives or remove permission to the user on the database elements.
TCL commands are used to control the data transaction using commit, rollback and savepoint.

Question 14

Create an “employee” table and make one primary key and one foreign key in it.

Answer 14

create table employee(
emp_id int,
emp_name varchar(20),
dept_id int,
primary key (emp_id),
foreign key (dept_id) references department(dept_id)
);

Question 15

Can a primary key and foreign key contain null?

Answer 15

A primary key field in the table cannot contain null as a value.But that is not the case with a foreign key.A foreign key is used to stabilize a relation between two tables and it can contain a null value.

Question 16

Assume you have created one table as “emp” and now you want to change that table name to “employee” then what are the ways, in SQL, through which we can change the table name?

Answer 16

For changing the table name in SQL, we can go for rename command or alter command:
With rename

rename table emp to employee;
With alter

alter table emp
rename to employee;

Question 17

Suppose Jack has created a table as “Food” with id and food_name field as varchar datatype.But now he wanted to change the datatype of id from varchar to int.What query he should write that will do his task?

Answer 17

alter table Food
modify column id int;

Question 18

Tell the difference between alter and update in SQL

Answer 18

The alter command is a DDL command, whereas update is a DML command
The alter command is used to perform the operation on the structure level.On the other hand, update is used to perform an operation on the data level.
The alter command is used to modify the attribute of the table.The update command is used to modify the rows of the table.