Showing posts with label cheat sheet. Show all posts
Showing posts with label cheat sheet. Show all posts

Thursday, September 1, 2022

SQL Cheatsheet - SELECT FROM WHERE clauses with examples

SQL Cheat sheet for beginners, SELECT clause, FROM clause, WHERE clause explained with examples.

 

 SELECT ... FROM ... WHERE clauses in SQL - Cheat sheet


Querying data using SELECT…FROM...WHERE clauses in SQL

Commands/Clauses

Examples


SELECT 👈

To view required data from one or more tables.

 

Syntax:

SELECT parameters FROM tablename;

 

Parameters for SELECT:

*, list of attributes, functions, arithmetic operations are the parameters for the command (clause) SELECT.

 

 

 

 

 

 

 

 


Parameters examples for SELECT clause

Simple:

SELECT * FROM student;

Result: Displays all columns of all records of student table.


List of attributes:

SELECT name, phone FROM student;

Result: Displays only name and phone of all students;


Function:

SELECT MAX(salary) FROM employee;

Result: Displays the maximum of all salary values stored in employee table.


Operations:

SELECT Basic + HRA FROM employee;

Result: Displays the sum of the values stored in Basic and HRA columns (attributes) of all employee records.

 


FROM 👈

To specify the name of the table/view from which we want to view the data.

 

Syntax:

SELECT parameters FROM tablename;


Parameters for FROM:

Table name or table names separated with comma.

 

 


Parameters examples for FROM clause

Single table:

SELECT * FROM employee;

Result: Displays all records from employee table.


Multiple tables:

SELECT * FROM employee, dept;

Result: It is a special case. It performs join. Displays columns of tables, employee and dept with every record of table employee combined with every record of dept.


WHERE 👈

To specify one or more conditions to filter the data to view/get required data only.

 

Syntax:

SELECT parameters FROM tablename WHERE condition;

 

Parameters for WHERE:

WHERE clause accepts the conditions (predicates) of the following format;

Attribute_name OP value;

Attribute_name OP attribute_name;

Here, OP refers to OPERATOR that can be one of =, <>, >, <, >=, and <=

 

More conditions can be mentioned in WHERE clause. In that case, the conditions should be connected using logical connectives AND, OR, NOT.


Parameter examples for WHERE clause

attribute_name OP value:

SELECT * FROM employee WHERE gender = ‘male’;

Result: Displays all records of employee who are MALE.

 

SELECT * FROM employee WHERE salary > 10000;

Result: Displays all records of employees who earn more than 10000.

 

SELECT name, age FROM employee WHERE salary = 25000;

Result: Displays only name and ages of all employees who earn 25000.


attribute_name OP attribute_name:

SELECT * FROM employee, dept WHERE emp_dno = dept_dno;

 

 

***********

 

What are the parameters for SELECT clause?

Can we perform arithmetic calculations using SELECT clause in SQL?

How to filter the data in a table using WHERE clause?

Can we use more than one condition in WHERE clause?

How do you find cars that are sold for fifty thousand dollars or more?

Where to specify the condition like the watches that are costlier than Omega watches? 

Saturday, August 13, 2022

SQL Cheat sheet - order of execution of SELECT statement in SQL

What is the order of execution of various clauses or parts of SQL SELECT statement? In what order a SELECT query is executed in SQL? Why SELECT clause is executed at the end in most of the SELECT statements? Order of execution of SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and FETCH clauses


Order of execution in SQL SELECT query

 

SELECT query in SQL is the most used query to access the database. It has the following clauses (or parts) which can be found in most of the DBMSs like Oracle, MySQL, SQL Server etc.

  • SELECT clause
  • FROM clause
  • WHERE clause
  • GROUP BY clause
  • HAVING clause
  • Order By clause

 

SELECT query, when executed, follows certain order to fetch the records as result to the user. The table given below discusses about the order of execution of various clauses of SELECT query.

Order

of

execution

Clause

Description

Presence

 

1

FROM

SELECT query identifies the records in one (or more tables) as per the conditions (if any) and displays the result to the user.

Hence, the first task is to identify the base table from which the record to be fetched.

Must

1a

JOIN

JOIN is a binary operator to combine rows from two tables and it is a part of FROM clause if used in the query. Hence, it is in position 2 in the order of execution.

JOIN can be used in the FROM clause of a query as INNER JOIN, NATURAL JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CARTESIAN JOIN (‘,’ (comma) between table names is used as the symbol for CARTESIAN JOIN).

Optional

2

WHERE

Once you have chosen the base table(s), now it is the time to mention the filtering constraints (conditions). If your query has any, it is mentioned in the WHERE clause.

After choosing records from base tables, we need to filter them based on certain values as per your requirement. Hence, after FROM (or JOIN) clause, WHERE clause will be executed next.

Optional

3

GROUP BY

If you like to group the result based on certain attributes and their values, GROUP BY clause has to be used. It is a way to specify ‘HOW’ I want to see my data in the result.

It executes after FROM (or JOIN, or WHERE) clause.

You typically use a GROUP BY clause in conjunction with an aggregate expression (eg. AVG(), SUM(), etc.).

 

4

HAVING

HAVING clause is used along with GROUP BY clause in case if you like to apply conditions (filters) on grouped data.

For example, assume that you have counted number of employees in each department (using COUNT(*) and ‘GROUP BY did’) and now you want to include that departments that have more than 5 employees. Here, we use HAVING clause.

Optional

5

SELECT

SELECT will take parameters (* OR list of columns OR functions OR arithmetic operations). As per the presence of parameters in the query, SELECT will pick and show necessary data from the results produced by the execution of previous clauses.

For example, the SELECT clause in “SELECT sname, age FROM student WHERE gender = ‘F’;” takes two columns. SELECT will select and show only sname and age columns after the execution of FROM clause and WHERE clause are completed.

Must

6

ORDER BY

ORDER BY clause sorts (ascending or descending) the result data produced by SELECT query.

Optional

7

TOP / LIMIT / FETCH

They are used to limit the number of records to be displayed as a result.

TOP is used by SQL Server, LIMIT is supported by MySQL, and Oracle (versions 12 or later) supports FETCH.

Optional

 

Examples - Order of execution of SQL clauses in SELECT query:

Example 1:

Example 2:
Examples 3:
Example 4:
Example 5:

 

What is the order of execution of various clauses or parts of SQL SELECT statement? 

In what order a SELECT query is executed in SQL? 

Why SELECT clause is executed at the end in most of the SELECT statements?

In which order, the clauses in the following query executed? Why?

List and execute the SELECT query clauses execution order in detail.

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery