Brainscape's Knowledge GenomeTM

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


See full index

SQL > Commands > Flashcards

Commands Flashcards

1
Q

SELECT * FROM tbl

A

Select all rows and columns from table tbl

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

SELECT c1,c2 FROM tbl

A

Select column c1,c2 and all rows from table tbl

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

SELECT c1,c2 FROM tbl WHERE conditions ORDER BY c1 ASC, c2 DESC

A

Select columns c1, c2 with where conditions and from able tbl. order results by column c1 in ascending order and c2 in descending order

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

SELECT DISTINCT c1,,c2 FROM tbl

A

Select unique rows by columns c1 and c2 from the table

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

SELECT c1, aggregate(expr) FROM tbl GROUP BY c1

A

Select column c1 and use aggregate function on expression expr, group columns by column c1

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

SELECT c1, aggregate(expr) AS c2 FROM tbl GROUP BY c1 HAVING c2 > v

A

Select column c1 and c2 as column alias of the result of aggregate function on expr. Filter group of records with c2 greater than value v

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

INSERT into tbl(c1,c2,…) VALUES(v1,v2…)

A

Insert data into table tbl

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

INSERT INTO tbl(c1,c2,…) SELECT c1,c2.. FROM tbl2 WHERE conditions

A

Insert data from tbl2 into tbl1

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

UPDAT tbl SET c1 = v1 c2 = v2 WHERE conditions

A

Update data in table tbl

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

DELETE FROM tbl WHERE conditions

A

Delete records from table tbl based on conditions

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

TRUNCATE TABLE tbl

A

Drop table tbl and recerate it, losing all data

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

CREATE TABLE tbl( c1 datatype(length) c2 datatype(length)… PRIMARY KEY(c1))

A

create a table with the primary key as c1

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

DROP TABLE tbl

A

Remove table tbl from database

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

ALTER TABLE tbl ADD COLUMN c1 datatype(length)

A

Add a column to the table

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

SELECT column_name FROM table_name

A

Gets you a column from your table

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

SELECT c1,c2 FROM table_1

A

Gets you the specific columns

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

SELECT DISTINCT column FROM table

A

Get the unique values from a column

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

SELECT COUNT(column) FROM table

A

Count all the rows in a column

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

SELECT COUNT( DISTINCT column) FROM TABLE

A

Gets you a number of distinct elements in a table

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

SELECT column FROM table WHERE condition

A

Lets you select a specific set of data

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

Example

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

SELECT column_1, column_2 FROM table ORDER BY column_1 ASC/DESC

A

Order the returned data by a specific column

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

SELECT company,name,sales FROM table ORDER BY company,sales

A

Orders the columns first by all unique companies, then by sales results

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

SELECT data FROM table
ORDER BY column
LIMIT 5

A

Limits the ordered data to 5 rows

26
Q

SELECT column FROM table
BETWEEN low AND high

A

Gets you data in a range of

27
Q

SELECT value FROM table
NOT BETWEEN low AND high

A

Finds you data within a range of

28
Q

SELECT color FROM table
WHERE color IN (‘red’, ‘blue’)

A

Find rows where the column targetted (color) is in a list of possibilities

29
Q

SELECT name FROM celebrity
WHERE name LIKE ‘A%’

A

Gets all celebrities whose names start with A from a celebrity table

30
Q

SELECT name FROM song
WHERE song ILIKE ‘%and’

A

Gets a list of all songs from a song table that end with and or And

31
Q

WHERE value LIKE ‘value__’

A

Each underscore is one blank space

32
Q

SELECT ROUND(AVG(num_column),3)
FROM TABLE

A

Returns the average value as a float. Can use ROUND() around the average to get a lower number of decimal places

33
Q

SELECT MAX(num_column) FROM TABLE

A

Return the maximum

34
Q

SELECT MIN(num_column) FROM table

A

Return the minimum number of

35
Q

SELECT SUM(num_column) FROM TABLE

A

Return the sum number

36
Q

SELECT category_col, AGG(data_col) FROM table
WHERE condition
GROUP BY category_col

A

It organizes the data into groups. You need to include the columns that you want to group by

37
Q

SELECT company, SUM(sales)
FROM finance_table
WHERE company != ‘Google’
GROUP BY company
HAVING SUM(sales) > 1000

A

Here having is used to sort companies that have more than 1000 sales. Importantly, having is used when where is filtered by the same thing it’s grouped by

38
Q

AS

A

Creates an alias for something (table or column)

SELECT SUM(column) AS new_name
FROM table

AS can’t be used in where because it happens at the end of the operation

39
Q

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.col_match = TableB.col_match

A

Gets you a table with all the rows where the values of the table A and table B columns match

40
Q

SELECT column, Table2.column, column
FROM Table1
INNER JOIN Table2
ON Table1.column = Table2.column

A

Gets you the columns on either end of the joined column. Also, only gets you one column when joining, not a duplicate

41
Q

JOIN is equal to inner join

A

There you go

42
Q

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

A

Gets everything from both tables.

You will get null where there is no match

43
Q

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableA.id IS null OR TableB IS null

A

Gives you all unique rows from both tables

44
Q

Left outer Join

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

A

Gets you all of table A and the overlapping columns of table B

45
Q

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableB.id IS null

A

Gets you only the things unique to table A

46
Q

SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

A

Gives you all rows where there is overlap in data or only data in Table B

47
Q

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2

A

Combines the columns together to display a new data table

48
Q

TIME

A

Column with only time

49
Q

DATE

A

Column with only date

50
Q

TIMESTAMP

A

Contains sate and time

51
Q

TIMESTAMPTZ

A

Contains date, time, and timezone

52
Q

TIMEZONE

A
53
Q

NOW

A
54
Q

TIMEOFDAY

A
55
Q

CURRENT_TIME

A
56
Q

CURRENT_DATE

A
57
Q

SHOW TIMEZONE

A

Does what it sounds like

58
Q

SELECT NOW()

A

Gives an up to the second timestamp

59
Q

SELECT CURRENT_DATE

A

Gets you the current date

60
Q

EXTRACT(YEAR FROM date_col)

A

Gets you just the year from a timestamp

61
Q

AGE(date_col)

A

Gets you an age based on the date in the column

62
Q

TO_CHAR(date_col, ‘mm-dd-yyyy’)

A

Gets you the year formatted

SQL (1 decks)

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