IDis the primary key of the Staff table (left), with
Staff No.being the primary key of the Staff Details table (right), and foreign key of the Staff table.
SELECTstatement is used to select data from a database.
SELECTstatement must be made up of the following components:
FROMclause to choose the location that you are selecting from. (If the
FROMclause is not used, or does not have a specified table, the query will error.)
DISTINCTclause is used to select data without duplicate values. This should only be used when you wish to select
DISTINCTdata from one column.
WHEREwill limit or filter the results of a query.
WHEREstatement will always come after the
WHERE x = y
WHERE x < y
WHERE x > y
WHERE x <= y
WHERE x >= y
WHERE x != y
WHERE x <> y
WHERE Date BETWEEN ‘01 Jan 20’ AND ‘31 JAN 20’
IN- this can be used to specify one or more values to match:
WHERE surname IN (‘Smith’, ‘Stock’, ‘Jones’);
VARCHAR– if using
WHEREto match a varchar field (essentially free text), you will need to put your text to match in single quotes. By default, SSMS will turn these text inputs red:
WHERE surname = Smith;
WHEREclauses with logical operators:
ANDcan be used when 2 (or more)
WHEREs need to be met at the same time.
ORcan be used when any 1 of 2 (or more)
WHEREs can be met.
TOPoperator to specify how many rows to return.
TOPwill return the first (x) records as specified.
DESCare specified, SQL will assume
WHEREclause, it is advisable to first filter by date (if possible!).
JOIN, each with a different function.
RIGHT JOINcan return
WHERE) when needed.
INNER JOIN. This combines columns from 1 or more tables, which can be saved as a table or used in result form.
LEFT JOIN. This will return all rows from the left table, with the matching rows from the right table – provided there is a match. If there is no match,
NULLwill be returned for the right table.
JOIN, if you wish to select a specific column, you will need to specify the table name as well.
BEGIN TRANand then not following it with
ROLLBACK) will lock the table(s) being operated upon and prevent all writing until the transaction is committed or rolled back.
PRINT @@TRANCOUNT– if the number is not 0, then there is an open transaction.
SET Surname = 'Smith'
WHERE Surname = 'Smit'
COUNT()- returns the number of rows that matches a specified criteria.
SUM()- returns the total sum of a numeric column.
MIN()- returns the largest/smallest value of the selected column.
HAVING– groups the result-set by one or more columns.
LCASE- converts the value of a field to uppercase/lowercase.
LEN()- returns the length of the value in a text field.
ROUND()- used to round a numeric field to the specified number of decimals.
DATEDIFF(datepart, startdate, enddate)
DATEADD(datepart, number, date)
*This is actually a legitimate use of P2P transfers, as you’re not pirating anything.