SQL Questions For Interview with Answers 2020

• SQL Questions For Interview: What is SQL?

  Answer: SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database etc. SQL is an ANSI(American National Standards Institute) standard. Using SQL, we can do many things, for example – we can execute queries, we can insert records in a table, we can update records, we can create a database, we can create a table, we can delete a table etc.

• SQL Questions For Interview: What is the difference between SQL and MySQL?

  Answer: SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

• SQL Questions For Interview: What is a Database?

  Answer: A Database is defined as a structured form of data which is stored in a computer or data in an organised manner and can be accessed in various ways. It is also the collection of schemas, tables, queries, views etc. Database helps us in easily storing, accessing and manipulation of data held in a computer. The Database Management System allows a user to interact with the database.

• SQL Questions For Interview: Does SQL support programming language features ?

  Answer: It is true that SQL is a language but it does not support programming as it is not a programming language, it is a command language. We do not have conditional statements in SQL like for loops or if..else, we only have commands which we can use to query, update , delete etc. data in the database. SQL allows us to manipulate data in a database.

• SQL Questions For Interview: What is subquery?

  Answer: A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.

• SQL Questions For Interview: What are the types of subquery?

  Answer: There are two types of subquery – Correlated and Non-Correlated.
  A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.
  
  A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.

• SQL Questions For Interview: What is a stored procedure?

  Answer: Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

• SQL Questions For Interview: What is a trigger?

  Answer: A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.
  Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.

• SQL Questions For Interview: What is Auto Increment?

  Answer: Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
  Mostly this keyword can be used whenever PRIMARY KEY is used.

• SQL Questions For Interview: What is the difference between Cluster and Non-Cluster Index?

  Answer: 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.  

What is user defined functions

What are all types of user defined functions?

Three types of user defined functions are.
• Scalar Functions.
• Inline Table valued functions.
• Multi statement valued functions.
Scalar returns unit, variant defined the return clause. Other two types return table as a return.

What is collation?

Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.
ASCII value can be used to compare these character data.

What are all different types of collation sensitivity?

Following are different types of collation sensitivity -.
• Case Sensitivity – A and a and B and b.
• Accent Sensitivity.
• Kana Sensitivity – Japanese Kana characters.
• Width Sensitivity – Single byte character and double byte character.

Advantages and Disadvantages of Stored Procedure?

Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.
Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

What is Datawarehouse?

Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.

What are local and global variables and their differences?

Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created whenever that function is called.
Global variables are the variables which can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

What is the difference between BETWEEN and IN operators in SQL?

BETWEEN

The BETWEEN operator is used to fetch rows based on a range of values.
For example,
SELECT * FROM Students
WHERE ROLL_NO BETWEEN 20 AND 30;
This query will select all those rows from the table Students where the value of the field ROLL_NO lies between 20 and 30.

IN
The IN operator is used to check for values contained in specific sets.
For example,
SELECT * FROM Students
WHERE ROLL_NO IN (20,21,23);
This query will select all those rows from the table Students where the value of the field ROLL_NO is either 20 or 21 or 23.

Write an SQL query to find names of employee start with ‘A’?

The LIKE operator of SQL is used for this purpose. It is used to fetch filtered data by searching for a particular pattern in where clause.
The Syntax for using LIKE is,
SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern;
LIKE: operator name
pattern: exact value extracted from the pattern to get related data in
result set.
The required query is:
SELECT * FROM Employees WHERE EmpName like ‘A%’ ;
You may refer to this article on WHERE clause for more details on LIKE operator.

What is the difference between CHAR and VARCHAR2 datatype in SQL?

Both of these datatypes are used for characters but varchar2 is used for character strings of variable length whereas char is used for character strings of fixed length. For example, if we specify the type as char(5) then we will not be allowed to store string of any other length in this variable but if we specify the type of this variable as varchar2(5) then we will be allowed to store strings of variable length, we can store a string of length 3 or 4 or 2 in this variable.

Name different types of case manipulation functions available in SQL.

There are three types of case manipulation functions available in SQL. They are,
• LOWER: The purpose of this function is to return the string in lowercase. It takes a string as argument and returns the string by converting it into lower case.
Syntax:
• LOWER(‘string’)
• UPPER:The purpose of this function is to return the string in uppercase. It takes a string as argument and returns the string by converting it into uppercase.
Syntax:
• UPPER(‘string’)
• INITCAP:The purpose of this function is to return the string with first letter in uppercase and rest of the letters in lowercase.
Syntax:
• INITCAP(‘string’)

What do you mean by data definition language?

Data definition language or DDL allows to execute queries like CREATE, DROP and ALTER. That is, those queries which define the data.

What do you mean by data manipulation language?

Data manipulation Language or DML is used to access or manipulate data in the database.
It allows us to perform below listed functions:
• Insert data or rows in database
• Delete data from database
• Retrieve or fetch data
• Update data in database.

What is the difference between primary key and unique constraints?

Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains. The primary key creates the cluster index automatically but the Unique key does not.

What is a view in SQL?

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.
The CREATE VIEW statement of SQL is used for creating Views.
Basic Syntax:
CREATE VIEW view_name AS
SELECT column1, column2…..
FROM table_name
WHERE condition;
view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows

What is a Foreign Key?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.
CREATE TABLE Students (
SET fname = ‘Captain’, lname = ‘America’
WHERE student_id = 1;

What is the SELECT statement?

SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set.
SELECT * FROM myDB.students;

What are some common clauses used with SELECT query in SQL?

Some common SQL clauses used in conjuction with a SELECT query are as follows:
WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).
SELECT *
FROM myDB.students
WHERE graduation_year = 2019
ORDER BY studentID DESC;
GROUP BY clause in SQL is used to group records with identical data and can be used in conjuction with some aggregation functions to produce summarized results from the database.
HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since WHERE clause cannot filter aggregated records.
SELECT COUNT(studentId), country
FROM myDB.students
WHERE country != “INDIA”
GROUP BY country
HAVING COUNT(studentID) > 5;

