Most Asked SQL Queries on Interviews

 

 Top 10 Frequently SQL  Queries Asked for Interviews 






SQL (Structured Query Language) is a crucial skill for anyone working with data and is often a topic in job interviews for data-focused roles. When preparing for interviews for positions like data analyst, data engineer, or database administrator, anticipate encountering questions about SQL.


What are the uses of SQL?

  • SQL's significance in data-centric positions cannot be overstated.
  • As a domain-specific language tailored for managing and querying relational databases, SQL is indispensable.
  • It is essential for tasks like data retrieval, result filtering, information aggregation, or merging data from various sources.
  • It makes SQL a fundamental instrument for data experts.

Here we will explore the top 10 frequently asked SQL queries with explanation.

1. Retrieving data from a table

  • The 'SELECT' statement is the foundation of SQL. It allows you to retrieve data from a specified table.
    -- Retrieve all columns from a table
you can use the SQL query: SELECT * FROM table name;



---- For Retrieve specific columns

you can use the SQL query: SELECT columns name FROM table name;



2. Filtering Data

The 'WHERE' clause allows you to filter rows based on specific conditions.

It is retrieving records having high calories. 



3. Aggregating Data

The 'GROUP BY' clause is used to group rows with the same values in specified columns. It's often used with aggregate functions like sum, count, min, max, avg etc.

  • select hl_calories, round(avg(rating),2) as avg_calories from cereals_data group by hl_calories;















4. Combining Data from Multiple Tables


  • The 'JOIN' clause combines rows from two or more tables based on related columns between them. By default, it will take inner join. 
  • (reference movie dataset)

-- Retrieve movie title and number of ratings

  • select mov_title,num_o_ratings from ratings

inner join movie using(mov_id) order by num_o_ratings

asc limit 1;



5. Drop Table


  • DROP is used to delete a whole database, a column or just a table.
  • It will drop entire table and all integrity constraints will be removed. 
  • It cannot be rollback as it is auto commit.



Here we drop (remove) ratio column.


    ALTER TABLE INSURANCE DROP COLUMN RATIO;







6. Sorting Data

  • The 'ORDER BY' clause is used to sort the result set in ascending or descending order based on one or more columns.

-- Retrieve rating sorted by descending order




7. Counting Rows


  • The 'COUNT ()'function is used to count the number of rows in a table or the number of rows that meet specific conditions.

-- Count the number of females insurance holders.





8. Calculating Sum of Values

  • The 'SUM()' function calculates the sum of values in a specified column.

-- Calculate the total salary paid for each department






9. Using Subqueries for Complex Queries


  • Subqueries are queries embedded within other queries. They are used to retrieve data that will be used in the main query.
-- Retrieve employees with highest salaries in each department





Window functions in SQL


10. Windows Function


Window functions apply to aggregate and ranking functions over a particular window (set of rows). OVER clause is used with window functions to define that window. OVER clause does two things: 

  • Partitions rows to form a set of rows. (PARTITION BY clause is used) 
  • Orders rows within those partitions into a particular order. (ORDER BY clause is used) 


BY-:
Gupta Anish

Comments

Post a Comment