Skip to main content

Posts

Showing posts with the label SQL

sub queries explaination

  1. Scalar Subqueries: Definition: A scalar subquery returns a single value (one row, one column). Usage: They can be used anywhere a single value is expected, such as in WHERE clauses, SELECT lists, or HAVING clauses. Example: SQL SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary > ( SELECT AVG (salary) FROM employees); Explanation: The inner subquery (SELECT AVG(salary) FROM employees) calculates the average salary, which is a single value. The outer query then compares each employee's salary to this average. 2. Multiple-Row Subqueries: Definition: These subqueries return multiple rows (typically one column). Usage: They are used with operators like IN , NOT IN , ANY , ALL . Examples: Using IN : SQL SELECT employee_id, first_name, last_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location = 'New York' ); Explanation: The subquery returns a list...

SQl hackerrank advanced select

  Write a query identifying each record type in the  TRIANGLES  table using its three side lengths. Output one of the following statements for each record in the table: Equilateral : It's a triangle with sides of equal length. Isosceles : It's a triangle with   sides of equal length. Scalene : It's a triangle with   sides of differing lengths. Not A Triangle : The given values of  A ,  B , and  C  don't form a triangle. Takeaway: we can use case in SQL but we have to give after select along with columns syntax: case When condition then  value or string that need to printed in output after case statement ends with END   Answer: SELECT CASE  WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'  WHEN A = B AND B = C THEN 'Equilateral' WHEN A = B OR B = C OR A = C THEN 'Isosceles' ELSE 'Scalene' END FROM TRIANGLES; output:  Equilateral Equilateral Isosceles Equilateral Isosceles Equil...

SQL Joins and advanced joins and Subqueries

  Refernce :  Expert Guide to Advanced SQL Joins: What You Need to Know It's helpful to visualize how these different SQL joins work. Here's a breakdown in a table-like format, along with explanations: SQL Join Types Overview Join Type Description Key Characteristics Use Cases INNER JOIN Returns rows where there is a match in both tables. - Shows only matching records. - Excludes unmatched rows from both tables. - Retrieving related data that exists in both tables. - Finding records with corresponding entries in another table. LEFT OUTER JOIN (LEFT JOIN) Returns all rows from the left table, and the matched rows from the right table. - Includes all records from the left table. - Fills in NULL values for columns from the right table where there's no match. - Retrieving all records from one table and their related data from another, even if some records don't have matches. - Finding records in one table that don't have corresponding entries in another. RIGHT OUTER JO...