Brainscape's Knowledge GenomeTM

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


See full index

Data > SQL > Flashcards

SQL Flashcards

1
Q

Database

A

A set of data stored in a computer and is usually structured into tables

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

Relational database

A

A type of database that uses a structure that allows us to identify and access data in relation to another piece of data in the database

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

Schema

A

The set of columns and data types for those columns that make up the table

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

Relational Database Management System

A

A program that allows you to create, update, and administer a relational database

Most use SQL as the language

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

Popular RDBMS

A

MySQL
PostgreSQL
OracleDB
SQL Server
SQLite

Each using slight variations of the core SQL language

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

Common data types

A

INTEGER (positive or negative whole number)
TEXT (text string)
DATE (YYYY-MM-DD)
REAL (decimal)

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

Statement

A

A text that the database recognizes as a valid command

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

Clauses / commands

A

Written in capital letters and perform a specific task

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

Parameter

A

A list of columns, data types, or values that are passed into a clause as an argument

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

SELECT

A

statement used to fetch data from a database

Every query will begin with SELECT

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

CREATE TABLE

A

creates a new table in a database. It allows you to specify the name of the table and the name of each column

The parameters must include each column name and data type

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

INSERT

A

used to insert new rows into a table

Must be structured as follows

INSERT INTO table name (columns to add data)

VALUES (the values to be added to the specific columns)

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

*

A

Wildcard used with SELECT that allows us to pull all columns from a table

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

ALTER TABLE

A

lets you add columns to a table in a database. Must be followed with ADD COLUMN

ALTER TABLE table name
ADD COLUMN column name data type

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

NULL

A

A special value in SQL that represents missing / unknown / blank data

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

UPDATE

A

edits a row in a table. Must be used with SET and WHERE

UPDATE table name
SET column name = new update
WHERE identifier column name = specific row

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

DELETE FROM

A

statement deletes one or more rows from a table. Must be used with WHERE

DELETE FROM table name
WHERE column name IS row identifier

Can be used with IS NULL

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

Constraints

A

Are added after specifying the data type when creating columns for a table

CREATE TABLE table name ( column name, data type, constraint)

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

Common constraints

A

PRIMARY KEY (same as unique but can only have one primary key column in a table)
UNQIUE (data entry must be unique, multiple columns can have unique constraints)
NOT NULL (data must be entered)
DEFAULT ‘text’ (if no entry provided insert a default)

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

Create table

A

CREATE TABLE ( )

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

Add a new row to table

A

INSERT INTO
VALUES

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

Queries data from a table

A

SELECT
FROM

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

Changes an existing table

A

ALTER TABLE
ADD

24
Q

Edits a row in a table

A

UPDATE
SET
WHERE

25
Q

Deletes rows from a table

A

DELETE FROM
WHERE

26
Q

AS

A

Added with SELECT, it will return the column you want renamed to what you’ve defined

The columns are not being renamed in the table but rather appearing only in the result

27
Q

DISTINCT

A

is used to return only unique values in the output, it will filter out all duplicate values in a specific column

SELECT DISTINCT
FROM

28
Q

WHERE

A

can be used to restrict the results of our queries

SELECT
FROM
WHERE

29
Q

Operators

A

Used to create a condition that can be evaluated (typically with WHERE)

= equal to
!= not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to

30
Q

LIKE

A

a special operator used with WHERE to search for a specific pattern in a column

SELECT
FROM
WHERE LIKE

31
Q

Wildcard characters used with LIKE

A

_

%

32
Q

BETWEEN

A

operator used with WHERE to filter the result within a certain range. It accepts two values that are either numbers, text, or dates.

must be used with AND

it will go up to the second value defined in the range

SELECT
FROM
WHERE BETWEEN AND

33
Q

AND

A

Operator used with WHERE to make the result more specific to multiple conditions

SELECT
FROM
WHERE BETWEEN AND
AND

34
Q

OR

A

Same use as AND however OR tests if any of the conditions are true. Whereas AND all conditions must be true

SELECT
FROM
WHERE
OR

35
Q

ORDER BY

A

Sorts our results either alphabetically or numerically based upon the column type. Can also define ascending (ASC) or descending (DESC)

SELECT
FROM
ORDER BY ASC;

36
Q

LIMIT

A

used to limit the max number of rows returned in a query

It always goes at the end of a query

SELECT
FROM
LIMIT

37
Q

CASE

A

Allows us to create different outputs. It is how to do If-Then logic. It takes the place of an argument entered into a SELECT

It is used with WHEN and THEN to create the If Then logic

ELSE closes the logic

END closes the Case

Use AS to return the new output with a clean name

SELECT
CASE
WHEN THEN
WHEN THEN
ELSE
END AS
FROM

38
Q

Aggregates

A

Calculations performed on multiple rows of a table

39
Q

Count the number of rows

A

COUNT( )

SELECT COUNT
FROM

40
Q

Sum the values in a column

A

SUM( )

41
Q

Return the largest / smallest value

A

MAX( ) / MIN( )

42
Q

Get the average of the values in a column

A

AVG( )

43
Q

Round the values in the column

A

ROUND( )

44
Q

SUM( )

A

Takes the name of a column as an argument and returns the sum of all values in that column

SELECT SUM
FROM

45
Q

ROUND( )

A

Takes two arguments

  1. Column name
  2. Integer

It will round the values to the number of decimal places specified by the integer

46
Q

GROUP BY

A

a clause used with aggregate functions in collaboration with SELECT to arrange data into groups. This can be used instead of multiple WHERE statements

It comes after WHERE but before ORDER BY or LIMIT

SELECT xxxx, AVG(yyyy)
FROM
GROUP BY xxxx
ORDER BY xxxx

47
Q

Column references

A

We can use simple column reference numbers (1, 2, 3 etc) with GROUP BY and ORDER BY when we want to organize our result when our columns are aggregated

48
Q

HAVING

A

performs a filter or limiting function similar to WHERE but on aggregated data

When we want to limit a query based upon values of individual rows - WHERE

When we want to limit results based on aggregate - HAVING

Always comes after GROUP BY but before ORDER BY and LIMIT

SELECT xxxx, yyyy, COUNT(zzzz)
FROM table
GROUP BY 1, 2
HAVING COUNT(zzzz) > 10

49
Q

JOIN

A

combines two tables. When specifying the column to join on you need to do table then column

This is an inner join that will only keep the records that are matched in both tables

SELECT
FROM
JOIN
ON table.column = table.column

50
Q

LEFT JOIN

A

same as JOIN but will keep all records from the left table and drop any records that don’t match in the right table

SELECT
FROM left
LEFT JOIN right
ON left.column = right.column

51
Q

Primary keys

A

Uniquely identifies each row for a table

None of the values can be null

Each value must be unique

A table cannot have more that one primary key column

52
Q

Foreign key

A

When the primary key of another table appears in a given table

53
Q

CROSS JOIN

A

will combine all rows of one table with all rows of another table. Does not require ON

SELECT
FROM table1
CROSS JOIN table2

54
Q

UNION

A

Stacks one dataset on top of the other

Tables must have the same number of columns and the same data types

SELECT
FROM table1
UNION
SELECT
FROM table2

55
Q

WITH

A

allows us to combine two tables but one of the tables is the result of another calculation

WITH calculation table name AS (
SELECT …
…
…
)
SELECT *
FROM calculation table name
JOIN table
ON

Data (4 decks)

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