Brainscape's Knowledge GenomeTM

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


See full index

SQL > SQL_1 > Flashcards

SQL_1 Flashcards

1
Q

DELETE command

A

will delete everything unless given a where condition, can use the rollback command to undo deletions.

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

TRUNCATE command

A

deletes all rows but leaves table structure in place, cannot be undone with rollback command

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

DROP command

A

drops the entire table structure along with all rows/contents, cannot be undone with rollback command

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

PRIMARY KEY

A

constraint can be used to uniquely identify the row.

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

UNIQUE

A

columns have a different value for every row.

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

NOT NULL

A

columns must have a value.

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

DEFAULT

A

assigns a default value for the column when no value is specified.

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

INSERT INTO

A

statement is used to add a new record (row) to a table.

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

UPDATE

A

statement is used to edit records (rows) in a table. It includes a SET clause that indicates the column to edit and a WHERE clause for specifying the record(s).

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

% Wildcard

A

The % wildcard can be used in a LIKE operator pattern to match zero or more unspecified character(s).

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

_ Wildcard

A

The _ wildcard can be used in a LIKE operator pattern to match any single unspecified character.

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

HAVING

A

The HAVING clause is used to further filter the result set groups provided by the GROUP BY clause. HAVING is often used with aggregate functions to filter the result set groups based on an aggregate property.

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

ROUND()

A

he ROUND() function will round a number value to a specified number of places. It takes two arguments: a number, and a number of decimal places. It can be combined with other aggregate functions, as shown in the given query.

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

Outer Join

A

An outer join will combine rows from different tables even if the join condition is not met. In a LEFT JOIN, every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table. Left, right, and full joins are all outer joins.

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

Cross Join

A

The CROSS JOIN clause is used to combine each row from one table with each row from another in the result set. This JOIN is helpful for creating all possible combinations for the records (rows) in two tables.

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

Inner Join

A

The JOIN clause allows for the return of results from more than one table by joining them together with other results based on common column values specified using an ON clause. INNER JOIN is the default JOIN and it will only return results matching the condition specified by ON.

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

Left join

A

An outer join that returns all rows from the left table, and the matched rows from the right table

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

Right join

A

An outer join that returns all rows from the right table, and the matched rows from the left table

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

Window Function

A

Similar to an aggregate function, a window function calculates on a set of rows. However, a window function does not cause rows to become grouped into a single output row.

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

PARTITION BY

A

the PARTITION BY clause is a subclause of the OVER clause. The PARTITION BY clause divides a query’s result set into partitions. The window function is operated on each partition separately and recalculate for each partition.

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

UNION vs JOIN

A

The UNION operator combines result sets of two or more SELECT statements into a single result set (no duplicates). A JOIN similarly combines tables but does so using common values using ON statements, where as the union operator just adds the select statements together.

22
Q

UNION ALL

A

A UNION statement that includes all data from both SELECT statements including duplicates.

23
Q

CTE / WITH Clause

A

A Common Table Expression is a named temporary result set. You create a CTE using a WITH query, then reference it within a SELECT, INSERT, UPDATE, or DELETE statement. If you use a CTE, you don’t need to create and drop a table. You can simply reference the temporary result set created by the WITH query

24
Q

INTERSECT Statement

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.

Just as with the UNION operator, the same rules apply when using the INTERSECT operator. MySQL does not support the INTERSECT operator.

25
Q

CASE Statement

A

The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

CASE Syntax -

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;
26
Q

Subquery

A

A subquery is a SQL query nested inside a larger query.

27
Q

Index

A

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

28
Q

Clustered Index vs Heap (Non-Clustered Index)

A

Clustered index provides an innate ordering for the table it is defined on and follows whatever column order the index is defined on. In a clustered index, when rows are inserted, updated, or deleted, the underlying order of data is retained.

A heap is a table that is stored without any underlying order. When rows are inserted into a heap, there is no way to ensure where the pages will be written nor are those pages guaranteed to remain in the same order as the table is written to or when maintenance is performed against it.

29
Q

Constraints

A

SQL constraints are used to specify rules for the data in a table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly
30
Q

field vs record

A

a field is a column name

a record is the same as a row

31
Q

Subsets of SQL

A

DDL - Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

DML - Data Manipulation Language handles user requests. These commands are used for manipulating the data in database.

DCL - Data Control Language includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions, and other controls of the database system. It is used to retrieve the stored or saved data.

TCL - Transaction Control Language commands deal with the transaction within the database.They are also used to make the changes that are done by DML statements.

32
Q

DATE_TRUNC

A

zeros out everything after the argument but keeps date type structure

33
Q

TO_CHAR

A

makes dates prettier but converts them into a string instead of date type structure

34
Q

CURRENT_TIMESTAMP

A

gets timestamp, similar to GET_DATE()

35
Q

Database Normalization vs Denormalization

A

Normalization is used to remove redundant data from the database and to store non-redundant and consistent data into it.

Denormalization is used to combine multiple table data into one so that it can be queried quickly.

36
Q

RDBMS

A

Relational Database Management System - A database management system (or DBMS) is essentially nothing more than a computerized data-keeping system. MySQL and PostgreSQL are RDBMS’.

37
Q

Write a SQL query to display the current date

A

SELECT CURRENT_DATE;

38
Q

ACID Properties

A

Atomicity - By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all. It involves the following two operations.
—Abort: If a transaction aborts, changes made to database are not visible.
—Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.

39
Q

Trigger statements

A

