Brainscape's Knowledge GenomeTM

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


See full index

Storing and retrieving data > Structured Query Language > Flashcards

Structured Query Language Flashcards

1
Q

What are the four key characteristics of SQL?

A
  1. Set-oriented and declarative;
  2. Free-form language;
  3. Case insensitive;
  4. Can be used both interactively from a command prompt or executed by a program.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Code for check existing databases:

A

SHOW DATABASES;

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

Code for creating a database:

A

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]

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

Code for use a database:

A

USE database_name;

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

Code for drop a database:

A

DROP DATABASE [IF EXISTS] database_name

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

Code for see the tables present in the database:

A

SHOW TABLES;
(after Use command)

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

Code for creating a table:

A

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value]
[AUTO_INCREMENT]
col2 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
col3 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE = storage_engine;

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

In the following code what does NOT NULL does?

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value]
[AUTO_INCREMENT]
col2 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
col3 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE = storage_engine;

A

It indicates that the column does not allow NULL

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

In the following code what does DEFAULT does?

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value]
[AUTO_INCREMENT]
col2 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
col3 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE = storage_engine;

A

The default value is used to specify the default value of the column.

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

In the following code what does AUTO INCREMENT does?

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value]
[AUTO_INCREMENT]
col2 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
col3 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE = storage_engine;

A

The auto increment indicates that the value of the column is incremented by one automatically whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT column.

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

In the following code what does ENGINE does?

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value]
[AUTO_INCREMENT]
col2 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
col3 data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT]
) ENGINE = storage_engine;

A

We can optionally specify the storage engine for the table in the ENGINE clause. We can use any storage engine such as InnoDB and MyISAM. If we don’t explicitly declare the storage engine, MySQL will use InnoDB by default.

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

What are examples of datatypes?

A
  • CHAR (size)
  • VARCHAR (size)
  • TEXT
  • INT (size)
  • FLOAT (size, d): small number with a floating decimal point
  • DOUBLE (size, d): large number with a floating decimal point
  • DATE()
  • YEAR()
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Code to create primary keys:

A

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)[PRIMARY KEY]
col2 data_type(length)[PRIMARY KEY]
col3 data_type(length)[PRIMARY KEY]
);

Or

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
col2 data_type(length)
col3 data_type(length)
PRIMARY KEY(col1, col2, …)
);

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

Code to create foreign keys:

A

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table (columns)
ON DELETE action
ON UPDATE action
);

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

What does the Constraint clause do in the following code:

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table (columns)
ON DELETE action
ON UPDATE action
);

A

Allows us to define the constraint name for the foreign key constraint. If we omit it, MySQL will generate a name automatically.

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

What does the Foreign key clause do in the following code:

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table (columns)
ON DELETE action
ON UPDATE action
);

A

Specifies the columns in the child table that refer to primary key columns in the parent table.

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

What does the References clause do in the following code:

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table (columns)
ON DELETE action
ON UPDATE action
);

A

Specifies the parent table and its columns to which the columns in the child table refer. The number of columns in the child table and parent table specified in the foreign key and references must be the same.

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

What does the On delete clause do in the following code:

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table (columns)
ON DELETE action
ON UPDATE action
);

A

Allows us to define what happens to the records in the child table when the records in the parent table are deleted. If we omit the on delete clause and delete a record in the parent table that has records in the child table refer to, MySQL will reject the deletion. Possible actions: CASCADE, SET NULL, NO ACTION.

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

What does the On update clause do in the following code:

CREATE TABLE [IF NOT EXISTS] table_name(
col1 data_type(length)
PRIMARY KEY(col1, col2, …)
CONSTRAINT constraint_name
FOREIGN KEY (columns)
REFERENCES parent_table (columns)
ON DELETE action
ON UPDATE action
);

A

Enables us to define what happens to the rows in the child table when the records in the parent table are updated. We can omit the on update clause to let MySQL reject any updates to the rows in the child table when the rows in the parent table are updated. Possible actions: CASCADE, SET NULL, NO ACTION.

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

Code to drop a table:

A

DROP TABLE [IF EXISTS] table_name[, table_name] …

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

What does the if exists addition do in the following code?

DROP TABLE [IF EXISTS] table_name[, table_name] …

A

It helps to prevent from the attempt of removing non-existent tables. When we use this addition, MySQL generates a NOTE, which can be retrieved by using the SHOW WARNING statement. It is important to note that this statement removes all existing tables and issues an error message or a note when we have a non-existent table in the list.

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

What are the CRUD operations?

