What is SQL? Explain DQL, DML, DDL, DCL and TCL statements with examples.

Learn about SQL and categories of SQL commands like DQL, DML, DDL, DCL and TCL statements with specific command examples.

 

SQL: 

SQL stands for Structured Query Language. It is the language for database that helps retrieving, storing, and manipulation of data in a relational database.

All RDBMS like Oracle, SQL Server, MySQL, PostgresSQL, DB2 etc uses SQL as database language. NoSQL database don't use it.

SQL uses certain commands like Select, Create, Insert, Update, Drop, Truncate etc. to carry out the specific tasks in the database.

These SQL commands are primarily categorized into following categories:

1. DDL – Data Definition Language
2. DQL – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5. TCL – Transaction Control Language

Data Definition Language (DDL)

Data Definition Language or DDL commands are comprised of the SQL commands that are used to define the database schema and objects like tables, index, procedures, triggers etc. 

DDL commands are auto-committed, meaning that changes saved permanently in the database. 

Examples of DDL commands:

• CREATE, 
• DROP, 
• ALTER, 
• TRUNCATE, 
• COMMENT,
• RENAME 

Data Query Language (DQL) :

Data Query Language or DQL statements are used to retrieve data from database tables. It is also in some form works for inserting data.

Example of DQL commands:

• SELECT

 

Data Manipulation Language(DML): 

DML SQL commands are used to manipulate data in the database. 

DML commands are not auto-committed, meaning that changes have to committed explicitly and it can be roolback

Examples of DML commands:

• INSERT
• UPDATE
• DELETE

 

Data Control Language(DCL): 

DCL commands deal with the rights, permissions and other controls of the database system.

Examples of DCL commands:

• GRANT
• REVOKE

Transaction Control Language(TCL): 

TCL commands are used to manage the transaction at the database level. These operations are automatically committed in the database.

Examples of TCL commands:

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION

What is the difference between primary key and unique key in SQL Server?

How to answer DBMS interview question about the difference between primary key and unique key

This is very commonly asked interview question from SQL Server or database. Following is the answer of this question:

1. By default, Primary key creates clustered index in the table but unique key creates non-clustered index.

2. Primary key column does not accept any null values, where as a unique key column accept only one null value.

3. A table can have only one primary key. On the other hand a table can have more than one unique key.

4. Duplicate values are not allowed in primary key where as duplicate value will be accepted if one or more key parts are null

5. The purpose of implementing primary key is to enforce integrity between entities of database, on the other hand the purpose of unique key is to enforce unique data within the table.

Microsoft Power BI interview questions and answers

Learn the most frequently asked questions on Microsoft Power BI in the job interviews in 2021 with answers

 

 

What feature is not available in the free version of Power BI?

1. Publish content to the Power BI mobile app
2. Connecting to more than one data source
3. The Power Query Editor
4. Creating reports with more than two visualizations

Ans: 1

 

What is the three-step process of data modeling in Power BI?

First, we ________ from a data source. Then, we ________ the data to how we need it, and last, we _________ the data into our Power BI data model.

1. export, load, transform
2. load, transform, publish
3. import, transform, load
4. load, transform, extract

 Ans: 3

How is Power Query like the human circulatory system? Check all that apply.

1. consumes data from external data sources
2. connects automatically to CSV files
3. loads enriched data into data model
4. generates new data with synced files
5. creates data visualizations automatically
6. transforms and enriches data

Ans: 1,3,6

You can manipulate datasets using Python or R in Power Query.

1. True
2. False

 Ans: 1

Once you make a change to your dataset in PowerQuery, you do not have the ability to view how the dataset looked prior to that transformation.

1. True
2. False

 Ans: 2

Which of the following terms describes a connection to a data source for the purpose of gathering information?

1. CSV file
2. Promoted Headers
3. Query
4. Query Settings
5. None of these

  Ans: 3

 Select each of the following functions which can you perform in Power Query. Check all that apply.

