SQL Flashcards
What is SQL?
Structured Query Language is a special-purpose programming language designed for managing data held in a relational database system.
DDL
Data Definition Language: To create and modify the structure of the database
DML
Data Manipulation Language: To perform read, insert, update and delete operations on the data of the database
DCL
Data Control Language: To control the access of the data stored in the database
BigInt Storage in Bytes
8
Int Storage in Bytes
4
SmallInt Storage in Bytes
2
TinyInt Storage in Bytes
1
money Storage in Bytes
8
smallmoney Storage in Bytes
4
Char and Varchar
String data types of either fixed length or variable length
Nchar and Nvarchar
UNICODE string data types of either fixed length or variable length
UNIQUEIDENTIFIER
a 16 byte GUID / UUID
NULL
Means literally “Nothing”, the absence of any value
WHERE field IS NULL | WHERE field IS NOT NULL
SELECT
The select statement is at the heart of most SQL queries. It defines what result set should be returned by the query, and is almost always used in conjunction with the FROM clause, which defines what part(s9 of the database should be queried.
- the wildcard character
It’s used to select all available columns in a table
table.*
It’s used to select all values from a specific table
- wildcard character problems
Excess IO network load, memory use, and so on.
Potencial problem if you add more columns in the tables involved
When to use * in production environment
When using EXISTS, such as select A.col1, A.col2 from A where exists (select * from B where A.ID = b.A_ID). In COUNT(*) can be used.
Aliases
Column aliases are used mainly to shorten code and make column names more readable.
Aliases can be applied to Columns and table as well
WHERE
the condition can be any SQL expression, specified using comparison or logical operator like >, , >=, <=, LIKE, NOT, IN, BETWEEN
CASE
When results need to have some logic applied ‘on the fly’ one can user CASE statement to implement it.
SELECT case when col1 < 50 then ‘under’ else ‘over’ end threshold from tablename
Selecting without Locking the table
Sometimes when tables are used mostly (or only) for reads, indexing does not help anymore and every little bit counts, one might use selects without LOCK to improve performance.
MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM TableName;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
AVG
The AVG() aggregate function will return the avergae of values selected
MIN
The MIN() aggregate function will return the minimun of values selected.
MAX
The MAX aggregate function will return the maximun of values selected
COUNT
The COUNT() aggregate function will return the count of values selected
SUM
The SUM() aggregate function will retrun the SUM of the values selected for all rows
How filter results after grouping?
If you need to filter the results after the grouping has been done, you need to use the HAVING clause.
Embed a CASE statement in the ORDER BY clause
ORDER BY CASE WHEN LName=’Jones’ then 0 ELSE 1 END ASC
DISTINCT
Select unique values only
GROUP BY
Results of a SELECT query can be grouped by one or more columns suing the GROUP BY statement.
All results with the same value in the grouped columns are aggregated together.
This generates a table of partial results, instead of one result.
GROUP BY can be used in conjunction with aggregation functions using having statement to define how non-grouped columns are aggregated.
GROUP BY results using a HAVING
A HAVING clause filters the results of a GROUP BY expression.
ORDER BY
You can use a colum’s number to indicate wich column to base the sort on, instead of describing the column by its name
ORDER BY Customized sorting order
ORDER BY CASE Department WHEN 'HR' THEN 1 WHEN 'Accountant THEN 2 ELSE 3 END;
CASE
The case expression is used to implement if-then logic.
CASE Shorthand
Evaluate and expression against a series of values. CASE Price WHEN 5 THEN 'Cheap' WHEN 15 THEN 'Affordable' ELSE 'Expensive' END AS PriceRating
LIKE
The % wildcard appended to the beggining or end (or both) of a string will allow 0 or more of any character before the beggining of after the end of the pattern to match
All records where FName 3rd character is ‘n’ from Employees
select * from Employees WHERE FName LIKE ‘__n%’
Single character match
The _ (underscore) character can be used as a wildcard for any single character in a pattern match
ESCAPE statement in the LIKE-query
This expression can be a problem if @in_SearchText have ‘50%’
WHERE LIKE CONCAT(‘%’, @in_SearchText, ‘%’)
You need to add
ESCAPE ‘'
at the end
Search for a range of characters
Followin statement matches all records having FName that starts with a letter from A to F from Employees Table
SELECT * FROM Employees WHERE FName LIKE ‘[A-F]%’
Match by Range or set
Match any single character within the specified range ([a-f]) or set ([abcd])
WHERE FName LIKE ‘[a-g]ary’
WHERE FName LIKE ‘[lmnop]ary’
With ^ Negate the match
WHERE FName LIKE ‘[^a-g]ary’
WHERE FName LIKE ‘[^lmnop]ary’
Wildcard characters %
% - A substitute for zero or more characters
Wildcard characters _
_ - A substitute for a single character
Wildcard characters [charlist]
[charlist] - Sets and ranges of characters to match
Wildcard characters [^charlist]
[^charlist] - Matches only a character NOT specified within the brackets
IN clause
To get records having any of the given ids
You can use it for subquerys
BETWEEN
The Between operator is inclusive
Use Having with aggregate functions
Unlike the where clause, HAVING can be used with aggregate functions.
COUNT(), SUM(), MIN(), MAX()
WHERE EXISTS
Will select records in TableName that have records matchinf in tableName1
SELECT * FROM TableName t WHERE EXISTS (
SELECT 1 FROM TableName1 t1 where t.Id = t1.Id)
EXCEPT
Select dataset except where values are in this other dataset
EXPLAIN
An EXPLAIN in front of a select query shows you how the query will be executed.
This way you to see if the query uses an index or if you could optimize your query by adding an index.
DESCRIBE
DESCRIBE and EXPLAIN are synonyms, DESCRIBE on table name returns the definition of the columns.
EXISTS, IN, JOIN
- > EXISTS should be used to check if a value exists in another table
- > IN should be used for static list
- > JOIN should be used to retrieve data from other(s) table(s)
JOIN
JOIN is a method of combining (joining) information from two tables. The result is a stitched set of columns from
both tables, defined by the join type (INNER/OUTER/CROSS and LEFT/RIGHT/FULL) and join criteria
(how rows from both tables relate).
SELF JOIN
A table may be joined to itself, with different rows matching each other by some condition. In this use case, aliases
must be used in order to distinguish the two occurrences of the table.
Differences between inner/outer joins
SQL has various join types to specify whether (non-)matching rows are included in the result: INNER JOIN, LEFT
OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN (the INNER and OUTER keywords are optional).
SQL DIAGRAM
http://prntscr.com/rdtcqm
INNER JOIN
An inner join using either of the equivalent queries gives the intersection of the two tables
LEFT OUTER JOIN
left outer join will give all rows in A, plus any common rows in B
RIGHT OUTER JOIN
A right outer join will give all rows in B, plus any common rows in A
FULL OUTER JOIN
A full outer join will give you the union of A and B, i.e., all the rows in A and all the rows in B. If something in A
doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.
RIGHT SEMI JOIN
Includes right rows that match left rows.
LEFT ANTI SEMI JOIN
Includes left rows that do not match right rows.
RIGHT ANTI SEMI JOIN
Includes right rows that do not match left rows
CROSS JOIN
A Cartesian product of all left with all right rows. Is equivalent to an INNER JOIN
IMPLICIT JOIN
Joins can also be performed by having several tables in the from clause, separated with commas, and defining the
relationship between them in the where clause
RECURSIVE JOIN
Recursive joins are often used to obtain parent-child data. In SQL, they are implemented with recursive common
table expressions
Create a table from another table
Creater Table ClonedEmployees AS Select * from Employee
Duplicate a table
Create Table newTable Like oldTable
Create temporary table
Create TEMP TABLE myTable(….);
TRY / CATCH
BEGIN TRANSACTION BEGIN TRY ---------- COMMIT TRANSACTION END TRY BEGIN CATCH THROW ROLLBACK TRANSACTION END CATCH
UNION Statement
UNION keyword in SQL is used to combine to SELECT statement results without any duplicate. In order to use
UNION and combine results both SELECT statement should have the same number of column with the same data type in
same order, but the length of the column can be different
UNION vs UNION ALL
- > UNION joins 2 result sets while removing duplicates from the result set
- > UNION ALL joins 2 result sets without attempting to remove duplicates
MERGE
MERGE (often also called UPSERT for “update or insert”) allows to insert new rows or, if a row already exists, to
update the existing row.
TRUNCATE
- > Delete all the rows from a table
- > you cannot rollback data
- > it is a DDL command
- > it is faster
DELETE
- > It is used to delete a row in a table
- > You can rollback data after using the delete statement
- > it is a DML command
- > it is slower than the truncate statement
INDEXES
Indexes are a data structure that contains pointers to the contents of a table arranged in a specific order, to help
the database optimize queries. They are similar to the index of book, where the pages (rows of the table) are
indexed by their page number.
SORTED INDEX
If you use an index that is sorted the way you would retrieve it, the SELECT statement would not do additional
sorting when in retrieval.
CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);
When you execute the query
SELECT * FROM scoreboard ORDER BY score DESC;
The database system would not do additional sorting, since it can do an index-lookup in that order.
Partial or Filtered Index
SQL Server and SQLite allow to create indexes that contain not only a subset of columns, but also a subset of rows.
Consider a constant growing amount of orders with order_state_id equal to finished (2), and a stable amount of
orders with order_state_id equal to started (1).
If your business make use of queries like this:
SELECT id, comment
FROM orders
WHERE order_state_id = 1
AND product_id = @some_value;
Partial indexing allows you to limit the index, including only the unfinished orders:
CREATE INDEX Started_Orders
ON orders(product_id)
WHERE order_state_id = 1;
This index will be smaller than an unfiltered index, which saves space and reduces the cost of updating the index.
DISABLE INDEX
ALTER INDEX ix_cars_employee_id ON Cars DISABLE;
REBUILD INDEX
Over the course of time B-Tree indexes may become fragmented because of updating/deleting/inserting data. In
SQLServer terminology we can have internal (index page which is half empty ) and external (logical page order
doesn’t correspond physical order). Rebuilding index is very similar to dropping and re-creating it.
We can re-build an index with
ALTER INDEX index_name REBUILD;
By default rebuilding index is offline operation which locks the table and prevents DML against it , but many RDBMS
allow online rebuilding. Also, some DB vendors offer alternatives to index rebuilding such as REORGANIZE
(SQLServer) or COALESCE/SHRINK SPACE(Oracle).
CONCAT
Many databases support a CONCAT function to join strings:
SELECT CONCAT('Hello', 'World'); --returns 'HelloWorld'LENGTH
The LEN doesn't count the trailing space.
SELECT LEN('Hello') -- returns 5
SELECT LEN('Hello '); -- returns 5TRIM
Trim is used to remove write-space at the beginning or end of selection
SELECT TRIM(' Hello ') --returns 'Hello'UPPER
SELECT UPPER(‘HelloWorld’) –returns ‘HELLOWORLD’
LOWER
SELECT LOWER(‘HelloWorld’) –returns ‘helloworld’
SPLIT
Splits a string expression using a character separator. Note that STRING_SPLIT() is a table-valued function
SELECT value FROM STRING_SPLIT(‘Lorem ipsum dolor sit amet.’, ‘ ‘);
REPLACE
Syntax:
REPLACE( String to search , String to search for and replace , String to place into the original string )
Example:
SELECT REPLACE( ‘Peter Steve Tom’, ‘Steve’, ‘Billy’ ) –Return Values: Peter Billy Tom
REGEXP
Checks if a string matches a regular expression (defined by another string).
SELECT ‘bedded’ REGEXP ‘[a-f]’ – returns True
SELECT ‘beam’ REGEXP ‘[a-f]’ – returns False
SUBSTRING
Syntax is: SUBSTRING ( string_expression, start, length ). Note that SQL strings are 1-indexed.
SELECT SUBSTRING('Hello', 1, 2) --returns 'He'
SELECT SUBSTRING('Hello', 3, 3) --returns 'llo'Get the last n characters of a string of unknown length
DECLARE @str1 VARCHAR(10) = 'Hello', @str2 VARCHAR(10) = 'FooBarBaz'; SELECT SUBSTRING(@str1, LEN(@str1) - 2, 3) --returns 'llo' SELECT SUBSTRING(@str2, LEN(@str2) - 2, 3) --returns 'Baz'
STUFF
Stuff a string into another, replacing 0 or more characters at a certain position.
Note: start position is 1-indexed (you start indexing at 1, not 0).
Syntax:
STUFF ( character_expression , start , length , replaceWith_expression )
Example:
SELECT STUFF(‘FooBarBaz’, 4, 3, ‘Hello’) –returns ‘FooHelloBaz’
LEFT
LEFT ( string-expression , integer )
SELECT LEFT('Hello',2) --return HeRIGHT
RIGHT(String-expression, integer)
SELECT RIGHT('Hello',2) --return loREVERSE
Syntax is: REVERSE ( string-expression )
SELECT REVERSE('Hello') --returns olleHREPLICATE
The REPLICATE function concatenates a string with itself a specified number of times.
Syntax is: REPLICATE ( string-expression , integer )
SELECT REPLICATE ('Hello',4) --returns 'HelloHelloHelloHello'INSTR
Return the index of the first occurrence of a substring (zero if not found)
Syntax: INSTR ( string, substring )
SELECT INSTR(‘FooBarBar’, ‘Bar’) – return 4
SELECT INSTR(‘FooBarBar’, ‘Xar’) – return 0
List Concatenation
List Concatenation aggregates a column or expression by combining the values into a single string for each group. A
string to delimit each value (either blank or a comma when omitted) and the order of the values in the result can be
specified. While it is not part of the SQL standard, every major relational database vendor supports it in their own
way.
MySQL
SELECT ColumnA
, GROUP_CONCAT(ColumnB ORDER BY ColumnB SEPARATOR ‘,’) AS ColumnBs
FROM TableName
GROUP BY ColumnA
ORDER BY ColumnA;
SQL Injection
SQL injection is an attempt to access a website’s database tables by injecting SQL into a form field. If a web server
does not protect against SQL injection attacks, a hacker can trick the database into running the additional SQL code.
By executing their own SQL code, hackers can upgrade their account access, view someone else’s private
information, or make any other modifications to the database.