What are UNION, MINUS and INTERSECT commands?

The UNION operator combines and returns the result-set retrieved by two or more SELECT statements.
The MINUS operator in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.
The INTERSECT clause in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.
Certain conditions need to be met before executing either of the above statements in SQL –
Each SELECT statement within the clause must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement should necessarily have the same order
SELECT name FROM Students
FROM students
WHERE first_name LIKE ‘__‘

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.

What is a Cursor?

A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

What is a relationship and what are they?

Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:.
• One to One Relationship.
• One to Many Relationship.
• Many to One Relationship.
• Self-Referencing Relationship.

What is CLAUSE?

SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.
Example – Query that has WHERE condition
Query that has HAVING condition.  

What is an ALIAS command?

ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.
Example-.
Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID
Here, st refers to alias name for student table and Ex refers to alias name for exam table.

What are aggregate and scalar functions?

Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.
Example -.
Aggregate – max(), count – Calculated with respect to numeric.
Scalar – UCASE(), NOW() – Calculated with respect to strings.

How can you create an empty table from an existing table?

Example will be -.
Select * into studentcopy from student where 1=2
Here, we are copying student table to another table with the same structure with no rows copied.

How to fetch common records from two tables?

Common records result set can be achieved by -.
Select studentID from student. INTERSECT Select StudentID from Exam

How to fetch alternate records from a table?

Records can be fetched for both Odd and Even row numbers -.
To display even numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
To display odd numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
from (Select rowno, studentId from student) where mod(rowno,2)=1.[/sql]

How to select unique records from a table?

Select unique records from a table by using DISTINCT keyword.
Select DISTINCT StudentID, StudentName from Student.

What is the command used to fetch first 5 characters of the string?

There are many ways to fetch first 5 characters of the string -.
Select SUBSTRING(StudentName,1,5) as studentname from student
Select LEFT(Studentname,5) as studentname from student

What are different types of statements supported by SQL?

1.DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop.
Some of the DDL Commands are listed below
CREATE: It is used for creating the table.
CREATE TABLE table_name
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
ALTER: The ALTER table is used for modifying the existing table object in the database.
ALTER TABLE table_name
ADD column_name datatype
OR
ALTER TABLE table_name
DROP COLUMN column_name

2.DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Delete.
The Select statement is used as partial DML statement that is used to select all or relevant records in the table.

3.DCL (Data Control Language): These statements are used to set privileges such as Grant and Revoke database access permission to the specific user.

Why do we use SQL constraints? Which constraints we can use while creating a database in SQL?

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.
Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE statement.
There are 5 major constraints are used in SQL, such as
• NOT NULL: That indicates that the column must have some value and cannot be left null
• UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column
• PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one column to identify the particular record with a unique identity.
• FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
• CHECK: It is used to ensure whether the value in columns fulfills the specified condition

What are transactions and their controls?

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database come from transactions.
In simple words, we can say that a transaction means a group of SQL queries executed on database records.
There are 4 transaction controls such as
• COMMIT: It is used to save all changes made through the transaction
• ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before
• SET TRANSACTION: Set the name of transaction
• SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

What are properties of the transaction?

Properties of the transaction are known as ACID properties, such as
• Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone
• Consistency: Ensures that all changes made through successful transaction are reflected properly on database
• Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other
• Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure

How many Aggregate Functions are available there in SQL?

Answer: There are 7 aggregate functions we use in SQL
• AVG(): Returns the average value from specified columns
• COUNT(): Returns number of table rows
• MAX(): Returns largest value among the records
• MIN(): Returns smallest value among the records
• SUM(): Returns the sum of specified column values
• FIRST(): Returns the first value
• LAST(): Returns Last value

What are Scalar Functions in SQL?

Scalar Functions are as follows
• UCASE(): Converts the specified field in upper case
• LCASE(): Converts the specified field in lower case
• MID(): Extracts and returns character from the text field
• FORMAT(): Specifies the display format
• LEN(): Specifies the length of the text field
• ROUND(): Rounds up the decimal field value to a number

How we can update the view?

SQL CREATE and REPLACE can be used for updating the view.
Following query syntax is to be executed to update the created view
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

How many types of Privileges are available in SQL?

There are two types of privileges used in SQL, such as
• System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.
• Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.

What is the difference between NVL function, IFNULL function, and ISNULL function?

These three functions work in the same way. These functions are used to replace NULL value with another value. Oracle developers use NVL function, MySQL developers use IFNULL function and SQL Server developers use ISNULL function.
Assume, some of the values in a column are NULL.
If you run below statement, you will get result as NULL
SELECT col1 * (col2 + col3) FROM Table1
Suppose any of the value in col3 is NULL then as I said your result will be NULL.
To overcome this we use NVL() function, IFNULL() function, ISNULL() Function.
ORACLE:
SELECT col1 * (col2 + NVL(col3,0)) FROM Table1
MySQL:
SELECT col1 * (col2 + IFNULL(col3,0)) FROM Table1
Also, you can use the COALESCE() function
SELECT col1 * (col2 + COALESCE(col3,0)) FROM Table1
SQL Server:
SELECT col1 * (col2 + ISNULL(col3,0)) FROM Table1

How do you update F as M and M as F from the below table TestTable?

| Name | Gender |
| John | M |
| Emma | F |
| Mark | M |
| Anne | F |
By using the below syntax we could achieve the output as required.
UPDATE TestTable SET Gender = CASE Gender WHEN ‘F’ THEN ‘M’ ELSE ‘F’ END

