SQL Intermediate Interview Questions
1: Explain the meaning of ‘index’.
Indexes help retrieve information from the database faster and with higher efficiency. In other words, it’s a method that enhances performance and there are 3 types of indexes:
- Clustered – reorders the table and searches for information with the use of key values
- Non-clustered – maintains the order of the table
- Unique – forbids fields to have duplicated values
Moreover, a table can have multiple non-cluster indexes, but only 1 single clustered one.
Code to create 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.
2.You forgot your root password, what do you do ?
- 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.
3. Are NULL values equal to a zero ?
No, because a “zero” has a numerical manner and NULL represent the absence of a character. This happens when the character is unknown or unavailable. Additionally, NULL shouldn’t be confused with blank space because data record without any value assigned is not the same as a plain blank space, with no data records attached.
4. Data disk gets overloaded, what do you do ?
You should apply a soft link: these links create a location where you are able to store your .frm and .idb files. This will resolve the overload problem.
5.Explain what‘auto increment’ is?
This command allows you to generate a unique number when a new record is written to a table. When you want to the primary key field value to be generated automatically each time you insert a new record, this is when this function comes in handy.
Another thing worth noting is that the command can be used on various platforms. For SQL Servers the “auto increment” command is “identity”.
6.Using an existing table, make an empty one?
Select * into employeecopy from employee where 1=2
9: Get alternative odd records from the table.
Select employeeId from (Select rowno, employeetId from employee) where mod(rowno, 2)=1
10. 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 |
11.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 |
12. Auto increment sql?
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.
13. What is Sub query with example?
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.
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.
14.How would you filter data using JOIN?
The basic meaning behind JOIN is that it will return data from one table when that data is equal to the data in a second table. If you combine it with the WHERE clause, JOIN can be used for filtering data.
Here is an example of another car dealership. The first table is named cars, and it consists of the following data:
model_id– The car model’s ID number.model_name– The name of that model.brand_id– The ID of the car brand.brand_name– The name of the car brand.year_id– The year when the model was produced. (Uses data from the tableproduction_year.)
The second table is production_year, which contains the following columns:
year_id– An ID number for each year.year– The actual year of the production.
What would you do if you are asked to find all the models that were produced before 2016? Here’s the code:
SELECT model_name, brand_nameFROM cars JOIN production_year ON cars.year_id = production_year.year_idWHERE year_id < 2016; |
Let me explain the code. It selects the column model_name and brand_name from the table cars. This table is joined with the table production_year using the column year_id, which is a connection between the cars and the production_year tables. Because of the WHERE clause, this code returns only the cars that are produced before 2016, i.e. WHERE year_id < 2016.
15.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.
16.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 |
17.What is the difference between NVL and NVL2 functions?
The NVL (exp1, exp2) function converts the expression exp1 to the target expression exp2 if exp1 contains NULL. exp1 has same data type as that of a return value.
The NVL2 (exp1, exp2, exp3) function checks the first expression exp1, if exp1 is not null then, the second expression exp2 is returned as a result. If the first expression exp1 is null, then the third expression exp3 is returned as a result.
18.What are SQL Injections? And How to Prevent SQL Injection Attacks?
It is a mechanism of getting secure data from the database.
SQL Injection Attacks::
- By providing proper validations for input fields.
- By using parameterized queries.
- By using stored procedures
- By using frequent code reviews
- We must not display database error messages in frontend
- An SQL injection is a code injection technique, used to attack data-driven applications.
19.Difference Between Views & Materialized Views?
Ans:
| Views | Materialized Views |
| The view does not store data | Materialized view stores data |
| Security purpose | Improved performance purpose |
| When we ar4e dropping base table then view can’t be accessible | When we are dropping base table also materialized view can be accessible |
| Through the view we can perform DML Operation | We can’t perform DML operation |
20.What are the Transaction Start & End Cases?
Ans:
- A Transaction Begins When The First Executable SQL Statement is Encountered.
- The Transaction Terminates When The Following Specifications Occur.
1. A COMMIT OR ROLLBACK is Issued
2. A DDL Statement Issued
3. A DCL Statement Issued.
- The User Exists The SQL * Plus
- Failure of Machine OR System Crashes.
- A DDL Statement OR A DCL Statement is Automatically Committed And Hence Implicitly Ends A Transaction.
21.GRANT Command?
Ans: Syntax::
1 2 3 | SQL> GRANT< Privilage Name1>, , ON TO ; |
GRANT Command is Used When We Want The Database To Be Shared With Other Users.
The Other Users Are GRANTED With Certain Type of RIGHTS.
GRANT Command Can Be issued Not Only on TABLE OBJECT, But Also on VIEWS, SYNONYMS, INDEXES, SEQUENCES Etc.
1 2 3 4 5 6 7 8 9 | SQL> GRANT SELECTON EMPTO ENDUSERS;SQL> GRANT INSERT, SELECT, DELETON EMPTO OPERATORS;SQL> GRANT INSERT (Empno, Ename, Job)ON EmpTo EndUsers; |
22.REVOKE Command?
Ans: Syntax::
1 2 3 | SQL> REVOKE< Privilage Name1>, , ON FROM; |
REVOKE Command is Used When We Want One Database To Stop Sharing The Information With Other Users.
Revoke Privileges is Assigned Not Only On TABLE Object, But Also on VIEWS, SYNONYMS, INDEXES Etc.
1 | SQL> REVOKE INSERT, DELETE |
ON EMP
FROM Operators;
23.What do you understand by query optimization?
The phase that identifies a plan for evaluation query which has the least estimated cost is known as query optimization.
The advantages of query optimization are as follows:
- The output is provided faster
- A larger number of queries can be executed in less time
- Reduces time and space complexity
24.What do you mean by Denormalization?
Denormalization refers to a technique which is used to access data from higher to lower forms of a database. It helps the database managers to increase the performance of the entire infrastructure as it introduces redundancy into a table. It adds the redundant data into a table by incorporating database queries that combine data from various tables into a single table.
25. What are Entities and Relationships?
Entities: A person, place, or thing in the real world about which data can be stored in a database. Tables store data that represents one type of entity. For example – A bank database has a customer table to store customer information. The customer table stores this information as a set of attributes (columns within the table) for each customer.
Relationships: Relation or links between entities that have something to do with each other. For example – The customer name is related to the customer account number and contact information, which might be in the same table. There can also be relationships between separate tables (for example, customer to accounts).
