Mock Interview Questions

What is the difference between a relational database and a NoSQL database?

What is database normalization?

What is the purpose of an index in a database?

What is the difference between a primary key and a foreign key?

What is a database transaction?

What is the purpose of a database view?

What is the difference between a database schema and a database instance?

What is data warehousing?

What is the purpose of a database trigger?

What is the difference between a database cursor and a database pointer?

What is the purpose of a database lock?

What is the difference between a database deadlock and a database livelock?

What is the purpose of a database journal?

What is the difference between a database backup and a database snapshot?

What is the purpose of a database replication?

What is the difference between a master-slave replication and a peer-to-peer replication?

What is the purpose of a database sharding?

What is the difference between a horizontal partitioning and a vertical partitioning?

What is the purpose of a database denormalization?

What is the difference between a database ETL and a database ELT?

What is the purpose of a database data masking?

What is the difference between a database data encryption and a database data hashing?

What is the purpose of a database access control?

What is the difference between a database authentication and a database authorization?

Write a SQL query to retrieve all rows from a table called 'employees' where the 'salary' is greater than 50000.

SELECT * FROM employees WHERE salary > 50000;

Write a Python function to connect to a MySQL database and retrieve all rows from a table called 'employees'.

import mysql.connector;
cnx = mysql.connector.connect(user='username', password='password', host='127.0.0.1', database='database');
cursor = cnx.cursor();
cursor.execute('SELECT * FROM employees');
rows = cursor.fetchall();
for row in rows: print(row);
cnx.close()

Write a Java function to insert a new row into a table called 'employees' in a PostgreSQL database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
Connection conn = DriverManager.getConnection('jdbc:postgresql://localhost:5432/database', 'username', 'password');
String query = 'INSERT INTO employees (name, salary) VALUES (?, ?)';
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, 'John Doe');
pstmt.setDouble(2, 60000);
pstmt.executeUpdate();
conn.close()

Write a SQL query to retrieve all rows from a table called 'orders' where the 'total' is greater than 1000 and the 'customer_id' is equal to 123.

SELECT * FROM orders WHERE total > 1000 AND customer_id = 123;

Write a C# function to connect to a SQL Server database and retrieve all rows from a table called 'customers'.

using System.Data.SqlClient;
string connectionString = 'Data Source=localhost;
Initial Catalog=database;
User ID=username;
Password=password';
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand('SELECT * FROM customers', connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {

Console.WriteLine(reader['name']);
}
connection.Close()

Write a SQL query to retrieve all rows from a table called 'products' where the 'price' is less than 50 and the 'category' is equal to 'electronics'.

SELECT * FROM products WHERE price < 50 AND category = 'electronics';

Write a Python function to connect to an Oracle database and retrieve all rows from a table called 'employees'.

import cx_Oracle;
conn = cx_Oracle.connect('username/password@localhost:1521/database');
cursor = conn.cursor();
cursor.execute('SELECT * FROM employees');
rows = cursor.fetchall();
for row in rows: print(row);
conn.close()

Write a Java function to update a row in a table called 'employees' in a MySQL database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
Connection conn = DriverManager.getConnection('jdbc:mysql://localhost:3306/database', 'username', 'password');
String query = 'UPDATE employees SET salary = ? WHERE name = ?';
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setDouble(1, 70000);
pstmt.setString(2, 'John Doe');
pstmt.executeUpdate();
conn.close()

Write a SQL query to retrieve all rows from a table called 'orders' where the 'total' is greater than 500 and the 'order_date' is within the last 30 days.

SELECT * FROM orders WHERE total > 500 AND order_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Write a C# function to connect to a PostgreSQL database and retrieve all rows from a table called 'customers'.

using Npgsql;
string connectionString = 'Host=localhost;
Database=database;
Username=username;
Password=password';
NpgsqlConnection connection = new NpgsqlConnection(connectionString);
connection.Open();
NpgsqlCommand command = new NpgsqlCommand('SELECT * FROM customers', connection);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {

Console.WriteLine(reader['name']);
}
connection.Close()