SQL Interview Questions

What's a column alias and how do you create one?

Aliases are used to give your column another name. You create an alias with the AS keyword.

What is the difference between nvarchar and varchar?

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage.

What are the ACID properties?

Atomicity, Consistency, Isolation, and Durability. ACID is an acronym that refers to the set of 4 key properties that define a transaction: Atomicity, Consistency, Isolation, and Durability.

What are the benefits of a stored procedure?

By grouping SQL statements, a stored procedure allows them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time.

Give me an example of a DDL statement? What does it do?

CREATE DATABASE. It creates an empty database shell.

What is the syntax used to create a function?

CREATE OR ALTER FUNCTION function_name(parameters) RETURNS data_type AS BEGIN SQL_statements RETURN return_value END GO

What's the syntax for creating a stored procedure?

CREATE PROCEDURE

What is the syntax for creating a view?

CREATE VIEW

What's a comment in SQL Server?

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

Why would I create a schema?

It helps us to create a logical grouping of objects such as tables, stored procedures, and functions.

What does the WHERE clause do?

It restricts what data is returned. Acts as filter in a SELECT statement. This clause specifies a condition that can be evaluated as either true or false.

What does the FROM keyword mean?

It specifies the object you're going to search. Almost always a table but it can be a view.

What type of statement is CREATE VIEW?

It's a DDL statement or data definition language.

What is a logical operator?

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

What is a view?

A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables. It does not hold any data and does not exist physically in the database. Like a SQL table, the view name should be unique in a database. It contains a set of predefined SQL queries to fetch data from the database. It can contain database tables from single or multiple databases as well.

What is a clustered index?

A clustered index is an index which defines the physical order in which table records are stored in a database. Since there can be only one way in which records are physically stored in a database table, there can be only one clustered index per table.

What is a data type?

A data type define what a column will hold.

What is the definition of a database?

A database is a collection of objects.

How many files must every relational database have?

A database must have two files. A data file to hold the data and a log file to hold a record of every transaction. A record of the transaction is written to the log file before the data is inserted into the database. This is a requirement for all relational databases.

A database is a collection of objects but that isn't what a database does? What does a database do?

A database processes transactions.

What is a derived column?

A derived column in SQL is a column that is created by performing a calculation or transformation on one or more existing columns in a table. Derived columns let you move the processing of an expression from the target instance to the source instance.

What is a natural key?

A natural key is a single column or set of columns that uniquely identifies a single record in a table, where the key columns are made up of real data. When I say "real data" I mean data that has meaning and occurs naturally in the world of data. A natural key is a column value that has a relationship with the rest of the column values in each data record. Here are some examples of natural keys values: Social Security Number, ISBN, and TaxId.

What does an int hold?

A number.

What is a primary Key?

A primary key is used to ensure data in the specific column is unique. It is a column cannot have NULL values.

What is a stored procedure?

A stored procedure is a prepared SQL code (query) that you can save, so the code can be reused over and over again. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

What is a surrogate key?

A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database . The surrogate key is not derived from application data, unlike a natural (or business) key which is derived from application data.

What is a system database?

A system database is a database created by the vendor that's often read only for internal tasks.

What is a table valued function?

A table function, also called a table-valued function (TVF), is a user-defined function that returns a table.

What is a user defined database?

A user defined database is a database you create. Most relational database systems have system databases.

What is a NULL value?

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

What is an operator? Can you give me an example of one?

A way of comparing values. For example, an equal sign is an equality operator. Less than and greater than are also example of operators.

What is DDL?

Data Definition Language

What is DML?

Data Manipulation Language: Sets and changes the data values within tables and rows.

How do you create a comment?

Example: /***This is an example. Text turns green in SQL.***/

What is a schema?

In a SQL database, a schema is a list of logical structures of data. The schema describes how data should be organized and how the table relationships should be built within a particular database.

What is an index?

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

How does the view differ from the table?

It does not hold any data and does not exist physically in the database.

How many NULL values can a primary key have?

None

Operators are primary used where?

Operators are primarily used in the WHERE clause to perform operations.

What's a parameter?

Parameters are used to exchange data between stored procedures and functions and the application or tool that called the stored procedure or function: Input parameters allow the caller to pass a data value to the stored procedure or function.

How do parenthesis help us in the WHERE clause?

Parentheses tell SQL Server to ignore the order of evaluation and assess what is in the parentheses first then evaluate the second part of the WHERE clause.

Name the four core DML statements.

SELECT, INSERT, UPDATE and DELETE

In SQL Server, what are the two methods of authentication?

SQL Server and Windows

What is SQL Server?

SQL Server is a relational database system created by Microsoft. It's one of the top relational database systems used in the real-world.

What is a function? Can you give me an example of a function in SQL?

SQL server functions are sets of SQL statements that execute a specific task.

What is SQL?

SQL stands for Structured Query Language. SQL lets you access and manipulate databases.

What does the USE statement do?

Sets the database context.

What does SQL stand for?

Structured Query Language

What are the two primary kinds of data in the world?

Structured and Unstructured

What is structured data?

Structured data is data that has been predefined and formatted to a set structure before being placed in data storage, which is often referred to as schema-on-write.

What does the count function do?

The COUNT() function returns the number of rows that matches a specified criterion.

What does an INNER JOIN do?

The INNER JOIN keyword selects records that have matching values in both tables.

What does the LIKE operator do?

The LIKE operator is used to compare a value to similar values using wildcard operators.

What does the asterisk do in a SQL statement?

The asterisk or star symbol ( * ) means all columns.

How do you get data into a table? What DML statement is used?

The keyword INSERT is used to put data into a table.

What is a result set?

The output produced when a SQL statement is executed.

What is an identity seed?

The seed is the value of the first row loaded into the table.

What is the difference between varchar and char?

The short answer is that VARCHAR is variable length, while CHAR is fixed length.

What are the most common types of joins?

There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN.

What is a scalar function?

These functions return a single value, such as a string, a number, or a date. Examples of scalar functions include LENGTH, which returns the length of a string, and AVG, which returns the average of a set of numbers.

How are tables related to one another in relational databases?

They are related based on keys.

Why should you create comments?

To let others know what we're doing or things they should be aware of.

What is Transact-SQL?

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements. These additional features make Transact-SQL Turing complete. Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

How do you create a database?

With the CREATE DATABASE keyword.