Common Database Interview Questions

Displaying 1 - 8 of 8

What Is The Main Difference Between Primary Key; Unique Key; And Foreign Key?

Below are the difference between Primary Key, Unique Key and Foreign Key

Parameters Primary Key Unique Key Foreign Key
Basic A primary key is a column of table which uniquely identifies each tuple (row) in that table. Primary key enforces integrity constraints to the table. Unique key constraints also identifies an individual tuple uniquely in a relation or table. A table can have more than one unique key unlike primary key. Foreign key is a field in the table that is primary key in another table.
Null Value Acceptance Cannot accept null values. Can accept one null value. Accept multiple null values.
Number of keys that can be defined in a table Only one primary key More than one unique key We can have more than one foreign key in a table.
Index Create cluster index Create non-cluster index Do not automatically create an index clustered or non-clustered. You must manually create an index of foreign key.
Support Auto-increment of Value Supported Not supported Not supported
Restriction on delete We can't delete primary key value from the parent table which is used as a foreign key in child table. To delete we first need to delete that primary key value from the child table. Unique Constraint can not be related with another table's as a Foreign Key. In SQL Server, Unique key can be made foreign key into another table. We can delete the foreign key value from the child table even though that refers to the primary key of the parent table.

Suppose you want to create a table named 'mobile_phone' which contains the following columns:

  1. Mobile_phone_ID, which is of integer type
  2. Camera, which is of string type
  3. RAM, which is of integer type
  4. Memory, which is of string type

The primary key of this table is 'Mobile_phone_ID'.

Q1: What would be the correct SQL query to create such a table? Also, identify the type of operation, i.e., whether it is a DDL statement or a DML statement.

Option 1: It is a DML Statement

 create table mobile_phone (
    Mobile_phone_ID int(11) NOT NULL,
    Camera varchar(12),
    RAM int(11),
    Memory varchar(12)
    );

Option2: It is a DDL Statement

create table mobile_phone (
     Mobile_phone_ID int(11) NOT NULL,
     Camera varchar(12),
     RAM int(11),
     Memory varchar(12)
     );

Option 3: It's a DML Statement

create table mobile_phone (
     Mobile_phone_ID int(11) NOT NULL,
     Camera varchar(12),
     RAM int(11),
     Memory varchar(12),
     Primary Key (Mobile_phone_ID)
     );

Option 4: It's a DDL Statement (Correct)

create table mobile_phone (
     Mobile_phone_ID int(11) NOT NULL,
     Camera varchar(12),
     RAM int(11),
     Memory varchar(12),
     Primary Key (Mobile_phone_ID)
     );

Q2: Suppose you want to add a new column, 'price', with ‘float’ as the data type, in the table 'mobile_phone'. What is the correct query to add this new column? 

  • ALTER ‘mobile_phone’ ADD COLUMN ‘price’ float(24)
  • ADD COLUMN ‘price’ float(24) IN TABLE ‘mobile_phone’
  • ALTER TABLE ‘mobile_phone’ ADD COLUMN ‘price’ float(24)
  • ADD COLUMN ‘price’ float(24) IN ‘mobile_phone’

Q3: Suppose you want to update the value of ‘Camera’ to 15MP, where RAM = 8. What would be the correct query to perform such an operation?

Option 1: Correct

update mobile_phone
set Camera= ‘15MP’
where RAM= 8;

 Option 2:

alter mobile_phone
set Camera= ‘15MP’
where RAM= 8;

Option 3:

insert Camera= ‘15MP’
where RAM= 8;
in table mobile_phone

Option 4:

update mobile_phone
set Camera= ‘15MP’
where RAM= ‘8’;

Suppose you are given a data set of a college. This data set mainly includes the following four tables:

Student: This table contains information about the students, such as student id, name, range of marks scored in the exam, year of graduation and branch id.

This table contains information about the students, such as student id, name, range of marks scored in the exam, year of graduation and branch id.

Branch: This table includes the different branches present in the college, such as Electrical, Mechanical, Civil, Computer Science, Chemical, along with branch ids.

Branch: This table includes the different branches present in the college, such as Electrical, Mechanical, Civil, Computer Science, Chemical, along with branch ids.

HoD: This table contains information about the HoDs, i.e., Head of the Departments of the different branches. This information includes HoD name, duration of service as HoD, branch id of the branch in which a particular person is/was HoD and their contact information.

HoD: This table contains information about the HoDs, i.e., Head of the Departments of the different branches. This information includes HoD name, duration of service as HoD, branch id of the branch in which a particular person is/was HoD and their contact information.

Marks: This table contains the mapping of the range of marks with the grades awarded.

Marks: This table contains the mapping of the range of marks with the grades awarded.

You can consider the following entries as examples of each of the table formats above.

Student

You can consider the following entries as examples of each of the table formats above.

Branch

Branch

HoD

HoD

Marks

Marks

Answer the following questions based on the information in the tables given above.

Refer to the ERD given below for the 'orders' data set. Answer given questions.

er diagram questions

Q1: How many foreign keys does the ‘Order Detail’ table have? 

  • 0
  • 1
  • 2
  • 3

The foreign keys are ‘product id’ and ‘order id’, referring to the ‘Product’ and ‘Order’ tables, respectively.

Q2: Which of the following statement(s) is/are true?

  • An order can have one or more order details.
  • A branch can have one or more headquarters.
  • A headquarter can have one or more branches.
  • A headquarter can correspond to only one order.

Feedback :

The relationship between ‘Branch’ and ‘Headquarters’ in that order has not been specified. The relationship between ‘Order’ and ‘Order Detail’ is denoted with a ‘|’ and the crow’s feet notation. The relationship between ‘Headquarters’ and ‘Branch’ is denoted with a ‘|’ and the crow’s feet notation.