A
  • Create (insert)
  • Read
  • Update
  • Delete
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Code for insert data into table:

A

INSERT INTO table_name(c1,c2,…)
VALUES (v1, v2, …)

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

Code for read table:

A

SELECT
column_1, column_2, …
FROM
table_1
[INNER | LEFT | RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

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

Code for update a table:

A

UPDATE [LOW_PRIORITY][IGNORE] table_name
SET
column_name1 = expr1,
column_name2 = expr2,
[WHERE
condition];

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

Code for delete data from a table:

A

DELETE FROM table_name
WHERE condition;

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

Describe the limit clause and how to use it.

A

The limit clause is used in the SELECT statement to constrain the number of rows to return.

Code:
SELECT
select_list
FROM
table_name
LIMIT [offset,] row_count;

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

What does the offset do in the following code?

SELECT
select_list
FROM
table_name
LIMIT [offset,] row_count;

A

The offset specifies the offset of the first row to return. The offset of the first row is 0 and not 1.

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

What does the row_count do in the following code?

SELECT
select_list
FROM
table_name
LIMIT [offset,] row_count;

A

The row_count specifies the maximum number of rows to return.

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

What does the distinct clause do?

A

Is to remove duplicates and it is used with select.

Example:
SELECT DISTINCT e.first_name FROM employees AS e LIMIT 10;

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

What does the between operator do?

A

Is a logical operator that allows us to specify whether a value is within a range or not.

Example:
SELECT e.emp_no, e.first_name, e.last_name, e.hire_date
FROM employees AS e
WHERE e.hire_date BETWEEN ‘1999-01-01’ AND ‘199-12-31’

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

What is the like operator?

A

Is a logical operator that tests whether a string contains a specified pattern or not.

Example: retrieve the employees whose last names begin with ‘T’:
SELECT e.first_name, e.last_name
FROM employees AS e
WHERE e.last_name LIKE ‘T%’

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

In the following code what is ‘%’

SELECT e.first_name, e.last_name
FROM employees AS e
WHERE e.last_name LIKE ‘T%’

A

Is a wildcard. Represents zero or more characters

Example: T% finds Tires, Teodoro, Timóteo

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

In the following code what is ‘_’

SELECT e.first_name, e.last_name
FROM employees AS e
WHERE e.last_name LIKE ‘T%’

A

Is a wildcard. Represents a single character.

Example: h_t finds hot, hat, and hit

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

Is there a not like operator?

A

Yes.

Example: retrieve the employees where the last name does not begin with ‘Tr’:
SELECT e.first_name, e.last_name
FROM employees AS e
WHERE e.last_name NOT LIKE ‘Tr%’;

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

What is the code for MySQL alias for columns?

A

SELECT
[column_1 | expression] AS descriptive_name
FROM table_name;

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

What is the code for MySQL alias for tables?

A

table_name AS table_alias

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

Is the AS keyword optional?

A

For tables, we can omit it, but for columns no.

39
Q

What does the in operator do?

A

It allows to determine if a specified value matches any value in a set of values or returned by a subquery.

40
Q

What is the code for usage of in operator?

A

SELECT
column1, column2, …
FROM
table_name
WHERE
column_n IN (value1, valur2, …);

41
Q

What is the order by clause?

A

The select statement does not provide the result set sorted. To sort the result set, we add the order by clause to the select statement. The order by clause is always evaluated after the from and select clause.

Code:
SELECT
select_list
FROM
table_name
ORDER BY
column1 [ASC|DESC],
column2 [ASC|DESC],
…;

42
Q

Say the types of SQL joins.

A
  • inner join
  • left join
  • right join
  • full join
43
Q

What is true about the SQL dialect?
a) refers to the original SQL-86 version
b) the way SQL coders communicate to each other
c) the SQL implementation provided by each vendor
d) the SQL property of being embedded in JAVA applications

A

c)

44
Q

CRUD refers to:
a) create, read, update, drop
b) create, read, update, delete
c) create, retrieve, update, drop
d) collect, read, update, delete
e) none of the above

A

b)

45
Q

We want to select the employees that do not belong to any department:
a) SELECT * FROM employee WHERE department_id is null;
b) SELECT * FROM employee WHERE department_id is not null;
c) SELECT * FROM employee WHERE department_id in (null);
d) SELECT * FROM employee IF EXISTS department_id;

A

a)

46
Q

Assume table REGION has 3 rows with region_name = ‘Americas’, 2 rows with region_name = ‘Europe’, and 4 rows with other world regions. How many rows are updated by the following SQL code:

