Brainscape's Knowledge GenomeTM

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


See full index

SQL > SQL Commands and Functions > Flashcards

SQL Commands and Functions Flashcards

1
Q

SELECT

A

The SELECT block specifies which columns you want to output. Its format is SELECT , , …. Each column must be separated by a comma, but the space following the comma is optional.

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

SQL Comments

A

The contents between /* and */ are taken as comments and aren’t run as code.

– is another way of indicating comments. It is used to mark the rest of a single line as a comment. When we have multiple lines we want to comment out, /* … */ is more convenient than prepending – to each line.

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

FROM

A

The FROM block specifies which table we’re querying from. Its format is FROM . It always comes after the SELECT block.

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

WHERE

A

The WHERE block allows us to filter the table for rows that meet certain conditions. Its format is WHERE and it always goes after the FROM block.

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

Quotes

A

In SQL, strings are denoted by single quotes. Backticks (ie `) can be used to denote column and table names. This is useful when the column or table name is the same as a SQL keyword and when they have a space in them.

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

LIKE

A

It allows us to use wildcards such as % and _ to match various characters.

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

% Wildcard

A

Allows us to find strings that contain a string. May be used at the beginning of a string, at the end, or both. Used with LIKE.

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

_ Wildcard

A

Allows us to find strings that start or end with a string. ‘roy’ and ‘Tro’ both return Troy. Used with LIKE.

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

COUNT()

A

COUNT is probably the most widely-used aggregate function. As the name suggests, it counts things! For instance, COUNT() returns the number of non-null rows in the column.

COUNT(*) counts rows as long as any one of their columns is non-null.

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

NULL

A

In SQL, NULL is the value of an empty entry. This is different from the empty string ‘ ‘ and the integer 0, both of which are not considered NULL. To check if an entry is NULL, use IS and IS NOT instead of = and !=.

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

LIMIT

A

Limits the number of results returned.

LIMIT

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

CASE WHEN block

A

Acts as an if/else statement.

CASE
    WHEN  THEN 
    WHEN  THEN 
    ...
    ELSE 
END
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

MIN()

A

The MIN() function returns the smallest value of the selected column.

MIN(column_name)

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

MAX()

A

The MAX() function returns the largest value of the selected column.

MAX(column_name)

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

AVG()

A

The AVG() function returns the average value of a numeric column.

AVG(column_name)

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

SUM()

A

The SUM() function returns the total sum of a numeric column.

SUM(column_name)

17
Q

LENGTH()

A

For a string value str, the length(str) function returns the number of characters (not bytes) in str prior to the first NUL character.

LENGTH(string)

18
Q

SELECT DISTINCT

A

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

SELECT DISTINCT column1, column2, …

19
Q

GROUP BY

A

It allows us to split up the dataset and apply aggregate functions within each group, resulting in one row per group. Its most basic form is GROUP BY , , … and comes after the WHERE block.

20
Q

AS

A

AKA Aliasing. In the SELECT block, AS provides an alias that can be referred to later in the query. This saves us from rewriting long expressions, and allows us to clarify the purpose of the expression.

21
Q

ROUND()

A

The ROUND() function rounds a number to a specified number of decimal places.

ROUND(number, decimals, operation)

22
Q

HAVING

A

Like WHERE but can be used on aggregate functions. You can think of it as a post-aggregation WHERE block. Modifies GROUP_BY.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
23
Q

JOIN

A

The JOIN block takes the form of JOIN ON . The clause works the same way as in WHERE . That is, it is a statement that evaluates to true or false, and anytime a row from the first table and another from the second line up with the clause being true, the two are matched

By default this is an INNER JOIN which drops unmatched rows.

24
Q

LEFT JOIN

A

LEFT JOIN ON

Preserves unmatched rows in the left table.

25
Q

UNION

A

The UNION operator is used to combine the result-set of two or more SELECT statements and stack the columns without returning any duplicate rows.

To use this UNION clause, each SELECT statement must have:

  • The same number of columns selected
  • The same number of column expressions
  • The same data type and
  • Have them in the same order
SELECT column_name(s) 
FROM table1
WHERE ...
    UNION
SELECT column_name(s) 
FROM table2;
WHERE ...
26
Q

UNION ALL

A

UNION statement that allows duplicate values

Each SELECT statement must have:

  • The same number of columns selected
  • The same number of column expressions
  • The same data type and
  • Have them in the same order
SELECT column_name(s) 
FROM table1
WHERE ...
   UNION ALL
SELECT column_name(s) 
FROM table2;
WHERE ...
27
Q

INTERSECT

A

The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

Each SELECT statement must have:

  • The same number of columns selected
  • The same number of column expressions
  • The same data type and
  • Have them in the same order

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

28
Q

EXCEPT

A

The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.

Each SELECT statement must have:

  • The same number of columns selected
  • The same number of column expressions
  • The same data type and
  • Have them in the same order

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

29
Q

SQL Order Of Operations and Readability

A

ORDER: SELECT, FROM, (AS), (JOIN), (ON), WHERE, (Set Op & second query) GROUP BY, HAVING, ORDER BY, LIMIT

SELECT DISTINCT AS

FROM AS

JOIN AS

ON (. = .)

WHERE

(Set operation and Second Query if necessary)

GROUP BY

HAVING

ORDER BY

LIMIT ;

30
Q

Set Operations

A

UNION, UNION ALL, EXCEPT, INTERSECT

31
Q

Subquery/Inner Query

A

Subqueries are queries within queries. A subquery can also be called an inner query with the “parent” query being called the outer query.

There are two main ways to use a subquery:

  • In an IN condition
  • As a derived or temporary table

A subquery in an IN condition must only have one column.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, , >=, <=, IN, BETWEEN, etc.

Rules:

  • Subqueries must be enclosed within parentheses.
  • A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
  • An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
  • Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
  • The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
  • A subquery cannot be immediately enclosed in a set function.
  • The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.
32
Q

Subqueries with SELECT statements

A 
SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name column_name IN
   (SELECT column_name [, column_name ]
   FROM table1 [, table2 ]
   [WHERE])
SELECT * 
FROM CUSTOMERS 
WHERE ID IN (SELECT ID 
      FROM CUSTOMERS 
       WHERE SALARY > 4500) ;
33
Q

Subqueries with INSERT statements

A

INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]

INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;

34
Q

Subqueries with UPDATE statements

A 
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]
UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE 
      FROM CUSTOMERS_BKP
      WHERE AGE >= 27 );
35
Q

Subqueries with DELETE statements

A 
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]
DELETE 
FROM CUSTOMERS 
WHERE AGE IN (SELECT AGE 
      FROM CUSTOMERS_BKP
      WHERE AGE >= 27 );
36
Q

Derived Tables

A

Temporary tables created as Subqueries nested inside FROM clauses. May be used for Joins but you must alias it.

 SELECT  
 FROM  
 WHERE  
  JOIN 
      (SELECT 	
       FROM 	
       WHERE ) AS  ON ;
SELECT * 
FROM Sale as sale
  INNER JOIN (SELECT CarID
              FROM Car
              WHERE ModelYear = 2015) AS temp
  ON sale.CarID = temp.CarID;
37
Q

Create cards for operators

A

do that

38
Q

Create cards for interview questions

A

do that

39
Q

Add cards on Normalization

A

1NF, 2NF, 3NF, 3.5NF, Normalization

SQL (1 decks)

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