Sql Coding Test For Interview

Explain Database White Box Testing And Black Box Testing

The white box testing method mainly deals with the internal structure of a particular database, where users hide specification details. The white box testing method involves the following:

• As the coding error can be detected by testing the white box, it can eliminate internal errors.
• To check for the consistency of the database, it selects the default table values.
• This method verifies the referential integrity rule.
• It helps perform the module testing of database functions, triggers, views, and SQL queries.

The black box testing method generally involves interface testing, followed by database integration. The black box testing method involves the following:

• Mapping details
• Verification of outgoing data from the other query functions

What Is The Acid Property In A Database

The full form of ACID is atomicity, consistency, isolation, and durability. ACID properties are used to check the reliability of transactions.

• Atomicity refers to completed or failed transactions, where a transaction refers to a single logical operation on data. This implies that if any aspect of a transaction fails, the whole transaction fails and the database state remains unchanged.

What Is A Unique Key

The key that can accept only a null value and cannot accept duplicate values is called a unique key. The role of a unique key is to make sure that all columns and rows are unique.

The syntax for a unique key will be the same as the primary key. So, the query using a unique key for the employee table will be:

//CREATE TABLE Employee  NOT NULL,Employee_designation varchar,Employee_Age int,UNIQUE) 

Also Check: How To Build A Portfolio For An Interview

What Is A Stored Procedure

A stored procedure is a subroutine available to applications that access a relational database management system . Such procedures are stored in the database data dictionary. The sole disadvantage of stored procedure is that it can be executed nowhere except in the database and occupies more memory in the database server. It also provides a sense of security and functionality as users who can’t access the data directly can be granted access via stored procedures.

DELIMITER $$CREATEPROCEDURE FetchAllStudentsBEGINSELECT*FROM myDB.students END $$DELIMITER  

What Is The Difference Between Cluster And Non

Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.

A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.

Don’t Miss: How To Interview A Graphic Designer

What Is An Index

Indexes help speed up searching in a database. If there is no index on a column in the WHERE clause, then the SQL Server has to skim through the entire table and check each and every row to find matches, which may result in slow operations in large data.

Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches.

Syntax:

CREATE INDEX INDEX_NAME ON TABLE_NAME 

The Prevalence Of Sql

One of the biggest reasons for the popularity of SQL is the tabular format for storage of data. It is easy to visualize databases as large spreadsheets with millions and millions of rows and columns. SQL allows users to quickly manipulate these tables to access information and present the results in the most common formats, tables and associated graphs and visualizations.

While No-SQL databases like MongoDB, Cassandra, etc have gained traction with the requirements of Big-Data and real time applications and increasing prevalence of unstructured data, SQL databases still hold the top seven positions of the top ten most popular database engines. In fact No-SQL databases have positioned themselves as No just SQL to highlight their support for SQL so as to help increase their acceptance in organizations where traditional SQL based databases are already used.

Another reason for the popularity of SQL is the ease of use. The syntax is very easy to understand, and anyone can pick it up very quickly. It is very easy to comprehend what the below statements do.

SELECTnameFROM EMPLOYEES 

SELECTnameFROM EMPLOYEESWHERE age > = 35 

SELECT state, AVG as average_age FROM EMPLOYEESWHERE age > = 35GROUPBY state 

Read Also: How To Prepare Resume For Interview

Before The Tasks What Can You Expect In An Sql Technical Screening

There are two common ways an SQL tech screening can be done.

The simpler but less common way is that you get a computer, a data set, and a task. While you are solving the task, the interviewers are listening and asking questions. A little trial-and-error is totally fine, as long as you can come up with the correct solution in a reasonable amount of time.

The other, more difficult way is the whiteboard interview. In this case, you dont get a computer. You have to solve the task and sketch up the code on a whiteboard. This means that you wont get feedback on whether you made a logical or a syntax error in your code. Of course, you can still solve the tasks by thinking iteratively. You can crack the different SQL problems one by one But you have to be very confident with your SQL skills.

Additionally, usually, you have to solve the tasks on the fly. Maybe you will get 3-5 minutes of thinking time but thats the maximum you can expect.

I know, this sounds stressful. And it is. But dont worry, there is some good news, as well. Because companies know that this is a high-stress interview type, compared to the real-life challenges, you will get relatively simpler tasks.

Victoria Cyczewska Talent Acquisition Specialist At Stepstone Services

We now have a tool which allows us to not only collaborate to create tests assessing technical skills we need to grow, but also to communicate internally and externally . What is more, our technical recruitment process is now more structured. As an example, we use our technical screening results as a conversation starter during technical interviews.

Also Check: How To Prepare For Toptal Interview

What Is A Unique Constraint

Unique constraints ensure that all the values in a column are different. For example, if we assign a unique constraint to the e_name column in the following table, then every entry in this column should have a unique value.

First, we will create a table.

