Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL > SQL > Flashcards

SQL Flashcards

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

DDL

A

Data Definition Language: To create and modify the structure of the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

DML

A

Data Manipulation Language: To perform read, insert, update and delete operations on the data of the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

DCL

A

Data Control Language: To control the access of the data stored in the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

BigInt Storage in Bytes

A

8

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Int Storage in Bytes

A

4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

SmallInt Storage in Bytes

A

2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

TinyInt Storage in Bytes

A

1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

money Storage in Bytes

A

8

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

smallmoney Storage in Bytes

A

4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Char and Varchar

A

String data types of either fixed length or variable length

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Nchar and Nvarchar

A

UNICODE string data types of either fixed length or variable length

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

UNIQUEIDENTIFIER

A

a 16 byte GUID / UUID

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

NULL

A

Means literally “Nothing”, the absence of any value

WHERE field IS NULL | WHERE field IS NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q
  • the wildcard character
A

It’s used to select all available columns in a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

table.*

A

It’s used to select all values from a specific table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

WHERE

A

the condition can be any SQL expression, specified using comparison or logical operator like >, , >=, <=, LIKE, NOT, IN, BETWEEN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

AVG

A

The AVG() aggregate function will return the avergae of values selected

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

MIN

A

The MIN() aggregate function will return the minimun of values selected.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

MAX

A

The MAX aggregate function will return the maximun of values selected

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

COUNT

A

The COUNT() aggregate function will return the count of values selected

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

SUM

A

The SUM() aggregate function will retrun the SUM of the values selected for all rows

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

How filter results after grouping?

A

If you need to filter the results after the grouping has been done, you need to use the HAVING clause.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Embed a CASE statement in the ORDER BY clause

A

ORDER BY CASE WHEN LName=’Jones’ then 0 ELSE 1 END ASC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

DISTINCT

A

Select unique values only

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

GROUP BY

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

GROUP BY results using a HAVING

A

A HAVING clause filters the results of a GROUP BY expression.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

ORDER BY

A

You can use a colum’s number to indicate wich column to base the sort on, instead of describing the column by its name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

ORDER BY Customized sorting order

A 
ORDER BY CASE Department
WHEN 'HR' THEN 1
WHEN 'Accountant THEN 2
ELSE 3
END;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

CASE

A

The case expression is used to implement if-then logic.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

CASE Shorthand

A 
Evaluate and expression against a series of values.
CASE Price
WHEN 5 THEN 'Cheap'
WHEN 15 THEN 'Affordable'
ELSE 'Expensive'
END AS PriceRating
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

LIKE

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

All records where FName 3rd character is ‘n’ from Employees

A

select * from Employees WHERE FName LIKE ‘__n%’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Single character match

A

The _ (underscore) character can be used as a wildcard for any single character in a pattern match

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

ESCAPE statement in the LIKE-query

A

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
Q

Search for a range of characters

A

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
Q

Match by Range or set

A

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
Q

Wildcard characters %

A

% - A substitute for zero or more characters

45
Q

Wildcard characters _

A

_ - A substitute for a single character

46
Q

Wildcard characters [charlist]

A

[charlist] - Sets and ranges of characters to match

47
Q

Wildcard characters [^charlist]

A

[^charlist] - Matches only a character NOT specified within the brackets

48
Q

IN clause

A

To get records having any of the given ids

You can use it for subquerys

49
Q

BETWEEN

A

The Between operator is inclusive

50
Q

Use Having with aggregate functions

A

Unlike the where clause, HAVING can be used with aggregate functions.
COUNT(), SUM(), MIN(), MAX()

51
Q

WHERE EXISTS

A

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
Q

EXCEPT

A

Select dataset except where values are in this other dataset

53
Q

EXPLAIN

A

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
Q

DESCRIBE

A

DESCRIBE and EXPLAIN are synonyms, DESCRIBE on table name returns the definition of the columns.

55
Q

