Brainscape's Knowledge GenomeTM

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


See full index

Role Related Knowledge > sql > Flashcards

sql Flashcards

1
Q

What does SQL stand for?

A

Structured Query Language

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

What is SQL used for?

A

Create, maintain, and retrieve a relational database.

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

What does DDL stand for?

A

Data Definition Language.

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

What is DDL used for?

A

To define the structure of the database. e.g. CREATE TABLE, ADD COLUMN, DROP COLUMN, etc.

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

What does DML stand for?

A

Data Manipulation Language.

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

What is DML used for?

A

To extract the data from the relations. e.g. SELECT

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

What form do most SQL queries use?

A

SELECT L
FROM R
WHERE C

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

What does the FROM command do?

A

Specifies which table to select or delete data from.

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

What is the WHERE clause?

A

The condition part of a query. Tuples must satisfy the condition in order to match the query.

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

What does the SELECT clause do in a query?

A

Tells which attributes of the tuples matching the condition are produced as part of the answer.

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

What does the keyword AS do?

A

Allows an alias to be used for column headers. Used in the SELECT clause. e.g. SELECT title AS name.

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

What are the six comparison operators used in the WHERE clause?

A

= (equality), <> (not equal to), , <=, >=

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

What is the concatenation operator in SQL?

A

||

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

What is an alternative form of string comparison?

A

s LIKE p, where s is a string and p is a pattern.

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

What does % in p do in s LIKE p?

A

% in p can match any sequence of 0 or more characters in s.

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

What does _ in p do in s LIKE p?

A

_ in p matches any one character in s.

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

What does the ORDER BY clause do?

A

Presents the tuples produced by a query in sorted order.

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

What is the default order of ORDER BY?

A

asc

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

What does ORDER BY follow in a query?

A

The WHERE clause or the optional GROUP BY and HAVING clauses.

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

What does the ADD keyword do?

A

Adds a column to an existing table. Used with ALTER TABLE. ADD (column1 datatype, column2 datatype)

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

What does ADD CONSTRAINT do?

A

Adds a constraint (e.g. PRIMARY KEY) to an existing table. ADD CONSTRAINT constraintName constraint (col1, col2,)

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

What are the types of constraints available in SQL?

A

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT

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

What are constraints in SQL?

A

Rules that apply to the data types in a table.

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

What is the NOT NULL constraint?

A

Constraint that ensures a column cannot have a NULL value.

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

What is the UNIQUE constraint?

A

Constraint that ensures all values in a column are different.

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

What is the PIMARY KEY constraint?

A

A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.

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

What is the FOREIGN KEY constraint?

A

Constraint that uniquely identifies a record in another table.

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

What is the CHECK constraint?

A

Constraint that ensures all values in a column satisfy a specific condition.

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

What is the DEFAULT constraint?

A

Constraint that sets a default value for a column when no value is specified.

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

What does the ALTER TABLE command do?

A

Used with ADD, DELETE, DROP or MODIFY to add, delete, or modify columns in a table.

31
Q

What does the ALTER COLUMN command do?

A

Changes the data type of a column in a table.

32
Q

What does the ALL operator do?

A

Returns TRUE if all of subquery values meet some condition.

33
Q

What must the ALL operator be preceded by?

A

Comparison operators – SELECT, WHERE, HAVING statements.

34
Q

What is AND used with?

A

The WHERE clause to only include rows where both conditions are true.

35
Q

What does the ANY operator do?

A

Compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row.

36
Q

What statements must the ANY operator be preceded by?

A

WHERE

37
Q

What does the BETWEEN command do?

A

Selects values within a given range (inclusive). The values can be numbers, text, or dates. Can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

38
Q

What is the CASE command used for?

A

Create different outputs based on conditions.

39
Q

What are the two valid ways of using CASE statements?

A
  1. CASE foo
    WHEN ‘bar’ THEN do something
  2. CASE
    WHEN some condition THEN do something
40
Q

What does the CHECK constraint do?

A

Specifies a predicate that every tuple must satisfy in a given relation. It limits the values that a column can hold in a relation.

41
Q

What is the CREATE DATABASE command used for?

A

Create a new SQL database.

42
Q

What is the CREATE INDEX command used for?

A

Creating indexes in tables (allows duplicate values).

43
Q

What does the CREATE TABLE command do?

A

Creates a new table in a database.

44
Q

What does the CREATE PROCEDURE command do?

A

Create a stored procedure.

45
Q

What does the CREATE UNIQUE INDEX command do?

A

Creates a unique index on a table (no duplicate values allowed).

46
Q

What does the CREATE VIEW command do?

A

Creates a view – a virtual table based on the result set of an SQL statement.

47
Q

What does the default constraint do?

A

Provides a default value for a column.

48
Q

What does the DELETE command do?

A

Delete existing records in a table.

49
Q

What does the DISTINCT command do?

A

Returns only distinct (different) values in the result set. Used in SELECT.

50
Q

What does the EXEC command do?

A

Executes a stored procedure.

51
Q

What does the EXISTS command do?

A

Checks whether the result of a correlated nested query is empty or not. Can be used in a SELECT, UPDATE, INSERT or DELETE statement.

52
Q

What is a FOREIGN KEY?

A

A field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

53
Q

What does FULL JOIN do?

A

Creates a result set by combining results of both LEFT JOIN and RIGHT JOIN. The rows which there is no matching will contain NULL values.

54
Q

What does the GROUP BY command do?

A

Arranges identical data into groups with the help of some functions.

55
Q

What are three important things to remember about GROUP BY?

A

Used with the SELECT statement.
Placed after the WHERE clause.
Placed before ORDER BY.

56
Q

What does the HAVING command do?

A

Result filter that works on aggregate data. Used instead of WHERE and acts as a post-filter. Typically comes after a GROUP BY.

57
Q

What does the IN operator do?

A

Tests if the expression matches any value in the list of values. It is used to remove the need of multiple OR condition in SELECT, INSERT, UPDATE or DELETE.

58
Q

What does the INNER JOIN command do?

A

Selects all rows from both the tables that satisfy some condition.

59
Q

What does the INSERT INTO command do?

A

Inserts new rows in a table.

60
Q

What does the IS NULL command do?

A

Tests for empty values.

61
Q

What does the LEFT JOIN command do?

A

Returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join.

62
Q

What does the LIMIT command do?

A

Sets an upper limit on the number of tuples returned by a query.

63
Q

What does the NOT operator do?

A

Includes rows where a condition is not true. Used in WHERE clause.

64
Q

What does the OR operator do?

A

Includes rows where either condition is true. Used in WHERE clause.

65
Q

What does the PRIMARY KEY constraint do?

A

Uniquely identifies each record in a table.

66
Q

What does the RIGHT JOIN command do?

A

Returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join.

67
Q

What does the SELECT INTO command do?

A

Copies data from one table and inserts it into a new table.

68
Q

What does the SELECT TOP command do?

A

Specifies the number of records to return.

69
Q

What does the SET command do?

A

Used with UPDATE to specify which columns and values should be updated in a table.

70
Q

What does the UNION command do?

A

Combines the result set of two or more SELECT statements (only distinct values).

71
Q

What does the UNION ALL command do?

A

Combines the result set of two or more SELECT statements (allows duplicate values).

72
Q

What does the UPDATE command do?

A

Updates existing rows in a table.

73
Q

What does the VALUES command do?

A

Specifies the values of an INSERT INTO statement.

Role Related Knowledge (15 decks)

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