SQL Interview Questions - Set 3

This page consists of SQL interview questions and answers.

Q1: What is the difference between IN and BETWEEN operators?

The IN operator is used to check for values in a given set.

For example, if we want to list the details of students having studentid lets say s01, s03 and s04 then we will use the IN operator.

mysql> SELECT * FROM student
WHERE studentid IN ('s01', 's03', 's04');

The BETWEEN operator is used to retrieve rows based on a given range of values.

For example, if we want to list all the students born from 1st January 2000 to 31st December 2000 then we will use the BETWEEN operator.

mysql> SELECT * FROM student
WHERE birthday BETWEEN '2000-01-01' AND '2000-12-31';

mysql> denotes MySQL command line.

Q2: List all the students whose firstname starts with 'G'

For this we will use the LIKE operator.

Following query will fetch all the students whose firstname starts with 'G'.

mysql> SELECT * FROM student
WHERE firstname LIKE 'G%';

Q3: List all the students whose lastname ends with 'a'

For this we will use the LIKE operator.

Following query will list all the students whose lastname ends with 'a'.

mysql> SELECT * FROM student
WHERE lastname LIKE '%a';

Q4: List all the students whose firstname or lastname contains the string 'ai'

For this we will use the LIKE operator.

Following is the query that will return all the students whose firstname or lastname contains the string 'ai'.

mysql> SELECT * FROM student
WHERE firstname LIKE '%ai%'
OR lastname LIKE '%ai%';

Q5: What is the difference between a primary key and a unique constraint?

Following are the differences between a primary key and a unique constraint.

Primary key can't have NULL value whereas a unique constraint can have the NULL value.

There can only be ONE primary key in a given table whereas there can be one and more number of unique constraints.

Q6: What is a VIEW?

A VIEW is like a virtual table that consists of columns from one or more number of tables but no data.

Q7: Write SQL statement to create a VIEW of your choice

Lets say we have a students table and it consists of the following columns.

• studentid
• firstname
• lastname
• contact_phone
• address
• birthday
• email
• modified_at
• created_at

Now lets say we want to create a view that will contain only the following columns.

• studentid
• firstname
• lastname

Then we can write the following SQL.

mysql> CREATE VIEW students_name AS
SELECT studentid, firstname, lastname
FROM students;

So, if we want to list all the students name using the view students_name then we can write the following query.

mysql> SELECT * FROM students_name;

Q8: How will you delete a VIEW?

To delete a view use DROP VIEW.

For example, if we have a view lets say students_score_view then we can delete it by writing the following SQL.

mysql> DROP VIEW students_score_view;

Q9: What is the difference between SQL and MySQL?

SQL or Structured Query Language is a language used to interact with database. MySQL is a RDBMS (Relational Database Management System) that uses SQL to work with the data.

Q10: Explain Entities and Relationships

An entity is a real world object whose data can be stored in a database table.

For example, a student is an entity and the data of a student can be stored in a students table.

A relationship is a link that connects two or more tables.

For example we can say the customers table has a relationship with the orders table.