UPDATE region SET
region_name = ‘America’
WHERE region_name = ‘Americas’ AND region_name = ‘Europe’;

a) 0
b) 5
c) 3
d) 2
e) 4

A

a)

47
Q

About the following SQL code:

DELETE FROM region
LIMIT 2
WHERE region_name = ‘Europe’

a) deletes 2 rows corresponding to Europe region
b) completes execution but deletes zero regions
c) deletes the two first rows regardless of which region they refer to
d) raises an error

A

d)

48
Q

If the following reasons are true, for which of them this SQL code raises an error:

INSERT INTO department (DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID)
VALUES (4, ‘MARKETING2’, 100, 1000);

a) we are inserting a DEPARTMENT_ID that already exists and DEPARTMENT_ID is defined as the primary key
b) the number of columns is different from the number of values
c) we are inserting a value for LOCATION_ID (foreign key) that does not exist in table LOCATION where LOCATION_ID is defined as NOT NULL
d) all of the previous reasons

A

d)

49
Q

We want to get the unique first names of the employees that are from Portugal:

a) SELECT UNIQUE first_name FROM employees WHERE country = ‘Portugal’;
b) SELECT DISTINCT first_name FROM employees WHERE country = ‘Portugal’;
c) SELECT first_name FROM employees WHERE country = ‘Portugal’;
d) none of the above

A

b)

50
Q

We want to find the products whose prices are between 200 and 400:

a) SELECT productName FROM products WHERE price > 200 AND < 400;
b) SELECT productName FROM products WHERE price BIGGER 200 AND SMALLER 400;
c) SELECT productName FROM products WHERE price BETWEEN 200 AND 400;
d) SELECT productName FROM products WHERE price IN 200 AND 400;

A

c)

51
Q

About the left join, being table A the left table and B the right:

a) return all of the records in table A that do not match any records in table B
b) return only the records in table A that match the records in table B
c) return all of the records in table A regardless if the records have a match in table B
d) return all of the records in table B regardless if the records have a match in table A

A

c)

52
Q

Assuming that emp_no is the primary key, and that the job_title is unique. What does the following query does?

SELECT e.first_name, t.title, e.emp_no
FROM employees AS e, titles AS t
WHERE e.emp_no = t.emp_no
AND t.title = ‘Senior Engineer’;

a) retrieve the first name, title, and employee identifier of the senior engineers
b) retrieve the first name, title, and employee identifier of the employees that are not senior engineers
c) retrieve the first name, title, and employee identifier of employees where the employee identifier is equal to the title identifier
d) none of the above

A

a)

53
Q

What retrieves the following query?

SELECT T1.orderNumber,
T1.status,
SUM(T2.quantityOrdered * T2.priceEach) total
FROM orders AS T1
INNER JOIN orderdetails AS T2
ON T1.orderNumber = T2.orderNumber
GROUP BY T1.orderNumber;

a) orderNumber, status, quantityOrdered, and priceEach
b) orderNumber, status, and the order average
c) orderNumber, status, and the total of the order
d) orderNumber, status, the total of each order detail

A

c)

54
Q

What is true about the following query?

SELECT o.orderNumber, p.productName, p.msrp, o.priceEach
FROM products p JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach
WHERE p.product code = ‘S10_1678’;

a) the query only retrieves data from table products
b) the query uses an inner join
c) the query uses a right join
d) the query joins tables based on the MSRP in the MSRP as key column

A

b)

55
Q

What is true for this code:

SELECT concat(p.product_code, ‘ ‘, p.product_name)
FROM product AS p;

a) retrieves two columns and presents as two separate columns in the result set: product code and product name
b) retrieves two columns (product code and product name) and presents as one single column in the result set
c) may retrieve more than two columns if product code is not unique
d) all the options are wrong

A

b)

56
Q

We want to retrieve the average salary by year:

a) SELECT YEAR, AVG(s.salary)
FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no
GROUP BY e.hire_date;
b) SELECT e.hire_date, AVG(s.salary)
FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no
GROUP BY e.hire_date;
c) SELECT YEAR(e.hire_date) AS year, SUM(s.salary)
FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no
GROUP BY YEAR(e.hire_date);
d) SELECT YEAR(e.hire_date) AS year, AVG(s.salary)
FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no
GROUP BY YEAR(e.hire_date);

A

d)

57
Q

What is true about a view?

a) it is a virtual table without physical rows
b) the name of the view cannot be the same as the table
c) the view does not physically store the data
d) all the previous are true
e) none of the above

