- VIEW - A view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
SQL CHEAT SHEET SQL GROUP BY The GROUP BY clause is usually used with an aggregate function (COUNT, SUM, AVG, MIN, MAX). It groups the rows by a given column value (specified after GROUP BY) then calculates the aggregate for each group and returns that to the screen. SELECT column1, COUNT(column2) FROM tablename GROUP BY column1.
SQL Cheatsheet Understanding data with SQL Clauses Clauses are distinct parts of an SQL statement. Put each on its own line and capitalize as below to increase legibility. Here are the five you will find most useful for understanding data: SELECT List the columns you want to show. selects all columns. FROM table Specify the table you want. Cheatsheet for all. Employeeid startdate enddate jobid departmentid; 102: 1993-01-13: 1998-07-24. SQL cheat sheet. GitHub Gist: instantly share code, notes, and snippets. SQL Cheat Sheet 2 - SELECT DML (Data Manipulation Language): The SQL commands that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
LIKE Logical Operator - used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator.
BETWEEN Logical Operator - selects values within a given range. The values can be numbers, text, or dates.
IS NULL Logical Operator - get records have NULL
IN Logical Operator - allows you to specify multiple values in a WHERE clause
EXISTS Operator - used to test for the existence of any record in a subquery and returns true if the subquery returns one or more records.
CASE - CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
References:
https://www.w3schools.com/sql/default.asp
In this post I wanted to share some helpful codes I learned from SQL and my own SQL cheat sheet. As far as I learn more programming languages I'll create more cheat sheets, hope they'll be helpful.
DOWNLOAD THE CHEAT SHEET:
Example codes:
Python library sqlite 3
This way shows how to read SQL from python with the library sqlite3.
This other way shows how to call a query using pandas library and save it as a data frame.
Subqueries
Sql Query Cheat Sheet Pdf
This code shows how to use a subquery as a condition.
In this code it's shown how to make multiples subqueries in different situations.
Sql Cheat Sheet Github Pdf
Multiple tables Joins
This example shows how to join multiple tables by different keys applying some conditions.
Picture retrieved from the SQL course www.dataquest.io
The next code below shows a multi-join operation using subqueries and group by among others.
CASE, WITH AND VIEW CLAUSES
The CASE clause allows to create a temporal column modifying its content using conditionals. This example shows how to use CASE clause and join operations with subqueries.
The WITH clause creates a temporal subquery that can be used as another table. This example shows how to use WITH clause in a multi-join operation.
Sql Cheat Sheet Github 2019
The VIEW clause works as the WITH clause allowing to save the new table for other queries. The next example shows how to create a VIEW and call it after.
OTHER LINKS:
Oracle Sql Cheat Sheet
- Github repository: cheatsheets
- A nice website to learn SQL: https://www.codecademy.com/
- Another nice website to learn SQL: https://www.dataquest.io/
- More resources like this here: https://carlosgrande.me/category/resources