SQL Cheat Sheet: Intermediate - LIKE, ORDER BY, GROUP BY

-

 

SQL Cheat Sheet: Intermediate - LIKE, ORDER BY, GROUP BY

CommandSyntaxDescriptionExample
LIKESELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Two wildcards often used in conjunction with the LIKE operator are percent sign(%) and underscore sign (_), depending upon the SQL engine being used.

SELECT f_name , l_name FROM employees WHERE address LIKE '%Elgin,IL%';
This command will output all entries with Elgin,IL in the Address.
BETWEENSELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
This generates all records of employees with salaries between 40000 and 80000.
ORDER BYSELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; ORDER BY keyword is used to sort the result-set in ascending or descending order. The default is ascending. In case of multiple columns in ORDER BY, the sorting will be done in the sequence of the appearance of the arguments.SELECT f_name, l_name, dep_id FROM employees ORDER BY dep_id DESC, l_name;
This displays the first name, last name, and department ID of employees, first sorted in descending order of department IDs and then sorted alphabetically as per their last names.
GROUP BYSELECT column_name(s) FROM table_name GROUP BY column_name(s) GROUP BY clause is used in collaboration with the SELECT statement to arrange data with identical values into groups.SELECT dep_id, COUNT(*) FROM employees GROUP BY dep_id;
This returns the department IDs and the number of employees in them, grouped by the department IDs.
HAVINGSELECT column_name(s) FROM table_name GROUP BY column_name(s) HAVING condition HAVING clause is used in conjunction with GROUP BY clause in collaboration with the SELECT statement in order to filter the data as per the given condition and then group as per identical values of a specified parameter.SELECT DEP_ID, COUNT(*) AS "NUM_EMPLOYEES", AVG(SALARY) AS "AVG_SALARY" FROM EMPLOYEES GROUP BY DEP_ID HAVING count(*) < 4 ORDER BY AVG_SALARY;

Comments

Post a Comment

Popular posts from this blog

Lila's Journey to Becoming a Data Scientist: Her Working Approach on the First Task

-
  Education and Skill Acquisition: Lila, with an economics background, decides to transition to data science. Enrolls in the IBM Data Science Professional Certificate for comprehensive training. Gains proficiency in statistics, machine learning, and programming languages. Building a Strong Foundation: Develops a deep understanding of data manipulation and visualization using Python libraries. Visualization for Storytelling: Learns to create informative visualizations, aiding in effective communication of findings. Hands-On Experience: Engages in Kaggle competitions and personal data projects for practical experience. Establishes a GitHub profile to showcase her projects and skills. Data Wrangling and Preprocessing: Focuses on data cleaning, preprocessing, and handling missing data and outliers. Communication and Storytelling: Hones data storytelling skills using tools like Matplotlib and Plotly. Networking and Collaboration: Actively participates in data science communities, attend...
Read more

Notes on Hiring for Data Science Teams

-
  Unicorn Myth in Hiring: Companies often seek individuals with a myriad of skills for data science roles. Ideal candidates possess domain-specific knowledge, excel in data analysis, and have strong presentation and storytelling skills. However, finding such a "unicorn" is rare, and the focus should shift to other essential qualities. Resonance with Company DNA: The key is to identify applicants who resonate with the company's values and business domain. While analytics skills can be taught, passion for the business is intrinsic and contributes significantly to productivity. Curiosity Matters: Curiosity is a top priority in hiring for a data science team. Candidates should demonstrate a broad curiosity, showing interest in various aspects of their surroundings beyond just data science. Sense of Humor: A lighthearted approach is crucial, as being too serious may hinder collaboration and creativity. A sense of humor aids in navigating challenges and fosters a positive work ...
Read more

switch functions

-
  Nested IF Function : The  nested IF  function allows you to perform tests with multiple conditions and return different values based on the results of those tests. It is called “nested” because one IF function is embedded inside another IF function. Syntax of a generic nested IF formula: =IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4) ) ) Each subsequent IF function is embedded into the  value_if_false  argument of the previous function. SWITCH Function : The  SWITCH  function is a more compact alternative to nested IF statements. It compares an expression against a list of values and returns the result according to the first matching value. Syntax of the SWITCH function: =SWITCH(expression, value1, result1, [default or value2, result2], ... [default or valueN, resultN]) It has four arguments: expression : The value to compare against the list of values. valueN : A value com...
Read more