Baker Construction....................... SQL Screening

Baker Construction
Screening Test/Interview Questions for SQL Developer (Remote)

Position
Instructions: Please precede all your answers with the question you are
answering.
Use acronyms only after you've explained them.
Use correct spelling and grammar.
Be sure to write your name and the interviewer’s name.
Candidate’s Name: Jiahui Jiang
Interviewer’s Name: Victor Howard
1. Are you currently employed? No, I was let go from last position 2023
Dec. because of company down-size.
2. Can you tell us some basic information about yourself? I am currently

holding a bachelor’s degree in computer science and have 3.5 years of
work experience doing full-stack engineering.
3. What are the main qualities you look for in a new job? Top three qualities
that I look for in a new job are Learning opportunities, good team
cultures, and fair compensations.
4. What is the difference between CHAR and VARCHAR2 datatype in SQL?
I Think of CHAR like a ruler with fixed markings. When you say
CHAR(10), it's like having 10 slots whether you fill them or not. So, if you
store "hi" in a CHAR(10) column, it actually takes up 8 extra spaces (10 -
2 for "hi"). While for VARCHAR2, it adjusts its size based on what you
put in. So, if you have VARCHAR2(10) and put in "hi," it only uses 2
spaces, not 10. No extra spaces or padding. So, CHAR gives you fixed
slots, good for things like IDs or codes. VARCHAR2 is flexible, great for
text where the length varies like names or descriptions. CHAR can be a
bit faster for certain things, but VARCHAR2 saves space when you don't
need fixed lengths.
5. What do you mean by table and field in SQL?

In SQL, a table is like a spreadsheet where you organize your data.
It's a collection of related information arranged in rows and columns.
Each row represents a single record, and each column represents a
specific attribute or piece of data about that record. For example, you
might have a table called "Customers" with columns like "CustomerID,"
"Name," "Email," and "Phone." A field in SQL refers to a specific piece
of information within a table. It's synonymous with a column. For
instance, in the "Customers" table, "CustomerID," "Name," "Email," and
"Phone" are all fields. Each field has a defined datatype (like CHAR,
VARCHAR2, INTEGER, etc.) that determines what kind of data it can
store.
6. What are UNION, MINUS and INTERSECT commands?

UNION is like putting together two lists, making sure there are no
duplicate items. It's handy when you want to combine results from
different queries into one big list. MINUS (or EXCEPT) is for figuring out
what's unique in one list compared to another. It's like finding the
differences between two sets of data. INTERSECT is about finding
what's common between two lists. It's like picking out items that show
up in both lists. So, each of these commands helps you manipulate and
compare data in SQL, depending on what you're trying to achieve.
7. What is OLTP? And what are the differences between OLTP and OLAP?
OLTP (Online Transaction Processing) is all about handling everyday
transactions like sales, orders, and inventory updates in real time. It's
like the engine that keeps things running smoothly in a business. On the
flip side, OLAP (Online Analytical Processing) is more about diving deep
into data for analysis, reporting, and making big-picture decisions. It's
like having a powerful microscope to explore trends, patterns, and
insights from large datasets. The main difference? OLTP is for quick,
everyday transactions, while OLAP is for in-depth analysis and decision-
making.
8. What are some common clauses used with SELECT query in SQL?
When using the SELECT query in SQL, you often pair it with
different clauses to get the exact data you need:
- SELECT: It's like saying "Hey database, show me these columns."
FROM: Tells the database which table to look in for the data you want.
- WHERE: Adds conditions to filter the rows based on specific criteria.
- GROUP BY: Groups data based on common values in a column, often
used with aggregate functions like counting or summing.
- HAVING: Works with GROUP BY to filter grouped data based on
conditions.
- ORDER BY: Sorts the results in ascending or descending order based
on specified columns.
- DISTINCT: Gives you only unique values, removing duplicates from the
result set.
- LIMIT (or TOP): Sets a maximum number of rows to return from the
query.
These clauses help you fine-tune your SELECT queries to get precisely
the data you're looking for from the database.
9. List the different types of relationships in SQL.
- One-to-One (1:1) Relationship: In a one-to-one relationship, each record in one
table is related to exactly one record in another table. Example: A table for
employees and a table for their employee IDs and contact information. Each
employee has only one set of contact information.
- One-to-Many (1:N) Relationship: In a one-to-many relationship, each record in

one table can be related to one or more records in another table. Example: A
table for customers and a table for their orders. A customer can have multiple
orders, but each order belongs to only one customer.
- Many-to-One (N:1) Relationship: In a many-to-one relationship, multiple

records in one table can be related to a single record in another table. Example:
A table for orders and a table for customers. Many orders can belong to the
same customer.
- Many-to-Many (N:N) Relationship: In a many-to-many relationship, multiple

