What To Know About Sql For An Interview

Given Two Tables Created In The Code Block Below How Would You Write A Query To Fetch Values In Table Fibonacci That Are Not In Table Prime Without Using The Not Keyword Can You Name A Database Technology Where This Is Not Possible

create table fibonacci create table prime insert into fibonacci values    ,    ,    ,    ,    ,     insert into prime values    ,    ,    ,     

SQLite, PostgreSQL, and SQL Server all support the ever useful âexceptâ keyword which can be employed as detailed below

select * from fibonacciexceptselect * from prime 

A popular database technology that does not support âexceptâ is MySQL, which is why it must use the ânot inâ keyword. Note that for Oracle, the âminusâ keyword must be used instead.

Explain Database White Box Testing And Black Box Testing

The white box test 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 test method generally involves interface testing, followed by database integration. It includes:

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

What Are The Differences Between Oltp And Olap Databases

This question is important for an interviewer to gauge whether a candidate understands the different SQL databases that may exist and how they differ from one another. Different use cases often dictate the type of SQL database to use.

OLTP databases are designed for fast queries with strong data integrity. They are typically optimized for handling day-to-day business operations that involve real-time reads and writes.

OLAP databases are optimized for offline analytics and typically involve complex aggregations. Compared to OLTP databases, they often have lower volumes of queries, contain more historical data, and involve significantly fewer edits.

If we illustrate this with a web application, an OLTP database probably handles the metadata for the web application. User activity and data is stored and retrieved for the OLTP database. OLAP databases will probably store the same type of data but be leveraged to understand key metrics such as user retention and behavior.

Your response to this question will be much more impactful if you provide examples of various OLTP databases and OLAP databases that you may have used.

Don’t Miss: How To Watch Oprah Meghan Markle Interview

What Is The Complexity Of The Ssrs Reports You Have Built

SQL Server Reporting Services is a solution by Microsoft that allows businesses to create, publish and manage reports for an SQL Server. Reports can be paginated so theyre easy to send via email, have a responsive layout so theyre easy to view from mobile devices, plus, you can also view reports on any web browser through a web portal.

The solution has been built so that even administrators with no SQL experience can create basic reports, using a simple report wizard. Youll therefore have to think of some of the more complex SSRS reports you have created to really impress your interviewer. For example, as well as basic tables, have you created more complex data visualisations, with charts and maps? Have you also added KPIs and manually added parameters perhaps?

Explain The Difference Between An Inner Join And Outer Join Using An Example

An inner join is when you combine rows from two tables and create a result set based on the predicate, or joining condition. The inner join only returns rows when it finds a match in both tables. An outer join will also return unmatched rows from one table if it is a single outer join, or both tables if it is a full outer join. A solid example of this will clearly illustrate the difference and demonstrate how well the developer understands joins.

You May Like: How To Reschedule A Phone Interview For Food Stamps

Logic Based Sql Interview Questions

Logic based SQL interview questions are very tricky. They aren’t really based on real life examples so much as putting the trickiness of algorithms and data structure interviews into SQL questions. This is exemplified on sites such as LeetCode, where you’ll see a lot of interview questions that aren’t very practical for real life scenarios.

Q1. Write a query to create a new table, named flight routes, that displays unique pairs of two locations.

Example: Duplicate pairs from the flights table, such as Dallas to Seattle and Seattle to Dallas, should have one entry in the flight routes table.

flights table:

Q2. Let’s say we have a table with an id and name field. The table holds over 100 million rows and we want to sample a random row in the table without throttling the database.

Write a query to randomly sample a row from this table.

Hint: We know that the RAND function actually returns a floating-point between 0 and 1. So if we were to run this function, SELECT RAND, we would get a random decimal point to some Nth degree of precision. RAND essentially allows us to seed a random value. How can we use this to select a random row quickly?

users table:

What Is A Recursive Stored Procedure

A stored procedure that calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required. Some SQL programming languages limit the recursion depth to prevent an infinite loop of procedure calls from causing a stack overflow, which slows down the system and may lead to system crashes.

DELIMITER $$     /* Set a new delimiter =>  $$ */CREATEPROCEDURE calctotal BEGINDECLARE score INTDEFAULTNULL    /* Set the default value =>  "score" */SELECT awards FROM achievements   /* Update "score" via SELECT query */WHERE id = number INTO score IF score ISNULLTHENSET total =0    /* Termination condition */ELSECALL calctotal    /* Recursive call */SET total = total + score    /* Action after recursion */END IF END $$     /* End of procedure */DELIMITER       /* Reset the delimiter */

