SQL

What keyword selects from a range of records?

BETWEEN

TRUNCATE

TRUNCATE TABLE will delete all the data in a table but keep the table itself. TRUNCATE TABLE table_name

CREATE DATABASE

creates a new database

CREATE TABLE

creates a new table

CREATE INDEX

creates an index (search key)

What does the :: symbol do?

:: is a shortcut for casting a value to a specific type in SQL In addition to using cast, you can use :: to cast a value to a specific type. select cast('2016-12-25' as date) as christmas becomes select '2016-12-25'::date as christmas

PRIMARY KEY

A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null. So, the following two table definitions accept the same data: CREATE TABLE products ( product_no integer UNIQUE NOT NULL, name text, price numeric ); CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); Primary keys can span more than one column; the syntax is similar to unique constraints: CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) ); Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key, and will force the column(s) to be marked NOT NULL.

ALTER DATABASE

ALTER DATABASE name [ [ WITH ] option [ ... ] ] where option can be: CONNECTION LIMIT connlimit ALTER DATABASE name RENAME TO new_name ALTER DATABASE name OWNER TO new_owner ALTER DATABASE name SET TABLESPACE new_tablespace ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT } ALTER DATABASE name SET configuration_parameter FROM CURRENT ALTER DATABASE name RESET configuration_parameter ALTER DATABASE name RESET ALL

CRUD

C reate R ead U update D elete

CREATE TABLE

CREATE TABLE table_name ( column_name1 DATA_TYPE(size), column_name2 DATA_TYPE(size), column_name3 DATA_TYPE(size), .... );

C.R.U.D.

Create, Retrieve, Update, Delete

DEFAULT

DEFAULT 'default_name' This sets a default value in a given column if a value is not provided

how to insert multiple records into table at once?

INSERT INTO table_name (field, field) VALUES (value, value), (value, value), (value, value), (value, value);

What is the indentation convention in sql?

It's important to pick a style and stick with it. I'll probably use the four spaces convention for indentation. I'd also like to generally follow the "coding" conventions I've learned concerning scope and precedence... i think it will be logical. Style 1 - each clause begins on a new line, none is indented select s.businessentityid, e.jobtitle from sales.salesperson as s inner join humanresources.employee as e on e.businessentityid = s.businessentityid Style 2 - each clause and each set of arguments begins on a separate line. Each statement subordinate to SELECT is also indented SELECT S.Businessentityid, E.Jobtitle FROM Sales.Salesperson AS S INNER JOIN Humanresources.Employee AS E ON E.Businessentityid = S.Businessentityid; Style 3 - indent all list items equally, together with all columns and table names SELECT S.Businessentityid, E.Jobtitle, S.Salesquota, S.Salesytd, S.Saleslastyear FROM Sales.Salesperson AS S INNER JOIN Humanresources.Employee AS E ON E.Businessentityid = S.Businessentityid;

VALUES

Keyword VALUES for manipulating the data

Are SQL statements case sensitive?

NO SQL itself is not case sensitive, however because of user interactions and naming, it's a good idea to treat it as such

SELECT Name, LifeExpectancy AS 'Life Expectancy' FROM Country ORDER BY Name;

Returns Names of country and LifeExpectancy labeled as 'Life Expectancy' alphabetized from Country table.

SQL Joins

SQL joins are used to combine rows from two or more tables. An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

DELETE FROM

The DELETE statement is used to delete records in a table. DELETE FROM table_name WHERE some_column=some_value; *** CAUTION!!! *** The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

SELECT DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values. SELECT DISTINCT column1, column2, ... FROM table_name;

WHERE

The WHERE clause is used to filter records. SELECT column_name,column_name FROM table_name WHERE column_name operator value ORDER BY;

Expressions in SQL

Used to derive values from data

list all tables in a database?

\dt

list all databases from terminal?

\l

% as a pattern search operator in SQL

Zero or more characters

NOT NULL

a constraint that will prevent records from being entered if a NULL is present in a NOT NULL column.

AS

allows you to return a column with a different label on it Select Population AS 'Country Population' AS is not necessary but can make the code easier to read. When stating label names, enclose labels with spaces in quotes

DROP INDEX

deletes an index

INNER JOIN

joining allows you to relate data in one table to the data in other tables only using results where specified conditions are met

command to open postgres in the terminal?

psql

COUNT

returns a count of the number of rows in the given query

what are the various acceptable boolean values?

1, TRUE, yes, t, y 0, FALSE, no, f, n

What does underscore return in pattern search

Any single character

what will the search pattern _a% return?

Any string in which the second letter is an a

DROP TABLE

Indexes, tables, and databases can easily be deleted/removed with the DROP statement. DROP TABLE table_name

DROP

Indexes, tables, and databases can easily be deleted/removed with the DROP statement. DROP DATABASE database_name DROP TABLE table_name

Basic indention convention

Indent two spaces and indent by what things are related to. Begin indentations with keywords and leave expressions hanging wherever possible.

How is white space handled in SQL?

It is ignored except when to separate tokens

How to indicate a comment in SQL?

Single line comments start with --. Any line between -- and the end of the line will be ignored (will not be executed). Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored.

ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ALTER TABLE table_name ADD column_name datatype To delete a column in a table, use the following syntax (some database systems don't allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name To change the data type of a column in a table, use the following syntax: SQL Server / MS Access: ALTER TABLE table_name ALTER COLUMN column_name datatype My SQL / Oracle (prior version 10G): ALTER TABLE table_name MODIFY COLUMN column_name datatype Oracle 10G and later: ALTER TABLE table_name MODIFY column_name datatype

what is the keyword ALTER TABLE used for?

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE table_name ADD/DROP column_name DATA_TYPE;

DELETE

The DELETE statement is used to delete rows in a table. DELETE FROM table_name WHERE some_column=some_value; It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: DELETE FROM table_name; or DELETE * FROM table_name;

INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. We can copy all columns from one table to another, existing table: INSERT INTO table2 SELECT * FROM table1; Or we can copy only the columns we want to into another, existing table: INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

ORDER BY

The ORDER BY keyword is used to sort the result-set. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC;

LIMIT

The SELECT LIMIT statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a limit value. TIP: SELECT LIMIT is not supported in all SQL databases. For databases such as SQL Server or MSAccess, use the SELECT TOP statement to limit your results.

SELECT

The SELECT statement is used to select data from a database or return a value in SQL. The result is stored in a result table, called the result-set. SELECT column_name,column_name FROM table_name; SELECT * FROM table_name; SELECT 1 + 3, 2* 6;

UNIQUE

The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. NULL values are exempted from the UNIQUE constraint Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

UPDATE table

The UPDATE statement is used to update existing records in a table. UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value; *****Notice the WHERE clause in the SQL UPDATE statement!******* The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

What is a Token?

The basic syntactical units of the SQL language are called tokens . A token consists of one or more characters of which none are blanks, control characters, or characters within a string constant or delimited identifier.

FROM

The destination from which to select from often a table

What is the database schema?

The visual representation of all tables, fields, objects, relationships et cetera A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data. ... It defines tables, views, and integrity constraints.

How to update a range of records?

WHERE id BETWEEN first_record and last_record

command to connect to a database in terminal?

\connect database_name

DROP

delete or 'drops' a table/column

INSERT INTO

inserts new data into a table. INSERT INTO table_name VALUES (values); Can also select which fields to insert into like this: INSERT INTO table_name(field_name) VALUES(value);

*

select all