EXISTS, IN, JOIN

A
  • > 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
Q

JOIN

A

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
Q

SELF JOIN

A

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
Q

Differences between inner/outer joins

A

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
Q

SQL DIAGRAM

A

http://prntscr.com/rdtcqm

60
Q

INNER JOIN

A

An inner join using either of the equivalent queries gives the intersection of the two tables

61
Q

LEFT OUTER JOIN

A

left outer join will give all rows in A, plus any common rows in B

62
Q

RIGHT OUTER JOIN

A

A right outer join will give all rows in B, plus any common rows in A

63
Q

FULL OUTER JOIN

A

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
Q

RIGHT SEMI JOIN

A

Includes right rows that match left rows.

65
Q

LEFT ANTI SEMI JOIN

A

Includes left rows that do not match right rows.

66
Q

RIGHT ANTI SEMI JOIN

A

Includes right rows that do not match left rows

67
Q

CROSS JOIN

A

A Cartesian product of all left with all right rows. Is equivalent to an INNER JOIN

68
Q

IMPLICIT JOIN

A

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
Q

RECURSIVE JOIN

A

Recursive joins are often used to obtain parent-child data. In SQL, they are implemented with recursive common
table expressions

70
Q

Create a table from another table

A

Creater Table ClonedEmployees AS Select * from Employee

71
Q

Duplicate a table

A

Create Table newTable Like oldTable

72
Q

Create temporary table

A

Create TEMP TABLE myTable(….);

73
Q

TRY / CATCH

A 
BEGIN TRANSACTION
BEGIN TRY
----------
COMMIT TRANSACTION
END TRY
BEGIN CATCH
THROW
ROLLBACK TRANSACTION
END CATCH
74
Q

UNION Statement

A

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
Q

UNION vs UNION ALL

A
  • > UNION joins 2 result sets while removing duplicates from the result set
  • > UNION ALL joins 2 result sets without attempting to remove duplicates
76
Q

MERGE

A

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
Q

TRUNCATE

A
  • > Delete all the rows from a table
  • > you cannot rollback data
  • > it is a DDL command
  • > it is faster
78
Q

DELETE

A
  • > 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
Q

INDEXES

A

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
Q

SORTED INDEX

A

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
Q

Partial or Filtered Index

A

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
Q

DISABLE INDEX

A

ALTER INDEX ix_cars_employee_id ON Cars DISABLE;

83
Q

REBUILD INDEX

A

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
Q

CONCAT

A 
Many databases support a CONCAT function to join strings:
SELECT CONCAT('Hello', 'World'); --returns 'HelloWorld'
85
Q

LENGTH

A 
The LEN doesn't count the trailing space.
SELECT LEN('Hello') -- returns 5
SELECT LEN('Hello '); -- returns 5
86
Q

TRIM

A 
Trim is used to remove write-space at the beginning or end of selection
SELECT TRIM(' Hello ') --returns 'Hello'
87
Q

UPPER

A

SELECT UPPER(‘HelloWorld’) –returns ‘HELLOWORLD’

88
Q

LOWER

A

SELECT LOWER(‘HelloWorld’) –returns ‘helloworld’

89
Q

SPLIT

A

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
Q

REPLACE

A

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
Q

REGEXP

A

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
Q

SUBSTRING

A 
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
Q

Get the last n characters of a string of unknown length

A 
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
Q

STUFF

A

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
Q

LEFT

A 
LEFT ( string-expression , integer )
SELECT LEFT('Hello',2) --return He
96
Q

RIGHT

A 
RIGHT(String-expression, integer)
SELECT RIGHT('Hello',2) --return lo
97
Q

REVERSE

A 
Syntax is: REVERSE ( string-expression )
SELECT REVERSE('Hello') --returns olleH
98
Q

REPLICATE

A 
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
Q

INSTR

A

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
Q

List Concatenation

A

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
Q

SQL Injection

A

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.

SQL (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions