Q20 What Is The Difference Between The Rank And Dense: Rank Functions
The RANK function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7.
The DENSE_RANK function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. It always indicates a ranking in order of precedence. This function will assign the same rank to the two rows if they have the same rank, with the next rank being the next consecutive number. If we have three records at rank 4, for example, the next level indicated is 5.
Q21. What are Tables and Fields?
A table is a collection of data components organized in rows and columns in a relational database. A table can also be thought of as a useful representation of relationships. The most basic form of data storage is the table. An example of an Employee table is shown below.
A Record or Row is a single entry in a table. In a table, a record represents a collection of connected data. The Employee table, for example, has four records.
A table is made up of numerous records , each of which can be split down into smaller units called Fields. ID, Name, Department, and Salary are the four fields in the Employee table above.
What Do You Mean By Etl In Sql
ETL in SQL represents Extract, Transform and Load.
Extracting It is about extracting data from the source, which can be a data warehouse, CRMs, databases, etc.
Transforming It includes many processes such as cleansing, standardization, deduplication, verification, and sorting.
Loading It is the process of loading the transformed data into the new destination. There are two types of loading data: full loading and incremental loading.
What Are Stored Procedures
It is a function that consists of a group of statements that can be stored and executed whenever it is required. Know that stored procedures are compiled only once. They are stored as Named Object in the SQL server database. Stored procedures can be called at any time during program execution. Moreover, a stored procedure can be called another stored procedure.
Also Check: What To Say When Scheduling An Interview
What Are The Differences Between Sql Mysql And Sql Server
The following comparison chart explains their main differences:
|MySQL can use variables constraints and data types.
|SQL Server can use variables constraints and data types.
|SQL is a programming language, so that it does not get any updates. Its commands are always fixed and remain the same.
|MySQL is software, so it gets frequent updation.
|SQL Server is also software, so it gets frequent updation.
How To Create A Table In Sql
The command to create a table in SQL is extremely simple:
CREATE TABLE table_name
We will start off by giving the keywords, CREATE TABLE, and then we will give the name of the table. After that in braces, we will list out all the columns along with their data types.
For example, if we want to create a simple employee table:
CREATE TABLE employee ,age int,gender varchar, ....)
You May Like: Java Programming Questions For Interview
Explain The Types Of Indexes
Single-column Indexes: A single-column index is created for only one column of a table.
CREATE INDEX index_nameON table_name
Composite-column Indexes: A composite-column index is created for two or more columns of a table.
CREATE INDEX index_nameON table_name
Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table.
CREATE UNIQUE INDEX indexON table_name
Courses you may like
Joining Tables & Queries
An Inner join returns only those rows where the data from one table matches the data from the second table.
Left outer join
The left join is a table join that will retrieve all the rows from the left table and only the matching rows from the right table.
Right outer join
This join is the one that returns all the rows from the right table and only the matching rows from the left table.
Full outer join
The full outer join will join the data so that the result will include all the rows from one table and all the rows from the second table.
This results in a Cartesian product. This means it will return all the combinations of rows from one table with all the combinations of rows from the other table.
This is an SQL command that will combine the result of one query with the result of another query. Therefore, it will show only unique records.
This one also combines the results from two or more queries. The difference between UNION and UNION ALL is it will also include duplicates.
You May Like: What Are Interview Questions For Accounts Payable
Solution Of Sql Interview Question #1
The solution code is:
SELECT authors.author_name, SUM AS sold_sumFROM authorsJOIN booksON books.book_name = authors.book_nameGROUP BY authors.author_nameORDER BY sold_sum DESCLIMIT 3
And here is a short explanation:
1. First you have to initiate the JOIN. I joined the two tables by using:
SELECT *FROM authorsJOIN booksON books.book_name = authors.book_name
2. After that, I used a SUM function with a GROUP BY clause. This means that in the SELECT statement I had to replace the * with the author_name and sold_copies columns.
3. Eventually, I ORDERed the results in DESCending order.
What Is A Dbms
DBMS stands for ‘database management system,’ which is a program used to help maintain, monitor, create, deploy and control the use of a database. It also acts as a file manager for the data kept in a database, no matter the size or complexity of the database.
Read more: What Is a Database Management System?
Also Check: How To Nail Amazon Interview
What Is Sql Injection
SQL injection is a type of vulnerability in website and web app code that allows attackers to control back-end operations and access, retrieve, and destroy sensitive data from databases. In this technique, malicious SQL statements are inserted into a database entry field, and once they are performed, the database becomes vulnerable to an attacker. This technique is commonly used to access sensitive data and perform administrative activities on databases by exploiting data-driven applications. It is also known as SQLi attack.
Some common examples of SQL injection are:
- Accessing confidential data to modify an SQL query to get desired results.
- UNION attacks to steal data from different database tables.
- Examine the database to extract information regarding the version and structure of the database.
Advanced Sql Whiteboard Questions
OK, so weâve already had some SQL practice for interviews but our exercises covered SQL basics. But what about a more advanced job, like SQL developer or database administrator? Recruiters will be interested in more than just your SQL knowledge they will also need to know how savvy you are with databases in general. In that case, see how well you can verbally answer SQL whiteboard questions like these:
- What are primary and foreign keys?
- What is normalization? What is denormalization?
- What is an index? Can you briefly explain the different index types?
- Can you explain database relationships and relationship types?
- What is a database cursor?
- What is a constraint?
Read Also: How To Answer Questions During An Interview
Who This Course Is For:
- Developers preparing for IT Interview and use SQL as backend
- Developers working mainly on SQL
- Anyone working on IT Projects and want to test SQL skills
- Anyone looking for a job and mentions SQL in their skillset
- 4.2 Instructor Rating
Greetings to all! I have 14+ years of experience working in IT Industry. I am an experienced Freelance Instructor and Career Counsellor. I frequently conduct online Job Search training sessions and conduct Mock Interviews for different IT roles. I am skilled in Agile, Scrum, Microsoft Technologies – C#, ASP.NET, Visual Studio, SQL Server and Azure DevOps.
What Are The Different Types Of Keys In Sql And When Do You Use Them
You can use this technical question to gauge whether an applicant has the right amount of SQL knowledge. Keys are a relatively basic concept in SQL, so anyone with experience in this technology should know what they are. What to look for in an answer:
- Strong understanding of what keys are and how theyre used in SQL
- Skill at appropriately using keys in SQL
- Ability to explain the keys concept in an interview environment
A unique key is a unique field or column that a table doesnt share with any other table in the SQL database. I use this key to create a unique identifier for each table for easier retrieval later.
You May Like: System Design Interview Url Shortener
How Can You Avoid Duplicate Keys In Sql
We can eliminate duplicate keys in SQL by using the following methods:
- Using INSERT INTO SELECT
- Code compilation
76. Differentiate: UNION and INTERSECT statements?
UNION: It is the operator that returns a single result set for two separate queries. And this operator functions based on specific conditions.
Syntax: query 1 UNION query2
INTERSECT: It is the operator that returns only the distinct rows from two separate queries.
Syntax: query 1 INTERSECT query2
Explain Union Minus Union All Intersect
INTERSECT returns all distinct rows selected by both queries.MINUS: returns all distinct rows selected by the first query but not by the second.
UNION: returns all distinct rows selected by either query
UNION ALL: returns all rows selected by either query, including all duplicates
|Explore SQL Server Sample Resumes Download & Edit, Get Noticed by Top Employers!
Also Check: How To Ace Project Manager Interview
How Can You Update A Table Using Sql
The UPDATE statement allows you to update a database table in SQL. After the execution, one or more columns in a table will be replaced by new values.
The syntax for the UPDATE statement is given as follows:
You can use the following statement to run the newly created stored procedure.
|EXECUTE HR.GetEmployeesTest2 N’Ackerman’, N’Pilar’
What Do You Mean By Lock Escalation
It is the process of converting row and page locks into table locks. Know that Reduction of lock escalation would increase the server performance. To improve performance, we need to keep transactions short and reduce lock footprints in queries as low as possible. Besides, we can disable lock escalation at the table and instance levels, but it is not recommended.
Recommended Reading: What To Wear To A Tech Interview
What Is The Difference Between Having And Where Clause
Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with GROUP BY to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.
What Is Primary Key In Sql
Primary Key is a constraint in SQL. So, before understanding what exactly is a primary key, lets understand what exactly is a constraint in SQL. Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. Constraints can either be column level or table level.
Lets look at the different types of constraints which are present in SQL:
|The CHECK constraint ensures that all values in a column satisfy certain conditions.
|Used to create and retrieve data from the database very quickly.
You can consider the Primary Key constraint to be a combination of UNIQUE and NOT NULL constraint. This means that if a column is set as a primary key, then this particular column cannot have any null values present in it and also all the values present in this column must be unique.
What Is Alias In Sql
SQL aliases help to assign temporary names for a table or column. It is used to simplify table or column names. And aliases can exist only for that query period. It can be created using the AS keyword. Know that creation of an alias is in no way affecting the column names in the database. It can be applied when more than one table is involved in a query.
Sql Exercise 2 Write A Statement
Write an SQL statement that lists student names, subject names, subject lecturers, and the max amount of points for all subjects except Computer Science and any subjects with a max score between 100 and 200.
Table: subjectsubject_id subject_name max_score lecturer---------- ---------- ---------- ----------- 11 Math 130 Christena Solem 12 Computer Science 150 Jaime Pille 13 Biology 300 Carrol Denmark 14 Geography 220 Yuette Galang 15 Physics 110 Colton Rather 16 Chemistry 400 Nan Mongeau
Table: studentstudent_id student_name city subject_id----------- ------------ ---------- -----------2001 Thurman Thorn New York 112002 Sharda Clemens San Francisco 122003 Buck Elkins New York 132004 Fabian Johns Boston 152005 Brad Cameron Stanford 112006 Sofia Roles Boston 162007 Rory Pietila New Haven 12
Read Also: How To Conduct Yourself In An Interview
Q16 What Are Sql Comments
SQL Comments are used to clarify portions of SQL statements and to prevent SQL statements from being executed. Comments are quite important in many programming languages. The comments are not supported by a Microsoft Access database. As a result, the Microsoft Access database is used in the examples in Mozilla Firefox and Microsoft Edge. Single Line Comments: It starts with two consecutive hyphens . Multi-line Comments: It starts with /* and ends with */.
What Is Wrong With The Following Sql Query
SELECT gender, AVG FROM employee WHERE AVG> 30 GROUP BY gender
When this command is executed, it gives the following error:
Msg 147, Level 16, State 1, Line 1
Aggregation may not appear in the WHERE clause unless it is in a subquery contained in the HAVING clause or a select list the column being aggregated is an outer reference.
Msg 147, Level 16, State 1, Line 1Invalid column name gender.
This basically means that whenever we are working with aggregate functions and are using the GROUP BY clause, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we should use the HAVING clause.
When we are using the HAVING clause, the GROUP BY clause should come first, followed by the HAVING clause.
select e_gender, avg from employee group by e_gender having avg> 30
Don’t Miss: Data Structures To Know For Coding Interviews
What Do You Mean By Autonomous Transaction
An autonomous transaction is an independent transaction initiated by a transaction that is the main transaction. Autonomous transaction holds the main transaction, performs SQL operations, and commits or rolls back. After that, it resumes the main transaction. Note that autonomous transaction doesnt share locks and resources with the main transaction.
What Is Etl In Sql
ETL stands for Extract, Transform and Load. It is a three-step process, where we would have to start off by extracting the data from sources. Once we collate the data from different sources, what we have is raw data. This raw data has to be transformed into the tidy format, which will come in the second phase. Finally, we would have to load this tidy data into tools which would help us to find insights.
Don’t Miss: What Are The Possible Interview Questions
Q32 How To Create A Temp Table In Sql Server
Temporary tables are created in TempDB and are erased automatically after the last connection is closed. We may use Temporary Tables to store and process interim results. When we need to store temporary data, temporary tables come in handy. The following is the syntax for creating a Temporary Table:CREATE TABLE #Employee ) INSERT INTO #Employee VALUES ,
Lets move to the next question in this SQL Interview Questions.
Q33. How to install SQL Server in Windows 11?
Install SQL Server Management Studio In Windows 11
Step 1: Click on SSMS, which will take you to the SQL Server Management Studio page.
Step 2: Moreover, click on the SQL Server Management Studio link and tap on Save File.
Step 3: Save this file to your local drive and go to the folder.
Step 4: The setup window will appear, and here you can choose the location where you want to save the file.Step 5: Click on Install.Step 6: Close the window after the installation is complete.Step 7: Furthermore, go back to your Start Menu and search for SQL server management studio.
Step 8: Furthermore, double-click on it, and the login page will appear once it shows up.
Step 9: You should be able to see your server name. However, if thats not visible, click on the drop-down arrow on the server and tap on Browse.
Step 10: Choose your SQL server and click on Connect.
After that, the SQL server will connect, and Windows 11 will run good.
Explain Inner Join With An Example
Inner Join basically gives us those records that have matching values in two tables.
Let us suppose that we have two tables, Table A and Table B. When we apply Inner Join on these two tables, we will get only those records that are common to both Table A and Table B.
SELECT columnsFROM table1INNER JOIN table2ON table1.column_x=table2.column_y
select * from employeeselect * from department
Now, we will apply Inner Join to both these tables, where the e_dept column in the employee table is equal to the d_name column of the department table.
select employee.e_name, employee.e_dept, department.d_name, department.d_locationfrom employee inner join departmentonemployee.e_dept=department.d_name
After applying Inner Join, we have only those records where the departments match in both tables. As we can see, the matched departments are Support, Analytics, and Sales.
Recommended Reading: How To Pass A Supervisor Interview