SQL Database Interview Question Part 1

SQL or Structured Query Language is a standard language for dealing with relational databases.

Table Structure

Table: Employee                             Table: Department
+----+-------+--------+--------------+      +----+----------+
| Id | Name  | Salary | DepartmentId |      | Id | Name     |
+----+-------+--------+--------------+      +----+----------+
| 1  | Ram   | 85000  | 1            |      | 1  | IT       |
| 2  | Henry | 80000  | 2            |      | 2  | Sales    |
| 3  | Sam   | 60000  | 2            |      +----+----------+
| 4  | Shyam | 60000  | 1            |
| 5  | Geeta | 90000  | 1            |
| 6  | Sheet | 90000  | 1            |
| 7  | Leela | 80000  | 1            |
| 8  | Geeta | 70000  | 1            |
+----+-------+--------+--------------+

Query: Create a database Query

Mysql

CREATE DATABASE BEIGNCODEEXPERT;

Query: Show database Query

Mysql

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| BEINGCODEEXPERT    |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Query: Select the database

Mysql

USE BEIGNCODEEXPERT

Query: Delete database

Mysql

DROP DATABASE BEIGNCODEEXPERT;

Query: Create a table 

Mysql

-- Create employee table
CREATE TABLE `Employee` (
  `id` bigint(20) NOT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Salary` double DEFAULT NULL,
  `DepartmentId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

-- create department table
CREATE TABLE `Department` (
  `id` bigint(20) NOT NULL,
  `Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Query: Alter table

Mysql

ALTER TABLE Employee ADD FULLNAME  varchar(255) DEFAULT NULL;

Query: Show the structure of the existing table.

Mysql

SHOW CREATE TABLE Employee\G;

CREATE TABLE `Employee` (
  `id` bigint(20) NOT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Salary` double DEFAULT NULL,
  `DepartmentId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Query: Drop table.

Mysql

DROP TABLE Employee;

Query: Insert records.

Mysql

INSERT INTO Employee (Id, Name,Salary,DepartmentId)
     VALUES (1,'Ram',85000,1);

INSERT INTO Employee (Id, Name,Salary,DepartmentId)
     VALUES (2,'Henry',80000,2);

 INSERT INTO Employee (Id, Name,Salary,DepartmentId)
    VALUES (3,'Sam',60000,2);

INSERT INTO Employee (Id, Name,Salary,DepartmentId)
     VALUES (4,'Shyam',60000,1);

INSERT INTO Employee (Id, Name,Salary,DepartmentId)
     VALUES (5,'Geeta',90000,1);

INSERT INTO Employee (Id, Name,Salary,DepartmentId)
     VALUES (6,'Sheeta',90000,1);

INSERT INTO Employee (Id, Name,Salary,DepartmentId)
    VALUES (7,'Leela',80000,1);

INSERT INTO Employee (Id, Name,Salary,DepartmentId)
     VALUES (8,'Geeta',70000,1);

Query: Update records.

Mysql

-- update single record
UPDATE Employee set Name='Ram Singh' WHERE Id=1; 

-- update department 1 all records (Name, Salary)
UPDATE Employee set Name='Ram Singh', Salary=90000 WHERE DepartmentId=1; 

Query: Write a query to find all the employees.

Mysql

SELECT * FROM Employee;
+----+--------+--------+--------------+
| id | Name   | Salary | DepartmentId |  |
+----+--------+--------+--------------+
|  1 | Ram    |  85000 |            1 |
|  2 | Henry  |  80000 |            2 |
|  3 | Sam    |  60000 |            2 |
|  4 | Shyam  |  60000 |            1 |
|  5 | Geeta  |  90000 |            1 |
|  6 | Sheeta |  90000 |            1 |
|  7 | Leela  |  80000 |            1 |
|  8 | Geeta  |  70000 |            1 |
+----+--------+--------+--------------+

Query: Write a query to fetch Name from the Employees table.

Mysql

SELECT Name FROM Employee;

Query: Write a query to fetch Name in the upper case from the Employees table.

Mysql

SELECT UPPER(Name) FROM Employee;

Query: Write a query to fetch the number of employees working in the department ‘IT’.

Mysql

SELECT COUNT(Name) FROM Employee Where DepartmentId=1;

Query: Write a query to find all the employees whose salary is between 80000 to 100000.

Mysql

SELECT * FROM Employee Where Salary BETWEEN 80000 AND 100000;

OR

SELECT * FROM Employee Where Salary>80000 AND Salary<100000;

Query:  Write a query to find the names of employees that begin with ‘R’

Mysql

SELECT * FROM Employee Where Name LIKE 'R%';

Query: Write a query to fetch the top 5 records.

Mysql

SELECT * FROM Employee ORDER BY Salary DESC LIMIT 5;

Query: Write a query to fetch all the records from the Employee table ordered by Salary in descending order and Department in ascending order.

Mysql

SELECT * FROM Employee ORDER BY Salary DESC, DepartmentId ASC;

Query: Write a query to fetch all employees who belong to the IT department.

Mysql

SELECT e.NAME, e.Salary, d.Name as DepartmentID
FROM Employee e 
INNER JOIN Department d ON (d.id=e.DepartmentID)

Exception Handling Interview Questions

DBMS Interview Questions Set -1

DBMS Interview Questions Set -2

SQL Interview Question Set -1

SQL Interview Question Set -2

JPA Interview Questions Set -1

JPA Interview Question Set -2

Hibernate Interview Questions

Spring Boot Interview Questions Set 1

Spring Boot Interview Questions Set 2