SQL Cheat Sheet: FUNCTIONS and Implicit JOIN

-

 

SQL Cheat Sheet: FUNCTIONS and Implicit JOIN

CommandSyntaxDescriptionExample
COUNTSELECT COUNT(column_name) FROM table_name WHERE condition;COUNT function returns the number of rows that match a specified criterion.SELECT COUNT(dep_id) FROM employees;
AVGSELECT AVG(column_name) FROM table_name WHERE condition;AVG function returns the average value of a numeric column.SELECT AVG(salary) FROM employees;
SUMSELECT SUM(column_name) FROM table_name WHERE condition;SUM function returns the total sum of a numeric column.SELECT SUM(salary) FROM employees;
MINSELECT MIN(column_name) FROM table_name WHERE condition;MIN function returns the smallest value of the SELECTED column.SELECT MIN(salary) FROM employees;
MAXSELECT MAX(column_name) FROM table_name WHERE condition;MAX function returns the largest value of the SELECTED column.SELECT MAX(salary) FROM employees;
ROUNDSELECT ROUND(2number, decimals, operation) AS RoundValue;ROUND function rounds a number to a specified number of decimal places.SELECT ROUND(salary) FROM employees;
LENGTHSELECT LENGTH(column_name) FROM table;LENGTH function returns the length of a string (in bytes).SELECT LENGTH(f_name) FROM employees;
UCASESELECT UCASE(column_name) FROM table;UCASE function displays the column name in each table in uppercase.SELECT UCASE(f_name) FROM employees;
LCASESELECT LCASE(column_name) FROM table;LCASE function displays the column name in each table in lowercase.SELECT LCASE(f_name) FROM employees;
DISTINCTSELECT DISTINCT column_name FROM table;DISTINCT function is used to display data without duplicates.SELECT DISTINCT UCASE(f_name) FROM employees;
DAYSELECT DAY(column_name) FROM table DAY function returns the day of the month for a given date.SELECT DAY(b_date) FROM employees where emp_id = 'E1002';
CURRENT_DATESELECT CURRENT_DATE;CURRENT_DATE is used to display the current date.SELECT CURRENT_DATE;
DATEDIFF()SELECT DATEDIFF(date1, date2); DATEDIFF() is used to calculate the difference between two dates or time stamps. The default value generated is the difference in number of days.SELECT DATEDIFF(CURRENT_DATE, date_column) FROM table;
FROM_DAYS()SELECT FROM_DAYS(number_of_days); FROM_DAYS() is used to convert a given number of days to YYYY-MM-DD format.SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE, date_column)) FROM table;
DATE_ADD()SELECT DATE_ADD(date, INTERVAL n type); DATE_ADD() is used to calculate the date after lapse of mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days after what is mentioned in date column. The type valiable can also be months or years.SELECT DATE_ADD(date, INTERVAL 3 DAY);;
DATE_SUB()SELECT DATE_SUB(date, INTERVAL n type); DATE_SUB() is used to calculate the date prior to the record date by mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days before what is mentioned in date column. The type valiable can also be months or years.SELECT DATE_SUB(date, INTERVAL 3 DAY);;
SubquerySELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])Subquery is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

SELECT emp_id, fmame, lname, salary
FROM employees
where salary
< (SELECT AVG(salary)
FROM employees);

 SELECT * FROM ( SELECT emp_id, f_name, l_name, dep_id FROM employees) AS emp4all;


 SELECT * FROM employees WHERE job_id IN (SELECT job_ident FROM jobs);

Implicit Inner JoinSELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name;Implicit Inner Join combines two or more records but displays only matching values in both tables. Inner join applies only the specified columns.SELECT * FROM employees, jobs where employees.job_id = jobs.job_ident;
Implicit Cross JoinSELECT column_name(s) FROM table1, table2;Implicit Cross Join is defined as a Cartesian product where the number of rows in the first table is multiplied by the number of rows in the second table.SELECT * FROM employees, jobs;

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

Reading: Additional Sources of Datasets

-
  In this reading, you will learn about: Open datasets and sources Proprietary datasets and sources Dataset license Open datasets and sources In this data-driven world, some datasets are freely available for anyone to access, use, modify, and share. These are called  open datasets . Open datasets include a public license and are very useful for your journey as a Data Scientist. Some of the most informative open dataset sources are listed below. Government Data: https://www.data.gov/ https://www.census.gov/data.html https://data.gov.uk/ https://www.opendatanetwork.com/ https://data.un.org/ Financial Data Sources: https://data.worldbank.org/ https://www.globalfinancialdata.com/ https://comtrade.un.org/ https://www.nber.org/ https://fred.stlouisfed.org/ Crime Data: https://www.fbi.gov/services/cjis/ucr https://www.icpsr.umich.edu/icpsrweb/content/NACJD/index.html https://www.drugabuse.gov/related-topics/trends-statistics https://www.unodc.org/unodc/en/data-and-analysis/ Health Da...
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