SQL

113 cards·by wood1626

Study this deck

Statement used to select data from a database.

SELECT

statement is used to return only distinct (different) values.

SELECT DISTINCT

The following SQL statement selects only the distinct values from the "City" columns from the "Customers" table.

SELECT DISTINCT City FROM Customers;

clause is used to extract only those records that fulfill a specified criterion.

WHERE

Search for a pattern

To specify multiple possible values for a column

keyword is used to sort the result-set by one or more columns.

ORDER BY

keyword used to sort the result-set in descending order

ORDER BY DESC

statement is used to insert new records in a table.

INSERT INTO

statement is used to update records in a table.

UPDATE

statement is used to delete records in a table.

DELETE

clause is used to specify the number of records to return.

SELECT TOP

A substitute for zero or more characters

A substitute for a single character

Sets and ranges of characters to match

[charlist]

Matches only a character NOT specified within the brackets

[^charlist]

operator is used to select values within a range.

BETWEEN

Not equal. Note: In some versions of SQL this operator may be written as !=

< >

To display the products outside the range

NOT BETWEEN

used to temporarily rename a table or a column heading.

SELECT AS

return all rows from multiple tables where the join condition is met.

INNER JOIN

Return all rows from the left table, and the matched rows from the right table

LEFT JOIN

Return all rows from the right table, and the matched rows from the left table

RIGHT JOIN

Return all rows when there is a match in ONE of the tables

FULL JOIN

operator is used to combine the result-set of two or more SELECT statements.

UNION

statement copies data from one table and inserts it into a new table.

SELECT INTO

statement copies data from one table and inserts it into an existing table.

INSERT INTO SELECT

statement is used to create a database.

CREATE DATABASE

statement is used to create a table in a database.

CREATE TABLE

Indicates that a column cannot store NULL value

NOT NULL

Ensures that each row for a column must have a unique value

UNIQUE

unique identity which helps to find a particular record in a table

PRIMARY KEY

Ensure the referential integrity of the data in one table to match values in another table

FOREIGN KEY

Ensures that the value in a column meets a specific condition

CHECK

Specifies a default value when specified none for this column

DEFAULT

constraint enforces a column to NOT accept NULL values.

NOT NULL

creates a unique constraint on a column when the table is already created

ADD CONSTRAINT UNIQUE

To drop a unique constraint

DROP CONSTRAINT

Creates a unique index on a table where duplicate values are not allowed

CREATE UNIQUE INDEX

statement is used to create indexes in tables.

CREATE INDEX

statement is used to delete an index in a table.

DROP INDEX

statement is used to delete a table.

DROP TABLE

statement is used to delete a database.

DROP DATABASE

deletes only the data inside the table

TRUNCATE TABLE

statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE

to change the data type of the column

ALTER COLUMN

to delete a column

DROP COLUMN

allows a unique number to be generated when a new record is inserted into a table.

AUTO_INCREMENT

creates a view

CREATE VIEW

delete a view

DROP VIEW

Update a view

CREATE VIEW

(MySQL) Returns the current date and time

NOW()

(MySQL) Returns the current date

CURDATE()

(MySQL) Returns the current time

CURTIME()

(MySQL) Extracts the date part of a date or date/time expression

DATE()

(MySQL) Returns a single part of a date/time

EXTRACT()

(MySQL) Adds a specified time interval to a date

DATE_ADD()

(MySQL) Subtracts a specified time interval from a date

DATE_SUB()

(MySQL) Returns the number of days between two dates

DATEDIFF()

(MySQL) Displays date/time data in different fomats

DATE_FORMAT()

(SQL server) Returns the current date and time

GETDATE()

(SQL server) Returns a single part of a date/time

DATEPART()

(SQL server) Adds or subtracts a specified time interval from a date

DATEADD()

(SQL server) Returns the time between two dates

DATEDIFF()

Displays date/time in different formats

CONVERT()

(MySQL) format YYYY-MM-DD

DATE

MySQL) format: YYYY-MM-DD HH:MM:SS

DATETIME

format: YYYY-MM-DD HH:MM:SS

TIMESTAMP

(MySQL) format YYYY or YY

YEAR

(SQL Server) format YYYY-MM-DD

DATE

format: YYYY-MM-DD HH:MM:SS

DATETIME

format: YYYY-MM-DD HH:MM:SS

SMALLDATETIME

(SQL Server) format: a unique number

TIMESTAMP

represent missing unknown data

NULL

select only the records with NULL values

IS NULL

select records w/ no NULL values

IS NOT NULL

function for treating NULL values

ISNULL()

Character string. Fixed-length n

CHARACTER(n)

Character string. Maximum length n

VARCHAR(n)

Binary string. Fixed-length n

BINARY(n)

Stores TRUE or FALSE values

BOOLEAN

Binary string. Max length n.

VARBINARY(n)

Integer # (no decimal). Precision 10

INTEGER(p)

Integer # (no decimal). Precision 5

SMALLINT

Integer # (no decimal).Precision 10

INTEGER

Integer # (no decimal). Precision 19

BIGINT

Exact numerical, precision p, scale s.

DECIMAL(p, s)

Exact numerical, precision p, scale s.

NUMERIC(p,s)

Float # in base 10 exp with precision p.

FLOAT(p)

Approx numerical, mantissa precision 7

REAL

Approx numerical, mantissa precision 16

FLOAT

Approx numerical, mantissa precision 16

DOUBLE PRECISION

Stores year, month, and day values

DATE

Stores hour, minute, and second values

TIME

Stores year, month, day, hr, min, sec values

TIMESTAMP

period of time composed of integers

INTERVAL

A set-length, ordered set of elements

ARRAY

unset length, unordered set of elements

MULTISET

Stores XML data

XML

Function returns the average value

AVG()

Function returns the number of rows

COUNT()

Function returns the first value

FIRST()

Function returns the last value

LAST()

Function returns the largest value

MAX()

Function returns the smallest value

MIN()

Function returns the sum

SUM()

Function converts a field to upper case

UCASE()

Function converts a field to lower case

LCASE()

Function extracts characters from a text field

MID()

Function returns the text length

LEN()

rounds to a specified # of decimals

ROUND()

current system date and time

NOW()

Function formats a field

FORMAT()