Question 1: What is an index? What are the two main index types?
An index is a structure in a database that can help retrieve data faster. When you search table data with an SQL query, it will search the whole table and return the result. An unindexed table is called a heap. The data stored in such tables are usually not arranged in any particular way. It is stored in the order it was entered. Therefore, searching for data can be very slow and frustrating.
When you query an indexed table, the database will go to the index first and retrieve the corresponding records directly. The two main index types are:
- Clustered
- Non-clustered
A clustered index defines the exact order of the data stored in the table. There can be only one clustered index per table, since the table can only be ordered in one way.
A non-clustered index simply points to the data, with the order of the data in the index not being the same as the physical order of the actual data. The data is stored in one location, while the index is stored in another location.
Question 2: What’s the code for creating an index?
Now we get to a practical example! Suppose we have the table employee, which has the following columns:
name– The employee’s first name.surname– The employee’s last name.nin– The employee’s national identification number (e.g. social security number).
Your task is to create a clustered index on the nin column and a non-clustered index on the surname column. How do you do that?
To create the clustered index, the code will be:
CREATE CLUSTERED INDEX CL_ninON employee(nin); |
This will create the clustered index with the name CL_nin, on the table employee and the column nin.
To create the non-clustered index, the code will be:
CREATE NONCLUSTERED INDEX NCL_surnameON employee(surname); |
Run this code and you will create a non-clustered index named NCL_surname on the column surname in the table employee.
Important: If you don’t specify a type (clustered or non-clustered), you’ll create a non-clustered index by default.
Question 3: How do you add ranking to rows using RANK()?
Monitoring sales numbers is essential for every company. Take the example of a car dealer. There can be many salespeople selling cars every day. Imagine that the dealership management is in terested in comparing their salespeople. They want to know who is performing well and who just hangs around. You have the table salespeople with the following information:
first_name– The employee’s first name.last_name– The employee’s last name.cars_sold– The number of cars sold by this employee.
How do you put a rank on this table, shown below?
| first_name | last_name | cars_sold |
|---|---|---|
| Clarabelle | Hanmer | 77 |
| Christiano | Overstall | 51 |
| Wilhelm | Kopec | 38 |
| Rubie | Ding | 72 |
| Artemus | Woolward | 100 |
| Emilee | Nanetti | 84 |
| Raina | Bedinn | 57 |
| Glendon | Knowlys | 116 |
| Carlotta | Dytham | 106 |
| Joly | Tschierasche | 114 |
The code for this problem is:
SELECT RANK() OVER(ORDER BY cars_sold DESC) AS rank_sales,first_name,last_name,cars_soldFROM salespeople; |
The code uses the RANK() window function. Since no window is defined, the function will use the whole table. It will rank the data according to the column cars_sold and the rank will be added to the new column rank_sales. Run the code and you will get a nice table – which will get you a point on your interview!
| rank_sales | first_name | last_name | cars_sold |
|---|---|---|---|
| 1 | Glendon | Knowlys | 116 |
| 2 | Joly | Tschierasche | 114 |
| 3 | Carlotta | Dytham | 106 |
| 4 | Artemus | Woolward | 100 |
| 5 | Emilee | Nanetti | 84 |
| 6 | Clarabelle | Hanmer | 77 |
| 7 | Rubie | Ding | 72 |
| 8 | Raina | Bedinn | 57 |
| 9 | Christiano | Overstall | 51 |
| 10 | Wilhelm | Kopec | 38 |
Question 4: What’s the difference between RANK() and DENSE_RANK()?
The main difference is that RANK() will give all rows with the same values (in the ranking criteria) the same rank. It will also skip ranks if more than one row has the same rank; the number of ranks skipped will depend on how many rows share the same value. This creates non-consecutive ranks.
With DENSE_RANK(), rows with the same values will also be ranked the same. However, this function will not skip any ranks, so it will lead to consecutive ranks.
Here’s an example to help clarify the difference. Let’s modify the table from the previous example. The table looks like this:
| first_name | last_name | cars_sold |
|---|---|---|
| Clarabelle | Hanmer | 72 |
| Christiano | Overstall | 84 |
| Wilhelm | Kopec | 38 |
| Rubie | Ding | 72 |
| Artemus | Woolward | 100 |
| Emilee | Nanetti | 84 |
| Raina | Bedinn | 72 |
| Glendon | Knowlys | 116 |
| Carlotta | Dytham | 106 |
| Joly | Tschierasche | 114 |
If you run the code from the previous example, RANK() will get you this result:
| rank_sales | first_name | last_name | cars_sold |
|---|---|---|---|
| 1 | Glendon | Knowlys | 116 |
| 2 | Joly | Tschierasche | 114 |
| 3 | Carlotta | Dytham | 106 |
| 4 | Artemus | Woolward | 100 |
| 5 | Emilee | Nanetti | 84 |
| 5 | Christiano | Overstall | 84 |
| 7 | Clarabelle | Hanmer | 72 |
| 7 | Rubie | Ding | 72 |
| 7 | Raina | Bedinn | 72 |
| 10 | Wilhelm | Kopec | 38 |
You’ll notice that rank 5 is allocated twice, then the ranking skips 6 and goes directly to 7. Rank 7 is allocated three times, after which the ranking goes directly to 10.
If you want to see how DENSE_RANK() ranks the rows, run the following code:
SELECT RANK() OVER(ORDER BY cars_sold DESC) AS rank_sales,DENSE_RANK () OVER (ORDER BY cars_sold DESC) AS dense_rank_sales,first_name,last_name,cars_soldFROM salespeople; |
It will give you a result that looks like this:
| rank_sales | dense_rank_sales | first_name | last_name | cars_sold |
|---|---|---|---|---|
| 1 | 1 | Glendon | Knowlys | 116 |
| 2 | 2 | Joly | Tschierasche | 114 |
| 3 | 3 | Carlotta | Dytham | 106 |
| 4 | 4 | Artemus | Woolward | 100 |
| 5 | 5 | Emilee | Nanetti | 84 |
| 5 | 5 | Christiano | Overstall | 84 |
| 7 | 6 | Clarabelle | Hanmer | 72 |
| 7 | 6 | Rubie | Ding | 72 |
| 7 | 6 | Raina | Bedinn | 72 |
| 10 | 7 | Wilhelm | Kopec | 38 |
Question 5: What is an auto-increment?
Any type of database job will require this knowledge. Auto-increment is a SQL function that automatically and sequentially creates a unique number whenever a new record is added to the table.
The keyword that’ll give you this function is AUTO_INCREMENT.
Here’s the example. The code below will create the table names with the values defined by INSERT INTO:
create table names (id INT NOT NULL AUTO_INCREMENT,first_name VARCHAR(50),last_name VARCHAR(50));INSERT INTO names(first_name, last_name) VALUES ('Kristen', 'Yukhnev');INSERT INTO names(first_name, last_name) VALUES ('Angelica', 'Hulson'); |
The table will look like this:
| id | first_name | last_name |
|---|---|---|
| 1 | Kristen | Yukhnev |
| 2 | Angelica | Hulson |
To see how the auto-increment function works, add a new record to the table above:
INSERT INTO names (first_name, last_name)VALUES ('Rosalia', 'O''Towey'); |
This will add a new record to a table. Select all the data to see how the table has changed:
SELECT *FROM names; |
Now the table looks like this:
| id | first_name | last_name |
|---|---|---|
| 1 | Kristen | Yukhnev |
| 2 | Angelica | Hulson |
| 3 | Rosalia | O’Towey |
When we added a new record to the table, AUTO_INCREMENT automatically added a new sequential number. As 1 and 2 previously existed in the table, with AUTO_INCREMENT the database knows that the next value will be 3.
Question 6: What is a subquery?
A subquery (also called an inner query or nested query) is a query placed within a query. It returns data that will be used by the main query. You’ll usually find it in the WHERE clause.
Question 7: What will the following code return?
The code below is an example of a subquery:
SELECT first_name,last_name,cars_soldFROM carsWHERE cars_sold > (SELECT AVG (cars_sold)FROM cars); |
Running the code will return the columns first_name, last_name, and cars_sold from the table cars, but only where cars_sold is greater than the average number of cars sold.
Question 8: Is there a difference between a NULL value and zero?
Yes! A NULL value is the absence of the data/information. It has a quantitative character in that it represents the absence of the quantity. Simply put, NULL in SQL means the value is unknown or missing; we don’t know what the value is. On the other hand, zero means there is a value which equals, well, zero. Therefore, zero has a qualitative character.
Question 9: How would you extract the last four characters from a string?
To do that, you would need the RIGHT() function.
For example, there’s the products table, which consists of the following data:
product– The name of the product.manufacturer– The company that makes the product.
The table looks like this:
| product | manufacturer |
|---|---|
| X278 Hammer 2018 | Sledge Hammer |
| M+S Tyres Z348 2020 | Goodtyre |
| Paint red pearly 9R458PT12 2019 | PaintItBlack |
You need to find the year when each product was produced. But whoever created the database did a lousy job. There’s no column with the production year! There’s no such data available – except as the last four characters of the product name. To extract the year from that field, here’s the code you’d use:
SELECT product,manufacturer,RIGHT(product,4) AS year_producedFROM products; |
And here’s the result! Simple, right?
| product | manufacturer | year_produced |
|---|---|---|
| X278 Hammer 2018 | Sledge Hammer | 2018 |
| M+S Tyres Z348 2020 | Goodtyre | 2020 |
| Paint red pearly 9R458PT12 2019 | PaintItBlack | 2019 |
Let me explain what we just did. The code, of course, selects the columns product and manufacturer. Then, using the RIGHT() function, we’ve instructed the query to take the strings in the column product and return the last four characters starting from the right. We’ll put these results shown in the new column year_produced.
Question 10: What is a view? How do you create one?
A view is a virtual table or a stored SQL statement that uses data from one or more existing tables. The view is called a ‘virtual table’ because the data is used like a table, but it is retrieved whenever the view is run. (The result of a view is not stored as a table. )
Let’s say there’s a table called salary that contains the following columns:
first_name– The employee’s first name.last_name– The employee’s last name.salary– The employee’s salary.
| first_name | last_name | salary |
|---|---|---|
| Mike | Hammer | 2780 |
| John | Johnson | 1600 |
| Kate | Williams | 3000 |
Employees’ salary information is not available to everybody in the company. However, at this company everybody needs to be able to access the list of employees. How would you allow them to do that and, at the same time, not break any confidentiality rules?
You would not allow them access to the whole table, but you could create a view for them. That way, they would always be able to access the latest data without seeing anything confidential. Here’s how to do it:
CREATE VIEW employee_list ASSELECT first_name,last_nameFROM salary; |
Running this code will create a view named employee_list, which will retrieve the first_name and last_name information from the table salary. It’s simple, isn’t it? You create it using the command CREATE VIEW, then you just write a regular query.
OK, but this just created a view. Creating it didn’t retrieve any data. So how do you run the view? You simply pretend it is a regular table. The code below will run the view:
SELECT *FROM employee_list; |
And the resulting table is here! Beautiful! No salaries are shown, so you didn’t screw anything up!
| first_name | last_name |
|---|---|
| Mike | Hammer |
| John | Johnson |
| Kate | Williams |
About Me:-
My name is Om Prakash Singh – welcome to my blog!
I am a data analytics consultant with a specialty in Looker. With years of experience in the field, I have developed a strong understanding of data analysis and visualization, and have a passion for helping organizations make informed decisions through data-driven insights. My expertise lies in utilizing Looker, a leading data platform, to help businesses turn their data into actionable insights, streamline their data processes, and drive growth. I am dedicated to sharing my knowledge and experience with others through this blog, and I hope you will find the content informative and valuable. Whether you’re a seasoned data professional or just starting out, I believe there is something here for everyone. Thank you for stopping by and I look forward to connecting with you!
Reach out to us here if you are interested to evaluate if Looker is right for you or any other BI solution.
Pingback: Top 25 SQL Interview Questions You Must Prepare - Data Analysis Experts