A

d)

58
Q

The trigger can be activated in the following events:

a) INSERT, UPDATE, DELETE
b) SELECT, INSERT, UPDATE, DELETE
c) INSERT, UPDATE
d) INSERT, DELETE

A

a)

59
Q

What is true about triggers:

a) triggers do not affect performance
b) they may cause deadlock situations
c) not appropriate for automatic auditing
d) triggers are easy to debug

A

b)

60
Q

The following table with purchase orders is created:

CREATE TABLE PURCHASE_ORDER
(PONR CHAR(7) NOT NULL PRIMARY KEY, PODATE DATE, SUPNR CHAR(4) NOT NULL, FOREIGN KEY (SUPNR) REFERENCES SUPPLIER(SUPNR) ON DELETE CASCADE ON UPDATE CASCADE);

What happens upon the deletion of a supplier?

a) all purchase orders records tied to that supplier are also deleted
b) the SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER
c) the SUPNR of this supplier is deleted in PURCHASE_ORDER
d) the SUPNR of this supplier is only deleted in SUPPLIER

A

a)

61
Q

What is a inner join and how to code it?

A

An inner join returns all of the records in the left table (table A) that have a matching record in the right table (table B).

Code:
SELECT column_list
FROM table_A A
INNER JOIN table_B B
ON A.key = B.key;

62
Q

What is a left join and how to code it?

A

A left join returns all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table.

Code:
SELECT column_list
FROM table_A A
LEFT JOIN table_B B
ON A.key = B.key;

63
Q

What is a right join and how to code it?

A

A right join returns all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the right table.

Code:
SELECT column_list
FROM table_A A
RIGHT JOIN table_B B
ON A.key = B.key;

64
Q

How to do a full outer join with union, and what is it?

A

MySQL does not support full outer join, we need to emulate it. It returns all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B).

Code:
SELECT * FROM table_1
LEFT JOIN table_2 ON table_1.id = table_2.id
UNION ALL
SELECT * FROM table_1
RIGHT JOIN table_2 ON table_1.id = table_2.id;

65
Q

What is a left excluding join and how to code it?

A

The left excluding join return all of the records in the left table (table A) that do not match any
records in the right table (table B). The where statement makes the difference with the left join (not excluding).

Code:
SELECT column_list
FROM table_A A
LEFT JOIN table_B B
ON A.key = B.key
WHERE B.key IS NULL;

66
Q

What is a right excluding join and how to code it?

A

The left excluding join return all of the records in the right table (table B) that do not match any
records in the left table (table A). The where statement makes the difference with the right join (not excluding).

Code:
SELECT column_list
FROM table_A A
RIGHT JOIN table_B B
ON A.key = B.key
WHERE A.key IS NULL;

67
Q

Can we do a self join?

A

Yes, a self join: is used to query hierarchical data. For instance, the table employees contains the employees and their supervisors (who are also employees). To perform a self-join we must use table aliases.

68
Q

What are aggregate functions?

A

Aggregate functions perform calculations on multiple values and return a single value. Aggregate functions are often used with the group by clause to calculate an aggregate value for each group.

69
Q

Give examples of aggregate functions:

A

AVG, MAX, MIN, STDEV, COUNT, SUM

70
Q

Give examples of math functions:

A

MOD, ROUND, TRUNCATE, SIN, SQRT, RAND, LOG, POW

71
Q

Give examples of date functions:

A

CURDATE (CURRENT_DATE), DATEDIFF, DAY, DAYOFWEEK, NOW, MONTH, TIMEDIFF, YEAR

72
Q

Give examples of string functions:

A

CONCAT, LENGHT, LOWER, REPLACE, SUBSTRING, TRIM, UPPER, INSTR

73
Q

What is the group by clause and how to use it?

A

This clause groups a set of rows into a set of summary rows by values of columns or expressions. The group by clause returns one row for each group. Usually, this clause is used with aggregate functions such as SUM, AVG, MAX, MIN, and COUNT.

Code:
SELECT
c1, c2, …, cn, aggregate_function(ci)
FROM
table_name
WHERE
where_conditions
GROUP BY c1, c2, …, cn;

74
Q

What is the having clause and how to use it?

A

The having clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates. The having clause is often used with the group by clause to filter groups based on a specified condition. Notice that the having clause applies a filter condition to each group of rows, while the where clause applies the filter condition to each individual row.

Code:
SELECT
select_list
FROM
table_name
WHERE
search_condition
GROUP BY
group_by_expression
HAVING
group_condition;