records in one table can be related to multiple records in another table. Example:
A table for students and a table for courses. A student can enroll in multiple
courses, and each course can have multiple students. These relationships are
fundamental to database design and help establish connections and
dependencies between different sets of data within the database.
10. What is Database Black Box Testing?

Database black box testing is like testing a magic box without
knowing how it works inside. You interact with the database just like a
regular user would, without peeking at its internal code or structure. The
focus is on checking if the database does what it's supposed to do—like
storing and fetching data correctly, handling errors gracefully, and
performing well even under heavy loads. You also want to make sure the
data is secure, the system doesn't crash under pressure, and any
changes you make don't break existing functionalities. It's all about
ensuring the database does its job without revealing its secrets.
12.What is schema in SQL Server?

In SQL Server, a schema is a collection of database objects (such as
tables, views, procedures, functions, etc.) that are grouped together and
owned by a database user. It provides a way to organize and manage
objects within a database, allowing for better organization, security, and
separation of concerns.
11. How to create a temp table in SQL Server?

Creating a temporary table in SQL Server is straightforward. You can
use the CREATE TABLE statement with the # symbol to denote a
temporary table. Here's an example of how to create a temporary table:
-- Create a temporary table with some columns
CREATE TABLE #TempTable (
ID INT PRIMARY KEY,
Name NVARCHAR(50),
Age INT
);
-- Insert data into the temporary table

INSERT INTO #TempTable (ID, Name, Age)
VALUES (1, 'John Doe', 30),
(2, 'Jane Smith', 25),
(3, 'Michael Johnson', 35);
-- Query the temporary table
SELECT * FROM #TempTable;
-- Drop the temporary table when no longer needed

DROP TABLE #TempTable;
13. Will you be able to devote 30 to 40 hours of your time weekly for the
company? YES
14. How long would you expect to work for us if hired and how much will you
request per hour if you are hired? Personally, I don’t like changing my job
frequently. For compensation I am looking somewhere around 40-50
hourly rate.
15. When can you start working with us if we offer you this position?
I can start immediately if needed.
16. What is BLOB and TEXT in MySQL?
In SQL, BLOB and TEXT are data types used to store large amounts of
binary or text data. BLOB (Binary Large Object): It's used to store binary
data, such as images, audio/video files, or other non-textual data.
BLOBs can store large amounts of data, ranging from a few bytes to
gigabytes, depending on the storage engine and configuration.
TEXT: It's used to store large amounts of text data, such as long strings
of characters or documents. SQL provides different TEXT types based
on the maximum size of data they can store.
Responsibilities:
• Manage migration of SQL 2005 databases to SQL 2008 R2.

• Work with dynamic T-SQL queries, DDL, DML statements for creating
objects, making updates and managing user permissions.
• Coordinate with DBA in creating and managing table, indexes, table
spaces, triggers, db links and privileges.
• Used subversion tools, SVN version management to manage various
versions of the procedures and functions for of the application.
• Design the structures and layouts of reports, apply drill-down, drill-through
and sub-reports.
• Develop the UI using JavaScript, JSP, HTML, and CSS for interactive
cross browser functionality and complex user interface.
• Support web base Java application heavily reliant upon XML documents.
• Involve in ETL architecture enhancements to increase the performance
data warehouse using query manager and optimizer.
• Consolidate redundant existing reports to create a dashboards and add
new metrics for improve data visualization.
• Enable drill-through for a cube.
Can you handle all of these duties effectively?

Based on my pass experiences working as a full-stack engineer and
experience building productional applications, I believe that I am able to
handle all theses duties effectively.

Baker Construction....................... SQL Screening

Uploaded by

Copyright:

Available Formats

Baker Construction....................... SQL Screening

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Baker Construction....................... SQL Screening

Uploaded by

Copyright:

Available Formats

Baker Construction

Screening Test/Interview Questions for SQL Developer (Remote)

Candidate’s Name: Jiahui Jiang

Interviewer’s Name: Victor Howard

2. Can you tell us some basic information about yourself? I am currently

5. What do you mean by table and field in SQL?

6. What are UNION, MINUS and INTERSECT commands?

- One-to-Many (1:N) Relationship: In a one-to-many relationship, each record in

- Many-to-One (N:1) Relationship: In a many-to-one relationship, multiple

- Many-to-Many (N:N) Relationship: In a many-to-many relationship, multiple

10. What is Database Black Box Testing?

12.What is schema in SQL Server?

11. How to create a temp table in SQL Server?

-- Insert data into the temporary table

-- Drop the temporary table when no longer needed

• Manage migration of SQL 2005 databases to SQL 2008 R2.

Can you handle all of these duties effectively?

You might also like