create table stu2)

Now, we will insert the records.

insert into stu2 valuesinsert into stu2 valuesinsert into stu2 values

Output:

A PRIMARY KEY constraint will automatically have a UNIQUE constraint. However, unlike a PRIMARY KEY, multiple UNIQUE constraints are allowed per table.

What Is A Database

This question can be most probably asked among various other SQL interview questions A database refers to a structured collection of data that can be stored, maintained, and accessed digitally from a local or remote computer network. A fixed design and modeling technique is used to build databases, which can be large and complex. Large databases are housed on multiple computers or cloud services, whereas smaller databases can be stored on a file system.

Get Software Engineering degrees from the Worlds top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Read Also: How To Act During An Interview

What Are All The Different Types Of Indexes

There are three types of indexes -.

• Unique Index.

This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

• Clustered Index.

This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.

• NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

Prepare Sample Data To Practice Sql Skill

Sample Table Worker

To prepare the sample data, you can run the following queries in your database query executor or on the SQL command line. Weve tested them with MySQL Server 5.7 and MySQL Workbench 6.3.8 query browser. You can also download these Softwares and install them to carry on the SQL exercise.

SQL Script to Seed Sample Data.

CREATE DATABASE ORG SHOW DATABASES USE ORG CREATE TABLE Worker ,LAST_NAME CHAR,SALARY INT,JOINING_DATE DATETIME,DEPARTMENT CHAR) INSERT INTO Worker  VALUES,,,,,,, CREATE TABLE Bonus ,BONUS_DATE DATETIME,FOREIGN KEY REFERENCES Worker        ON DELETE CASCADE) INSERT INTO Bonus  VALUES,,,, 

CREATE TABLE Title ,AFFECTED_FROM DATETIME,FOREIGN KEY REFERENCES Worker        ON DELETE CASCADE) INSERT INTO Title  VALUES , , , , , , ,  

Once above SQL would run, youll see a result similar to the one attached below.

Creating Sample Data to Practice SQL Skill.

Read Also: How To Ace Amazon Interview

Practical Sql Exercises For Interview

Technical job interviews often have a practical side. This is where you need to prove your SQL skills as you would use them in real life â apart from answering typical SQL whiteboard questions, youâll be asked to write a SQL statement, check a query for mistakes, or determine the result of a given query.

Below, Iâve copied ten SQL exercises of the types you might do on a technical job interview. They are sorted from easy to hard.

What Is The Difference Between Between And In Operators In Sql

The BETWEEN operator is used to represent rows based on a set of values. The values may be numbers, text, or dates. The BETWEEN operator returns the total number of values that exist between two specified ranges.

The IN condition operator is used to search for values within a given range of values. If we have more than one value to choose from, then we use the IN operator.

Don’t Miss: What To Wear To An Interview In The Summer

How Are Sql Online Tests Evaluated

One of the main advantages of DevSkiller testing is that evaluation of our SQL online tests happens automatically. As soon as the candidate is finished, our screening tool gets to work on the solutions and automatically assesses how well each candidate has done. This saves recruiters countless arduous hours spent marking algorithm tests.

Candidates are assessed on whether or not the solution they came up with would run . How their solution would perform in edge cases and the content and quality of their code. Successful candidates are those who can best demonstrate their critical thinking skills in the time allotted.

Results are then compiled into concise, easy-to-follow reports, that non-technical professionals can understand regardless of their own level of technical skill.

How Do Candidates Take An Sql Online Test

Devskiller SQL online tests are accessed remotely and can be taken from anywhere in the world. Saving valuable time spent waiting until both the candidate and the interviewer have time to conduct a test. Remote-testing means once the test invites are sent, the recruiter just has to sit back and wait for the results to come in. Recruiters can even add time limits to each test to make sure theyre not left waiting around.

When it comes to the test itself, candidates have the option of using DevSkillers state-of-the-art in-browser IDE or they can complete the project on their favorite IDE and then clone the project to GIT. If they want, candidates can run unit tests to make sure their solutions work the way they are supposed to.

Read Also: How To Win A Job Interview

About Sql Online Test

SQL is used to manage relational database management system and to perform various operations such as execute queries, retrieve data, edit data, and insert new dataessentially, everything that makes databases interactive and not static. SQL language is regularly used not only by database administrators but also by developers writing data integration scripts, data architect designing, creating, deploying and managing an organization’s data architecture, data analysts looking to set up and run analytical queries. What is an SQL online test? SQL Online Test enables employers, hiring managers and recruiters to assess the basic SQL knowledge of applicants who can write programs, functions, and queries to manage the data and the structure of the database. SQL coding test is designed by experienced Subject Matter Experts to evaluate and hire SQL professionals as per industry standards.

SQL online test helps you to screen the candidates who possess traits as follows:

• Knowledge of SQL syntax and basic language elements