How to get each name only once from an employee table?

By using the DISTINCT keyword, we could get each name only once.
SELECT DISTINCT employee_name FROM employee_table;

How to rename a column in the output of SQL query?

By using SQL AS keyword
SELECT column_name AS new_name FROM table_name;

What is the order of SQL SELECT?

Order of SQL SELECT statement is as follows
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

How to display the current date in SQL?

In SQL, there is a built-in function called GetDate() which helps to return the current date.
1 SELECT GetDate();

What are the types of DBMS?

There are two types of DBMS
Relational Database Management System (RDBMS)
Non-Relational Database Management System

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is a database management system (DBMS) that is based on the relational model. Data from a relational database can be accessed using Structured Query Language (SQL)

What are the popular Database Management Systems in the IT Industry?

Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.,

What are the different types of SQL commands?

SQL commands are segregated into the following types:
• DDL – Data Definition Language
• DML – Data Manipulation Language
• DQL – Data Query Language
• DCL – Data Control Language
• TCL – Transaction Control Language

What are the different DDL commands in SQL?

DDL commands are used to define or alter the structure of the database.
• CREATE: To create databases and database objects
• ALTER: To alter existing database objects
• DROP: To drop databases and databases objects
• TRUNCATE: To remove all records from a table but not its database structure
• RENAME: To rename database objects

What are the different DML commands in SQL?

DML commands are used for managing data present in the database.
• SELECT: To select specific data from a database
• INSERT: To insert new records into a table
• UPDATE: To update existing records
• DELETE: To delete existing records from a table

What are the different DCL commands in SQL?

DCL commands are used to create roles, grant permission and control access to the database objects.
• GRANT: To provide user access
• DENY: To deny permissions to users
• REVOKE: To remove user access

What are the different TCL commands in SQL?

TCL commands are used to manage the changes made by DML statements.
• COMMIT: To write and store the changes to the database
• ROLLBACK: To restore the database since the last commit

What is the difference between Rename and Alias?

‘Rename’ is a permanent name given to a table or column
‘Alias’ is a temporary name given to a table or column.

How to avoid duplicate records in a query?

The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.

What is the difference between UNIQUE and PRIMARY KEY constraints?

There should be only one PRIMARY KEY in a table whereas there can be any number of UNIQUE Keys.
PRIMARY KEY doesn’t allow NULL values whereas Unique key allows NULL values.

What is the difference between NULL value, Zero, and Blank space?

As I mentioned earlier, Null value is field with no value which is different from zero value and blank space.
Null value is a field with no value.
Zero is a number
Blank space is the value we provide. The ASCII value of space is CHAR(32).

How to Test for NULL Values?

A field with a NULL value is a field with no value. NULL value cannot be compared with other NULL values. Hence, It is not possible to test for NULL values with comparison operators, such as =, <, or <>. For this, we have to use the IS NULL and IS NOT NULL operators.
SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

What is a CHECK constraint?

A CHECK constraint is used to limit the value that is accepted by one or more columns.
E.g. ‘Age’ field should contain only the value greater than 18.
CREATE TABLE EMP_DETAILS(EmpID int NOT NULL, NAME VARCHAR (30) NOT NULL, Age INT CHECK (AGE > 18), PRIMARY KEY (EmpID));

What is a DEFAULT constraint?

DEFAULT constraint is used to include a default value in a column when no value is supplied at the time of inserting a record.
Define the SELECT INTO statement.
The SELECT INTO statement copies data from one table into a new table. The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.
SELECT * INTO newtable FROM oldtable WHERE condition;

What is the difference between Delete, Truncate and Drop command?

The difference between the Delete, Truncate and Drop command is
• Delete command is a DML command, it is used to delete rows from a table. It can be rolled back.
• Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cant be rolled back.
• Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows). All the tables’ rows, indexes, and privileges will also be removed.

How to get unique records from a table?

Some of the ways to fetch the first 5 characters of a string are as follows:

SELECT RIGHT(EmpName,5) AS EmployeeName FROM Employee
SELECT SUBSTRING(EmpName,1,5) AS EmployeeName FROM Employee

How to add new Employee details in an Employee_Details table with the Answer: following details Employee_Name: John, Salary: 5500, Age: 29?

INSERT into Employee_Details (Employee_Name, Salary, Age) VALUES (‘John’, 5500 , 29);

How to add a column ‘Salary’ to a table Employee_Details?

ALTER TABLE Employee_Details ADD (Salary);

What is SQL Injection

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have access to sensitive data and perform administrative tasks on databases.
For Example: SELECT column_name(s) FROM table_name WHERE condition;

What is SQL Sandbox in SQL Server?

SQL Sandbox is the safe place in SQL Server Environment where untrusted scripts are executed. There are 3 types of SQL sandbox, such as
• Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory and cannot create files.
• External Access Sandbox: User can have access to files without having a right to manipulate the memory allocation.
• Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.
• How to select random rows from a table?
Answer: Using a SAMPLE clause we can select random rows.
• Example:
• SELECT * FROM table_name SAMPLE(10);

What are STUFF and REPLACE function?

STUFF Function: This function is used to overwrite existing character or inserts a string into another string. Syntax:
STUFF(string_expression,start, length, replacement_characters)
where,
string_expression: it is the string that will have characters substituted
start: This refers to the starting position
length: It refers to the number of characters in the string which are substituted.
replacement_string: They are the new characters which are injected in the string.
REPLACE function: This function is used to replace the existing characters of all the occurrences. Syntax:
REPLACE (string_expression, search_string, replacement_string)
Here every search_string in the string_expression will be replaced with the replacement_string.
So this brings us to the end of the SQL interview questions blog. I hope this set of SQL Interview Questions will help you ace your job interview.