75
Q

Is having used before or after where?

A

After

76
Q

What is a view?

A

A view is defined by means of an SQL query and its content is generated upon invocation of the view by an application or other query. We can run queries against the view.

77
Q

Does the view have data?

A

A view does not physically store the data, so each time we query the view, the underlying query (view definition) is executed. The name of the view cannot be the same as the name of an existing table.

78
Q

How to create a view?

A

CREATE [OR REPLACE] VIEW [db_name.]view_name
[(column_list)]
AS
select-statement;

79
Q

What is a trigger?

A

A trigger is a piece of SQL code consisting of declarative and(or procedural instructions and stored in the catalogue of the RDBMS. It is automatically activated and run by the RDBMS whenever a specified event (insert, update, delete) occurs and a specific condition is evaluated as true.

80
Q

What are the two types of triggers?

A
  • Row-level trigger: is activated for each row that is inserted, updated, or deleted (only ones supported by MySQL).
  • Statement-level trigger: is executed once for each transaction regardless of how many rows are inserted, updated, or deleted (not supported by MySQL).
81
Q

How to create a trigger?

A

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
Code of what the trigger will do
END;

  • trigger_time can be BEFORE or AFTER.
  • trigger_event can be INSERT, UPDATE, or DELETE.
82
Q

What are the $$ in the following code?

DELIMITER $$
CREATE TRIGGER employee_bi_update_comission
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
IF new.department_id = 80 THEN
SET new.comission_pct = 0.15
END IF;
END $$

A

To change the delimiter as in the body of what the trigger will do we do queries.

83
Q

What are the advantages of triggers?

A
  • Automatic monitoring and verification in case of specific events or situations.
  • Modelling extra semantics and/or integrity rules without changing the user front-end or application code.
  • Assign default values to attribute types for new tuples.
  • Synchronic updates in case of data replication.
  • Automatic auditing and logging, which may be hard to accomplish in any other application layer.
  • Automatic exporting of data.
84
Q

SQL stands for:

a) storing query language
b) standard query language
c) structured quality language
d) none of the above

A

d)

85
Q

Assume the table EMPLOYEE has 20 rows, some employees have the same last name, there are only 4 unique last names. How many rows returns the following query:

“SELECT DISTINCT last_name FROM employee LIMIT 10”

a) 4
b) 20
c) 0
d) 10

A

a)

86
Q

Which type of join is defined with this statement: “Returns all of the records in the left table (table A) that have a matching record in the right table (table B).”

a) RIGHT JOIN
b) OUTER JOIN
c) LEFT JOIN
d) INNER JOIN

A

d)

87
Q

Assume that table SALARY has 4 columns, and table EMPLOYEES has 6 columns. All the columns have different column names. How many columns will be displayed using the following query:

“SELECT * FROM salary AS a JOIN employees AS b ON a.emp_id = b.emp_no LIMIT 20”

a) 0
b) 5
c) 10
d) 20

A

c)

88
Q

You need to display the salaries of employees sorted, which clause should you use?

a) GROUB BY
b) HAVING
c) UPPER
d) None of the above

A

d)

89
Q

You create a foreign key between a parent table and child table. You want to prevent rows from the parent table get deleted. What clause should you use?

a) ON UPDATE CASCADE
b) ON DELETE RESTRICT
c) ON UPDATE RESTRICT
d) ON DELETE CASCADE

A

b)

90
Q

What kind of join is the following query:

SELECT a.customer_id, a.last_name, b.first_name, b.last_name
FROM customer AS a INNER JOIN customer AS b
ON a.last_name = b.first_name;

a) LEFT EXCLUDING JOIN
b) INNER EXCLUDING JOIN
c) SELF-JOIN
d) None of the above

A

c)

91
Q

What is true about the following ERD:

a) to retrieve the name of cinema and the seat row you need to join cinema, theatre and seat.
b) contains identifying and non-identifying relationships
c) booking is a linking table between seat and theatre_film
d) all of the above

A

d)

92
Q

You want to get the total amount of the salaries paid to the employees. What is the correct query?

a) SELECT SUM(salary) FROM employees;
b) SELECT salary FROM employees;
c) SELECT SUM(salary) FROM employees GROUP BY employee_id;
d) SELECT MAX(salary) FROM employees;

A

a)

93
Q

What is NOT true about a VIEW?

a) a view does not physically store the data
b) each time you query the view, the underlying query is executed
c) a view can have the same name of a table
d) a view is a virtual table without physical rows

A

c)

Storing and retrieving data (5 decks)

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