Don’t Miss: How To Answer It Interview Questions

What Approach Do You Take To Troubleshoot Database Discrepancies

A successful SQL professional needs to have a logical and systematic process to troubleshooting database problems. The goal of the question is to evaluate the candidate’s ability to follow an effective troubleshooting strategy. What to look for in an answer:

• Critical thinking and analytical skills to assess and diagnose the problem
• Problem-solving skills to develop viable solutions
• Solid foundational knowledge of SQL

Example:

“How I troubleshoot a database discrepancy varies based on the type of problem experienced. However, I gather as much information as possible in every case to understand the full scope of the problem. Then I create a theory and test it by…”

How To Change A Table Name In Sql

This is the command to change a table name in SQL:

ALTER TABLE table_name

RENAME TO new_table_name

We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.

For example, if we want to change the employee table to employee_information, this will be the command:

ALTER TABLE employee

Don’t Miss: What Are Some Good Interview Questions To Ask A Candidate

Q5 What Is The Primary Key

Ans. A primary key constraint uniquely identifies each row/record in a database table. Primary keys must contain unique values. Null value and duplicate values are not allowed to be entered in the primary key column. A table can have only one primary key. It can consist of single or multiple fields.

What Is The Difference Between Change Data Capture And Change Tracking

Change Data Capture captures both the data before and after changes were made whereas Change Tracking only keeps changed data. It does not keep any information about older data that was changed with the new one. Change Data Capture uses an asynchronous mechanism to track changes it means changes are available to post DML operations whereas Change Tracking uses a synchronous mechanism to capture changes and because of this all-change information is available immediately.

Read Also: How To Conduct A Technical Interview

How Is A Full

Full-text search and LIKE predicate are designed to fulfill different requirements. Full-text search is more efficient and powerful while running complex searching operations with the help of their in-built predicates CONTAINS and FREETEXT and the rowset-valued functions CONTAINSTABLE and FREETEXTTABLE with a SELECT statement whereas LIKE predicates work efficiently for character-based search patterns using liner scan process and find all matching terms. LIKE predicate works inefficiently when you have to search a string or character in very large unstructured text data whereas full-text search will return its output very quickly on such data sets.

You need to install a full-text search component to use this feature whereas there is no such requirement to use LIKE predicate.

Strategies For The Live Sql Interview

Let’s go over the common strategies when tackling SQL interview questions.

1.Repeat the problem statement

When presented with a SQL question, listen carefully to the problem description and repeat back what you think the crux of the problem is. The interviewer can then help verify if your understanding is correct.

2. Understand the edge cases

If time permits, write out a base case and an edge case to show that you understand the problem. For example: if the interviewer asks you to pull the average number of events per user per day, write out an example scenario where you’re verifying this metric.

Do duplicate events matter? Are we looking at distinct users? These are questions we need to clarify.

3. Try working backwards if the problem is tricky

Sketching out what the output of the SQL question will look like is a great strategy towards solving the problem. Usually, if I know what the end output table is supposed to look like, I can work backwards from there on what functions need to be applied before.

For example, if the output looks like this:

date        | average events per user------------+-----------------------2021-12-01  |  3.52021-12-02  |  4.0

I know that the table before this aggregation would have to look something like this.

date       | event | user_id-----------+-------+--------2021-12-01 | click | 12021-12-01 | view  | 1......

And then, I can figure out what functions I should use to get to my desired output!

4. Pattern match to different functions

You May Like: What Can You Ask In An Interview

How To Prepare For A Sql Server Dba Interview And Questions

Categories: 0

So, you have an interview lined up for a sweet new gig as a SQL Server database administrator . What interview questions will you be asked? How can you make sure you ace the interview? What will make you stand out from the other candidates? There are no concrete answers, because it depends. However, you can count on at least two major components of your interview a technical component and a non-technical component, often focusing on soft skills.

How Can You Patch The Always On Availability Group With Minimum Downtime

We can follow the rolling upgrade approach to patch availability group instances. First, we will patch the DR replica hosted in another data center. Once the DR replica will be patched, next we will change failover mode to manual in case it is automatic, and patch the secondary replica hosted in the primary datacenter. Reboot this server and once the secondary replica will come online, initiate failover of primary replica to newly patched secondary replica to make this replica primary. Now your previous primary replica is secondary, go ahead and patch this replica. Once patching will complete, reboot the server and fail back SQL Server instance to this instance to make it primary as per your initial design.

You May Like: How To Succeed In An Interview

What Is A Default Constraint

Constraints are used to specify some sort of rules for processing data and limiting the type of data that can go into a table. Now, lets understand the default constraint.