List the ways in which Dynamic SQL can be executed?

Following are the ways in which dynamic SQL can be executed:
• Write a query with parameters.
• Using EXEC.
• Using sp_executesql.

What are the various levels of constraints?

Constraints are the representation of a column to enforce data entity and consistency. There are two levels of a constraint, namely:
• column level constraint
• table level constraint

What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?

HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.

What Is the Difference Between OSQL And Query Analyzer?

OSQL is the command-line tool that executes the query and displays the result same a query analyzer but the query analyzer is graphical and OSQL is a command-line tool. OSQL is quite useful for batch processing or executing remote queries.

What is a live lock?

A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

How SQL Server executes a statement with nested subqueries?

When SQL Server executes a statement with nested subqueries, it always executes the innermost query first. This query passes its results to the next query and so on until it reaches the outermost query. It is the outermost query that returns a result set.

How do you add a column to an existing table?

ALTER TABLE Department ADD (AGE, NUMBER);
Which statement do you use to eliminate padded spaces between the month and day values in a function TO_CHAR(SYSDATE,’Month, DD, YYYY’) ?
To remove padded spaces, you use the “fm” prefix before the date element that contains the spaces. TO_CHAR(SYSDATE,’fmMonth DD, YYYY’)

What are Checkpoint In SQL Server?

When we done operation on SQL SERVER that is not commited directly to the database.All operation must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint are the point which alert Sql Server to save all the data to main database if no check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER.When we stop the SQL Server it will take long time because Checkpoint is also fired.

Why we use OPENXML clause?

OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the DB.

What is the use of SIGN function?

SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.
Example –
SIGN(-35) returns -1

Can we check locks in database? If so, how can we do this lock check?

 Yes, we can check locks in the database. It can be achieved by using in-built stored procedure called sp_lock.

What is Bulkcopy in SQL?

Bulkcopy is a tool used to copy large amount of data from Tables. This tool is used to load large amount of data in SQL Server.

What is the use of SET NOCOUNT ON/OFF statement?

By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).

What is UPDATE_STATISTICS command?

UPDATE_STATISTICS command is used to update the indexes on the tables when there is a large amount of deletions or modifications or bulk copy occurred in indexes.

What are Magic Tables in SQL Server?

During DML operations like Insert, Delete, and Update, SQL Server creates magic tables to hold the values during the DML operations. These magic tables are used inside the triggers for data transaction.

What is the difference between SUBSTR and CHARINDEX in the SQL Server?

The SUBSTR function is used to return specific portion of string in a given string. But, CHARINDEX function gives character position in a given specified string.
SUBSTRING(‘Smiley’,1,3)
Gives result as Smi
CHARINDEX(‘i’, ‘Smiley’,1)

How can you create a login?

You can use the following command to create a login
CREATE LOGIN MyLogin WITH PASSWORD = ‘123’;

What is ISNULL() operator?

ISNULL function is used to check whether value given is NULL or not NULL in sql server. This function also provides to replace a value with the NULL.

What is the use of FOR Clause?

FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or in browser.

What will be the maximum number of index per table?

For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

What is the difference between COMMIT and ROLLBACK?

Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.

What is the difference between varchar and nvarchar types?

Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.

What is the command used to Recompile the stored procedure at run time?

Stored Procedure can be executed with the help of keyword called RECOMPILE.
Example:
Exe <SPName> WITH RECOMPILE

How to delete duplicate rows in SQL Server?

Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.

Where are SQL Server user names and passwords stored in SQL Server?

User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.

What is the difference between GETDATE and SYSDATETIME?

Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.

How data can be copied from one table to another table?

INSERT INTO SELECT
This command is used to insert data into a table which is already created.
SELECT INTO
This command is used to create a new table and its structure and data can be copied from existing table.

What is TABLESAMPLE?

TABLESAMPLE is used to extract sample of rows randomly that are all necessary for the application. The sample rows taken are based on the percentage of rows.

Which command is used for user defined error messages?

RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.

What do mean by XML Datatype?

XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.

What is CDC?

CDC is abbreviated as Change Data Capture which is used to capture the data that has been changed recently. This feature is present in SQL Server 2008.

What is Filtered Index?

Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered Index

What are the major enterprise relational database systems?

The four main enterprise options for SQL databases are:
• Microsoft SQL Server
• MySQL
• Oracle Database
• IBM DB2
Most enterprise versions of SQL databases come with their own “dialect” of SQL. For example, Oracle has created the PL/SQL extension of SQL, while Microsoft SQL Server uses the Transact-SQL language.
These different SQL versions are usually fairly similar on the whole. However, each has its own features and quirks to distinguish it from other options.

What is the difference between SQL and NoSQL databases?

Relational databases aren’t the only model for storing information in a database. Non-relational databases (also known as NoSQL databases) use a concept other than the relational model to store data.
There are several non-relational models for storing data, including:
• Key-value: Each item in the database is a key paired with a corresponding value.
• Document: Each item in the database is a document that stores data in a standard format, and is then associated with a unique key.
• Column: Data is stored in columns instead of rows, in order to improve performance for certain types of queries.
• Graph: Data is stored as a graph with nodes and edges that describe the relationships between different items.
Some of the most popular NoSQL databases are MongoDB, Apache Cassandra, HBase, and Redis.
Unlike SQL databases (which use SQL), NoSQL databases do not use a single, standard query language for interacting with data.

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

Primary keys, unique keys, and foreign keys are similar yet distinct concepts:
• A primary key is an attribute or attributes serving as a unique identifier for each record. For example, the primary key for students at a university could be their student ID numbers, since each student has a unique ID number. Each table can have only one primary key.
• A unique key is an attribute that must be different for each record. Unlike primary keys, there can be multiple unique keys in a table. For example, we may want students’ phone number to be a unique key, since no two students should have the same number. However, this would not be a good primary key, since students’ phone numbers may change during their studies.
• A foreign key is an attribute in one table that refers to a primary key in a different table. For example, suppose we have a table that contains the students in a given class. We might insert a foreign key in this table that refers to the primary key in another table (such as the students’ personal information).