Trigger is a statement that a system executes automatically when there is any modification to the database. In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified using the constraint mechanism of SQL.

Example –
Suppose, we are adding a tupple to the ‘Donors’ table that is some person has donated blood. So, we can design a trigger that will automatically add the value of donated blood to the ‘Blood_record’ table.

Types of Triggers –
We can define 6 types of triggers for each table:

AFTER INSERT activated after data is inserted into the table.
AFTER UPDATE: activated after data in the table is modified.
AFTER DELETE: activated after data is deleted/removed from the table.
BEFORE INSERT: activated before data is inserted into the table.
BEFORE UPDATE: activated before data in the table is modified.
BEFORE DELETE: activated before data is deleted/removed from the table.
40
Q

SQL Operators

A

SQL Arithmetic Operators

\+ 	Add 	
- 	Subtract 	
* 	Multiply 	
/ 	Divide 	
% 	Modulo

SQL Bitwise Operators

& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR

SQL Comparison Operators

= 	Equal to 	
> 	Greater than 	
< 	Less than 	
>= 	Greater than or equal to 	
<= 	Less than or equal to 	
<> 	Not equal to

SQL Compound Operators

\+= 	Add equals
-= 	Subtract equals
*= 	Multiply equals
/= 	Divide equals
%= 	Modulo equals
&= 	Bitwise AND equals
^-= 	Bitwise exclusive equals
|*= 	Bitwise OR equals

SQL Logical Operators

ALL TRUE if all of the subquery values meet the condition
AND TRUE if all the conditions separated by AND is TRUE
ANY TRUE if any of the subquery values meet the condition
BETWEEN TRUE if the operand is within the range of comparisons
EXISTS TRUE if the subquery returns one or more records
IN TRUE if the operand is equal to one of a list of expressions
LIKE TRUE if the operand matches a pattern
NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is TRUE
SOME TRUE if any of the subquery values meet the condition

41
Q

NULL values same as zero or blank space?

A

No

42
Q

Wildcards

A

Symbol Description Example

% Represents zero or more characters bl% finds bl, black, blue, and blob
_ Represents a single character h_t finds hot, hat, and hit
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
^ Represents any character not in the brackets h[^oa]t finds hit, but not hot and hat
- Represents a range of characters c[a-b]t finds cat and cbt

All the wildcards can also be used in combinations!

Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
LIKE Operator Description
WHERE CustomerName LIKE ‘a%’ Finds any values that starts with “a”
WHERE CustomerName LIKE ‘%a’ Finds any values that ends with “a”
WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a__%’ Finds any values that starts with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ Finds any values that starts with “a” and ends with “o”

43
Q

Group Functions

A

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

44
Q

3 Types of Table Relationships

A

a. One-One Relationship (1-1 Relationship)

One-to-One (1-1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row.

b. One-Many Relationship (1-M Relationship)

The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table.

c. Many-Many Relationship (M-M Relationship)

A row from one table can have multiple matching rows in another table, and a row in the other table can also have multiple matching rows in the first table this relationship is defined as a many to many relationship.

45
Q

IN vs BETWEEN

A

IN operator allows you to easily test 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.

The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers.

46
Q

MERGE Statement

A

The MERGE command in SQL is actually a combination of three SQL statements: INSERT, UPDATE and DELETE. In simple words, the MERGE statement in SQL provides a convenient way to perform all these three operations together which can be very helpful when it comes to handle the large running databases. But unlike INSERT, UPDATE and DELETE statements MERGE statement requires a source table to perform these operations on the required table which is called as target table.

47
Q

SQL Clauses

A

Clauses are in-built functions available to us in SQL. With the help of clauses, we can deal with data easily stored in the table.

Clauses help us filter and analyze data quickly. When we have large amounts of data stored in the database, we use Clauses to query and get data required by the user.

    WHERE Clause
    ORDER BY clause
    HAVING Clause
    TOP Clause
    GROUP BY Clause
48
Q

WHERE vs HAVING

A

In simple words, the WHERE and HAVING clauses act as filters; they remove records or data that don’t meet certain criteria from the final result of a query. However, they are applied to different sets of data. That’s the important point to understand about WHERE vs. HAVING: WHERE filters at the record level, while HAVING filters at the “group of records” level.

49
Q

SQL Aggregate Functions

A

AVG() – returns the average of a set.
COUNT() – returns the number of items in a set.
MAX() – returns the maximum value in a set.
MIN() – returns the minimum value in a set
SUM() – returns the sum of all or distinct values in a set

50
Q

What is a view?

A

To put it simply, a view is a stored SQL query. Every time a view is used, it executes its stored query and creates a result set consisting of rows and columns.

An SQL view is called a virtual table because it does not store the rows and columns on the disk like a concrete table. Instead, it just contains the SQL query. Let’s look at the diagram below to get a better grasp of what a view is.

51
Q

Stored Procedure

A

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

52
Q

COMMIT and ROLLBACK

A

COMMIT and ROLLBACK are performed on transactions. A transaction is the smallest unit of work that is performed against a database. Its a sequence of instructions in a logical order. A transaction can be performed manually by a programmer or it can be triggered using an automated program.

COMMIT is the SQL command that is used for storing changes performed by a transaction. When a COMMIT command is issued it saves all the changes since last COMMIT or ROLLBACK.

ROLLBACK is the SQL command that is used for reverting changes performed by a transaction. When a ROLLBACK command is issued it reverts all the changes since last COMMIT or ROLLBACK.

SQL (1 decks)

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