• Ability to write queries used for front-end applications and uncover useful business insights.

• Review query performance and optimizing code

• Design and coding database tables to store the applications data

• Knowledge of data modeling to visualize database structure

• Familiarity with stored procedures, and functions

Test Duration: 20 minutes

Example Amazon Sql Interview Questions

Explain the difference between the types of joins.Hint: Try thinking of the question as a series of Venn diagrams.

2. Purchase History

Consider the following two tables:

+------------+---------------+------------+------------+---------------+|customer_id | purchase_date | product_id | unit_price | unit_purchased|+------------+---------------+------------+------------+---------------+| 10001      | 2021-02-01    | 35525      | 25.00      | 4             || 10002      | 2021-02-02    | 30321      | 10.00      | 8             || 10003     | 2021-02-14    | 35525      | 25.00      | 3             || 10004     | 2021-02-05    | 34826      | 300.00     | 1             || 10005     | 2021-02-23    | 30321      | 10.00      | 5             |.....

+------------+-------------------+|customer_id | registration_date |+------------+-------------------+| 10001      | 2020-10-10        || 10002      | 2021-01-31        || 10003      | 2021-02-10        || 10004      | 2021-01-15        || 10005      | 2021-02-15        |.....

a. Write a query to retrieve data with unique customer ids that made over $100 in purchases during the first week of February.

b. Write a query to retrieve data with unique customer ids that made over $100 in purchases within ten days of registering.

3. Comments Histogram

Consider the following tables:

Write a SQL query to create a histogram of the number of comments per user in the month of January 2020. Assume bin buckets class intervals of one.

Also Check: How To Prepare For A Project Coordinator Interview

What Are Aggregate And Scalar Functions

An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. Following are the widely used SQL aggregate functions:

• AVG – Calculates the mean of a collection of values.
• COUNT – Counts the total number of records in a specific table or view.
• MIN – Calculates the minimum of a collection of values.
• MAX – Calculates the maximum of a collection of values.
• SUM – Calculates the sum of a collection of values.
• FIRST – Fetches the first element in a collection of values.
• LAST – Fetches the last element in a collection of values.

Note: All aggregate functions described above ignore NULL values except for the COUNT function.

A scalar function returns a single value based on the input value. Following are the widely used SQL scalar functions:

• LEN – Calculates the total length of the given field .
• UCASE – Converts a collection of string values to uppercase characters.
• LCASE – Converts a collection of string values to lowercase characters.
• MID – Extracts substrings from a collection of string values in a table.
• CONCAT – Concatenates two or more strings.
• RAND – Generates a random collection of numbers of a given length.
• ROUND – Calculates the round-off integer value for a numeric field .
• NOW – Returns the current date & time.
• FORMAT – Sets the format to display a collection of values.

What Is Sql Server Agent

SQL Server Agent plays an important role in the daily work of SQL Server administrators or DBAs. This is one of the important parts of SQL Server. The aim of the server agent is to easily implement tasks using a scheduler engine that enables the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to store scheduled management task information.

Also Check: How To Prepare For A Social Media Manager Interview

Sql Exercise 9 Write A Complex Query

Write a query that lists coursesâ subject names and the number of students taking the course only if the course has three or more students enrolled.

Table: subjectsubject_id  subject_name       max_score        lecturer----------  ----------         ----------       -----------   11         Math                    130         Christena Solem  12         Computer Science         50         Jaime Pille  13         Biology                 300         Carrol Denmark  14         Geography               220         Yuette Galang  15         Physics                 110         Colton Rather  16         Chemistry               400         Nan Mongeau

What Is The Difference Between Drop And Truncate Commands

If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints.

To create and use the table again in its original form, all the elements associated with the table need to be redefined.

However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.

Read Also: Rpa Business Analyst Interview Questions And Answers

Recommended To My Readers

Are you learning SQL or preparing for the next technical interview and you liked the style of the question in this article?

Then, you should have a look at theSQL coding challengesoffered byStrataScratch, an excellent platform dedicated to people that wish to pursue a career in Data Science , Data Engineeringand Machine Learning.

Sql Interview Question #: Rules To Determine Grades

Find the rules used to determine each grade. Show the rule in a separate column in the format of ‘Score > X AND Score < = Y => Grade = A’ where X and Y are the lower and upper bounds for a grade. Output the corresponding grade and its highest and lowest scores along with the rule. Order the result based on the grade in ascending order.

Link to the question:

The SELECT statement returns the minimum and maximum score per grade. The last column is used for labeling the rules with the concatenate function. Use both MIN and MAX functions to get the following rules:

• Grade A: score > 89 and < = 100
• Grade B: score > 79 and < = 88
• Grade C: score > 69 and < = 79

Of course, the labels have to be formatted as is required.

The output is grouped by and ordered by grade.

Here are the rules.

Recommended Reading: How To Watch Meghan And Harry Interview