What are the different types of clauses in SQL?

SQL clauses are used to qualify a database query by restricting or altering the values that it returns. The types of SQL clauses are:
• FROM: Used to specify which tables the data will be pulled from.
• WHERE: Used to filter results by requiring them to fulfill one or more conditions.
• ORDER BY: Used to specify how the results of a query should be sorted. For example, if you are returning students’ records, you may wish to order them by last name or by ID number.
• GROUP BY: Used to group together rows that have the same values. For example, you may wish to group together students based on their major.
• HAVING: Used in combination with the GROUP BY clause. It restricts the returned values to only those that fulfill a given condition.
• DISTINCT: Used to retrieve a table containing records with the duplicate values removed. For example, you may wish to count the number of different states or countries that students are from.

What does a NULL value represent in SQL?

NULL is a special signifier in SQL that represents “no value.” A field with a NULL value is not equivalent to a field with a value of 0, or a field that contains whitespace.
NULL values are common when adding a record with one or more optional fields. If you do not enter a value for an optional field, it will take on the value NULL until you change the field’s value.
You can test whether a field has a NULL value by using the SQL operators IS NULL and IS NOT NULL.

What are the goals and methods of database testing?

Database testing is necessary to ensure that a database adheres to the ACID properties described above.
Some of the most important database elements to test are:
• Transactions: Testers should verify that the database continues to fulfill the ACID properties after every transaction.
• Schema: A schema is the formal structure of a database. It describes how the data is organized and what relations exist between the records and fields. Testers should verify that the schema mapping between the front end and back end is correct. In addition, the schema should contain common-sense restrictions on the contents of a field (for example, ensuring that the ID number field is a numeric value).
• Triggers: A trigger is a database event that has been configured to occur once a given condition is fulfilled or another event takes place. Testers should verify that all triggers take place as expected.

Why do we use SQL limitations? Which constraints can we use while making a database in SQL?

SQL Constraints are used to set the principles for all tables in the table. On the off chance that any imperatives get abused then it can prematurely end the activity that caused it.
Constraints are characterized while making the database itself with CREATE TABLE explanation or even after the table is made once with ALTER TABLE statement.
There are 5 noteworthy constraints we use as a part of SQL, for example,
• NOT NULL: That demonstrates that the section must have some value and we can’t leave it invalid
• Interesting: We use this constraint to guarantee that each line and section has one of a kind value and we make sure we don’t rehash any values in some other line or segment
• Essential KEY: We use this constraint as a part of the relationship with NOT NULL and UNIQUE imperatives, for example, on one or the mix of in excess of one segments to distinguish the specific record with a one of a kind character.
• Remote KEY: Generally, we use it to guarantee the referential uprightness of data in the table and furthermore coordinates the incentive in one table with another utilizing Primary Key
• CHECK: We use it to guarantee whether the incentive in sections satisfies the predetermined condition.

Explain the working of SQL Privileges?

SQL GRANT and REVOKE charges are used to execute benefits in SQL various user conditions. The chairman of the database can give or repudiate benefits to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL and so on.

What is SQL Sandbox in SQL Server?

SQL Sandbox is the protected place in SQL Server Environment where untrusted contents are executed. There are 3 sorts of SQL Sandbox, for example:
Safe Access Sandbox: Here a user can perform SQL tasks, for example, making put away systems, triggers and so forth yet can’t approach the memory and can’t make records.
External Access Sandbox: User can approach records without having a privilege to control the memory distribution.
Unsafe Access Sandbox: This contains untrusted codes where a user can approach memory.

What is the Cartesian product of table?

The output of a Cross Join is a Cartesian Product. It returns lines combining each column from the primary table with each line of the second table. For Example, in the event that we join two tables having 15 and 20 products the Cartesian result of two tables will be 15×20=300 Rows.

What are diverse Clauses that form a part of SQL?

Clauses used as a part of SQL:
Top SQL Interview Questions – SQL Clauses
• WHERE Clause: The clause serves to help characterize the condition, extract, and display tables that satisfy the given condition.

What is “scheduled jobs” or “scheduled tasks“?

Scheduled jobs or tasks allows automated task management on regular or predictable cycles. One can schedule administrative tasks and decide the order of the tasks.
Name the encryption mechanisms in SQL server.
Ans. The encryption mechanism used in SQL server are –
• Transact-SQL functions
• Asymmetric keys
• Symmetric keys
• Certificates
• Transparent Data Encryption

Name symmetric key encryption algorithms supported in SQL server?

SQL Server supports several symmetric key encryption algorithms, such as DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES

Can you name different types of DDL commands?

DDL commands are divided into following –
CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.
DROP – Used to delete objects
ALTER – Used to change database structures
TRUNCATE – Used to erase all records from a table, excluding its database structure
COMMENT – Used to add comments to the data dictionary
RENAME – Used to rename a database object

Name different DML commands in SQL?

DML commands are divided into following –
SELECT – Used to select specific database data
INSERT – Used to insert new records into a table
UPDATE – Used to update existing records
DELETE – Used to delete existing records from a table
MERGE – Used to UPSERT operation (insert or update)
CALL – Used to call a PL/SQL or Java subprogram
EXPLAIN PLAN – Used to interpret data access path
LOCK TABLE – Used to control concurrency

Name different DCL commands in SQL?

DCL commands are –
GRANT – Used to provide user access privileges to the database
DENY – Used to deny permissions to users
REVOKE – Used to withdraw user access by using the GRANT command

