What Are Functions And Their Usage In Sql
SQL functions are simple code snippets that are frequently used and re-used in database systems for data processing and manipulation. Functions are the measured values. It always performs a specific task. The following rules should be remembered while creating functions:
- Functions can only work with the SELECT statements.
- Every time a function is called, it compiles.
- Functions must return value or result.
- Functions are always used with input parameters.
SQL categories the functions into two types:
- User-Defined Function: Functions created by a user based on their needs are termed user-defined functions.
- System Defined Function: Functions whose definition is defined by the system are termed system-defined functions. They are built-in database functions.
SQL functions are used for the following purposes:
- To perform calculations on data
- To modify individual data items
- To manipulate the output
- To format dates and numbers
- To convert data types
Q40 What Is Clause In Sql
SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the rows from the entire set of records.
For example WHERE, HAVING clause.
Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more.
What Are The Types Of Join And Explain Each
There are various types of join which can be used to retrieve data and it depends on the relationship between tables.
- Inner Join.
Inner join return rows when there is at least one match of rows between the tables.
- Right Join.
Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.
- Left Join.
Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
- Full Join.
Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
Also Check: How To Ace Video Conference Interview
Q17 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 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 .
Sql Exercise 1 Write A Statement
Write an SQL statement that lists school names, student names, and their cities only if the school and the student are in the same city and the student is not from New York.
Table: schoolschool_id school_name city ----------- ------------------ ---------- 1 Stanford Stanford 2 University of Cali San Francisco 3 Harvard University New York 4 MIT Boston 5 Yale New Haven
Table: studentstudent_id student_name city school_id----------- ------------ ---------- -----------1001 Peter Brebec New York 11002 John Goorgy San Francisco 22003 Brad Smith New York 31004 Fabian Johns Boston 51005 Brad Cameron Stanford 11006 Geoff Firby Boston 51007 Johnny Blue New Haven 21008 Johse Brook Miami 2
Don’t Miss: How To Interview Front End Developer
Is Leetcode Designed To Prepare You For Data Science Interviews
As an aspiring data scientist, you might have a question: Is LeetCode for data science? As great as LeetCode is to help software engineers get jobs, LeetCode was NOT designed to help prepare data scientists for their data science interviews or improve their analytical skills. While both jobs require programming skills, how the skills are implemented in the industry are different. Data scientists are required to clean and manipulate data, and churn out insights from the data that would lead to product decisions and recommendations. Manipulating the data to create metrics, understanding those tradeoffs, building statistical and machine learning models are what data scientists do on the job and what is being tested on data science interviews.
LeetCode focuses more on your technical algorithm skills to perform specific tasks like inverting a binary tree or writing sorting algorithms in efficient ways. How to implement technical concepts effectively is the focus on software engineering interviews. How to manipulate and understand data is the focus of data science interviews. In addition, there are non-coding concepts data scientists need to understand and master to become a data scientist and succeed in interviews. These include understanding machine learning theory , statistics, probability, business cases, and product sense just to name a few. These are not the core focus of LeetCode and forces the user to turn to other platforms and resources to master these concepts.
What Is Full Join In Sql
The Full Join results from a combination of both left and right join that contains all the records from both tables. It fetches rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side tables. If a match is not found, it puts NULL value. It is also known as FULL OUTER JOIN.
The following visual representation explains it more clearly:
The following syntax illustrates the FULL JOIN:
To read more information, .
Also Check: How To Say Thank You For An Interview
What Are The Different Modes Of Training That Intellipaat Provides
At Intellipaat, you can enroll in either the instructor-led online training or self-paced training. Apart from this, Intellipaat also offers corporate training for organizations to upskill their workforce. All trainers at Intellipaat have 12+ years of relevant industry experience, and they have been actively working as consultants in the same domain, which has made them subject matter experts. Go through the sample videos to check the quality of our trainers.
Logic Based Sql Interview Questions
Logic based SQL interview questions are very tricky. They aren’t really based on real life examples so much as putting the trickiness of algorithms and data structure interviews into SQL questions. This is exemplified on sites such as LeetCode, where you’ll see a lot of interview questions that aren’t very practical for real life scenarios.
Q1. Write a query to create a new table, named flight routes, that displays unique pairs of two locations.
Example: Duplicate pairs from the flights table, such as Dallas to Seattle and Seattle to Dallas, should have one entry in the flight routes table.
flights table:
Q2. Let’s say we have a table with an id and name field. The table holds over 100 million rows and we want to sample a random row in the table without throttling the database.
Write a query to randomly sample a row from this table.
Hint: We know that the RAND function actually returns a floating-point between 0 and 1. So if we were to run this function, SELECT RAND, we would get a random decimal point to some Nth degree of precision. RAND essentially allows us to seed a random value. How can we use this to select a random row quickly?
users table:
| datetime |
Read Also: How To Dress For An Interview Male
What Are The Goals And Methods Of Database Testing
Database testing is necessary to ensure that a database adheres to the ACID properties described above.
Some of the most important database elements to test are:
-
Transactions: Testers should verify that the database continues to fulfill the ACID properties after every transaction.
-
Schema: A schema is the formal structure of a database. It describes how the data is organized and what relations exist between the records and fields. Testers should verify that the schema mapping between the front end and back end is correct. In addition, the schema should contain common-sense restrictions on the contents of a field .
-
Triggers: A trigger is a database event that has been configured to occur once a given condition is fulfilled or another event takes place. Testers should verify that all triggers take place as expected.
Union And Case Statements
To round out your SQL arsenal you just need a couple more tools. The first one is easy to grasp: unions.
Unions are just the vertical version of joins: whereas joins combine tables or CTEs horizontally using a join key, a union just stacks the tables on top of each other to form one table containing all the rows from the two original tables. The requirement for this is that the two tables that are being union-ed have the same exact columns otherwise there would be no way to logically combine them.
An example of when a union might be useful is when you have separate tables for two types of transactions, but want a single query to tell you how many of each type of transaction you have.
with sales as SELECT type, count as num_transactionsFROM unionedGROUP BY type
Union-ing is the answer when you wish that two tables had instead been structured as one combined table.
Case statements are another fairly simple concept: theyre exactly the same as the ifelse functions in environments like R and Excel. Theyre useful for mapping from one set of predefined values to another.
For example, you might want to turn a day of week column into a variable representing whether or not that day was a weekend.
SELECT CASE WHEN day_of_week in then 'Weekend' else 'Weekday' end as day_typeFROM table_a
Similarly, you could convert a string column like day of week to a binary variable and sum it up to count the number of weekend days in your table.
Don’t Miss: What Questions Do They Ask You At A Job Interview
Joining Tables & Queries
Inner join
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.
Cross join
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.
Union
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.
Union all
This one also combines the results from two or more queries. The difference between UNION and UNION ALL is it will also include duplicates.
How To Delete A Row In Sql
We will be using the DELETE query to delete existing rows from the table:
DELETE FROM table_name
WHERE
We will start off by giving the keywords DELETE FROM, then we will give the name of the table, after that we will give the WHERE clause and give the condition on the basis of which we would want to delete a row.
For example, from the employee table, if we would like to delete all the rows, where the age of the employee is equal to 25, then this will the command:
DELETE FROM employee
Read Also: What To Prepare For A Phone Interview
What Is The Difference Between Clustered And Non
Indexing is a method to get the requested data very fast. There are mainly two types of indexes in SQL, clustered index and non-clustered index. The differences between these two indexes are very important from an SQL performance perspective. The following comparison chart explains their main differences:
| Clustered Index | Non-Clustered Index |
|---|---|
| A clustered index is a table or view where the data for the rows are stored. In a relational database, if the table column contains a primary key, MySQL automatically creates a clustered index named PRIMARY. | The indexes other than PRIMARY indexes are called non-clustered indexes. It has a structure separate from the data row. The non-clustered indexes are also known as secondary indexes. |
| Clustered indexes store the data information and the data itself. | Non-clustered indexes stores only the information, and then it will refer you to the data stored in clustered data. |
| There can only be one clustered index per table. | There can be one or more non-clustered indexes in a table. |
| A clustered index determines how data is stored physically in the table. Therefore, reading from a clustered index is faster. | It creates a logical ordering of data rows and uses pointers for accessing the physical data files. Therefore, reading from a clustered index is slower. |
| A clustered index always contains an index id of 0. | A non-clustered index always contains an index id> 0. |
To read more information, .
Tell Me About A Challenging Sql Project You Worked On
Interviewers like this question as it can tell them a lot about your personality and drive. Firstly, they want to know if youve ever been in a challenging situation, but most importantly, they want to know how you handled it. This is because it will help them to understand how you cope under pressure.
Make sure you think of a relevant example and draw positive conclusions from it that could relate to the role youre currently applying for.
Of course, this isnt an exhaustive list of questions you may be asked, but its a good starting point. By being prepared and familiarising yourself with these SQL interview questions and answers, youll have a much better chance of impressing your interviewer and securing your next job.
Read Also: What To Ask Nanny Interview
Sql Interview Question #2
You work for a startup that makes an online presentation software. You have an event log that records every time a user inserted an image into a presentation. The event_log SQL table looks like this:
| user_id |
and it has over one billion rows.Note: If the event_date_time columns format doesnt look familiar, google epoch timestamp!
Write an SQL query to find out how many users inserted more than 1000 but less than 2000 images in their presentations!
What Is The Difference Between Delete And Truncate In Sql
Consider the following Student table.
If we want to delete the rows of this table, we can use the delete command. When we run the following command, it will delete all the rows.
Delete from Student
We can also delete a specific row using the delete command as shown below:
Delete from Student where ID=1
The truncate command is also used to delete the rows. However, in truncate, we cannot delete a specific row. Thus, when we run the following command, it will delete all the rows of the table.
Truncate Student
Lets say, we have executed rollback before committing changes, but in the case of truncate, it is not possible because there is no log file generated .
In the case of delete, we can perform rollback before committing the changes. Hence, with the delete command, we have the option of recovering the original.
Also, the delete command is slower than the truncate command.
Want to get certified in SQL! Learn SQL from top SQL experts and excel in your career with Intellipaats SQL Certification.
You May Like: How To Prepare For Amazon Coding Interview
What Are The Disadvantages Of Not Performing Database Normalization
The major disadvantages are:
The occurrence of redundant terms in the database causes the waste of space in the disk.
Due to redundant terms, inconsistency may also occur. If any change is made in the data of one table but not made in the same data of another table, then inconsistency will occur. This inconsistency will lead to the maintenance problem and effects the ACID properties as well.
Prepare For Sql Tech Screenings By Practicing
If you managed to solve all these questions properly, you are probably ready for a junior or even for a mid-level Data Analyst SQL technical screening.If not, let me recommend my new online course: SQL for Aspiring Data Scientists where you can level up your SQL skills in only 7 days. When you finish the course, just come back to this article and I guarantee that you will be able to solve these questions!
And if you are just about to start with SQL, start with my SQL For Data Analysis series on the blog!
Also Check: How To Interview An Attorney
Q29 What Are The Different Types Of A Subquery
There are two types of subquery namely, Correlated and Non-Correlated.
Correlated subquery: These are queries which select the data from a table referenced in the outer query. It is not considered as an independent query as it refers to another table and refers the column in a table.
Non-Correlated subquery: This query is an independent query where the output of subquery is substituted in the main query.
Organizational Culture And Teamwork
Be ready to discuss your experiences supporting operations across departments. Have examples ready where your interpersonal skills in working with software developers, business analysts, executives, vendors, and external customers were key. Have stories ready to talk about your experiences with cross-functional teams and how you juggled multiple priorities.
Don’t Miss: How To Code Qualitative Interviews
