SQL Hacks

 

Topic

Four fundamental functions of a data base system: CRUD: Create, Read, Update, Delete

In standard SQL, the SELECT statment is used for all queries that return values

The INSERT statement is used to add a row to a table

The UPDATE statement is used to change data

the DELETE statement is used to remove rows from a table

How to use SELECT statement, to retrieve data from SQL database

How to write the select statement

To write the select statement as a column name, to create an alias identifier to the column, or using AS statement

To get all the rows and columns of a table or using *

How to check the number of rows in table you have just received using SELECT

To get all the rows and columns of a table, ordered by a column

Select n columns from a table while retrieving

change the name of a column to something else when retrieving a table

change the name of a column to something else but in quotes

Select rows from a table using WHERE clause ( as a logical expression or filter)

Select rows from a table using WHERE clause ( as a logical expression or filter), ordered by a column, Or using ORDER BY

How to limit the number of rows in your SELECT result, or using LIMIT

Suppose I want to start by skipping first 5 results, or using OFFSET, how to do it

Order is important in SQL , FROM-> WHERE-> ORDER BY -> LIMIT --> OFFSET

How to select all the rows and all the columns or using *

Order is not important in how you select the columns from retrieving

How to count rows instead of listing them- using COUNT function

to count all rows

To count rows with a filter condition; using WHERE

To count rows with mulitple conditions using AND clause

Count all the rows of a particular column which has data

How to use INSERT INTO function to add rows to a table with the values using VALUES in selected columns

How to insert less values into a table with more columns, means to populate only some of the columns

NULL values means no data

Update the values in columns of a table with a given row ID using UPDATE, SET, WHERE

How to update the values in columns with null values

How to SELECT the fourth row of a table

Delete the fourth row of a table using DELETE, WHERE

how to create a table with CREATE TABLE statement

The column definitions are sometimes called database schema or the table schema

Insert values into table

Deleting a table using DROP TABLE

Deleting a table after checking if it exist by using IF EXISTS

To add a row or rows to a table

to Insert a rows with values

to insert a rows with some values in some of the columns

to insert default values in the table as a row

to insert values from some other table

Using DELETE from statement to delete rows where a column has certain value

NULL is a special state in SQL, it represents the absence of a value

How to SELECT if a column has a value NULL, you cannot put a = NULL

How to SELECT if a column doesn't have a value NULL

How to create a table where we put a constraint that a cell cannot have a null value, create a column with a not null constraint

how can you add a NOT NULL constraint in one or more of the column

How to give a column a default value while creating table using DEFAULT

How to make every value in the column unique using UNIQUE

how to change the table schema after it is defined use ALTER TABLE, eg. create a new column , also using ADD

An ID Column is a column that holds a unique value for each row in a table. Typically ID Columns are auto populated

Creating ID field, if needed using PRIMARY KEY

How to filter rows with a criteria

How to arrange the results in the descening order using DESC

How to filter rows where column contain null values and using OR

How to filter rows where column contain the word 'island' using LIKE and %

How to filter rows where column contain word that starts with the word 'island'

How to filter rows where column contain any letter as first letter but contain a in the second letter using '_' operator

How to filter rows where a column has some values using IN operator

How to find the values in a column without duplication with SELECT DISTINCT command, to get only unique results

Example of removing duplicates

Order by One column

Order by One column descending ( from high to low)

Order by One column descending ( from low to high)

Order by two columns

Select three columns Order by three columns, one column descending and two columns default

CASE WHEN value of a column is true or 1 THEN select one column ELSE select other column and then END ing the command, using if

Visualization of Joined Query

What is inner join or simple join

What is left outer join

What is right outer join

What is Full outer join

creating a join query to inner join two tables using JOIN and ON

creating a left outer join by using LEFT OUTER JOIN

An example of simple inner join

Relating multiple tables, joining three tables

Example of LEFT OUTER JOIN

How to write the SQL string

concatenating two strings using ||, CONCAT or +

Types of string functions, SUBSTR, LENGTH, TRIM, UPPER, LOWER

How to find the length of a string or a colunn of stings in SQL

How to find the length of a string or a column of strings in SQL

How to find a substring of a string or a column of strings in SQL starting from a position

How to find a substring of a string or a column of strings in SQL starting from a position and ending to a position

How to separate day, month and year from a date or a column of dates using substring

Removing space from a column of string or a string using TRIM, RTRIM and LTRIM

Converting upper and lower case for all the values in the column

Fundamental Numeric types

Interger types

Real types

Precision vs scale

to find the type of a value using TYPEOF

How to change one type of value to other using CAST

Rounding numbers or a column of numbers using ROUND

SQL standard datetime format

SQL dates and Time types

How to return the current date and time using DATETIME

How to return just the date using DATE

How to return just the time using TIME

How to add one day to the current date time

How to subtract one month from the current date time

How to add one year to the current date time

How to add 3 hours, add 27 minutes, and subtract one and and add three years from the current date time

Aggregate data is information derived from more than one row at a time

What is the total count of rows in the database

What is the toal count of rows aggregated by one of the columns using GROUP BY

What is the toal count of rows aggregated by one of the columns using GROUP BY, order by count from high to low

What is the total number of rows group by one of the columns in a joined table

How to group by a column with a filter condition using HAVING

How to group by a column with a filter condition using HAVING and a condition in the non aggregate part of the query using WHERE

WHERE clause has to be before GROUP BY clause( Non Aggregate data) and HAVING should be after the GROUP BY clause ( Aggregate data)

There are functions that operate on the aggregate values

How to count the numbers of rows in a column

How to find the average of rows in a column using AVG

How to find the average of rows in a column group by another column

How to find the min and max of rows in a column which is grouped by another column

How to find the sum of values in a column which is grouped by another column

How to operate the aggregate function on the distinct values using DISTINCT

A transaction is a group of operations that are handled as one unit of work

If any one of the operations fail, the database is restored to its previous state

How to use transactions in SQL using BEGIN TRANSACTION and END TRANSACTION or COMMIT

How to abort using ROLLBACK

Transactions increse the performance of the statements between them

Triggers are operations that are automatically peformed when some database event happens

How to implement triggers in SQL using CREATE TRIGGER, BEGIN and END, AFTER INSERT ON and NEW

How to implement triggers to prevent changes, use of BEFORE

RAISE to raise an exception

How to use Triggers to create timestamps

A SELECT statement can be used as a datasource in another SELECT statement, called subselect

How to use SUBSTR, so that we can split the values as separate columns similar to split function in Excel

How to use the data sources of SUBSTR as a select in another statement

How to provide a list of rows for searching a table, suppose you want an album that have tracks with a duration of 90 seconds or less

You can save a query as a view with CREATE ViIEW and DROP VIEW

You can create any query and create a view out of it. Now you can use view just like table