Name different TCL commands in SQL?

Different DCL commands are –
COMMIT – Used to commit a transaction
ROLLBACK – Used to roll back a transaction
SAVEPOINT – Used to roll back the transaction within groups
SET TRANSACTION – Used to specify transaction characteristics

What is Referential Integrity?

Referential integrity is a relational database concept which suggests that accuracy and consistency of data should be maintained between primary and foreign keys.

What is Business Intelligence?

Business intelligence (BI) includes technologies and practices for collecting, integrating, analyzing and presenting business information. It combines business analytics, data mining, data visualization, data tools and infrastructure, and best practices.

What is faster between a table variable and a temporary table?

Between these, a table variable is faster mostly as it is stored in memory, whereas a temporary table is stored on disk. In case the size of table variable exceeds memory size then both the tables perform in a similar manner.

How do you explain what SQL is to someone without a technical background?

SQL or Structured Query Language is a standardized programming language used to access or manipulate data in a database. It was designed to update, add, delete a row of data and retrieve subsets of information within the database.
In other words, a way to “talk” to databases by using coded commands, called SQL queries.

What are the different type of keys and their uses?

SQL keys are sets of one or more columns that uniquely identify a record in a database table. They are used in fetching records or data-rows/column from data tables and connect tables or views in databases. Different types of keys are:
Candidate Key – A table identifies only one Primary Key for a single or more column. Candidate keys are potential and qualified keys to be the Primary Key.
Primary Key – Also called “Primary Keyword,” is a unique data identifier. It identifies telephone numbers, driver’s license numbers, or vehicle identification numbers(VIN).
Alternate Key – Alternate Keys are like a Primary Key that can be a column or set of columns on a table which values are always unique. They can contain null values unless it’s clearly specified with a NOT NULL integrity rule. Currently not selected as a primary key of the table but can also be the Primary Key.
Note: A table is composed of Candidate keys, but only one Candidate key can be a Primary key. Once the Primary key is specified, the rest are called Alternate keys.
Composite Key – Also known as a compound key or concatenated key. It is a combination of two or more columns in a table used to identify each table row uniquely. If columns are not combined and are taken individually, uniqueness is not guaranteed.
Foreign Key – In two tables’ relationship, a primary key of one table is referred to as a foreign key in another table. A foreign key can have duplicate values and can also keep null values if a column is defined to accept nulls.
Unique Key -An alternate key that accepts just one value and doesn’t allow duplicate values in a column.
Super Key – it’s a set of columns, where each column is functionally dependent. The Super key may hold additional columns that aren’t strictly required to identify each row uniquely. If you add any other column/attribute to a Primary Key, then it becomes a super key, like EmployeeID + FullName, is a Super Key.

How we can refresh the view?

SQL CREATE and REPLACE can be used for refreshing the view.
The syntax for SQL Create and Refresh:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

What is the use of NVL work?

We use NVL work to change over the null a value to its real value.

What are number line correlation administrators will use while working with a subquery?

There are 3-row comparison operators which we use in subqueries, for example, IN, ANY what not.

How does the query above work exactly?

It’s actually pretty simple. First, the subquery (which is basically a derived table here, named TopOrderAmountsPerSalesperson) returns the orders with the highest dollar amounts per salesperson, and the associated salesperson ID. So, now we have each salesperson’s highest valued order and his/her ID in a derived table. That derived table (the results from the subquery) is then joined with the entire Orders table on the condition that the salesperson ID matches and that the Amount from the Orders table matches the MaxOrder amount returned from the derived table. What’s the point of this? Well, that join will give us the correct OrderNumber since it is matching on both the salesperson ID and the amount. Even if there are 2 rows with the same exact salesperson ID and amount it will not even matter because no matter which ordernumber is associated with that row, the result set will be exactly the same.
And remember that the whole reason we are doing this is to avoid the original problem with not being able to select a non-aggregated column with a group by.
Now, retrieving the salesperson name is simple. Try to figure it out on your own.

Why does the selected column have to be in the group by clause or part of an aggregate function?

So, now you understand how to fix the error – but do you understand why it is a problem in the first place? Well, you should – because that is the most important thing to understand! So, let’s explain some more about why SQL gives that error shown above .
First off, let’s talk a little bit more about aggregate functions. You probably know what aggregate functions in SQL are – we used one in the example above. In case you forgot, aggregate functions are used to perform a mathematical function on the values inside a given column, which is passed into the aggregate function. Here are some of the commonly used aggregate functions:
AVG() – Returns the average value
COUNT() – Returns the number of rows
FIRST() – Returns the first value
LAST() – Returns the last value
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum
To illustrate why the SQL standard says that a selected column has to be in the group by clause or part of an aggregate function, let’s use another example. Suppose we have some tables called Starbucks_Stores and Starbucks_Employees. In case you don’t already know, Starbucks is a popular coffee shop/cafe in the USA
SELECT count(*) as num_employees, HourlyRate
FROM Starbucks_Employees JOIN Starbucks_Stores
ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
GROUP BY city
It looks like the SQL above would just return the number of Starbucks employees in each city, along with the HourlyRate – because it will group the employees based on whatever city they work in (thanks to the “group by city” statement).

What are the constraints and what are their types?

These are commands you can use to set the rules for your data. Any operations violating the set rules are aborted by the constraints.
They are of 5 types:
     NOT NULL– columns cannot be left empty or null
     UNIQUE – ensures values in cells are unique and not repeated
      PRIMARY KEY – to identify a record
     FOREIGN KEY – ensures integrity of data
     CHECK – makes certain values in cells meet the set rules.

What Clauses are used in SQL?

Clauses are commands in the SQL software, and there are 6 main types:
     WHERE
     ORDER BY
      GROUP BY
     HAVING
     JOIN
      USING

