SQL Flashcards
(101 cards)
1
Q
What is SQL?
A
Structured Query Language is a special-purpose programming language designed for managing data held in a relational database system.
2
Q
DDL
A
Data Definition Language: To create and modify the structure of the database
3
Q
DML
A
Data Manipulation Language: To perform read, insert, update and delete operations on the data of the database
4
Q
DCL
A
Data Control Language: To control the access of the data stored in the database
5
Q
BigInt Storage in Bytes
A
8
6
Q
Int Storage in Bytes
A
4
7
Q
SmallInt Storage in Bytes
A
2
8
Q
TinyInt Storage in Bytes
A
1
9
Q
money Storage in Bytes
A
8
10
Q
smallmoney Storage in Bytes
A
4
11
Q
Char and Varchar
A
String data types of either fixed length or variable length
12
Q
Nchar and Nvarchar
A
UNICODE string data types of either fixed length or variable length
13
Q
UNIQUEIDENTIFIER
A
a 16 byte GUID / UUID
14
Q
NULL
A
Means literally “Nothing”, the absence of any value
WHERE field IS NULL | WHERE field IS NOT NULL
15
Q
SELECT
A
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.
16
Q
- the wildcard character
A
It’s used to select all available columns in a table
17
Q
table.*
A
It’s used to select all values from a specific table
18
Q
- wildcard character problems
A
Excess IO network load, memory use, and so on.
Potencial problem if you add more columns in the tables involved
19
Q
When to use * in production environment
A
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.
20
Q
Aliases
A
Column aliases are used mainly to shorten code and make column names more readable.
Aliases can be applied to Columns and table as well
21
Q
WHERE
A
the condition can be any SQL expression, specified using comparison or logical operator like >, , >=, <=, LIKE, NOT, IN, BETWEEN
22
Q
CASE
A
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
23
Q
Selecting without Locking the table
A
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;
24
Q
AVG
A
The AVG() aggregate function will return the avergae of values selected
25
MIN
The MIN() aggregate function will return the minimun of values selected.
26
MAX
The MAX aggregate function will return the maximun of values selected
27
COUNT
The COUNT() aggregate function will return the count of values selected
28
SUM
The SUM() aggregate function will retrun the SUM of the values selected for all rows
29
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.
30
Embed a CASE statement in the ORDER BY clause
ORDER BY CASE WHEN LName='Jones' then 0 ELSE 1 END ASC
31
DISTINCT
Select unique values only
32
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.
33
GROUP BY results using a HAVING
A HAVING clause filters the results of a GROUP BY expression.
34
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
35
ORDER BY Customized sorting order
```
ORDER BY CASE Department
WHEN 'HR' THEN 1
WHEN 'Accountant THEN 2
ELSE 3
END;
```
36
CASE
The case expression is used to implement if-then logic.
37
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
```
38
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
39
All records where FName 3rd character is 'n' from Employees
select * from Employees WHERE FName LIKE '__n%'
40
Single character match
The _ (underscore) character can be used as a wildcard for any single character in a pattern match
41
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
42
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]%'
43
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'
44
Wildcard characters %
% - A substitute for zero or more characters
45
Wildcard characters _
_ - A substitute for a single character
46
Wildcard characters [charlist]
[charlist] - Sets and ranges of characters to match
47
Wildcard characters [^charlist]
[^charlist] - Matches only a character NOT specified within the brackets
48
IN clause
To get records having any of the given ids
| You can use it for subquerys
49
BETWEEN
The Between operator is inclusive
50
Use Having with aggregate functions
Unlike the where clause, HAVING can be used with aggregate functions.
COUNT(), SUM(), MIN(), MAX()
51
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)
52
EXCEPT
Select dataset except where values are in this other dataset
53
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.
54
DESCRIBE
DESCRIBE and EXPLAIN are synonyms, DESCRIBE on table name returns the definition of the columns.
55
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)
56
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).
57
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.
58
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).
59
SQL DIAGRAM
http://prntscr.com/rdtcqm
60
INNER JOIN
An inner join using either of the equivalent queries gives the intersection of the two tables
61
LEFT OUTER JOIN
left outer join will give all rows in A, plus any common rows in B
62
RIGHT OUTER JOIN
A right outer join will give all rows in B, plus any common rows in A
63
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.
64
RIGHT SEMI JOIN
Includes right rows that match left rows.
65
LEFT ANTI SEMI JOIN
Includes left rows that do not match right rows.
66
RIGHT ANTI SEMI JOIN
Includes right rows that do not match left rows
67
CROSS JOIN
A Cartesian product of all left with all right rows. Is equivalent to an INNER JOIN
68
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
69
RECURSIVE JOIN
Recursive joins are often used to obtain parent-child data. In SQL, they are implemented with recursive common
table expressions
70
Create a table from another table
Creater Table ClonedEmployees AS Select * from Employee
71
Duplicate a table
Create Table newTable Like oldTable
72
Create temporary table
Create TEMP TABLE myTable(....);
73
TRY / CATCH
```
BEGIN TRANSACTION
BEGIN TRY
----------
COMMIT TRANSACTION
END TRY
BEGIN CATCH
THROW
ROLLBACK TRANSACTION
END CATCH
```
74
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
75
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
76
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.
77
TRUNCATE
- >Delete all the rows from a table
- >you cannot rollback data
- >it is a DDL command
- >it is faster
78
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
79
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.
80
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.
81
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.
82
DISABLE INDEX
ALTER INDEX ix_cars_employee_id ON Cars DISABLE;
83
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).
84
CONCAT
```
Many databases support a CONCAT function to join strings:
SELECT CONCAT('Hello', 'World'); --returns 'HelloWorld'
```
85
LENGTH
```
The LEN doesn't count the trailing space.
SELECT LEN('Hello') -- returns 5
SELECT LEN('Hello '); -- returns 5
```
86
TRIM
```
Trim is used to remove write-space at the beginning or end of selection
SELECT TRIM(' Hello ') --returns 'Hello'
```
87
UPPER
SELECT UPPER('HelloWorld') --returns 'HELLOWORLD'
88
LOWER
SELECT LOWER('HelloWorld') --returns 'helloworld'
89
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.', ' ');
90
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
91
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
92
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'
```
93
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'
```
94
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'
95
LEFT
```
LEFT ( string-expression , integer )
SELECT LEFT('Hello',2) --return He
```
96
RIGHT
```
RIGHT(String-expression, integer)
SELECT RIGHT('Hello',2) --return lo
```
97
REVERSE
```
Syntax is: REVERSE ( string-expression )
SELECT REVERSE('Hello') --returns olleH
```
98
REPLICATE
```
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'
```
99
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
100
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;
101
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.
