SQL Interview Questions

1. What is normalization?

Ans : Normalization is a database design technique to remove redundant data.

2. How to Implement normalization?

Ans : Normalization is implemented by splitting tables.

Example : This country data having redundant data, you create a master table or a reference table. Say 1 for India and 2 for Bangladesh.

------------------------------------------------------------------------------------------------------------

ID Country

1 India

2 Nepal

And in the transaction table just give the references.

-----------------------------------------------------------------------------------------------------------------

* De-Normalization : Denormalization is a database design technique to improve search performance. We merge tables. Duplicate data is present in denormalization.

----------------------------------------------------------------------------------------------------------------------------

* Explain : OLTP vs OLAP?

1. OLTP : Online Transaction Processing.

2. OLAP : Online Analytical Processing.

Point 1 - Insert, update, and delete it all sends to OLTP system.

Point 2 - Fetching records or doing analysis, reads, selects is all done through this OLAP system.

Different B/W Primary key vs Unique Key.

Primary and Unique keys ensure they do not have duplicate values. Big difference

Ns, N is Nulls, in unique key we can put Null. We can put Null for unique key but in case of primary key we cannot have nulls while unique key can have nulls.

Second point : Many unique keys but only ONE Primary key.

* Different between char vs varchar

> char() is fixed length while varchar() is variable length.

> Example : Country_code : char(3),

Country_Name : Varhcar(10)

1. Char(3)

countrycode charlength

IND 3

BAN 3

SL 2

It will take fixed length even if the charlength is 2.

2. Varhcar(10)

countrycode charlength

INDIA 5

Sri Lanka 9

Singapore 9

Chile 5

Depending on the size it can adjust.

* Difference between nchar() vs char

nchar(3) vs char(3)

> nchar(3) : For one character it will take two bytes because you have to support now unicode. When we say char for 1 character it will take 1 byte. nchar helps to store non-english characters or unicode characters while char helps to only store pure english characters and 0 to 9 numbers and so on,

* What is the use of index?

Indexes increases search performance.

-----------------------------------------------------------------------------------------------------------------------------------------

1. Describe Joins in SQL?

SQL join statement is used to combine data or rows from two or more tables based on common field between them.

Different types of joins are as follows.

1. INNER JOIN - returns rows when there is a match in both tables.

2. LEFT JOIN - returns all rows from left table and matching records from right table.

3. RIGHT JOIN - returns all rows from right table and matching records from left table.

4. OUTER JOIN - returns all records from both tables that satisfy the join condition.

2. What is calculated field in tableau?

Ans : A calculated field is used to create a new (modified) fields from existing data in the data source. It can be used to create more robust visualizations and doesn't affect the original dataset.

3. How do you create a hyperlink in excel?

Ans : Hyperlinks are used to navigate between worksheets and files/websites. To create a hyperlink, the shortcut used is ctrl+k. The 'insert hyperlink' box appears. Enter the address and the text to display.

4. What is parameter in Tableau? Give an example.

Parameters in Tableau enable users to add some advanced calculations and calculated fields. Parameters provide adding a non-existing variable to the entire workbook and simplify the needs and requirements to analyze and visualize the data.

or

A parameter is a dynamic value that a customer could select, and you can use it to replace constant values in calculations, filters, and reference lines.

For example, when creating a filter to show the top 10 products based on total profit instead of the fixed value, you can update the filter to show the top 10, 20 or 30 product using a parameter.

5. What is database?

Ans : A database is an organized collection of data that is structured into tables, rows, columns and indexes. It helps the user to find the relevant information frequently. It is an electronic system that makes data access, data manipulation, data retrieval, data storing and data management very easy. Almost every organization uses database for storing data due to its easily accessible and high operational ease. The database provides perfect access to data lets us perform required tasks.

6. What is the purpose of DCL language?

Data control language allows users to control access and permission management to the database. It is the subset of a database, which decides that what part of the database should be accessed by which user at what point of time. It includes two commands, Grant and REVOKE.

GRANT : It enables system administrators to assign privileges and roles to the specific user accounts to perform specific tasks on the database .

REVOKE : It enables system administrator to revoke privileges and roles from the user accounts so that they cannot use the previously assigned permission on the database.

7. What is meant by DBMS?

DBMS stands for database management system. It is a software program that primarily functions as an interface between the database and the end user. It provides us the power such as managing the data, the database engine, and the database schema to facilitate the organization and manipulation of data using a simple query in almost no time. It is like a file manager that manages data in a database rather than saving it in file systems. Without the database management system. It would be more difficult for the user to access the database data.

8. What are the subsets in SQL?

Data definition lanaguage (DDL) : It define the data structure that consists of commands like CREATE, ALTER, DROP etc.

Data manipulation language (DML) : It is used to manipulate existing data in the database. The commands in this category are Select, UPDATE, INSERT etc.

DATA Control Language : It controls access to the data stored in the database. The commands in this category include GRANT and REVOKE.

Transaction Control Language : TCL It is used to deal with the transaction operations in database. The commands in this are COMMIT, ROLL BACK, SET Transaction, save point etc.

9. What is Macro in excel?

Ans : Macro refers to an algorithm or set of actions that help automate a task in excel by recording and playing back the steps taken to complete that task. Once the steps are stored, you create a macro, and it can be edited and played back as many times as the user wants.

Macro is great for repetitive tasks and also eliminates errors. For example, suppose an account manager has to share reports regarding the company employees for non-payment of dues. In that case, it can be automated using macro and doing minor changes every month as needed.

10. What is the cartesian product of the table?

Ans : The output of cross join is called as cartesian product. It returns rows combining each row from the first table with each row of the second table. For example, if we join two tables having 15 and 20 columns the cartesian product of two tables will be 15*20 = 300 rows.

11. What is the difference between SQL and MySQL?

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

12. What is Data Intergrity?

Ans : Data integrity is the assurance of accuracy and consistency of data over its entire life-cycle and is a critical aspect of the design, implementation and usage of any system which stores, processes or retrieves data.

13. What is the use of dual axis in tableau?

Ans : Dual axis allows you to compare measures, and this is useful when you want to compare two measures that have different scales.

14. What is the difference between .twb and .twbx extension?

Ans. A .twb file contains information on all the sheets, dashboards and stories, but it wont contain any information regarding the datasource. Whereas, .twbx file contains all the sheets, dashboards, stories and also compressed data sources. For saving a .twbx extract needs to be performed on the data source. If we forward .twb file to someone else than they will be able to see the worksheets and dashboards. but wont be able to look into the dataset

--------------------------------------------- --------------------------------------------- ---------------------------------------------

SQL JOINS.

-- Formula for outer joins:

Left join = inner join + fetch any additional records from left table which is not present in right table.

Right join = inner join + fetches any records from right table which is not present in left table.

Full join = inner join.

+ fetch additional records from left table which is not present in right table

+ fetch additional records from right table which is not present in left table.

-->> Scenario 1:

drop table table_1;

create table table_1

(id int);

drop table table_2;

create table table_2

(id int);

insert into table_1 values (1),(1),(1),(2),(3),(3),(3);

insert into table_2 values (1),(1),(2),(2),(4),(null);

Cross Join.

Natural Join :

8 (If same column name exists) OR (If same column name does not exists the output will be cross join).