1. Remove columns
2. Build visualizations
3. Rename columns
4. Perform calculations
5. View all transformations

  Ans: 1,3,4,5

 We must click "Close & Apply" to leave Power Query and return to Power BI Desktop

1. True
2. False

 Ans: 1

Power BI visualizations are only interactive once we enable the feature in Power BI Desktop.

1. True
2. False

 Ans: 2, Most visualizations are interactive by default in Power BI, and we do not have to take any extra steps for it.

To create a new visualization, we must click out of an already selected chart before selecting a new chart.

1. True
2. False

 Ans: 1, If we keep a chart selected and attempt to add a new chart, it will change the selected chart into the new chart we have clicked. To avoid this, it is very important to learn to first UN-SELECT the chart we are working in before adding a new chart to the report canvas.

Which of the following is not a correct way to add a field to a visualization?

1. Drag and drop from fields pane onto the visualizations pane
2. Click the field's checkbox in the fields pane
3. Double-click the field in the fields pane
4. Drag and drop from fields pane onto the visualization in the report body

 Ans: 3 

What are three types of filters?

1. Visual-level filters
2. Table-level filters
3. Page-level filters
4. Report-level filters
5. Column-level filters

 Ans: 1, 3, 4

__________ is a special kind of filter that allows us to filter a visual to just the top or bottom-ranked values based on a particular measure.

1. Visual-level filter
2. Top N filter
3. Filters pane
4. Basic filtering
5. Report-level filter

 Ans: 2

 

In Power BI, the _______ icon locks a filter, so no one can change it when they run the report, and the _______ icon hides the filter completely, so no one can see it in the Filters pane when they run the report.

1. key, glasses
2. padlock, eyeball
3. door, globe

 Ans: 2

What is the difference between a slicer and a filter?

1. Slicers have three scopes: visual-level, page-level, and report-level
2. A slicer puts a filter directly on your report page without using the filters pane
3. Filters can only function as on-page filter controls
4. None of the above
5. All of the above

Ans: 2

 True or False: You can configure slicers so they only affect particular visuals on a page.

1. True
2. False

Ans: 1

 

What option allows us to view a visual's data in an Excel or CSV file?

1. More Options
2. Close & Apply
3. Export Data
4. Back to report
5. Show Data

Ans: 3

The Merge Columns option gives you several methods for taking an existing column of data and breaking it out into two or more columns.

1. True
2. False

Ans: 2

Which of the following is not a use for the Column Profiler?

1. You want to see which value occurs most in the column.
2. You want to substitute new data in the column.
3. You want to see how many empty cells exist in the column.
4. You want to see if there are any errors in the column.

Ans: 2

Which join type would we use in a Merge Queries transformation if we wanted to keep only those records that exist in both the left and right table?

1. Left outer
2. Inner join
3. Left Anti
4. Right Anti

Ans: 2

True or False: Data profiling is the process of examining our data and summarizing information about it.

1. True
2. False

Ans: 1


Which of the following is NOT a correct reason to create an Index Column?

1. To create a key that can be used to join two tables together
2. To act as unique identifier for each record in our table
3. To establish a date hierarchy

Ans: 3

The __________________ option in Power BI by default breaks down a column of dates into its individual components, making it easy to aggregate values at different periods.

1. split date
2. date hierarchy
3. format date
4. date table

Ans: 2

 

How did we get a count of the number of unique items in our data model?

1. Use the DIVIDE function in DAX with the ShotID column
2. Use the Dropdown feature in our slicer
3. Use the DISTINCTCOUNT function in DAX with the ShotID column
4. Create an index column with unique identifiers

Ans: 3

Which of the following options represent good uses for a scatter chart? (may be more than one)

1. Plotting two measures to see how they might relate to each other
2. Creating a geographic map of the data
3. Ranking countries by a single measure or variable
4. Plotting spatial (x,y) coordinates

Ans: 1,4