TamilDada
    Facebook Twitter Instagram
    Facebook Twitter Instagram
    TamilDada
    Friday, March 31
    • Home
    • Business
      • Life Insurance
      • Insurance
    • Entertainment
    • Health
      • Fitness
    • World
      • Education
      • Travel
      • Law
      • News
    • Sports
    • Life Style
      • Home Improvement
      • Fashion
      • Kitchen
    • Technology
      • App
      • Gadget
      • Software
      • Website
      • SEO
      • Games
    TamilDada
    You are at:Home»News»SQL Interview Questions

    SQL Interview Questions

    tamildaBy tamildaJuly 31, 2021No Comments8 Mins Read
    Share
    Facebook Twitter LinkedIn Pinterest Email

    Most favorite topics of the interviewer for SQL are:

    1. SELECT and FROM: This is the “heart” of any SQL query – SELECT columns FROM table.
    2. WHERE: This acts as a filter and allows you to select only relevant rows based on conditions.
    3. GROUP BY: This helps you aggregate the data by grouping columns.
    4. AVG/SUM/MAX/MIN: These are aggregate functions that help you summarize the data.
    5. COUNT and DISTINCT: Count helps you find the number of entries and Distinct helps you find unique entries.
    6. CASE WHEN: This is a very useful way to create a new derived field or perform complex aggregate operations.
    7. JOIN: Join helps you combine data from different tables using join conditions. Please get familiar with inner and left/right/full outer joins.
    8. UNION: Union and Union All help you to combine data from multiple tables with similar structure.
    9. WINDOW FUNCTION: Allow you to perform calculations across a set of rows.
    10. CTE and SUBQUERY: They help you create more complex queries by combining smaller queries.

    Here I will discuss 6 questions for each topic that are most relevant for SQL interview purposes.

    These SQL Interview Questions were asked in different interviews for data science jobs and data analyst jobs so please focus on these questions and do not forget to practice more and more to become perfect. There is no shortcut to success. So, practice more and more to get acquainted with SQL.

    SELECT and FROM:

    Syntax: 

    • SELECT column1, column2, column3,…,columnN FROM table_name;
    • SELECT * FROM table_name;

    Q1. Consider a table with ID, NAME, AGE, ADDRESS, SALARY. Select ID, NAME, AGE from the table Customers.

    Sol. SELECT id, name, salary FROM CUSTOMERS;

    Q2. Select all the columns from the table CUSTOMERS.

    Sol. SELECT * FROM CUSTOMERS;

    Q3. Select unique customers from the customer table.

    Sol. SELECT DISTINCT * FROM CUSTOMERS;

    Q4. Select a table from a csv file.

    Sol. COPY CUSTOMER_TABLE from ‘path\of\file\file_name.csv’ delimiter ‘,’ csv header;

    Q5. Select Customers with age greater than 30 from the table Customers.

    Sol.  SELECT * FROM CUSTOMERS WHERE AGE>30;

    Q6. Select Customers with age between 20 and 40 from the table Customers.

    Sol. SELECT * FROM CUSTOMERS WHERE AGE<40 AND AGE>20;

    WHERE:

    • It is used to specify a condition while fetching the data from a single table or by joining with multiple tables. 
    • If the given condition is satisfied, then only it returns a specific value from the table.
    • SYNTAX:
      • SELECT “column_name” FROM “table_name” WHERE “condition”;

    Equal to condition:

    Q1. Select first_name from customer_table where age=25.

    Sol. SELECT first_name FROM customer_table WHERE age=25;

    Less than/Greater than condition:

    Q2. Select first_name and age from customer where age is more than 25.

    Sol. SELECT first_name FROM customer_table WHERE age>25;

    Q3. Select first_name and age from customer where age is less than 25.

    Sol. SELECT first_name, age FROM customer_table where age<25;

    Q4. Select first_name and age from customer_table where age is between 25 to 40.

    Sol. SELECT first_name, age FROM customer_table where age<40 and age>20;

    Matching text condition:

    Q5. Find the customers details whose first_name= ‘John”.

    Sol. SELECT * FROM customer_table WHERE first_name=”John”;

     

    BETWEEN:

    • The BETWEEN condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.
    • SYNTAX:
      • SELECT “column_name” FROM “table_name” WHERE “column_name” BETWEEN ‘lower_value1’ AND ‘upper_value2’;

    Q1. Select the customer details whose age is between 20 and 30.

    Sol1: SELECT * FROM customer WHERE age BETWEEN 20 AND 30;

    Sol2: SELECT * FROM customer WHERE age>=20 AND age<=30;

    Q2. Select the customer details whose age is not between 20 and 30.

    Sol. SELECT * FROM customer WHERE age NOT BETWEEN 20 AND 30;

    Q3. Select the sales details where the ship_date is between ‘2015-04-01’ and ‘2016-04-01’

    Sol:  SELECT * FROM sales WHERE ship_date BETWEEN ‘2015-04-01’ AND ‘2016-04-01’;

     

    IMPORTANT INTERVIEW QUESTIONS:

    Q1. find the nth highest salary from a table.

    select * from

    select ename, sal, dense_rank()

    over(order by sal desc)r from Employee)

    where r=&n;

    To find to the 2nd highest sal set n = 2

    To find 3rd highest sal set n = 3 and so on.

    Q2. top 4 salary from the table.

    select * from employee where salary=

    select min(sal) from employee

    where sal in

    select distinct top n=4

    salary from employee

    order by salary desc

    Q3. To find the nth highest salary.

    SELECT ename, sal FROM emoloyee e1 WHERE

    N-1 = (select count(distinct sal) from employee e2

    where e2.sal> e1.sal)

    Q4. Write an sql query to fetch only the even rows from the table.

    Sol.

    • if we have autoincrement field

    SELECT * FROM employeedetails

    WHERE MOD(empid,2)=0;

    • if we do not have auto increment field

    SELECT e.empid, e.project, e.salary

    FROM (

    SELECT *, ROW_NUMBER() OVER(ORDER BY empid) AS rownumber

    FROM employeesalary

    ) e

    WHERE e.rownumber %2=0;

    Q5. write an sql query to fetch records that are present in one table but not in another table.

    Sol.

    SELECT employeesalary.* FROM employeesalary

    LEFT JOIN

    managersalary USING (empid) WHERE managersalary.empid IS NULL;

    Q6. DIFFERENCE BETWEEN WHERE AND HAVING CLAUSE.

    WHERE HAVING
    WHERE is used in the selection of rows according to the given condition HAVING is used in column operations and is applied to aggregate rows or groups
    If GROUPBY is used then it is executed after the WHERE clause is executed.

    It means it selects the rows before grouping is done or aggregate calculations are performed.

    GROUPBY is executed before the execution of the HAVING clause. It means it selects the rows after aggregate calculations are performed.
    WHERE clause can be used with SELECT, UPDATE, DELETE, etc. We cannot use HAVING clause without SELECT statement
    Aggregate functions can never be used with WHERE clause. We can use aggregate functions like SUM, MIN, MAX, AVG, etc, with the HAVING clause

    Q7. DIFFERENCE BETWEEN DELETE AND TRUNCATE.

    DELETE TRUNCATE
    DELETE command is used to delete a row in a table TRUNCATE is used to delete all the rows from a table.
    You can rollback data after using DELETE statement You cannot rollback data
    It is a DML (Data Manipulation Language) Command It is a DDL (Data Definition Language) command
    It is slower than TRUNCATE It is faster

    Q8. WHAT ARE THE DIFFERENT SUBSET OF SQL.

    1. DDL(Data Definition Language): Consist of the commands that can be used to define the database schema.
    2. DML(Data Manipulation Language): consist of commands that deal with the manipulation of data present in the database.
    3. DCL(Data Control Language): Includes commands which deals with the rights, permissions and other controls of the database system
    4. TCL(Transaction control Language): Includes the commands which mainly deal with the transaction of the database.

    Q9. What is the sequence of operation of SQL queries.

    FROM

    JOIN

    WHERE

    GROUP BY

    HAVING

    SELECT

    ORDER BY

    WINDOW FUNCTIONS:

    1. The RANK() function
    2. The DENSE_RANK() function
    3. THE ROW_NUMBER() function
    4. THE LEAD() function
    5. THE LAG() function
    6. THE RUNNING TOTAL

    Q1. Query to display the columns name, category, and ranking_score. It also ranks the rows using the RANK() function in the order defined by the column ranking_score.

    Sol.

    select rank() over(order by ranking_score) as rank_number,

    name, category, ranking_score

    from product;

    1. DENSE_RANK()

    • DENSE_RANK() is similar to RANK(), but it does not allow gaps in the way RANK() does.

    select dense_rank() over(order by ranking_score DESC) as dense_rank_number, name, category, ranking_score

    from product;

    2. ROW_NUMBER()

    • Another popular ranking function used in databases is ROW_NUMBER().
    • It simply assigns consecutive numbers to each row in a specific order.

    select row_number() over(order by ranking_score) as row_number,

    name, category, ranking_score

    from product;

    3. Ranking within partition

    • You can assign ranks separately within each partition with RANK().DENSE_RANK(),ROW_NUMBER()
    • This is done by dividing row into partitions by a column or a combination of columns then assigning ranks independently within each partition.

    SELECT RANK() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number, name, category, ranking_score

    FROM product;

    SELECT DENSE_RANK() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number,

    name, category, ranking_score

    FROM product;

    SELECT ROW_NUMBER() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number,

    name, category, ranking_score

    FROM product;

    4. LEAD()

    SELECT toy_name, month, sale_value, LEAD(sale_value) OVER(PARTITION BY toy_name ORDER BY month)

    AS next_month_value

    FROM toys_sale;

    5. LAG()

    SELECT toy_name, month,sale_value, LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS prev_month_value,

    LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month)- sale_value AS difference FROM toys_sale;

    If you want to get latest news then you can visit this site parishiltonblog.org and you can also check here newshubpages.com. Click here newsvibes.net for getting most popular news.

    6. RUNNING TOTAL

    SELECT toy_name, month, sale_value,

    SUM(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS total_toy_value

    FROM toys_sale;

    SELECT toy_name, month, sale_value,

    SUM(sale_value) OVER(ORDER BY sale_value) AS total_toy_value

    FROM toys_sale;

    This brings us to the end of the blog on SQL Interview Questions. These are the most commonly asked SQL interview questions in various interviews for different job roles as well. Learning these will surely help you land your dream job. All the best!

    To know more Information: meetyou

    Read more about this website: chatrad

    You should visit this site: newsinsightz

    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email
    Previous ArticleHow to quickly identify trees and flowers
    Next Article ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­W88 The Best Gaming & Gambling Platform
    tamilda

    Related Posts

    Hovsco Electric Bikes

    March 31, 2023

    The Art of Creating Photorealistic Interior 3D Renderings

    March 31, 2023

    Disposable Vapes: What You Need to Know

    March 28, 2023

    Leave A Reply Cancel Reply

    You must be logged in to post a comment.

    LATEST POST

    What to Expect from Your Iovera  Treatment

    March 31, 2023

    Hovsco Electric Bikes

    March 31, 2023

    The Art of Creating Photorealistic Interior 3D Renderings

    March 31, 2023

    Master the ins and outs of the quickest hoverboard

    March 30, 2023

    The Benefits of Using Fast Moose Free CDN Web Hosting for Your Website

    March 30, 2023
    Categories
    • App
    • Automobile
    • Automotive
    • Business
    • Education
    • Entertainment
    • Fashion
    • Fitness
    • Food
    • Gadget
    • Health
    • Home Improvement
    • Insurance
    • Kitchen
    • Law
    • Life Insurance
    • Life Style
    • News
    • SEO
    • Social Media
    • Software
    • Special Collections
    • Sports
    • Travel
    • Website
    • World
    • Games
    • Year Collection
    About Us
    About Us

    Tamildada is provide World Biggest News Web Portal 2021. Business Travel Life Style App Automobile Digital Marketing Education Entertainment Fitness Food Gadget Health Games SEO Technology News Here

    Contact Us: [email protected]

    Facebook Twitter Instagram LinkedIn VKontakte
    Popular Posts

    What to Expect from Your Iovera  Treatment

    March 31, 2023

    Know About The Ways To Stop Click Fraud

    March 31, 2023

    Hovsco Electric Bikes

    March 31, 2023
    POPULAR CATEGORIES
    • Technology
    • Business
    • Life Style
    • Fashion
    • Entertainment
    © Copyright 2021, All Rights Reserved
    • Home
    • DMCA
    • Privacy Policy
    • Contact Us

    Type above and press Enter to search. Press Esc to cancel.