The default constraint is used to define a default value for a column so that the default value will be added to all the new records if no other value is specified. For example, if we assign a default constraint for the E_salary column in the below table and set the default value as 85000, then all the entries of this column will have a default value of 85000 unless no other value has been assigned during the insertion.

Now, lets see how to set a default constraint. We will start off by creating a new table and adding a default constraint to one of its columns.

Code:

create table stu1, s_marks int default 50)select *stu1

Output:

Now, we will insert the records.

Code:

insert into stu1 valuesinsert into stu1 valuesinsert into stu1 valuesselect *from stu1

Output:

Do You Know What Is Resource Semaphore Wait Type

RESOURCE SEMAPHORE is an internal algorithm in SQL Server that grants requested memory to SQL Server queries based on their compiled execution plan to run and execute on the instance. If there is memory pressure or the query does not get their requested memory to execute then that query will wait with RESOURCE SEMAPHORE wait type until memory will be granted to execute the query. RESOURCE SEMAPHORE works on a first come first serve basis so all new queries will be in the queue and waiting queries will get their request memory based on their arrival in the queue.

Don’t Miss: What Are Some Job Interview Questions

What Are The Important Topics In Sql For An Interview

The important topics in SQL for an interview may relate to the SQL database and its key structures. You may have to explain the purpose and use of SQL databases, their structure and ways to manipulate the tables and data to get desired results. It can benefit you to master SQL concepts like SQL Commands, SQL Joins, SQL Constraints, Data Modelling, Select Queries, Relational Keys, Table Types, Data Definition Language and Data Manipulation Language .

Related:11 Common SQL Interview Questions and Answers

Sql Exercise 1 Write A Statement

Write an SQL statement that lists school names, student names, and their cities only if the school and the student are in the same city and the student is not from New York.

Table: schoolschool_id       school_name                    city  -----------     ------------------       ----------  1               Stanford                   Stanford    2               University of Cali    San Francisco       3               Harvard University         New York      4               MIT                          Boston       5               Yale                      New Haven        

Table: studentstudent_id   student_name  city           school_id-----------  ------------  ----------   -----------1001         Peter Brebec  New York              11002         John Goorgy   San Francisco         22003         Brad Smith    New York              31004         Fabian Johns  Boston                51005         Brad Cameron  Stanford              11006         Geoff Firby   Boston                51007         Johnny Blue   New Haven             21008         Johse Brook   Miami                 2

Also Check: What To Prepare For A Phone Interview

What Is Wrong With The Below

SELECT gender, AVG FROM employee WHERE AVG> 30 GROUP BY gender

When we execute this command, we get the following error:

Msg 147, Level 16, State 1, Line 1

Aggregation may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, the column being aggregated is an outer reference.

Msg 147, Level 16, State 1, Line 1Invalid column name gender.

This basically means that whenever we are working with aggregate functions and we are using GROUP BY, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we should use the HAVING clause.

Also, when we are using the HAVING clause, GROUP BY should come first, and HAVING should come next.

select e_gender, avg from employee group by e_gender having avg> 30

Output:

Explain Subqueries And Their Different Types

Subqueries are queries nested into a main, larger query. In SQL, the subquery types are:

• Single-row subquery Returns one or zero rows as a result.
• Multiple-row subquery Returns one or more rows as a result.
• Multiple-column subquery Returns one or more columns as a result.
• Correlated subquery References one or more columns in the main query.
• Nested subquery A subquery within another subquery.

One of the important things about subqueries is the SQL clauses you can use them in:

• HAVING

Also Check: How To Get Interviewed On The News

What Is Rdbms How Is It Different From Dbms

RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

What Are The Different Types Of Sql Operators

Operators are the special keywords or special characters reserved for performing particular operations. They are also used in SQL queries. We can primarily use these operators within the WHERE clause of SQL commands. It’s a part of the command to filters data based on the specified condition. The SQL operators can be categorized into the following types:

Also Check: How To Thank An Employer For An Interview

How Has Your Training Prepared You For An Sql Position At Our Company

You may require an SQL professional to have specific academic training, such as a diploma or bachelor’s degree. The goal of the question is to evaluate the candidate’s ability to adapt their knowledge into hands-on experience. What to look for in an answer:

• Meets academic and training requirements outlined in the job description
• Ability to adapt knowledge to the benefit of hands-on work
• Critical thinking to understand the SQL role within your company

Example:

“My SQL training certificate set me up with a solid foundation in designing, developing, and testing SQL databases. I have the knowledge to jump into the position and immediately make an impact by testing and debugging your database.”