SQL Query
SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database.A query is a question or inquiry about a set of data.SQL is used for accessing and manipulating databases. The major commands that SQL uses are(SELECT, INSERT, DELETE,UPDATE)
A select SQL query is written as:
SELECT * FROM table.
Where “* “ means all.
SQL query with constraints is written as:
SELECT column_1, column_2, …
FROM table
WHERE condition
AND/OR other_condition
Many operator can be used to write the conditions.
Some keywords can be used to write SQL queries which helps in filtering and sorting results
DISTINCT — Blindly removes duplicate rows.
GROUP BY — Discards duplicates based on specific columns using grouping
ORDER BY — Sorts each row alpha-numerically in ascending or descending order or specify a collation to better sort data containing international text. It is used in specific columns.
For limiting the quantity of result “LIMIT” keyword is used and “OFFSET “keyword which row number should it begin from.
Queries written on multiple tables have “JOINS”
There are two types of “JOINS” :- INNER JOIN and OUTER JOIN
The major difference between these two JOINS are, INNER JOIN result in the intersection of two tables, whereas OUTER JOIN result in the union of two tables.
OUTER JOIN can be of several types:- LEFT JOIN, RIGHT JOIN, FULL JOIN
SELECT Queries can also have expressions and they can also have aggregates.
Besides SELECT queries there are other types of queries:
CREATE — to create a table with columns and data types
Different databases support different data types, but the common types support numeric, string, and other miscellaneous things like dates, boolean, or even binary data. Here are some examples.
Data type
Description
INTEGER, BOOLEAN
The integer data types can store whole integer values like the count of a number or an age. In some implementations, the boolean value is just represented as an integer value of just 0 or 1.
FLOAT, DOUBLE, REAL
The floating point data types can store more precise numerical data like measurements or fractional values. Different types can be used depending on the floating point precision required for that value.
CHARACTER(num_chars), VARCHAR(num_chars), TEXT
The text-based data types can store strings and text in all sorts of locales. The distinction between the various types generally amount to underlying efficiency of the database when working with these columns.
Both the CHARACTER and VARCHAR (variable character) types are specified with the max number of characters that they can store (longer values may be truncated), so can be more efficient to store and query with big tables.
DATE, DATETIME
SQL can also store date and time stamps to keep track of time series and event data. They can be tricky to work with especially when manipulating data across time zones.
BLOB
Finally, SQL can store binary data in blobs right in the database. These values are often opaque to the database, so you usually have to store them with the right metadata to requery them.
INSERT — to insert rows in a table
UPDATE — to update rows with new values
DELETE — To delete rows from a table
ALTER — to alter table by adding and removing new columns to the table. It can contain default values.
Each database implementation supports different methods of altering their tables, so it’s always best to consult your database docs.
each column can have additional table constraints on it which limit what values can be inserted into that column. Below are some common examples.
Constraint
Description
PRIMARY KEY
This means that the values in this column are unique, and each value can be used to identify a single row in this table.
AUTOINCREMENT
For integer values, this means that the value is automatically filled in and incremented with each row insertion. Not supported in all databases.
UNIQUE
This means that the values in this column have to be unique, so you can’t insert another row with the same value in this column as another row in the table. Differs from the `PRIMARY KEY` in that it doesn’t have to be a key for a row in the table.
NOT NULL
This means that the inserted value can not be `NULL`.
CHECK (expression)
This allows you to run a more complex expression to test whether the values inserted are valid. For example, you can check that values are positive, or greater than a specific size, or start with a certain prefix, etc.
FOREIGN KEY
This is a consistency check which ensures that each value in this column corresponds to another value in a column in another table.
For example, if there are two tables, one listing all Employees by ID, and another listing their payroll information, the `FOREIGN KEY` can ensure that every row in the payroll table corresponds to a valid employee in the master Employee list.
DROP — to drop the entire table
I have included my gist here which holds the SQL solutions from Question number 2 in SQL lesson 8 from SQL bolt.
Resources
