SQL Pocket Guide (PostgreSQL) Flashcards
This deck is Built for PostgreSQL. It is based of the SQL Pocket Guide written by Alice Zhao and Published by O'Reilly (26 cards)
What does SQL stand for
Structured Query Language
What is an SQL Database?
It is a data base with tables or relations. It is also known as a Relational Database or RDB.
What is a NOSQL Database
It stands for Not Only SQL.This is a database which is not relational. It is just a suppository of unstructured information. It may have some tables in it though.
What is a DBMS
This is a Database Management System. It is software which is designed to manage Databases. examples of this are Microsoft SQL Server, SQL Light, PostgreSQL, and Orical.
what is an SQL query
This is similar to an SQL statement. The only difference is that a query only has read access and can only do the read function.
What is an SQL statement
An SQL statement is SQL code which can do any of the four operations from CRUD.
What is CRUD
A common acronym for SQL queries is CRUD. it stands for Create, Read, Update and Delete. These are the four main purposes to write an SQL query.
What is the order of a written SQL query
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
What is the order of execution of an SQL statement.
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
What is an RDBMS
it stands for Relational Database Management System. this is the software which executes you commands. You could also use a command line to use the data bases as well as embed the code into other languages such as Python or R. An RDBMS is the most common way though.
What is an SQL Function
an SQL function is a Keyword which takes in zero input or more input, does something to the input and returns an output. examples of this are COUNT(), and YEAR().
What is an identifier
An identifier is the name of a database object such as a table or a column.
What is an Alias
An Alias renames a column or a table temporarily, only for the duration of the query.
The standard is to use the keyword AS and no additional text when renaming tables and subqueries when required.
What is an expression
an expression can be thought of as a formula that results in a value. An example of an expression is COUNT(column_name).
What is a predicate
A predicate is a logical comparison which results in one of three values, TRUE/FALSE/UNKNOWN. These are found mostly in a WHERE or HAVING clause.
What is a CLAUSE
this is a line of code in SQL which begins with a Key word such as, SELECT, FROM, WHERE, HAVING, GROUP BY, etc.
What is a comment
a comment can be created by using – for a comment on a single line or
/*
comment
here
*/
for a comment on multiple lines.
a comment is used to pass on information to other users who may be using your code or your future self so that they can get a better understanding of what you are doing and why.
What is the SELECT clause
The SELECT clause returns the specific columns you want the statement to return.
SELECT expressions
In addition to listing columns, you can list more complex expressions within the select clause to be returned as columns in the results.
SELECT name, ROUND(population * 0.9, 0)
FROM county;
this would return a name column and a column which was 90% or the population which was rounded to a whole number.
SELECT function
expression in the SELECT list typically refer to columns in the table that you are pulling from, but there are expressions. For example a common expression that does not refer to any table is the one to return the current date
SELECT CURRENT_DATE:
What is Qualifying Columns
When you have two tables merged they may have a column each with the same name. To tell the database which column you mean you will have to Qualify the Column.
Select table_name.column_name, second_table_name.column_name
How do you Qualify a table
If you qualify a column by the table name you qualify a table by the database or schema name in the same way.
SELECT sqlbook.owner.id, sqlbook.owner.name
FROM sqlbook.owner;
Selecting Subqueries
A subquery is a query that is nested inside another query.
- subqueries must be surrounded by parenthesis.
- when you write a subquery in a SELECT clause it is highly recommended that you give it an ALIAS this way the column has a simple name.
Corelated and non corelated subquery
A Corelated subquery is a query which refers to the values in the outer query. A Non Corelated subquery is a query which does not refer to any of the values in the outer query.