Define SQL Update Statement?

Update statement is used to modify the data value in the table. General syntax for update is as below:
UPDATE table_name
SET column_name1 = value1,
column_name2 = value2,
column_nameN = valueN,
[WHERE condition]

Define SQL Delete Statement?

Using this statement, we can delete the records in the entire table or specific record by specifying the condition.
DELETE FROM table_name [WHERE condition];

What is Auto Increment feature in SQL?

Auto increment allows the user to create a unique number to be generated whenever a new record is inserted in the table. AUTO INCREMENT is the keyword for Oracle, AUTO_INCREMENT in MySQL and IDENTITY keyword can be used in SQL SERVER for auto-incrementing. Mostly this keyword is used to create the primary key for the table.

What is Hibernate and its relation to SQL?

Hibernate is Object Relational Mapping tool in Java. Hibernate let’s us write object-oriented code and internally converts them to native SQL queries to execute against a relational database.
Hibernate uses its own language like SQL which is called Hibernate Query Language(HQL). The difference is that HQL boasts on being able to query Hibernate’s entity objects.
It also has an object-oriented query language in Hibernate which is called Criteria Query. It proves very beneficial and helpful to developers who primarily use objects in their front-end applications and Criteria Query can cater to those objects in even add SQL-like features such as security and restriction-access.

What is a SQL Profiler?

The SQL Profiler is a Graphical User Interface that allows database developers to monitor and track their database engine activities. It features activity logging for every event occurring and provides analysis for malfunctions and discrepancies.
It basically is a diagnostic feature in SQL that debugs performance issues and provides a more versatile way of seeing which part in your trace file is causing a clog in your SQL transactions.

How can we solve SQL Error: ORA-00904: invalid identifier?

This error usually appears due to syntax errors on calling a column name in Oracle database, notice the ORA identifier in the error code. Make sure you typed in the correct column name. Also, take special note on the aliases as they are the one being referenced in the error as the invalid identifier.

How can we link a SQL database to an existing Android App?

It will require a JDBC (Java Database Connectivity) driver to link these two. Also, you must add the corresponding dependencies to your build.gradle file along with the permissions and grants.

What to do when you forget your root password?

If you forgot or lost your root password, start the database with the command of “skip-grants-table”. After you set the new password, restart the database in normal mode and enter the new password.

What should you do if the data disk is overloaded?

You might encounter situations where you fill-up the data disk. You can’t continue to do anything if it’s overloaded.
What you need to know during this SQL interview question is that in this situation is to apply what is known as a soft link. These links create a location where you can store your .frm and .idb files – and that is exactly what you should do. This will resolve the overload problem.

What are the types of locks?

There are 2 types of locks:
Shared Lock: – In this type of locks, the data being locked can be read by other users / sessions. But it will not allow others to update the data that was being locked.
Exclusive Lock: – In this type of locks, data that is being locked cannot be read or updated by other users or sessions. It will be visible for read and write for the user who has locked it. Other users/session will have to wait till the lock is being released.

What are the techniques of locking?

Database lock can be placed at different levels – on single row or multiple rows or on particular column or on entire table. This database locking at different level is known as locking granularity. Let us discuss them one by one below:
• Database Level Locking: – In this method, entire database is locked for update. Here, only one user or session will be active for any update and any other users cannot update the data. This method is not widely used, as it locks entire database. However, in Oracle the exclusive lock is same as Database lock and does not allow others to use entire database. It will be helpful when some support update is being executed like upgrading to new version of software etc.
• File Level Locking: – Here, entire database file will be locked. When we say database file, it may include whole table, or part of a table or part of multiple tables. Since file lock can include either whole or partial data from different tables, this type of lock is less frequent.
• Table Level Locking: -In this method, entire table will be locked. This will be useful when we need to update whole rows of the table. It will also be useful when we add/ remove some columns of the table where the changes affect entire table. Therefore, in Oracle this type of lock is also known as DDL lock.
• Page or Block Level Locking: – In this method, page or block of the database file will be locked for update. A page or block might contain entire or partial data of the table. This page or block represents space in memory location where data is occupied. This may contain entire table data or partial data. Hence this type of locking is also less frequent.
• Row Level Locking: – In this method entire row of a table is locked for update. It is most common type of locking mechanism.
• Column Level Locking: – In this method some columns of a row of a table is locked for update. This type of lock requires lots of resource to manage and release locks. Hence it is very less frequently used locking type.

How to select 10 records from a table?

MySQL: Using limit clause, example select * from Employee limit 10;
Oracle: Using ROWNUM clause, example SELECT * FROM Employee WHERE ROWNUM < 10;
SQL Server: Using TOP clause, example SELECT TOP 3 * FROM Employee;

How can you maintain the integrity of your database on instances where deleting an element in a table result in the deletion of the element(s) within another table?

This is possible by invoking an SQL trigger which listens for any elements that are deleted in Table A and deletes the corresponding linked elements from Table B.

What is the process of copying data from Table A to Table B?

INSERT INTO TableB (columnOne, columnTwo, columnThree, …)
SELECT columnOne, columnTwo, columnThree, …
FROM TableA WHERE added_condtion;

What are the differences between IN and EXISTS clause?

The apparent difference between the two is that the EXISTS keyword is relatively faster at execution compared to IN keyword. This is because the IN keyword must search all existing records while EXISTS keywords automatically stop when a matching record has been found.
Also, IN Statement operates within the ResultSet while EXISTS keyword operates on virtual tables. In this context, the IN Statement also does not operate on queries that associates with Virtual tables while the EXISTS keyword is used on linked queries.

Does SQL support programming?

SQL refers to the Standard Query Language, which is not actually the programming language. SQL doesn’t have a loop, Conditional statement, logical operations, it can not be used for anything other than data manipulation. It is used like commanding (Query) language to access databases. The primary purpose of SQL is to retrieve, manipulate, update and perform complex operations like joins on the data present in the database.

What are the types of operators available in SQL?

Operators are the special keywords or special characters reserved for performing particular operations and are used in the SQL queries. There is three type of operators used in SQL:
Arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/), etc.
Logical operators: ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE.
Comparison operator: =, !=, <>, <, >, <=, >=, !<, !>

What is the usage of the DISTINCT keyword?

The DISTINCT keyword is used to ensure that the fetched value is only a non-duplicate value. The DISTINCT keyword is used to SELECT DISTINCT, and it always fetches different (distinct) from the column of the table.

What is DBMS?

DBMS stands for Database Management System. This is a program which is used to control them. It is like a File Manager that manages data in a database rather than saving it in file systems.
Database management system is an interface between the database and the user. It makes the data retrieval, data access easier.
Database management system is a software which provides us the power to perform operations such as creation, maintenance and use of a data of the database using a simple query in almost no time.
Without the database management system, it would be far more difficult for the user to access the data of the database.

What are the different types of database management systems?

There are four types of database:
Hierarchical databases (DBMS)
Relational databases (RDBMS)
Network databases (IDMS)
Object-oriented databases

What is NoSQL?

It stands for Not Only SQL and provides an alternative to relational databases. Instead of tabular data stores, they use graph stores, key-value stores, document databases, and wide-column stores. It is popular in the agile development world as developers don’t have to finalize the data model before storing information.
How to use a specific database
Here is the SQL command used to select the database containing the tables for your SQL statements:
USE fcc_sql_guides_database;
SELECT and FROM clauses
Use SELECT to determine which columns of the data you want to show in the results. There are also options you can use to show data that is not a table column.
The following example shows two columns selected from the “student” table, and two calculated columns. The first of the calculated columns is a meaningless number, and the other is the system date.
SELECT studentID, FullName, 3+2 AS five, now() AS currentDate FROM student;

What are the types of normalization available in SQL and how do you use them?

Normalization is an important data quality and performance tool in SQL. Applicants who know how to use this feature show that they’re experienced at maintaining SQL databases. What to look for in an answer:
• Definition of normalization in SQL
• Discussion of the different SQL normalization types
• Explanation of the most appropriate times to use each normalization type
Example: “Normalization is a way to improve database efficiency by reducing the overall size of the database and its dependencies. I use this process to accelerate data access speed and improve overall SQL performance.”

Can you access or query remote SQL Server database from a Mac, Linux or Ubuntu machine?

Yes, you can connect or query your remote SQL Server database from your Mac, Linus or Ubuntu machines using Azure Data Studio tool.

What is Azure Data Studio?

Azure Data Studio is an alternative way to SQL Server Management Studio (SSMS) which you can run only on windows machines to query, editing and data development tasks. Azure Data Studio offers a modern editor experience to connect with a remote SQL Server database. It helps us to query and manage data across multiple sources with intellisense.

What are the differences between char and nchar?

These data type is used to stores characters but these are different in many cases as given below:
char:
This is a fixed length characters data type. It takes one byte per character and used to store non-Unicode characters. Suppose, you declare a field with char(20) then it will allocate memory for 20 characters whether you are using only 10 characters. Hence memory for 10 characters which is empty will be wasted.
nchar:
This is like as char data type but it takes two bytes per character and used to store Unicode characters means multiple languages (like Hindi, Chinese, etc.) characters in the database.

How would apply date range filter?

You can use simple condition >= and <= or similar or use between/and but the trick is to know your exact data type.
Sometimes date fields contain time and that is where the query can go wrong so it is recommended to use some date related functions to remove the time issue. In SQL Server common function to do that is datediff function.
You also have to be aware of different time zones and server time zone.
To increase query performance you may still want to use between however you should be aware of proper format you should use if not it might misbehave during filtering.

How can you combine two tables/views together? For instance one table contains 100 rows and the other one contains 200 rows, have exactly the same fields and you want to show a query with all data (300 rows). This sql interview question can get complicated?

You use UNION operator. You can drill down this question and ask what is the difference between UNION and UNION ALL. More tricky question are how to sort the view (you use order by at the last query), how to name fields so they appear in query results/view schema (first query field names are used). How to filter groups when you use union using SQL (you would create separate query or use common table expression (CTE) or use unions in from with () or what happens when you have a combination of UNION and UNION ALL

SQL Questions For Interview:How to display top 5 employees with the higest number of sales (total) and display position as a field. Note that if both of employees have the same total sales values they should receive the same position, in other words Top 5 employees might return more than 5 employees?

Microsoft introduced in SQL Server 2005 ranking function and it is ideal to solve this query. RANK() function can be used to do that, DENSE_Rank() can also be used. Actually the question is ambiguous because if your two top employees have the same total sales which position should the third employee get 2 (Dense_Rank() function) or 3 (Rank() Function)? In order to filter the query Common Table Expression (CTE) can be used or query can be put inside FROM using brackets ().

How to get accurate age of an employee using SQL?

The word accurate is crucial here. The short answer is you have to play with several functions. For more comprehensive answer see the following link SQL Age Function. Calculate accurate age using SQL Server

How to return truly random data from a table? Let say top 100 random rows?

Again this is more SQL Server answer and you can do that using new_id() function in order by clause and using top 100 in select. There is also table sample function but it is not truly random as it operates on pages not rows and it might not also return the number of rows you wanted.

What is the difference between UNION and UNION ALL?

UNION returns only distinct values, eliminate duplicate rows.
UNION ALL will not eliminate duplicate rows, returns all values.